Option Data from IB Trader Workstation (TWS)

Overview

The RTD database contains preconfigured tables for getting real-time data from InteractiveBrokers Trader Workstation (TWS).

Option tables are designed for getting data for options. Use quote tables for stocks, futures, and currency pairs.

The data are updated from the DDE server.

You can disable unusable columns to reduce the server overhead.

The DDE server does not contain an actual data time field.
The DateTime, Date, and Time fields are updated using the PC time adjusted to the table time zone (Eastern Standard Time).

Real-Time Data Tables

Table Configurations

Microsoft SQL Server and Microsoft SQL Server Compact:

TABLE_SCHEMA	TABLE_NAME	LOAD_CODE	IS_HISTORY
`rtd`	OptionsTWS	`SELECT TickId, Code FROM rtd.QuoteListTWS WHERE Code LIKE '%_OPT_20%'`
`rtd`	OptionDayHistoryTWS	`SELECT TickId, Code FROM rtd.QuoteListTWS WHERE Code LIKE '%_OPT_20%'`
`rtd`	OptionTickHistoryTWS	`SELECT TickId, Code FROM rtd.QuoteListTWS WHERE Code LIKE '%_OPT_20%'`	1

MySQL, MariaDB, and PostgreSQL:

TABLE_SCHEMA	TABLE_NAME	LOAD_CODE	IS_HISTORY
`rtd`	options_tws	`SELECT TICK_ID, CODE FROM rtd.quote_list_tws WHERE CODE LIKE '%_OPT_20%'`
`rtd`	option_day_history_tws	`SELECT TICK_ID, CODE FROM rtd.quote_list_tws WHERE CODE LIKE '%_OPT_20%'`
`rtd`	option_tick_history_tws	`SELECT TICK_ID, CODE FROM rtd.quote_list_tws WHERE CODE LIKE '%_OPT_20%'`	1

Oracle Database, IBM DB2, and NuoDB:

TABLE_SCHEMA	TABLE_NAME	LOAD_CODE	IS_HISTORY
`RTD`	OPTIONS_TWS	`SELECT TICK_ID, CODE FROM RTD.QUOTE_LIST_TWS WHERE CODE LIKE '%_OPT_20%'`
`RTD`	OPTION_DAY_HISTORY_TWS	`SELECT TICK_ID, CODE FROM RTD.QUOTE_LIST_TWS WHERE CODE LIKE '%_OPT_20%'`
`RTD`	OPTION_TICK_HISTORY_TWS	`SELECT TICK_ID, CODE FROM RTD.QUOTE_LIST_TWS WHERE CODE LIKE '%_OPT_20%'`	1

* Click on the table name to go to the table description.

Task Table Examples

rtd.QuoteListTWS for Microsoft SQL Server and Microsoft SQL Server Compact:

TickId	Code
1	AAPL
2	GOOG
8	AAPL_OPT_20150116_500_C_100_SMART_USD_~
9	AAPL_OPT_20150116_600_C_100_SMART_USD_~

rtd.quote_list_tws for MySQL, MariaDB, Oracle Database, IBM DB2, NuoDB, and PostgreSQL:

TICK_ID	CODE
1	AAPL
2	GOOG
8	AAPL_OPT_20150116_500_C_100_SMART_USD_~
9	AAPL_OPT_20150116_600_C_100_SMART_USD_~

The task table contains tickers for quote and option tables as the TWS DDE server requires ticker registration using the TickId field.

Accordingly, option table tasks select option tickers only.

Primary Key Columns

Microsoft SQL Server and Microsoft SQL Server Compact:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	RTD_FORMULA	COMMENT
`rtd`	OptionsTWS	Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
`rtd`	OptionsTWS	TickId		`Formula`
`rtd`	OptionsTWS	Code		`PK`
`rtd`	OptionDayHistoryTWS	Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
`rtd`	OptionDayHistoryTWS	TickId		`Formula`
`rtd`	OptionDayHistoryTWS	Code		`PK`
`rtd`	OptionDayHistoryTWS	Date	`=Date()`	`PK`
`rtd`	OptionTickHistoryTWS	Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
`rtd`	OptionTickHistoryTWS	TickId		`Formula`
`rtd`	OptionTickHistoryTWS	ID		`PK, IDENTITY`

