Archive for April, 2012

Trainers with runners at Sandown

Saturday, April 28th, 2012

Sandown today represents one of the more unusual meetings on the racing calendar, with its combination of top class jump racing mixed with top class flat racing. As such, we see Flat training giants of the like of Henry Cecil rubbing shoulders with their jumps equivalents, such as Paul Nicholls.

Using Smartform we can identify exactly which trainers have runners at Sandown and produce various statistics off the back of this. This sort of query is often a useful prerequisite to working out the relative strike rate of each trainer with runners, for example.

Here, however, we will get the trainer names and show a simple count of the number of runners for each by using the group function.  We’re interested in a count from those trainers fielding the largest team to those trainers fielding only one runner.

This can be done as follows:
mysql> select count(trainer_id) AS ‘#Runners’, trainer_name AS “Trainer” from daily_runners join daily_races using (race_id) where meeting_date=CURDATE() and course=”Sandown” group by trainer_name order by count(trainer_id) DESC;

#Runners Trainer
6 P F Nicholls
5 N J Henderson
5 D Pipe
4 P J Hobbs
3 J H M Gosden
3 Sir H R A Cecil
3 S bin Suroor
3 Miss V Williams
2 W Greatrex
2 D T Hughes
2 N A Twiston-Davies
2 A M Balding
2 M J Attwater
2 G L Moore
2 D McCain Jnr
2 Evan Williams
1 C G Cox
1 N Quinlan
1 Ian Williams
1 R C Guest
1 S Gollings
1 John Berry
1 M H Tompkins
1 B G Powell
1 C J Mann
1 T T Clement
1 A W Carroll
1 R Hannon
1 W S Kittow
1 Simon Earle
1 E F Vaughan
1 Miss H C Knight
1 D K Ivory
1 A King
1 M Keighley
1 Mrs L Wadham
1 J P Ferguson
1 Dr R D P Newland
1 E J Alston
1 Nick Williams
1 George Baker
1 R Lee
1 S Kirk
1 N J Gifford
1 M Appleby
1 A P O’Brien
1 W Jarvis
1 J W Mullins
1 P Phelan
1 Jamie Snowden
1 Dr J D Scargill
1 Mrs Mary Hambro
1 Jonjo O’Neill
1 R Varian
1 B Ellison
1 R Charlton
1 J R Jenkins
1 G McPherson
1 E De Giles
1 M Scudamore
1 Miss E C Lavelle
1 M R Hoad
1 R H Buckler

63 rows in set (0.09 sec)

So no surprise that the top stables in both flat and jumps are best represented today, and some testament to what a classy day’s racing Whitbread day is (though now appears to be bet365 day?).

As for the trainers mentioned, on the Flat Henry Cecil weighs in with an impressive 14 day strike rate (according to Smartform calculations) of 24%, whilst John Gosden and Saeed Bin Suroor are languishing on 8% and 7% respectively.

Over the jumps, Paul Nicholls’ strike rate is 18% in the last 14 days and Nicky Henderson’s is an impressive 26% from 38 runners.

2 year old racing by foaling date

Saturday, April 21st, 2012

As the flat season gets into its swing we will start to see more and more two year old racing.  At this time of year in particular there is very little form to go on.  In order to predict outcomes, punters have to rely instead on stable whispers, the bloodlines and expense of the horses in question, and the records of their handlers in these type of races as well as their current overall form.  We can add to this some signals from the market once the horses emerge into the paddock at the course for the first time.

However, there is an often ignored and occasionally hard-to-find form element that has great significance in the early part of the season – that is the actual foaling date (ie. birthday) of the horse in question, rather than their “official” birthday – ie. 1st Jan.  As a proportion of age, the month or two differences you find in foaling dates are of course significantly greater early in the career of a two year old, though these will diminish in significance over time.   Identifying foaling dates and ranking them can be a laborious process.   Fortunately, 2 year old foaling dates are simple to identify and rank in Smartform from the earliest to the latest date.  Moreover, we can automate the search of 2 year old races and subsequent ranking of each race by age of contender.

Below are the queries you need – applied to today’s racing.

First, let’s identify all the two year old races in the database for today’s racing:

mysql> select race_id, scheduled_time, course, age_range from daily_races where age_range LIKE “%2%” and meeting_date= CURDATE();

race_id scheduled_time course age_range
397147 2012-04-21 18:00:00 Nottingham 2YO only

1 row in set (0.10 sec)

Next, let’s use this race_id to retrieve basic details of all horses competing in it and at the same time rank them by age:

mysql> select trainer_name, jockey_name, name, foaling_date, forecast_price from daily_races join daily_runners using (race_id) where race_id=”397147″ order by foaling_date;

trainer_name jockey_name name foaling_date forecast_price
D Shaw D Swift Top Boy 2010-02-02 20/1
M R Channon Charles Bishop Effie B 2010-02-15 2/1
M Johnston R Ffrench Marshland 2010-02-18 3/1
M R Channon S Hitchcott Golac 2010-03-28 8/1
K A Ryan Amy Ryan Bapak Bangsawan 2010-05-06 7/4

5 rows in set (0.00 sec)

Easy.  We can also automate this with a simple script for all races on any given day – basically saving the race_ids from the first query, and looping over them with the second query.  A script is available for Smartform members that does just this.

By the way, on the above evidence alone, the late foaling date of the favourite,  Bapak Bangsawan, makes him look quite vulnerable, doesn’t it?

Grand National contenders ranked by prize money

Saturday, April 14th, 2012

Prize money won to date is often touted as a better measure of class than any other attribute in horseracing, including the class of races won.

To be sure, it’s not a bad marker, and it’s one of the many attributes available in our programmatic database, Smartform.

But how to use this attribute?   Let’s start with total prize money won by each horse.

We can test this for the favourite in the Grand National – here, we want to take the sum of all historic prize_money, limited to the record over the race type in question, chases, as follows:

mysql> select SUM(prize_money) AS ‘total prizes’ from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);

Total prizes

1 row in set (2.15 sec)

Note the inclusion in the query statement of a subclause to exclude races where the horse in question has been a non-runner.  So far so good, but whilst this may tell us something about class over the horse’s whole career, it does not tell us much about its recent performances.  So let’s constrain the time period to the last year and a bit.

select SUM(prize_money) AS ‘total prizes’ from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”) ORDER BY historic_races.meeting_date DESC;

Total prizes

1 row in set (0.43 sec)

So we’re happy with the time period – and also that we can obtain total prize money for each contender.  But how to compare all the runners – is ranking them by their total a fair representation of ability?  What if some runners raced 10 times and other raced once?  Their average prize should enable a fair comparison, as follows:

>select AVG(prize_money) from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);


1 row in set (0.43 sec)

Let’s check back over the runs of Synchronised to sanity check this average:

> select finish_position, unfinished, prize_money from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”) ORDER BY historic_races.meeting_date DESC;

finish_position unfinished prize_money
1 NULL 284750.00
1 NULL 80172.41
3 NULL 7711.20

4 rows in set (0.41 sec)

There’s a mistake here.  MySQL assumes because the result of prize money is NULL for one of the races, when calculating the average, that the average is only calculated over 3 races and not 4.

We can get around this with a trick to treat NULL values in prize money as zeros, which is actually what we want here, so that the average is calculated correctly, as follows:

select AVG(COALESCE(prize_money,0)) from historic_races JOIN historic_runners USING (race_id) where name=”Synchronised” and meeting_date>”2011-02-02″ and race_type=”Chase” and (unfinished IS NULL OR unfinished != “Non-Runner”);


1 row in set (0.42 sec)

If we do this for every runner, we can start to rank them. Typically, this type of operation is much easier using a programming language interfacing with Smartform – we show examples with Perl and R using Smartform elsewhere on the site – running the above query for every runner and saving results. An example output from such a program which calculates average prize money won, run over the Grand National contenders today, is as follows:

Key: Race time, course, form figures, name, average prize money, forecast price.

