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:
=RTD("tws.twsrtdserverctrl",," AMZN@SMART")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")
How can I plot selected Options Greeks onto the Advanced Charts in IRB Trader Workstation?
Hello, thank you for reaching out. At this time, it is not possible to plot options Greeks on Advanced Charts. We have passed your feedback to the appropriate team. You can also view this FAQ for instructions to submit any specific suggestion(s) on how we can improve: https://www.ibkr.com/faq?id=32653353
Also, you can also post in the Feature Poll in Client Portal where other IBKR users can comment and vote:
https://portal.interactivebrokers.com/portal/#/suggestions
Please reach back out with any additional questions. We are here to help!
I try to copy paste syntax above in my excel 2010. but no one is working. there is error: The formula you type contains an error…. please help
Thank you for reaching out. Please create a web ticket for this inquiry; we have a category specifically for “API.” One of our API experts will be happy to guide you! https://spr.ly/IBKR_TicketCampus
Is it possible to access the indicator values that TWS is calculating on a particular chart. My Use Case is that I am monitoring the ES emini contract. I am able to get it’s information via RTD into excel. On my chart in TWS desktop I have applied a volatility stop. I am wanting the volatility stop value as calculated by TWS to be shared to my excel file … is that doable ? Also, where do i find the algorithm for that volatility stop (in the trend studies) – that way I can build it outside TWS if necessary. Thanks
Thank you for reaching out. Indicator values are not available through the API. Interactive Brokers does not provide our formula calculations. If you have any additional questions, please create a web ticket for this inquiry. We have a category specifically for “API.” One of our API experts will be happy to guide you. http://spr.ly/IBKR_ClientServicesCampus
Please, what is wrong in thsi formula: =RTD(“tws.twsrtdserverctrl”;;”sym=SI”; “sec=FUT”; “exch=CMX”; “cur=USD”; “exp=202505”; “qt=last”) Thank you very much.
Hello, thank you for reaching out. Please try modifying your exchange to “COMEX” and test the behavior. If the issue persists, please create a ticket with our customer service team. You can find the steps to create a ticket here: https://www.ibkrguides.com/complianceportal/creatingaticket.htm.
What is wrong with this formula for spot gold price: =RTD(“tws.twsrtdserverctrl”,,”sym=XAUUSD”, “sec=CASH”, “exch=IDEALPRO”, “cur=USD”, “qt=Last”). It returns “wsRtdServer error: No security definition has been found for the request”.
Hello Ronnie, thank you for reaching out. Taking a look at the contract, it seems we may be conflating the XAUUSD commodities contract with a Forex Pair. If we were to search XAUUSD in TWS, we can see that the XAUUSD contract is held on the “SMART” exchange and is a CMDTY security type. If you have any additional questions, please create a web ticket for this inquiry; we have a category specifically for “API.” One of our API experts will be happy to guide you! http://spr.ly/IBKR_ClientServicesCampus
We hope this helps!