Integrate your Excel API with Interactive Brokers’ Trading Workstation - AlgoJi
Jayantha has been selected as Campus Ambassador at AlgoJi- 2017. He is pursuing B.Tech. + M.Tech. (Dual Degree) from IIT BHU. His hobbies include maths and music.

Excel API – IB TWS Introduction

Interactive Brokers is a global leader in Automated Trading who provide access to over a 100 global electronic exchanges and more than 20 currencies. It is a direct market access broker known for its low commission rates and excellent order management.

The IB TWS uses mainly two types of software frameworks to connect to an excel API – DDE(Dynamic Data Exchange) and ActiveX. You can use these frameworks to connect any two windows applications. DDE is much simpler to code than ActiveX, but it is also older and slower. ActiveX is the more recent and advanced form of DDE.

However, both frameworks are different and have its own pros and cons, which is why IB provides support for both of them. In this article we will only explain how to use the DDE framework.

Before you get started with integrating the APIs, make sure you have downloaded the Trading Work Station (you can use free demo account). Login and keep it running. Next, download the API software and install it. Make sure you have Microsoft Excel installed on your computer.

In this article we will demonstrate using the sample API given by IB. It is actually a fully functional API on its own.

Getting Started on Excel API – IB TWS Integration

First You need to enable the API connection in the TWS. To do this follow these steps –

  1. On the Edit menu in TWS, go to File.
  2. Select Global Configuration.
  3. Select API in the left pane, then click Settings
  4. In the right pane, click the check box for Enable DDE Clients. You must have this setting enabled to connect to the API through TWS. Disable the option which says “Read Only API”.

Excel API IB TWS Settings

You will get a sample API along with the API software. You will typically find this in  –  C:\TWS API\samples\Excel\TwsDde.xls or maybe this  –  C:\TWS API X.XX\samples\Excel\TwsDde.xls , where X.XX is the version number.

Open this file and enable macros. Next, you’ll need to enter your login username on most of the sheets. Glance through them and decide which you need to use. Make sure your username is entered, otherwise, your API won’t work.

Excel API - IB TWS Integration

Create Tickers for Excel API

Next, to enter a ticker, click on the “Create Ticker” button near the top left side of the spreadsheet.

It will bring up a form. For example enter the following values – Symbol = SBIN, Type = STK, leave all other fields till Exchange blank as you only want data for stock and not derivatives, Exchange = NSE, Currency = INR. Click OK.

Now, select the row which we created and click on the button “Request Market Data”. Now, scroll right to the grey coloured fields, you will find the latest data on the stock.

Create Tickers for Excel API

Place an Order to Excel API

To place an order, copy the row of the stock which you want to buy/sell, and paste it in the sheet “Basic Orders”.

Enter whether you want to BUY/SELL in under the “Action” Column, enter the quantity, and whether it is a Market order or Limit Order. If it is a LMT order then enter the limit price also.

Now, select the row and click on “Place/Modify” button. Your Order should now get filled up. Your order status should now look like this –

Place an Order to Excel API

The same order status should appear in your TWS also.

Place an Order to Excel API

Placing Conditional Order to Excel API

Now we are beginning to enter the realms of Automated Trading.

At the time of writing this article, IBM was trading at around $153 per share. Let’s place a condition order which says – “Buy 100 shares of IBM at MKT order if the price slips below 150”

Follow these steps to achieve this –

  1. Go to the Tickers sheet, select the cell which gives the Bid price for IBM, and copy the formula which appears in the formula bar. This should look something like this – “=username|tik!id6?bid”
  2. Go to the Conditional Orders sheet and enter the details – Symbol->IBM, Type->STK, Exchange->SMART, Currency->USD
  3. Go to the Condition Statements area, which is to the right of Order Description and Order Status, and paste the copied formula under the column Statement. You should see the bid value in the cell upon entering this formula.
  4. Now, Add “<150” to this formula. You should see the value in the cell change to “FALSE”.
  5. Fill in the following details under the next columns – ADD/MOD-> ADD(because we are adding the order, not modifying it), Action-> BUY, Quantity-> 100, Order Type-> MKT.

