Help
Please
I need some formulas for excel. I have been going through progressively in the attached spreadsheet. I have been trying to learn as I go. But when I try to put the textbook and video tuition in, I am failing.
I did for a good period of time lay the field to a liability and made a good profit until the profit became less and less.
Now I wish to revisit the system. But if I am going to do it, I want to (1) be able to enter the info into another excel data base with tote prices and the on course bookmaker prices.
Betfair is a little sneaky with their data. They put all the horse races together, they join the TAB# with the horse’s name and then shuffle them up. And they keep the win and place markets separate.
I need formulas not VBA because I can understand the formula and I need to be able to transfer the formulas with suitable adaption into the worksheet with the other data.
Attached is a sheet that was derived from Betfair final data files. This was put through another spreadsheet that was VBA. It is the sheet that I used to test the final data before. It removes the harness races and gives a result based on laying the field. The result was columns A-R and X & Y.
I then removed the special events, the blank spaces where unmatched (NT races) and the odd $1.01 outcome.
I then split col G to G & H, sorted numerically and ranked the odds. Then added field size.
Then got stuck.
In the attached spread I have put what equations I am trying to achieve.
Col U I want to be able to put a liability in for each rank up to 24 horses. Thus I can do the calculation with variable liabilities. IE $120 for 1st , $110 for 2nd and the rest at $100. Or $0 for 1st etc. I want to be able to test many combinations until I find some good ones that work. So Col U will be a reference match for each rank. This may need to be a table
Col V was the start of my pain. I need a formula that says “when Col S (ranking) = 1 then match col U $U$4 and when Col S =2 match $U$6 etc to rank 24.
Col W is the betfair lay amount liability/(odds-1)
Col x and Y I want to keep as reference to compare to.
Col Z will be the laying based on the col W
Col AA is the sum of each race of col Z with the result at the end of each race.
Col AB is the odds expressed as a percentage
Col AC is the market%
Col AD is the factor to convert the odds % to suit a 100% market.
Col AE is the odds% in a 100% market
Col AF previously entropy was touted as a metric testing the validity of the pricing of the race. This is only 2 columns from here so AF is col AE x Col AE
Col AG is sum of AF per race placed at the last horse of the race.
Thanks in advance.
I have explained my procedure so if anybody else wanted to follow they could from the betfeiar final data. They would have to manually remove the trots. And copy the columns as values from col Z and AA.

Attached Files

formula.xlsx (406.8 KB, 1088 views)

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif

This won't help with your question, but also remember that quite often Betfair data is flawed in that in many cases, late scratchings and even scratchings are often still included in the data. So you need to cross reference with TAB results or some other way.

__________________
RaceCensus - powerful system testing software.
Now with over 399,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/03/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

CP ultimately I have to match up with other data. This will sort out the scratchings. It may flawed but it will still show a trend.

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif

I'm still around and had a go based on the descriptions of what you wanted (laying otherwise makes very little sense to me).

Brief explanation:

U3:U26 - liability for ranks 1 down to 24
Fill Down columns V & W
Fill Down columns Z through AI

If you're testing combinations of liabilities, use something like this: http://opensolver.org/. It can find the combos that maximise your return/minimise your loss.

No guarantee that it's predictive though. It might just describe the past, rather than forecast the future.

Attached Files

attachment.xlsx (435.7 KB, 1072 views)

Thank you Walkermac.
They are better answers than I came up with. I freed up some time and searched for the answers. I had to add extra columns because I could not get multiple equations to work. I knew that there were far easier answers.
Everytime I have started to proceed with excel tuition , I get snowed under elsewhere.
I will download opensolver and see if I can get past the first screen.

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif

Walkermac
I know that I can safely say this here because nobody is going to go to the trouble of downloading and sorting the BF data. I have postponed it now for 3 years.
I used to lay the field with a slight variation for a set liability. This simple act each day earned about $26K per year then it started to wane and I saw that
more than two thirds of the money was in the first six months. So I stopped. Which was fortunate because what was a winner turned into a loser. I had a couple of ideas but no way to test them. Then I got busy elsewhere and the punting has taken a back seat.

