Basic XML assistance

[norisk]

Hoping someone can help me with what I am sure is a fairly simple request - I have setup the example XML code from the Tatts website -

http://tatts.com/tattsbet_help/info/xml-excel-example

& have even manipulated it to include extra fields such as the ratings, form etc but for the life of me cannot work out how to also include the win & place odds.

I have attached the excel file & if anyone can assist it would be much appreciated.

[norisk]

Anyone?

[Chrome Prince]

Yes I have problems too.
The runner list is different to the odds list.
You need to make a new variable I think that is separate to the runner data.

If you can leave it with me tonight, I may be able to work out an xml map for the site and modify it and then rejig the code.

I'll have a crack at it, but I'm not up to speed with xml.

[norisk]

Cheers Chrome, I know enough about VBA to be dangerous but ended up going around in circles trying to get this to work.

[Shaun]

I did create a sheet to do this but can't it right now as i have another sheet running and it will stuff of my macros if i open another sheet.

Will try and help with looking at it, here is the original code.

Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2010/6/28/VR6.xml")

If (xmldoc.parseError.errorCode <> 0) Then
MsgBox ("An error has occurred: " & xmldoc.parseError.reason)
Else
Set runnerList = xmldoc.selectNodes("//Runner")

Sheet1.Cells.Clear

For i = 0 To (runnerList.Length - 1)

Set runner = runnerList.Item(i)

Set runnerNumber = runner.Attributes.getNamedItem("RunnerNo")
Set runnerName = runner.Attributes.getNamedItem("RunnerName")
Set runnerWeight = runner.Attributes.getNamedItem("Weight")
Set riderName = runner.Attributes.getNamedItem("Rider")

If Not runnerNumber Is Nothing Then
Sheet1.Cells(i + 1, 1) = runnerNumber.Text
End If

If Not runnerName Is Nothing Then
Sheet1.Cells(i + 1, 2) = runnerName.Text
End If

If Not runnerWeight Is Nothing Then
Sheet1.Cells(i + 1, 3) = runnerWeight.Text
End If

If Not riderName Is Nothing Then
Sheet1.Cells(i + 1, 4) = riderName.Text
End If

Next
End If

End Sub

Here is the part of the exml page we need.
Runner RunnerNo="1" RunnerName="MAHAZ KINTA" Scratched="N" Rider="J LYON" RiderChanged="N" Barrier="6" Handicap="0" Weight="58.0" LastResult="77X" Rtng="84"
WinOdds Odds="16.80" Lastodds="13.70" LastCalcTime="2012-01-23T12:28:35" CalcTime="2012-01-23T12:47:22" Short="N"/
PlaceOdds Odds="10.20" Lastodds="7.20" Short="N"/

This is i would be looking at doing.

Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2010/6/28/VR6.xml")

If (xmldoc.parseError.errorCode <> 0) Then
MsgBox ("An error has occurred: " & xmldoc.parseError.reason)
Else
Set runnerList = xmldoc.selectNodes("//Runner")

Sheet1.Cells.Clear

For i = 0 To (runnerList.Length - 1)

Set runner = runnerList.Item(i)

Set runnerNumber = runner.Attributes.getNamedItem("RunnerNo")
Set runnerName = runner.Attributes.getNamedItem("RunnerName")
Set runnerWeight = runner.Attributes.getNamedItem("Weight")
Set riderName = runner.Attributes.getNamedItem("Rider")

If Not runnerNumber Is Nothing Then
Sheet1.Cells(i + 1, 1) = runnerNumber.Text
End If

If Not runnerName Is Nothing Then
Sheet1.Cells(i + 1, 2) = runnerName.Text
End If

If Not runnerWeight Is Nothing Then
Sheet1.Cells(i + 1, 3) = runnerWeight.Text
End If

If Not riderName Is Nothing Then
Sheet1.Cells(i + 1, 4) = riderName.Text
End If

Next
Set winoddsList = xmldoc.selectNodes("//WinOdds")


For i = 0 To (winoddsList.Length - 1)

Set winodds = winoddsList.Item(i)

Set Wodds = winodds.Attributes.getNamedItem("Odds")


