Spreadsheets speed

[Wolf1877]

My final comments on this after yet more experimentation with Guardian refresh into excel - then I'll bow out as I am probably adding far too much noise.

BA Guardian is refreshing events on a roughly 1 second cycle per event (for todays racing events which are currently over 2 hours away).
If I have 20 events in my Guardian list thats a 20 second cycle - even if only 4 of the 20 events are bound to an excel sheet.
This can be speeded up by using Advanced Settings with Restrict refresh. If only 4 of 20 the events are within range it speeds it up to 4 seconds cycle (1 second per event within range).

What I am observing when Guardian refreshes cells a BA excel worksheet (with a set of logical updates) this is actually triggering 6 Worksheet_Change events on my machine.
For each worksheet_change event triggered I am extracting data to a flat file. See the following 6 rows of logged data for a race today for some updates that came in at 11:19:40 after I manually reconnected to excel after a couple of minute delay.

BA Matched amount, BA Last Update, System Run Time, System GetTickCount
15378.77,11:17:38,11:19:40,271625805
15378.77,11:17:38,11:19:40,271625820
15378.77,11:17:38,11:19:40,271625836
15378.77,11:17:38,11:19:40,271625852
15378.77,11:17:38,11:19:40,271625852
15809.87,11:19:40,11:19:40,271625867

You will notice that the BA Matched Amount and BA Last Update cell values are only updated at the end of the process. Which is good for WorkPeter as that signifies that a collection of cell updates are likely to be complete. You will also notice that an approximate gap of 15/16/0 milliseconds shows up between each logging task by observing the system GetTickCount. Its probably taking about 12 or 13 milliseconds for my worksheet_change process to complete (rows 4 and 5 have the same GetTickCount value which is not a true millisecond timer and only resolves to 15 or 16 milliseconds intervals). Clearly excel itself is not slow. The delays are on whatever the BA process is doing to populate the cells but even so this is only taking approximately 50 milliseconds to actually populate the cells from start to finish on my machine at least.

Finally it is obvious Guardian does not look to be a viable option for anyone looking for the fastest processing possible across multiple markets (due to the max 1 second cycling through the guardian events). The good news is that when an individual market is selected in BA - say linked to the Guardian bound to excel sheet "Bet Angel (10)" or whatever - then the data refresh speed for that excel sheet speeds up to max speed. This is true if Guardian auto switches to the market event say 300 seconds or if you manually select the market in BA.

[spreadbetting]

I don't actually use BA for my bots so not sure of what sets of data get sent to excel but you can check which cells are getting updates by simply entering
Debug.Print Target.Address after your Private Sub Worksheet_Change(ByVal Target As Range) event call and your cell updates will display in the intermediate window. From there you can strip out any unneccessary cell updates from running your code by simply setting the code to execute only once a full price refresh has been received or like workpeter only run your coding if C3 has been changed and ore money matched.

[workpeter]

I'll need to have a proper read through your replies when I have a bit more time, but I did flick through and wanted to make a quick comment regarding referencing cell C2. My observations suggested to me that all the BA cells get updated at the same time regardless if the value changes or not, so it didn't really make a difference if I referenced C2 or let's say C3.

[spreadbetting]

Like I say I don't actually use it for my bots so only have access to the Betdaq version which I assume is basically the same. If you look at the target data it actually does 6 writes of data each refresh which is why Wolf1877's timings shows 6 separate entries when he uses an unrestricted worksheet_change event call

$A$1:$B$1
$G$1:$H$1
$C$2:$C$6
$F$2:$F$4
$A$9:$K$18
$T$9:$AE$18

Because your price data is not included until after your $C$2 data it would make more sense for you to trigger your code to run when the A:K price or T:AE blocks had been written to the spreadsheet. At the moment your code is most likly running on the previous price data so it may be wise to change your code away from C2 and set it to run after the last set of data hits excel i.e 12 columns


Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address

If Target.Columns.Count <> 12 Then Exit Sub

' your code

End Sub

[spreadbetting]

