Help on XML feed

[Rinconpaul]

Can anyone help me with getting the live feed from Tatts into an excel spreadsheet. I've tried Tatts help page but keep getting an error. I've tried many times over the past 12 months but always the same story. I have Excel 2010.

Thanks

[Shaun]

Most of my work has been designed around web queries but if you are after updating prices XML would be better, i have done some work in the past with this, have you copied the sample in to excel and just played with the code to get the information needed.?

[Rinconpaul]

Just get this error massage Shaun. I put todays date in the script to copy.

[Rinconpaul]

Hang on Shaun, I had VR4 not MR4, so I fixed that but now all I get is Saddelcloth no. Horse name, wght & Jockey....no prices. Must be getting warmer, hey?

[evajb001]

I don't have a working version of this so just going by looking at the example but I'm assuming its because your using the example which only pulls in those 4 areas by using:

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

So Shaun can correct me but i'm guessing you'd have to add something like:

Set runnerOdds = winodds.Attributes.getNamedItem("Lastodds")

and then to have this show in excel add something this as well:

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


EDIT: By the way I don't use this at all but thought i'd check it out as i'm into website coding and this whole datafeeds stuff. Give the above a try, Shaun might be able to correct it though if i've missed something?

2nd EDIT: Actually in the attributes part "Lastodds" will bring in the last fluc i think, might have to have this as just "Odds"

[Rinconpaul]

Some error here Josh, debug report below. Greedy as I am...l:), assuming we can overcome this bug, how could you get all races displayed?

[Shaun]

You are correct, you need to add in the extra code and make changes.

This page https://tatts.com/tattsbet_help/info/xml-feed lists all the XML feed pages and if you open a feed you will be able to see what's needed, am kinda busy right now but after todays races i can knock up a sheet that will list the extra items, been awhile since i looked at this myself

[evajb001]

RCP I think it has something to do with the following section needing to be set up for the Winodds part:

Set runnerList = xmldoc.selectNodes("//Runner")

Sheet1.Cells.Clear

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

Set runner = runnerList.Item(i)

I'd have to fiddle with the code later on as about to go to work. But i'd suggest its this section that also needs to be fiddled with to account for the Winodds addition.

[Rinconpaul]

Cheers to both Josh and Shaun, It'll have to wait till after the races now as very busy too, good day so far, hope yours is to?

[evajb001]

RCP after a bit of stuffing around to get it to initially work give this a try:

Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2014/5/21/SR7.xml")

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

Sheet1.Cells.Clear

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

Set Runner = runnerList.Item(i)
Set winodds = oddsList.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")
Set runnerOdds = winodds.Attributes.getNamedItem("Odds")


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

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

Next
End If

End Sub

[evajb001]

Here's the code to get more then 1 race on the same sheet, should help you figure out how to get all races in a basic sense. Obviously you can build on this so that it works out how many races for that venue etc etc but this should give you a good starting block to get it all working.

Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2014/5/21/SR1.xml")

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

Sheet1.Cells.Clear

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

Set Runner = runnerList.Item(i)
Set winodds = oddsList.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")
Set runnerOdds = winodds.Attributes.getNamedItem("Odds")


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

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

Next
End If

Race2
End Sub

Sub Race2()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2014/5/21/SR2.xml")

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

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

Set Runner = runnerList.Item(i)
Set winodds = oddsList.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")
Set runnerOdds = winodds.Attributes.getNamedItem("Odds")


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

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

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

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

If Not runnerOdds Is Nothing Then
Sheet1.Cells(i + 25, 5) = runnerOdds.Text
End If

Next
End If

End Sub

EDIT: I noticed it seems to load quicker then doing the web queries too, might have to be something I look at using instead.

[Shaun]

Nake sure you add the "s" to the address https://tatts.com/pagedata/racing/2014/5/21/SR7.xml

[Rinconpaul]

You're destined for higher things Josh, I've always said that! Not only are you brilliant but you're willing to help others when asked. If you ever need a character reference, just ask.

Now from what I can work out, I'll need to amend the script (is that what you call it?) for each raceday date, venue and race number?

Is the result that I see on the page a continually updating one or is it static, as in a picture of the tote odds at the instant I executed the button?

I wanted this so as to load a scatter chart instantly and start cataloging 'star patterns' ???,.......... you know, for the upcoming book! Which by the way, you'll be getting the first signed copy of...:)

Cheers again.

[evajb001]

RCP, it will be like taking a screenshot of whatever is viewable on the website at the time you press the button.

In terms of changing the date, venue and race number that is something you'll have to do within the script (macro) at the moment, however it should be possible to make it so you can put these details into cells within the spreadsheet, hit the button and it will update without actually having to go within the script and risk disrupting anything.

Let me see what I can come up with and i'll post a bit later. In theory it shouldn't be too difficult but computers find a way of never making things that easy.

[Rinconpaul]

I'll look forward to that improvement. I seem to remember about a year ago you doing something similar?

Anyway here's the first print run below, brilliant.

[Rinconpaul]

I just checked out which horse won SR2, the bottom image on the prev post's jpg. It was that 'faraway star' the longshot Speedy Wally BF $65, the highest priced horse in the race.

