I don't think you can use an Excel Web Query, given how the page is constructed.

I discovered phantomJS recently (http://phantomjs.org), a headless browser which lets you get at the "guts" of a page that a server creates with javascript (and where the content remains otherwise hidden if you were to view the HTML source directly). You can call it from Excel using VBA:
In your test.js file you'd have a script that simply opened the URL you wanted, then output it to a text file. Once it was there you could open that text file in VBA and parse it however you like.

The average speed data comes out looking like:
To get distance-specific sectional speeds is a bit more painful. You can either just cherry pick from each runner's last 10 races which are loaded by default; else you'll have to read up a bit more on PhantomJS and learn how to do some Page Automation (selecting the correct item from a dropdown, click a button and then saving the output for parsing).


Mid and Late speeds I imagine would depend quite a bit on the particular race circumstance and track layout - to the point where I would question its usefulness (you can select a horse's races at the same distance at the same track, but most races would undoubtedly not have enough info to draw sound conclusions from). Maybe, if you built up enough data you could find blackbook runners by noting unusually fast late speeds given fast early speeds.

I can see how the Early Speed indicator would be handy in predicting the pace of a race. And in recording the data over a number of years you could determine track/distance combos where Early Speed was particularly advantageous (likely ones with tight/early bends and/or short straights).


I know you raised this on the Communal Ratings thread and called for interest, but I don't really know much about how to apply it. I've used settling positions to predict leaders and pace in the past, and it was accurate often enough to make the exercise feel worthwhile - but probably was not at all helpful, just as often.

Thanks for the reply walkermac, that looks reasonably easy to put in place if I follow your instructions. I'd probably stick with the averages as not every horse would've ran at the venue or at the distance or a combination of both etc etc in the last 10 runs.

I just get the feeling this data could be pretty useful in helping to determine the better prospects of the race based on where you think they may settle. i.e. if a horse has a sub par late speed compared to other runners and you expect it to settle on the rail midfield or at the back of the field then its likely they won't win. Compare that to a horse with good late speed jumping from a decent barrier in a field with mediocre early speed and you've got a combination that could land you the winner. The examples above are the extremes and are more situational but even in a typical race I think the speed data could help combined with settling position predictions.

In terms of the communal ratings proposal, in a previous thread CP showed that certain tracks at certain distances favor leaders much more compared to others. That way if you have a relatively accurate pre-race speed map and an understanding of the pace for the race you can bet on those leader types at particular venues/distances. People stated they were doing this in-running because at that stage your speed map is 100% correct, you can see who is leading. The issue arises by doing it pre race and getting an accurate speed map together.

I feel like its possible and worked at it for a while but couldn't find the success I wanted to. Still a project I plan to try again in the future but need to take a fresh look at it I think.

Just a quick question walkermac,
It's obvious to see now, soon Excel VBA's days are numbered in the area of web query, ( dynamic ) unless MS does what the phantoms and the pythons do, and I doubt it.

So do you see it happening, where there may be many "band-aid" type scriptings to full on scripting to compensate the ever changing web page structures, where once Excel VBA was able to do with a simple Record Macro and now it's becoming more and more not able to ?

I ask this cos the past few weeks on the other thread about the bet sender project, it became obvious this is the way we ( punters/non programmers but savvy types) may have to accept, an adaption to change or compliment VBA.

First, I don't want to paint myself an expert, as the recent thread you referred to was what spurred my learning and led me to these tools. I agree that Excel isn't likely to implement this as they have Web Querys. As close as it's probably going to get is a third party module like Selenium (http://florentbr.github.io/SeleniumBasic). According to their blurb, you can record macros using user actions in a browser (like you can record macros in Excel presently).

It supports PhantomJS, but I'm not sure how you'd interract with a headless browser. ...maybe you record the macro using a regular browser and then just change the VBA code to use the PhantomJS instead of Firefox. That is, in the auto-generated code, just change:

to

Though you can't see the actual data in the HTML code (when you View Source in a regular browser) they still have a named placeholder for it. *shrug* I'm not sure... There's some documentation on Selenium here: https://code.google.com/p/selenium-vba/.

Thanks WM



Yep, learn as tools.


Thanks

Hi evajb001

You need to adopt caution when using these types of figures as they have been produced using the average of a horses time through a particular section. If for example a horse has had 10 runs and they have gone hard early in say half of those then those races (making up 50% of the sample) then they are going to show it has run slow final sectionals. On the flip side if 5 of the runs that are used are based on slow early sectionals then it skews that part of the overall sample buy showing that it has fast closing sectionals. The problem and therefore the reality is that this will most likely lead you down the wrong path if relying on them.

Fortunately or unfortunately depending on your view, good quality sectional data requires a significant investment. Personally we invest a substantial 5 figure sum purchasing sectional data from what is essentially a closed shop and 6 figures when it comes to obtaining all the data that we require. Obviously though we feel that the benefits that we get from doing so justify the expense.

__________________
Regards


Paul Daily - Ratings2Win Pty Ltd (Director)
R2W Axis - Axis is Australia's leading horse racing software and database; with sophisticated form analysis tools and accurate performance ratings that include Hong Kong.
http://www.ratings2win.com.au/

..., or spell check ?

You mean, sample by showing

"EXACTLY"

Taking years to build my own the above is 100 % correct,
While not quite in the same category as R2W i found out very quickly.

PUT IN NOTHING.
TAKE OUT NOTHING..

Whether it be buying data or setting up data,
Somewhere along the line you will have to " PAY".

Unfortunately this type of data is one of the most expensive.

Good Luck with it all.

Cheers.

I came across an article today that reminded me of this thread. I won't link to it as it'll probably be edited out anyway...

"attempting to determine whether horses take up running positions according to their raw early speed ability or more in keeping with their historical running style."

"the table below, displays a predicted settle position using only historical settle position data as the basis for the prediction, versus actual settle position. Those predicted to lead in the race did so on 29.3% of occasions and were in the first 3 in running 62.6% of the time."

"the table below shows a predicted settle position using only raw early sectional time speed data (ETRPrR) as the basis for the prediction, versus actual settle position. Those predicted to lead in the race now did so on 25.1% of occasions and were in the first 3 in running 55.1% of the time. This was from the same sample of approx. 1,800 races."

"What we can do is look at how the two measures interact with one another. It makes sense that a runner with top ranking on historical run-style and the top ranking on early speed ability should lead races at a higher rate than either factor considered independently. This in fact is the case, with this type of runner leading on 43% of occasions (up from 29% based on one measure only). Likewise, a runner with top ranking on historical run-style and a ranking on early speed ability outside of the top 6, ends up leading races at a rate of 13%, much lower than the single measure, 29% benchmark."

Interesting post walkermac. When mechanically calculating the predicted settling positions for a race myself I use the historical settling position approach. My calcs are based on the settling and turn positions from the horses last 4 starts and then also take into account barrier and weight them each differently. The weightings to each are based on correlation to settling position of today's race.

I haven't got stats in front of me for accuracy of my settling predictions but from memory my predicted leader actually lead approx 30% of the time, and was in the top 3 65-70% of the time.

Greater accuracy could probably be squeezed out of this as well if I re-worked it and added/tested some other features but it serves its main purpose for now. In terms of predicting a leader on a favorable leaders track with not much pace in the race, Tawteen was the perfect example on saturday at the valley for easy money.