Excel Automation

[PocketChange]

DDE request is initiated by OEC.Functions Add In:

=QuotesBySymbol(B1,K4:Y4,J5:J99)

Function builds a table array in excel and places a formula in each cell... Slow to initialize but updates very fast.

Using Excel 2007 which further optimizes recalcs. Full recalc of worksheet takes 0.15102 seconds.

ie.
=Acct#|quote!ESU8?Last =Acct#|quote!ESU8?Bid

TT uses arrays pricedepth90 and stores to memory variable.
TT RTD Throttle is set to zero which sends data as fast as the PC will process. Can't find any DDE Throttle.

With OEC Feed received 77,273 price data updates and 2,592 changes in bid/ask in past 14 hours for ES.

Serial Tick ID Session Time
77273 2592 2008-08-29 14:01:55.43

Underlying ESU8 Data is fine but the option chains do not fully update via OEC DDE or TT RTD. CME states they provide level II DOM data for each option but nothing more than current bid or ask are displayed using either TT or OEC.

I suspect the Level II option data is not being processed or delivered by either TT or OEC.

In the end i do not believe there is a significant performance difference using either rtd or dde. More dependent on your worksheet design and formulas.

 

[syswizard]

Quote from PocketChange:

With OEC Feed received 77,273 price data updates and 2,592 changes in bid/ask in past 14 hours for ES.

Serial Tick ID Session Time
77273 2592 2008-08-29 14:01:55.43

In the end i do not believe there is a significant performance difference using either rtd or dde. More dependent on your worksheet design and formulas.

How did you determine the above statistics ? What about the TT stats as a comparison ?
Also, a comment on RTD vs. DDE:
one huge difference is RTD can place data DIRECTLY into a variable or even an array. This is doable with DDE only with a DDERequest statement that must be called on a timer interval.

 

[PocketChange]

Quote from syswizard:

How did you determine the above statistics ? What about the TT stats as a comparison ?
Also, a comment on RTD vs. DDE:
one huge difference is RTD can place data DIRECTLY into a variable or even an array. This is doable with DDE only with a DDERequest statement that must be called on a timer interval.

Recalc timer is MS microtimer macro using cpu cycles for accuracy to the 5's... Worksheet captures price data - calculates stats - places orders and saves all data to DB.

Serial is a worksheet calculation incremented upon change of any data... most serial changes are due to level ii quantity updates.

Ticks are incremented upon each change of Bid or Ask.

Using a circular referenced formula trick... Optimizes DDE and RTD but really complicated to explain. Three step iterative calc to error check data and order submissions. Place, Confirm, Verify... 3 cycles...

100ms order buffer needed for TT, OEC doesn't need the Buffer delay but takes 100ms for confirmation.

The price data is received and processed 3 times faster than the orders can be placed and managed. All processing is in ram, buffered and then logged to DB. No Disk I/O... DB Lookups ... So far its the Fastest Price Inflection Algo Trading System we can reasonably execute.

Don't have TT Stats for today... Tick updates are close but Serials are way off. Working with CME and TT to track down the Options Data issue. Suspect the TT Order Time Buffer may be delaying RTD updates.

 

[powersignals]

Quote from futures_shark:

It is true that there is no excel function that can store a history of live streaming prices. But that doesn't mean it's impossible. It's possible to write custom VBA code that will get the job done.

My excel does store all the data from streaming prices and it´s not the 2007 version. I use 2003 Excel.
Excel is able to do everything , I mean everything like a C++ application in terms of speed and calcs. And it´s much better than a c++ application. My worksheet is capable of doing 100 million calculations per second with CPU usage at 30% .

 

[syswizard]

Quote from PocketChange:

Recalc timer is MS microtimer macro using cpu cycles for accuracy to the 5's... Worksheet captures price data - calculates stats - places orders and saves all data to DB.

Excuse, but I'm a bit skeptical here....as above you mention a database, and below you indicate NO disk IO ??? You are using a ram-based database then ? Which one ?

The price data is received and processed 3 times faster than the orders can be placed and managed. All processing is in ram, buffered and then logged to DB. No Disk I/O... DB Lookups ... So far its the Fastest Price Inflection Algo Trading System we can reasonably execute.

