Interactive Brokers

Getting Started with the TWS DDE for Excel API Webinar Notes

PDF
Create a PDF of this page to save or print.

Overview

Purposes of the TWS API

We are going to start off by talking about the purposes of the TWS API:

  1. Allows you to automate manual activities that you would normally do in TWS
  2. Allows you to subscribe to market data and market depth information. A subscription in this sense is not the same as signing up and paying to receive something. Subscriptions in the Excel API refer to electing to view or extract certain data from TWS, such as requesting real-time or historical data.
  3. Allows you to extract historical data and process large volumes of that kind of information.
  4. Allows you to execute algorithms and trading strategies which require automation.
  5. Automates access to account and portfolio information.
  6. Automates access to open orders, order statuses and executions.

TWS API Trading

The API is all about the trader building an application to his own personal needs and specifications. Algorithmic trading is possible via proprietary technology built by the customer and customized to the customer’s needs and goals.

  • You can subscribe to 100 simultaneous market data tickers via the API. If you unsubscribe then subscribe to new ones, you can look at many more than just 100 tickers in a trading day. That initial 100 limit can be increased if commission volume justifies that.
  • You can place or modify orders for any order type accepted by TWS except baskets and conditional orders, which the API can do, just in different ways.
  • Execution reports and portfolio updates lets you see the composition of your portfolio and any changes to it as they occur.
  • Account and portfolio updates let you see your account’s financial status and portfolio composition as trading occurs, and lets you maintain automated books and records.
  • An important point to make about the DDE/Excel spreadsheet, as well as the test clients for the other API technologies, is that because they are open source, they provide the basis for you to build your own application.
  • The commission structure when using the API is the same as the commission structure that applies to TWS.

For more information, see the Connection Comparison on the Application Programming Interfaces page on our website.

So if you need to build an application, you’re probably already half-way there if you download the open source test client, then run it and see how it works. Choose the technology that you are comfortable working with and take a look at the test client and see how it does the things that it does.

TWS API Technologies

We offer several TWS API technologies:

  1. First we have DDE, which is accessed via the TwsDde.xls Excel spreadsheet. Please note that the focus of this API Beginners webinar is the DDE/Excel API. We focus on Excel for this presentation because:
    • Excel is a familiar interface to most users.
    • The learning curve is small and the time to development is fast.
  2. Here are the other API technologies that are available:
    • ActiveX, which is accessed using technologies such as Visual Basic, Visual C++ and the .NET framework.
    • The socket library, which is accessed via Visual C++ or .NET
    • Java, which is accessed through a provided socket client object.
  3. Another important point I’d like to make about the different ways of accessing the API is that in all these cases, TWS’s market data, extended order, combo order, bond and derivatives trading capabilities are fully supported.

Getting Started

Download and Install the API Software

Although we will focus on DDE/Excel today, all of the various API technologies have open source test clients that are included in the open source API downloader. Each API technology has its own sample application, including the TwsDde.xls Excel spreadsheet that you will use to access and test DDE API access to the TWS. You can get the downloader (InstallAX.exe) on the Interactive Brokers website by clicking Software/ Application Programming, then clicking the Proprietary API tab and finding the downloader that fits the platform you are running on.

Once you download the installer, just run it to install the API technologies on your computer.



Configure TWS to Allow the API Connection

First, start the TWS, then select Configure/API/Enable DDE Clients, or
Select Configure/Global Configuration to open the TWS Configuration dialog, then click API in the left pane to display TWS’s API settings. Select the Enable DDE clients check box.


This is the TWS Configuration dialog, and we can see here we can do things like: enable DDE clients, download your open orders when application connects, manage trusted IP addresses, etc.


Connect the Excel API to TWS

Now that the TWS has been configured to allow the connection to the DDE/Excel API application, the next step is to connect the Excel API application to the TWS.

  1. Open the TwsDde.xls Excel spreadsheet that was installed with the rest of the API technologies.
  2. Enable Macros. If you are not prompted upon opening the spreadsheet, make sure Macros are turned on (in Excel 2007, you do this by clicking the Options button next to the security warning):


  3. Finally, connect each page in the Excel API spreadsheet to the TWS by entering your TWS user name into the appropriate cell on each Excel page.

