Get Prices Using COM

[jc0r]

Hi all

Everyday i open all UK Win and Place races in one tab. I then want this information in Excel. Currently im using "Log To Sheet" method, but this gives me very little control over when to refresh. As i have thousands of formulas that execute on a refresh from any of the (using todays example of 21 races) 42 markets, this serverely hampers performance.

I use a little bit of COM elsewhere, so i was wondering if this might offer a solution. I still want BA to be refreshing to get the latest prices but by not logging to Excel causing thousands of formulas to execute many times a second. Could i run a Sub that uses COM to basically pull all the data from the BA Tab to a sheet? That way i can control when i want the information to be pulled by running the Sub at given times.

Many thanks

[osknows]

Excel normally should be able to handle thousands of calculations within a 200ms refresh rate. I would check the following

1. Are there more efficient formulae? Eg use MATCH/INDEX instead of VLOOKUP, INDIRECT etc
2. Remove all volatile functions (INDIRECT, OFFSET, ROW(), COLUMN()), in Gruss/Excel none are needed.
3. Reduce the amount of overall calculations, sometimes increasing number of calculation columns using helper columns reduces the overall calculation amounts. Eg calculate a value once only and then refer to it from other calcs instead of calculating the same value multiple times.
4. Try an order your calcs from left to right as it sometimes aids the Excel calculation engine. Eg how can a column calculate a value to the right which hasn't yet calculated?

If there are still problems after that then it may be worth looking at other ways to improve performance using VBA and/or COM

With the COM you can handle the object directly and process only required data. Without the COM using VBA and Excel still you can determine which range Excel has update and only process those cells.

It's hard to be specific though without knowing what you are doing.

Os

[bolpx001]

Hi jc0r
For what it is worth, I have a very complex SS which runs a lot of VBA code and also a lot of formulas (6735) . Typically I would have 10 tabs in BA at refreshes of .2 with no issues - all this on windows 7 on an old Dell Dimension 8100
best regards
Paul

[Captain Sensible]

6735 formulas, jesus I hope it's very profitable, I couldn't even think of anywhere near that amount of formulas to stick in a bot

[jc0r]

I have well in excess of 40k formulas, maybe that's where I'm going wrong lol. It does watch 32 markets at once. I tried scaling it down and run separate instances of BA and Excel but the improvement gain is lost by processing power by running the multiple instances.

[Captain Sensible]

40K !!! how do you even fit them on the sheet, are they excel formulas or running via vba?

[jc0r]

There's 11 sheets in the workbook, some are just helper sheets though

[jc0r]

I use a bit of VBA, typical formulas in a single cells are as follows. It might be asking a bit much

Code: Select all

=IF(ISERROR("http://www.oddschecker.com/horse-racing/"&VLOOKUP(LEFT(AE3,(FIND(" ",AE3,1)-1)),$M$2:$N$62,2,FALSE)&"/"&LEFT(RIGHT(AE3,LEN(AE3)-FIND("- ",AE3)-1),5)&"/winner"),""," -no-remote -P ""Race2"" https://www.betbrain.com/horse-racing/united-kingdom/"&TEXT(TODAY(),"dd-mm-yyyy-")&VLOOKUP(SUBSTITUTE(LEFT(RIGHT(AE3,LEN(AE3)-FIND("- ",AE3)-1),5),":","-"),J3:L170,2,0)&"-"&VLOOKUP(LEFT(AE3,(FIND(" ",AE3,1)-1)),$M$2:$N$62,2,FALSE)&"/"&TEXT(TODAY(),"dd-mm-yyyy-")&VLOOKUP(SUBSTITUTE(LEFT(RIGHT(AE3,LEN(AE3)-FIND("- ",AE3)-1),5),":","-"),J3:L170,2,0)&"-"&VLOOKUP(LEFT(AE3,(FIND(" ",AE3,1)-1)),$M$2:$N$62,2,FALSE)&"/#/win-and-each-way/whole-event/")

[bolpx001]

Hi Captain, most are just copies from the cells above. A lot of them have nothing to do with getting and processing data from BA but rather testing and recording different ideas. I am a great believer in record keeping. In the ideal world I would split the workbook up and make it a leaner fitter fighting machine but I keep finding other things to do with my time and also why fix something that works. - best regards Paul

[Captain Sensible]

In the ideal world I would split the workbook up and make it a leaner fitter fighting machine but I keep finding other things to do with my time and also why fix something that works. - best regards Paul

Couldn't agree more , there are much better things to do in life than messing around with VBA, I'm of the side mind "if it ain't broke, why fix it" Most of my bots are just tweaks from the original bots I started with. I did have a period of tweaking them to switch from formulas to VBA solely to make things more efficient and only run routines when they were needed rather than formulas that ran every refresh.

I run some stuff using php that I'd written before Gruss came along and have forever been meaning to switch to VBA .Net etc but still haven't got round to it. Even bought that programming for Betfair book but still haven't had the time or inclination to read it

[bolpx001]

Hi jc0r
I think I see where your slowdown would likely be. I see that excel is having to go to the internet to get info for it's formula, this I imagine slows thing down a lot - not sure where the solution lies though.
Very interesting formula I would never have thought of using it like that. Definitely food for thought
Best regards
Paul

[osknows]

VLOOKUP(LEFT(AE3,(FIND(" ",AE3,1)-1)) - you're calculating this same value 3 times in the same formula. Put this in it's own column and point the formula to it

RIGHT(AE3,LEN(AE3)-FIND("- ",AE3)-1),5) - same here 3 times in the same formula

VLOOKUP - replace with INDEX/MATCH on a single column

TODAY() is volatile. Also, it can only be 1 value so put it in 1 cell only in Excel so it calculates once only instead of multiple times in lots of formulae. Even better use cell B2 of the odds sheet where the time is written by Gruss so don't need a volatile function for the datetime.

If you are then using the links to download data, do it asynchronously so it doesn't block. Try and re-write yuor formulae so a value is calcualted once only.

[jc0r]

Many thanks for the pointers! I'll be sure to make the change

[osknows]

Also, if you are using VBA, don't read/write cells one by one. Read in a whole range into an array, do your processing and write out the result once only in an array.

[bolpx001]

just a quick example of reading and writing blocks of data with VBA, which may help

Sub copy1()
Dim numRunners As Double
Dim orgData01 As Variant

numRunners = 7
orgData01 = Range(Sheets("Refresh").Cells(5, 1), Sheets("Refresh").Cells(5, 1)).Resize(numRunners, 16)
Range(Sheets("storeData").Cells(1, 1), Sheets("storeData").Cells(1, 1)).Resize(numRunners, 16) = orgData01

End Sub