Guardian -> Excel update speeds

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Re: The update of excel from Guardian

The maximum refresh rate is 20ms and I've no doubt that updates are applied at that speed. However I'm starting to think the refresh rate of the data that's presented in that update is updated much slower. Is the api stream it being batched or throttled ?

I'll explain....
I've written a lightweight proceedure that polls the Bet Angel sheet.
I've got 1 market in Guardian
It checks the sheet approx 500 times/s (each does a DoEvents)
When a volume change is found it increments a counter
Each second that counter is displayed and the counter is reset

..It's never shown more than 3 ? (Obv 50 being the max I expect to detect)

How often is the data Guardian is using being refeshed?

(I know this may be a niche issue, but it's not just an exercise, amongst other things I'm trying to count the number of transactions occuring in the market)
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Peter, I seem to remember you once mentioning BF's average trade size.

Do you use something bespoke to count it (assuming the API contains every market event), I'm not convinced you could have done it using BA if what I'm seeing is correct.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

mmm i just had a 6.

Maybe it's the api playing up or there's just been some very slow days. Not much of that activity on the ladders is trades by the look if it.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Hi Shaun, My peak excel data capture over the last few days has been 8 frames of excel data per second though I average capturing about 3 or 4 frames of excel data a second. I can see from my SQL database that I have have previously peaked at 11.

NOTE:
(1) I only capture data when the race traded volume changes
(2) I currently use Guardian set on a 100ms refresh to avoid thrashing my machine so my theoretical maximum is now only 10.

My excel workbook has Bet Angel (1) to Bet Angel (70) so the number of worksheets isnt massively slowing it down except except on really busy days.
I use Advanced Settings > Restrict Refresh > 1200/1200 to limit the races to log 20 minutes pre off through til end of inplay.
I also have a separate instance of Bet Angel/Guardian to race log data from 6am for until 20 minutes pre off without the restrict refresh.

I invoke my VBA with the following code for each of the 70 worksheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2:$C$6" Then
Call LogData(1) ' <======= Hard coded Call Logdata(1) to Call Logdata(70) for each Bet Angel (1) to Bet Angel (70) sheet.
End If
End Sub

then I have a VBA module with the following

Sub LogData(s As Integer)
' my private VBA code goes here
End Sub


I know you run your process very differently which in theory should allow you a greater refresh rate. Clearly not delivering that though for some reason.
I think I have mentioned before though (and you disagreed) that my observations are that Guardian will not refresh the excel data while a VBA macro code is running or until your VBA macro yields to the operating system. I believe that the Guardian just gives up a frame refresh until the next refresh if your VBA polling code happens to be running and not yielding causing you to drop frame refreshes. I'm not sure if Bet Angel Guardian has that coded deliberately or if it is a feature of .NET Excel Interop which I suspect is the interface Bet Angel uses to refresh the Excel data. Try increasing the time that your VBA yields and slowing your excel polling down as it may ultimately allow a faster excel data frame refresh.

I have queried my SQL database and here are my maximum frames per second for all races since Saturday. As you can see things do slow down a bit on a busy race day and things speed up at the start and end of the racing day as the number of races being refreshed in guardian reduces. I
Do keep in mind this is with Guardian Streaming Refresh rate set to 100ms and I also run way too much stuff concurrently on my ageing i5 laptop so I suspect I could ramp this refresh rate up if I upgraded my laptop, migrated some processes off to a server etc. Always too busy though!

