API Release Notes for build 9.0

The enhancements and modifications below are in build 9.0 of the TWS API. For clarification on any of the items listed, refer to the appropriate section in the Users Guide, or send us an email at Beta Support.

Excel/DDE Subscriptions for Open Orders, Executions, Account and Portfolio Updates

TWS Version 861 and API Version 8.90 introduced Excel/DDE support for historical data queries and market scanner subscriptions. This was done via the highly-efficient DDERequest mechanism that the ddedll.dll DDE server supported for the first time. The TwsDde.xls spreadsheet released with API 8.90 included two new worksheet pages, “Historical Data” and “Market Scanner,” for this purpose. TWS Version 863 and API Version 9.0 extend that functionality by introducing Excel/DDE support for array query subscriptions to open orders, executions, account updates, and portfolio updates. Four additional new TwsDde.xls pages have been introduced, corresponding to the four newly-supported subscriptions. In all cases, the DDE/Excel logic that receives array query results from TWS is very similar to that which drives DDE conditional orders, with each subscription page having a Worksheet_Calculate macro that receives subscription results and puts them into named ranges for easy programmatic access. The “Historical Data” and “Market Scanner” pages allow the user to specify the range of rows that are monitored for returning results, so that multiple subscriptions can be run simultaneously.

Running and Receiving DDE Market Scanner Subscriptions

The new “Market Scanner” page’s “Start Scanner Subscription” button allows Excel/DDE subscription to the TWS Market Scanner. Many of the scans that are currently published in the Interactive Brokers Daily Options Commentary are specified on that page, allowing the DDE/Excel user to subscribe to those same scans intraday. Scans are specified in the same way as is done when subscribing to the existing socket-based API market scanner. As Interactive Brokers adds new scans, they both become available in TWS, and are specified in scanner parameters that can be viewed in any of the socket-based API clients, including via the Java test client’s “run.bat” (for Windows) and “run.ksh” (for UNIX/LINUX) scripts. The name of the worksheet that will receive scanner subscription results is also specified. When scanner results return, an application-created named range on the specified worksheet is populated with the results, with that worksheet being created if it doesn’t exist yet. If “Activate Page” is set to “true,” then when a scanner result returns, TwsDde.xls changes to the page that is being populated. A button also exists to cancel scanner subscriptions that are no longer desired. The elements that make up a scanner subscription are:

- Number of Rows (as is done for market depth request)
- Instrument (possibilities listed in scanner parameters)
- Location Code (possibilities listed in scanner parameters)
- Scan Code (possibilities listed in scanner parameters)
- Above Price (filter out contracts with lower prices. Can leave empty)
- Below Price (filter out contracts with higher prices. Can leave empty)
- Above Volume (filter out contracts with lower volumes. Can leave empty)
- Average Option Volume Above (Can leave empty)
- Market Capitalization Above (Can leave empty)
- Market Cap Below (Can leave empty)
- Moody Rating Above (Can leave empty)
- Moody Rating Below (Can leave empty)
- S & P Rating Above (Can leave empty)
- S & P Rating Below (Can leave empty)
- Maturity Date Above (Can leave empty)
- Maturity DateBelow (Can leave empty)
- Coupon RateAbove (Can leave empty)
- Coupon Rate Below (Can leave empty)
- Exclude Convertibles (For bonds -- can leave empty)
- Scanner Setting Pairs (Such as “Annual, true”. Can leave empty)

Scanner setting pairs are delimited by slashes, making this parameter open ended as further developments occur. The “Annual,true” pairing would cause the “Top Option Implied Vol % Gainers” scan to return annualized volatilities.

Running and Receiving DDE Historical Data Queries

Via its “Request Historical Data” button, the “Historical Data” page specifies queries in two sections; the first specifying the contract (in the same way this is done when subscribing to market data), and the second specifying the historical data query parameters, those parameters having the same meaning and allowed values as the already existing socket-based historical data query parameters do. The name of the worksheet that will receive query results is also specified. When query results return, an application-created named range on the specified worksheet is populated with the results, with that worksheet being created if it doesn’t exist yet. A button also exists to cancel historical data queries that TWS has not responded to yet. TWS 863 and API 9.0 extend the originally-introduced historical data querying capability by allowing historical data to be queried for on expired contracts.

As has always been the case with API historical data queries, DDE historical data results are returned in bars of a nature very similar to the bars in TWS charts. In addition to the parameters that specify the contract, the parameters needed to request historical data via DDE are: endDateTime, durationStr, barSizeSetting, whatToShow, useRTH, and formatDate.

