Skip to main content

Streaming vs Static Functions

AEX custom functions come in two types: streaming and static. Understanding the difference helps you build efficient, reliable workbooks.

Streaming Functions​

A streaming function maintains a live subscription to market data or order state. The add-in pushes updates to the cell automatically — you do not need to press F9 or trigger a recalculation. The cell value changes whenever the underlying data changes.

Internally, each streaming function registers an onCanceled callback. When you delete the formula or close the workbook, the subscription is torn down cleanly. Updates are throttled to a minimum interval of 100 ms per cell to avoid overwhelming Excel with repaints.

When you reopen a workbook, Excel shows #GETTING_DATA in streaming function cells until the add-in reconnects and pushes the first value. This is a security feature of the Office shared runtime — streaming results are never persisted to disk.

Static Functions​

A static function returns a value once when Excel evaluates it. It does not update automatically. To get a fresh result, press F9 to recalculate the workbook (or Ctrl+Alt+F9 for a full recalculation).

Static functions are appropriate for reference data that changes infrequently — such as the list of available contracts or contract metadata.

Which Functions Are Streaming vs Static​

FunctionTypeReturns
AEX.PRICEStreamingScalar
AEX.VOLUMEStreamingScalar
AEX.LASTStreamingScalar
AEX.SPREADStreamingScalar
AEX.BOOKStreamingSpill array
AEX.SESSIONStreamingScalar
AEX.TIME_REMAININGStreamingScalar
AEX.STATUSStreamingScalar
AEX.ORDERTRACKStreamingScalar
AEX.ORDERStreamingScalar
AEX.ORDERSStreamingSpill array
AEX.FILLSStreamingSpill array
AEX.POSITIONStreamingScalar
AEX.POSITIONSStreamingSpill array
AEX.CONTRACTSStaticSpill array
AEX.CONTRACT_INFOStaticSpill array
AEX.ROUNDTOTICKStaticScalar

Spill Ranges​

Several functions return a matrix — a two-dimensional array that spills into adjacent cells. Excel automatically occupies the cells needed to display the full result.

FunctionSpill dimensions
AEX.BOOK(depth + 1) rows × 4 columns
AEX.CONTRACTSOne row per contract + header row × 4 columns
AEX.CONTRACT_INFO5 rows × 2 columns
AEX.ORDERSOne row per working order × 1 column
AEX.FILLSOne row per fill + header row × 7 columns
AEX.POSITIONSOne row per position + header row × 8 columns
caution

Spill errors: If any cell in the spill range is occupied, Excel shows a #SPILL! error. Always ensure there is a clear block of empty cells below and to the right of a spill function. For streaming spill functions (AEX.BOOK, AEX.ORDERS, AEX.FILLS, AEX.POSITIONS), the spill range can grow or shrink as data changes — keep a safety margin.

Performance Considerations​

Every streaming function maintains an active subscription. Each subscription:

  • Holds a callback in the add-in's subscription registry.
  • Calls invocation.setResult() up to 10 times per second (100 ms throttle).
  • Causes Excel to repaint the affected cell on each update.

For a small number of functions (tens of cells), this has negligible impact. For large workbooks with hundreds of streaming cells, consider the following:

Limit streaming functions to what you need​

Use AEX.PRICE for the specific contract and level you need rather than building a large matrix of every contract. Fetch the full order book with AEX.BOOK only when you need all depth levels.

Use static functions for stable reference data​

AEX.CONTRACTS, AEX.CONTRACT_INFO, and AEX.ROUNDTOTICK are static. They do not consume subscription resources. Use them freely for lookup tables and contract lists, and only refresh them with F9 when you need updated data.

Prefer AEX.POSITIONS() over many AEX.POSITION() calls​

AEX.POSITIONS() uses a single subscription regardless of how many contracts you hold. Using 10 individual AEX.POSITION() calls creates 10 subscriptions. For a compact position summary, AEX.POSITIONS() is more efficient.

Limit AEX.FILLS count​

Pass a count argument to AEX.FILLS to limit the number of rows returned. A large fill history produces a large spill array that Excel repaints on every new fill.

=AEX.FILLS("BPK1AH6",20)      ' Last 20 fills only — more efficient than all fills

Avoid redundant streaming cells​

If you need the same price in multiple places, reference a single AEX.PRICE cell rather than entering the formula multiple times. A single streaming function feeds many dependent formula cells without extra subscription overhead.

Recalculation​

ScenarioAction
Streaming function cell is stale after reconnectWait — the add-in pushes the first value within seconds of reconnecting
Static function cell needs a refreshPress F9 to recalculate
Streaming function shows #GETTING_DATAThe add-in is connecting — wait for authentication and connection
Streaming function shows #VALUE!An input parameter is invalid — check the formula arguments
Spill function shows #SPILL!Clear the cells in the spill range

Best Practices Summary​

  • Use streaming functions for anything that needs to auto-update: prices, order state, positions, fills.
  • Use static functions for contract reference data and tick rounding — they are lightweight and stable.
  • Keep spill ranges clear of other data by isolating them in dedicated areas of the sheet.
  • Pass a count parameter to AEX.FILLS in large workbooks.
  • Consolidate repeated price reads into a single reference cell.
  • Use AEX.STATUS() in a status cell to confirm the add-in is connected before relying on other values.

Next Steps​