Don't have TT Stats for today... Tick updates are close but Serials are way off. Working with CME and TT to track down the Options Data issue. Suspect the TT Order Time Buffer may be delaying RTD updates.

When you say "serials", this means "time and sales" data (date,time,price,volume)? Correct ?

 

[syswizard]

Quote from powersignals:

Excel is able to do everything , I mean everything like a C++ application in terms of speed and calcs. And it´s much better than a c++ application. My worksheet is capable of doing 100 million calculations per second with CPU usage at 30% .

Excel is single threaded architecture (STA). Unless you are calling custom-made "C" DLL routines, I find the above statistic pretty hard to believe.
Can you prove the above ?

 

[PocketChange]

Quote from syswizard:

Excuse, but I'm a bit skeptical here....as above you mention a database, and below you indicate NO disk IO ??? You are using a ram-based database then ? Which one ?

When you say "serials", this means "time and sales" data (date,time,price,volume)? Correct ?

Serials is a counter that increments upon change of any information in the price feed that updates a calculation in the worksheet. Mostly all of the quantity level ii updates. Not logged to DB but used for trade decision calculations.

Ticks are incremented upon change of either current bid or ask price and a snapshot of all data and calcs are logged.

Orders are released based on serial updates which will also trigger a record out to the DB.

Actually data is just written to a text file from excel using MS's writelog function and another server polls the directory and updates the db.

Suppose I can capture every serial to DB... Doesn't fit our data purpose... Just interested in capturing Real time market executable prices.

Sub WriteLogFile(msg)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, TF, fn

'add reference to Microsoft Scripting Runtime to your VBA Project
Set fso = CreateObject("Scripting.FileSystemObject")

'set file name
fn = ActiveWorkbook.Path & "\" & "XT" & Format(Now(), "MMDDYY") & ".CSV"

'if file doesn't exist, create, otherwise open and append
Set TF = fspenTextFile(fn, ForAppending, True)

'write a line of text with automatic carriage return/linefeed
TF.WriteLine Format(Now(), "hh:mm:ss") & " : " & ActiveWorkbook.Name & " : " & CStr(msg)
TF.Close

End Sub

Function WriteLog(msg As String, ToF As Boolean, FileName As String, index As Integer)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, TF, fn
If ToF And StrComp(msg, lstMsg(index), vbTextCompare) <> 0 Then
lstMsg(index) = msg
'add reference to Microsoft Scripting Runtime to your VBA Project
Set fso = CreateObject("Scripting.FileSystemObject")

'set file name
fn = ActiveWorkbook.Path & "\LOGS\" & FileName & ".CSV"

'if file doesn't exist, create, otherwise open and append
Set TF = fspenTextFile(fn, ForAppending, True)

'write a line of text with automatic carriage return/linefeed
TF.WriteLine Format(CStr(msg))
TF.Close
WriteLog = msg
Else
WriteLog = "Logging"
End If
End Function

The timer is as precise as the function... the serial calcs and ticks are based on the feed and may be off... we are having data integrity issues.

The only thing we are doing out of the box is using circular referenced formulas to force updates.

 

[PocketChange]

2003 has issues... 2007 is worth moving to.

Quote from powersignals:

My excel does store all the data from streaming prices and it´s not the 2007 version. I use 2003 Excel.
Excel is able to do everything , I mean everything like a C++ application in terms of speed and calcs. And it´s much better than a c++ application. My worksheet is capable of doing 100 million calculations per second with CPU usage at 30% .

 

[shortie]

Quote from PocketChange:

2003 has issues... 2007 is worth moving to.

i heard excel 2007 has ton of bugs. it is good to see somebody actually likes it. i am worried about moving to 2007 just to suffer and to have to move back to 2003 later.

 

[PocketChange]

Depends on your needs... if its not broken....

Main thing ifor us was support for worksheets larger than 65000 rows and recalcs were optimized and are much faster. I'm sure there are bugs but for now all is working. Reality is we just use just a handful of functions for a specific trading need.