How to use a Spreadsheet to Analyze Race Results
Using Spreadsheets to Calculate the Outcome of Horse Races
Nov 26, 2008
Information on horse racing information is freely available on many web sites around the world, and it is very easy to download data such as:
- the time and date of a race
- the current starting price of each horse
- the results of any races already run
By combining all of this information into a spreadsheet then it becomes possible to analyze the data and (hopefully) make a lot of money. However, before investing too much money, it's worth looking at some real races and how bookmakers make their books.
Aintree: Sunday, November 23, 2008 12:30
1st My Moment 12/1
2nd Fairyland (favorite) 13/8
3rd Honest John 7/4
4th Going Wrong 40/1
5th Mighty Moon 16/1
6th Big Burrows 40/1
7th Youngstown 20/1
8th Geojimali 40/1
Pulled up:
Drawback 14/1
Ebert 50/1
Caravel 7/1
At first glance this would appear to be a bad start for the bookmaker with the winner (My Moment) coming in at 12/1; but how can the punter make the most of this? Are there any strategies that statisticians can make use of that would guarantee choosing the winner? To see if this is possible the dynamics of the betting can be better understood by analyzing them in a spreadsheet (such as Microsoft Excel or OpenOffice.org Calc).
Using a Spreadsheet to Calculate the Betting Returns in a Horse Race
Obviously the most important thing that anyone can ask is "How much money will I win?". The first step to answering this question is to place the details of the winning horse into the first three columns of a spreadsheet:
A1: 1st
B1: My Moment
C1: '12/1
And then the winning returns can be calculated from:
D1: =MID(C1,1,FIND("/",C1)-1)
E1: =MID(C1,FIND("/",C1)+1,20)
F1: 1
G1: =F1*D1/E1+F1
The stake (the amount bet) is placed in F1 and the winning return is displayed in G1 - and in this example an initial stake of $1 returns $13. Now, given that there were 11 runners in the race there would appear to be a very obvious (and simple) strategy - place $1 on each horse in the race:
- total stake = $11
- winning returns = $13
- profit = $2
Of course, the next step is to apply the same technique to every race of the day and to see if the trend continues:
Time Paid Won
12:30 $11 $13
13:05 $13 $9
13:40 $13 $12
14:15 $9 $5.50
14:50 $13 $8.50
15:25 $16 $26
And so, over the whole day:
- total stake = $75
- winning returns = $74
- loss = $1
Obviously that's not the way to go - perhaps a better way is to use the likelihood of a horse winning to govern the size of wager.
Using a Spreadsheet to Calculate the Percentage Probability in a Horse Race
In order to calculate the percentage probability of a horse winning a race an extra column is required:
H1: =100*E1/(E1+D1)
So, from this My Moment actually had a 7.79% probability of winning the race, and therefore should have received 7.79% of the initial stake.
However, if the spreadsheet formulae are applied to all of the horses in the race, and the percentage probabilities are added up, then an interesting thing will be found - the total is not 100% as the novice might expect, the total is actually 121.24%. This is known as an over-round book.
The Over-Round Book
The fact that the percentage probabilities add up to more that 100% is not a mistake - the book is designed that way, and it means that (in this example) anyone placing bets on all of the horses would actually have to place $1.21 in order to guarantee a $1 win - giving the bookmaker a profit of $0.21 regardless of which horse wins. This can be seen by adding a few more columns to the spreadsheet:
I1: =F1*H1/100
J1: =I1*D1/E1+I1
This will result in a $0.08 stake producing a $1 payback, however if all of the stakes (in column F) are summed then it confirms that a total investment of $1.21 is needed:
1st My Moment 12/1 $0.08
2nd Fairyland (f) 13/8 $0.38
3rd Honest John 7/4 $0.36
4th Going Wrong $0.02
5th Mighty Moon 16/1 $0.06
6th Big Burrows 40/1 $0.02
7th Youngstown 20/1 $0.05
8th Geojimali 40/1 $0.02
Pulled up:
Drawback 14/1 $0.07
Ebert 50/1 $0.02
Caravel 7/1 $0.13
Total outlay: $1.21
Summary
Spreadsheets are a powerful way of analyzing horse racing results, however once the details are entered into Microsoft Excel or OpenOffice.org then it's easy to see that:
- laying equal stakes on every horse may lead to profits on individual races, but lead to an overall loss
- the over-round book means that more has to invested in a race than can be won back
So, the spreadsheet analysis shows that it is possible to make a profit by betting on horse racing, but that profit belongs to the bookmaker.
 |
 |
