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

  1. Allows you to automate manual activities that you would normally do in TWS.
  2. Subscribe to market data and market depth information. 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. Extract historical data and process large volumes of that kind of information.
  4. Execute algorithms and trading strategies which require automation.
  5. Automates access to account, portfolio and trade information.

TWS API Trading

The API is all about the trader building an application to his or her 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. Increase your allowance of simultaneous quotes windows by purchasing monthly Quote Booster packs.
  • 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.

TWS API Technologies

We offer several TWS API technologies:

  • DDE for Excel, which is accessed via the TwsDde.xls Excel spreadsheet. This API technology is intended for beginners.
  • ActiveX, via provided COM Components for Visual Basic, Visual C++, C# and the .NET framework.
  • The socket library, which is accessed via Visual C++, C#, POSIX and Java.
  • TWS's market data, extended order, combo order, bond and derivatives trading capabilities are fully supported.

Download and Install the API Software

Although we will focus on the DDE for Excel today, all of the API technologies have open source test clients that are included in the API installation program. Each API technology has its own sample application, including the TwsDde.xls Excel spreadsheet that we will access in today’s webinar.

To access the API software page from the IB Web site, click Trading | API Solutions | IB API button, then API Software. Click the I Agree button on the license agreement page to open the API software download page. In the Windows column, click the IB API for Windows button. This opens a File Download box, where you can decide whether to save the installation file, or open it. Choose Save and then select a place where you can easily find it.


Configure TWS to Allow the API Connection

To use the ActiveX API sample application, you first need to configure TWS to allow the type of API connection you are using.

  • Start TWS, then select Global Configuration in the File menu.
  • Select API in the left panel, then check the Enable DDE Clients check box.

To start the installation wizard, go to the place where you saved the file, double-click the filename and hit next to move through the installation.


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.

  • Open the TwsDde.xls Excel spreadsheet that was installed with the rest of the API technologies.
  • Click on the button that says Enable Macros.
  • 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.

Requesting Market Data

In the Excel API spreadsheet, you subscribe to 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.

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. 2Click the Create Ticker button in the Toolbar section of the page.
  3. 3Enter 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 at the top of the spreadsheet.

Market Depth

Market depth displays the range of prices in the market to help you gauge market liquidity.

  • 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.

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.

Placing an Order

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). To the far right, you will see institutional and extended order attribute values.

  • On a blank row, enter the information in the appropriate cells in the Contract Description section, depending on the type of instrument:
  • 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.
  • Press the Tab key to move to the next empty cell in the row, then click the Place/Modify Order button.
  • Scroll to the right to see the cells in the Order Status section get filled in automatically.

Placing a Basket Order

  • Enter the appropriate information in the Contract Description and Order information cells in multiple rows.
  • 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.
  • Click the Place/Modify Order button.
  • Scroll to the right to see the cells in the Order Status section get filled in automatically.

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.

Trade Executions

Once you have placed an order or two, you can view the completed trades on the Executions page.

  • To view execution details 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.

Extended Order Attributes

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.

Note: The Transmit value - when set to 1 (true), all placed orders are transmitted immediately. When set to 0 (false), orders are not transmitted.


The Advanced Orders Page

You place and modify orders that require the use of Extended Order Attributes on the Advanced Orders page. This page is exactly the same as the Basic Orders page; we’ve separated advanced orders from basic orders in the sample spreadsheet to make it easier for you to learn how to place more complex orders, such as Bracket, Trailing Stop Limit, Scale, Volatility and Relative orders. Each of these order types require you to use the Extended Order Attributes page.

Tip: Hover your mouse over the red arrow in the Multiplier column to view order set-up steps.


Placing a Bracket Order

To place a BUY-LMT bracket order:

  • 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.
  • 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.
  • Go to the Advanced 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.
  • 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.
  • Go to the Advanced 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.
  • Go to the Extended Order Attributes page and change the Value for Transmit back to 1 (row 13).
  • Go to the Advanced Orders page, highlight the third order and click the Place/Modify Order button.
    • The entire bracket order is transmitted.
  • Delete the value for Parent Order Id from the Extended Order Attributes page.

Account and Portfolio Updates

  • 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. Here’s a list of Account Values referenced on the Account page.

You can view your portfolio if you subscribe to Portfolio Updates on the Portfolio page.

The columns on the Portfolio page are the same as the columns displayed in the Portfolio section of the TWS Account window.

Historical Data

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.

  • API historical data requests allow you to extract the entire previous calendar year.

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, 20140714 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.

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.

Note: There are some limitations in the way TWS API handles historical data requests. Specifically, requesting the same historical data in a short period of time can cause extra load on the backend and cause pacing violations. For more information on these pacing violations, see Historical Data Limitations in the API Reference Guide.

Market Scanners

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.

The Market Scanner 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 API Online Reference 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.

Contract Details

You can 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
  • 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.

To request a contract, enter Contract Summary information and then click on the Request Contract Details button.

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.

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 2010, 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.

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 2010, click the View tab, then click the Macro button to see the list of macros used in the spreadsheet.

More Information

For more on the Excel DDE API, review the Getting Started with the Excel DDE API guide, available on our website. This guide walks you through the software setup and the common trading tasks that you can perform using the Excel DDE API sample application.

From the IB API web page, you can also access our release notes for the current production API release and the most recent Beta API release.


Direct Links


TWS API Bulletin Board

You can trade ideas and ask for help on the IB Bulletin Board, which is part of our website. To view or participate in the IB Discussion Forum, go to the Education menu and click Bulletin Boards | Launch Discussion Forums. Click TWS API thread. To participate in the discussion forum, create a userid and password in Account Management under Manage Account | Security | Voting Subscription.


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