Spreadsheets speed

Discussion regarding the spreadsheet functionality of Bet Angel.
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
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 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.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

spreadbetting wrote:
Mon Sep 11, 2017 8:05 pm

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
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Sun Apr 22, 2018 12:42 pm
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
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

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.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

Just out of curiosity, has anyone experimented with multithreading solutions for their VBA code? ie hacking vba so that it uses more than one core? From some brief research it seems there are a couple of ways to do it
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

sa7med wrote:
Sat Aug 11, 2018 8:28 pm
Just out of curiosity, has anyone experimented with multithreading solutions for their VBA code? ie hacking vba so that it uses more than one core? From some brief research it seems there are a couple of ways to do it
Can't imagine you get any significant speed increase as you're always dependant on BA for submitting bets and gathering data. I remember in the past BEtfair released a fully functionally spreadsheet that connected direct to the API to show off the early API capabilities, I could imagine multithreading being useful for that
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

spreadbetting wrote:
Sat Aug 11, 2018 8:44 pm

Can't imagine you get any significant speed increase as you're always dependant on BA for submitting bets and gathering data. I remember in the past BEtfair released a fully functionally spreadsheet that connected direct to the API to show off the early API capabilities, I could imagine multithreading being useful for that
Do you have a link to that sheet?

Issue is my excel sheet is the bottleneck, would like to speed it up a bit.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Must be a complicated sheet for it to be slowing things down. Don't think I have a copy of the sheet anymore as I dumped it when they switched to the new API as it was only coded to log into the previous API version. It didn't do anything clever other than log into the API and show how to do a few of the old API requests so wouldn't help with any speed issues.


Here's a link to the example betfair have for the NG API

https://github.com/betfair/API-NG-sampl ... master/vba

Just tried it with my session cookie and appkey and seems to work, so I guess if you have your own appkey you could tweak it to your own needs.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

sa7med wrote:
Sat Aug 11, 2018 8:28 pm
Just out of curiosity, has anyone experimented with multithreading solutions for their VBA code? ie hacking vba so that it uses more than one core? From some brief research it seems there are a couple of ways to do it
Obviously this was posted a couple of weeks ago now so maybe you have solved your issue.

I was and am getting "reasonable speed" out of my pretty low powered laptop with a couple of bet angel instances each linked to 70 sheet workbooks and doing some fairly complex VBA and running lots of other stuff as well such as SQL Server Express. I am pretty much maxing out the CPU and memory on this this low powered machine with the guardian excel refresh rate set to 100ms but I it is just about keeping up with everything I have thrown at it.

"Reasonable speed" depends on what your objectives/expectations/requirements are for processing speed of course. If you are aiming for 20ms and have got your existing VBA speed down to say 40ms or 60ms then I'd say maybe looking further into the possibility of multi threading might be a possible route to try to help you reach your objective. But if your VBA code runs in 200ms then I'd really start looking for other ways to speed up your code before even thinking about multi threading. StackOverflow casts some doubt as to if it can even be done anyway from within excel VBA. Even if it can be done (probably via external modules) the rapid refresh rate that is being applied to Guardian linked Bet Angel worksheets will only make this even more complicated than it would be from a static manually updated excel workbook.

I've just bought some new higher powered hardware (i5-8400 6 core which gives a cpubenchmark.net rating of 11700) with 16GB of RAM and a M.2 NVMe hard drive (which basically is an ultra ultra fast SSD). One of my plans is to check out just how close I can get my existing VBA down to keeping up with the 20ms theoretical best refresh speed. Nothing is off the table in what I will consider trying over the next few months including trying newer versions of excel etc, using RAM disks, expanding RAM further if needed. I'd say trying to get some kind of excel VBA multi-threading solution working is way, way down the bottom of the list of things I would try to get a performance improvement.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”