maxseq - RaceDateTm - VenueCode
8 2018-06-25 21:10:00 Wind
5 2018-06-25 21:00:00 Kilb
5 2018-06-25 20:50:00 Wolv
5 2018-06-25 20:40:00 Wind
5 2018-06-25 20:30:00 Kilb
5 2018-06-25 20:20:00 Wolv
5 2018-06-25 20:10:00 Wind
5 2018-06-25 20:00:00 Kilb
5 2018-06-25 19:50:00 Wolv
4 2018-06-25 19:40:00 Wind
5 2018-06-25 19:30:00 Kilb
4 2018-06-25 19:20:00 Wolv
5 2018-06-25 19:10:00 Wind
5 2018-06-25 19:00:00 Kilb
5 2018-06-25 18:50:00 Wolv
4 2018-06-25 18:40:00 Wind
5 2018-06-25 18:30:00 Kilb
5 2018-06-25 18:20:00 Wolv
5 2018-06-25 18:10:00 Wind
5 2018-06-25 18:00:00 Kilb
4 2018-06-25 17:50:00 Wolv
6 2018-06-25 17:45:00 Sthl
8 2018-06-25 17:15:00 Sthl
8 2018-06-25 17:00:00 Chep
5 2018-06-25 16:45:00 Sthl
8 2018-06-25 16:30:00 Chep
7 2018-06-25 16:15:00 Sthl
8 2018-06-25 16:00:00 Chep
7 2018-06-25 15:45:00 Sthl
7 2018-06-25 15:30:00 Chep
8 2018-06-25 15:15:00 Sthl
8 2018-06-25 15:00:00 Chep
7 2018-06-25 14:45:00 Sthl
7 2018-06-25 14:30:00 Chep
6 2018-06-25 14:15:00 Sthl
7 2018-06-25 14:00:00 Chep
8 2018-06-24 17:40:00 Worc
8 2018-06-24 17:10:00 Worc
4 2018-06-24 17:05:00 GowP
4 2018-06-24 17:00:00 Ponte
4 2018-06-24 16:50:00 Hex
5 2018-06-24 16:40:00 Worc
4 2018-06-24 16:35:00 GowP
4 2018-06-24 16:30:00 Ponte
4 2018-06-24 16:20:00 Hex
5 2018-06-24 16:10:00 Worc
3 2018-06-24 16:05:00 GowP
3 2018-06-24 16:00:00 Ponte
4 2018-06-24 15:50:00 Hex
5 2018-06-24 15:40:00 Worc
3 2018-06-24 15:35:00 GowP
4 2018-06-24 15:30:00 Ponte
4 2018-06-24 15:20:00 Hex
5 2018-06-24 15:10:00 Worc
3 2018-06-24 15:05:00 GowP
3 2018-06-24 15:00:00 Ponte
4 2018-06-24 14:50:00 Hex
4 2018-06-24 14:40:00 Worc
4 2018-06-24 14:35:00 GowP
4 2018-06-24 14:30:00 Ponte
4 2018-06-24 14:20:00 Hex
5 2018-06-24 14:10:00 Worc
4 2018-06-24 14:05:00 GowP
4 2018-06-24 14:00:00 Ponte
3 2018-06-24 13:50:00 Hex
8 2018-06-23 21:00:00 Ling
7 2018-06-23 20:45:00 Hayd
7 2018-06-23 20:30:00 Ling
7 2018-06-23 20:15:00 Hayd
7 2018-06-23 20:00:00 Ling
7 2018-06-23 19:45:00 Hayd
7 2018-06-23 19:30:00 Ling
7 2018-06-23 19:15:00 Hayd
7 2018-06-23 19:00:00 Ling
7 2018-06-23 18:45:00 Hayd
6 2018-06-23 18:30:00 Ling
4 2018-06-23 18:15:00 Hayd
5 2018-06-23 18:10:00 Perth
3 2018-06-23 18:00:00 Ling
4 2018-06-23 17:55:00 Newm
3 2018-06-23 17:50:00 Redc
2 2018-06-23 17:45:00 GowP
3 2018-06-23 17:40:00 Perth
3 2018-06-23 17:35:00 Ascot
3 2018-06-23 17:30:00 DownR
3 2018-06-23 17:25:00 Ayr
3 2018-06-23 17:20:00 Newm
3 2018-06-23 17:15:00 Redc
2 2018-06-23 17:10:00 GowP
3 2018-06-23 17:05:00 Perth
3 2018-06-23 17:00:00 Ascot
3 2018-06-23 16:55:00 DownR
3 2018-06-23 16:50:00 Ayr
3 2018-06-23 16:40:00 Newm
3 2018-06-23 16:35:00 Redc
3 2018-06-23 16:30:00 GowP
3 2018-06-23 16:25:00 Perth
3 2018-06-23 16:20:00 Ascot
3 2018-06-23 16:15:00 DownR
3 2018-06-23 16:10:00 Ayr
3 2018-06-23 16:00:00 Newm
3 2018-06-23 15:55:00 GowP
3 2018-06-23 15:50:00 Redc
3 2018-06-23 15:45:00 Perth
3 2018-06-23 15:40:00 Ascot
3 2018-06-23 15:35:00 DownR
2 2018-06-23 15:30:00 Ayr
2 2018-06-23 15:25:00 GowP
2 2018-06-23 15:20:00 Newm
3 2018-06-23 15:15:00 Redc
3 2018-06-23 15:10:00 Perth
3 2018-06-23 15:05:00 Ascot
3 2018-06-23 15:00:00 DownR
3 2018-06-23 14:55:00 Ayr
3 2018-06-23 14:50:00 GowP
3 2018-06-23 14:45:00 Newm
3 2018-06-23 14:40:00 Redc
3 2018-06-23 14:35:00 Perth
3 2018-06-23 14:30:00 Ascot
2 2018-06-23 14:25:00 DownR
3 2018-06-23 14:20:00 Ayr
3 2018-06-23 14:15:00 GowP
3 2018-06-23 14:10:00 Newm
3 2018-06-23 14:05:00 Redc
3 2018-06-23 14:00:00 Perth
3 2018-06-23 13:55:00 DownR
3 2018-06-23 13:45:00 Ayr
4 2018-06-23 13:40:00 GowP
4 2018-06-23 13:35:00 Redc
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Tue Jun 26, 2018 6:41 pm
I think I have mentioned before though (and you disagreed) that my observations are that Guardian will not refresh the excel data while a VBA macro code is running or until your VBA macro yields to the operating system.
Thanks for all that,

