Once the TWS API has been set up, this lesson will show you how to set up Excel RTD to create your own custom watchlist.
Overview of RTD
In this lesson, we’re going to define Excel RTD, verify the TWS settings for RTD access, walk-through a sample RTD spreadsheet and look at syntax of an RTD formula.
RTD is a worksheet function provided by Microsoft that allows Excel to retrieve data from a real-time-data server. In this case, the Trader Workstation or IB Gateway is the server that sends data back to Excel.
In essence, it allows you to create your own custom watchlist to display quotes in Excel. Each cell will contain a formula that represents a specific market data column of an instrument.
If you are using RTD, you must also ensure that the TWS API was installed on the same drive as the operating system which is usually the c drive, or there may be issues.
Verify TWS API settings for RTD
First, we must verify that the TWS API settings are configured correctly as described in Lesson 1.
- To do this just open Global Configuration from the File menu and go to API > Settings and enable Enable ActiveX and Socket Clients” then review the Socket Ports.
- If you would like to connect on a different socket port or IP, you may add connection parameters to your RTD formulas. We will cover the syntax of the RTD more in depth shortly.
Here’s an example of the Host Port Client ID parameters.
Overview of Sample RTD Spreadsheet
- The sample RTD spreadsheet will be located in the TWS API folder of the API installation.
- The sample sheet primarily uses the START and END macro buttons to automatically add or remove an equal sign at the beginning of the pre-populated formulas in order to complete each request.
- In the sample, the columns of the spreadsheet such as “Volume”, are used as the “topic string” and are referenced directly in the RTD formulas to create the formulas for each column. You may do the same in your custom spreadsheet or you may write your formulas explicitly.
Syntax of an RTD Formula
- RTD formulas consist of the progID, server, and topic strings.
- As such, the first two fields in your RTD formulas will always be the same.
Formulas can be built using either a simple or complex.
- You can also mix the syntax.
- The complex syntax is most explicit.
- Here is IBKR’s “Syntax Samples” page where it’s suggested you view example formulas for the different instrument types.
Here is an example on how to specify the last price for AMZN using the complex syntax:
=RTD("tws.twsrtdserverctrl",,"sym=AMZN", "sec=STK", "exch=SMART", "cur=USD", "qt=Last")
The “qt=” topic string tells which market data column to request from TWS.
Available quote types/tick types can be found in the following page.
Tick types are different quote types which are available for different instruments supported by TWS. The data relayed back for these ticks is equivalent to the data displayed in its corresponding TWS market data column.
As can be seen here, tick types include bid, ask, last, volume, etc. Each RTD formula is able to relay back data for a single tick type.
After the formula has been entered into a cell, Excel will begin to query and update the price automatically. I’ll just hit Enter and now you’ll see the prices are updating.
Note, due to limitations of the RTD technology and Excel, there may be some delay when comparing the price values in Excel and TWS, as Excel cannot update the displayed values as quickly as the API sends them.
Now this is how to query the last price for AMZN using the simple syntax:
The sample syntax page will provide more details and the link will be included in the study notes.
Generic Tick Types
Let’s access the tick types page again you may have noticed some tick types are marked as “generic tick types”. These are additional values that may be pulled from TWS. Here you’ll see tick types for things like option historical volatility, 52 week high, Average volume, etc.
For example if you want to query the 52 Week High for AMZN specify genTicks equal 165 and qt equals Week52Hi.
Let’s enter this here and hit return to see the 52 week high value.
=RTD("tws.twsrtdserverctrl",,"sym=AMZN", "sec=STK", "exch=SMART", "cur=USD", "genticks=165", "qt=Week52Hi")
Note: Not all generic ticks are supported for all instrument types.
If you request data for an unsupported tick, you may receive error 321, Incorrect generic tick list, which will also tell you the supported generic ticks for the instrument. If you obtain this error or do not receive the expected data, you may verify the values returned in the TWS. If the TWS market data type is not listed in our documentation, it will not be returned in the API, however.
Next, let’s check out an example to obtain dividend information. Again, you may enter the formula shown here and hit Return to display the information.
=RTD("tws.twsrtdserverctrl",,"sym=AMZN", "sec=STK", "exch=SMART", "cur=USD", "genticks=456", "qt=IBDividends")
Disclosure: Interactive Brokers
The analysis in this material is provided for information only and is not and should not be construed as an offer to sell or the solicitation of an offer to buy any security. To the extent that this material discusses general market activity, industry or sector trends or other broad-based economic or political conditions, it should not be construed as research or investment advice. To the extent that it includes references to specific securities, commodities, currencies, or other instruments, those references do not constitute a recommendation by IBKR to buy, sell or hold such investments. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.
Supporting documentation for any claims and statistical information will be provided upon request.
Any stock, options or futures symbols displayed are for illustrative purposes only and are not intended to portray recommendations.
Disclosure: API Examples Discussed
Throughout the lesson, please keep in mind that the examples discussed are purely for technical demonstration purposes, and do not constitute trading advice. Also, it is important to remember that placing trades in a paper account is recommended before any live trading.