We all know that if the price of one horse drifts, another must shorten to balance the book.
On the attached you’ll see one solution to calculate a price for a particular horse based on the movement of another. In this case I am looking to calculate the price of the second favourite, based on the price movements of the favourite.
Column A holds the price of the favourite.
Column B holds that price expressed as a percentage (rounded to 2 figures) and the formula would be: ROUND(100/A6,2)
Column C shows whether the horse has drifted ( in this case shown by a positive number, and of course negative if the price is coming in).
For example. On row five you can see that it is currently at 3.20 (or 31.25%). Next row down it has drifted to 3.25, which is 30.77%, so it has moved by 0.48%
This is shown by the simple calculation in C6 of: =B5-B6
Column E (Second Favourite)
Looking across to E6, we can see that the horses price is currently at 3.0
We need to calculate what the new price should be, given the movement of the first horse.
Column F
In F6 the calculation is:
=IF(C6>0,ROUND(100/E6+C6,2),IF(C6<0,ROUND(100/E6-C6,2),""))
Which
1 .Checks to see if the first horse has drifted (C6>0), or shortened (C6<0)
2. Get the current % figure of 3, which is 100/E6, which gives 33.33
3. If the fav has drifted, add the amount of drift onto 33.33
4 If the fav has shortened, minus the amount of drift
Ie, the fav has drifted by .48 so add that onto 33.33 which gives 33.81
Column G
So the target figure is 33.81, and we need to find out the equivalent price:
=IF(F6<>"",VLOOKUP(F6,Percentages!A:B,2,1),"")
Which gives us a target of 2.96
(You’ll notice that this is a True lookup, which finds the closest match)
You’ll notice for example, G6 is suggesting that the price should be 2.96 (currently at 3’s) a little while later you can see that that the price does indeed reach it (E29)
G55 is suggesting that it should be 2.76 (current price 2.84). In E61 the price reaches 2.72
Of course, this is a simplified version which only takes into account the first two horses and it would have to be expanded to take into account as many horses as you wished.
Anyway, hope you find it gives food for thought, but please bear in mind that I cannot be held responsible for any losses that you may incur, as this is intended for educational purposes only.
NigelK
Price Movement Calculation For Bots
Hi NigelK,
This is a very interesting alternative approach to predicting odds movement/change and possibly to an automated method for predicting the number of ticks the odds may move. Weight of money triggers have several limitations.
Has anyone tested/developed the idea any further? Your example records all the movements line by line, presumably for clarity. Is there a method in Excel which would avoid the need for this?
As always, Nigel, a great piece of work.
This is a very interesting alternative approach to predicting odds movement/change and possibly to an automated method for predicting the number of ticks the odds may move. Weight of money triggers have several limitations.
Has anyone tested/developed the idea any further? Your example records all the movements line by line, presumably for clarity. Is there a method in Excel which would avoid the need for this?
As always, Nigel, a great piece of work.