Scraping Race DIstance pre-off

[MatGreenaway]

Hi All,

I have been using Gruss since 2008 and it has been profitable in that time but in recent months, I am seeing more losing bets. The small profit winning bets make for me mean that even a small increase in the number of losers turns this from being profitable to not. Its currently about break-even and has stopped generating profit in the past few months.

How long the race will run for is important for me and I always analyse my bets and I know I could do a better job of estimating the times, but until now, its not been a priority. I feel if I can try and get the estimated race times more accurate, I should have fewer losers and then this should make me profitable again.

A big issue for me is races that are longer than Betfair actually says they are... As an example, one of the recent losers was on the 13th November at Taunton. A 2m 3f 110y race - obviously Betfair omits the yards so to Betfair and my spreadsheet, its just 2m 3f. But 110 yards is half a furlong and that meant to me, my betting window was increased by approx 7 seconds. It might not have meant I had a loser in this race, but chances are, it did.

I am wondering if somebody can help with scraping race times pre-off. I've asked a question about scraping before, but not for getting the distance. I did some testing, but never succeeded.

How would I go about scraping the distance. To make it simple, the GG.com site use the date and race time in the URL. https://gg.com/racing/17-nov-2014/leicester-1345 and in there, it has the distance.

I can do the simple bits like lookup the abbreviated BF course name and change the date so I can create a URL and then trigger this when the market loads. I just don't know how to scrape that particular bit of data.

I would appreciate any help on this.

Thanks,
Mat

[Captain Sensible]

Without you posting any code I'm not sure how far you've got or clued up with vba you are, have you tried something like getElementsByTagName then just use a bit of regex using brackets to get the distance.

Code: Select all

Sub test()
    Dim xml As Object
    Set xml = CreateObject("MSXML2.XMLHTTP")
    xml.Open "Get", "https://gg.com/racing/17-nov-2014/leicester-1345", False
    xml.send

    Dim doc As Object
    Set doc = CreateObject("htmlfile")
    doc.body.innerhtml = xml.responsetext

    Dim name
    Set name = doc.getElementsByTagName("h4")(1)
   
   
    openingParen = InStr(name.innerText, "(")
    closingParen = InStr(name.innerText, ")")
    distance = Mid(name.innerText, openingParen + 1, closingParen - openingParen - 1)
    MsgBox distance

End Sub



[MiniBlueDragon]

Lovely clean code Cap! What object library is this in?

It's actually inspired me to re-look at mine when time allows and optimise it all. Currently using the ancient and clunky Microsoft HTML Object Library with createDocumentFromUrl for my scraping. :/

[Captain Sensible]

I wouldn't have a clue, dragon , I'm not too clued up with vba to be honest. I just adapt snippets I find on the web and rarely know what they're actually doing. I found that a while ago when I was looking to add some other web scraping to my bots and it was a lot smaller and neater than the code I'd been using as I was getting into the html DOM stuff with php and found it much better than the regex I'd used before and wanted something similar with VBA xml etc

I've been meaning to update and optimise my stuff with all these snippets of code but never get round to it either

I like the fact it's laid out quite well so you can follow it thru, the stuff I'm using in one sheet still confuses the hell out of me using ActiveSheet.QueryTables.Add(Connection:= "URL; etc and load of other parameters that I'm sure aren't neccessary, I need to replace it but still have some stuff to do with my php bots that I'd left and left hoping they'd delay swithover

[MiniBlueDragon]

Unfortunately my code has been "improved" over the last few years in incremental steps so I have a ton of pointless stuff in the spreadsheets that I no longer need but was experimenting with at the time. It scrapes race distances, jockey names, trainer names, form strings etc at a predefined interval for the currently displayed race and I use not a single one of them any more so it's just an unnecessary loss of speed.

[Captain Sensible]

That's one of my bug bears the fact that so much redundant code is sitting amongst my bots from old strategies and apart from the fact it probably slows it down I always forget to comment lines so it's now a pain to follow it properly. And at the end of the day it's usually the simplest ones which make the money.

It came to me yesterday what you meant about libraries I'm using 2010 excel and think it must include the XML stuff as there's not many references added. Reckon I'll gonna re-write to use MSXML for scraping as it does seem efficient compared to the older stuff I'm using now.

Untitled.png

http://msdn.microsoft.com/en-us/library ... 85%29.aspx

[MiniBlueDragon]

Yep nothing added reference-wise so it's definitely one of the defaults. Really glad I found this thread for future refactoring

[MatGreenaway]

Hi both,

Thanks for your replies and I do appreciate the code example. I experimented with your code and found I could easily get the actual distance in to a cell in Excel. I didn't use the getElementsByTagName but now I've just re-read your first post, I'll have a look at that.

In normal Excel (outside of VBA) I am quite proficient and can easily change a string like 1m 2f 110yds into 10.5 furlongs.

Thanks and have a good day's betting!
Mat