OZmium Sports Betting and Horse Racing Forums

22nd January 2012, 03:20 PM

norisk

Member

Join Date: Dec 2011

Posts: 334

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.

Attached Files

xml test ws.xlsm (18.7 KB, 844 views)

23rd January 2012, 12:05 PM

norisk

Member

Join Date: Dec 2011

Posts: 334

Anyone?

23rd January 2012, 12:57 PM

Chrome Prince Chrome Prince is offline

Member

Join Date: Jan 1970

Posts: 4,426

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.

__________________
RaceCensus - powerful system testing software.
Now with over 412,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/12/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

23rd January 2012, 01:04 PM

norisk

Member

Join Date: Dec 2011

Posts: 334

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

23rd January 2012, 01:32 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

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.

23rd January 2012, 02:10 PM

Chrome Prince Chrome Prince is offline

Member

Join Date: Jan 1970

Posts: 4,426

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.

Attached Files

xml test ws.xlsm (22.3 KB, 786 views)

23rd January 2012, 02:57 PM

norisk

Member

Join Date: Dec 2011

Posts: 334

Thanks guys, will have a look at it all tonight

24th January 2012, 09:56 AM

norisk

Member

Join Date: Dec 2011

Posts: 334

Thanks to Chrome Prince & Shaun

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

Attached Files

xml test ws.xlsm (25.2 KB, 761 views)

24th January 2012, 10:09 AM

norisk

Member

Join Date: Dec 2011

Posts: 334

updated file to fix a date error

Attached Files

xml test ws.xlsm (25.1 KB, 767 views)

#10

24th January 2012, 10:10 AM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

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.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

	To advertise on these forums, e-mail us.