XLNative
Download Beta

Documentation

XLNative quick reference

XLNative reads data from your local TWS or IB Gateway session.

Compatibility

The current beta is for M-series Macs running the Microsoft Excel for Mac desktop app, version 16.4 or later. Excel for web, Excel for mobile, Windows Excel, and Intel Macs are not supported in the current beta.

Enable API access

Before Excel formulas can stream data, XLNative needs a local API socket connection to your signed-in TWS or IB Gateway session. Read-only API access is recommended for XLNative.

Trader Workstation (TWS)

  1. Open Global Configuration using the gear icon, or use Edit > Global Configuration.
  2. Open API > Settings.
  3. Select Enable ActiveX and Socket Clients.
  4. Leave Read-Only API selected.
  5. Confirm the Socket Port: normally 7497 for paper trading or 7496 for live trading.
  6. Click Apply, then OK.

IB Gateway

  1. Open Configure > Settings.
  2. Open API > Settings.
  3. Confirm that Enable ActiveX and Socket Clients is selected. IB Gateway normally enables socket connections by default.
  4. Leave Read-Only API selected.
  5. Confirm the Socket Port: normally 4002 for paper trading or 4001 for live trading.
  6. Click Apply, then OK.

XLNative connects locally using host 127.0.0.1. If you choose a different socket port, the value in XLNative must match the value in TWS or IB Gateway. TWS may ask you to approve the local API connection the first time XLNative connects.

For more detail, see the official IBKR setup guide.

Market data

XN.IB(symbol, field) streams live market data into a cell.

Examples

=XN.IB("AAPL","last")=XN.IB("EURUSD:fx","bid")=XN.IB("BTC:crypto","ask")=XN.IB("ES:future:202606","bid")=XN.IB("ES:future:20260620","last")=XN.IB("AAPL:call:200:20260918","delta")=XN.IB("SPX:index","last")=XN.IB("IBUS500:cfd","bid")

iv, delta, gamma, theta, vega, optprice, and undprice are option-only fields. For stocks, futures, FX, crypto, indices, and CFDs, XLNative returns blank for those fields. For option symbols, these fields may also be blank outside regular option market hours, including weekends.

Account summary

XN.ACCOUNT(account, field) streams IBKR account summary values.

Examples

=XN.ACCOUNT("ALL","NetLiquidation")=XN.ACCOUNT("U12345","BuyingPower")=XN.ACCOUNT("U12345","ExcessLiquidity")

Common field names

NetLiquidation, TotalCashValue, BuyingPower, ExcessLiquidity, AvailableFunds, GrossPositionValue, MaintMarginReq, InitMarginReq, UnrealizedPnL, RealizedPnL.

Use ALL to see combined values across accounts when available.

P&L

XN.PNL(account, field) streams session P&L from the IBKR account summary.

Examples

=XN.PNL("U12345","unrealized")=XN.PNL("U12345","realized")=XN.PNL("U12345","daily")=XN.PNL("ALL","unrealized")

Valid field values

  • unrealized — open-position P&L; updates continuously while markets are open.
  • realized — closed-position P&L accumulated for the current session.
  • daily — total daily P&L, unrealized and realized combined.

Use ALL as the account to sum P&L across all managed accounts.

Positions and cost basis

XN.POSITION(account, symbol) streams current position size.

XN.COSTBASIS(account, symbol) streams average cost basis for the same lookup symbol.

Examples

=XN.POSITION("U12345","AAPL")=XN.POSITION("U12345","ES:future:202606")=XN.POSITION("U12345","BTC:crypto")=XN.POSITION("U12345","IBUS500:cfd")=XN.COSTBASIS("U12345","AAPL")=XN.COSTBASIS("U12345","SPX:index")

Flat positions return 0 instead of waiting indefinitely.

Live positions table

XN.POSITIONS(account) returns a streaming spill table of all nonzero positions for an account. The table includes Symbol, Position, and CostBasis columns and refreshes automatically as positions change.

Example

=XN.POSITIONS("U12345")

If the account has no tracked positions yet, the table returns its header row until data arrives.

Symbol guide

  • Stock: TICKER or TICKER:stock
  • Future: SYM:future:YYYYMM or SYM:future:YYYYMMDD[:EXCHANGE]
  • Option: SYM:call|put:STRIKE:YYYYMMDD[:EXCHANGE]
  • FX: EUR:fx or EURUSD:fx
  • Crypto: BTC:crypto[:EXCHANGE]
  • Index: SPX:index[:EXCHANGE[:CURRENCY]]
  • CFD: IBUS500:cfd[:EXCHANGE[:CURRENCY]]

Omit optional exchange or currency segments to use XLNative's normal defaults for that asset class.

Fields

bid · ask · last · close · open · high · low · volume · bidsize · asksize · lastsize · bidlots · asklots · lastlots · iv* · delta* · gamma* · theta* · vega* · optprice* · undprice*

bidsize, asksize, and lastsize are display-oriented size fields. bidlots and asklots keep the raw IBKR lot values.

* option-only, blank for non-option symbols.

Troubleshooting

Cell shows Waiting...

XLNative is still waiting for a live update. Check that the engine is running, IB is connected, and the symbol format is valid.

Cell shows #VALUE!

Usually this means the formula or symbol format is invalid, or IBKR rejected the request. Recheck the exact symbol string and field name.

Task Pane says IB disconnected

Make sure TWS or IB Gateway is open, then follow the API setup steps. Confirm that XLNative uses host 127.0.0.1 and the same socket port shown in TWS or IB Gateway.

No data after restarting TWS or IB Gateway

Give XLNative a moment to reconnect and resubscribe. If needed, use the menu bar app to reconnect manually.