I-XL's data capture spreadsheet multi market

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

Been a few requests for this. Just a couple of small changes, there's no option to set a capture rate for each sheet (this can be set/overriden via Guardian), and the 'manual record' button is gone
You do not have the required permissions to view the files attached to this post.
User avatar
ruthlessimon
Posts: 2051
Joined: Wed Mar 23, 2016 3:54 pm

Lovely stuff Nigel you've saved my fingers - clicking ladders + switching sheets is too much ;)

(was too much!!)
User avatar
ruthlessimon
Posts: 2051
Joined: Wed Mar 23, 2016 3:54 pm

Just wondered - before I go on a copy spree - if I make extra copies of the BA + Data sheet will they still work/paste their data properly?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Yes just remember to

1 Keep the names consistant "Data (2), Data (3)" etc, but it should do this automatically if you create a copy anyway.
2. When you make a copy of the Bet Angel sheet, remember that Bet Angel (2) refers to Data (2) and so on
3 . Change the sheet name in each new copy of the Bet Angel sheet code

Code: Select all

Private Sub Worksheet_Calculate()

ws = "Data (2)"

Copydata

End Sub
EDIT: The only bug being is that their are over 150 formulas on each sheet that have to be changed as well, for example, all the formulas that show a sheet name : =OFFSET('Bet Angel (2)'!$K$9,MATCH(J3,'Bet Angel (2)'!$B$9:$B$68,0),0)

will have to be changed to the new sheet you are adding:=OFFSET('Bet Angel (22)'!$K$9,MATCH(J3,'Bet Angel (22)'!$B$9:$B$68,0),0)
User avatar
ShaunWhite
Posts: 6227
Joined: Sat Sep 03, 2016 3:42 am

For those that don't know you can use a find/replace to change the all the formulas in one go.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Yes i just posted a video. Just make sure that the worksheet already exists before making the change

https://www.youtube.com/watch?v=4WU8JZC ... e=youtu.be
User avatar
Dallas
Posts: 16057
Joined: Sun Aug 09, 2015 10:57 pm
Contact:

Good work as always Nigel
jonedelman
Posts: 17
Joined: Sat Aug 20, 2011 4:17 pm

Hi all

Firstly - thank you for the spreadsheet its really great !!

With this spreadsheet - does anyone know how to change the code so it captures the data ONLY pre-race? I think it should be quite simply - but I'm not sure how to do it ... basically I think it should update the "Data" sheet only if
(1) The current "Countdown" is a positive number (which happens pre-race, but also when the race starts
(2) The cell G1 on Bet Angel sheet is not "In-play" , and the cell H1 is not "Suspended"

I think if the sheet had these 2 conditions - then it would only record pre-race data (admittedly it would miss some data between the actual race off (when the countdown is negative) but I don't mind this.

Would be very grateful if anyone knows how to tweak the VBA code to add this

Thanks!
Jonny
CallumPerry
Posts: 523
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Could you not just use restrict refresh in guardian and put the end time as 0? So when it hits 0 it will stop capturing. As an extra precaution, in two of the recorded cells, record cells G1 and H1 so if an event somehow started early or suspended for any reason you could see this after.
jonedelman
Posts: 17
Joined: Sat Aug 20, 2011 4:17 pm

Yep for some restrict refresh doesn't seem to work all the time - and even if it does - I also sometimes get a random final row at the end of the race 0 ie.. 5 -10 mins after the race with odds of zero etc...

Not a problem for data capturing (as I can easily remove the row) - but could be a problem if I wanted to use the sheet to actually fire bets in for me

There must be a line of VBA code I could add in the BetAngel sheets .. to say :

" If either G1 = "In-Play", or H1 = "Suspended" then .. don't do nothing, or exit sub or something - otherwise , run the copydata macro "

Anyone able to help?
Post Reply

Return to “Betfair Data”