Fixing Run-Time error ‘6’ Overflow when using Excel twsdde.xls to send orders to IB

Twsdde.xls (the sample excel file provided by Interactive Brokers) contains a procedure that creates order ID’s using a calculation based on the current date and time. The variable “ongoingID” is defined as type Long. On 8/26/2012 the calculation began producing a value higher than the maximum value for a Long in Excel which is 2147483647 [wiki].

To solve this error I currently recommend the following steps:

From TWS (Trader Workstation) menu:

  1. Navigate “edit”>global configuration>API
  2. Press the “Reset Order ID and ID sequence” button

 

In any workbook that you use such as Twsdde.xls or any custom Excel/VBA workbook used to send orders to IB:

  1. Open the workbook and press Alt+F11 to open the vba editor
  2. Press CTRL+F (or use menu) to open the Find (search) box. Search for and change “Const orderMult = 1000000” to “Const orderMult = 100000” (one less zero)

 

 

Now you should once again be able to successfully send order to IB via Excel.

 

 

 

How to retrieve live data for SPY futures in Twsdde.xls

The multiplier column in Twsdde.xls is not used by the code that constructs the links to retrieve data. Filling it in has no affect on the contract received. This creates an issue when attempting to retrieve data for Futures with more than one contract available with the same contract expiration month.

As an example, SPY futures are available in contracts with a multiplier of 1000 and 100 on the ONE exchange.

The error message received in this case is “The contract description specified for SPY is ambiguous;”

The solution is to first find the local symbol. You can do this by first adding the contract in TWS, right click on the contract name within TWS, then select contract details and select “details”.
This will open the page shown below and you can find the local symbol. As shown below, leave out the other parameters such as expiration date as it’s inherit in the local symbol (and will result in no data).