How to make spreadsheets more efficient

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
pgetty
Posts: 5
Joined: Sat Dec 22, 2018 6:56 am

Hi all

I have been working on my excel (including VBA) skills over the last year and have completed various work / personal projects and thought it would be a good challenge / next step for me to create a Betfair bot. So far I have just been experimenting with the basics but have some questions that I am not experienced enough to answer myself.

I have different complex formulas that are required at different stages

- place a back bet
- wait for back bet to be matched
- place a lay bet
- wait for lay bet to be matched

I have placed each complex formula within an if statement that determines if the complex formula needs to run based on the above stages. The logic behind this is that I am presuming that the complex formula will only run when the answer to the if statement is true therefore reducing the burden on my resources as simple if statements are being calculated rather than the more complex formulas.

Is this the case or am I better off removing the additional if statements?

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

Keep it simple, complex or "mega formulas" can create bottlenecks when performing calculations, they can also be a pain if you need to alter/add to any of the criteria in them at a later date. Don't forget that some some formulas are more resource hungry than others and I've found that the K.I.S.S. approach is always the best

Take for example a horse race

Column A

Q1 Is it a handicap? 0
Q2 Are there less than 10 runners? 1
Q3 Are the fav odds between 2 & 3? 1
Q4 Are the 2nd fav odds > 5? 1
Q5 Is it being run at Cheltenham? 1
Q6 Is it on a Saturday? 1
Q7 Is it a National Hunt race? 1
Total 6


Column B
Q1 Is it a maiden? 0
Q2 Are there less than 10 runners? 1
Q3 Are the fav odds between 2 & 3? 1
Q4 Are the 2nd fav odds > 5? 1
Q5 Is it being run at Cheltenham? 1
Q6 Is it on a Saturday? 1
Q7 Is it a National Hunt race? 1
Total 6


Column C
Q1 Is it a Group Race? 1
Q2 Are there less than 10 runners? 1
Q3 Are the fav odds between 2 & 3? 1
Q4 Are the 2nd fav odds > 5? 1
Q5 Is it being run at Cheltenham? 1
Q6 Is it on a Saturday? 1
Q7 Is it a National Hunt race? 1
Total 7


You can see the only difference between them is the first question and the spreadsheet is doing all the hard work, all I need to do is to look at A8,B8 AND C8 to see if any give the total i need.

Code: Select all

If Range("A8")=7 Then call PlaceHandicapBet
If Range("B8")=7 Then call PlaceMaidenBet
If Range("C8")=7 Then call PlaceGroupBet
At a later date I might decide that questions 5 & 6 are irrelevant and remove them (adjusting the total to look for to 5), or replace them with something totally different.

Note: A quicker method rather than using a lot of 'if' statements would be to use the 'find' method.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Definitely only execute the intensive parts if necessary. A single basic if will execute in a proverbial nanosecond, if that prevents a dozen IFs buts and maybes from running the that's great.

As nigelk says, kiss is the key. Fast code is usually good looking code, so if you've got routines you can't fit on a page or can't understand with a quick glance then it's probably time to chop things up into separate procedures.

One of the biggest speed boosters I've found is to disable recalcs and repaints when they're not necessary, ditto the dreaded worksheet-change events. The last thing you need is excel continually saying "what should I do what should I do what should I do" just because you're writing an array to a sheet etc.

If in doubt about your code, insert some timers and see what's slowing down the show.
User avatar
Derek27
Posts: 23477
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

=(((D7/B7)^(1/J7)-1)*J7+K6*(L7-J7))/L7
I've just plucked the above formula out of a spreadsheet as an example. If I needed it executed every second, would it be faster to place it in a VBA function and reference the function from the spreadsheet cell?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If, for example, D7 changes in value 10 times in a second, then your formula will change 10 times in a second as well. Obviously, no change in any of the cells referenced means no change in your formula.

So don't worry about it being executed once a second.
pgetty
Posts: 5
Joined: Sat Dec 22, 2018 6:56 am

Thanks for your replies

I have keep my formulas as streamlined as possible but I am trying to plan ahead as with the large number of potential runners the impact of adding one new formula is multiplied and although I am not experiencing any performance issues now I would rather try and prevent them than deal with them later on.

I am currently only using VBA (Worksheet Calculate Event) to clear the status cells. Is there an advantage to using VBA more or are worksheet formulas sufficient. Should I be considering triggering bets using only VBA or a combination of both. To keep it simple I am trying understand where is the best place to focus my efforts in worksheet formulas or VBA macros. I appreciate this question probably depends a lot on circumstances but would be helpful to hear others views on this.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Personally all I used my sheets for was as a data source and a repository. Zero formulas and I did everything in code. I found it much easier to maintain. Then again I'm from a coding background so keeping data and functionality separate is 2nd nature.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

pgetty wrote:
Sun Jan 06, 2019 5:48 pm

I am currently only using VBA (Worksheet Calculate Event) to clear the status cells. Is there an advantage to using VBA more or are worksheet formulas sufficient. Should I be considering triggering bets using only VBA or a combination of both. To keep it simple I am trying understand where is the best place to focus my efforts in worksheet formulas or VBA macros. I appreciate this question probably depends a lot on circumstances but would be helpful to hear others views on this.
If you have the knowledge set, or ability to learn VBA, it's worth porting all your formulas to VBA as it'll make your sheet so much more efficient. The least number of reading and writing to a sheet the better in terms of speed. Excel these days is efficient when it comes to managing formulas but with VBA (Calculate or Change) you can have any routines only run exactly when you need them to rather than simply because a cell has changed on the sheet kicking off a host of formulas.

Once you get your head around arrays you can visualise the data in exactly the same format as a range of cells and like Shaun says it's a lot easier to maintain your loops by amending one line of code rather than a host of formulas for each runner. For my bots I pick up the data I need into one array when the sheet updates, do any calculations and amendments to the array in memory and then dump back to the sheet if they've been changes like placing/cancelling bets etc
pgetty
Posts: 5
Joined: Sat Dec 22, 2018 6:56 am

Great to get the perspective from more experienced excel users. I am competent with excel VBA and have been slowly teaching myself over the last year. I don't have much experience with arrays bar a few videos I have previously watched though I will look into them more over the next few weeks.

Thanks to those that have responded it has been a great help. I have already put Nigels tip to use find instead of ifs into practice.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Be careful with VBA/Excel, it's a gateway drug to VB/SQL. Before you know it you'll be nocturnal and wearing t-shirts that are only humorous if you know hexadecimal.
pgetty
Posts: 5
Joined: Sat Dec 22, 2018 6:56 am

I have started my attempt to move all my formulas into VBA and have a few quick questions to make sure I am going down the right path.

Should I use arrays with 3+ dimensions or stick to 2?
I have declared a public array. Is there a way to view this similar to how you you would view a private array in the locals window?
I have googled how to index/match in arrays and from my reading it suggests the best way is to loop through all the data. Is this the best way or is there a better way?

Thanks
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”