16:15, Aintree , 1/1121-4 , Ballabriggs ,    179611.33 , 13.00
16:15, Aintree , 3-P7311 , Synchronised , 93158.40 , 8.00
16:15, Aintree , 144P3/4- , State of Play , 50445.00 , 34.00
16:15, Aintree , 2-133F5 , Organisedconfusion, 32098.06, 21.00
16:15, Aintree , 6741UP , Alfa Beat , 21655.24 , 34.00
16:15, Aintree , 01/35-23 , Planet Of Sound , 21377.50 , 26.00
16:15, Aintree , 18P-13P , Weird Al , 19542.50 , 34.00
16:15, Aintree , 1/321-82 , Junior , 19298.00 , 11.00
16:15, Aintree , 11PB-P1 , West End Rocker , 14067.50 , 11.00
16:15, Aintree , 2F-8511 , Calgary Bay , 13634.50 , 26.00

All done with a Smartform and Perl script.

On the strength of class (remembering that prize money is the best indication of that), we would therefore expect the top few contenders above to be involved at the finish.

Most valuable Flat races today

Saturday, April 7th, 2012

As the Flat season is yet to get into full swing, the racing fare is still jumps dominated.  The Irish National followed by the Aintree meeting next week will keep it that way for a couple of Saturdays to come.   However, we’re mainly concerned with Flat racing analysis here at the blog (though there will be an exception for the National next Saturday), and we can use Smartform (which carries full data for both codes in UK and Ireland) to tell us what the breakdown is.

Prize money is usually a good indicator of where the quality racing lies, so let’s use the Smartform daily updates to rank all races by prize money under both codes today, with the following query:

>select penalty_value,  race_title, scheduled_time, course from daily_races where meeting_date=CURDATE() order by penalty_value DESC;

The ‘order by penalty_value DESC’ part of the query simply means ranking the query results from highest prize to lowest.  If you’re using MySQL via one of the many MySQL interfaces (such as Sequel Pro for the Mac), you can manipulate your query results by point and click to re-order and rank for small queries such as this.

Anyway, here’s the result of listing all races by prize money on offer for today using the query above:

penalty_value race_title scheduled_time course
32490.00 Read Nicholls And McCain On Betfair Handicap Chase (Betfair Chase Series Final) 2012-04-07 14:25:00 Haydock
32490.00 Betfair. Don’t Settle For Less Handicap Hurdle (Betfair Hurdle Series Final) 2012-04-07 14:55:00 Haydock
32490.00 Betfair. For Better Odds Levy Board Handicap Chase 2012-04-07 15:30:00 Haydock
31125.00 Betfred Royal Mile Handicap 2012-04-07 15:50:00 Musselburgh
26000.00 Cork Stakes (Listed) 2012-04-07 14:50:00 Cork
18714.00 Betfred Mobile Sports Snowdrop Fillies’ Stakes (Listed) 2012-04-07 14:40:00 Kempton
13000.00 Friday Evening Racing Handicap 2012-04-07 16:00:00 Cork
13000.00 Mallow Town Summer BBQ Evening July 13th Handicap 2012-04-07 14:20:00 Cork
12938.00 Betfred The Bonus King Whistlejacket Conditions Stakes 2012-04-07 14:45:00 Musselburgh
12938.00 Betfred Goals Galore Handicap (London Mile Qualifier) 2012-04-07 14:05:00 Kempton
12660.00 SIS Handicap Chase 2012-04-07 15:40:00 Newton_Abbot
12450.00 Betfred ‘Double Delight’ Conditions Stakes 2012-04-07 16:20:00 Kempton
12450.00 Betfred ‘When Both Teams Score’ Fillies’ Conditions Stakes 2012-04-07 15:45:00 Kempton
12450.00 Betfred Bonus King Queen’s Prize Handicap 2012-04-07 15:15:00 Kempton
10006.00 Irish Stallion Farms E.B.F. Fillies Maiden 2012-04-07 15:25:00 Cork
9747.00 Find Live totescoop6 Information At Handicap Chase 2012-04-07 15:55:00 Carlisle
9314.00 Irish Stallion Farms E.B.F. Fillies Maiden 2012-04-07 16:30:00 Cork
8410.00 Betfred ‘Goals Galore’ Handicap 2012-04-07 15:20:00 Musselburgh
8092.00 Betfred ‘Hat Trick Heaven’ Handicap 2012-04-07 16:55:00 Kempton
7148.00 Betfair Brings You Better Value Novices’ Chase 2012-04-07 13:50:00 Haydock
7148.00 Betfair Remembers Tim Molony Handicap Chase 2012-04-07 16:40:00 Haydock
6900.00 Best Dressed Lady & Gent (C & G) Maiden 2012-04-07 17:30:00 Cork
6498.00 Better Prices On Betfair Mobile ‘Fixed Brush’ Novices’ Handicap Hurdle 2012-04-07 16:05:00 Haydock
6210.00 Apprentice Handicap (50-70) 2012-04-07 17:00:00 Cork
5175.00 Betfred ‘Double Delight’ E.B.F. Maiden Stakes 2012-04-07 17:00:00 Musselburgh
4874.00 Bet totequadpot Text tote To 89660 Novices’ Chase 2012-04-07 14:50:00 Carlisle
3946.00 Betfred Bonus King Bingo/British Stallion Studs E.B.F. Maiden Stakes 2012-04-07 13:35:00 Kempton
3899.00 St Austell Brewery Handicap Chase 2012-04-07 14:30:00 Newton_Abbot
3881.00 Betfred ‘When Both Teams Score’ E.B.F. Maiden Stakes 2012-04-07 14:10:00 Musselburgh
3422.00 South West Racing Club Handicap Hurdle 2012-04-07 17:20:00 Newton_Abbot
3422.00 Mike Wilding Birthday Celebration ‘National Hunt’ Novices’ Hurdle 2012-04-07 15:05:00 Newton_Abbot
3249.00 Free Racing Post Form At Handicap Chase 2012-04-07 17:05:00 Carlisle
3249.00 Bet totescoop6 Text tote To 89660 Novices’ Handicap Hurdle (Div 1) 2012-04-07 13:45:00 Carlisle
3249.00 Bet totescoop6 Text tote To 89660 Novices’ Handicap Hurdle (Div 2) 2012-04-07 14:15:00 Carlisle
3249.00 Bet totepool Text tote To 89660 Handicap Hurdle 2012-04-07 16:30:00 Carlisle
2738.00 Newton Abbot Races Handicap Chase 2012-04-07 16:45:00 Newton_Abbot
2599.00 totepool Mobile Text tote To 89660 Claiming Hurdle 2012-04-07 15:25:00 Carlisle
2395.00 Keith And Ginny Bell Ruby Wedding Novices’ Handicap Hurdle 2012-04-07 16:10:00 Newton_Abbot
2395.00 RNIB Mares’ Maiden Hurdle 2012-04-07 13:55:00 Newton_Abbot
2264.00 Betfred Bonus King Bingo Handicap 2012-04-07 17:35:00 Musselburgh
2264.00 Betfred Still Treble Odds On Lucky 15’s Handicap 2012-04-07 16:25:00 Musselburgh
1848.00 Check Betfair Before You Bet Standard Open NH Flat Race 2012-04-07 17:15:00 Haydock
1754.00 Follow totepool On Facebook And Twitter Standard Open NH Flat Race 2012-04-07 17:40:00 Carlisle

43 rows in set (0.09 sec)

What does this tell us?  Well, 3 jumps races are still the most valuable as we thought.  Looking at the race titles, you also can’t help but notice that Betfair and BetFred are pumping a lot of cash into sponsorship at the moment (!) and Betfred have sponsored the two most valuable Flat races at different courses.

We can easily alter the original query to see Flat racing only (as opposed to all racing) as follows:

>select penalty_value,  race_title, scheduled_time, course from daily_races where meeting_date=CURDATE() and race_type = “Flat” order by penalty_value DESC;

And if we want to exclude Irish racing, add a further qualifier as follows:

>select penalty_value,  race_title, scheduled_time, course from daily_races where meeting_date=CURDATE() and race_type = “Flat” and country != “IRE” order by penalty_value DESC;

This will leave us with racing at Musselburgh and Kempton, only, and the most valuable race at each course at the top of the query.  So, if betting on quality Flat races is part of the betting plan, then the 3.50 Royal Mile Handicap at Musselburgh (worth over 30k in prize money) and the Listed race for fillies at Kempton (the 2.40, worth c. 20k) are the races to focus on.

Of those, the non-handicap looks of interest.  No detailed race analysis today, but Night Lily catches the eye at a double figure price, having finished a close second in this last year.