Excel Tick Data

[PocketChange]

Most people underestimate the power of excel as a real time calculation engine. Out the box it resolves time to 10ms (I know you can display time formats to 1ms but its internal timers are set to 10ms) and VBA resolves to 1 second by default. As a result excel gets a slammed as being "too slow". Its very powerful if you know what your doing... If not its a bastard child you want to punt

Excel is programmable... You will need to use high resolution timers and you can capture tick data in microseconds and save it out to a database.

Its a good modeling tool and some recent products and extensions greatly extend its capabilities... There are Excel compilers, Jedox for OLAP Cubes and Trading Dashboards. Sparkcharts, microcharts, Gigaspace for Excel is really powerful... 5 years ago this type of setup would have cost a few million dollars and now is accessible to retail traders literally for free.

Attached is a screen shot of the last 3 hours of live feeds (afterhours) running through an optimized excel quant engine on to an iphone skin for mobile monitoring.

50 symbols streamed through Excel - 2GB of data processed and a little over 70000 ticks captured. Timer resolution to 10 decimals... in microsoeconds. about 16,000,000 calculations. The base Array is 50 x 500... 25000 cells of real time calcs and triggers updating about 250 cells each symbol update.

You can consume CME's 70 TPS feed, calculate all triggers and log it all to a db on the fly. The game is only getting faster as their next system is suppose to process orders in microseconds...

Now sitting with a huge database of historic tick data really serves no purpose unless you can model it. Checkout all of the OLAP BI tools available. There is a very cool downloadable example at: http://thecommon.net/3.html and at JEDOX / PALO http://www.jedox.com/en/home/overview.html.

Finally, this is all pretty advanced programming and code optimizations but once done it works well. For most people, The DDE worksheet provided for your trading platform is more than adequate.

 

[melanie911]

Quote from PocketChange:

Most people underestimate the power of excel as a real time calculation engine. Out the box it resolves time to 10ms (I know you can display time formats to 1ms but its internal timers are set to 10ms) and VBA resolves to 1 second by default. As a result excel gets a slammed as being "too slow". Its very powerful if you know what your doing... If not its a bastard child you want to punt

Excel is programmable... You will need to use high resolution timers and you can capture tick data in microseconds and save it out to a database.

Its a good modeling tool and some recent products and extensions greatly extend its capabilities... There are Excel compilers, Jedox for OLAP Cubes and Trading Dashboards. Sparkcharts, microcharts, Gigaspace for Excel is really powerful... 5 years ago this type of setup would have cost a few million dollars and now is accessible to retail traders literally for free.

Attached is a screen shot of the last 3 hours of live feeds (afterhours) running through an optimized excel quant engine on to an iphone skin for mobile monitoring.

50 symbols streamed through Excel - 2GB of data processed and a little over 70000 ticks captured. Timer resolution to 10 decimals... in microsoeconds. about 16,000,000 calculations. The base Array is 50 x 500... 25000 cells of real time calcs and triggers updating about 250 cells each symbol update.

You can consume CME's 70 TPS feed, calculate all triggers and log it all to a db on the fly. The game is only getting faster as their next system is suppose to process orders in microseconds...

Now sitting with a huge database of historic tick data really serves no purpose unless you can model it. Checkout all of the OLAP BI tools available. There is a very cool downloadable example at: http://thecommon.net/3.html and at JEDOX / PALO http://www.jedox.com/en/home/overview.html.

Finally, this is all pretty advanced programming and code optimizations but once done it works well. For most people, The DDE worksheet provided for your trading platform is more than adequate.

The DDE worksheet provided for your trading platform is more than adequate.

ya.

 

[syswizard]

Quote from PocketChange:


Attached is a screen shot of the last 3 hours of live feeds (afterhours) running through an optimized excel quant engine on to an iphone skin for mobile monitoring.

How does Excel interopt with the skin ? It's a pretty cool technique.

Finally, this is all pretty advanced programming and code optimizations but once done it works well. For most people, The DDE worksheet provided for your trading platform is more than adequate.

I dispute that a bit. The reason being there is a huge difference between handling tick events FOR EACH SYMBOL vs. EACH SET OF SYMBOLS. In the latter case, it's a single message hitting the workbook for all symbols. Few DDE implementations use this array approach which is very efficient. TOS for example requires a separate event handler for each symbol being tracked. CPU load can easily go to 80% or more in fast markets, even with just 3 or 4 emini futures contracts. Once again, I must remind you that Excel is still STA (Single Threaded Architecture) despite some threading calculation improvements in Excel 2007. Other platforms use sophisticated multithreading techniques for greater efficiency.

 

[vikana]

Quote from PocketChange:

Most people underestimate the power of excel as a real time calculation engine. Out the box it resolves time to 10ms (I know you can display time formats to 1ms but its internal timers are set to 10ms) and VBA resolves to 1 second by default. As a result excel gets a slammed as being "too slow". Its very powerful if you know what your doing... If not its a bastard child you want to punt

Q: How do you change the timers? Maybe I'm misunderstanding, but you seem to imply that you can switch to a faster recalc timer somehow.

Thanks in advance,

 

[syswizard]

Quote from vikana:

Q: How do you change the timers? Maybe I'm misunderstanding, but you seem to imply that you can switch to a faster recalc timer somehow.

