Step 1: Install IB API - it's a one time affair. You don't have to touch it later until a new version is available.
Agree to terms.
Choose the stable version for your operating system (Windows or Mac/Unix).
Download and install.
Step 2: Enable API Access in IB TWS - Trader Workstation - trading platform.
Go to global configuration - API - Setting and check "Enable Activex and Socket Clients"
Step 3: IB Quotes Excel Formulas
The formulas are quite simple. I list a few examples below, but it is best to see official documentation for more examples and full list of parameters:
RTD Server for Excel > Simple Syntax
RTD Server for Excel > TWS RTD Server Samples
There are three types of syntax for these formulas: Simple, Complex, and Mixed. They differ in the way individual inputs (details like symbol, exchange, or currency) are arranged in the formula.
That’s it for the setup. You can now open Excel, write a formula into a cell and if all goes well you will see quotes from IB updated in real time.
Syntax for Simple style:
Forex (and Explanation of General Format)
Let’s get IB bid quote for the EUR/USD exchange rate in Excel.
The formula is:
It has four parameters:
- "Tws.TwsRtdServerCtrl" is the same for all formulas.
- The second is empty (notice there are two commas).
- Symbol (in this case "EUR.USD/CASH").
- Type of quote, named Topic in the documentation. It can be "Bid", "Ask", "Last", or some other things. If omitted, it defaults to "Last".
Once you enter the formula in an Excel cell and hit Enter, you should see the EUR/USD rate updating in real time.
If something goes wrong, you will see an error message instead of the quote.
For example, if you try to get a quote for an invalid symbol like "ABC.USD/CASH", you will see this text in the Excel cell:
TwsRtdServer error: No security definition has been found for the request,
With futures you also need to specify expiration:
=RTD("Tws.TwsRtdServerCtrl",,"[email protected]//FUT/202009///USD", "Bid")
With options you also need the strike and call/put:
=RTD("Tws.TwsRtdServerCtrl",,"[email protected]//OPT/20200717/C/360/USD", "Bid")
Besides bid, ask, and last, there are many different kinds of quotes you can request:
- “Last” (default)
For options also things such as:
… and much more