This is very important. If you forget to enter your user name on each Excel page, you won’t be able to do anything. We are now ready to start using the Excel API.

Note: For this Webinar, we are using Microsoft Excel 2007. The functionality in the Excel API spreadsheet is identical in Excel 2007 as in previous versions of Excel. However, there are a couple of minor differences in the Excel interface, but these do not affect how the DDE API works, and I will call attention to these if we encounter them in today’s presentation.

Market Data

Subscribing to market data is a very important part of using the TWS API.

  • Request market data on the Tickers page.
  • Get market data for stocks, combos, futures, options, foreign exchange and bonds.
  • Options market data includes implied volatility and delta ticks for the last trade and the NBBO (National Best Bid and Offer), as well as options model values, so that you can you use the option modeler in the TWS to setup your own volatility curves and then subscribe to those model values and model volatilities from the API.
  • An important point to remember about market data subscriptions is that you can cancel them when you no longer want to watch a particular ticker.
  • The Excel API supports IB SmartRouting, which searches for the best firm stock, option, and combination prices available at the time of your order, and seeks to immediately execute your order electronically.

Market Data in Excel – The Tickers Page

In the Excel API spreadsheet, you subscribe to market data on the Tickers page. We are currently seeing market data for a variety of stocks and options.


On the left side of the page, you see the contract definitions to which we are subscribing. We see Microsoft, Yahoo! and other stocks as well as Microsoft, IBM and Google options. Don’t forget to connect each page in the Excel API spreadsheet to the TWS by entering your TWS user name into the User Name cell. If you don’t do this, you will not get any market data. You’ll note that I have already entered my user name on the Excel pages, so I can actually get market data.


Adding a Ticker

To see more market data, you simply add more tickers. To add a ticker:

  1. Insert a blank row if necessary, then click in the Symbol cell on the row.
  2. Click the Create Ticker button in the Toolbar section of the page.
  3. Enter information about the contract description.
    • For stocks, enter the symbol, type, exchange and currency.
    • For options, enter the symbol, type, expiry, right, multiplier, exchange and currency.
    • For futures, enter the symbol, type, expiry, exchange and currency.

You can also just type the required information into the appropriate cells, select the entire row, and then click Request Market Data. You can use the Contract Search page on our website to find contract information for tickers. To access the Contract Search page, select Trading/Contract Search on our website.



Contract Details in Excel – The Contract Details Page

Let’s take a look at some of the other pages in the spreadsheet and the kinds of information you can see on them.

  • You request contract details on the Contract Details page.
  • Contract details include:
    • a list of order types for this contract
    • a list of exchanges on which a contract is traded
    • conid, which is the unique contract ID
    • minimum price tick
    • order size multiplier
    • market name
    • trading class
  • Generally, contract detail requests give your application access to the entire database of contracts that can be traded in TWS.
  • The Bond Contract Details page is similar to the Contract Details page, except it lets you get information about bond contracts from TWS. Bond contract details include such information as coupon, maturity, issue date of the bonds, credit ratings on the bonds, whether the coupons are fixed, etc.

Starting from the left side of the page, you see the contract summary descriptions, then the contract details. Note the Order Types and Exchanges columns; these list all the available order types and exchanges for that contract.

The Market Depth Page

Let’s talk about market depth. Market depth displays the range of prices in the market to help you gauge market liquidity. An obvious application is if you need to trade in a hurry, or if your application wants to place a big order, the market depth will tell you whether you will get filled at the most advantageous price.

  • The Excel API spreadsheet includes a Market Depth page which lets you subscribe to market depth.
  • Market Depth in the Excel API shows five rows of data for each underlying.
  • As market depth changes, real-time update messages are sent to the application so that you can see the depth of market in real-time as the various market makers post their bids and offers.
  • You can cancel market depth subscriptions when you no longer need to subscribe to market depth information.
  • You can get the NYSE open book market depth and Nasdaq TotalView data as long as you’ve signed up for those Market Data Subscriptions.

