Excel Automation

Again, I don't know where you guys are coming from, but when I tested Excel 2003's speed, writing to a cell vs. writing to the registry, the latter was 3 TIMES faster. I was amazed at how much overhead there was just placing data into a cell.

When I used IB's ActiveX component (TWS.OCX), I maxxed my CPU when trying go collect realtime data on 100 symbols. In this case, the overhead of ActiveX was apparent.

The next thing I am going to try is a DLL interface to IB's api functions. What I discovered is that the only efficient way of collecting realtime data into Excel, is via an entire array of prices updated on a time interval. Otherwise, with multiple symbols randomly updating, the Windows messaging overhead inherent in ActiveX architecture is just so great. Then the issue of the Excel Event overhead comes into play as well.
 
Quote from syswizard:

Again, I don't know where you guys are coming from, but when I tested Excel 2003's speed, writing to a cell vs. writing to the registry, the latter was 3 TIMES faster. I was amazed at how much overhead there was just placing data into a cell.

When I used IB's ActiveX component (TWS.OCX), I maxxed my CPU when trying go collect realtime data on 100 symbols. In this case, the overhead of ActiveX was apparent.

The next thing I am going to try is a DLL interface to IB's api functions. What I discovered is that the only efficient way of collecting realtime data into Excel, is via an entire array of prices updated on a time interval. Otherwise, with multiple symbols randomly updating, the Windows messaging overhead inherent in ActiveX architecture is just so great. Then the issue of the Excel Event overhead comes into play as well.
More...

SysWiz,

What is it you are trying to accomplish... skipping any proprietary info of course...

Couldn't you go with another DDE feed besides IB... to accomplish your objectives...
 
Quote from EdgeHunter:

SysWiz,

What is it you are trying to accomplish... skipping any proprietary info of course...

Couldn't you go with another DDE feed besides IB... to accomplish your objectives...
More...
Well, of course. However, the problem remains the same.....especially if one is attempting to build tick bars or time bars. So even with DDE and SetLinkOnData, there is still event overhead of processing each tick that hits the cells. It would be manageable if the DDE updated all cells at once...which would then result in only 1 procedure/event call.....but I don't think that is the case with IB's implementation. I could be wrong though as I've never tested this in detail.

What I really need is a data vendor that provides a Time Interval or Tick Interval Server. I know that Genesis provides this with their API and data service. IB got around doing this by providing RealTimeBars every 5 seconds. But like I said, the ActiveX interface messaging blizzard for 100 symbols simply overwhelmed Excel, even when it was running in realtime windows priority as the only application. To really collect effectively and efficiently, a multithreaded C/C++ DLL would be needed to capture the data and store it into array structures.
 
Quote from syswizard:

Well, of course. However, the problem remains the same.....especially if one is attempting to build tick bars or time bars. So even with DDE and SetLinkOnData, there is still event overhead of processing each tick that hits the cells. It would be manageable if the DDE updated all cells at once...which would then result in only 1 procedure/event call.....but I don't think that is the case with IB's implementation. I could be wrong though as I've never tested this in detail.

What I really need is a data vendor that provides a Time Interval or Tick Interval Server. I know that Genesis provides this with their API and data service. IB got around doing this by providing RealTimeBars every 5 seconds. But like I said, the ActiveX interface messaging blizzard for 100 symbols simply overwhelmed Excel, even when it was running in realtime windows priority as the only application. To really collect effectively and efficiently, a multithreaded C/C++ DLL would be needed to capture the data and store it into array structures.
More...

SysWiz, Do you need EVERY single new tick per each 100 symbols, or whatever, to build your Time Bars... to make your trade decisions or...

COULD you be okay with 1 second snapshots of what is in the DDE cell at each 1 second interval... throughout the day...to build Time Bars in a charting package... (60 updates per minute)


edge...
 
Quote from EdgeHunter:

SysWiz, Do you need EVERY single new tick per each 100 symbols, or whatever, to build your Time Bars... to make your trade decisions or...
COULD you be okay with 1 second snapshots of what is in the DDE cell at each 1 second interval... throughout the day...to build Time Bars in a charting package... (60 updates per minute)
edge...
More...
Interesting...you know I've THOUGHT ABOUT THAT BEFORE. What bothered me about it was that I'd be sampling a sampled data feed (with IB). Now that's a bit scary, especially with fast movers like NQ, YM, and ES mini futures.

Thanks for resurrecting the idea...it's worth a try, for sure.

It's a study in statistical sampling of sorts....
 
Syswizard

do you know any software to record my screen?
I can make a video and then I will put it on youtube.com to show you what is speed in excel.
 
camtasia not enough. it does not catch all the photograms
the speed of quotes is too high for camtasia record the image in real time
I´ll try with my cam
 
OK, but why don't you just tell us what you are really doing here that makes Excel look so good ?
Just capturing data via DDE or RTD means NOTHING.
It's the PROCESSING of the incoming data (building bars, running studies, indicators, etc) that is the huge problem with Excel from a performance standpoint.
Again, there is no way Excel can stand up to a multithreaded C/C++ applications.
NO WAY.
 
You must log in or register to reply here.
Back
Top