Announcement

Collapse
No announcement yet.

Non-interactive login using VBA question

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Non-interactive login using VBA question

    Hi,
    Has anyone worked out how to login using the non-interactive/bot login method in VBA?
    The bf documentation:
    https://api.developer.betfair.com/se...28bot%29+login

    shows examples in Java and Python but not in VBA. The sample Excel workbook does not shed any light on this as well.

    Following the above documentation, I've got it working with certificate generation via openssl, linking the certificate to my account and testing using curl....but I'm struggling with the vba implementation....doing my head in.
    I know I could simply build a wrapper around the supplied curl command to grab the session id but would prefer to do a more elegant implementation through httprequest etc.
    Is there anyone that can assist?
    Thanks

  • #2
    I am new to this and have just managed an interactive login in excel with a http request retrieving the ssoid. If that's any good to you let me know and I'll post the code.

    Comment


    • #3
      I'd find that useful - currently trying to get non interactive login working, but struggling a little.

      Comment


      • #4
        non interactive login with vba

        thanks PeeOoo
        I would find that useful too.....

        Comment


        • #5
          I found a C# version and converted in to VB.NET if anyone wants that?

          Comment


          • #6
            I have a 3 line proof of Fermat's Last Theorem. Don't suppose anybody's interested but if they are I'll post it at a later date.

            Comment


            • #7
              Funny...surprising no-one has figured out the vba implementation

              Comment


              • #8
                Sorry for the delay, I thought I would get an email if anyone replied to my message. Will be more attentive in the future.

                Anyway here is a test routine I wrote in excel vba, just to get me started. I had to mess around to get it working but it ended up pretty simple. Sorry for the rubbish code, it's just a test, not what I would use in a proper application.


                Sub test1()

                Dim cookies As String
                Dim loggedIn As Boolean
                Dim oHTTP As Object: Set oHTTP = CreateObject("MSXML2.ServerXMLHTTP")
                Dim ssoid As String
                Dim uri As String: uri = "https://identitysso.betfair.com/api/login?username=" + USERNAME + "&password=" + PASSWORD + "&login=true&redirectMethod=POST&product=home.betf air.int&url=https://www.betfair.com/"
                Dim x

                oHTTP.Open "POST", uri, False
                oHTTP.Send

                If oHTTP.Status >= 400 And oHTTP.Status <= 599 Then
                MsgBox ("Error Occurred : " & oHTTP.Status & " - " & oHTTP.statusText)
                GoTo finish
                End If

                MsgBox (oHTTP.ResponseText)
                MsgBox (oHTTP.getAllResponseHeaders())
                'MsgBox (oHTTP.getResponseHeader("Set-Cookie"))

                cookies = oHTTP.getResponseHeader("Set-Cookie")

                x = InStr(1, cookies, "ssoid=") + 6
                If x = 6 Then
                MsgBox ("Error logging in")
                GoTo finish
                End If
                ssoid = Mid(cookies, x, InStr(x, cookies + ";", ";") - x)

                MsgBox (ssoid)

                finish:

                End Sub

                Comment


                • #9
                  Just found out you have to subscribe to thread to get email notifications, how thick am I?

                  P.

                  Comment


                  • #10
                    Thanks PeeOoo
                    Tried the code and it works for me...many thanks
                    If you get the certlogin going would you mind posting it too

                    Comment


                    • #11
                      No problem, but I am not sure if I am going to bother with the cert login because it is only for my own use.

                      P.

                      Comment


                      • #12
                        Hi

                        I decided to have a go at the cert login yesterday and I could not find any decent examples. I managed to cobble together different bits and pieces from different places and languages, but after hours of trying everything I could think of I failed. However I think I have broken the back of it, but I don't have the time to go any further. So maybe someone else can finish it off.

                        Here is the code I created, afterwards I will give some more information.

                        ---------------------------

                        Sub test2a()

                        Dim oHTTP As Object: Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
                        Dim uri As String: uri = "https://identitysso.betfair.com/api/certlogin"

                        oHTTP.Open "POST", uri, False
                        oHTTP.SetClientCertificate "LOCAL_MACHINE\My\betfairLogin"
                        oHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
                        oHTTP.setRequestHeader "X-Application", APP_KEY
                        oHTTP.setAutoLogonPolicy (2)
                        oHTTP.send ("{""username"":" + USERNAME + ",""password"":" + PASSWORD + "}")

                        MsgBox (oHTTP.responsetext)

                        If oHTTP.Status >= 400 And oHTTP.Status <= 599 Then
                        MsgBox ("Error Occurred : " & oHTTP.Status & " - " & oHTTP.statusText)
                        GoTo finish
                        End If

                        MsgBox (oHTTP.responsetext)
                        MsgBox (oHTTP.getAllResponseHeaders())

                        finish:

                        End Sub

                        ---------------------------

                        1. In order to specify a certificate you need to use WinHttp.WinHttpRequest.5.1
                        2. You cannot read the .pem file contents and pass it as a string to SetClientCertificate
                        3. You need to create a .p12 file as per betfair instructions
                        4. You open explorer, right click the .p12 file and install to Local Machine in the Personal store
                        5. To reference the certificate you use LOCAL_MACHINE\My\<CN> where <CN> is the common name you used when creating the .csr file (This took ages to figure out, and I was just about to give up when I found a reference to it)

                        Up until I figured out point 5 I always got a runtime error "A certificate is required to complete authentication", afterwards I no longer got an error.

                        However, now the bad news: I now get an oHTTP.Status of 404, "not found".

                        I spent another couple of hours trying to figure out why this is but to no avail.

                        That's as much as I can do for now. If anyone else figures out the rest let me know please.

                        P.

                        Comment


                        • #13
                          thanks for sharing this
                          i'll try and finish it and if I succeed i'll post back

                          Comment


                          • #14
                            PeeOoo

                            You were nearly there. I managed to get it going.
                            Here is my example code with notes following:

                            Dim oHTTP As Object: Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
                            Dim uri As String: uri = "https://identitysso.betfair.com/api/certlogin"

                            oHTTP.Open "POST", uri, False

                            oHTTP.SetClientCertificate "Common Name"

                            oHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
                            oHTTP.setRequestHeader "X-Application", myAppKey
                            oHTTP.setRequestHeader "Accept", "application/json"

                            oHTTP.send "username=myuser&password=mypassword"


                            If oHTTP.status >= 400 And oHTTP.status <= 599 Then
                            MsgBox ("Error Occurred : " & oHTTP.status & " - " & oHTTP.statusText)
                            Exit Sub
                            End If

                            result = oHTTP.responseText

                            Dim status: status = Mid(result, InStr(1, result, "loginStatus") + 14)
                            If InStr(1, status, "SUCCESS") > 0 Then

                            Dim start, length
                            start = 18
                            length = Len(result) - (start - 1) - 26
                            ssoid = Mid(result, start, length)

                            End If

                            Notes:
                            1. To get this going I needed to load the .p12 version of the certificate (and key) to my local certificate store (i'm on Windows 7 Home Premium)
                            2. On the betfair host however, the .p12 format is not permitted. I found that either the .crt file or the .pem file works when associated with my account. I'm using the .crt file
                            3. I found this article useful
                            http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx
                            At the bottom of the article it describes the use of winhttprequest object with 3 parameters: location, store and subject. Location and Store are optional. Through trial and error I found the using the Common Name only, as you described, worked for me. This name would need to be presumably unique as it will return the first certificate it finds using the string Common Name.

                            Trust this is of help.

                            Comment


                            • #15
                              Just saw your code. That's great.

                              Looks like the request headers and send are slightly different. I will try it out sometime.

                              Brilliant that you got it working.

                              Thanks

                              Paul

                              Comment

                              Working...
                              X