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: 2094
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: 2094
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: 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.
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: 22673
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

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: 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.
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?
CallumPerry
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? ...

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
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.
jonedelman
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 !
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

BA Rolling Logger Test Dogs.xls
This may help you get started. This is a rolling logger I made, the only part you need (Can just copy and paste the code from VBA into your own workbook) is the yellow area and the 30 cells underneath. You put your formula(s) inside the yellow area and it ticks down the sheet every second. I assume you're going to take some average like an EMA or something so this is the best way to do it as the spreadsheet only 'remembers' the past 30 seconds so your system won't get swamped.

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.
CallumPerry
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.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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.

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
Post Reply

Return to “Betfair Data”