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 (, ,).

Common mistake: putting a connection string in the second argument. =RTD("Tws.Rtd", "127.0.0.1:7497", "AAPL", "BID") is wrong — the connection string is silently ignored and StreamXLS falls back to the default 127.0.0.1:7496. Connection tokens must be topic arguments, e.g. =RTD("Tws.Rtd", , "127.0.0.1:7497", "AAPL", "BID").

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 overrides any clientid= token. See Configuration → Environment variables.

A contract can be specified five interchangeable ways. Pick whichever reads best for the security; the compact and key=value forms are recommended for options and futures.

1 · Simple symbol (stocks)

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

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

2 · Positional

Symbol, security type, exchange, currency, then field.

=RTD("Tws.Rtd", , "AAPL", "STK", "NASDAQ", "USD", "LAST")

3 · Key=value (recommended for complex securities)

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

4 · 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")

5 · 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

=RTD("Tws.Rtd", , "sym=SPY", "sec=BAG",
     "cmb=643929299#1#BUY#SMART;643929301#1#SELL#SMART", "BID")

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 cell always has a reasonable price regardless of market hours or subscription:

Last is strict. It stays #N/A under real-time/frozen data until a trade actually prints, then shows the numeric last. If you want a value that resolves before the first trade — after hours, or without a real-time subscription — use MarketPrice. Per-contract, MarketDataType returns the data type TWS is actually serving for that contract, which can differ from the default.

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:

No quotes while orders and positions work? Your TWS API is almost certainly older than the market-data floor (negotiated ServerVersion 206 / TWS API 10.38.01). StreamXLS withholds market-data topics fail-loud in that case. Diagnose and fix at Configuration → The TWS-API version floor; the per-connection status fields MarketDataState and MarketDataMessage report it.

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.

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.

Reflection fallback. Any field not explicitly mapped is resolved by case-insensitive reflection against the IBKR C# API types (Order, Contract, OrderState) — so many IBApi properties beyond the list above resolve by their native name.

Order-status glossary

StageOrder stages an order in TWS as a side-effect of subscribing — placing the formula in a cell 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")

Required keys

Common optional keys

For a complete list of supported keys see the TWS API Order reference.

Return values

Staged is not the order's market state: StreamXLS always places orders with Transmit=false, so the order lands in the TWS order list staged, awaiting Transmit — it shows a Transmit button you must click to actually send it to the market. TWS sends no confirmation for staged orders (they are invisible to order queries until transmitted or discarded, so they also don't appear in StreamXLS Orders topics); Staged means "delivered to TWS without error." Once you act on the order in TWS, the cell tracks TWS's reports — including recovery from a failed Transmit attempt (e.g. a missing account allocation): fix and transmit in TWS and the cell updates to the working status.

Two cautions

Deduplication. Excel deduplicates RTD topics with identical parameters, so submitting the same contract/side/price/size twice in a row places only one order. To force a second order, add a unique tag/nonce (e.g. nonce=2) so Excel issues a distinct subscription.

Practice in paper first. Because a live cell can stage a live order, build and test order-staging workbooks against a paper session (paper / port 7497) before pointing them at a funded account.

=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.

The bare token-free form also works for single-value status/metadata reads, e.g. =RTD("Tws.Rtd", , "ServerVersion"). Combine IsConnected with LastUpdateUtc to flag stale subscriptions.

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". 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