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.

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.

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

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:

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

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

• API Reference Guide
• API Message Codes
• Getting Started with the TWS DDE for Excel API
• Getting Started with the TWS DDE for Excel API for Advisors
• DDE for Excel Tutorials – Requesting Real-Time Market Data
• DDE for Excel Tutorials – Requesting Historical Data

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