Interactive Brokers Quotes in Excel, Made Simple

I am often asked about linking Interactive Brokers quotes to Macroption calculators and to Excel in general. IB offers several ways to do that and although (or because) their documentation is very detailed, the task may be a bit overwhelming to a non-technical person.

Here is the simplest way you can get Interactive Brokers quotes into Excel, step by step (there are only three steps).

Step 1: Install IB API

The technology that enables your Excel (and other programs) to access Interactive Brokers data is called API – Application Programming Interface, or in this case IB Excel API.

So the first step is to download and install IB API to your computer.

Go to:

https://interactivebrokers.github.io

Agree to terms.

Choose the stable version for your operating system (Windows or Mac/Unix).

Download and install.

Note: You don't have to "run" this software every time you want to use IB data in Excel. Just having it installed does the job.

Step 2: Enable API Access in IB TWS

Next step is to enable IB API to access data from your IB TWS (Trader Workstation – Interactive Brokers trading platform).

Inside your IB TWS, go to (Global) Configuration -> API -> Settings.

Check the item "Enable ActiveX and Socket Clients".

You've just told your IB TWS that its data can be accessed by some software on your computer.

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.

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

Note: 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. For now let's stick with Simple.

Forex (and Explanation of General Format)

Let's get IB bid quote for the EUR/USD exchange rate in Excel.

The formula is:

=RTD("Tws.TwsRtdServerCtrl",,"EUR.USD/CASH","Bid")

It has four parameters:

1. "Tws.TwsRtdServerCtrl" is the same for all formulas.
2. The second is empty (notice there are two commas).
3. Symbol (in this case "EUR.USD/CASH").
4. Type of quote, named Topic in the documentation. It can be "Bid", "Ask", "Last", or many 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

Stocks

Following the same logic as forex above, the formula for IBM stock bid price is:

=RTD("Tws.TwsRtdServerCtrl",,"IBM","Bid")

Because the same stock can trade on multiple exchanges, you can also specify the exchange to only get quotes from that exchange:

=RTD("Tws.TwsRtdServerCtrl",,"IBM@NYSE","Bid")

Without the exchange specified, the symbol "IBM" is the same as "IBM@SMART".

Real-Time vs. Delayed Quotes

You may get the following error:

TwsRtdServer error: Requested market data is not subscribed. Displaying delayed market data...

This happens when you request data for a symbol for which you don't have real-time data subscription. You can still get delayed data – just replace "Bid", "Ask", or "Last" in the formula with "DelayedBid", "DelayedAsk", "DelayedLast".

Indexes

This formula gets the last value of the VIX index from the CBOE exchange:

=RTD("Tws.TwsRtdServerCtrl",,"VIX@CBOE//IND")

Same logic as stocks, but notice the extra //IND at the end of index symbol.

Futures

With futures you also need to specify expiration:

=RTD("Tws.TwsRtdServerCtrl",,"ES@GLOBEX//FUT/202009///USD","Bid")

Options

With options you also need the strike and option type (call/put):

=RTD("Tws.TwsRtdServerCtrl",,"AAPL@SMART//OPT/20200717/C/360/USD","Bid")

Quote Types

Besides bid, ask, and last, there are many different kinds of quotes you can request:

• "BidSize"
• "Bid"
• "Ask"
• "AskSize"
• "Last" (default)
• "LastSize"
• "High"
• "Low"
• "Volume"
• "Close"
• "Open"
• "LastTime"

For options also things such as:

• "BidImpliedVol"
• "BidDelta"
• "BidUndPrice"
• "AskImpliedVol"

... and much more. For full list see:

Simple Syntax > Topic

Plugging IB Data into Macroption Calculators

The typical use case is having the underlying price input updated in real time in Black-Scholes Calculator, Binomial Option Pricing Calculator, Option Strategy Payoff Calculator, or Option Strategy Simulator.

The easiest and fastest way to do that is to enter the IB data formula directly in the underlying price yellow input cell.

For example, instead of hard typed IBM stock price, enter this formula in the underlying price input cell:

=RTD("Tws.TwsRtdServerCtrl",,"IBM")

You will have underlying price updated in real time, as well as all the calculated option prices, Greeks, and P/L.

Alternatively, you can set up another input cell for symbol and refer to that cell in the IB formula. The formula in the underlying price input cell becomes:

=RTD("Tws.TwsRtdServerCtrl",,Symbol)

... where Symbol is the cell where you enter the stock symbol.

In Option Portfolio Manager the IB data formulas are already implemented and you only need to enter the symbol you want (in the correct IB symbol format as explained above).