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 Install TWS on a 64 bit Windows 7 system.

If you are unable to run TWS after installing on a 64 bit system, below are the instructions that will avoid the “missing shortcut” error that you might encounter and allow you to run TWS as expected.

  1. Install 32 bit Java. (remove any old java installations first)
  2. Install TWS (followed by the API if you are using it)
  3. Copy the file “Javaw.exe” from “C:\Program Files (x86)\Java\jre7\bin” and place it in “C:\Windows\System32”
  4. Now open from the TWS shortcut as normal.

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

Constant Volume Charts added

I have added the option of constant volume  bar charts (as alternative to time based) for the Excel/VBA based automated trading platform

The constant volume bar option, updates a new bar after a certain amount of volume has completed. This amount should be entered into D3 of the “Live” sheet. Checks are in place to prompt if the values are not entered and the constant volume is set to override the default time based bars.

Below is an example of Constant volume bar charting. You will notice that each bar contains roughly the same amount of volume.

CV