OZmium Sports Betting and Horse Racing Forums - NSW TAB Live Odds help needed

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - NSW TAB Live Odds help needed (http://forums.ozmium.com.au/showthread.php?t=23332)

NSW TAB Live Odds help needed

Hi everyone,

I am having trouble trying to get info from NSW TAB live odds. By trouble I mean I wouldn't have a clue where to start. I have been trying to understand how it all works, but it is going way over my head at the moment. I can get the fields etc from Tatts (UniTab?) xml feed mainly thanks to their example they give you. Why can't the NSW TAB do that?! It would make it a little easier!

Any help people could give me, even if it's just getting the Access Key, would be greatly appreciated.

Cheers,
Chris.

You need to use a web query to pull the data in, you will also need a way to manipulate the race number and venue.

I would set it up in a drop down list then add today's markets with the variables needed.

Here is a simple web query macro

Code:


Public Function ExecuteWebRequest(url As String) As String
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing
End Function

Code:


Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
        MyFile = ThisWorkbook.Path & "\temp.txt"
        fnum = FreeFile()
        Open MyFile For Output As fnum
        Print #fnum, text
        Close #fnum
End Function

Code:


Sub Race()
Application.ScreenUpdating = False
With ThisWorkbook
.Sheets("Prices").Select
Range("BJ1:BU100").Select
Selection.ClearContents
formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Control").Range("AL4").Value)
outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("Prices").QueryTables.Add(Connection:= _
            "URL;" & ThisWorkbook.Path & "\temp.txt" _
            , Destination:=ThisWorkbook.Sheets("Prices").Range("$BJ$1"))
    With temp_qt
        .Name = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """BetGrid_DGTableOne"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.QueryTables.Item(1).Delete
    Set temp_qt = Nothing
    Kill ThisWorkbook.Path & "\temp.txt"
    If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect  ions.Count).Delete
    .Sheets("Control").Activate
    Application.ScreenUpdating = True
End With
End Sub

This is the formula i have in cell AL4
=IF(AK2="","","http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1"&"&MeetingCode="&AN2&"&RacingCode="&AO2&"&FromDate="&TEXT(AS1,"yyyy-mm-dd")&"T00:00:00&BetType=Win&RaceNumber="&AL3)

The parts in red are the variables like what state meeting codes date and race number.

There is a lot more involved but this would work if you stuck it in a sheet and added the variables at the correct locations with the same sheet names.

You can run multiple macros pulling in different info, you only need the first 2 macros once you don't need them for each query.

Hi Shaun,

Just wondering if this VBA code you have uploaded is doing a simple screen scrape of a race, or is actually using the Live Odds WSDL and methods?

Only reason I ask is that I didn't even know you could call SOAP Web services through VBA (if in fact, you are using the SOAP services).

Neurokahuna

Thanks Shaun for your reply, I will give it a go and have a play around with a few things to get the info I need.

It would be a lot easier to do it your way than through the Live Odds SOAP web service. Although if anyone knows how to go about it I would still like to know just out of interest.

Cheers,
Chris.

My way is simple screen scrap, downloads to a temp txt file then pastes it in excel.

It has only been in the last 12 months i have changed the way i get web info using the CreateObject("MSXML2.XMLHTTP") method, the speed difference is huge.

I have used the xml feeds before but unless you know what to look for can be a bit harder to get the info and not all sites use xml.

I started a thread here http://forums.ozmium.com.au/showthread.php?t=23333 to try and help people understand the web query.