XLNative
Get Free 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.

Native Mac architecture

How does XLNative run without Windows RTD or DDE?

XLNative bypasses Windows-only Excel technologies and the Windows registry entirely. It does not use RTD, DDE, COM, or a compiled Windows .xll add-in. Instead, its cross-platform Excel architecture connects directly to the local TWS API network socket, so XN. formulas calculate and refresh in Excel for Mac.

Do I need Parallels or a virtual machine?

No. XLNative runs directly on macOS. It requires no Parallels installation, Windows license, virtual machine, or dual-boot environment.

Is XLNative only a market-data feed?

No. In addition to quotes, sizes, option IV, Greeks, and model fields through XN.MARKET, XLNative streams live IBKR account metrics through XN.ACCOUNT, real-time session P&L through XN.PNL, and holdings through XN.POSITION, XN.COSTBASIS, and the dynamic XN.POSITIONS portfolio array. 

=XN.MARKET("AAPL","last")
=XN.ACCOUNT("U12345","NetLiquidation")
=XN.ACCOUNT("U12345","BuyingPower")
=XN.POSITIONS("U12345")

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. XLNative works whether Read-Only API is selected or not, but read-only access is recommended unless you use the IBKR API with another app that requires write access.

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. Optional: add 127.0.0.1 to Trusted IPs if it is not already listed. This can reduce local API approval prompts.
  5. Keep Read-Only API selected unless you use the IBKR API with another app that requires write access. XLNative works either way.
  6. Set the Socket Port to 7496.
  7. 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. Optional: add 127.0.0.1 to Trusted IPs if it is not already listed. This can reduce local API approval prompts.
  5. Keep Read-Only API selected unless you use the IBKR API with another app that requires write access. XLNative works either way.
  6. Set the Socket Port to 4001.
  7. Click Apply, then OK.

XLNative connects locally using host 127.0.0.1. As currently configured, XLNative tries port 7496 for TWS and port 4001 for IB Gateway. TWS or IB Gateway may still 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.MARKET(symbol, field) streams live market data into a cell. The field argument is optional; XN.MARKET("AAPL") is the same as XN.MARKET("AAPL","last").

Examples

=XN.MARKET("AAPL")
=XN.MARKET("AAPL","last")
=XN.MARKET("SPX:index","last")
=XN.MARKET("ES:future:20260813","last")
=XN.MARKET("EURUSD:fx","bid")
=XN.MARKET("BTC:crypto","last")
=XN.MARKET("AAPL:call:105:20260815","delta")
=XN.MARKET("AAPL:call:105:20260815","iv")
=XN.MARKET("AAPL:call:105:20260815","askiv")
=XN.MARKET("ETH:crypto:USD","last")
=XN.MARKET("IBUS500:cfd","bid")

iv, bidiv, askiv, lastiv, modeliv, 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.

For options and futures options with no trade today, last may show a marked fallback: C for previous close, M for mark price, or O for model option price, such as C2.49.

Account summary

Start with XN.ACCOUNTS() to see the IBKR account IDs visible from the active local TWS or IB Gateway session. Use the returned account ID in other account, P&L, position, and cost-basis formulas.

Account discovery

=XN.ACCOUNTS()
=XN.ACCOUNTS("details")

If cell A2 contains an account ID returned by XN.ACCOUNTS(), you can use references such as =XN.ACCOUNT(A2,"NetLiquidation") and =XN.POSITIONS(A2).

Use XN.ACCOUNTS("details") to include account metadata when IBKR provides it: Account, AccountType, Alias, and BaseCurrency.

Account summary values

XN.ACCOUNT(account, field) streams IBKR account summary values, which IBKR updates about every three minutes.

Examples

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

Common field names

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

For real-time session P&L, use XN.PNL instead of account-summary P&L fields.

Use ALL to see combined values across accounts when available.

P&L

XN.PNL(account, field, [symbol]) streams account-level or symbol-level session P&L in real time from IBKR's P&L subscription.

Examples

=XN.PNL("U12345","unrealized")
=XN.PNL("U12345","realized")
=XN.PNL("U12345","daily")
=XN.PNL("ALL","unrealized")
=XN.PNL("U12345","daily","AAPL")
=XN.PNL("U12345","unrealized","AAPL:call:105:20260815")
=XN.PNL("U12345","daily","ES:future:202606")

Valid field values

  • unrealized — real-time open-position P&L.
  • realized — real-time closed-position P&L accumulated for the current session.
  • daily — real-time total daily P&L, unrealized and realized combined.

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

Pass a symbol as the third argument to stream contract-level P&L for a specific account. Contract-level P&L requires a specific account ID; ALL is not supported with a symbol.

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:20260813")
=XN.POSITION("U12345","BTC:crypto")
=XN.COSTBASIS("U12345","AAPL")
=XN.COSTBASIS("U12345","IBM")

Flat positions return 0 instead of waiting indefinitely.

Use a specific account for position functions. ALL is not supported for XN.POSITION, XN.COSTBASIS, or XN.POSITIONS.

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")
=XN.POSITIONS(A2)

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

Symbol guide

  • Stock or ETF: AAPL or AAPL:stock
  • Index: SPX:index
  • Option: AAPL:call:105:20260815
  • Future: ES:future:20260813
  • FX: EUR:fx or EURUSD:fx
  • Crypto: BTC:crypto or BTC:crypto:USD
  • CFD: IBUS500:cfd

Omit optional exchange or currency segments to use XLNative's normal defaults for that asset class. BTC:crypto defaults to BTC:crypto:USD; use explicit pairs such as ETH:crypto:USD when you want to be specific.

Fields

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

iv is the same as modeliv. bidiv, askiv, lastiv, and modeliv expose IBKR's bid, ask, last-trade, and model implied-volatility computations.

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 the socket port is 7496 for TWS or 4001 for 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.