No I agree, I'm yielding almost 1000 time a second. And I also agree about keeping Guardian tidy even when the restrict refresh is set tight.

Those numbers you have are pretty good seeing as you are polling so much data, and as your check is on vol change then you're getting some reliable figures.

I think my issue has been mainly with the API today. I'm getting 5s and 6s now even though it's not especially busy. I'll give it a few days and see how it goes.

thx again
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Shaun

Yep, definitely reliable figures. Here are the 8 individual Market Matched data figures in a single second from last nights 21:10 at Windsor from 252 seconds before the start.
I've got the individual runner data as well but just showing the market matched data here for brevity.

RaceDatetm, VenueCd, Date Captured, SecsToStart, TickCount, MarketMatched
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.110 252 162783236 192133.68
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.250 252 162783377 192140.66
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.377 252 162783502 192386.66
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.500 252 162783626 192529.64
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.623 252 162783751 192561.09
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.750 252 162783876 192589.65
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:47.873 252 162784001 192603.65
2018-06-25 21:10:00.000 Wind 2018-06-25 21:05:48.000 252 162784126 192653.62
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Shaun

I have been doing some performance tests in Guardian today.
I set up a new guardian workbook with a single Bet Angel sheet and then I set my Guardian refresh time to 20ms.

Note that Guardian refreshes cell data in 6 batches of cells, the last one of which is cells C2:C6
My LogData code is called by Worksheet_Change only when Guardian refreshes cells C2:C6 as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2:$C$6" Then
Call LogData(1)
End If
End Sub

I changed my existing production LogData code to capture data regardless of if the market volume changed and I ran tests extracting 200 frames of excel market data to a flat file on SSD. The fastest I could get that code to run was averaging 12.4 cycles per second.
I then removed the disk-io OPEN/PRINT/CLOSE VBA statements but left my all of my other code intact - and it was still averaging around 12.4 excel refresh cycles per second (.... i.e. the SSD disk IO had negligible impact on my speed test)

I then changed my LogData VBA code so that it did only minimal processing needed to track how frequently the refresh cycle was being triggered.
I ran 3 tests of 200 cycles which ran in between 7.8 and 8 seconds. So with absolute minimal VBA code to perform tracking I was averaging 25.3 refresh cycles per second - around half the theoretical maximum of 50. I also note that 25.3 is not far off double my standard code with disk io speed of 12.4 frames per second so maybe alternate frames of data are being skipped by my additional standard code.

My tests seem to indicate that a higher level of processing being performed in a VBA macro slows down the Guardian refresh rate.
Nero Tulip
Posts: 686
Joined: Wed Apr 15, 2009 5:29 pm

Great topic guys. I want to implement my own recording to SQL, but not sure how to go about it... perhaps this is worthy of a different starting post. This sort of thread is much appreciated though, as a heavy guardian / excel user... reading with interest.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Nero Tulip wrote:
Wed Jun 27, 2018 9:22 am
Great topic guys. I want to implement my own recording to SQL, but not sure how to go about it... perhaps this is worthy of a different starting post. This sort of thread is much appreciated though, as a heavy guardian / excel user... reading with interest.
Extracting data from Guardian/Excel and loading it into SQL is is a very broad topic that I wouldnt want to get into too deeply on a Bet Angel forum.

If you are already competent at coding and using VBA macros in Guardian/Excel then as a general starting place I would suggest that you first capture data from the Bet Angel worksheets and write it out to flat files (I would suggest .csv or .txt file format). You can assign cell values into strings and then use VBA OPEN, PRINT and CLOSE statements within VBA to open a file, write data to it and close it.

You would need to install a version of SQL (the main ones are Microsoft SQL Server and the open source MySQL). Learning about SQL can be quite a big and complex area area if you are unfamiliar and not an IT professional or at the very least a very enthusiastic amatuer IT geek. When you have extracted your data out of excel, have have installed SQL and learned the basics then you can look at importing your flat file data into SQL. One off manual imports are fairly simple but automating the processes to load and process data every day is more complex. Finally you can start thinking about how to further organise and deploy your data further when it is in SQL.

