Custom Functions Reference
The AEX Excel Add-in provides a library of custom functions under the AEX. namespace. You enter them like any Excel formula — for example, =AEX.PRICE("BPK1AH6","bid",1). Functions marked as streaming push updates to your cell automatically as market data changes. Functions marked as static return a value once and require manual recalculation (press F9) to refresh.
All functions require an active, authenticated connection. If the add-in is not connected, streaming functions show #GETTING_DATA and static functions return stale or empty results.
Market Data Functions
These functions stream live market data from the order book. They update automatically whenever the underlying data changes.
AEX.PRICE
Returns the real-time bid or ask price at a specified depth level.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code, e.g. BPK1AH6 |
side | string | bid or ask |
level | number | Price level, 1–5 (1 = best) |
Returns: number (streaming)
=AEX.PRICE("BPK1AH6","bid",1) ' Best bid price
=AEX.PRICE("BPK1AH6","ask",1) ' Best ask price
=AEX.PRICE("BPK1AH6","bid",2) ' Second-best bid price
Returns #N/A when there is no order at that level.
AEX.VOLUME
Returns the real-time volume available at a specified price level.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
side | string | bid or ask |
level | number | Price level, 1–5 (1 = best) |
Returns: number in MW (streaming)
=AEX.VOLUME("BPK1AH6","bid",1) ' Volume at best bid
=AEX.VOLUME("BPK1AH6","ask",1) ' Volume at best ask
AEX.LAST
Returns the price of the most recent trade for a contract.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
Returns: number (streaming)
=AEX.LAST("BPK1AH6")
Returns #N/A if no trades have occurred in the current session.
AEX.SPREAD
Returns the current bid-ask spread as a percentage of the mid-price.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
Returns: number (percentage, streaming)
=AEX.SPREAD("BPK1AH6")
AEX.BOOK
Returns the full order book as a spill array with headers. The array dimensions are (depth + 1) × 4.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
depth | number | Number of price levels to return, 1–5 |
Returns: matrix — columns: Bid Vol, Bid, Ask, Ask Vol (streaming, spill array)
=AEX.BOOK("BPK1AH6",5)
Enter AEX.BOOK in an empty cell and ensure there are at least depth + 1 blank rows and 4 blank columns below and to the right. Excel spills the result automatically.
AEX.SESSION
Returns the current market session state.
Parameters: none
Returns: string — one of OPEN, PRE_OPEN, CLOSED, or HALTED (streaming)
=AEX.SESSION()
AEX.TIME_REMAINING
Returns the time remaining until market close for the current session.
Parameters: none
Returns: string (streaming)
=AEX.TIME_REMAINING()
AEX.STATUS
Returns the current connection and authentication status of the add-in.
Parameters: none
Returns: string (streaming)
=AEX.STATUS()
Use this in a status cell to confirm the add-in is connected before relying on other streaming functions.
Reference Functions
Reference functions return data from the product tree loaded at session start. They are static — they return once and do not auto-update. Press F9 to recalculate if the product tree changes.
AEX.CONTRACTS
Returns all available contracts as a spill array. Optionally filtered by GXP node.
| Parameter | Type | Required | Description |
|---|---|---|---|
node | string | No | GXP node filter, e.g. BEN or OTA |
Returns: matrix — columns: Node, Series, Contract, Expiry (static, spill array)
=AEX.CONTRACTS() ' All contracts
=AEX.CONTRACTS("BEN") ' Benmore contracts only
=AEX.CONTRACTS("OTA") ' Otahuhu contracts only
AEX.CONTRACT_INFO
Returns details for a single contract as a spill array.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
Returns: matrix — rows: ID, Code, Expiry, State (static, spill array)
=AEX.CONTRACT_INFO("BPK1AH6")
AEX.ROUNDTOTICK
Rounds a price to the nearest valid tick size for a contract. The tick size for all AEX contracts is $0.05/MWh. The contractCode parameter validates that the contract exists in the product tree.
| Parameter | Type | Description |
|---|---|---|
price | number | Raw price to round |
contractCode | string | Contract code (used for validation) |
Returns: number (static)
=AEX.ROUNDTOTICK(A2,"BPK1AH6") ' Round price in A2 to nearest $0.05
Returns ERR: Contract not found if the contract code is invalid.
Use AEX.ROUNDTOTICK to clean up prices before passing them to AEX.ORDERTRACK. Submitting an off-tick price will be rejected by the server.
Order Functions
AEX.ORDERTRACK
Creates and manages a live order directly from a cell. See ORDERTRACK Formula for a full explanation of its behaviour.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
side | string | buy or sell |
volume | number | Order quantity in MW |
price | number | Order price in $/MWh |
liveFlag | boolean | TRUE = working (in matching); FALSE = held (has ID but not matching) |
Returns: string — the server-assigned order ID once confirmed, or a status string (streaming)
=AEX.ORDERTRACK("BPK1AH6","buy",10,85.50,TRUE)
Possible cell outputs: PENDING..., an order ID like xl-a1b2c3..., DISABLED, or ERR: message.
ORDERTRACK formulas are disabled by default when a workbook opens. Enable them from the task pane Orders tab before they will submit orders. See ORDERTRACK Formula for details.
AEX.ORDER
Returns a live-updating property of an order. Pair this with the order ID returned by AEX.ORDERTRACK or AEX.ORDERS.
| Parameter | Type | Description |
|---|---|---|
orderId | string | Order ID |
property | string | Property name (see table below) |
Returns: string or number (streaming)
Available properties:
| Property | Type | Description |
|---|---|---|
status | string | working, held, partial, filled, cancelled, expired, or rejected |
price | number | Limit price in $/MWh |
side | string | BUY or SELL |
wrkQty | number | Working quantity remaining in MW |
fillQty | number | Quantity filled so far in MW |
avgFillPx | number | Average fill price in $/MWh |
contractId | string | Contract UUID |
timestamp | string | Order creation time |
source | string | Order origin: ordertrack or manual |
=AEX.ORDER(A2,"status") ' Order status (A2 contains the order ID)
=AEX.ORDER(A2,"wrkQty") ' Remaining working quantity
=AEX.ORDER(A2,"avgFillPx") ' Average fill price
AEX.ORDERS
Returns all working order IDs as a spill array. Optionally filtered by contract or side.
| Parameter | Type | Required | Description |
|---|---|---|---|
contractCode | string | No | Filter by contract code |
side | string | No | Filter by side: buy or sell |
Returns: matrix of order ID strings (streaming, spill array)
=AEX.ORDERS() ' All working orders
=AEX.ORDERS("BPK1AH6") ' Orders for a specific contract
=AEX.ORDERS("BPK1AH6","buy") ' Buy orders for a specific contract
Position and Fill Functions
AEX.FILLS
Returns fill history as a spill array. Rows include a header row.
| Parameter | Type | Required | Description |
|---|---|---|---|
filterKey | string | Yes | Filter by order ID or contract code |
count | number | No | Maximum number of fills to return |
Returns: matrix — columns: Fill ID, Order ID, Contract, Side, Price, Qty, Time (streaming, spill array)
=AEX.FILLS("BPK1AH6",10) ' Last 10 fills for contract BPK1AH6
=AEX.FILLS(A2) ' All fills for the order ID in A2
AEX.POSITION
Returns a single property of a position for a specific contract.
| Parameter | Type | Description |
|---|---|---|
contractCode | string | 7-character contract code |
property | string | Property name (see table below) |
Returns: number (streaming)
Available properties:
| Property | Description |
|---|---|
netQty | Net position in MW (positive = long, negative = short) |
long | Total long volume filled |
short | Total short volume filled |
avgBuyPx | Volume-weighted average buy price |
avgSellPx | Volume-weighted average sell price |
avgOpenPx | Average open price |
pnl | Unrealised P&L for the session |
=AEX.POSITION("BPK1AH6","netQty") ' Net position in MW
=AEX.POSITION("BPK1AH6","pnl") ' Unrealised P&L
=AEX.POSITION("BPK1AH6","avgBuyPx") ' Average buy price
Returns #N/A if no position exists for the contract.
AEX.POSITIONS
Returns all current positions as a spill array.
Parameters: none
Returns: matrix — columns: Contract, Net Qty, Long, Short, Avg Buy, Avg Sell, Avg Open, PnL (streaming, spill array)
=AEX.POSITIONS()
Next Steps
- ORDERTRACK Formula — deep dive on creating and managing orders from cells
- Position Tracking — building a position blotter with
AEX.POSITIONandAEX.POSITIONS - Streaming vs Static Functions — performance considerations and best practices