The “endDateTime” parameter accepts a string in the form yyyymmdd{space}HH:mm:ss{space}{TMZ}, with a time zone optionally allowed after a space at the end of the string. At the time of this release, “20060717{space}18:26:44{space}GMT” is a legal value for this parameter. The “durationStr” time span covered by a reqHistoricalData() request is specified via an integer followed by a space, followed by one of these units: S (for seconds), D (for days), W (for weeks), and Y (for years – currently limited to 1). If no unit is given, seconds is assumed. The “barSizeSetting” specifies the size of the bars that will be returned (within limits imposed by IB’s servers). The table below shows the values that are allowed: Note that prior to TWS 863/API 9.0, only the integer value could be used to specify the bar size. TWS 863 and API 9.0 also allow the bar size string to be used. In Excel/DDE, either value can be sent to TWS 863 and higher, with TWS decoding what is received. In the socket API, versions 4 and higher of the REQ_HISTORICAL_DATA message require sending the bar size string. As the table below demonstrates, use of the bar size string by TWS allows new query bar sizes to be introduced without counter-intuitive ordering being necessary (as occurred with the integer value for 3 minute bars, which were introduced after 1 year bars):

Bar Size String
Integer Value

The “whatToShow” parameter determines the nature of the data extracted, with allowable values being “TRADES,” “MIDPOINT,” “BID,” “ASK,” or “BID_ASK.” Bars of the first four types contain the start time, open, high, low, close, volume, and weighted average price during the time slice in question, and, in TRADES bars returned by TWS 863/API 9.0, include the number of trades that occurred during the time covered by the bar. The contents of bars returned in response to a BID_ASK query differ from those returned by the other query types, in that the open and close values are actually the time weighted average bid, and time weighted average offer, respectively. This makes these bars identical in nature to TWS’s “BID_ASK” candlestick chart bars.

Two additional parameters to reqHistoricalData() calls are “useRTH” and “formatDate.” If useRTH is set to 0, all data available during the time span requested is returned, even data bars covering time intervals where the market in question was outside of its “Regular Trading Hours” (RTH). If useRTH has a non-zero value, only data within the “Regular Trading Hours” of the product in question is returned, even if the time span requested falls partially or completely outside of them.

If “formatDate” = 1, dates applying to bars are returned in a format “yyyymmdd{space}{space}hh:mm:dd” - the same format already used when reporting executions. If formatDate = 2, those dates are returned as an integer specifying the number of seconds since 1/1/1970 GMT.

API 9.0 and TWS 863 extend DDE/Excel historical data queries by allowing a new final parameter, “Include Expired.” If it is true, historical data queries can be done on expired contracts, those queries being limited to the last year of the contracts life. When TWS connects either to IB via the internet, or an API client application, it creates Java-based sockets of a predetermined size. If an API application intends to make historical data requests that return more than 1000 bars, it is recommended that TWS be configured to increase the sizes of the API socket buffer. This can be done in the "tws.xml” file in the user’s Jts directory. It is important that TWS not be running when its tws.xml file is manually modified. In the XML element, the elements can be used for this purpose. Modifying that entry in the element in tws.xml like this should suffice: <apiSocketBufferSizes>500000</apiSocketBufferSizes>

Revamped TwsDde.xls spreadsheet

API Versions 8.90 and higher include a revamped and improved version of the long-standing TwsDde.xls Excel spreadsheet, the goal being to make TwsDde.xls more robust and more amenable to user modification and user programming. In addition, the introduction in API 9.0 of array query subscription to open orders, executions, account updates, and portfolio updates, address long-standing difficulties in efficiently getting that data into Excel via DDE.

The aforementioned improvements to TwsDde.xls VBA code include the following:

- All hard-coded cell references have been recorded so that they refer to named ranges. This helps the user to move data around on worksheets without breaking existing logic. It also makes important data available to user-written macros and worksheets.

- The previously-existing modules have been reorganized into these four: ArrayQueries, ErrorDisplay, Orders, and util. The functions in those modules are much more amenable to use in user programming than was the case previously. These new functions in util are particularly helpful in building new VBA functionality:

- The utils module contains many pre-defined constants that can be used in user programming. Using such constants instead of hard-coded values makes an application more robust and easier to maintain.