MySQL, MariaDB, and PostgreSQL:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	RTD_FORMULA	COMMENT
`rtd`	options_tws	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`rtd`	options_tws	TICK_ID		`Formula`
`rtd`	options_tws	CODE		`PK`
`rtd`	option_day_history_tws	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`rtd`	option_day_history_tws	TICK_ID		`Formula`
`rtd`	option_day_history_tws	CODE		`PK`
`rtd`	option_day_history_tws	DATE	`=Date()`	`PK`
`rtd`	option_tick_history_tws	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`rtd`	option_tick_history_tws	TICK_ID		`Formula`
`rtd`	option_tick_history_tws	ID		`PK, IDENTITY`

Oracle Database, IBM DB2, and NuoDB:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	RTD_FORMULA	COMMENT
`RTD`	OPTIONS_TWS	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`RTD`	OPTIONS_TWS	TICK_ID		`Formula`
`RTD`	OPTIONS_TWS	CODE		`PK`
`RTD`	OPTION_DAY_HISTORY_TWS	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`RTD`	OPTION_DAY_HISTORY_TWS	TICK_ID		`Formula`
`RTD`	OPTION_DAY_HISTORY_TWS	CODE		`PK`
`RTD`	OPTION_DAY_HISTORY_TWS	DATE	`=Date()`	`PK`
`RTD`	OPTION_TICK_HISTORY_TWS	REQ	`=S\|tik!'id[TICK_ID]?req?[Code]'`	`Formula`
`RTD`	OPTION_TICK_HISTORY_TWS	TICK_ID		`Formula`
`RTD`	OPTION_TICK_HISTORY_TWS	ID		`PK, IDENTITY`

Real-Time Formulas for Microsoft SQL Server and Microsoft SQL Server Compact

rtd.OptionsTWS

The table contains the last values of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
TickId		`Formula`
Code		`PK`
DateTime	`=DateTime()`
Date	`=Date()`
Time	`=Time()`
Last	`=S\|tik!id[TickId]?last`
LastSize	`=S\|tik!id[TickId]?lastSize`
Bid	`=S\|tik!id[TickId]?bid`
Ask	`=S\|tik!id[TickId]?ask`
BidSize	`=S\|tik!id[TickId]?bidSize`
AskSize	`=S\|tik!id[TickId]?askSize`
High	`=S\|tik!id[TickId]?high`
Low	`=S\|tik!id[TickId]?low`
Close	`=S\|tik!id[TickId]?close`
Volume	`=S\|tik!id[TickId]?volume`
UnderlyingPrice	`=S\|tik!id[TickId]?modelUndPrice`
ModelPrice	`=S\|tik!id[TickId]?modelPrice`
ImpliedVol	`=S\|tik!id[TickId]?modelVolatility`
BidImpliedVol	`=S\|tik!id[TickId]?bidImpliedVol`
AskImpliedVol	`=S\|tik!id[TickId]?askImpliedVol`
LastImpliedVol	`=S\|tik!id[TickId]?lastImpliedVol`
BidDelta	`=S\|tik!id[TickId]?bidDelta`
AskDelta	`=S\|tik!id[TickId]?askDelta`
LastDelta	`=S\|tik!id[TickId]?lastDelta`
Delta	`=S\|tik!id[TickId]?modelDelta`
Gamma	`=S\|tik!id[TickId]?modelGamma`
Theta	`=S\|tik!id[TickId]?modelTheta`
Vega	`=S\|tik!id[TickId]?modelVega`
pvDividend	`=S\|tik!id[TickId]?pvDividend`
LastUpdateTimeStamp

rtd.OptionDayHistoryTWS

The table contains day history of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
TickId		`Formula`
Code		`PK`
Date	`=Date()`	`PK`
Time	`=Time()`
Last	`=S\|tik!id[TickId]?last`
LastSize	`=S\|tik!id[TickId]?lastSize`
Bid	`=S\|tik!id[TickId]?bid`
Ask	`=S\|tik!id[TickId]?ask`
BidSize	`=S\|tik!id[TickId]?bidSize`
AskSize	`=S\|tik!id[TickId]?askSize`
High	`=S\|tik!id[TickId]?high`
Low	`=S\|tik!id[TickId]?low`
Close	`=S\|tik!id[TickId]?close`
Volume	`=S\|tik!id[TickId]?volume`
UnderlyingPrice	`=S\|tik!id[TickId]?modelUndPrice`
ModelPrice	`=S\|tik!id[TickId]?modelPrice`
ImpliedVol	`=S\|tik!id[TickId]?modelVolatility`
BidImpliedVol	`=S\|tik!id[TickId]?bidImpliedVol`
AskImpliedVol	`=S\|tik!id[TickId]?askImpliedVol`
LastImpliedVol	`=S\|tik!id[TickId]?lastImpliedVol`
BidDelta	`=S\|tik!id[TickId]?bidDelta`
AskDelta	`=S\|tik!id[TickId]?askDelta`
LastDelta	`=S\|tik!id[TickId]?lastDelta`
Delta	`=S\|tik!id[TickId]?modelDelta`
Gamma	`=S\|tik!id[TickId]?modelGamma`
Theta	`=S\|tik!id[TickId]?modelTheta`
Vega	`=S\|tik!id[TickId]?modelVega`
pvDividend	`=S\|tik!id[TickId]?pvDividend`
LastUpdateTimeStamp

