Using the IB ActiveX Control with Excel

I produced a simple VB6 program that uses the TWS ActiveX Control to request market data for up to 13 securities and writes each tickprice or ticksize event to a file (a separate file for each security). 

I also modified an existing Excel 2000 spreadsheet to request market data for the same securities, and also log each tick to files. The spreadsheet has a couple of options to simulate heavy-duty processing:

EITHER by very frequently looping for up to a second, to simulate intensive VBA processing in an event handler, so that any events arriving during the 'busy' period cannot be fired.

OR by performing intensive spreadsheet calculations every 250 milliseconds.

[If you want to experiment with these programs, you can get them from here.]

I chose 13 heavily traded securities, and set the programs running just at the open of the US stock markets on 2 March - ie 9:30 EST or 14:30 UK time. I ran the programs for the first 5 minutes of the session, then stopped them. In this test, the spreadsheet was running with the intensive VBA option set.

When I compared the files, I found that the events recorded were identical. However because Excel was finding so little time to be able to process the events, their timestamps became increasingly later than those recorded by the VB6 program. For example, for the S&P Emini March 05 (ES) contract, the last event recorded by the spreadsheet at 14:35:23 UK time was recorded by the VB6 program at 14:30:40. So at the time I closed the programs Excel was processing events that had been buffered up by the ActiveX control for nearly 5 minutes. 

Note that not a single event was lost. Nearly 2700 events for ES alone were still buffered up at the time I closed the programs (this is the number of events logged by the VB6 program that Excel had not yet had time to process).

Later that day I ran the programs again, this time with the spreadsheet using its intensive calculation option. The results were the same: no events were missing from the Excel logs. In this case the markets were not so busy, and the maximum difference between the timestamps in the VB and the Excel log files was about 5 seconds.

So I am very convinced that the ActiveX control DOES NOT LOSE EVENTS.

The question then is why do so many people think it does? 

Well, there are some nasty gotchas with Excel that most people may be unaware of.

1. If you type anything into an Excel cell, Excel will ignore all events from ActiveX controls from the time you type the first letter (or click in the formula bar) until you press enter. So during this period, the IB ActiveX control could be busy firing off events, but Excel will not pass any of them on to the relevant VBA event handler.

2. Similarly, if Excel displays a modal dialog box, it ignores events until the dialog box is closed. For example File > New will cause this, as will the VBA code displaying a message box.

I strongly suspect that another cause of 'missing' events or other odd behaviour is re-entrancy. This is when code that is already processing one event is entered again to process a subsequent event before the first event's processing is completed. When this second event has been processed, processing of the first event continues. (In fact the second event could be any event that is processed by that class module, not necessarily the same kind of event as the first one.)

Re-entrancy is dangerous because the second event's processing may change some variables that are significant to the first event's processing. Then when the processing for the first event resumes after the second event's processing has completed, it no longer functions correctly.

Re-entering an event procedure like this can be caused in at least two ways: by calling DoEvents, or by showing a form.

To avoid problems with re-entrancy, the developer must take great care when calling DoEvents or displaying a form within an event handler, to ensure that any processing after that point would not be affected by re-entrant processing.

So the question is: is it worthwhile trying to use the TWS ActiveX control in Excel?

I think it's perfectly possible to use it successfully, but there are some serious caveats:

- any required user input must be via controls such as text boxes, check boxes, combo boxes etc (typing in these does not cause events to be dropped)

- the user must not type into any Excel cell (unfortunately locking all the cells does not help, because an attempt to type into a locked cell causes Excel to display a message box, which itself causes events to be dropped!)

- the user must not use any menu options that display dialogue boxes (such as File > Open or Tools > Data Analysis)

- VBA code must not use the msgBox function

It is worth pointing out that Excel 2002 and 2003 have a mechanism that might be more appropriate for using the TWS ActiveX Control. This is called an RTD Server (RTD stands for RealTime Data). An RTD Server is essentially a COM component that implements the IRtdServer interface. Excel and the RTD Server interact via this interface to get data into Excel without having to use VBA code. I believe that using an RTD server avoids the problems of lost events caused by user data entry into cells and dialog box display. However as I don't have an Excel version later than 2000, I'm unable to investigate this further.

Richard King
4 March 2005