Getting Started with the TWS DDE for Excel API Webinar Notes


Purposes of the TWS API

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.

TWS API Technologies

We offer several TWS API technologies:

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.

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.

Exploring TWS Excel DDE Tutorials

The Dynamic Data Exchange (DDE) protocol is a method of inter-process communication developed by Microsoft. DDE makes it possible for TWS to communicate with other applications such as MS Excel.

One of the most common inquiries we receive at Interactive Brokers is how to export data from TWS into Excel. In the API Reference Guide, please find two basic tutorials that explain how to retrieve market data and historical data through MS Excel via the TWS DDE for Excel API. All of the VBA code included in this tutorial is kept to a minimum and is intended to be illustrative.

The sample API applications are merely demonstrations of the API capabilities aimed at experienced programmers who will in turn use them as a reference to develop more complex and robust systems.

Getting Started with Sample Spreadsheet

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.

Requesting Market Data

In the Excel API spreadsheet, you subscribe to market data on the Tickers page.

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.

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.

Placing a Basket Order

Viewing Open Orders

The Open Orders page shows you all transmitted orders.

Trade Executions

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

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.

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:

Account and Portfolio Updates

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.

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:

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.

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.

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.

For those of you who are unfamiliar with the Visual Basic Editor, there are three main areas of the Visual Basic Editor 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:


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.

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:

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