You can check to see you're actually botting with the previous refreshes data by using the following codes, it will simply dump the value from G10 into J1. With your current setings you'll see using C2 to trigger any code means your bot is operating on the data from the previous refresh, with the amended code it should be apparent the data is up to date and matching. Slow the refresh rate down for it to become more obvious.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("C2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Range("J1").Value = Range("g10").Value
End If

End Sub


Amended coding

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 12 Then Exit Sub

Range("J1").Value = Range("g10").Value

End Sub

[workpeter]

This looks very interesting, thanks for share, ill have a play with this.

[nigelk]

Just for information.

I made a bot that would start running whenever the race description (B2) changed, as soon as it was detected that a new race was present, it ran a "new race detected" procedure that amongst other things, cleared the list old runners and re-populated with the new.

Very occasionally, I found that the code fired but no runners had been listed. Upon further investigation, it transpired that the code had run before the worksheet had been fully populated.

[Wolf1877]

Like I say I don't actually use it for my bots so only have access to the Betdaq version which I assume is basically the same. If you look at the target data it actually does 6 writes of data each refresh which is why Wolf1877's timings shows 6 separate entries when he uses an unrestricted worksheet_change event call

$A$1:$B$1
$G$1:$H$1
$C$2:$C$6
$F$2:$F$4
$A$9:$K$18
$T$9:$AE$18

My observations seem to indicate that the $C2:$C6 changes are done in the 6th batch of changes in the BetFair version.
I am confident in saying this as the C2, C3 values written into my flat file only got refreshed on the 6th record - probably good news for WorkPeter.
A developer might have "improved" the Betfair code without applying the same changes to the BetDaq version based on your findings.

[Wolf1877]

A further update on the excel spreadsheet update process. I was looking closely at a snap caused by a large Lay bet placed in one of todays racing markets that cleared out several "rungs" of the lay ladder from 4.7 to 5.2.

My excel data capture detected the market volume change (cell C2) and the selection volume changes (cells K10, ....K12 etc) were in sync with the market volume change but the last traded odds (cell K9, ..... K11 etc) were not in sync and neither were available Back/Lay ladder prices which were unchanged values from the previous excel refresh cycle before the market snap and still showing 4.6/4.7 as the Back/Lay price.

I happened to have a video recording of the ladder data in BA so I was able to step through frame by frame to see exactly what happened on the Bet Angel screen. Clearly Bet Angel had accurate visibility of the Back/Lay situation immediately post market snap but this was not reflected in the subsequent excel refresh. Spreadbetting previously observed that cells A9:K18 were updated together. That may be the case but what I am detecting is that cell K10 (.....K12 etc) is updated with accurate market data before cell K9 (....K11 etc) and cell range E9:J10 (....E11:J12 etc ) has been properly refreshed with accurate market data.

All I would say to traders is be careful of relying on the excel last traded price and back/lay prices in the milliseconds immediately following a market volume change and most especially following a large market snap as there seems to be a slight lag in refreshing this data. Traders might want to do their own checks on this but I'm pretty confident in my assertion. I was working with Guardian linked excel sheets on a 100ms refresh cycle with restricted refresh of 1200/900 giving an excel refresh cycle of approx 1/3 of a second per market. My video was at a faster frame rate than the excel refresh.

[spreadbetting]

Wolf my observations were just on the Betdaq version. It's easy enough to log the data timings with gettickcount and Printing the addresses to check the orders of data



Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address

End Sub

[Wolf1877]

Spreadbetting, thanks for the code suggestion. At this stage it is enough for me to know that excel market volume and excel market back/lay odds data are not necessarily updated completely in sync and I mainly wanted to warn others.

[sa7med]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("C2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Range("J1").Value = Range("g10").Value
End If

End Sub

a bit off topic here but why would you declare keycells and initialize it instead of just writing :

"If Not Application.Intersect(Range("C2"), Range(Target.Address)) _
Is Nothing Then"

assuming you're going to use Range("C2") only once?

[nigelk]

It's for ease of identification and easier to understand.

For example, range("C4") hold the number of runners. If you call it "Runners" then

if Runners>=10 then..........

It's easier to understand then

If Range("C4")>=10 then.........

So if you have lots of comparing to do it's something like

If Runners>=10 and CurrentPrice>=2.50 and Overround<=101% etc etc

[ShaunWhite]

It's for ease of identification and easier to understand.

+1
Or set all of your declarations and constants in a single Init routine somewhere. It's often surprising what bugs leap out at you when you can see everything in one place.

[Wolf1877]

It's quite interesting for me looking back at this thread. It started up in the very first week I used Bet Angel. Reading my posts from back then are a little bit cringe because I clearly didnt have a clue of the various options available within BA to fine tune it. I wasnt even using streaming at the time because BA installed with polling set on by default!

I'm now looking back now with more experience of BA/Guardian/Excel. What's really jumping out at me is back then that Peter even with a lot of tweaking only managed to get his single sheet excel cycle down to around 125ms with his streaming speed set to 20ms. I think Peter was hoping that he could get it down to closer to 20ms.I achieve similar speeds of 125ms now and that is with running 2 instances of BA/Guardian/Excel set up with multiple sheets and I only use a refresh setting of 100ms to avoid thrashing my laptop because I'm already running way too much other background stuff on it. In the words of Scotty from Star Trek - "She cannae take any more Captain"!

Looking back now I think the key factors identified at the time on the excel VBA cycle speed were:
(1) Peter identified that there is some background disk IO when BA copies data into excel so having a SSD hard drive will help with any IO speed and hence overall cycle speed. I'd assume that more RAM on a PC would also help. I run with 8GB and I should really upgrade to 16GB min. If anyone is using 4GB or less then I'd certainly suggest more RAM would help speed.
(2) Having an excel window open/focused on with a dynamically updated worksheet seems to slow things down
(3) Having a BA ladder window open and focused also seems to slow things down - more ladders slows things down even more - my ladders are definitely a bit clunky so maybe I need to use 2 PC's
(4) You might be able to tweak the windows CPU priority of excel tasks within windows itself
(5) Other than that its down to the efficiency of your excel VBA code (or DLLs) and excel refresh itself. i.e. excel calculations, formulae etc. I personally avoid all excel calculations.

Other features that I have observed
(1) Bet Angel excel updates seem to be locked out if it tries to refresh cells whilst a macro is still running (unless DoEvents is coded to yield to the Windows operating system).
(2) The last traded price and runner volume updates feed sometimes feed through in advance of ladder prices/values changing. I assume this is because of the way that Bet Angel is coded.

I wonder what progress Peter has made in optimising his Excel set up.