As a longshot Layer, I never got caught by that one, in fact no accidents in over 3 weeks, and I have to thank the systems I've built around the Ratings 2 Win data, which had it 'in the Green' for a possibility of an upset win. That just saved me $2k in one bet, the cost of purchasing the Pro package and 3 months subscription. Onya R2W. Still a way to go, to adapt the system for the Layers though....:)

Does this mean an end to 'star gazing', not likely, there's still money to be made.

[Shaun]

I hard codded a couple cells for the date and the race code

on sheet1 in cell I1 put =Today()
In cell K1 put your race code like SR1

Had to also adjust the clear function so it only clears to row E

Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("https://tatts.com/pagedata/racing/" & Format(Sheets("Sheet1").Range("I1").Value, "yyyy/m/dd") _
& "/" & Sheets("Sheet1").Range("K1").Value & ".xml")

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

Sheets("sheet1").Range("A1:E50").Select
Selection.ClearContents
Range("A1").Select

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

Set Runner = runnerList.Item(i)
Set winodds = oddsList.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")
Set runnerOdds = winodds.Attributes.getNamedItem("Odds")


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

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

Next
End If

End Sub

[Shaun]

What you could also do is set up a sheet to bring in the meetings first then you could setup a list to choose the races or if data collection is needed it could be designed to run in auto mode.

[Rinconpaul]

Thanks for your input Shaun. I'd like to see the perfect setup posted here for all forumites to access. There's been quite a few posts on the subject in the past, coz I searched. They were all ad hoc, with no final solution revealed. Not as if your giving the 'Grail' away here. More power to all forumites, I say! Heaven knows, we need it.

[evajb001]

Here you go RCP, give this a try as attached

[Shaun]

As far as xml, yes no real working model but tones of web query sheets, as i post a lot of what i have done and it is similar to xml, i guess the difference is that they provide the xml feed where as with queries i just take what i want and have to alter code when webpages change.

It is good to provide the tools but is better to teach people how to do it because we all want something different.

[Shaun]

Give me an idea of what you would like to display on the page and if i get some time i will work on it.

[Rinconpaul]

Can anyone help me with getting the live feed from Tatts into an excel spreadsheet. I've tried Tatts help page but keep getting an error. I've tried many times over the past 12 months but always the same story. I have Excel 2010.

Thanks
Mate, anything that moves, Dogs, Harness & Racing. Let's go global and look at the overseas stuff covered by them. No, Name, Tote Win, Fixed Price Win,Winner, Placegetters.

That's the wish list......Good man...:)

[Rinconpaul]

At least that'll allow anyone to build a reliable database to analyse, even if only for price. I downloaded the most recent Betfair historical file the other day. I set the filter to <$1.20 win price Pre Event. The results threw up anything with a SP up to $5. A complete waste of time for a historical data site.

The smarties with programming knowledge can build a bot to record accurate prices, which leaves a lot of forumites disadvantaged. I know forumites who still use a ruled school excercise book to record prices. Imagine how empowered they could be with a simple recording program.

It's a legacy, you won't be forgotten for Shaun....:)

[Shaun]

So you want the Unitab site in excel with the ability to record past results with some type of filter system.

Sounds simple enough, well if anything it will give me xml knowledge that i don't correct possess so that's a plus.

[Puntz]

I'll add some snippets of code that helps when Excel slows down.

This one here, deletes Excel's Active Workbook Connections somewhere in the process of a continual web query.

My first count was over 200 connections at the same time. Excel became really sluggish.
I think it meant 200 web queries.
Imagine clicking your mouse 400 or 600 times at the Ribbon to delete these "connections" manually.
These "connections" were weeks old, well after the races had run.

I run this code before after each web query within a Do Until Loop.

So basically, you may consider running the code between the last race and the new race so the memory starts with a clean slate.

Sub Delete_WorkBook_Connections()
' this deletes the connection if above zero count
Do While ActiveWorkbook.Connections.Count > 0
ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete

'counter
Sheets("Sheet10").Range("C21").Value = Sheets("Sheet10").Range("B21").Value
Sheets("Sheet10").Range("B21").Value = Sheets("Sheet10").Range("B21").Value + 1
Loop

End Sub
-----------
To explain this part of the code is basically a counter showing the total times it Loops, ( meaningless/important statistical stuff).


'counter
Sheets("Sheet10").Range("C21").Value = Sheets("Sheet10").Range("B21").Value
Sheets("Sheet10").Range("B21").Value = Sheets("Sheet10").Range("B21").Value + 1

Mine is curently up to 5208.
This means the Excel race worksheet has connected 5208 times in that particular Workbook.

Running the code eliminated the task of clicking the mouse 5208 multiplied by about 3
5208 * 3 would have been lots of mouse clicks, lots of races missed, POT down.


Or do it manually on Excel 2010, click on Data then click on Connections, there you will see it under Workbook Connections.

Run the above code and it goes away, freeing up Excel.

[Chrome Prince]