If you are confident that you can meet those challenges then I do recommend having the data in SQL but it does take quite a lot of commitment and generally isnt something you can do in just a few days. I've spent months on it and I do have a professional IT background. If you decide to take the plunge then good luck.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Wed Jun 27, 2018 9:11 am

I changed my existing production LogData code to capture data regardless of if the market volume changed and I ran tests extracting 200 frames of excel market data to a flat file on SSD. The fastest I could get that code to run was averaging 12.4 cycles per second.
I then removed the disk-io OPEN/PRINT/CLOSE VBA statements but left my all of my other code intact - and it was still averaging around 12.4 excel refresh cycles per second (.... i.e. the SSD disk IO had negligible impact on my speed test)

I then changed my LogData VBA code so that it did only minimal processing needed to track how frequently the refresh cycle was being triggered.
I ran 3 tests of 200 cycles which ran in between 7.8 and 8 seconds. So with absolute minimal VBA code to perform tracking I was averaging 25.3 refresh cycles per second - around half the theoretical maximum of 50. I also note that 25.3 is not far off double my standard code with disk io speed of 12.4 frames per second so maybe alternate frames of data are being skipped by my additional standard code.

My tests seem to indicate that a higher level of processing being performed in a VBA macro slows down the Guardian refresh rate.
Thx Wolf,
I'm not looking for specific changes, i just poll it as fast as possible (with a doevents in the loop), reading a 2 cells checking that F4 is positive and comparing K10 it with it's previous value. I also don't store the results as I go, I save my changes to a 2 dimentional array and then write this to a separate sheet only when the market changes. I'm reading the cells at about 120-150/s ReadCyles and the maximum K10Updates has been as high is 8. Like I said I've no doubt BA is updating at 20ms but was concerend that even at peak activity, it might not contain 50 different updates in that second (obv depends on the market).

I might do some tests to use change(target address), ie to see if waiting for excel to tell me about changes performs better than me continually looking for them.

You're right about sql being the way to go if you want to store a lot of info, but for what I'm doing at the moment I don't need to retain the info after the event is done, it's just live monitoring of various things.

It's a shame C3 isn't the full time with milliseconds, but as usual, there's workarounds.

I'm still curious about an answer to this below, is it an actual definate figure, or a damn good approximation based on a rapid sampling. Either is fine but it would answer the question about how rapid that sampling could be.
ShaunWhite wrote:
Tue Jun 26, 2018 4:56 pm
Peter, I seem to remember you once mentioning BF's average trade size.

Do you use something bespoke to count it.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Why do you want to check the sheet 500 time a second? I can't imagine Betfair even stream the data at that speed. Surely just using streaming would be much more efficient, streaming is supposed to push to BA so not as if you'd miss any changes as I'd imagine it will also buffer data, the API data is also timestamped but dunno if BA checks the stamp times or sends that data to excel.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

spreadbetting wrote:
Wed Jun 27, 2018 4:41 pm
Why do you want to check the sheet 500 time a second? I can't imagine Betfair even stream the data at that speed. Surely just using streaming would be much more efficient, streaming is supposed to push to BA so not as if you'd miss any changes as I'd imagine it will also buffer data, the API data is also timestamped but dunno if BA checks the stamp times or sends that data to excel.
I don't need to, it was just a measure to see if i was under the 50/s. I'm at about 100/s now that i've got 50 cells and 3 charts being updated.
I'm using streaming, the timestamp doesn't come through. I'm happy with the aprroximation i'm getting now that I've seen more trn/s coming through. I think the api is just a bit sticky atm.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

The API is always gonna be under pressure with the WC and tennis on I guess.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I probably can't see for looking but is there anything obvious with this code?

It works great for changes to L10 that I key manually but it's not spotting any changes to F4 or K10 when BA is connected?

It's coded this way becuase I only want to spot changes to the sheet i'm currently interested in ie SourceSheet$ and CurrMktNum#
The main code loop (while recording is active) looks for Changed(CurrMktNum) <> 0 and calls my processing.


Private Sub Worksheet_Change(ByVal Target As Range)
ChangeCycles = ChangeCycles + 1
DoEvents
If SourceSheet <> "" Then
If Target.Address = Sheets(SourceSheet).Range("F4").Address Then
Changed(CurrMktNum) = 1
End If

If Target.Address = Sheets(SourceSheet).Range("K10").Address Then
Changed(CurrMktNum) = 2
End If

If Target.Address = Sheets(SourceSheet).Range("L10").Address Then
Changed(CurrMktNum) = 3
End If
End If
End Sub
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I don't use BA but what gets sent to those cells? Wsheet_change won't recognise a calculate event, do they contain formulas rather than having data sent to them?

You can always switch them within the code to

Code: Select all

If  Range("L10").Value ="whatever you're looking for"  Then
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”