DDE Formula Reference
Real Time Data
Attribute | Description |
---|---|
twsuser | The user name with which you logged into the TWS |
requestId | The request unique identifier (any positive integer) |
symbol | The contract symbol |
sectype | The kind of contract |
exchange | The exchange from which we want to pull the data from |
currency | The contract currency |
Initial Request
=S[twsuser]|tik!'id[reqestId]?req?[symbol]_[sectype]_[exchange]_[currency]_~/'
Data Reception
=S[twsuser]|tik!id[requestId]?bidSize
=S[twsuser]|tik!id[requestId]?bid
=S[twsuser]|tik!id[requestId]?ask
=S[twsuser]|tik!id[requestId]?askSize
=S[twsuser]|tik!id[requestId]?last
=S[twsuser]|tik!id[requestId]?lastSize
=S[twsuser]|tik!id[requestId]?high
=S[twsuser]|tik!id[requestId]?low
=S[twsuser]|tik!id[requestId]?volume
=S[twsuser]|tik!id[requestId]?close
=S[twsuser]|tik!id[requestId]?bidImpliedVol
Option-specific Ticks
=S[twsuser]|tik!id[requestId]?bidDelta
=S[twsuser]|tik!id[requestId]?askImpliedVol
=S[twsuser]|tik!id[requestId]?askDelta
=S[twsuser]|tik!id[requestId]?lastImpliedVol
=S[twsuser]|tik!id[requestId]?lastDelta
=S[twsuser]|tik!id[requestId]?modelVolatility
=S[twsuser]|tik!id[requestId]?modelDelta
=S[twsuser]|tik!id[requestId]?modelPrice
=S[twsuser]|tik!id[requestId]?pvDividend
=S[twsuser]|tik!id[requestId]?modelGamma
=S[twsuser]|tik!id[requestId]?modelVega
=S[twsuser]|tik!id[requestId]?modelTheta
=S[twsuser]|tik!id[requestId]?modelUndPrice
Historical Data
In the example Receiving the data, you pulled midpoint historical data for EUR.USD from TWS.
Most of the formula’s components are self-explanatory with the exception of duration and bar sizes, which require very specific codes as listed below:
Duration
Time Unit | Formula Abbreviation |
---|---|
Seconds | S |
Day | D |
Week | W |
Month | M |
Year | Y |
Bar Sizes
Bar Size | Formula Parameter |
---|---|
1 second | 1 |
5 seconds | 2 |
15 seconds | 3 |
30 seconds | 4 |
1 minute | 5 |
2 minutes | 6 |
5 minutes | 7 |
15 minutes | 8 |
30 minutes | 9 |
1 hour | 10 |
1 day | 11 |
For available whatToShow parameters and detailed descriptions, please refer to the Historical Data Types.
Please also be mindful of the Historical Data Limitations.
Contract Definitions
The TWS DDE for Excel API lets you retrieve data for any instrument available in TWS. So far we have been using the simplest instrument of all: CASH. Using slight variations of the same formula, you can define any security type available in TWS.
How to Find the Definition of a Contract
The best way of finding a contract’s description is within the TWS itself. Within the TWS, you can easily check a contract’s description either by double clicking it or through the Contract Info -> Description menu, which you access by right-clicking a contract in TWS:
The description will then appear:
Formulas for Different Security Types
[!NOTE] There are two different request types, i.e. "req1" and "req2", that differentiate in defining contracts with underlying Symbol (req1) or Local Symbol (req2) of the contract. While either request type can be used, the Local Symbol is most common for FUT, OPT and FOP.
Please find available contract parameters and sample contracts for each security type below:
FX Pairs
Formula
=S[twsuser]|tik!'id[reqId]?req?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id1?req?EUR_CASH_IDEALPRO_USD_~/'
STK
Formula
=S[twsuser]|tik!'id[reqId]?req?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id2?req?IBKR_STK_SMART_USD_~/'
FUT
FUT using the contract’s local symbol
Formula
=S[twsuser]|tik!'id[reqId]?req2?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id3?req2?ESZ6_FUT_GLOBEX_USD_~/'
FUT using underlying’s symbol, multiplier and expiration date
Formula
=S[twsuser]|tik!'id[reqId]?req?[underlying_symbol]_[SecType]_[expiry]_[multiplier]_[exchange]_[currency]_~_~/'
Example
=Ssample123|tik!'id3?req?ES_FUT_201612_50_GLOBEX_USD_~_~/'
OPT
OPT using the contract’s local symbol
Formula
=S[twsuser]|tik!'id[reqId]?req2?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id4?req2?C DBK DEC 16 1300_OPT_DTB_EUR_~/'
[!NOTE] The format of the option local symbol conforms to the Option Symbology Initiative (OSI).
OPT using underlying’s symbol, multiplier and expiration date
Formula
=S[twsuser]|tik!'id[reqId]?req?[underlying_symbol]_[SecType]_[expiry]_[strike]_[P/C]_[multiplier]_[exchange]_[currency]_~_~/'
Example
=Ssample123|tik!'id4?req?DBK_OPT_20161216_13_C_100_DTB_EUR_~_~/'
FOP
FOP using the contract’s local symbol
Formula
=S[twsuser]|tik!'id[reqId]?req2?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id5?req2?XTZ6 C1100_FOP_GLOBEX_USD_~/'
FOP using underlying’s symbol, multiplier and expiration date
Formula
=S[twsuser]|tik!'id[reqId]?req?[underlying_symbol]_[SecType]_[expiry]_[strike]_[P/C]_[multiplier]_[exchange]_[currency]_~_[tradingClass]/'
Example
=Ssample123|tik!'id5?req?EUR_FOP_20161209_1.1_C_125000_GLOBEX_USD_~_XT/'
IND
Formula
=S[twsuser]|tik!'id[reqId]?req?[symbol]_[SecType]_[exchange]_[currency]_~/'
Example
=Ssample123|tik!'id6?req?ES_IND_GLOBEX_USD_~/'
BAG
Formula
=S[twsuser]|tik!'id[reqId]?req?[symbol]_[SecType]_[exchange]_[currency]_CMBLGS_[num of legs]_[legId]_[legQuantity]_[legAction]_[legExchange]_[legPrice]...CMBLGS_~/'
Example
=Ssample123|tik!'id7?req?SPY_BAG_SMART_USD_CMBLGS_2_141149249_1_BUY_SMART_0_141149252_1_SELL_SMART_0_CMBLGS_~/'