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 –
- On the Edit menu in TWS, go to File.
- Select Global Configuration.
- Select API in the left pane, then click Settings
- 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”.
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.
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.
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 –
The same order status should appear in your TWS also.
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 –
- 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”
- Go to the Conditional Orders sheet and enter the details – Symbol->IBM, Type->STK, Exchange->SMART, Currency->USD
- 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.
- Now, Add “<150” to this formula. You should see the value in the cell change to “FALSE”.
- 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.
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 –
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 –
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.
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.