Excel

OptionsRealTime provides a comprehensive Excel RTD (Real-Time Data) API that streams live portfolio data directly into spreadsheets.

Overview

The Excel integration subscribes directly to the same NATS data stream as the web UI, with full access to all fields and accounts. Where the web UI provides pre-built views for analyzing risk, Excel is the path for users who want to build their own dashboards and analytics in a spreadsheet, driven by RTD formulas.

The API is self-discoverable: functions exist to enumerate accounts, tables, fields, and values. Workbooks can be built to discover the connected account and adapt automatically.

Excel runs as an independent client. It can run on the same machine as the web UI or on any other machine that can reach the NATS server, and does not require the web UI to be running.

Getting Started

1. Ensure OptionsRealTime Rover is running and connected to TWS (no user interfaces need to be running)
2. Open the provided OptionsRealTime.xlsx template or create a new workbook
3. RTD functions automatically connect to the NATS data stream

Configuration

The Excel RTD add-in reads its settings from OptionsRealTime.ini, located in the installation folder (typically C:\Users\<username>\AppData\Local\OptionsRealTime\).

You can edit this file using the shortcut: OptionsRealTime → Advanced → Edit Excel RTD Config

[Addin]
LogLevel="trace"
LogPopupLevel="error"
DateFormats=["%Y-%m-%d", "%Y%b%d"]

[OptionsRealTime]
NatsUrl="nats://localhost:4222"
RtdThrottleMs=250
MaskSize=4

Setting	Description
NatsUrl	NATS server address. Default is localhost:4222.
RtdThrottleMs	Minimum interval between Excel updates in milliseconds. Lower = more responsive, higher = less CPU usage.
MaskSize	Number of characters to mask in account IDs (for privacy when sharing screenshots).

Multi-Machine Setup

If you’re running Excel on a different machine than NATS and Rover, you need to point Excel to the NATS server’s IP address:

1. Open OptionsRealTime → Advanced → Edit Excel RTD Config
2. Change NatsUrl from localhost to your NATS server’s IP:

   NatsUrl="nats://192.168.1.100:4222"

3. Save and restart Excel

See the Multi-Machine Setup guide for complete instructions.

Function Reference

Real-Time Data

These functions return live values from the four core tables.

Function	Description	Arguments
ort_portfolio_value	Returns live portfolio field value	account ID, field name
ort_system_value	Returns live system field value	account ID, field name
ort_position_value	Returns live position field value	account ID, contract ID or symbol, field name
ort_underlying_value	Returns live underlying field value	account ID, contract ID or underlying, field name

Scenario Metrics

Access the 5×9 implied volatility and price move scenario grid.

Function	Description	Arguments
ort_position_metric	Returns metric value from scenario grid for a position	account ID, contract ID or symbol, metric field, IV label, move label
ort_underlying_metric	Returns metric value from scenario grid for an underlying	account ID, contract ID or underlying, metric field, IV label, move label
ort_underlying_metric_price	Returns underlying price at specified move scenario	account ID, underlying, move label

Account Discovery

Function	Description	Arguments
ort_accounts_size	Returns count of connected accounts	(none)
ort_account_at	Returns account ID at 1-based index	index
ort_accounts_masked_size	Returns count of connected accounts	(none)
ort_account_masked_at	Returns masked account ID at 1-based index	index

Position Discovery

Function	Description	Arguments
ort_position_conids_size	Returns count of positions for account	account ID
ort_position_conid_at	Returns position contract ID at 1-based index	account ID, index
ort_position_symbols_size	Returns count of position symbols for account	account ID
ort_position_symbol_at	Returns position symbol at 1-based index	account ID, index
ort_position_expirations_size	Returns count of unique expiration dates	account ID
ort_position_expiration_at	Returns expiration date at 1-based index (sorted ascending)	account ID, index

Position Filter by Underlying

Function	Description	Arguments
ort_position_conids_by_underlying_size	Returns count of positions for a given underlying	account ID, underlying
ort_position_conid_by_underlying_at	Returns conId at index for underlying	account ID, underlying, index
ort_position_symbols_by_underlying_size	Returns count of positions for a given underlying	account ID, underlying
ort_position_symbol_by_underlying_at	Returns symbol at index for underlying	account ID, underlying, index

Position Filter by Expiration

Function	Description	Arguments
ort_position_conids_by_expiration_size	Returns count of positions for a given expiration date	account ID, expiration
ort_position_conid_by_expiration_at	Returns conId at index for expiration	account ID, expiration, index
ort_position_symbols_by_expiration_size	Returns count of positions for a given expiration date	account ID, expiration
ort_position_symbol_by_expiration_at	Returns symbol at index for expiration	account ID, expiration, index

Underlying Discovery

Function	Description	Arguments
ort_underlying_conids_size	Returns count of underlyings for account	account ID
ort_underlying_conid_at	Returns underlying contract ID at index	account ID, index, [sort field], [sort order]
ort_underlying_symbols_size	Returns count of underlying symbols for account	account ID
ort_underlying_symbol_at	Returns underlying symbol at index	account ID, index, [sort field], [sort order]
ort_underlying_symbols_size_by_metric	Returns count of underlying symbols (metric-sorted)	account ID, metric field, IV label, move label
ort_underlying_symbol_at_by_metric	Returns underlying symbol at index, sorted by metric value	account ID, index, metric field, IV label, move label, sort order

