I'd appreciate some input on my Excel update speeds. I have an Excel bot that can be connected to up to 100 markets on busy days. The critical window for me to bet is the last minute before the start time, so within this window I need as fast a refresh rate as possible to ensure it captures the moment when all conditions are met.
Before any racing starts, if I turn off restrict refresh I can see Excel cycling through all worksheets one at a time every 2-3 seconds, which is just fine. When racing is happening I have restrict refresh on 150/30, so only 1 or occasionally 2 markets are updating at any one time.
My problem is that Excel updates can take 15s when 1 market is active and 30s+ if 2 markets are active. As a result, it is regularly not updating frequently enough, within the 1 min pre-off time window, to get all bets on. The major difference near the off, compared to before any racing starts, is that more calculations are occurring in the spreadsheet, as the relevant Bet Angel N sheet is doing a lookup on imported form data in another sheet (up to 1000 rows of data on any day) using array formulas.
I am struggling to understand why the Excel update rate is 10x slower close to the off compared to well in advance. Is the delay due to the array / lookup formulas re-calculating maybe?
I've played with the refresh interval in Bet Angel, which is currently at 100ms, but this doesn't seem to make much difference to the Excel update rate - Excel is still slow to update whether 20ms or 1 second. CPU is around 40% utilised (AMD 1.5Ghz quad core), 25%-35% of which is Excel and 70% of 8GB RAM free, so it doesn't appear to be a hardware resource issue but I might be wrong.
Can anyone shed light? Thanks in advance.
Experience of Excel update speeds
-
CallumPerry
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
It is most likely not the refresh speed to be honest. I have multiple excel bots which run (on average) approx 11 updates a second. Max is 50 on 20m/s but even in busy markets do I rarely see money entering exactly 50 times a second.
I think it is the efficiency of your bot most likely. Instead of using formulas could you convert some of the tables into VBA? Are the tables filled with static values or formulas inside of formulas which update EVERY refresh? I had that problem before and soon realised I had to start learning some basic VBA.
Even writing a table to an array and referencing it in the background will massively speed things up.
I think it is the efficiency of your bot most likely. Instead of using formulas could you convert some of the tables into VBA? Are the tables filled with static values or formulas inside of formulas which update EVERY refresh? I had that problem before and soon realised I had to start learning some basic VBA.
Even writing a table to an array and referencing it in the background will massively speed things up.
That's very useful insight thanks.
I was starting to suspect as much. I already have a fair bit of VBA behind the scenes, it's just that it's a lot more work to move all the various formulas to VBA, so I was seeing how it went. But based on your experience I'm going to start shifting all this to VBA. Thanks again.
I was starting to suspect as much. I already have a fair bit of VBA behind the scenes, it's just that it's a lot more work to move all the various formulas to VBA, so I was seeing how it went. But based on your experience I'm going to start shifting all this to VBA. Thanks again.