RTD Server for Excel
Introduction
TWS RTD Server API is a dynamic link library which allows user to request real-time market data from TWS via API using Microsoft Excel®. The TWS RTD Server API directly uses the C# API Client source, which connects to TWS via the socket. It allows displaying streaming live (or 15-minute delayed) market data in Excel by entering formulas into an Excel cell following a specific syntax.
[!NOTE] At the current stage, only top-level market data is supported via TWS RTD Server API. No trading capability or other data types are supported. Both Delayed and Real-Time data are supported via TWS RTD Server API. Real-time market data is required for requesting live streaming market data.
Excel API comparison
RTDServer | DDE | |
---|---|---|
Full API functionality | No | Yes, in API v975*** |
Easy to use formulas | Yes | some functionality |
Use without VBA | Yes | some functionality |
Designed to not overwhelm Excel | Yes | No |
Open Source | Yes | No |
Market Data Refresh rate | 250 ms | 250 ms |
Sample compatible with 64 bit Excel | Yes* | Yes |
What You Will Need
Windows Operating System
Since the TWS RTD Server API technology directly refers to the C# API client source functions, it is supported on Windows Environment only.
API version 9.73.03+
You need to download API Windows version 9.73.03 or higher and install on your computer. Once you have installed the API, you can verify the API Version by checking C:\TWS API\API_VersionNum.txt by default.
TWS (or LYNX Gateway) Build 963+
By default, market data requests sent via TWS RTD Server will automatically request for all possible Generic Tick Types. There are several generic tick types being requested that are only supported in TWS 963 or higher. Sending any RTD market data request with default generic tick list to an old build of TWS will trigger a "TwsRtdServer error" indicating incorrect generic tick list is sent. Make sure a TWS builds 963+ is downloaded from LYNX website and kept running at the background for TWS RTD Server API to function properly.
Enable Socket Client in TWS (or IB Gateway)
Since the TWS RTD Server API directly refers to the C# API source, RTD market data requests will be sent via the socket layer. Please make sure to Enable ActiveX and Socket Client settings in your TWS.
Please also be mindful of the socket port that you configure in your TWS API settings. The default socket port TWS will listen on is 7496 for a live session, and 7497 for a paper session. It is further discussed in section Connection Parameters that TWS RTD Server connects to port 7496 by default, and you are able to customize the port number to connect by specifying pre-defined Connection Parameters or using string "port=
Microsoft Excel®
After installing the API, the pre-compiled RTD library file (located at C:\TWS API\source\csharpclient\TwsRtdServer\bin\Release\TwsRtdServer.dll by default) registered on your computer will be in 32-bit by default for API versions from 973.03 to 973.06. If you are using 64-bit Microsoft Excel, you would need to re-compile RTD server dll file into 64-bit and register the library by re-building the RTD source solution using Visual Studio. Please refer to the TWS Excel APIs, featuring the RealTimeData Server recorded webinar for more information. Beginning in API v973.07* it is expected that the API installer for RTD Server will be compatible with both 32 bit and 64 bit Excel (* expected version number).
TWS RTD Server Formula Syntax
Customer can request market data by entering the following formula with corresponding parameters into an Excel spreadsheet cell:
=RTD(**ProgID**, **Server**, **String1**, **String2**, ...)
where
- ProgID = "Tws.TwsRtdServerCtrl"
- Server = "" (empty string)
- String1, String2, ... is a list of strings representing Ticker, Topic, Connection Parameters or other Complex Syntax strings.
[!NOTE] TWS RTD Server API formula is not case-sensitive.
There are three ways to compose an RTD Formula:
Syntax Samples
A resourceful Syntax Samples page is provided for demonstration of RTD formulas categorized by security type using different syntax.
Outgoing message rate limit
It is important to keep in mind the 50 message/second API limit applies to RTD Server in the same way as other socket-based API technologies. So the Excel spreadsheet can send no more than 50 messages/second to TWS. Each subscription or cancellation request counts as 1 message (messages in the opposite direction are not included). So a spreadsheet can have hundreds of streaming tickers, but the subscriptions must be spread out over time so that no more than 50 new subscriptions are made per second, or the spreadsheet can become disconnected.