mathare
9th August 2006, 22:31
Introduction
A couple of months ago (at least!) I started looking at BigC's ratings with a view to dutching them, something that was seemingly very popular at the time. I started looking at systems based on about a week's data from May. I soon realised that if this was worth doing it was worth doing properly so I started to compile a lot more data with which to work.
I devised a number of possible ways of selecting which runners to dutch and then set about testing them with some Excel trickery. The results of that study, along with full details of the systems, are given below.
The Data
All systems have been tested on the same data set. I copied BigC's ratings from his forum posts from 1st April to 14th May inclusive and added 21st May in as well. These were converted to Excel format and then for each date covered I used the daily Runners Index from the Racing Post website to obtain the full list of horses declared for that's day's racing and their SP. From then on a bit of VBA was required to extract the SP of each rated horse
giving me a spreadsheet giving and rating and SP for each horse in each UK race in the period covered.
The Next Step...
Having got the list of all declared runners in each race along with their BigC rating and SP I then wrote some VBA to mark the NRs and then compute the data needed to make the dutching calculations easier. Each SP was turned into a % of the book (1 / SP+1, e.g. 2/1 = 33.33%).
Then came a tricky bit. How could I set up the spreadsheet to automatically calculate the stakes for all selections to be dutched? The actual calculations are not very tricky at all and can easily be done with in-cell formulae but the formulae need to be set up carefully. I couldn't just do the first one and fill down to the end row because the formulae contains bits that are based on the particular race. For example when working out the total book included
in the dutch I used:
=SUMIF(H2:H12,"Y",G2:H12)
where G contains the % book for each runner, H is a Y if the horse is in the dutch and rows 2 to 12 form this race.
That same formula needed to be used for each runner in the race. But for rows 13 to 25 (the next race) I needed to use:
=SUMIF(H13:H25,"Y",G13:H25)
and so on for all races.
So how do you overcome problems like this? The same way I think to address any problem in Excel - write a bit of VBA to do it!
I wrote a routine to generate the in-cell formulae for each runner in each and every race. This routine wrote the formulae that computed the total % book in the dutch, the stake for each runner and the profit on each runner should
they win. It also wrote out a formula that would calculate the profit for each race once the runners to be dutched were selected by means of a Y in column H.
I wrote another routine to calculate a few rolling averages too but more on those later.
The Starting Point
As I was testing this for my own use I wanted to make the starting conditions as representative of what I would do as possible. So I set up the following:
Stake per race = £20
Minimum stake = £2
My plan had been to set aside a bank of £200 and use Bet-IE to dutch the selections so I had this £2 minimum stake in place. I figured starting with £20 per race might be a tad risky as it was 10% of the starting bank but as this was only paper-trading I figured I see it through and see what came out at the end of it all.
The Systems
I set out with a few fixed ideas of what I wanted to test and this quickly grew to be a reasonably long list with me testing over 500 different systems in the end!
OK, technically speaking it was only really 9 categories of system each with a few tweaks and twiddles in the form of selections filters applied but it all amounts to 544 different systems being tested.
The following briefly describes the categories of systems tested. The number in brackets after the system category identifies the number of individual systems in that category.
Top Rated (80)
This was where I started really. These systems simply involve adding in runners starting at the top of the ratings and working down the list. The top 1 to 10 runners were included in the dutch.
Bottom Rated (80)
BigC had noticed that some big winners had come from the bottom of his ratings so I set about testing whether it was worth following. Here runners were added in starting from the foot of the ratings in each race and working upwards.
The bottom rated 1 to 10 runners were dutched.
Mid-Up Rated (80)
At various points during the early testing of these systems I was in quite regular contact with BigC and during one of those discussions he suggested I could test adding runners in from the middle of the list rather than the top or bottom. While I was happy enough to do this it proved very tricky. My idea had been to add an equal number of runners in above and below the midpoint of the list but the way I had designed my code meant this was getting tricky so I opted for a slight variation on the theme. Mid-up Rated systems involved me starting at the middle of list and working towards the top of the ratings, adding runners into the dutch as I went. Systems involving 1 to 10 runners were tested.
Mid-Down Rated (80)
It should come as no surprise to you to learn that for these systems I started at the middle of the list and selected runners for the dutch by working towards the bottom. Again 1 to 10 runners in each race.
Min Rated (72)
Another simple idea this one, dutch all runners whose rating is at least X where X equalled 150 then 180 to 250 in steps of 10.
Max Rated (104)
Unsurprisingly given the previous category, these systems dutched all runners whose rating was no greater than X where X went from 50 to 170 in steps of 10.
Just Enough (16)
These systems were (I think) how BigC originally got started with the dutching idea on his ratings. The premise is to dutch as much of the field as possible yet still manage a profit should any of the horses win. Basically dutch as many as you can without exceeding a 100% book. Why did I call this bunch 'enough'? Dunno really. I had it in my head that it was enough runners to still be sure of a profit but the more I think about it the lamer that category title sounds. But I'm stuck with it now.
Average Rating (16)
The first category to use one of the rolling averages I said I wrote a function to compute. What I did was calculate the rolling average of the rating of the race winners and use that as a minimum rating, dutching all runners rated above that point.
I have just this minute realised that I made a slight balls-up here in that I was checking the rating of each runner against the average winner's rating including the winner of the race in question. But this should be negligible really and can be ignored, especially when you see the figures!
Percentage of Runners (16)
Another slightly weird idea this one but one of the first I came up with, funnily enough. This uses the other rolling averages calculated, as I said earlier, by the VBA. The premise here is to work out the average position of
the race winner in the list of ratings, expressed as a percentage of the field. So if the top rated is the winner in a 10-runner race it will be 10% of the way down the field, 3rd rated in a 14 runner race is 21.4% down the list etc.
All will hopefully become a bit clearer later.
Exit Conditions & Selection Filters
For each system I would take each race in turn, starting at the appropriate place in the ratings list for that race (which depends on the category of system I am testing) and then enter a Y next to each horse that meets the system rules and is therefore part of the dutched bet.
There a few obvious exit conditions to each loop going through adding horses to the dutch. These include:
1) The horse is a non-runner. Not much point adding these as they have no SP so don't have a % book calculated
2) We've hit the end of the field. In an 8-runner race you can't dutch the top 10 runners, it's simply not possible.
For such a race dutching the top 8, 9 or 10 would give the same result i.e. all 8 runners would be selected. In all races where less than the number of runners specified met the selection criteria as many as possible were added to the dutch even if this fell short of the target number.
The system categories above are simply the basic systems. Each category had a series of selection filters that could be applied to form a new set of selection criteria, and thus a new system, to see how this affected the profits compared to the basic system.
The selection filters, along with which basic systems they were applied to, are described below.
Exc Races
(all systems bar Just Enough)
My lazy shorthand at work here I'm afraid. If the dutch would result in a guaranteed loss then the race was scrapped and no dutched bet was placed on that race. There didn't seem a lot of point dutching runners if we guaranteed ourselves a loss no matter who won the race so this was one of my first filters.
Exc Runners
(all systems)
Sorry, more lazy shorthand going on here. What this actually means is that only selections whose stake in the bet was greater than the defined minimum (£2) were included. Obviously taking one selection out adjusts the stakes for
all the others so runners below minimum stake were removed in reverse order to which they were added until all runners were staked to at least £2.
Allow Singles
(Min Rating, Max Rating, Average Rating, Percentage of Runners)
This was supposed to be a dutching test but I noticed often that only 1 selection was being made per race, and not just for systems where the criteria specified I had to have only 1 selection! So I wondered what would happen if I got rid of these bets. A strange part of my brain was happy to put £20 on a race spread across several runners but it seemed a lot of have running on a single nag.
Count NRs
(Top Rated, Bottom Rated, Mid-Up Rated, Mid-Down Rated)
Suppose you have a 14-runner race and you're supposed to be dutching the top 5 rated. That's easy enough isn't it?
Now suppose the third-rated horse is a non-runner. Do you dutch the remaining four (1st, 2nd, 4th and 5th rated) or do you dutch the top 5 of the remaining horses who actually started the race and so include the 6th rated? If this filter is set to N then that's exactly what you'd do. You would skip over NRs as if they didn't exist and keep selecting horses to add to the dutch until you reached the specified number of horses. Were this filter set to Y then in the example above you would dutch only four horses.
Exit Loop
(Just Enough)
This one was a specific filter for the Just Enough basic system. When adding runners to the dutch I would monitor the potential profit from the race and when it slipped below zero I would uninclude the last selection. However, it was often possible that horses further down the list could be added to the dutch without turning our potential profit into a guaranteed loss - it all depends on the odds. What this oddly-named filter did was continue to add in horses further down the list where possible when the filter was set to N but when set to Y this filter would mean the loop was exited and no futher runners would be considered for inclusion.
Minimum Profit
(Just Enough)
Another filter specific to the Just Enough category and for a change one with a sensible name. I set the minimum profit level to 10% of the race stake (so £2) and like the Exc Races filter if I couldn't be sure of at least that level of profit if the bet came in then I wasn't gambling on this race at all.
Greater Than
(Average Rating)
This was inspired by something BigC put in his C-Max thread recently, about how a runner being greater than a certain rating rather than being greater than or equal to that rating can make a big difference to the figures. And so this filter was born. When it's set to Y horses are only included when they are greater than the average rating, else just being equal to it is good enough to warrant selection.
Runners or Field
(Percentage of Runners)
Strictly speaking this is just an extension of the Count NRs filter. Think back to the Percentage of Runners category of systems. I said I copmputed a rolling average of the winning position in the rating list expressed as a percentage of the runners in that race. I actually computed two averages - one based on the position expressed as a percentage of the actual runners in the race and another based on the position expressed as a percentage of the declared runners, i.e. counting the NRs as well.
The above filters were often combined in several ways in order to create further sets of selection criteria and thus more systems. These 8 filters along with the 9 basic systems and the ability to specify where to start in the ratings list (top, bottom etc) were all that was needed to create the 500+ systems tested in this study.
The results of this study may be found here... (http://www.win2win.co.uk/forum/showthread.php?p=358675)
A couple of months ago (at least!) I started looking at BigC's ratings with a view to dutching them, something that was seemingly very popular at the time. I started looking at systems based on about a week's data from May. I soon realised that if this was worth doing it was worth doing properly so I started to compile a lot more data with which to work.
I devised a number of possible ways of selecting which runners to dutch and then set about testing them with some Excel trickery. The results of that study, along with full details of the systems, are given below.
The Data
All systems have been tested on the same data set. I copied BigC's ratings from his forum posts from 1st April to 14th May inclusive and added 21st May in as well. These were converted to Excel format and then for each date covered I used the daily Runners Index from the Racing Post website to obtain the full list of horses declared for that's day's racing and their SP. From then on a bit of VBA was required to extract the SP of each rated horse
giving me a spreadsheet giving and rating and SP for each horse in each UK race in the period covered.
The Next Step...
Having got the list of all declared runners in each race along with their BigC rating and SP I then wrote some VBA to mark the NRs and then compute the data needed to make the dutching calculations easier. Each SP was turned into a % of the book (1 / SP+1, e.g. 2/1 = 33.33%).
Then came a tricky bit. How could I set up the spreadsheet to automatically calculate the stakes for all selections to be dutched? The actual calculations are not very tricky at all and can easily be done with in-cell formulae but the formulae need to be set up carefully. I couldn't just do the first one and fill down to the end row because the formulae contains bits that are based on the particular race. For example when working out the total book included
in the dutch I used:
=SUMIF(H2:H12,"Y",G2:H12)
where G contains the % book for each runner, H is a Y if the horse is in the dutch and rows 2 to 12 form this race.
That same formula needed to be used for each runner in the race. But for rows 13 to 25 (the next race) I needed to use:
=SUMIF(H13:H25,"Y",G13:H25)
and so on for all races.
So how do you overcome problems like this? The same way I think to address any problem in Excel - write a bit of VBA to do it!
I wrote a routine to generate the in-cell formulae for each runner in each and every race. This routine wrote the formulae that computed the total % book in the dutch, the stake for each runner and the profit on each runner should
they win. It also wrote out a formula that would calculate the profit for each race once the runners to be dutched were selected by means of a Y in column H.
I wrote another routine to calculate a few rolling averages too but more on those later.
The Starting Point
As I was testing this for my own use I wanted to make the starting conditions as representative of what I would do as possible. So I set up the following:
Stake per race = £20
Minimum stake = £2
My plan had been to set aside a bank of £200 and use Bet-IE to dutch the selections so I had this £2 minimum stake in place. I figured starting with £20 per race might be a tad risky as it was 10% of the starting bank but as this was only paper-trading I figured I see it through and see what came out at the end of it all.
The Systems
I set out with a few fixed ideas of what I wanted to test and this quickly grew to be a reasonably long list with me testing over 500 different systems in the end!
OK, technically speaking it was only really 9 categories of system each with a few tweaks and twiddles in the form of selections filters applied but it all amounts to 544 different systems being tested.
The following briefly describes the categories of systems tested. The number in brackets after the system category identifies the number of individual systems in that category.
Top Rated (80)
This was where I started really. These systems simply involve adding in runners starting at the top of the ratings and working down the list. The top 1 to 10 runners were included in the dutch.
Bottom Rated (80)
BigC had noticed that some big winners had come from the bottom of his ratings so I set about testing whether it was worth following. Here runners were added in starting from the foot of the ratings in each race and working upwards.
The bottom rated 1 to 10 runners were dutched.
Mid-Up Rated (80)
At various points during the early testing of these systems I was in quite regular contact with BigC and during one of those discussions he suggested I could test adding runners in from the middle of the list rather than the top or bottom. While I was happy enough to do this it proved very tricky. My idea had been to add an equal number of runners in above and below the midpoint of the list but the way I had designed my code meant this was getting tricky so I opted for a slight variation on the theme. Mid-up Rated systems involved me starting at the middle of list and working towards the top of the ratings, adding runners into the dutch as I went. Systems involving 1 to 10 runners were tested.
Mid-Down Rated (80)
It should come as no surprise to you to learn that for these systems I started at the middle of the list and selected runners for the dutch by working towards the bottom. Again 1 to 10 runners in each race.
Min Rated (72)
Another simple idea this one, dutch all runners whose rating is at least X where X equalled 150 then 180 to 250 in steps of 10.
Max Rated (104)
Unsurprisingly given the previous category, these systems dutched all runners whose rating was no greater than X where X went from 50 to 170 in steps of 10.
Just Enough (16)
These systems were (I think) how BigC originally got started with the dutching idea on his ratings. The premise is to dutch as much of the field as possible yet still manage a profit should any of the horses win. Basically dutch as many as you can without exceeding a 100% book. Why did I call this bunch 'enough'? Dunno really. I had it in my head that it was enough runners to still be sure of a profit but the more I think about it the lamer that category title sounds. But I'm stuck with it now.
Average Rating (16)
The first category to use one of the rolling averages I said I wrote a function to compute. What I did was calculate the rolling average of the rating of the race winners and use that as a minimum rating, dutching all runners rated above that point.
I have just this minute realised that I made a slight balls-up here in that I was checking the rating of each runner against the average winner's rating including the winner of the race in question. But this should be negligible really and can be ignored, especially when you see the figures!
Percentage of Runners (16)
Another slightly weird idea this one but one of the first I came up with, funnily enough. This uses the other rolling averages calculated, as I said earlier, by the VBA. The premise here is to work out the average position of
the race winner in the list of ratings, expressed as a percentage of the field. So if the top rated is the winner in a 10-runner race it will be 10% of the way down the field, 3rd rated in a 14 runner race is 21.4% down the list etc.
All will hopefully become a bit clearer later.
Exit Conditions & Selection Filters
For each system I would take each race in turn, starting at the appropriate place in the ratings list for that race (which depends on the category of system I am testing) and then enter a Y next to each horse that meets the system rules and is therefore part of the dutched bet.
There a few obvious exit conditions to each loop going through adding horses to the dutch. These include:
1) The horse is a non-runner. Not much point adding these as they have no SP so don't have a % book calculated
2) We've hit the end of the field. In an 8-runner race you can't dutch the top 10 runners, it's simply not possible.
For such a race dutching the top 8, 9 or 10 would give the same result i.e. all 8 runners would be selected. In all races where less than the number of runners specified met the selection criteria as many as possible were added to the dutch even if this fell short of the target number.
The system categories above are simply the basic systems. Each category had a series of selection filters that could be applied to form a new set of selection criteria, and thus a new system, to see how this affected the profits compared to the basic system.
The selection filters, along with which basic systems they were applied to, are described below.
Exc Races
(all systems bar Just Enough)
My lazy shorthand at work here I'm afraid. If the dutch would result in a guaranteed loss then the race was scrapped and no dutched bet was placed on that race. There didn't seem a lot of point dutching runners if we guaranteed ourselves a loss no matter who won the race so this was one of my first filters.
Exc Runners
(all systems)
Sorry, more lazy shorthand going on here. What this actually means is that only selections whose stake in the bet was greater than the defined minimum (£2) were included. Obviously taking one selection out adjusts the stakes for
all the others so runners below minimum stake were removed in reverse order to which they were added until all runners were staked to at least £2.
Allow Singles
(Min Rating, Max Rating, Average Rating, Percentage of Runners)
This was supposed to be a dutching test but I noticed often that only 1 selection was being made per race, and not just for systems where the criteria specified I had to have only 1 selection! So I wondered what would happen if I got rid of these bets. A strange part of my brain was happy to put £20 on a race spread across several runners but it seemed a lot of have running on a single nag.
Count NRs
(Top Rated, Bottom Rated, Mid-Up Rated, Mid-Down Rated)
Suppose you have a 14-runner race and you're supposed to be dutching the top 5 rated. That's easy enough isn't it?
Now suppose the third-rated horse is a non-runner. Do you dutch the remaining four (1st, 2nd, 4th and 5th rated) or do you dutch the top 5 of the remaining horses who actually started the race and so include the 6th rated? If this filter is set to N then that's exactly what you'd do. You would skip over NRs as if they didn't exist and keep selecting horses to add to the dutch until you reached the specified number of horses. Were this filter set to Y then in the example above you would dutch only four horses.
Exit Loop
(Just Enough)
This one was a specific filter for the Just Enough basic system. When adding runners to the dutch I would monitor the potential profit from the race and when it slipped below zero I would uninclude the last selection. However, it was often possible that horses further down the list could be added to the dutch without turning our potential profit into a guaranteed loss - it all depends on the odds. What this oddly-named filter did was continue to add in horses further down the list where possible when the filter was set to N but when set to Y this filter would mean the loop was exited and no futher runners would be considered for inclusion.
Minimum Profit
(Just Enough)
Another filter specific to the Just Enough category and for a change one with a sensible name. I set the minimum profit level to 10% of the race stake (so £2) and like the Exc Races filter if I couldn't be sure of at least that level of profit if the bet came in then I wasn't gambling on this race at all.
Greater Than
(Average Rating)
This was inspired by something BigC put in his C-Max thread recently, about how a runner being greater than a certain rating rather than being greater than or equal to that rating can make a big difference to the figures. And so this filter was born. When it's set to Y horses are only included when they are greater than the average rating, else just being equal to it is good enough to warrant selection.
Runners or Field
(Percentage of Runners)
Strictly speaking this is just an extension of the Count NRs filter. Think back to the Percentage of Runners category of systems. I said I copmputed a rolling average of the winning position in the rating list expressed as a percentage of the runners in that race. I actually computed two averages - one based on the position expressed as a percentage of the actual runners in the race and another based on the position expressed as a percentage of the declared runners, i.e. counting the NRs as well.
The above filters were often combined in several ways in order to create further sets of selection criteria and thus more systems. These 8 filters along with the 9 basic systems and the ability to specify where to start in the ratings list (top, bottom etc) were all that was needed to create the 500+ systems tested in this study.
The results of this study may be found here... (http://www.win2win.co.uk/forum/showthread.php?p=358675)