At least that'll allow anyone to build a reliable database to analyse, even if only for price. I downloaded the most recent Betfair historical file the other day. I set the filter to <$1.20 win price Pre Event. The results threw up anything with a SP up to $5. A complete waste of time for a historical data site.


Forget PE, you want to delete all IP and PE.
Then it's accurate.

[Rinconpaul]

Forget PE, you want to delete all IP and PE.
Then it's accurate.

Beg to differ CP. Take the latest file 28 April to 4 May. If you filter out IP In Play and PE Pre Event, that only leaves NI ?? Not sure what that means, but doesn't matter, take a typical race at random Wyong R1 1/05/14.
If you filter PE and IP OUT, leaves you with NO horses! That's because NI was only applicable for TO BE PLACED on that event.


Another example: 28/4/14 Bathurst R1 Pace runner Amelia Regal Last Taken actually three prices at 3:39..... 50 60 100. How do you know which figure relates to what price classification. This is a simplistic example but the favourite in the race would have lot's of differing numbers displayed.
The prices, that Betfair paid on, were Back 50, SP 81.83 & Lay 110.

That's really accurate, hey? Build a database, don't think so?

Then what are the prices published representing, they're not WAP, SP, Back or Lay. A punter needs some reference to what he has available to him on screen and them providing last price taken is of no consequence. Let's say I wanted to query the file to find the largest price never to had a win. Well I can tell you the figures provided are so far off and bear no resemblance to actual SP.

[Rinconpaul]

Here you go RCP, give this a try as attached

Morning Josh, the only hiccup with this one is that if the field size was greater on the previous race shown (Yesterday SR4) than todays VR1 then it still displays the overrun of horses from the previous race. See the jpg, horses 9 ~ 16 were from SR4 yesterday.

Nearly there mate.....:)

[evajb001]

Just need to add this bit of code in:

Set oddsList = xmldoc.SelectNodes("//WinOdds")

Sheets("sheet1").Range("A1:E50").Select
Selection.ClearContents
Range("A1").Select

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

Then it works like a well oiled machine.

RCP on a side note if you look at SR7 yesterday, see how there is a significant gap on the scatterplot between the runners with the highest odds and all the fancies below that. Is this what your looking for when laying longshots?

Basically was just wondering what the significance of the scatterplot was for you? Obviously don't have to go into huge detail as its something you've clearly researched but just interested.

[Shaun]

Puntz, i use a similar code but don't bother with the loop or count.

If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete

I also delete the queries right after i have imported data.


ActiveSheet.QueryTables.Item(1).Delete

[Puntz]

Shaun, initially the Loop was added because there were so many connections at the time when the sluggishness was discovered, but yeah if the query tables are deleted right after as suggested, Loop method can be optional.
All well and good.
Thanks.

[Rinconpaul]

RCP on a side note if you look at SR7 yesterday, see how there is a significant gap on the scatterplot between the runners with the highest odds and all the fancies below that. Is this what your looking for when laying longshots?

Basically was just wondering what the significance of the scatterplot was for you? Obviously don't have to go into huge detail as its something you've clearly researched but just interested.

I guess the main point of this exercise is to look for patterns that are easily identified and show a profit by either laying longshots and/or betting exotics. In the attachment you'll find four races wherein the longshot won (red), so it's not perfect, nothing ever is in this game. But despite the accidents it might still show a profit (research req'd, hence the aim of this thread to facilitate that). Given that our only filters here are pattern, price and field size. No form or ratings. Without the last bit it makes it harder but you can still make money. I have a bot set at a min price running 24/7 just Laying Aus, NZ trots and UK racing. Now it doesn't make much, $25 a day for $1650L, but multiply that by 365, throw in a couple of accidents and you've got nearly $6k, enough for you and your girlfriend to fly around the world economy class!

So if you can identify price parameters and strike rates per field size and country you'll do OK, add exotics and pattern recognition, who knows, a 1st class ticket RTW???....:)

Thanks for that extra script, I'll throw it in now.

[Rinconpaul]

Shaun, there are probably two types of macro enabled excel excercises appearing out of all this. There's a pre race visual pattern recognition system as in what Josh's provided (also able to be back tested) and a longer term build a database system for analysis. Similar to what Betfair provides. You would have column headings:
Date
Country
Full Race description
Race No.
Selection name
No. of Runners
Tote Win price
Win Flag
Any others people consider important.

From that you could query the datatbase like:
Find all UK selections between $1 and $1.5 in 8 horse fields and from that determine the strike rate.
What do you think?

[Shaun]

Yes that is kind what i was thinking, i can put together the pre race part easier enough i think, just need to understand the format.

As far as a data base i will have to give some thought how best to set this up, i could use the built in fetures but i also could code it, at least with coding we have a little more control on the output, designed my own in the past for a stable of runners so am sure i can come up with some ideas.

[Rinconpaul]

I've attached a mockup Database and Pattern analysis, on Sheet 2, of todays races thus far.

Still a lot of manual work to do to transfer the info from Josh's page to here. A lot of pasting, sorting, graphing. Ideally this could be streamlined heaps. You'll notice two plum quinella, trifecta patterns in NR1 & 2. Once all sorted the database would be awesome.