Excel RTD

The Interactive Brokers Excel RTD offering is a .NET offering for IBKR Pro customers. This is a unique API implementation designed to efficiently retrieve live market data through Excel. This is optimized for ease of use and efficiency without overwhelming Excel. As such, this is the optimal choice for customers who wish to collect large quantities of data without prior coding experience, or interest in algorithmically-placed orders.

By design, Microsoft Excel gives precedence to the user interface over the data connection to other applications. For that reason, Excel only receives updates when it is in a ‘ready’ state, and may ignore data sent for instance when a modal dialogue box is displayed to the user, a cell is being edited, or Excel is busy doing other things. The Excel Real Time Data server (RTD) API has been introduced to help address some of these limitations, but they are inherent to Excel as a trading application and not specific to an API technology.

Please keep in mind that the Excel RTD offering can only retrieve streaming market data. There is no other established utility for this offering.

Sample spreadsheet applications are distributed with the API download for each of the API technologies (RTD Server, ActiveX, DDE). It is important to keep in mind that the sample applications are intended as simple demonstrations of API functionality for third party programmers. They do not have robust error handling functionality and are not intended to be used as production level trading tools.

Since the TWS RTD Server API technology directly refers to the C# API client source functions, it is supported on Windows Environment only.

In many cases, customers might see a response for a given market data column which displays “N/A”, “0”, “-1” in some of their cells. This can be occur for a few different reasons.

1. While you may have part of the required market data subscription on the instrument, you may not have all of it. This is particularly common for derivatives traders who may not have a market data subscription for the given underlying.
2. You might also encounter this by requesting too many symbols simultaneously. This is discussed further in the Outgoing Message Rate Limitations section
3. Similar to (2) above, every client has a maximum number of market data lines shared between TWS and their API. By default, all customer’s have a maximum of 100 market data lines, allowing for 100 unique contracts to be requested at any one time. If you surpass this limit, you may find that some fields can not be returned.
4. This may also happen in the event your machine, in tandem with Excel, simply can not handle the volume of data requested. While the machine limitations are unique to each user, you may explore and modify the refresh rate as described in our documentation

Clients who may have installed the API into a unique location may receive an #NA message in each RTD cell. While this is often indicative of a reference error, the #NA reference may be failing to call an installed DLL essential for the connection with Trader Workstation. To resolve this error, please follow the steps listed below:

1. Uninstall the API from the “Add/Remove Tool” in the Windows Control Panel as usual
2. Delete the C:\TWS API\ folder if any files are still remaining to prevent a version mismatch.
3. Locate the file “C:\Windows\SysWOW64\TwsSocketClient.dll”. Delete this file.

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.

1. 1. Log in to the Trader Workstation or IB Gateway
   2. Open the Global Configuration by selecting the Cog Wheel icon in the top right corner
      
   3. On the left, select API and then Settings
   4. At a minimum, you will need to maintain these settings:
      • “Enable ActiveX and Socket Clients” must be checked
      • “Read-only API” should be unchecked
      • Unless you are interested in modifying each request or the base code, we would advise setting your Socket port to 7496
   5. This will let you get started; however, there are some option settings we would encourage you to enable:
      • “Create API message log file” should be checked
      • “Include market data in API log file” should be checked
      • “Logging Level” should be changed from “Error” to “Detail”

   

Microsoft RTD interface has a ThrottleInterval property that determines the interval between data refreshes. By default, the value is set to 2000 milliseconds, which means Excel waits at least 2000 milliseconds between checks for updates. You are able to manually change the Throttle Interval to a smaller value* so as to increase the refresh rate of real time data.

The easiest way to change the ThrottleInterval property is through VBA:

1. In Excel, go to the Visual Basic Editor window by pressing Alt_F11.
2. On the Visual Basic Editor window, click on View -> Immediate Window or hold Ctrl_G to open the Immediate Window.
3. On the Immediate Window, type in the following code and then click Enter:
   Application.RTD.ThrottleInterval=250
4. To verify that it is set correctly, type this line of code on the Immediate Window and click Enter:? Application.RTD.ThrottleInterval

5. Verify the next line should display 250. If this value is changed, the new value will persist when Microsoft Excel is restarted.

*Warning: As the ThrottleInterval is lowered, updates can come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything, effectively getting in a hung state. If this happens, set the Excel throttle interval higher.

Source: Microsoft Real-Time Data: Frequently Asked Questions How Do I Configure the RTD Throttle Interval in Excel?.

Since the TWS RTD Server API directly refers to the C# API Client, so it connects to TWS (or IB Gateway) the same as C# via the socket. The Host IP Address, Socket Port and Client ID are required parameters for initiating a socket connection.

• The Host IP Address is the IP address where your TWS is running on. For a local connection, local IP 127.0.0.1 can be used.
• The Socket Port is the port for socket connection. You can setup the host port in TWS API Settings, and you need to have your API connect to the same port as you setup in TWS.
• The Client ID is an identification for each API connection. TWS can maintain up to 32 API Clients connecting at the same time, and the Client ID is used to distinguish each connection. This was originally designed so that API users can have multiple API programs (i.e. clients) running at the same using different strategies to trade separately. Since the TWS RTD Server API is only provided for relaying real-time data, there is no need to use multiple client IDs.

The above three parameters are defaulted to the following values if not directly specified by the user:

• Host = “127.0.0.1” (i.e. the “localhost”)
• Port = “7496”
• ClientID = Integer.MaxValue – 1

Simple Syntax supports several pre-defined Connection Parameters that can be specified as a separate string (i.e. String2, String3…) in the RTD formula:

• “paper”: use port=7497 for connection instead (7497 is the default port for paper TWS sessions)
• “gw”: use port=4001 for connection instead (4001 is the default port for live IB Gateway sessions)
• “gwpaper”: use port=4002 for connection instead (4002 is the default port for paper IB Gateway sessions)

For example, to request High price for SPY@SMART while connecting to a TWS logged with a paper account via port 7497:

=RTD("Tws.TwsRtdServerCtrl",,"SPY@ARCA", "High", "paper")

The ticker is a means of defining a contract for a given market data request. This will incorporate the symbol, security type, exchange, currency, and potentially more in some shape.

This can be achieved in a single string, known as Simple Syntax, or through multiple definitions, known as Complex Syntax. While both are of equal value, some security types benefit from one structure over another.

Notes:

1. Not all contract attributes are required to be specified. You can leave the field to be blank to make that field un-specified. Sequentially, if you only need to specify several contract attributes at the begining part of the Ticker string, you can leave out the rest of the string entirely as well. For example, instead of specifying “SPY@SMART//////”, “SPY@SMART” would be sufficient to define the contract properly.
2. There are several default contract attributes in the Ticker string. If you leave them un-specified, they will take the default values as following:
   • EXCHANGE = “SMART”
   • SECTYPE = “STK”
   • CURRENCY = “USD”

For example, Ticker = “SPY” is the same as “SPY@SMART//STK////USD”.

Complex Syntax Strings

The Topic specifies the tick types returned values after requesting market data. Each cell in Excel will correspond to a given Topic string to represent the given dataset. If no Topic is specified,  “Last” value will be returned by default.

Note: If you do not have the corresponding Market Data Subscription, ‘0’ will be displayed if you request for live tick types above. Please refer to Delayed Tick Types if you are interested in receiving 15-minute delayed data.

Standard tick types returned to the user upon requesting data by default.

A selection of Generic Tick Types are also supported in TWS RTD Server API. To request for any Generic Tick Type, you just need to specify the name of the generic tick type as the Topic string in the RTD formula.

When live streaming market data is not available because of missing Market Data Subscription, delayed data will be automatically relayed back. To request delayed data via RTD, you need to specify delayed tick types for the Topic. The table below shows a full list of available delayed tick types:

Note: Delayed tick types are 15-minute delayed. Requesting for live tick types without market data subscription will result in error message “Requested market data is not subscribed. Displaying delayed market data…”

This page is provided as a demonstration of RTD formulas categorized by security type as well as syntax type. This will help to provide context on how a unique contract type should be formatted for either Simple or Complex syntax structures. The generic structure of a Contract is as follows:

Symbol@Exchange/PrimaryExchange/SecType/Expiration/Right/Strike/Currency

Note: TradingClass, Multiplier, and LocalSymbol can only be specified through Complex Syntax.

Spread contracts, also known as combos or combinations, combine two or more instruments. To define a combination contract it is required to know the Contract ID of the combo legs. The ConId can be easily found in the Contract Description page in TWS. The spread contract’s symbol can be either the symbol of one of the contract legs or, for two-legged combinations the symbols of both legs separated by a comma as shown in the examples below.

Simple Syntax is not sufficient to define spread contracts. You need to use either Complex Syntax . As a reminder, here is the string formula for defining the Combo Legs:

“cmb=<conid1>#<ratio1>#<action1>#<exchange1>;<conid2>#<ratio2>#<action2>#<exchange2>;”

Note: Only Index CFD data can be directly queried via the API, but not equity CFD. Please directly request data for the underlying equity if you need data for an equity CFD contract.

Important notes for Stocks and SMART routing

• Specifying the Exchange as opposed to using PrimaryExchange means requesting data from that exchange specifically.
• Stock symbols that contain a ‘.’ are supported in both Simple Syntax Complex Syntax.
• For certain smart-routed stock contracts that have the same Symbol, Currency and Exchange, you would also need to specify the PrimaryExchange attribute to uniquely define the contract. This should be defined as the native exchange of a contract, and is good practice for all stocks.

Some customers may find that as opposed to generic ticks or live streaming data, they may want to remove or reduce the number of ticks. It is even possible to change RTD to report snapshot data instead. This section will document the process to change these underlying values requested.

Keep in mind, RTD is based on underlying C# code. As such, a working knowledge of C# is required. These modifications will also require a C# compiler and interpreter are installed on your windows machine.

This guide is intended to introduce new customers to the RTD Excel Sample Sheet and introduce the systems available of our underlying API.

Sample spreadsheet applications are distributed with the API download for each of the API technologies (RTD Server, ActiveX, DDE). It is important to keep in mind that the sample applications are intended as simple demonstrations of API functionality for third party programmers. They do not have robust error handling functionality and are not intended to be used as production level trading tools.

To request a specific Tick type, you can utilize the ‘qt’ parameter in the complex syntax by specifying it (for example, “qt=Bid”) or simply enter its name at the end of a simple syntax formula (for example, “AAPL@SMART BidSize”). Explore the various available tick types in our Generic Tick Types section.
*It is important to note that each cell can only return one tick type (one quote).

As stated within the Architecture section, the Excel RTD API is built on the underlying Java API with standard Visual Basic Translations to allocate the data onto the Excel sheets. For additional insight into the underlying methods, see the TWS API Documentation.

Interactive Brokers always welcomes users to contact customer support in the event of API behavior issues. However, to help expedite the troubleshooting process for customers, it is encouraged to first consider the following:

• Does this issue persist in other platforms? Am I receiving errors with an order even when performing the same action in Trader Workstation rather than through the API?
• Did the request match the Required parameters from the documentation?
• Were documentation or guides consulted on the matter prior?
• How long has this issue been going on? Is the error on the first attempt, or has this request been working for days prior?
• Do other, similar requests work? Perhaps portfolio data is returned, but not trading information.