You subscribe to these on the Account Management page, which is accessible from TWS. You subscribe to market depth on the Market Depth page.


For market depth, all you have to do is enter the contract description information, then click in the symbol cell and click the Request Market Depth button. In the Excel API, you get a maximum of five rows of market depth.

You see that I only have a few lines of market depth here. That’s because I am not signed up for IB’s Market Data Subscriptions. If you sign up for the Total View Market Data Subscription, you will see various market makers on the bid side and offer side on the Market Depth page. Those cells will all update in real-time as market depth changes.

Orders

The Orders Page

Placing and modifying orders is another very important part of using the TWS DDE/Excel API.

  • You place orders on the Orders page. You can modify or cancel an order on the same page.
  • TWS extended order types are fully supported
  • You can request and receive open orders and executions in real-time as your trading occurs.

For a complete list of order types supported by TWS, see the Order Types page on our website.

You place and modify orders on the Orders page.


Contract descriptions are displayed on the left side of the spreadsheet while descriptions of actual orders are displayed in the center of the spreadsheet. On the right side of the spreadsheet, you can see that each order has a different status (PreSubmitted, Filled, and Submitted).


Placing an Order

To place an order:

  1. On a blank row, enter the information in the appropriate cells in the Contract Description section, depending on the type of instrument:
  2. In the Order Description section, enter the Action (Buy or Sell), Quantity, Order Type, and Limit Price if it’s a limit order or Aux Price if it’s a stop order, in the appropriate cells.
  3. Press the Tab key to move to the next empty cell in the row, then click the Place/Modify Order button.
  4. Scroll to the right to see the cells in the Order Status section get filled in automatically.

Placing a Basket Order

To place a basket order:

  1. On a blank row, enter the appropriate information in the appropriate cells in the Contract Description section.
  2. In the Order Description section, enter the Action (Buy or Sell), Quantity, Order Type, and Limit Price if it’s a limit order or Aux Price if it’s a stop order, in the appropriate cells.
  3. On the next rows, enter the appropriate information for the remaining orders in the basket.
  4. To select a group of contiguous orders, highlight the first order, hold down the Shift key, and highlight the last order. To select a group of non-contiguous orders, hold the Ctrl key down as you select each order. 5. Click the Place/Modify Order button.
  5. Scroll to the right to see the cells in the Order Status section get filled in automatically.

Extended Order Attributes in Excel

The DDE/Excel API supports extended order attributes. You can see these on the Extended Order Attributes page. The extended attributes match the extended attributes that are in the TWS.

The Extended Order Attributes page is a template. This means that any value you enter in the fields on this page will apply to ALL orders you transmit unless you specify a different attribute value for an order on the Orders page. Extended order attributes are only applied to orders if you enter a value on this page.

Starting on the left side of the page, you can see the attributes, their current values if there are any, and the descriptions of the attributes.



Placing a Bracket Order

To place a BUY-LMT bracket order:

  1. Enter the contract descriptions and order descriptions for all three orders on three contiguous rows:
    • The first order should be a BUY LMT order.
    • The second order should be a SELL STP order.
    • The third order should be a SELL LMT order.
  2. Go to the Extended Order Attributes page and change the Value for Transmit to 0 (row 13).
    • This ensures that your orders are not transmitted until you have completed the order setup
  3. Go to the Orders page, highlight the first order in the bracket order, then click the Place/Modify Order button.
    • The order is not executed, but the system generates an Order ID.
  4. Copy the Order ID for the first order, omitting the “id” prefix, then go to the Extended Order Attributes page and paste the Order ID into the Value cell for Parent Order Id (row 14). This value will be applied to all subsequent orders until you remove it from the Extended Order Attributes page.
    • The first order of the bracket order is now the primary order.
  5. Go to the Orders page, highlight the second order and click the Place/Modify Order button.
    • The order is not executed but is now associated with the primary order by means of the Parent Order Id extended order attribute.
  6. Go to the Extended Order Attributes page and change the Value for Transmit back to 1 (row 13).
  7. Go to the Orders page, highlight the third order and click the Place/Modify Order button.
    • The entire bracket order is transmitted.

