Time & Sales bottleneck - just one more time..

[JimBecker17]

This is the same request I've made for about four years now, keep trying every year or so. If you want me to code it for free let me know.

The API request will receive the Time & Sales data coming down from the server in a few seconds. So lets say I request one day of BIDU it gets to the WinSig application very quickly.

The movement of this data from WinSig to my application is ONE BAR at a time. Since there are 84,068 trades in BIDU on Friday and the logic is probably doing offset calculations for each call this is VERY VERY slow. It is actually VERY to the tenth power slow.

This is just the trades, the quote data would take days to move over this link. Once the history trades are done realtime imagine there isn't much problem. But going from wanting T&S to getting it over the link is minutes where it could be a few seconds.

The computer being used has plenty of power, it is a software bottleneck that should be fixable in a few hours of someones time. Return an Array or share a file or shared memory. The easiest is to write a file containing the history data to disk then provide RT from there on, no API changes needed but perhaps have some twiddle flag that will write files to some directory.

Just about anything which lets me get lots of Bars at a time would be useful.

Searching via my name you will find more details on this going way back. Thanks.

-Jim

[GenSpoo]

+1 billion

I remember making the same request a long long long long time ago. I used to work extensively with tick data and it wasn't fun just sitting there and waiting for the fill to move across the API. Even more so since you know the data has already made its way across the Internet and is sitting in winsig.

[JimBecker17]

Hi there GenSpoo - Yes you were the first one to take up this torch.... which apparently is sustained only by my occasional flicking of a well used, slightly rusty, Bic lighter that is almost out of juice.

The sad thing is this is such a small amount of work for a big reward. But apparently no one is minding the store anymore with the API. So we have what we have and that's it.

The good news is have been able to make things work correctly with what is released. But the T&S thing certainly inhibits my ability to respond quickly when see some stock want to monitor.

I guess things could be worse.

-Jim

[GenSpoo]

Aye. That was the problem, not that it didn't work exactly, but that I had to change my style of trading based on the API. I have to imagine it will never be changed at this point unless a manager in power deems it a worth while effort. Since I am not actively trading these days, it doesn't bother me a lot. However if I do start trading again, that issue alone will likely sway me to look into alternative feeds.

[scheel]

JimBecker17 and GenSpoo, I don't see your problem. The following VBA code downloads and processes 1-day of AAPL time and sales:

153601 bars in 30.688 sec with loading timeandsales bars to a worksheet
153601 bars in 11.109 secs without loading them into a worksheet

Of course, these profiles depend on what eSignal is otherwise doing. These times were obtained when I ran outside of prime time.

Magic? No, all data stashing is done duing the event. But, you are sure right about dog slow when one "reprocesses" these dumps outside of the OnTimeSalesChanged event. I can't phantom why caching within the event should be faster. Perhaps the API may be able to process eSignal.GetTimeSalesBar(lHandle, lBar) faster when lBar is just pumped down the pipe. It wouldn't surprise me to learn that the API has to refish stuff when it is requested at another time. It would make no sense that walking the bars would cause the processing delays.

Anyway, we're not talking minutes here. I don't know how to (or have permission to) broadcast code in a more accessible way so here goes within this post. In the following, we have a proc (GetTimeAndSales) that does the request. In this proc, I setup a worksheet for the symbol and remember state in a collection, vC, that is indexed by the handle. It is later recalled and used in the event, OnTimeSalesChanged.

More commentary follows the event code.


Public Sub GetTimeAndSales(tsFilter As IESignal.TimeSalesFilter)
Dim nCookie&, vC(nCookieItems) As Variant, sInterval$, sIdx$
Dim i&, sSymbol$
Dim vTsFilter As Variant
Dim ws As Worksheet

sInterval = sBarInterval
nActiveHistoryCnt = nActiveHistoryCnt + 1
nActiveSymbolCnt = nActiveSymbolCnt + 1

nCookie = eSignal.RequestTimeSales(tsFilter) 'fires event to esignal_OnTimeSalesChanged when data ready

If nCookie = 0 Then
'Check validity of symbol.
If Not VerifySymbol(sSymbol) Then
nUErr = nUErr + 1
AddLog sSymbol & " is an invalid symbol"
Else
'what to do? try a couple more times with .5 sec waitstate then trash
For i = 1 To 3
WaitAwhile 0.5
nCookie = eSignal.RequestTimeSales(tsFilter) 'fires event to esignal_OnTimeSalesChanged when data ready
If nCookie <> 0 Then Exit For
Next i
If i > 3 Then
nUErr = nUErr + 1
AddLog sSymbol & " has no data. Cookie=0"
End If
End If
End If

