Skip to main content

ORDERTRACK Formula

AEX.ORDERTRACK is a streaming custom function that creates and manages a live order directly from a spreadsheet cell. When you enter the formula, the add-in submits the order to the AEX matching engine. When you change the price, volume, or live flag, the add-in amends the order. When you delete the formula, the order is left on the server until you cancel it explicitly.

This makes it possible to build trading sheets where your order parameters live in cells — formula-driven pricing models, quote curves, or manual workbenches — and the market engine stays in sync automatically.

Syntax​

=AEX.ORDERTRACK(contractCode, side, volume, price, liveFlag)
ParameterTypeDescription
contractCodestring7-character contract code, e.g. BPK1AH6
sidestringbuy or sell (case-insensitive)
volumenumberOrder quantity in MW (must be > 0)
pricenumberOrder price in $/MWh (must be > 0)
liveFlagbooleanTRUE = order is working in the matching engine; FALSE = order is held on the server

Returns: string (streaming)

What the Cell Shows​

The cell output changes as the order progresses through its lifecycle:

Cell valueMeaning
DISABLEDORDERTRACK formulas are disabled (the global enable switch is off)
PENDING...Formula is active and the order submission is in flight
xl-a1b2c3d4-...Server-assigned order ID — the order is live on the exchange
xl-a1b2c3d4-... [FILLED]Order has been fully filled
xl-a1b2c3d4-... [CANCELLED]Order has been cancelled
xl-a1b2c3d4-... [EXPIRED]Order has expired
xl-a1b2c3d4-... [REJECTED]Order was rejected by the server
ERR: messageA validation or submission error occurred

Once you have an order ID, use AEX.ORDER to read its properties in other cells:

=AEX.ORDER(E2,"status")       ' Status of the order whose ID is in E2
=AEX.ORDER(E2,"wrkQty")       ' Working quantity remaining
=AEX.ORDER(E2,"fillQty")      ' Quantity filled so far
=AEX.ORDER(E2,"avgFillPx")    ' Average fill price

The liveFlag Parameter​

The liveFlag boolean controls whether the order participates in matching:

  • TRUE (working): The order is queued in the matching engine. It can be matched against incoming counter-orders immediately.
  • FALSE (held): The order is persisted on the server and assigned an order ID, but it does not participate in matching. It is invisible to other participants.

Every ORDERTRACK formula always submits an order to the server — even when liveFlag is FALSE. This means:

  • The order has a stable ID from the moment the formula is entered.
  • You can stage held orders and release them to the market by flipping liveFlag to TRUE — without cancelling and resubmitting.
  • You can hold an entire quote sheet and release all legs simultaneously by updating a single reference cell.
' Cell B1 contains TRUE or FALSE — toggle it to release or hold all orders
=AEX.ORDERTRACK("BPK1AH6","buy",10,85.50,$B$1)
=AEX.ORDERTRACK("BPK1AH6","sell",10,86.00,$B$1)
tip

Use a named cell or a single toggle cell as the liveFlag source for a group of ORDERTRACK formulas. Changing the toggle releases or holds all of them at once.

Enable / Disable Mechanism​

ORDERTRACK formulas are disabled by default whenever a workbook opens. This is a deliberate safety feature: opening a workbook containing ORDERTRACK formulas will not automatically submit orders to the exchange.

When ORDERTRACK is disabled, every formula in the workbook returns DISABLED regardless of its parameters.

To enable ORDERTRACK formulas, use the Enable Orders button in the task pane Orders tab. The setting persists for the life of the session. If you close and reopen the workbook, you must enable them again.

caution

Always verify your prices and volumes before enabling ORDERTRACK formulas. Once enabled, the add-in submits orders immediately.

danger

Do not share workbooks containing ORDERTRACK formulas with colleagues who are not expecting to trade. Opening the file will show DISABLED in all cells until they explicitly enable it, but make clear to recipients that the workbook submits live orders.

Cell Address as Reconciliation Key​

Each ORDERTRACK formula is bound to its cell address. The add-in uses the cell address — for example, Sheet1!E4 — as a stable clientOrderId when reconciling with the server.

This means:

  • Moving the formula to a different cell creates a new order.
  • Copying the formula to another cell creates an independent order bound to that cell.
  • Refreshing or recalculating the formula in the same cell reconnects to the existing order rather than creating a duplicate.

The underlying binding ID is derived as xl-bind-<address>. You do not need to manage this manually.

Amendment Debouncing​

When you change the price, volume, or liveFlag in a cell that ORDERTRACK references, the add-in does not immediately send an amendment. Instead, it waits 300 milliseconds after the last change before sending the order.modify event to the server.

This debouncing prevents excessive amendment traffic when you are typing a new price or dragging a cell value. If you make multiple changes within 300 ms (for example, scrolling through values or pasting a range), only the final value is sent.

The cell continues to show the current order ID while an amendment is pending. There is no visual indicator for pending amendments — the order on the server reflects the last confirmed state, not the pending state.

Changing Contract or Side​

If you change contractCode or side in an existing ORDERTRACK formula, the add-in treats this as a fundamentally different order:

  1. The existing server order is cancelled.
  2. A new order is submitted for the new contract or side.
  3. The cell shows PENDING... briefly before the new order ID appears.

Changing price, volume, or liveFlag amends the existing order without cancelling it.

Cancelling an Order​

ORDERTRACK does not automatically cancel server orders when you delete or clear the formula. The order remains on the server in its current state. To cancel:

  • Use the Cancel button next to the order in the task pane Orders tab.
  • Or submit volume = 0 (which is also an error state — see validation below).
  • Or use the Cancel All ribbon command if available.
note

This design is intentional. Excel can cancel and restart streaming functions during normal operations (such as recalculation). If deleting a formula immediately cancelled the order, accidental recalculations could silently remove working orders.

Validation and Error Outputs​

The formula validates its inputs before submitting. Invalid inputs return an error string in the cell:

ErrorCause
ERR: Invalid contractCode: expected a non-empty stringcontractCode is empty or not a string
ERR: side must be BUY or SELLside is not buy or sell
ERR: volume must be > 0volume is zero, negative, or not a number
ERR: price must be > 0price is zero, negative, or not a number
ERR: Cannot resolve contract — product tree not loaded or invalid codeThe contract code is not in the current product tree
ERR: Cell address unavailable — requires Office 2021+Running on an unsupported version of Office
ERR: Submit failed - messageThe server rejected the submission

Practical Examples​

Single order with manual price​

' Enter a price in B2, volume in C2, and toggle in D2
=AEX.ORDERTRACK("BPK1AH6","buy",C2,B2,D2)

Quote curve across multiple contracts​

Set up one row per contract. Use a shared toggle in B1 to release all at once:

ABCDE
Toggle:FALSE
BPK1AH6buy1085.50=AEX.ORDERTRACK(A2,B2,C2,D2,$B$1)
BON3CH6buy1084.00=AEX.ORDERTRACK(A3,B3,C3,D3,$B$1)
BPK1AH6sell1086.00=AEX.ORDERTRACK(A4,B4,C4,D4,$B$1)

Reading order state alongside ORDERTRACK​

' Column E: ORDERTRACK formula — returns order ID
=AEX.ORDERTRACK("BPK1AH6","buy",10,85.50,TRUE)

' Column F: status of that order
=AEX.ORDER(E2,"status")

' Column G: remaining working quantity
=AEX.ORDER(E2,"wrkQty")

' Column H: average fill price
=AEX.ORDER(E2,"avgFillPx")

Next Steps​