Download

Tuning & diagnostics

The defaults are good. StreamXLS runs correctly out of the box, and most users never set a single option. This page is for the times you want to tune behavior — a faster position refresh, delayed-only quotes, verbose logs — or diagnose something that is misbehaving. Everything here is optional. The Reference defines the topic families and field names these settings affect.

On this page Environment variables Market-data type Logging Version floor Connections

§ · Environment variables

The settings you'll actually touch

The engine reads its configuration from environment variables — the TWS_RTD_* family, plus STREAMXLS_TWSAPI_PATH — the moment Excel first loads it. An invalid value is never fatal: it logs a warning, surfaces on the CONFIGWARNINGS status field, and the setting falls back to its default.
Below are the everyday settings. The engine also honors a set of advanced and diagnostic keys whose defaults are the tested values — most people never touch them. Those are catalogued, with their authoritative ranges and clamping behavior, in the complete configuration-key reference on GitHub, which is generated from the engine itself.
Restart Excel after changing settings. Excel inherits the environment that was in effect when it launched, and the engine reads it once at load. Two ways to change settings:
  • StreamXLS Control Panel → Settings.
  • Set a user-level environment variable (Windows Settings → Edit environment variables for your account)

Where the two disagree, the environment wins: the engine reads environment variables first, then the config file the Control Panel writes, then its own defaults. Clear a variable to hand that setting back to the Control Panel.

Logging
Variable Default Purpose
TWS_RTD_LOG_FILE (none) Path to the log file, e.g. %TEMP%\streamxls.log. Unset means logging is off. Supports environment-variable expansion in the path.
TWS_RTD_LOG_LEVEL INFO Verbosity threshold: ERROR, WARN (or WARNING), INFO, DEBUG, TRACE, VERBOSE (or NONE / OFF to silence). An unrecognized value falls back to INFO with a warning.Use DEBUG when reporting an issue.
Market data
Variable Default Purpose
TWS_RTD_MARKET_DATA_TYPE 4 Which market-data type to request — see Market-data type below. Default 4 (DELAYED_FROZEN) gives real-time data when subscribed and delayed otherwise.Range: 1–4
TWS_RTD_DELAYED_ANNOTATION false When true, delayed market data values are appended with "(delayed)" text to clearly distinguish them from realtime data.
⚠ The annotated cell becomes text (e.g., 10.25 (delayed)), so Excel formulas expecting numbers will not work correctly — see Marking delayed prices below.
TWS_RTD_ERROR_DISPLAY MESSAGE How TWS error responses are handled: MESSAGE displays the error text; NA only returns #N/A.
Connection
Variable Default Purpose
TWS_RTD_CLIENT_ID (auto) Fixed TWS API client ID. Leave unset — the engine auto-picks a per-process ID to avoid collisions between Excel instances. Set only when you need a predictable ID.
TWS_RTD_HOST 127.0.0.1 Default host dialed when a formula does not name one. Any hostname or IP. Per-formula connection tokens override it — see Connections.
TWS_RTD_PORT 7496 Default socket port when a formula does not name one (7496 = live TWS). Per-formula tokens override it.Range: 1–65535
TWS_RTD_PRESERVE_ON_DISCONNECT false Fail loud (default): account, order, position, and P&L subscriptions return #N/A if TWS is not connected or responding. Set true to keep the last-known values in Excel when TWS is unavailable. (Does not apply to market data.)
STREAMXLS_TWSAPI_PATH (auto) Explicit location of the Interactive Brokers TWS API client (CSharpAPI.dll) — a full path to the DLL, or a folder or install-root that contains it. Leave unset — the engine auto-locates the API from your IBKR install (including the default C:\TWS API); set this only when the API lives somewhere non-standard. Supports environment-variable expansion, e.g. %USERPROFILE%\TWS API. Read the binding status in Excel via the TWSAPI_STATE / TWSAPI_MESSAGE metadata topics.

Configuration problems can be checked in Excel: read =RTD("Tws.Rtd", , "status", "CONFIGWARNINGS") to see any warnings the engine raised while loading its settings.

§ · Market-data type

Delayed vs. real-time

TWS serves four market-data types, selected with TWS_RTD_MARKET_DATA_TYPE. The type governs which quotes TWS is willing to send for a contract.

Type Value Subscription needed Data
REALTIME1YesLive streaming quotes.
FROZEN2YesLast available real-time values, frozen (e.g. after the close).
DELAYED3No15–20 minute delayed quotes.
DELAYED_FROZEN4NoDelayed quotes, frozen when the market is closed.

The default is 4 (DELAYED_FROZEN), chosen so StreamXLS works without any market-data subscription. Under type 4, TWS returns real-time data when you do hold a live subscription for the contract, falls back to delayed data when you don't, and serves frozen values when the market is closed. This avoids the "API data requires subscription" error for users without an API market-data subscription.

