I-XL's data capture spreadsheet multi market
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.
- ruthlessimon
- Posts: 2096
- 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!!)
(was too much!!)
- ruthlessimon
- Posts: 2096
- 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?
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
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)
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
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)
- ShaunWhite
- Posts: 9731
- 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.
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
https://www.youtube.com/watch?v=4WU8JZC ... e=youtu.be
-
- 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
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
-
- Posts: 575
- 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.
-
- 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?
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?
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
How would you fire bets in? Would it not be better to use a rolling logger than an ever-expanding one that gradually gathers more and more data thus slowing down your system? (Just food for thought)
I'm still rubbish at VBA myself but maybe something like this could work? ...
Run that in your sheet code so as long as G1 and H1 are not in play or suspended your CopyData sub would run and then just make sure your CopyData sub is in a separate module somewhere so it can be called. Only problem with this is the system would stop if either one popped up so you may want to think how you could refine this for your needs. Hope this helps a little.
I'm still rubbish at VBA myself but maybe something like this could work? ...
Code: Select all
Sub Example ()
If Worksheets(“Bet Angel”).Cells("G1") <> “In Play” And
Worksheets(“Bet Angel”).Cells("H1") <> “Suspended” Then
Call CopyData
End if
End Sub
-
- Posts: 17
- Joined: Sat Aug 20, 2011 4:17 pm
Thanks Callum
Was thinking of tracking things in the data capture sheet and then using that to fire bets into the main BetAngel sheet ..... sadly Excel skills are lacking !
Was thinking of tracking things in the data capture sheet and then using that to fire bets into the main BetAngel sheet ..... sadly Excel skills are lacking !
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
I'll leave the rest for you to figure out as it's a good way to learn. Good luck
You do not have the required permissions to view the files attached to this post.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Change the interval setting before attaching to a market and then save the document? Once it is linked to a market it will be running constant VBA code so best to make any modifications between going live always. That should be a total fix but if not you're best to just make your refresh (Restrict refresh in Guardian too) one minute, that way the spreadsheet will only 'talk' to BetFair once a minute giving you minute by minute updates per each line.
The code expects the "Data" sheet to always be the active sheet, when you switch to "Bet Angel", it can't find what it's looking for on that sheet, so, as you found, it throws an error.
To re-set it without closing it down, make sure that the "Data" sheet is the one in view, then just below the "run" button on the top row, there's a little blue box (re-set), press this.
Try the code below (untested), it does the same thing as what you've already got but doesn't need to select anything, hence there shouldn't be a problem when switching between sheets.
To re-set it without closing it down, make sure that the "Data" sheet is the one in view, then just below the "run" button on the top row, there's a little blue box (re-set), press this.
Try the code below (untested), it does the same thing as what you've already got but doesn't need to select anything, hence there shouldn't be a problem when switching between sheets.
Code: Select all
Sub Record_data()
' Record_data macro
Dim rngFrom As Variant, rngTo As Variant
Set rngFrom = Sheets("Data").Rows("5:5")
Set rngTo = Sheets("Data").Rows("7:7")
rngTo.Value = rngFrom.Value
With Sheets("Data")
.Rows("7:7").Insert
End With
'
End Sub