View Single Post
  #7  
Old 22nd January 2016, 12:34 PM
walkermac walkermac is offline
Member
 
Join Date: Nov 2013
Posts: 603
Default

Quote:
Originally Posted by beton
I downloaded the opensolver but have no idea what I am doing.
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
Code:
=SUM(AA:AA)
(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.
Reply With Quote