Spreadsheets speed

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I think he's looking more for the time it takes for BA to refresh the data rather than how long your VBA within the sheet is taking. So the timer is started at the end of your worksheet_change event and then output to the sheet or debug.print at the start of it to catch the refresh rate from BA
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

spreadbetting wrote:
Sun Sep 10, 2017 5:12 pm
I think he's looking more for the time it takes for BA to refresh the data rather than how long your VBA within the sheet is taking. So the timer is started at the end of your worksheet_change event and then output to the sheet or debug.print at the start of it to catch the refresh rate from BA
OK my misunderstanding was based on the opening sentence of the thread - "I would appreciate if you could share with me the speed of your VBA code per cycle." I agree on closer look that the code suggested looks more like trying to measure the delay between worksheet_change being invoked (by a BA data refresh) rather than the speed of the VBA code.

I also assume this is for standard BA (non Guardian) excel refresh as the suggested code is not relevant for multiple worksheet updates as far as I can see. i.e. say sheet 4 gets updated by BA Guardian and then sheet 6 is next in the cycle, the sheet 6 worksheet_change task thread just picks up the GetTickCountOffset2 global value set by the sheet 4 worksheet_change task thread and the result is pretty meaningless.

I'll take another look at this tomorrow. I'd also be interested how the quoted figure of 20ms was determined for refreshes within standard BA itself.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Whilst I don't use multiple sheets, the same logic should apply. We can do it on one sheet or many. Yes, GetTickCountOffset2 is declared globally but it's also set to private so its only seen within the sheet/module is declared within. This will be an interesting experiment with multiple sheets because you should notice the offset on each sheet slow down as you add more markets. Whilst using 1 sheet will give you the fastest response. the shortest gap between cycles I have seen on my computer is around 70-90ms.

I think having a SSH is defiantly advantageous due to the many IO calls betangel makes to excel.exe (see attachment).
You do not have the required permissions to view the files attached to this post.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

workpeter wrote:
Sun Sep 10, 2017 8:08 pm
Whilst I don't use multiple sheets, the same logic should apply. We can do it on one sheet or many. Yes, GetTickCountOffset2 is declared globally but it's also set to private so its only seen within the sheet/module is declared within. This will be an interesting experiment with multiple sheets because you should notice the offset on each sheet slow down as you add more markets. Whilst using 1 sheet will give you the fastest response. the shortest gap between cycles I have seen on my computer is around 70-90ms.
If I am understanding your code correctly, your computation is only being performed when the Matched total changes based on the intersect logic, however Excel is triggering the Worksheet_Change process whenever excel detects that ANY SINGLE CELL has changed within the worksheet. And then your code disregards the detected change unless the C2 cell has changed. BA is refreshing cells a lot more frequently than it changes cell C2 (matched total). At least this is what I am observing.

I'd suggest that you move your GetTickCountOffset2 assignment after the End If and before the End Sub to get a more accurate calculation of how long excel is taking to process each individual WORKSHEET_CHANGE task. In my case it is between 0 and 16 ms. Now it might be that excel is slowing things down by being a little too quick off the mark in running WORKSHEET_CHANGE too frequently! Say for example that BA updates 50 cells in a worksheet for a logical collection of updates. Ideally Excel would trigger WORKSHEET_CHANGE just the once after the full group of 50 cells has updated. In practice Excel seems to detect several small groups of cell changes and triggers WORKSHEET_CHANGE many times over rather than just once. Your logic then disregards the change unless cell C2 has changes. I'm not aware of any options available in excel to control or throttle back Worksheet_Change to optimise triggering.

Obviously Excel is a great tool but fundamentally it was designed for general number crunching purposes rather than handling millisecond critical time sensitive control processes like BA automation. Excel still manages to cope pretty well though with it all things considered!

Anyway my code suggestion to calculate the time since the last WORKSHEET_CHANGE excel process.

'your private code

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End If
GetTickCountOffset2 = GetTickCount() '<--- Move your GetTickOffsetCount2 assignment outside the IF THEN ..... ENDIF logic so it refreshes every time the process runs.
End Sub
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

A small addendum to my immediate previous post.

Looking into it further flipping Application.EnableEvents between False and True in your code is temporarily switching off new "Worksheet_Change" events and does throttle it back..
I assume that this is because your code might be loading BA commands into columns L to O and you dont want to trigger BA again - however - this also is potentially slowing down Worksheet_Change from being triggered when C2 would have been updated and slowing down your response.

I'm not sure if this would be because
  • BA deliberately does not update the BA sheet cells while your code is in progress[ or if
    BA cannot update the BA sheet cells while your code is in progress due to locking (so it gives up) or if
    BA does update the BA sheet cells while your code and it goes undetected because Application.EnableEvents is switched off
What I am seeing without Application.EnableEvents switched off at all in my code is a typical delay of between 0 and 16 milliseconds between the end of one Worksheet_Change and the start of a new Worksheet_Change task (for the same worksheet). I'm now storing the GetTickCount() long value from the end of a Worksheet_Change in my independent "RaceSummary" worksheet and then reading it back at the start of a new Worksheet_Change event to compare timings. I should further add that I'm not currently assigning BA commands into columns L to O so I have no concerns about worksheet_change being re-triggered by my own assignments.

Logically It might be better to have two computers running linked to BA, 1 to receive incoming data from BA only and 1 to load commands into BA only!
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

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.
Last edited by Wolf1877 on Mon Sep 11, 2017 6:35 pm, edited 1 time in total.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

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.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

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
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

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
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

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
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

This looks very interesting, thanks for share, ill have a play with this. :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

spreadbetting wrote:
Mon Sep 11, 2017 7:41 pm
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
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

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
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

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
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”