OZmium Sports Betting and Horse Racing Forums

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)

- - Basic XML assistance (http://forums.ozmium.com.au/showthread.php?t=23272)

Basic XML assistance

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.

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.

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

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.

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.

Code:

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.

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
    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.

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.

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

Thanks to Chrome Prince & Shaun

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

updated file to fix a date error

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.

This code includes the error handling for scratched horses

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.

Code:

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

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