Excel Racecards

[Purwell]

I have been searching for several years now to find a way to import race cards into Excel which I use to crunch the numbers for my ratings.

I use HRB to do it at present, and it works very well and is worth the money as far as I am concerned, as I use it for other purposes as well.

I have recently come across BetfairGuru from which you can download the days cards in CSV form. Save the file to your computer and then open a new blank work book in Excel and import the CSV file as a text file. It is a bit fiddly I must admit, but still faster than typing the data manually.

You can now extract the particular races that you are interested in and analyse them on another Excel sheet in any way that you wish. I intend at some stage to write a Macro to suit my existing ratings sheet at some stage but for the moment I will continue with HRB.

BetfairGuru can be found at: http://www.betfairguru.co/free_Racecards

HRB is at: http://www.horseracebase.com/horse-racing-today.php?raceid=7489366&viewselection=1

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Green]

Hi Purwell, there are a couple of other options you could look into if you’ve not already considered them.

First, Excel has a function on the Data menu to import web page content. This can be a bit hit and miss, some web pages import better than others and there seems to be differences in the way it works in the various versions of Excel.

Another option would be to write an Excel macro that ‘scrapes’ the details from the race card web page directly into your ratings sheet. This is probably a more flexible and complete solution but would require a bit more work to set up.

Good luck!

[Purwell]

I have not tried the data import function, will have a look tomorrow.
As regards “scraping” I have no idea how to do this!

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[GeorgeJ]

“Another option would be to write an Excel macro that ‘scrapes’ the details from the race card web page directly into your ratings sheet.”

That’s what I do, and indeed that is how I get the results each day to put in my databases (pretty time-consuming at this time of the year with five six meetings a day, and not many cards with what used to be the traditional six races).

It is of course a matter of programming to scrape, manipulate where necessary and then place the data in the format from which one analyses races. For cards, I am content with thirteen data items:

horse name
date of race
course
VDW class rating*
average OR class rating*
distance
weight
OR
going
course type*
trainer
days since last run
claim, if any

which go straight into the Excel application I use for analysis.

The three with * are not simply scraped but derived from data that is – for example if the course is Epsom, course type shows up as “speed”, if it is Carlisle, “power”.

Other data is possible, eg draw, but as I rarely worry about draw I handle that differently when I need to (sprints at Chester and Wolverhampton in particular).

The one data item my contact hasn’t managed to find a satisfactory way of downloading, because of the way it is presented and coded on the RP site, is jockey. But there again, I have never been put off backing a good thing because of who is riding and nor do I believe a horse becomes a good thing because of who is riding.

I haven’t sufficient programming skills myself, but found an Excel expert in Indonesia whose charges are very modest. Happy to give you his email address if of interest

[Purwell]

Thanks for the reply, but I think it all sounds a bit too complicated for me.
At the moment I can get everything that I need from HRB.

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[GeorgeJ]

Yes, it is certainly too complicated for me. I can write complex Excel formulas but the coding for scraping is quite another matter.

But when someone has done it for you, the result is easy to use. The race that interests me most today is the 7.35 Windsor, the card for which took me all of ten seconds to download into my application ready for analysis:

https://www.dropbox.com/s/edminw7juakajpy/7.35%20Windsor.xlsm?dl=1

[Purwell]

.. 7.35 Windsor
No. Name Rating
1 A Sure Welcome 91.00
2 Lihou 63.00
5 Rose Hip 55.00
8 Operatic (IRE) 42.00
6 Endowed 38.00
9 Second Collection 36.00
4 Glamorous Anna 33.00
7 Firepower (FR) 27.00
3 Huraiz (IRE) 1.00

I had a look at this race earlier and these are my ratings which are derived from allocating a numerical value to various criteria including; position in last three races, course or distance winner, days since last race and the current odds available.

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[GeorgeJ]

A problem for me with A Sure Welsome is that he has an unusual profile – not many 7yos win three handicaps in a calendar year and fewer still make it four. In the nine full years prior to 01/01/21, I can only find 166 who won three handicaps and 44 of them went on to win a fourth, and of the 44 only six did so off ASW’s current mark or higher.

[Purwell]

A Sure Welcome certainly didn’t seem to be in the mood to race did he?

I have managed to convert the download from Betfair Guru to suit my Excel sheet with only one problem. The course and/or distance indicators “C” and “D” are not recognised by Excel. I’m guessing that they are in some sort of graphic format. The only way I can enter them is to type in a separate column alongside and then delete the original column. No great problem for me as I rarely consider more than three races per day.

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Green]

There shouldn’t be any problem to extract the jockey or the draw details, George, the information is there on the page and also in the website code so there’s no reason why it can’t be downloaded.

Purwell, the C and D can probably be extracted via code as well although it sounds like you’ve got a reasonable method that isn’t too time consuming.

I’d be happy to help with either of these problems if required.

[GeorgeJ]

Green

Thanks.

It is two or three years ago that my contact in Indonesia reformatted my results and database applications and the jockey was the one thing he couldn’t manage. I’ll ask him if, from his perspective, things have changed and he can now add jockey in.

If it is still not possible, I’ll ask him why (he did tell me those years ago but I’ve long forgotten the specifics) and post his response in the hope that you can explain where he is going wrong.

[Purwell]

I have solved the problem of getting the CD etc into my worksheet.
When they are downloaded from the Betfair Guru site they look like this “CD | |”; I deleted the vertical lines and that was a mistake. If I leave them exactly as downloaded, they work perfectly in my Excel formulas.

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Purwell]

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Purwell]

I still cannot post a table from Excel into here.
Does anyone know how to do it?

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Purwell]

These race cards can be easily imported into Excel. https://backthathorse.com/

I've stumbled on the side of twelve misty mountains
I've walked and I crawled on six crooked highways

[Author]

Posts