Notes:

• Parameters in [brackets] are optional
• Metric field: DELTA_DLRS_METRICS, DELTA_METRICS, EXTRINSIC_VAL_METRICS, GAMMA_DLRS_METRICS, GAMMA_METRICS, PNL_METRICS, PNL_UNREALIZED_METRICS, THETA2_ORT_METRICS, THETA_LIN_METRICS, THETA_METRICS, TM_VAL_METRICS (position table only), VALUE_METRICS, VEGA_METRICS
• IV label: MKT, IV10, IV25, IV50, IV75
• Move label: -20%, -10%, -5%, -1%, 0%, +1%, +5%, +10%, +20%
• Sort order: DESC (highest first, default) or ASC (lowest first)
• _by_metric functions sort underlyings by a specific cell in the 5×9 metric grid (e.g., PNL at MKT/0% or DELTA at IV25/+5%)

Field Discovery

Enumerate available fields for each table.

Function	Description	Arguments
ort_system_fields	Returns array of valid System table field names	(none)
ort_system_fields_size	Returns count of System table fields	(none)
ort_system_field_at	Returns System field name at 1-based index	index
ort_portfolio_fields	Returns array of valid Portfolio table field names	(none)
ort_portfolio_fields_size	Returns count of Portfolio table fields	(none)
ort_portfolio_field_at	Returns Portfolio field name at 1-based index	index
ort_position_fields	Returns array of valid Position table field names	(none)
ort_position_fields_size	Returns count of Position table fields	(none)
ort_position_field_at	Returns Position field name at 1-based index	index
ort_underlying_fields	Returns array of valid Underlying table field names	(none)
ort_underlying_fields_size	Returns count of Underlying table fields	(none)
ort_underlying_field_at	Returns Underlying field name at 1-based index	index

Metric and Scenario Labels

Function	Description	Arguments
ort_move_labels	Returns array: -20%, -10%, -5%, -1%, 0%, +1%, +5%, +10%, +20%	(none)
ort_move_labels_size	Returns count of move scenario labels (9)	(none)
ort_move_label_at	Returns move label at 1-based index	index
ort_iv_labels	Returns array: MKT, IV10, IV25, IV50, IV75	(none)
ort_iv_labels_size	Returns count of IV scenario labels (5)	(none)
ort_iv_label_at	Returns IV label at 1-based index	index
ort_metric_fields	Returns array of valid metric field names. TM_VAL_METRICS is only available for positions (ort_position_metric), not underlyings.	(none)
ort_metric_fields_size	Returns count of metric field names (13)	(none)
ort_metric_field_at	Returns metric field name at 1-based index	index

Account Masking

Function	Description	Arguments
ort_get_mask_size	Returns current account mask size (number of asterisks)	(none)
ort_set_mask_size	Sets account mask size (1-8) and returns actual value	size
ort_mask_account	Masks an account ID (e.g., U2641234 → U264****)	account ID
ort_unmask_account	Converts masked account ID to real account ID	masked account ID

Connection and Control

Function	Description	Arguments
ort_nats_state	Returns live NATS connection state	(none)
ort_nats_start	Starts NATS connection	(none)
ort_nats_stop	Stops NATS connection	(none)
ort_nats_init	Starts or stops NATS connection	start (TRUE/FALSE)
ort_request_refresh	Request full data refresh for an account	account ID
ort_request_refresh_all	Request full data refresh for all known accounts	(none)

Configuration

Function	Description	Arguments
ort_config_url	Returns configured NATS URL from ini file	(none)
ort_config_throttle	Returns current RTD throttle interval in ms	(none)
ort_set_rtd_throttle_msec	Sets RTD throttle interval (50-60000 ms)	milliseconds

Utility

Function	Description	Arguments
ort_version	Returns ORT version and build info	(none)
ort_utc_now	Returns current UTC date/time (YYYY-MM-DD HH:MM:SS)	(none)
ort_utc_now_rtd	Returns live UTC time (RTD)	(none)

Template Workbook

OptionsRealTime includes a template workbook (OptionsRealTime.xlsx) with pre-built tabs demonstrating common use cases:

• Accounts: Portfolio summary across all accounts

• Exposure: Underlying-level aggregated data

• Securities: Per-position detail

• Underlying: Drill-down by underlying symbol

• Maturity: Positions grouped by expiration

• Vol Scenario: Position risk metrics across volatility scenarios

• Vol Exposure: Underlying risk metrics across volatility scenarios

• System: Connection health and diagnostics

• Util: Helper functions and configuration

• Documentation: Quick reference

Examples

Aggregated Greeks and PnL by underlying

Detailed position view

View of positions for a single underlying

View of positions at a selected expiration

Underlying metrics across price and IV scenarios

Metrics across IV and price scenarios for a single aggregated underlying

System health and data throughput

Demo

No Visual Basic for Applications (VBA) is required. This is a pure .xlsx workbook consisting mostly of simple Real Time Data (RTD) formulas. No macros, no security warnings, no code to maintain.