rtd.OptionTickHistoryTWS

The table contains tick history of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
Req	`=S\|tik!'id[TickId]?req?[Code]'`	`Formula`
TickId		`Formula`
ID		`PK, IDENTITY`
Code
DateTime	`=DateTime()`
Date	`=Date()`
Time	`=Time()`
Last	`=S\|tik!id[TickId]?last`
LastSize	`=S\|tik!id[TickId]?lastSize`
Bid	`=S\|tik!id[TickId]?bid`
Ask	`=S\|tik!id[TickId]?ask`
BidSize	`=S\|tik!id[TickId]?bidSize`
AskSize	`=S\|tik!id[TickId]?askSize`
High	`=S\|tik!id[TickId]?high`
Low	`=S\|tik!id[TickId]?low`
Close	`=S\|tik!id[TickId]?close`
Volume	`=S\|tik!id[TickId]?volume`
UnderlyingPrice	`=S\|tik!id[TickId]?modelUndPrice`
ModelPrice	`=S\|tik!id[TickId]?modelPrice`
ImpliedVol	`=S\|tik!id[TickId]?modelVolatility`
BidImpliedVol	`=S\|tik!id[TickId]?bidImpliedVol`
AskImpliedVol	`=S\|tik!id[TickId]?askImpliedVol`
LastImpliedVol	`=S\|tik!id[TickId]?lastImpliedVol`
BidDelta	`=S\|tik!id[TickId]?bidDelta`
AskDelta	`=S\|tik!id[TickId]?askDelta`
LastDelta	`=S\|tik!id[TickId]?lastDelta`
Delta	`=S\|tik!id[TickId]?modelDelta`
Gamma	`=S\|tik!id[TickId]?modelGamma`
Theta	`=S\|tik!id[TickId]?modelTheta`
Vega	`=S\|tik!id[TickId]?modelVega`
pvDividend	`=S\|tik!id[TickId]?pvDividend`

Real-Time Formulas for MySQL, MariaDB, Oracle Database, IBM DB2, NuoDB, and PostgreSQL

rtd.options_tws