Now, your order will get placed when the value of the cell under Statement becomes TRUE, or when the stock bid price goes below 150.

Placing Conditional Order to Excel API

Code your Algorithm for Excel API

Now, you need to write a macro which runs periodically, checks the prices of a stock/stocks, and if the conditions are met, it will place and order.

First, we’ll have to write some code which schedules a macro to get executed periodically, maybe in periods of 1 min/1 hour,etc. We can achieve this using Excel VBA’s built in function Application.Ontime. To schedule a task, you have to give it two arguments – the time at which it should execute the macro, and the name of the macro as a string.

Application.OnTime NextTime, “MacroName”

This will schedule the execution of a macro  named “MacroName” at the time NextTime. To call this periodically, you must call this method at the end of the macro.

To cancel the schedule, you have to call the method with some other parameters –

Application.OnTime NextTime, “MacroName”, , False

Here’s the complete code which schedules a macro named as “Algo” to run periodically –

 

Option Explicit
Private NextTime As Date
Private m_Interval As Date
Public Sub Enable(Interval As Date)
    Disable
    m_Interval = Interval
    StartTimer
End Sub
Private Sub StartTimer()
    NextTime = Now + m_Interval
    Application.OnTime NextTime, "Algo"
End Sub
Public Sub Disable()
    On Error Resume Next ' Ignore errors
    Dim dtZero As Date
    If NextTime <> dtZero Then
        ' Stop timer if it is running
        Application.OnTime NextTime, "Algo", , False
        NextTime = dtZero
    End If
    m_Interval = dtZero
End Sub

Now, if you want to start executing the macro named “Algo”, just call the subroutine “Enable” with the period of execution. For example, if you want to schedule the execution for every 1 min –

Enable TimeValue(“00:01:00”)

Now, let’s write the macro Algo. Suppose we want to check whether the price of Google, and if the price of its’ stock is more than 940, then we will place an order. The following is the code for the macro Algo –

Sub Algo()
    Dim lastPrice As Double
    lastPrice = Sheets("Tickers").Cells(21, 26).value ‘because google is in row 21
    If lastPrice > 940 Then
        placeOrders
    End If
    StartTimer
End Sub

Now we have to write the code for placeOrders, which will actually place an order. For this, you just have to fill up the Basic Orders sheet, and call the macro which is assigned to the Place/Modify order button. Here is the code for the macro placeOrders –

Sub placeOrders()
    Dim row As Integer
    row = 12
    ‘find an empty row
    Do While True
        If Sheets("Basic Orders").Cells(row, 1) = "" Then
            Sheets("Basic Orders").Cells(row, 1) = "GOOG"
            Sheets("Basic Orders").Cells(row, 2) = "STK"
            Sheets("Basic Orders").Cells(row, 8) = "SMART"
            Sheets("Basic Orders").Cells(row, 10) = "USD"
            Sheets("Basic Orders").Cells(row, 13) = "BUY"
            Sheets("Basic Orders").Cells(row, 14) = 100
            Sheets("Basic Orders").Cells(row, 15) = "MKT"
            Sheets("Basic Orders").rows(row).Select
            Sheets("Basic Orders").placeOrder      ‘call the placeOrder macro which is assigned to the button also
            Exit Do
        End If
        row = row + 1
    Loop
End Sub

If the condition is met you should see this in the second sheet –

Placing Conditional Order to Excel API

Since the price was greater than 940, the order got placed at market price, which is 965.37.

Similarly, if you want to cancel an order, you can just select the row of the order which needs to be canceled, and call the same macro which is assigned to the cancel order button in the second sheet.

Further Reading

The IB official documentation is the best soource to learn more about TWS Excel functionality

If you want to automate a more sophisticated algorithm, like a moving average crossover algorithm, read this tutorial for further information. You can write the same algorithm in the Algo macro, without much modification to the rest.

Free Webinar Series on Algorithmic Trading

29th May | From Concept to Automation – 9 Trading Strategies
30th May | Algorithmic Trading using Machine Learning in Python
31st May | Kickstart Guide to Algorithmic Trading
1st June | Algorithmic Trading Using Amibroker and MTv3