Interactive Brokers Trader Workstation (TWS) is comprehensive enough for my investment management and research. However, for curiosity sake I decided to try out their API software to see how far it can take me. If you simply want a fuss free method, please check out my post here on Alpha Vantage Excel Add-in. Please not that I did not subscribe to Interactive Brokers market data services hence there will be limitation on the functions available on their API calls.
Allow me to digress, they do offer FREE Research and News Services on their TWS platform for users to access. However, there is a monthly fee for viewing real time quotes and premium newswires. Hence stated otherwise, the market data you are viewing are delayed quotes. Fees differ according to the stock exchange and type of user (Pro or Non- Pro). For the most of us, if you are reading this, you are most likely a Non-Pro user.
**Interactive Brokers Sign Up link here (affiliate link)**
Source: Interactive Brokers website
“Non-Professional – is any natural person and cannot be a corporation, trust, organization, institution or partnership account.”
Steps to set up API in your Excel Spreadsheet
Disclaimer: I acknowledge that there may be better methods out there and there are various programming language or protocol to utilize the IB API Software such as Java, C++, Python, .NET(C#), ActiveX and DDE. However, in this article I am going to write what I have digested so far. More information can be found on IB website as well.
Use what you are comfortable with.
Steps:
1. Install API Software from Interactive Brokers
http://interactivebrokers.github.io/
Install Directory: “C:\TWS API” for the API source code. Important to ensure that API is installed to the C: Drive
2. Download the API Reference Guide
http://interactivebrokers.github.io/tws-api/
3. Initial Configuration to enable API Connection
Edit – Global Configuration – API – Settings – Click “Enable ActiveX and Socket Clients”
Socket Port: 7496 – Production Account
7497 – Paper Account
Trusted IPs: 120.0.0.1
4. Select one of the available Excel APIs and Install it
– RTD Server –> My Personal Preference: Easy to use BUT require Market Data Subscription for API calls. This is contrary to the IB document stating that both real time and delayed data are supported. According to the help desk, only Forex is supported if you don’t subscribe to their market data. However, we can easily retrieve live Forex data with a simple search online. So I don’t see the point of using API call for this.
– Dynamic Data Exchange (DDE)
– ActiveX for Excel API
5. In this case, I am trying out RTD Server.
Go to Excel – ALT+F11 to open Visual Basic – Tools – Reference – Click “TwsRtdServer”
Now try out API calls on Excel spreadsheet.
=RTD(ProgID, Server, String1, String2, …)
where ProgID = Tws.TwsRtdServerCtrl
Server = “” (empty string)
String1, 2 … = can be Ticker, Connection Parameters etc
e.g. =RTD(“Tws.TwsRtdServerCtrl”,,”AAPL”, “Week52Hi”)
RTD Server API calls are not case sensitive
6. API calls for Delayed Market Data (doesn’t work for my case)
=RTD(“Tws.TwsRtdServerCtrl”,,”IBM@ISLAND”, “DelayedBid”)
For complete Syntax List:
http://interactivebrokers.github.io/tws-api/rtd_simple_syntax.html
