The complete formula grammar.

The shape is always the same:

=RTD("Tws.Rtd", , <topic…>, <field>)

The first argument is the ProgID Tws.Rtd. The second argument is ignored — it is Excel's "Server" parameter, and StreamXLS reads none of it. All connection and contract information lives in the third argument onward. Leave the second argument empty (, ,).

The six topic families

There are six families. Two of them come in singular / list pairs — position/positions and order/orders — so you will see eight topic words in total, but they group into six families. A bare contract (no leading topic word) selects market data.

Return-value convention. A field returns a number (or text) when its value is known to be current. When it is not — TWS disconnected or unresponsive — time-sensitive fields return #N/A rather than a stale value. A visible gap is safer than a silently frozen price.

With no connection tokens, StreamXLS connects to 127.0.0.1:7496 (TWS live). Add connection tokens anywhere in the topic arguments (order does not matter) to target a different host, port, or client ID.

Port aliases

TWS live is 7496; TWS paper 7497; IB Gateway live 4001; IB Gateway paper 4002.

Compact form

Instead of separate host=/port=/clientid= tokens, use one host:port or host:port:clientid string. Use colons — never underscores.

=RTD("Tws.Rtd", , "host=192.168.1.100", "port=4001", "clientid=5", "AAPL", "BID")
=RTD("Tws.Rtd", , "192.168.1.100:4001:5", "AAPL", "BID")
=RTD("Tws.Rtd", , "paper", "AAPL", "BID")            ' 127.0.0.1:7497

Multiple simultaneous connections

Each distinct host:port:clientid is a separate connection. A single workbook can watch a live and a paper session at once — put a different connection token in each cell:

=RTD("Tws.Rtd", , "port=7496", "AAPL", "BID")       ' Live TWS
=RTD("Tws.Rtd", , "port=7497", "AAPL", "BID")       ' Paper TWS
=RTD("Tws.Rtd", , "gw",        "AAPL", "BID")       ' Gateway live

The client ID can be pinned with the TWS_RTD_CLIENT_ID environment variable, which supplies the ID for any connection whose formula does not name one; a clientid= token in the formula wins over it. See Configuration → Environment variables.

1 · Simple symbol (stocks)

A bare symbol assumes SecType=STK, Exchange=SMART, Currency=USD.

=RTD("Tws.Rtd", , "AAPL", "BID")

2 · Key=value (recommended for complex securities)

=RTD("Tws.Rtd", , "sym=SPY", "sec=OPT", "strike=680", "right=C", "exp=20251219", "BID")

3 · Compact notation

Use @ for the exchange and / as a delimiter, omitting trailing segments you don't need. Format: SYMBOL@EXCH/PRIMEXCH/SECTYPE/EXP/RIGHT/STRIKE/CURRENCY.

=RTD("Tws.Rtd", , "AAPL@NASDAQ/STK/USD", "BID")
=RTD("Tws.Rtd", , "ES@CME/FUT/202503/USD", "LAST")
=RTD("Tws.Rtd", , "EUR.USD/CASH", "BID")

4 · ConID (most precise)

The TWS Contract ID identifies a contract unambiguously — useful for options with similar strikes. Find it in TWS via right-click → Financial Instrument Info → Description.

=RTD("Tws.Rtd", , "conid=265598", "BID")            ' AAPL

Worked examples

Market-data formulas take a contract followed by a field: =RTD("Tws.Rtd", , "<contract>", "<field>"). These are the fields nearly every workbook uses.

Common price & size fields

Derived price fields

Two fields are computed by StreamXLS so a formula always has a reasonable price regardless of market hours or subscription:

Last never falls through to a different tick field.

Delayed-data indicators

TWS_RTD_MARKET_DATA_TYPE (default 4) is a ceiling request, not a description of your data: within one session TWS serves real-time to the symbols your subscriptions entitle and 15–20-minute-delayed data to the rest, per contract, and reports the served tier per contract. So "type 4 is set" never means every cell is delayed. Two per-contract fields read the served tier back, so a delayed price can't silently pass for live:

Check IsDelayed per symbol rather than assuming from the requested type. To instead turn every delayed price into visibly-marked text — at the cost of making the cell text — see TWS_RTD_DELAYED_ANNOTATION.

Option greeks

For an option contract, greeks and model values are exposed per computation source. Field names compose as <Source><Measure>, where the source is Bid, Ask, Last, or Model and the measure is one of the eight below — e.g. ModelDelta, BidImpliedVol, LastOptPrice:

=RTD("Tws.Rtd", , "sym=SPY", "sec=OPT", "strike=680", "right=C", "exp=20251219", "ModelDelta")

Option-chain enumeration

Query an underlying to discover its option chain:

Full tick catalog

Every tick type StreamXLS maps, by field name. Core price/size ticks arrive for any tradeable security; the rest are requested per security type. When delayed data is active, the delayed tick IDs (66–76, 88, 90) feed the same base field names above — you keep using BID, LAST, and so on.

Read any IBKR account-summary value with =RTD("Tws.Rtd", , "account", "<acct>", "<field>"). Numeric values return as numbers. Append a currency filter ("cur=USD") to pull a per-currency variant instead of the generic figure.

