Using compiled Excel sheets

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
acecard
Posts: 85
Joined: Wed Nov 01, 2017 9:39 pm

Hi guys,

I have some pretty in-depth staking calculations that I have implemented into a spreadsheet, which makes it quite slow. I came across this thread:

viewtopic.php?t=10449

and in the second post, someone mentioned compiling the Excel sheet into a standalone application to boost performance. I liked the sound of that but just wondering if anyone has any experience with using compiled Excel sheets with BetAngel? How would one connect BetAngel to a compiled Excel workbook?

Thanks!
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

"Excel compilers" are all about encryption and protecting the workbook from copying or tampering - they do nothing for performance and may well slow things down since things need to be decrypted / interpreted AFAIK.

There are some things you can try - most notably turning off events that can cause race conditions.

Have a look at https://www.microsoft.com/en-us/microso ... practices/ and also Google for "excel vba performance"
acecard
Posts: 85
Joined: Wed Nov 01, 2017 9:39 pm

Thanks! Although it's not so much the VBA elements that are slowing it down, but rather the fact that I have iterative calculations being done on a shed load of cells. Essentially what I have is a feature that will compare my allocated bank with the required bank based on certain stakes. If the latter exceeds my bank, it will modulate down a co-efficient in order to bring my staking down to a level that doesn't exceed my bank. So essentially, Excel is iterating my already iterative calculations, which means it takes a while to calculate. If compiling wouldn't work then I think the only way to improve the speed would be to reduce the resolution with which the stake modulation occurs, but then of course, accuracy is them compromised!
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

I'd recommend that you try performing your calculations within VBA rather than as an excel formula if possible.

Basically the excel cell formula are probably being recalculated every time the worksheet gets refreshed via Guardian which could in theory be 50 times a second on a 20ms refresh cycle even if the cells used in the formula contains the exact same value as prior to the refresh.

If you only need to perform calculations for example whenever say the market volume changes then you can control that within VBA whereas you cant control that in an excel cell formula.
I dont use any cell formulae whatsoever. All my logic is performed within VBA and I successfully process worksheet refreshes up to 30 times per second at the moment.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I'd agree, if speed is an issue transfer everything to VBA. Just copy your sheet to an array , do your calculations in memory then dump any amendments back to the sheet so there's only one read and write to excel.
acecard
Posts: 85
Joined: Wed Nov 01, 2017 9:39 pm

spreadbetting wrote:
Sat Sep 08, 2018 11:51 am
I'd agree, if speed is an issue transfer everything to VBA. Just copy your sheet to an array , do your calculations in memory then dump any amendments back to the sheet so there's only one read and write to excel.
This sounds interesting........you make it sound easy to do! Is that the case? VBA isn't my strong point!
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

A lot is obviously dependant on your VBA knowledge to translate the formulas to VBA but most excel worksheet functions are available in VBA. Copying data to an array is simple enough then you simply amend the array in memory and dump it back to the sheet after all your processing.

It's usually the reading and writing to the sheet that slows things down and doing those in memory will speed up things massively if you have formulas all linked together on a sheet.

If you don't think you can do it yourself sometimes it's worth getting someone to do it for you , especially of its winning money. Or it might be an idea to try and simplify your staking methods as most things can usually be broken down to a simple calc that'd be near enough your current one.
acecard
Posts: 85
Joined: Wed Nov 01, 2017 9:39 pm

Thanks for all the responses; certainly some food for thought. I think it would be worth commissioning someone to tidy up what I've done into some more seamless VBA; definitely not something I can do myself!!

Thanks guys!
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I think Nigel on the forum does back spreadsheets might be worth contacting him even just to get an idea of costs etc. He's stuck up a few sheets on here.

viewtopic.php?f=19&t=15708
acecard
Posts: 85
Joined: Wed Nov 01, 2017 9:39 pm

Much appreciated!
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”