View Full Version : NSW TAB Live Odds help needed

1st February 2012, 12:25 AM
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.


1st February 2012, 03:54 AM
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

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

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

Sub Race()
Application.ScreenUpdating = False
With ThisWorkbook
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
Set temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt"
If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete
Application.ScreenUpdating = True
End With
End Sub

This is the formula i have in cell AL4

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.

1st February 2012, 09:30 PM
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).


2nd February 2012, 05:45 PM
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.


2nd February 2012, 05:58 PM
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.