Thanks in advance,

You can implement the base Windows API functions GETTIMER and SETTIMER within a VBA module. However, there are all sorts of restrictions....no keyboard input allowed, can't be used within a custom class, etc, etc. These API functions have no restrictions, but they can be delayed if Excel cannot keep up with the processing.

 

[PocketChange]

Quote from syswizard:

How does Excel interopt with the skin ? It's a pretty cool technique.

I dispute that a bit. The reason being there is a huge difference between handling tick events FOR EACH SYMBOL vs. EACH SET OF SYMBOLS. In the latter case, it's a single message hitting the workbook for all symbols. Few DDE implementations use this array approach which is very efficient. TOS for example requires a separate event handler for each symbol being tracked. CPU load can easily go to 80% or more in fast markets, even with just 3 or 4 emini futures contracts. Once again, I must remind you that Excel is still STA (Single Threaded Architecture) despite some threading calculation improvements in Excel 2007. Other platforms use sophisticated multithreading techniques for greater efficiency.

Except for DOM data from TT and some of the Bloomberg implementations I haven't seen many Array to Array data feeds.

DDE for our purposes is actually more efficient than RTD. All DDE changes synchronize as one batch update into excel and process in 1 calc cycle.

RTD FIFO asynchronously triggers calcs so you end up with a series of sequential calc updates. Small and Fast but more Calc cycles.

The Trick is taking complete control of the calc engine and stack space. You've got to remove all volatile functions from excel and explicitly manage worksheet calculate. Than you can hook the windows timers without crashing and do microseconds.

The Iphone skin is a VBA Modeless form with transparency that is hooked by handle to the System Tray. When the calc engine runs it sits in the system tray with the excel application hidden. It runs really fast because there are no cells being populated or painted on screen, each calculation cycle is tightly controlled and the data is saved to SQL or CSV in blocks. You really can't even tell its Excel... Excel as a pure calc engine is damn fast when you strip out the UI.

Remote monitoring is RPC/VNC. Another little trick for managing all of the market data... We use SQLite as our application file format in lieu of XLS.
With the Data saved to SQL other systems can immediately act on the calcs without import/export/conversion bs. take a look at dhRichClient from thecommon.net. Lighting fast!

So why do we use Excel? Rapid modeling, easy to share, test and communicate with others. Too Old to change

 

[syswizard]

DDE for our purposes is actually more efficient than RTD. All DDE changes synchronize as one batch update into excel and process in 1 calc cycle.

I haven't tested this. If this were the case, they should be passing back an array. Otherwise, each cell change triggers yet another worksheet change event function callback. With an array returned, only 1 worksheet change event is triggered.

 

[PocketChange]

Quote from syswizard:

I haven't tested this. If this were the case, they should be passing back an array. Otherwise, each cell change triggers yet another worksheet change event function callback. With an array returned, only 1 worksheet change event is triggered.

Yeah this was kind of a pleasant surprise find for us too.

The way we tested this is we added a
counter to the worksheet calculate event: range("a1") = range("a1")+1

and a Sum for all symbol updated in the calc cycle: range("a2") = sum(dde_update_Column)

For each Calc Cycle you get a serialized calc count, count of symbols updated and DDE cells updated.

We set a formula to count the changed DDE cells in each symbol row and a column count for updated symbols.

DDE updates queue up until the worksheet is ready for data and then update the sheet in one pass.

If you use any volatile functions in your worksheet you are going to delay each Calc Cycle by 10ms.

ie. if you use rand() or now() in the worksheet you'll see the values change on each calc cycle / DDE update.

And if you have formulas using both DDE cells and volatile functions you create stack loops that are a bitch... Cardinal Rule: No Volatile Functions Period. Do it in VBA

Once you control the Calc Process you can really get some speed.

 

[syswizard]

Quote from PocketChange:


and a Sum for all symbol// updated in the calc cycle: range("a2") = sum(dde_update_Column)

I understand everything except for the above. Are these the sum of the price quotes coming back ? I don't see how that proves anything because there may be a DDE update to a cell without the price change (side-tick). I guess it all depends on the DDE server; they could remove side-ticks I guess.

The absolute proof IMHO is tick count for an entire day for ES or NQ or YM. Then compare that to CME/CBOT counts.

 

[PocketChange]

Quote from syswizard:

I understand everything except for the above. Are these the sum of the price quotes coming back ? I don't see how that proves anything because there may be a DDE update to a cell without the price change (side-tick). I guess it all depends on the DDE server; they could remove side-ticks I guess.

The absolute proof IMHO is tick count for an entire day for ES or NQ or YM. Then compare that to CME/CBOT counts.

For each symbol with dde cells you have to determine if any of the cells have changed since the last calc cycle.

So you need to add a calc column. ie. ESU9 has 9 updates in its 52 DDE cells.

Then sum the count of symbols with updates.

calc cycle: 52134
symbol updates: 9
Cells updated: 177

The more i think about this... single threading is the reason dde queues up.

When Excel is busy for 10 - 20ms processing updates the incoming updates stack up.


Just so we are talking the same syntax:

CME has TPS, ticks and other measurements.

A tick to us is a change in ask or bid.

Serial updates are changes to any information. Ask Size, Last Trade, Dom etc. Substantially more data.

Which are you referring to?