- Each worksheet page that has one or more buttons on it has the corresponding macro logic in its own VBA, as opposed to in worksheet-wide modules. The VBA that does the work on a particular page is easier to find and understand, is better organized, and uses the functions listed above where applicable. For these reasons, it is much easier for the user to add functionality to an existing worksheet, or base the code in a user-built worksheet on that of a pre-existing one.

- A new “Clear All Links” button on the Tickers page. Using named ranges on each spreadsheet page, “Clear All Links” removes all DDE links that the VBA provided with TwsDde.xls could create. This cancels any market data, historical data, scanner subscription, and other requests, and prepares the spreadsheet to be saved to disk. Should the user add his own links to existing or new pages, the TwsDde.xls!ThisWorkbook.clearAllLinks macro can be updated to clear them as well, possibly via the existing page-specific clearLinks() macros that it calls.

NOTE: “Clear All Links” deliberately does NOT cancel orders, as clearing order links does NOT cancel any orders placed by those links. DDE order cancellation only occurs in response to specific user instructions to do that.

Provided DDE/Excel “Executions Reporting” Application

Utilizing the aforementioned Executions array subscription capability, the Tws Dde.xls spreadsheet included in API 9.0 includes a new application, “Execution Reporting,” and a worksheet page that is dedicated to that purpose. When TwsDde.xls is subscribed to Executions, that page can generate four different types of reports:

The Executions Reporting application gives a practical example of how array subscription data can be easily extracted from the named ranges it is put into when it is received, and how such data can be used in user-built applications.

API Reporting on Expired Contracts

For all API technologies, including DDE and all socket-based API technologies, including the socket client library, ActiveX, and Java, TWS 863 (server version 31) and API version 9.0 (socket client version 30) extend contract details requests and historical data queries by supporting a new final parameter, “Include Expired.” If it is true, those operations can be performed pertaining to expired contracts. Historical data queries on expired contracts are limited to the last year of the contracts life, and are initially only supported for expired futures contracts, with that restriction being imposed by IB’s servers. At some point in the future, should historical data queries for expired contracts on other asset classes become available, TWS 863 (server version 31) and API version 9.0 (both DDE and socket client version 30) will support those queries.

Reporting of the Number of Trades per Historical Data Bar

For all API technologies, including DDE and all socket-based API technologies, including the socket client library, ActiveX, and Java, when TRADES historical data is requested, TWS 863 (server version 31) and API 9.0 (socket client version 30) include in each resulting bar the number of trades that occurred during the time period the bar covers. Server version 31 sends to client version 30 and higher a new version 3 HISTORICAL_DATA message for that purpose. DDE historical data query results include the trade count as a new column in the resulting array.

Option Computation Market Data Ticks

Starting with API Version 8.90 (socket client version 28), and TWS Version 861 (server version 29), a new option model computation market data tick has been introduced. Receiving this tick is supported by all API technologies, including DDE and all socket-based API technologies, including the socket client library, ActiveX, and Java.

As the market in an option or its underlier moves, an API application subscribing to market data for an option will receive TWS’s option model volatilities, prices, and deltas, along with the present value of dividends expected on that option’s underlier. Socket-based API applications receive this data via a new “MODEL_OPTION” market data tick. DDE/Excel receives this information via links that are created in the TwsDde.xls spreadsheet when market data on an option is subscribed to.

If the TWS user runs the Option Model Editor and fits volatility curves to option market prices, or modifies option pricing parameters such as interest rates or dividends, those changes will be reflected in any option model ticks that those actions pertain to. If the volatility curve pertaining to a particular option has not been modeled, the model volatility will be the same as that displayed in TWS’s “Implied Volatility” column (which is computed from market data on the option). The model price and model delta will then be computed from the model volatility.

New Socket-based Market Data Ticks

For all socket-based API technologies, including the socket client library, ActiveX, and Java, TWS 863 (server version 31) and API version 9.0 (client version 30) send several new market data ticks, many of which have been available in various TWS columns for some time.

Beyond making a traditional market data request, four of the new market data ticks do not require any further action, and are sent by server version 31 and higher to client version 30 and higher when market data is requested for a contract. They are:

OPEN: tick value = 14.
OPEN_INTEREST (for stocks only): tick value = 22.
BID_EXCH (for SMART options only): tick value = 32.
ASK_EXCH (for SMART options only): tick value = 33.

In addition, TWS 863 (server version 31) and API version 9.0 (client version 30) introduce several new types of market data ticks that can be requested as a part of a traditional market data request. API version 9.0 (client version 30) sends a new version 6 REQ_MKT_DATA message containing a new field that specifies the requested new ticks as a list of comma-delimited integer IDs. Requests for these ticks will be answered if the tick type requested pertains to the contract at issue. In TWS 863 (server version 31), the new market data tick types are:

Integer ID Value 
Tick Type
Resulting Tick Value (see list below)
100 Option Volume (currently for stocks) 29, 30
101 Option Open Interest (currently for stocks) 27, 28
104 Historical Volatility (currently for stocks) 23
106 Option Implied Volatility (currently for stocks) 24
162 Index Future Premium 31
165 Miscellaneous Stats 15, 16, 17, 18, 19, 20, 21
221 Mark Price (used in TWS P&L computations) 37
225 Mark Price (used in TWS P&L computations) 34, 35, 36

The resulting market data tick values are:


Extensions to Bond Contract Details Requests

For all API technologies, including DDE and all socket-based API technologies, including the socket client library, ActiveX, and Java, TWS 863 (server version 31) and API version 9.0 (client version 30) extend bond contract details request responses, by adding these four new fields:

For socket-based bond contract details requests being sent to client version 30 and higher, server version 31 and higher sends these new fields in the new version 2 BOND_CONTRACT_DETAILS message. In TwsDde.xls running against TWS 863 and higher, these four new fields are requested and returned via DDE links with items “nextOptionDate,” “nextOptionType,” “nextOptionPartial,” and “notes.”

Full API Support for Trailing Stop Limit Orders

Starting with API Version 8.91, when running against TWS version 861 or higher, trailing stop limit orders are supported by all API technologies, including DDE and all socket-based API technologies, including the socket client library, ActiveX, and Java. The order type to use is “TRAILLIMIT.” When placing trailing stop limit orders, the aux price specifies the trailing amount, and the stop price is specified in a new extended order attribute, “Trailing Stop Price,” which is also reported in open order messages to Java, the socket .dll, and the ActiveX control’s openOrder4 event. Note that Visual Basic 6.0 can not receive that event (though it will still receive openOrder3 messages for TRAILLIMIT orders, where appropriate).

Excel/DDE Order Group Placement and Cancellation

API Version 8.91 and up include further improvements to the TwsDde.xls spreadsheet than those discussed above. On both of the Orders and Conditional Orders pages, groups of orders can be simultaneously placed, modified, or cancelled. For a group of worksheet rows containing orders to be operated upon, group order operations are done by highlighting a range of cells that overlaps any cell contained in one of those rows, and then clicking on either of the “Place/Modify Order” or “Cancel Order” buttons, which now operate upon the entire group selected. Note that ranges of orders do not need to be contiguous. In support of this feature, TwsDde.xls in API 8.91 and higher creates order IDs differently, so that multiple orders can be placed in the same second. This enables baskets of orders to be simultaneously placed and cancelled from DDE. So that each order in a group can have its own unique extended order attributes, in API Version 8.91 and higher, the TwsDde.xls Extended Order Attributes page can act as a template that can be applied to a selected group of orders. To do this, select the order or orders you want to apply the template to, and click on the “Apply Extended Template” button. TwsDde.xls will then copy the vertical column of extended order attribute values from the template to newly created space at the end of each selected order row. Further edits can then be applied to any order row’s extended order attributes. When orders are placed, if a corresponding order row contains a value in the “Time in Force” column, then that order’s extended order attributes will be taken from the order row. If the “Time in Force” cell is empty, the order’s extended order attributes will be taken from the template (as has previously been the case).

OCA Group modify via the API is blocked

TWS does not allow the OCA group of an order to be manually modified once that order is transmitted. Starting with TWS 863 (server version 31), when an API client attempts to modify an order that has already been transmitted, TWS version 863 and upward will process the modify attempt as prior versions have, but will NOT modify the OCA Group. In addition, when an attempt to modify the OCA Group on such an order has occurred, TWS version 863 (server version 31) and upward will send error code 2109, which is an “order event warning” message, with this accompanying text: "Attempted modify of OcaGroup will not be done. Order modify now being processed." API developers should keep in mind that the “order event warning” error code 2109 exists to transmit warnings about an order placement or modification that will be processed in spite of the situation being warned about. It has been used in the past to indicate to an API client that a pattern day trader violation could occur on the next order that will be placed, and could be used in the future to warn of other issues.

© 2001 Interactive Brokers LLC. All rights reserved. Sun, Sun Microsystems, the Sun Logo and Java are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. Excel is a trademark or registered trademark of Microsoft Corporation in the United States and/or other countries.