The sheet allows me to test the ideas that I had. I have only tested one month
but by using a variable liability it changed a losing month into a winning month. Mark had alluded to this in previous posts. IE he loaded the pointy end.
I have to work out to efficiently bot variable liability.

I downloaded the opensolver but have no idea what I am doing.

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif

I'm going to presume that opensolver is downloaded and installed. That is, when you click on the Data tab in Excel you now have the additional bit - highlighted in blue - in this image:
)

Now we're going to make one small addition to the spreadsheet you posted. In cell U2 enter (i.e. your total profit/loss)

Next we're going to describe our model. Click on the Model button in the OpenSolver section and select "Model..." from the submenu.

From the resultant window we firstly specify the Objective Cell, i.e. the cell that we want our model to observe. We specify it as U2. We then need to tell the model how to adjudge changes to this objective cell (i.e. tell it when one solution is better than another). Select the maximise radio button (we want the optimum parameters to maximise our profit/minimise our loss)

Next up we specify the Variable Cells, i.e. the cells whose values our Model can change to examine the effect on our Objective Cell. Enter U3:U26

Lastly we have to limit the possible values our variable cells can hold. We obviously can't have negative values and we also want to specify an upper limit (e.g. $120). The Constraints listbox on the left displays the existing limits on the parameters in the model - naturally it's empty to begin with. To add a constraint, look over to the right and you'll see a bunch of 3 input boxes and an "Add constraint" button under it. In the top-left cell enter U3:U26. In the box to its right, select the "<=" operator. In the remaining box enter 120. Lastly click Add constraint. You'll see it has now been added to the Constraints on the parameters in our model.

Do the same as above, but this time adding a constraint that the parameters are ">=" 0.

Then down the bottom click on Save Model.

This bit's not necessary, but gives you reassurance that it's actually doing something when analysing large amounts of data: Click on the Model button in the OpenSolver section and select "Options..." from the submenu. Check the "Show optimisation progress while solving" checkbox.

To run the Model, click on the "Solve" button in the OpenSolver region of the toolbar.

The sample you uploaded is small and it's a pretty simple calculation it's doing, so it's over quick. The optimum result I got was a profit of $2719.67.

And now for the warning....

Past results are no indication of future results. The combination of parameters are retrodictive, i.e. they well describe the past. Whether they are predictive should only be a determination by FURTHER analysis. So you might want to divide your data into several parts and test the solution across them, monitor new races, paper test, etc. I got a profit of $2719.67 in this instance but, looking at the optimum parameters it determined, there is no way that they would consistently work for other data sets.

If you do manage to find your fortune, I'll send you my paypal details and you can buy your way out of feeling guilty for taking advantage of my knowledge


One last note: In the OpenSolver group, click on the Model submenu. From the resultant menu, select "Solver Engine...". The way it determines the optimum values for the parameters is via a mathematical process. (If it were to exhaustively test every possible combination of parameters it would take a loooooong time to calculate). There are methods that can be used to select parameters so that a solution is approached quicker and - as is the case for most mathematics - there's usually several ways of doing everything. Some of those ways might not work for your particular problem, so you might have to change the Model around. Other methods might come up with slightly better parameters, so it's worth experimenting. There are two version of OpenSolver, one for solving linear equations and another for freaky-deaky ones. Could be worth trying your luck with all of them.

Thanks again.
I should have twelve months data to test today.
I am testing 2013 first to be able to forward test 2014 and 2015.

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif

Just finished testing 2013. proves that I was correct in stopping.
ALL IN for $100 liability gives $5200 for the year. It was all won in the first two months with 4 losing months. A simple adjustment to the staking improved this by 235%.

Now it is a question of which bot can allow you to specify different liabilities per rank.

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif