434 views

Scraping

Categories > General

Page 1

posts 1–13 of 13

tams

novice - member

15 posts

+0 permalink

I'm having problems logging into the main Betfair site using MS Access and VBA.

Set xml = CreateObject("MSXML2.ServerXMLHTTP.6.0")

xml.Open "POST", "https://www.betfair.com/account/login/LoginAction.do", False
xml.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xml.send "username=XXXXXXXX&password=XXXXXXXX"

Anyone any ideas where I'm going wrong?

Tams

quote

06th November 2008 01:23 a.m.

birchy

superstar - member

230 posts

+0 permalink

First thing to note as i'm not running a Windows machine, so this is from memory...
I played around with lots of VB6 HTTP methods and finally settled upon using WinHTTP as it auto-handles cookies which is very important if you're gonna scrape the website. Sooo, with that in mind, add a reference to the WinHTTP library something like Tools > References > Microsoft WinHTTP 5.1. Now create a Module and at the very top, declare a new WinHTTP object something like:

Dim oHTTP As New WinHTTP.WinHttpRequest

Declaring it publicly like this allows your new WinHTTP object to track all cookies between different function calls.

Next i would recommend you install Live HTTP Headers in Firefox or IEHTTPHeaders in Internet Explorer so that you can actually monitor what the the web browser sends/receives and then emulate it in your VB functions. Next, you'll need to write a Login function:

Public Function Login(username As String, password As String) As String
    Dim s As String, sPostData As String

On Error GoTo ExitError

    ' create post data + header + send
    s = "https://www.betfair.com/account/login/LoginAction.do"
    sPostData = "languageSelector=en%2CGBR&username=" & username & "&password=" & password
    With oHTTP
        .SetTimeouts 5000, 5000, 5000, 5000
        .Open "POST", s, False
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
        .Send sPostData
        If .Status = "200" Or .Status = "302" Then
            ' check login status
            s = .GetResponseHeader("Location")
            If s = "http://www.betfair.com/account/login/LoginView.do?status=success" Then
                Login = "SUCCESS"
            Else
                Login = "FAIL"
            End If
        Else
            ' http error (usually 404 if site down)
            Login = "HTTP ERROR " & .Status
        End If
    End With

Exit Function
ExitError:
Login = "FUNCTION ERROR"
Err.Clear
End Function

I've never done this in Access but i've had it working in both Excel and VB6 so it should be a goer.

__________________

www.bespokebots.com

quote

06th November 2008 02:24 a.m.

birchy

superstar - member

230 posts

+0 permalink

Once you've got the login code working ok, check the cookie handling by calling Login() and when it returns "OK", call this function:

Public Function GetAccountBalance() As String
    Dim s As String

On Error GoTo ExitError

    ' create header + send
    s = "https://uk.site.sports.betfair.com/wallet/LocalAccountSummaryWalletView.do"
    With oHTTP
        .SetTimeouts 5000, 5000, 5000, 5000
        .Open "GET", s, False
        .SetRequestHeader "Accept", "text/html"
        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9"
        .Send
        If .Status = "200" Then
           s = StrConv(.ResponseBody, vbUnicode)
            ' TODO: parse account info from HTML

        Else
            ' http error (usually 404 if site down)
            GetAccountBalance = "HTTP ERROR " & .Status
        End If
    End With

Exit Function
ExitError:
GetAccountBalance = "FUNCTION ERROR"
Err.Clear
End Function

__________________

www.bespokebots.com

quote

06th November 2008 02:57 a.m.

tams

novice - member

15 posts

+0 permalink

Thanks for that birchy, but it's still not working (on two different computers).

Interestingly I receive one of two different errors when I run it repeatedly.

Firstly when I check the headers in .getAllResponseHeaders, Location is not included. Then the next time I run it, Location is included, but the value is 'http://betfair.comnull'.

I've also tried it with and without "languageSelector=en%2CGBR&" at the start of PostData, but receive the same result. I use Fiddler, and I'm not seeing "languageSelector=en%2CGBR&" as part of PostData.

quote

07th November 2008 09:57 a.m.

birchy

superstar - member

230 posts

+0 permalink

The basic http code is sound, but the actual post data has most likely changed. You'll have to modify the code so that it matches what you see in Fiddler. I've given you an example of both a GET and a POST function, so you should be able to build your own API by copy/pasting in conjunction with Fiddler. It's pretty easy once you get into the swing of it.

__________________

www.bespokebots.com

quote

07th November 2008 13:53 p.m.

tams

novice - member

15 posts

+0 permalink

I agree that it's normally pretty easy, but this has me completely stumped. I'm able to login to the lite site okay, but that doesn't have all of the info that I need. I can also get the market data from the main site which doesn't require logging in.

Anyone else that is scraping Betfair that could point me in the right direction?

quote

07th November 2008 14:42 p.m.

birchy

superstar - member

230 posts

+0 permalink

All my bots scraped the website with VB6, so i have a fair bit of experience. I'm not quite sure what you are asking now?

The thing i found hardest to grasp was getting the correct URL. One in particular to watch out for is the market data. On the first load, the url is:
http://uk.site.sports.betfair.com/betting/api/json/getFullMarketData.do?mi=21317898&prevcache=1226070542516
which loads the whole market data, but on successive refreshes, the url is:
http://uk.site.sports.betfair.com/betting/api/json/getPartialMarketData.do?mi=21317898&prevcache=1226070482264
which only loads the latest odds, etc.

Personally, i just used getFullMarketData for every request as i didn't see much point in writing 2 different functions to do pretty much the same thing. Don't forget that until you are logged in, the data is cached and therefore delayed for a few seconds. I assume that "&prevcache=1226070482264" tells the server when you last refreshed. You can probably omit it from the URL.

You've probably noted that all the info you need is now nicely delimited as a JSON string. Betfair have certainly made it a LOT easier to parse.

__________________

www.bespokebots.com

quote

07th November 2008 15:16 p.m.

birchy

superstar - member

230 posts

+0 permalink

I've just noticed you mention the "Lite Site". Don't bother scraping against that unless you're happy with data that's a few seconds out of date.

__________________

www.bespokebots.com

quote

07th November 2008 15:25 p.m.

tams

novice - member

15 posts

+0 permalink

I was asking if anyone could spot the problem with my code (or yours) for logging in to the main Betfair site.

quote

07th November 2008 15:45 p.m.

birchy

superstar - member

230 posts

+0 permalink

I was asking if anyone could spot the problem with my code (or yours) for logging in to the main Betfair site.
-tams

Well you didn't actually ask that.

What is the problem? Have you modified my Login code to suit whatever Fiddler shows you...particularly the PostData? The URL is probably different as well. What is the http response? Is it a 200 (ok) or perhaps a 302 (page temporarily moved)? As long as you build your functions to match Fiddler, there should be no problem. If i get time later, i might boot my XP partition and try to get VBA in Excel to Login.

__________________

www.bespokebots.com

quote

07th November 2008 16:08 p.m.

tams

novice - member

15 posts

+0 permalink

Yes I have modified the post data, as I said above. username=XXXXXXXX&password=XXXXXXXX
Url = https://www.betfair.com/account/login/LoginAction.do

As I said, I get a one of two errors by running your code repeatedly.

One error is that the Status is 302 and the Location is 'http://betfair.comnull'.

The other error is that the Status is 200, but there is no Location returned.

quote

07th November 2008 16:43 p.m.

birchy

superstar - member

230 posts

+0 permalink

Well i've booted XP up and tried my old VB6 code. Other than the PostData change, my function is working ok in VB6. The http response seems to be randomly changing between 200 and 302, but both are valid responses and, more importantly, the "Location: http://www.betfair.com/account/login/LoginView.do?status=success" header is present. I've also noticed that this header is always present in a successful login via Firefox, i assume that Fiddler is telling you the same?

Now here's the crunch: i copy/pasted the code into an Excel sheet, added a WinHTTP reference, and ran it. I get the same 200 or 302 response, BUT the actual headers are different. I don't know WHY the same code would be different but we can safely say that there is a difference between the way VB6 and VBA runs the code. I'm certainly baffled by this oddness as i do know that i've run code from Excel in the past without any problems....

__________________

www.bespokebots.com

quote

07th November 2008 18:22 p.m.

birchy

superstar - member

230 posts

+0 permalink

I've just discovered this page while browsing. It should be of help regarding your login problem...

http://diybetfairbots.lefora.com/2008/07/02/website-scraping-login-failure/page1/

__________________

www.bespokebots.com