sSymbol = tsFilter.sSymbol
If nCookie <> 0 Then
vC(colSymbol) = sSymbol
ReDim vTsFilter(13)
With tsFilter
vTsFilter(1) = .sSymbol
vTsFilter(2) = .lNumDays
vTsFilter(3) = .bQuotes
vTsFilter(4) = .bTrades
vTsFilter(5) = .bFilterPrice
vTsFilter(6) = .dMinPrice
vTsFilter(7) = .dMaxPrice
vTsFilter(8) = .bFilterVolume
vTsFilter(9) = .lVolume
vTsFilter(10) = .bFilterTradeExchanges
vTsFilter(11) = .sTradeExchangesList
vTsFilter(12) = .bFilterQuoteExchanges
vTsFilter(13) = .sQuoteExchangesList
End With
vC(colTimeSalesFilter) = vTsFilter
vC(colBegTime) = Timer() 'begin clocking
On Error Resume Next
Err = 0
With ThisWorkbook.Sheets
Set ws = .item(sSymbol)
If Err <> 0 Then
Set ws = .Add(, .item(.Count))
ws.Name = sSymbol
Else
ws.Cells.Clear
End If
End With
On Error GoTo 0
'mark other state in sheet memory variables
With ws
.Names.Add BarCount, 0, False
End With
Set vC(colSheet) = ws 'remember sheet for recall in event
vC(colHandle) = nCookie
sIdx = CStr(nCookie)
colCookies.Add vC, sIdx
Else
AddLog sSymbol & " failed. Could not request history"
DecrementActiveSymbolCnt
DecrementActiveHistoryCnt
End If
End Sub



Private Sub eSignal_OnTimeSalesChanged(ByVal lHandle As Long)
Dim vC As Variant, sIdx$
Dim dtChange!, nEr&, k&, j&
Dim sM$, nNewBars&
Dim nElapsed!
Dim lNumBars, lNumRtBars, lBar, lDiff As Long
Dim item As IESignal.TimeSalesData, lLastTsCount&
Dim ws As Worksheet, r As Range, sV$
Dim nm As Name

On Error Resume Next
sIdx = CStr(lHandle)
Err = 0
vC = colCookies.item(sIdx)
If Err <> 0 Then Exit Sub 'ignore

With eSignal
lNumBars = .GetNumTimeSalesBars(lHandle) 'total bars
If lNumBars <= 0 Then Exit Sub 'this can be 0!
lNumRtBars = .GetNumTimeSalesRtBars(lHandle) 'total new bars since last entry. Worthless, unless that is what you really want during event entry
End With

Set ws = vC(colSheet)
With ws
Set nm = .Names(BarCount)
sV = nm.RefersTo
lLastTsCount = CLng(Right$(sV, Len(sV) - 1)) 'strip off =
.Names.Add BarCount, lNumBars 'save state to sheet for later recall. Row count can get mashed when >64K.
End With

If True Then
'read items and save in worksheet
Application.ScreenUpdating = False
nNewBars = lNumBars - lLastTsCount
lDiff = nNewBars * -1
k = lLastTsCount
ReDim vBar(1 To 1, 1 To 6)
With ws
For lBar = lDiff + 1 To 0
item = eSignal.GetTimeSalesBar(lHandle, lBar)
With item
vBar(1, 1) = .DataType
vBar(1, 2) = .dPrice
vBar(1, 3) = .dtTime
vBar(1, 4) = .lFlags
vBar(1, 5) = .lSize
vBar(1, 6) = .sExchange
End With
k = k + 1
If k <= .Rows.Count Then .Cells(k, 1).Resize(1, 6).Value = vBar 'overflow not handled here!
Next lBar
End With
End If

nElapsed = Timer() - CSng(vC(colBegTime))
sM = "Bars = " & CStr(lNumBars) & vbCrLf & "Elapsed VBA time = " & Format(nElapsed, "#,###.##0") & " secs"
frmUpdate.StatusMsg sM, False, False
Application.ScreenUpdating = False
End Sub


You can see there isn't much happening in the event code other than accessing the bars and moving their content into a worksheet. I recall state both from the collection and the worksheet that is referenced in the collection. I determine what is new that has come in, and then I read it and move it to a worksheet. I could have just as easily cached it to a database or an ADO recordset. Recordsets offer a fast way to cache this stuff and later easily recall and move around it. The time needed to create an ADO recordset instead of putting the data in a worksheet only would be marginally different.

This approach is easily generalized into multiple-symbol processing; it need not be done in a linear fashion.

Be happy.

--wcs

[scheel]

Duh. I didn't notice the Attach file button in the reply post. Here is an attachment with the code I put in the last post. Maybe it's a bit easier to follow. I still don't know how to post an Excel workbook.

-wcs

[JimBecker17]

I only check here occasionaly to see if any work has been done by eSignal. It does not appear there have been any API updates since either 2004 or 2005.

The bottleneck is getting T&S one entry at a time. At one point when was trying to get them to do something wrote a sample and profiled it and 97% of the CPU time was spent getting single bars. This could all be done with one data exchange rather than single twinkies via COM.

Your little loop that gets these entries is where all the time takes place. Moving from eSignal to your application.

The data is all on the box at that point but it's being fit through the eye of a needle. This should be a firehose. The intraday data has the same issue but there is a lot less of it.

You just haven't had enough time yet to get frustrated.

-Jim