Excel Automation

Quote from syswizard:

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

http://www.willowsolutions.com/news/news_rtd.shtml

PDD (Reuters) and Bloomberg DDE have settings that enable a user to set the update interval for real-time data on their workstation. The Willow table function uses DDE formulas so its ability to specify update intervals depend on the DDE flavor being used.
 
Quote from syswizard:

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.
More...
Hi SysWiz,

Let's think outside the box here together for a second...

What about taking the data that arrives in excel and writing it to a file every 2 seconds that is read by a charting application every 2 seconds... to update its charts...

thereby using Excel for what it is best at with DDE... data reception...

We use SierraCharts to chart data that we grab every 2 seconds from a DDE feed and a formula function in a field to build custom indicators...

Let a charting app do what it is built to do... I think there is no need to have Excel do Charts, or build bars... which it is not very good at...

Below is a pic of the Advance Decline Line for the SP500 that we create by writing the data to a file that SierraCharts can read...

<img src="http://www.elitetrader.com/vb/attachment.php?s=&postid=2089889">
 

Attachments

Quote from EdgeHunter:

Hi SysWiz,
Let's think outside the box here together for a second...
What about taking the data that arrives in excel and writing it to a file every 2 seconds that is read by a charting application every 2 seconds... to update its charts...
thereby using Excel for what it is best at with DDE... data reception...
More...

It's not a bad idea...however, Excel actually stinks here as well.....if you try to run a procedure that is called upon every change in the DDE cell, you'll overwhelm even the fastest CPUs when running multiple symbols.
We use SierraCharts to chart data that we grab every 2 seconds from a DDE feed and a formula function in a field to build custom indicators...
More...
Some would say 2 second sampling is inadequate. However, having the indicators computed in Sierra is a good idea. Also, one would want to know why not let Sierra capture the data as well ?
Let a charting app do what it is built to do... I think there is no need to have Excel do Charts, or build bars... which it is not very good at...
More...
Actually Excel is pretty good at charting...if you know how to control the display, etc. from VBA. Few know how to do that.
 
Syswizard...

Had the same swamped CPU issue and needed a way to throttle DDE updates.

Used circular references and enabled iterative calculations inside excel to build a three step counter.

ie... =IF($C$15=1,0,IF(E21=E22,0,IF(AND(E22<>E23,B22<>B23),2,1)))

Modified all of the DDE formulas with an if statement restricting the updates to when the counter=0.

When the counter changes to one we trigger orders and updates. When the counter changes to two we error check and verify and if everything is OK the counter resets to zero. DDE Updates resume.

This trick throttles DDE updates dynamically to as fast as Excel and your worksheet can calculate. Provides ability to error check the updates and filter erroneous data prior to acting on it.

A variation of this circular reference trick may provide a workable solution for you.
 
Quote from PocketChange:

Syswizard...
Had the same swamped CPU issue and needed a way to throttle DDE updates.
Used circular references and enabled iterative calculations inside excel to build a three step counter.
ie... =IF($C$15=1,0,IF(E21=E22,0,IF(AND(E22<>E23,B22<>B23),2,1)))
Modified all of the DDE formulas with an if statement restricting the updates to when the counter=0.
More...
Clever. I take it you are using Application.OnTime to update the counter every second then ?
This trick throttles DDE updates dynamically to as fast as Excel and your worksheet can calculate. Provides ability to error check the updates and filter erroneous data prior to acting on it.
A variation of this circular reference trick may provide a workable solution for you.
More...
Well, the other way to do this is via the DDERequest command within VBA that is much better than just placing the DDE references into cells. The DDERequest can be placed on a timer and called as fast as you like using the Windows API SleepEx function...as Excel's OnTime is only granular to the second. I should also like to note here that many DDE server implementations allow YOU to set the delay interval. Both IB and TOS have this feature in their DDE feeds.
 
Quote from syswizard:

It's not a bad idea...however, Excel actually stinks here as well.....if you try to run a procedure that is called upon every change in the DDE cell, you'll overwhelm even the fastest CPUs when running multiple symbols.
More...

If you use a VBA timer event you can grab the data in a cell every 1 second and that is a very good representation sample of the true value sample... and the effect on the CPU is slight even with 1000 symbols in our Reuters spreadsheet... if you try to grab every change using a worksheet change code - yes - CPU would be crushed...

We use excel because we are doing custom indicators... other wise i pull data into NeoTicker for regular stuff...
 
Quote from syswizard:

Clever. I take it you are using Application.OnTime to update the counter every second then ?
More...

Nope... The counter is the dynamic tick/time keeper... as quickly as it does its calcs it takes on more data... if it skips ticks cause the data is streaming faster than it can process... thats ok... we can't act on it anyways.

Big issue we had was the ability to apply some sanity rules and scrub on the data feeds. We drop Ticks that are way off market ... allow our positions to be confirmed and updated before triggering new orders.

99% of the time we are still processing data inside of one second.. The feeds are relatively fast and easy to process. its all of the back office updates, position triggers, calculations and error checking that force us to slow down.

Great to have the data but if you can't reliably execute throw it out.
 
Quote from PocketChange:

We drop Ticks that are way off market ... allow our positions to be confirmed and updated before triggering new orders.
More...

How do you test for bad ticks... we are just looking into that...
 
Quote from EdgeHunter:

How do you test for bad ticks... we are just looking into that...
More...

We dynamically build our own "Executable" Price feed based on level II DOM.

Current Bid/Ask are used as part of the weighted calculations based on our trading increments.

Attached is a sample from today. The low ask quantities in pos 1&2 of the DOM would results in slipage. The data table updates about 10 times a second so you take the last three serialized quantity changes and your orders should flow through with no surprises.

Main gaps we see are related to interruptions of the feed. intermittent 10 second - 30 delays cause spikes. Timers trap the price delays and formulas filter for resulting gaps.
 

Attachments

Quote from EdgeHunter:

If you use a VBA timer event you can grab the data in a cell every 1 second and that is a very good representation sample of the true value sample... and the effect on the CPU is slight even with 1000 symbols in our Reuters spreadsheet... if you try to grab every change using a worksheet change code - yes - CPU would be crushed...
More...
I would be willing to bet that DDERequest within VBA is about 10 times less CPU intensive than placing it on the spreadsheet.
Next week, I'll try to prove this.
I once compared just moving data from VBA variables to a worksheet vs. placing them into the windows registry. The latter was 5 times as fast !
 
You must log in or register to reply here.
Back
Top