The table contains the last values of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
REQ	`=S\|tik!'id[TICK_ID]?req?[CODE]'`	`Formula`
TICK_ID		`Formula`
CODE		`PK`
DATETIME	`=DateTime()`
DATE	`=Date()`
TIME	`=Time()`
LAST	`=S\|tik!id[TICK_ID]?last`
LAST_SIZE	`=S\|tik!id[TICK_ID]?lastSize`
BID	`=S\|tik!id[TICK_ID]?bid`
ASK	`=S\|tik!id[TICK_ID]?ask`
BID_SIZE	`=S\|tik!id[TICK_ID]?bidSize`
ASK_SIZE	`=S\|tik!id[TICK_ID]?askSize`
HIGH	`=S\|tik!id[TICK_ID]?high`
LOW	`=S\|tik!id[TICK_ID]?low`
CLOSE	`=S\|tik!id[TICK_ID]?close`
VOLUME	`=S\|tik!id[TICK_ID]?volume`
UNDERLYING_PRICE	`=S\|tik!id[TICK_ID]?modelUndPrice`
MODEL_PRICE	`=S\|tik!id[TICK_ID]?modelPrice`
IMPLIED_VOL	`=S\|tik!id[TICK_ID]?modelVolatility`
BID_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?bidImpliedVol`
ASK_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?askImpliedVol`
LAST_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?lastImpliedVol`
BID_DELTA	`=S\|tik!id[TICK_ID]?bidDelta`
ASK_DELTA	`=S\|tik!id[TICK_ID]?askDelta`
LAST_DELTA	`=S\|tik!id[TICK_ID]?lastDelta`
DELTA	`=S\|tik!id[TICK_ID]?modelDelta`
GAMMA	`=S\|tik!id[TICK_ID]?modelGamma`
THETA	`=S\|tik!id[TICK_ID]?modelTheta`
VEGA	`=S\|tik!id[TICK_ID]?modelVega`
PV_DIVIDEND	`=S\|tik!id[TICK_ID]?pvDividend`
LAST_UPDATE_TIMESTAMP

rtd.option_day_history_tws

The table contains day history of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
REQ	`=S\|tik!'id[TICK_ID]?req?[CODE]'`	`Formula`
TICK_ID		`Formula`
CODE		`PK`
DATE	`=Date()`	`PK`
TIME	`=Time()`
LAST	`=S\|tik!id[TICK_ID]?last`
LAST_SIZE	`=S\|tik!id[TICK_ID]?lastSize`
BID	`=S\|tik!id[TICK_ID]?bid`
ASK	`=S\|tik!id[TICK_ID]?ask`
BID_SIZE	`=S\|tik!id[TICK_ID]?bidSize`
ASK_SIZE	`=S\|tik!id[TICK_ID]?askSize`
HIGH	`=S\|tik!id[TICK_ID]?high`
LOW	`=S\|tik!id[TICK_ID]?low`
CLOSE	`=S\|tik!id[TICK_ID]?close`
VOLUME	`=S\|tik!id[TICK_ID]?volume`
UNDERLYING_PRICE	`=S\|tik!id[TICK_ID]?modelUndPrice`
MODEL_PRICE	`=S\|tik!id[TICK_ID]?modelPrice`
IMPLIED_VOL	`=S\|tik!id[TICK_ID]?modelVolatility`
BID_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?bidImpliedVol`
ASK_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?askImpliedVol`
LAST_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?lastImpliedVol`
BID_DELTA	`=S\|tik!id[TICK_ID]?bidDelta`
ASK_DELTA	`=S\|tik!id[TICK_ID]?askDelta`
LAST_DELTA	`=S\|tik!id[TICK_ID]?lastDelta`
DELTA	`=S\|tik!id[TICK_ID]?modelDelta`
GAMMA	`=S\|tik!id[TICK_ID]?modelGamma`
THETA	`=S\|tik!id[TICK_ID]?modelTheta`
VEGA	`=S\|tik!id[TICK_ID]?modelVega`
PV_DIVIDEND	`=S\|tik!id[TICK_ID]?pvDividend`
LAST_UPDATE_TIMESTAMP

rtd.option_tick_history_tws

The table contains tick history of option data from InteractiveBrokers Trader Workstation (TWS).

COLUMN_NAME	RTD_FORMULA	COMMENT
REQ	`=S\|tik!'id[TICK_ID]?req?[CODE]'`	`Formula`
TICK_ID		`Formula`
ID		`PK, IDENTITY`
CODE
DATETIME	`=DateTime()`
DATE	`=Date()`
TIME	`=Time()`
LAST	`=S\|tik!id[TICK_ID]?last`
LAST_SIZE	`=S\|tik!id[TICK_ID]?lastSize`
BID	`=S\|tik!id[TICK_ID]?bid`
ASK	`=S\|tik!id[TICK_ID]?ask`
BID_SIZE	`=S\|tik!id[TICK_ID]?bidSize`
ASK_SIZE	`=S\|tik!id[TICK_ID]?askSize`
HIGH	`=S\|tik!id[TICK_ID]?high`
LOW	`=S\|tik!id[TICK_ID]?low`
CLOSE	`=S\|tik!id[TICK_ID]?close`
VOLUME	`=S\|tik!id[TICK_ID]?volume`
UNDERLYING_PRICE	`=S\|tik!id[TICK_ID]?modelUndPrice`
MODEL_PRICE	`=S\|tik!id[TICK_ID]?modelPrice`
IMPLIED_VOL	`=S\|tik!id[TICK_ID]?modelVolatility`
BID_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?bidImpliedVol`
ASK_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?askImpliedVol`
LAST_IMPLIED_VOL	`=S\|tik!id[TICK_ID]?lastImpliedVol`
BID_DELTA	`=S\|tik!id[TICK_ID]?bidDelta`
ASK_DELTA	`=S\|tik!id[TICK_ID]?askDelta`
LAST_DELTA	`=S\|tik!id[TICK_ID]?lastDelta`
DELTA	`=S\|tik!id[TICK_ID]?modelDelta`
GAMMA	`=S\|tik!id[TICK_ID]?modelGamma`
THETA	`=S\|tik!id[TICK_ID]?modelTheta`
VEGA	`=S\|tik!id[TICK_ID]?modelVega`
PV_DIVIDEND	`=S\|tik!id[TICK_ID]?pvDividend`