=RTD("Tws.Rtd", , "account", "U1234567", "NetLiquidation")
=RTD("Tws.Rtd", , "account", "U1234567", "AvailableFunds", "cur=USD")
=RTD("Tws.Rtd", , "account", "U1234567", "OpenPositionCount")

Fields are pass-through. Any IBKR account-summary tag resolves — case-insensitive and separator-tolerant ("Net Liquidation" matches NetLiquidation). Around 136 tags are delivered; the demo workbook's Account worksheet is the canonical enumeration. Rather than list all of them, here are the common anchors:

OpenPositionCount is computed per-account from non-zero positions and updates live. For the full field list open the demo workbook and read its Account worksheet. For clean resolution of aggregates vs. per-currency values, enable TWS's "$LEDGER-" prefix API setting — see Get Started → TWS / Gateway setup.

Single / aggregate position

=RTD("Tws.Rtd", , "position", "<acct>", "<contract>", "<field>"). The account argument accepts one code, a comma-separated list, or * / blank for all accounts — matched positions aggregate (shares and market values sum; average cost is position-weighted).

=RTD("Tws.Rtd", , "position", "U1234567", "AAPL", "UnrealizedPnL")
=RTD("Tws.Rtd", , "position", "*", "AAPL@SMART", "MarketValue")

Positions list

=RTD("Tws.Rtd", , "positions", "<accts>", "<field>") enumerates the active positions across the accounts (every position where size ≠ 0 or market value ≠ 0).

Spill the list across cells with TEXTSPLIT (Excel 365). The list delimiter is a semicolon:

=TEXTSPLIT(RTD("Tws.Rtd", , "positions", , "SymbolsCsv"), ";")           ' across columns
=TEXTSPLIT(RTD("Tws.Rtd", , "positions", , "SymbolsCsv"), , ";")         ' down rows
=IFERROR(TEXTSPLIT(RTD("Tws.Rtd", , "positions", , "SymbolsCsv"), ";"), "")  ' first-load guard

Initial-load guard. The server withholds the first SymbolsCsv/ConIdCsv/PositionsChangedUtc until the initial positions snapshot completes, so wrap the spill in IFERROR to avoid a transient error on first load. Pair PositionsChangedUtc with SymbolsCsv to detect when the set changes.

Orders list

=RTD("Tws.Rtd", , "orders", "<accts>", "<field>") returns a comma-separated list of PermIDs, filtered by account (* / blank = all).

Single-order fields

=RTD("Tws.Rtd", , "order", "<permID>", "<field>"). Orders are addressed by PermID (the permanent, cross-client order ID that the orders lists return) — not the transient client-side order ID. Individual order fields always show last-known values regardless of order status.

Closed field set. The list above is the complete set of supported order fields. An unknown or misspelled field errors loudly in the cell at formula entry (e.g. Unknown order field 'FOO'…) rather than silently showing #N/A. Need a field that isn't listed? Contact support — additions are trivial.

ORDERREF returns your bare tag/ClientTag for an engine-staged order — the engine strips its own |SXLS:<token> suffix, so a tagless engine order reads #N/A, not blank. Orders entered in TWS or by other clients return their Order Ref verbatim. (The TWS-visible composed format is described under Staging orders.)

Order-status glossary

StageOrder populates an order ticket in TWS as a side-effect of subscribing — entering the formula is what stages it. It takes key=value tokens in any order and returns a status string. (SendOrder is an accepted synonym; both spellings parse identically.)

=RTD("Tws.Rtd", , "StageOrder", "sym=AAPL", "side=BUY", "shares=100",
     "type=LMT", "limit=150.05", "exch=SMART", "tag=SampleOrder#1")

Required keys

Common optional keys

Order attribute keys (validated)

Return values

How the staged order presents in TWS is chosen per formula with the optional park key.

• Default: the order appears in TWS order lists as a deactivated ("PreSubmitted") order with a Submit button. PreSubmitted orders are visible to other TWS instances viewing the same account, and survive a TWS restart.
• park=true (synonyms parked=, saved=): populates a local order ticket, which appears only in your TWS order list with a Transmit button. Other TWS instances on the account don't see it, and it does not appear in the API's Orders list. (TWS assigns a permanent id only if it is transmitted.)

Either way the order can never reach the market without a human click in TWS. Once you act on the order there, the cell tracks TWS's reports — including recovery from a failed release attempt (e.g., a missing account allocation): fix and transmit in TWS and the cell updates to the working status.

To watch and manage live orders, use the Orders topics.

=RTD("Tws.Rtd", , "status", "<field>"). Status fields are per-connection: when several connections exist, supply a connection token (paper/live, or host=/port=) to target one, or omit it to piggyback the single connection.

Metadata fields take no topic word — just the field: =RTD("Tws.Rtd", , "<field>"). They resolve in every license state, so a workbook can surface build and license status without special handling.

Build

License

TWS-API binding

Update breadcrumb

The UPDATE_* fields read a local, fail-open breadcrumb; the engine makes no network call, and no breadcrumb means "up to date". Each field comes in two forms: as a metadata field (shown above) it resolves once when the cell connects, and the same four names are also available as live status fields — =RTD("Tws.Rtd",,"status","UPDATE_CRITICAL") — which re-check on every heartbeat, so a critical-update notice can reach a cell mid-session without re-entering the formula. For how the 30-day trial auto-starts and how to activate a license key, see Get Started → Your trial & license.

← Get Started Configuration → How it works