Spreadsheets speed

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

Just checking my Guardian>Excel>SQL Server data capture performance figures on my new PC.

I managed to capture up and extract up to 31 frames of data per second via excel/guardian this evening on an inplay market and extract to flat file on a Ramdisk and then loaded into SQL server. So at 18:27:23 for Angels Glory I managed to capture 31 frames with 9 distinct trades across the market in that particular second in play in the 18:25 at Hamilton.

Not bad but I'm still missing 19 frames off the theoretical max of 50 frames per second with the 20ms excel refresh rate.

I'd be interested to know if anyone has managed to get more then 31 frames of data per second via guardian and excel?
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”