If Not Wodds Is Nothing Then
Sheet1.Cells(i + 1, 1) = Wodds.Text

End If

Next
Set placeoddsList = xmldoc.selectNodes("//PlaceOdds")


For i = 0 To (placeoddsList.Length - 1)

Set placeodds = placeoddsList.Item(i)

Set Podds = placeodds.Attributes.getNamedItem("Odds")


If Not Podds Is Nothing Then
Sheet1.Cells(i + 1, 1) = Podds.Text

End If
End If

End Sub


This is just an idea with no testing you would need to debug it.

I will see if i can find my sheet later today after the races.

[Chrome Prince]

Here are the win and place pool odds.
Note that scratchings have really high odds amounts rather than scratched or 0.
Haven't figured this out yet, but at least the odds are there.

[norisk]

Thanks guys, will have a look at it all tonight:)

[norisk]

Thanks to Chrome Prince & Shaun

Ive uploaded a file for those interested to use as starting point.

[norisk]

updated file to fix a date error

[Shaun]

Looking at those 2 codes in the sheets shows me i was on the right path, a bit mixed with some code.

With this starting point you should be able to add anything that is needed to the code to extract data.

[Neurokahuna]

Hopefully this resolves your issue. I am also ripping this data from Unitab and it returns and processes it really fast in Excel. Combine it with a 'pull' of the Meetings List from Tab Racing's Live Odds Web Service and you can build a really quick tree navigation list box that can be used to get each race's details from Unitab's XML feed very efficiently and cleanly.

Sub LoadRaceOdds()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim xmldoc As MSXML2.DOMDocument
Dim RaceDate As String
Dim RaceNumber As String

RaceDate = Worksheets("RaceCard").Range("A3").Text
RaceNumber = Worksheets("RaceCard").Range("A4").Text

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False



xmldoc.Load ("http://tatts.com/pagedata/racing/" & RaceDate & "/" & RaceNumber & ".xml")

If (xmldoc.parseError.ErrorCode <> 0) Then
MsgBox ("An error has occurred: " & xmldoc.parseError.reason)
Else

'Last Odds Displayed
Set runnerlastlist = xmldoc.SelectNodes("//Runner/WinOdds")

Worksheets("Racecard").Range("b2:25").ClearContents

For I = 0 To (runnerlastlist.Length - 1)

Set runner = runnerlastlist.Item(I)

Set runnerLastOdds = runner.Attributes.getNamedItem("Lastodds")

If Not runnerLastOdds Is Nothing Then
Sheet1.Cells(I + 2, 2) = runnerLastOdds.Text
End If

Next

'Win Odds Displayed
Set runnerwinoddslist = xmldoc.SelectNodes("//Runner/WinOdds")

Worksheets("Racecard").Range("c2:c25").ClearContents

For I = 0 To (runnerwinoddslist.Length - 1)

Set runner = runnerwinoddslist.Item(I)

Set runnerWinOdds = runner.Attributes.getNamedItem("Odds")

If Not runnerWinOdds Is Nothing Then
Sheet1.Cells(I + 2, 3) = runnerWinOdds.Text
End If


Next

Worksheets("Racecard").Activate
Range("c2:c25").Select
Selection.Replace What:="1638.30", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


'PLACE ODDS DISPLAYED

Set runnerplaceoddslist = xmldoc.SelectNodes("//Runner/PlaceOdds")

Worksheets("Racecard").Range("d2:d25").ClearContents

For I = 0 To (runnerplaceoddslist.Length - 1)

Set runner = runnerplaceoddslist.Item(I)

Set runnerPlaceOdds = runner.Attributes.getNamedItem("Odds")


If Not runnerPlaceOdds Is Nothing Then
Sheet1.Cells(I + 2, 4) = runnerPlaceOdds.Text
End If

Next

Worksheets("Racecard").Range("d2:d25").Select
Selection.Replace What:="1638.30", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End If



End Sub

[Monty]

Hi All

This is my first post so Hello to all.

I am very interested in what you guys are discussing here and have been playing around with similar code myself.

I was wondering if any of you knew if there was a similar kind of xml feed for form for Australian races?

I would be looking for say the last 10 races for each runner in a particular race.

Cheers