Viewing Open Orders

The Open Orders page shows you all transmitted orders.

  • You can view open orders if you subscribe to them on the Open Orders page.
  • Once you subscribe to open orders, the Open Orders page is updated every time you submit a new order, either through the Excel API spreadsheet or TWS.
  • You can request a cancellation or clear your Open Order subscription at any time by clicking the appropriate button on the toolbar.


The Executions Page

Now that we’ve placed an order or two, we can take a look at the completed trades on the Executions page.

  • To view executions on this page, you must first subscribe to them. You do this by clicking the Subscribe to Executions button in the Toolbar section of the page.
  • In the Excel API, execution reporting is done on the Executions page.
  • It’s important to remember that the executions reported to your API application match the executions displayed in the TWS Trades window.


The execution details are displayed in the Execution Description section of the Executions page. To see this information, you have to click the Subscribe to Executions button. Again, “subscribe” here simply means that you’re choosing to view data from TWS.

The Executions Reporting Page You can filter your returns using the Executions Reporting page. This page is linked to the Executions page (which means you don’t need to enter your username anywhere) and can be used as long as you have subscribed to executions on the Executions page.

The Executions Reporting page can generate four different types of reports:

  • Order ID: Find the executions resulting from orders with a given Perm ID.
  • Order Ref: Find the executions resulting from orders with a given order reference (for example, executions resulting from a particular basket order).
  • VOL Order: Find the executions resulting from a given VOL order, including its hedge delta executions.
  • Strategy: Find the executions resulting from orders with a given order reference and their resulting hedge delta orders, if any. The order reference in this case is thought to be the name of a strategy.


The Conditional Orders Page

A conditional order is an order that is submitted only after some condition is met, for example a price changes or another order executes. You create conditional orders on the Conditional Orders page.


Starting on the left side of the page, you enter the contract details in the Contract Description section. You then enter the details of the order in the Order Description section and finally the condition that must be met in the Condition Statements section.

The key to setting up a conditional order in the Excel API spreadsheet is the Statement field in the Condition Statements section of this page. This field is where you enter the condition that must be met for the order to be submitted; for example, the value in one cell is equal to the value in another, or the value in one cell is larger or small than some other value, and so on. If the condition is met, the order is submitted. Until the condition is met, the Statement field displays FALSE. Once the condition is met, the Statement field displays TRUE.


Placing a Conditional Order

This procedure describes how to set up a simple conditional order to buy an option only if the price of the underlying stock is less than a price that you specify.

  1. Enter the contract description of the buy order on a blank row.
    • For example, enter IBM, OPT, 200810, 120, P, 100, SMART, and USD for symbol, type, expiry, strike, p/c, multiplier exchange and currency.)
  2. Enter the order details on the same line.
    • For example, enter BUY, 1, and LMT for Action, Quantity and Order Type). Enter your desired price in the Lmt Price cell.
  3. Enter the condition in the statement field. Remember to enter the condition as an Excel formula.
    • The condition calls for the buy order to transmit as soon as the ask price of the underlying is below a certain number. Check the Ask Price of the underlying on the Tickers page.
    • Copy the contents of the Ask Price cell for the underlying stock on the Tickers page, then paste it into the STATEMENT cell for the conditional order on the Conditional Orders page.

      The contents of the Ask Price cell for the underlying will look something like this:

      =stdnt311|tik!id11?ask

    • Modify the formula to add the “less than X” condition, where X is the price.

      For example, if you want to buy an IBM option only if the price of the underlying falls to below 120, then modify the formula so it looks like this:

      =(stdnt311|tik!id11?ask<120)

    • Notice that this field now displays FALSE. This means that the condition has not been met; in other words, the ask price of the underlying has not dropped to below 120.
  4. Enter the rest of the information for the condition:
    • Type ADD in the ADD/MOD field (this is an additional order, not a modification to an existing order).
    • Using the IBM example, type BUY in the Action field.
    • Using the IBM example, type 1 in the Quantity field.
    • Using the IBM example, type MKT in the Order Type field.
  5. Click in any blank cell in the buy order row, then click the Place/Modify Order button to place the order. When the condition is met, the trade will execute and the STATEMENT cell changes to TRUE. If the order executes properly, the Order Status section of the page gets filled in. You can check the Executions page to see the trade displayed there, if you have subscribed to Executions on that page.

