Pulling data from web page

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

Pulling data from web page

Postby MatGreenaway » Tue Aug 04, 2015 8:17 pm

Hi All,

I bet on in-play horse racing and for years have been having success. I improved this earlier this year by getting my Excel to extract race going and exact distance from a web page. This has been working well, but I have only just realised this has stopped working. It actually stopped on 24th June.

I'm no expert on VBA - most of my code has been pulled off other sites, or has been suggested on forums (probably Gruss). I grab the data from gg.com. Not sure if it is their site that has changed, or whether a Windows update has changed the way Excel macro's work (I will check Windows Update log).

The code I use is below. I've stripped out other stuff not relevant to this, and use Cells (1,1) as the trigger just in a blank spreadsheet (in reality, the trigger is something else in multiple cells). Note there are a couple of REM'd lines. The text in cell B2 (2, 1) is "https://gg.com/racing/03-aug-2015/carlisle-2045" (without the quotes)

I would really appreciate any comments or help!! TIA - Mat G


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhand
Application.EnableEvents = False

If Cells(1, 1) = "yes" Then

Dim xml As Object
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "Get", Cells(2, 1), False
xml.Send

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

Dim name
Dim going

Set name = doc.getElementsByTagName("h4")(1)
Rem Set going = doc.getelementsbyclassname("going")(0).getElementsByTagName("h1")(3)

Rem Cells(187, 4) = going.innertext


openingParen = InStr(name.innertext, "(")
closingParen = InStr(name.innertext, ")")
distance = Mid(name.innertext, openingParen + 1, closingParen - openingParen - 1)
Cells(183, 4) = distance
Cells(185, 4) = name.innertext



Dim result As String
Dim myURL As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

winHttpReq.Open "GET", Cells(2, 1), False
winHttpReq.Send
result = winHttpReq.responseText
Application.ScreenUpdating = True

Range("A5").Value = result

Cells(185, 4) = Cells(185, 2)


End If

errhand:
Application.EnableEvents = True
End Sub
MatGreenaway
 
Posts: 31
Joined: Tue Jan 26, 2010 3:00 pm

Re: Pulling data from web page

Postby MatGreenaway » Wed Aug 05, 2015 1:30 pm

Hi, I believe I have resolved this (and will know for sure when the 1.50 at Brighton starts).

Just for reference in case somebody else has an issue with data scraping, I changed one line from

Set xml = CreateObject("MSXML2.XMLHTTP")

to

Set xml = CreateObject("MSXML2.ServerXMLHTTP")

and all now works. Although I checked Windows Updates and when my machine was rebooted, it must have been something to do with the way Windows / MS Office / VBA changed (I can only assume).

Have a good day's betting / trading!
MatGreenaway
 
Posts: 31
Joined: Tue Jan 26, 2010 3:00 pm

Re: Pulling data from web page

Postby Fsolovaki » Tue Sep 08, 2015 9:06 am

This information can be found on the page or not.
Fsolovaki
 
Posts: 2
Joined: Tue Sep 08, 2015 9:03 am

Re: Pulling data from web page

Postby Shaun » Fri Sep 18, 2015 8:27 am

This may have solved my issue i was using Set oXHTTP = CreateObject("MSXML2.XMLHTTP") and replaced it with Set oXHTTP = CreateObject("MSXML2.ServerXMLHTTP")

I need to test some more to see if it solved the issue as mine would crash on on first use of my macro but function after that.
Shaun
 
Posts: 435
Joined: Fri May 09, 2008 11:11 pm
Location: Kellerberrin, Western Australia

Re: Pulling data from web page

Postby MatGreenaway » Fri Sep 18, 2015 10:37 am

Hope you get yours fully sorted Shaun, I don't really know VBA - I only find snippets from Google and just about manage to edit to suit! I don't know what difference just adding "Server" to that CreateObject line makes!

Cheers,
Mat
MatGreenaway
 
Posts: 31
Joined: Tue Jan 26, 2010 3:00 pm

Re: Pulling data from web page

Postby Shaun » Sat Sep 19, 2015 3:49 pm

Thanks, yes it worked a treat, no more issues and i will be sure to add that to all my other sheets, even those that don't have issues.

My issue was something to do with a cookie and when it expired.
Shaun
 
Posts: 435
Joined: Fri May 09, 2008 11:11 pm
Location: Kellerberrin, Western Australia


Return to Discussion

Who is online

Users browsing this forum: No registered users and 17 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.