Spreadsheets speed

Discussion regarding the spreadsheet functionality of Bet Angel.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Mon Sep 11, 2017 8:53 pm

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

nigelk
Posts: 355
Joined: Wed Apr 15, 2009 11:00 pm

Mon Sep 11, 2017 9:11 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: 52
Joined: Fri Sep 08, 2017 10:59 am

Mon Sep 11, 2017 11:33 pm

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

Sun Nov 05, 2017 1:45 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: 935
Joined: Sun Jan 31, 2010 8:06 pm

Sun Nov 05, 2017 10:28 am

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

Sun Nov 05, 2017 11:50 am

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.

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 3 guests