Account and Portfolio Updates

Account Updates in Excel – The Account Page

  • You can see detailed information about your account if you subscribe to Account Updates on the Account page.
  • You can cancel your Account Update subscription at any time by clicking the Cancel Account Subscription button.
  • You can also clear all the account data from the page by clicking the Clear Account Data button.

Once you subscribe to Account Updates in the Excel API spreadsheet, the Account page displays a variety of details about your account, including various financial values, available funds, and more. Complete descriptions of all of these fields are included in Appendix B in the Excel API for Beginners Guide, available on the API page on our website.

Portfolio Updates in Excel

  • The Portfolio Page You can view your portfolio if you subscribe to Portfolio Updates on the Portfolio page.
  • The columns shown here are the same as the columns displayed in the Portfolio section of the TWS Account window.

Historical Data

The Historical Data Page

The DDE/Excel API supports historical data requests.

  • You access the same historical data in the DDE/Excel API that is used in TWS charting.
  • You request and view historical data on the Historical Data page.
  • The contracts for which historical data are available are the same ones that are covered in TWS, and the results come back to your application as rows of data in on a separate page in the spreadsheet
  • API historical data requests allow you to extract the entire previous calendar year.

Use the Historical Data page to request historical data for an instrument based on data you enter in a query. This information is the same as charting a contract in TWS, except that the information is presented to you in rows on a spreadsheet.

The TwsDde.xls spreadsheet includes some sample historical data queries.



Requesting Historical Data

To enter an historical data query, fill in the following fields in the Query Specification section, then click in any blank cell in that row and click the Request Historical Data button:

  • End Date/Time in the format: yyyymmdd{space}HH:mm:ss{space}{TMZ} For example, 20080430 18:25:18 GMT.
  • Duration (X seconds, minutes, days, etc) For example, 1 W, 1 Y, 1 M, 300 S, 1 D.
  • Bar Size: Enter the integer value that represents the desired bar size:
Bar Size String Integer Value
1 SEC 1
5 SEC 2
15 SEC 3
30 SEC 4
1 MIN 5
2 MIN 6
3 MIN 16
5 MIN 7
15 MIN 8
30 MIN 9
1 HOUR 10
1 DAY 11
1 WEEK 12
1 MONTH 13
3 MONTHS 14
3 YEAR 15
  • What to Show (MIDPOINT, TRADES, BID, ASK, or BID_ASK)
  • RTH Only: set to zero (0) to get data outside of regular trading hours
  • Date Format Style: Set this to 1 to apply dates to bars in the format “yyyymmdd{space}{space}hh:mm:dd” (the same format already used when reporting executions).
    Set this to 2 to return the dates as an integer specifying the number of seconds since 1/1/1970 GMT.
  • Page Name: the title of the results page that will be created.

You can find a complete list of the query fields with descriptions in the Excel API for Beginners Guide.

Excel shows the results of your query on a separate page in the spreadsheet created specifically for these results. When you define the query parameters, you can include a name for the results page in the Page Name field.


Historical Data Results


Market Scanners

The Market Scanner Page

