Excel Automation

Quote from dima777:

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
More...
Yes, that will help albeit Excel 2003 is not optimized to take advantage of multiple core CPUs....however, I do believe Excel 2007 has been changed to support multicore. Unfortunately, Excel 2007 suffers in performance overall compared to Excel 2003 due to the increased size of the worksheet and the conversion from half Words to full Words for the cell indice references.
As long as there is a setting where you can tell the DDE server to transmit only every 1000 milliseconds, you should be ok. However, keep in mind that if you have multiple symbols, the DDE server may be sending the streaming quotes at [bold]different[/bold] intervals every 1000 milliseconds. This is not good because it can swamp the worksheet_change call back procedure and cause a loss of data. The best DDE server design for this application would be where an entire ARRAY of price data is sent every 1000 milliseconds. In that case, there would be ONE change event called every second compared to many. However, I have not seen anyone design it this way (sadly).
The above explains technically why so many traders have seen Excel swamp their CPUs.
 
Quote from syswizard:


The above explains technically why so many traders have seen Excel swamp their CPUs.
More...

syswizard, thanks for the detailed explanations.
is the above hits on CPUs just regarding the DDE processes? So this is one the reasons to use RTD w/ Excel 2007 if possible?
 
Quote from syswizard:

Yes, that will help albeit Excel 2003 is not optimized to take advantage of multiple core CPUs....however, I do believe Excel 2007 has been changed to support multicore. Unfortunately, Excel 2007 suffers in performance overall compared to Excel 2003 due to the increased size of the worksheet and the conversion from half Words to full Words for the cell indice references.
As long as there is a setting where you can tell the DDE server to transmit only every 1000 milliseconds, you should be ok. However, keep in mind that if you have multiple symbols, the DDE server may be sending the streaming quotes at [bold]different[/bold] intervals every 1000 milliseconds. This is not good because it can swamp the worksheet_change call back procedure and cause a loss of data. The best DDE server design for this application would be where an entire ARRAY of price data is sent every 1000 milliseconds. In that case, there would be ONE change event called every second compared to many. However, I have not seen anyone design it this way (sadly).
The above explains technically why so many traders have seen Excel swamp their CPUs.
More...
Syswizard, Every time you post about Excel i learn something...
and we are constantly programming with it and working in it for hours a day...

So thank you... :)

After trying out eSig, DTN, MarketLink, MyTrack DDE... with all but Market Link our CPU got crushed... toasted...

then - Marketlink terminated our account because they said they never expected us to use the maximum number of symbols that we subscribed to and it was way over their bandwidth usage allowances... !!

We finally are very satisfied with using Reuters / Equis/ Metastock DDE feed BECAUSE REUTERS DDE FEED DOES ARRAY row links and our DDE feed pulls in 900+ stocks for about 8-12 columns only using up 15% of CPU... during the day... its amazing...

here is an a short example of an array link from Reuters / Equis / Metastock DDE feed for a 5 columns feed...

=BDDE|TKR!'A/net,flow,vo,vwap,tik'

edge...


HAVE STOP <img src="http://www.enflow.com/p.gif"> WILL TRADE
 
Quote from syswizard:

...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.
More...
Excel 2007 is supposedly multi-threaded and supports multi-core cpus, so it should work a lot better with high-frequency data. I haven't tested the difference, but use it with TT and Autotrader and to my naked eye it's instant.
 
Excel 2007 with RTD (X-Trader PRO) can autotrade.
Real Time Data can be logged from excel to CSV, DB via Macro.
RTD is not necessarily more reliable than DDE. Async vs Sync

Time to develop core ideas and strategies may be quicker in excel but if you are going to rely on a trading worksheet you need to code for extensive error checking.

Excel may crash and depending on your trading system you should probably have a hot stand by with access to your worksheet. Save often...

X-Trader Pro is good for RTD excel links
OpenEcry has a good Excel DDE add-in.

OpenEcry is Free and X-Trader Pro is $1500 a month.

OpenEcry Autotrading functionality via Excel is easier and better developed than TT's XTAPI / Autotrader.

Eitherway its a nicer way to papertrade and test strategies before you expend the effort to port to a compiled strategy.

Both systems are dependent on the core platform running and not pure Excel. Both offer excellent support.

OpenEcry is stronger for futures and future options. TT is a broader base overall trading platform.
 
Quote from EdgeHunter:

Syswizard, Every time you post about Excel i learn something...
and we are constantly programming with it and working in it for hours a day...

So thank you... :)
More...

You are welcome !

We finally are very satisfied with using Reuters / Equis/ Metastock DDE feed BECAUSE REUTERS DDE FEED DOES ARRAY row links and our DDE feed pulls in 900+ stocks for about 8-12 columns only using up 15% of CPU... during the day... its amazing...

here is an a short example of an array link from Reuters / Equis / Metastock DDE feed for a 5 columns feed...

=BDDE|TKR!'A/net,flow,vo,vwap,tik'
More...
See... I told you it could be done. Excel is very efficient with ARRAY processing, but not so for cell-by-cell processing. In fact, it's quite inefficient.
 
Quote from 4XQs:

Excel 2007 is supposedly multi-threaded and supports multi-core cpus, so it should work a lot better with high-frequency data. I haven't tested the difference, but use it with TT and Autotrader and to my naked eye it's instant.
More...
Well, please report back to us some objective results. From what I understand, the multi-threaded feature was only for the spreadsheet formulas, not the VBA or GUI forms. So I guess technically, since DDE is formula-based, it could be much faster with multithreading.
 
Quote from PocketChange:

RTD is not necessarily more reliable than DDE. Async vs Sync
More...
BUT....the RTD protocol is much more flexible than DDE. One important feature: you can place the data right into a VBA Array instead of a spreadsheet cell. This is about 10-20 TIMES more efficient than updating a cell. It's all about the overhead of placing things on a spreadsheet.
X-Trader Pro is good for RTD excel links
OpenEcry has a good Excel DDE add-in.
More...

With this, I totally agree. OEC did a tremendous job of documenting their Excel DDE implementation. X-Trader as well has done some good documentation. Now for comparison, let's take IB's implementation...with little to no documentation.
 
SysWizard

Finding the speed for processing price feeds to be significantly faster with OEC DDE versus TT's XTAPI.

RTD Throttle is set to zero and pulling just the ES and its option chain (about 50 symbols). OEC's DDE seems more efficient sending arrays versus TT requiring seperate RTD commands for each symbol.

The actual bid/ask ticks are about the same but the level II DOM data streams twice as fast.

However, DDE skips updates where as RTD queues the data.
Protocol benefit goes to RTD... Any tricks you can think of to improve DDE data integrity?
 
Quote from PocketChange:

SysWizard

Finding the speed for processing price feeds to be significantly faster with OEC DDE versus TT's XTAPI.
RTD Throttle is set to zero and pulling just the ES and its option chain (about 50 symbols). OEC's DDE seems more efficient sending arrays versus TT requiring seperate RTD commands for each symbol.
More...
It's the array processing that makes the difference.
Too bad TT did not pick-up on that fact in their RTD implementation.

However, DDE skips updates where as RTD queues the data.
Protocol benefit goes to RTD... Any tricks you can think of to improve DDE data integrity?
More...
This is all up to the DDE server to handle. Most have throttle rates which determine the data sampling frequency. I know TOS has one that can be set to zero...meaning no lost data. However, I have not tested this yet.
BTW: there are about three ways to fetch the DDE data....to a variable, to a spreadsheet cell, to a control on a form. Which are you doing ?
 
You must log in or register to reply here.
Back
Top