1) You must shutoff the GUI in Excel for it to work reliably in a realtime data environment. I found few people who know how to do that. Entering data into a cell by hand is a "no-no". 2) Excel is very sensitive when ActiveX components are being referenced and used (which is often the case for the trading APIs) 3) Excel formulas are nearly unusable for large volumes of fast moving data....VBA must be used instead. 4) RTD is much more robust and efficient compared to DDE which unfortunately is most often the only option available in many of the APIs out there (TOS, IB, Genesis, etc).
1.) By shutting off the gui do you just mean staying out of "Edit Mode"? 3.) this is likely up to debate, Ive read that excel functions (written in c++) will calculate faster than uncompiled VBA.
1. Nope...worksheet protection is the best option. Shutting down edit mode is another way. 3. Agreed. Excel worksheet functions are very fast. Try using WorksheetFunction.CountBlank over an entire worksheet to see if the worksheet is empty. VBA using a loop is like molasses comparatively. Technically if you know what you are doing, you can almost get Excel to perform at a VB level of performance...somewhat below C/C++. Otherwise, Excel can be such a "dog" at times....I've seen it spike my CPU to 80-90% at times.
do you mean that a strategy created with excel formulas alone can run almost as fast as a c++ equivalent? thanks!
Absolutely NOT....what I meant is that using WorksheetFunctions within VBA show terrific performance...compared to emulating them using raw VBA. Tip: Avoid using in-cell formulas and just use the worksheet for output/display. Do all calcs in VBA. That's what's great about RTD vs. DDE. With the latter, you must use in-cell formulas, not so for RTD.
thank you for your reply...I am wondering what is so wrong with using formula driven excl workbook to automate your strategy....unless you are operating on minute by minute basis the speed is not an issue as I see it....
It's only a problem if you are getting real-time Level 1 quotes or even time-and-sales data. Excel's event handling mechanism is too slow for fast moving stocks or index futures for instance. Part of the problem is that Excel's internal architecture is single-threaded. Most trading platforms are multi-threaded.....and some of the latest ones, like Multicharts, use really advanced multicore parallelism. Ideally, to circumvent the above issue, you would want Excel to collect price / volume data on it's own timer...say every second. This can be done with RTD via the RefreshInterval setting. DDE does not have this feature. BTW: If you are getting bar data say every minute, then you have no worries using Excel's formula approach.
thank you for detailed answer...what if i use the powerful processor and run my system on a second by second basis - provided that my workbook takes half a second to recalculate? thank you
There is a firm in Chicago that has a product to do just this. Give me a call or send email and I can introduce you to them. 312.953.6260 david.madison@madisontechnologies.com