Use the Market Scanner page to subscribe to market scanners in the Excel API spreadsheet. The TwsDde.xls spreadsheet included in the API comes with several market scanners set up and ready to use.

  • You access the same data in the DDE/Excel API that is used in the TWS Market Scanner.
  • The results of each market scanner subscription are displayed on their own page in the spreadsheet.
  • API Scanner subscriptions update every 30 seconds, just as they do in TWS. In fact if you run the same scan, you will see the same data returned.

This page lists available market scans and includes the elements of each scan, which are the same kind of parameters that the TWS allows you to select. If you run the same scan in the spreadsheet as you run on the TWS, you will get the same results. For a complete list of elements that make up a market scan, see the Excel API for Beginners Guide.

The Activate Page column tells the spreadsheet whether or not to display the scan results page on top of the Excel window. If you set this to TRUE, the results page for each market scanner subscription will display on top of your window every time it updates.


Looking at the Code

The real power of the Excel API is the Visual Basic code behind the scenes. You will definitely gain a better understanding of the Excel API spreadsheet by looking at the code.

To view the code, press Alt+F11 From any page in the spreadsheet. The Visual Basic Editor opens.


The purpose of this Webinar is not to teach you how to write Visual Basic code. However, you will gain a better understanding of the Excel API spreadsheet if you spend a little time looking at the code. So let’s look at some code by pressing Alt+F11. For those of you who are unfamiliar with the Visual Basic Editor, there are three main areas of the Visual Basic Editor window:

  • Project Explorer
  • Properties Window
  • Code Window

You can see the Microsoft Excel Objects in the Project Window that correspond to the pages in the spreadsheet. Double-click any of them to display the code for that page. There are also additional forms and code modules used by the rest of the code that you can see if you scroll down in the Project Window.

Named Ranges

Named ranges are meaningful names that you can assign to a single cell or a range of cells in Microsoft Excel. The TwsDde.xls API spreadsheet uses named ranges throughout the VB code, and a good way to get a feel for how the code works is to find the named ranges and see where they are used in different code modules.

To find the named ranges used in spreadsheet:

  • In Excel 2007, you can see a complete list of all named ranges used in the spreadsheet by clicking Formulas/Name Manager. The Name Manager displays every named range used in the spreadsheet, the value of the range, and the page and range of cells covered by the range. As you can see, this can be very useful in learning how our spreadsheet uses values from different pages in the VB code.
  • In earlier versions of Excel, you select Insert/Name/Define to view the named ranges in the spreadsheet or to create new ones. You can also download a free Name Manager from Microsoft that gives you additional functionality.


Macros

The other Microsoft Excel feature used throughout the TwsDde.xls spreadsheet is the macro. Every button on every page in the spreadsheet has a macro associated with it. You can see the macros used in the spreadsheet by viewing the list of macros, then clicking Edit to open the macro in the Visual Basic Editor to look at the actual code.

  • In Excel 2007, click the View tab, then click the Macro button to see the list of macros used in the spreadsheet.
  • In earlier versions of Excel, select Tools/Macro/Macros.

More Information

For more on the DDE for Excel API, review the Getting Started with the TWS DDE for Excel API Guide, available on our website. This guide walks you through the software setup, the various pages within the spreadsheet and even some simple VBA programming example to help you customize your application. The guide is located on Proprietary API tab on the IB website under Software/Application Programming.


Contact Our API Support Team

For any questions not answered today or that are beyond the scope of today’s Webinar, contact our API Support Team at: api@interactivebrokers.com

Options and Futures are not suitable for all investors. The amount you may lose may be greater than your initial investment. Before trading options read the “Characteristics and Risks of Standardized Options”. Before trading futures, please read the CFTC Risk Disclosure. Security futures involve a high degree of risk and are not suitable for all investors. The amount you may lose may be greater than your initial investment. Before trading security futures, please read the Security Futures Risk Disclosure Statement. For a copy any of these disclosures, call (203) 618-5800. Any ticker symbols displayed are for illustrative purposes and do not portray recommendations.

Any symbols displayed are for illustrative purposes only and do not portray a recommendation.

Interactive Brokers LLC is a member of NYSE, FINRA, SIPC