Set TWS_RTD_MARKET_DATA_TYPE=1 to enforce strict real-time only — TWS then returns an error instead of falling back to delayed data when a subscription is missing. See the Reference market-data fields for the tick names these quotes populate.

Marking delayed prices in the cell

The type is a ceiling request, not a description of your data: under the default (4), TWS serves each contract at the best tier your subscriptions allow and delays the rest, per contract. By default a delayed quote is just a number — nothing in the cell marks it as 15–20 minutes old. Two ways to make delayed-ness visible: display the IsDelayed or MarketDataType field beside your prices (plain numbers, recommended), or turn on in-cell annotation below.

Set TWS_RTD_DELAYED_ANNOTATION=1 — or enable Delayed-price annotation in the StreamXLS Control Panel → Settings — and every delayed value is delivered as text like 12.34 (delayed). It is off by default.

⚠ Read before enabling — the annotated cell becomes text. Excel ranks any text above any number, so comparison and aggregate formulas can go silently wrong: =IF(A1>100,…) is always true, and MAX / AVERAGE / COUNTIF skip the cell — no #VALUE!, just a wrong answer. Plain arithmetic at least fails loudly with #VALUE!. Recover the number in a formula with =VALUE(SUBSTITUTE(A1," (delayed)","")). If you want delayed-awareness without altering the values, add a separate RTD request for the IsDelayed field.
§ · Logging

Logs & support reports

Logging is off by default. To enable, open the StreamXLS Control Panel, Settings, and:
  • Set the Log file to a writable file name and location.
  • Set the Log level. For troubleshooting raise it to Debug or Trace.
  • Restart Excel to run with the new settings.
Log files rotate daily and by default store the most recent 5 days.
§ · Version floor

The TWS API version floor

This is the single most common reason for "everything works except quotes," so it gets a permanent home here. If your positions, orders, account values, and P&L all populate but market-data cells stay empty, your installed TWS API is almost certainly too old.

Interactive Brokers moved market data onto a newer wire protocol that a modern TWS or IB Gateway will only speak once the connection negotiates ServerVersion ≥ 206. An older TWS API can neither request nor decode that protocol, so a modern TWS sends it zero market-data ticks — no error, no quotes — while orders, positions, and account values keep flowing over their still-supported legacy paths. The minimum required TWS API is 10.47.01.

Diagnose it from three per-connection status fields:

  • =RTD("Tws.Rtd", , "STATUS", "ServerVersion") — this connection's negotiated ServerVersion (an integer, or Not Connected).
  • =RTD("Tws.Rtd", , "STATUS", "MarketDataState") — Ok: streaming quotes supported. TooOld: streaming quotes not supported, or Unknown: not yet connected.
  • =RTD("Tws.Rtd", , "STATUS", "MarketDataMessage") — the actionable "update your TWS API" message when the state is TooOld; empty otherwise.

The fix: update your TWS API install to the latest Stable release and reconnect (reopen Excel, or force it with the VBA below). This is a separate package from the TWS or IB Gateway application — updating the desktop app does not update the API. Confirm the binding at any time with the TWSAPI_VERSION and TWSAPI_STATE metadata topics.

§ · Connections

Multiple connections & reconnection

A single workbook can talk to several TWS or IB Gateway instances at once. Each unique host:port:clientid combination is a separate connection, opened on demand from the connection tokens in your formulas. Full token grammar and the port aliases (paper, gw, gwpaper) are on the Reference; the default host and port for formulas that name neither come from TWS_RTD_HOST / TWS_RTD_PORT above.

=RTD("Tws.Rtd", , "port=7496", "AAPL", "Bid")  — live TWS
=RTD("Tws.Rtd", , "port=7497", "AAPL", "Bid")  — paper TWS
=RTD("Tws.Rtd", , "port=4001", "AAPL", "Bid")  — IB Gateway live

Because connections are per host:port:clientid, watch which one you are diagnosing: the status fields scope per-connection, so pass the same connection token (paper, host=, port=…) to read that connection's state, or omit it to piggyback the single connection.

Reconnection

When TWS drops mid-session, StreamXLS reconnects automatically and re-establishes the subscriptions that were active — no need to touch your formulas, and non-volatile values are preserved across the gap. What StreamXLS cannot control is exactly when Excel gives it a chance to try, because reconnection is driven by Excel's heartbeat. Per Microsoft:

The Excel calculation cycle determines when the Heartbeat method is called. In most scenarios with default settings, it is less than 1 second. However, depending on configuration, it could be as long as 2 minutes.

So if Excel is very busy, an automatic reconnect can lag. To force it immediately, reopen Excel or run the VBA call Application.RTD.RefreshData. Watch =RTD("Tws.Rtd", , "STATUS", "IsConnected") to see the link return.