|
|
11-06-2022, 10:32 AM
|
#46
|
Registered User
Join Date: Aug 2022
Posts: 322
|
Quote:
Originally Posted by MJC922
Correct, this is not a template that you can just import your data into. This is only a simple demonstration of how solver can be setup to do some things with handicapping data.
|
Thank you again....
That was a lot of work.....
I believe I can handle the fix now that I have the " how it works"
As for the others , I think we'll lose about 90% of em...
|
|
|
11-10-2022, 12:02 PM
|
#47
|
Registered User
Join Date: Oct 2022
Location: Southern CA and Las Vegas
Posts: 105
|
I used Sqlplus and it works fine for me.
I down DRF Formulator into it and
generate my reports from there.
|
|
|
11-11-2022, 09:17 AM
|
#48
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Quote:
Originally Posted by MJC922
Correct, this is not a template that you can just import your data into. This is only a simple demonstration of how solver can be setup to do some things with handicapping data.
|
I took a quick look at it. I don’t understand it all yet or even how best to apply it, but I think regression is more up my alley. What I want to do is supply X number of factors and the spreadsheet to tell me how to weight each to produce the maximum number of winners, in the money horses etc…. I looked at my old spreadsheets again and I can’t remember exactly what I was doing there either. But I still have the queries I used to download the data and the spreadsheets to test it. So I’ll just have freshen my memory and take it further this time.
__________________
"Unlearning is the highest form of learning"
|
|
|
11-11-2022, 04:15 PM
|
#49
|
Registered User
Join Date: Nov 2012
Posts: 1,542
|
Yea it's probably difficult for most folks to wrap their head around initially but after a while it sinks in and then it's not too bad. In this example it's just four factors that I put into the mix, fr1, 2, 3, then a morning line rank for the fourth one.
Basically if you look at Crypto Mama in row 31, the rank of the horse's FR1 in that specific race was a 2 so then when you go over to Column Q the FR1_Val formula will pull in the value that's in Cell G3 because that's where 2nd rank's value for the first FR is located. If the rank were a 3 it would be looking in the next cell down in G4. When you run solver it's optimizing these yellow shaded values in order to maximize the number of winners in the sample, that is we're maximizing the value in cell T2. Find the weights using evolutionary that will give me the most winners in this sample with a positive ROI. That's what we're asking of solver. You could maximize on U2 instead (WPCT) or V2 (ROI), etc, there are some constraints I've set that you may want to change as far as that goes, I think I required at least 200 bets as one of the constraints and a positive ROI as another constraint. Column U is the average of the four factors for each horse. The horse with the highest average within each race then gets a ranking of 1 and becomes a 'bet'.
Last edited by MJC922; 11-11-2022 at 04:24 PM.
|
|
|
11-14-2022, 12:56 PM
|
#50
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Here's one of many things I don't understand yet.
In the yellow shaded area, I can see that when you enter values in those columns the sheet goes through calculations and isolates certain horses to check the win% and ROI. But I don't understand what should be entered. It looks like I can enter any number in any given cell and it produces the same result. Is that just like an on/off switch?
__________________
"Unlearning is the highest form of learning"
Last edited by classhandicapper; 11-14-2022 at 12:58 PM.
|
|
|
11-14-2022, 02:23 PM
|
#51
|
Registered User
Join Date: Nov 2012
Posts: 1,542
|
No worries, that's fine. Just set them all back to zero manually. Then keep in mind you have to have the Solver add-in installed. That's included with Excel but needs to be enabled first. Once you have it enabled go to the Data tab > Analysis, click on Solver. That should open up the Solver Parameters dialog box. This will I hope still have retained the original parameters that I applied to this excel sheet. Here is where you should see the objective to maximize T2 by Changing the Variable Cell range of G2:J6, those are the yellow shaded cells you were manually playing around with. Click the Solve button at the bottom and those will start changing automatically. Another possibility is you may need to re-download the sheet and retry the above steps.
Last edited by MJC922; 11-14-2022 at 02:26 PM.
|
|
|
11-14-2022, 02:31 PM
|
#52
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Quote:
Originally Posted by MJC922
No worries, that's fine. Just set them all back to zero manually. Then keep in mind you have to have the Solver add-in installed. That's included with Excel but needs to be enabled first. Once you have it enabled go to the Data tab > Analysis, click on Solver. That should open up the Solver Parameters dialog box. This will I hope still have retained the original parameters that I applied to this excel sheet. Here is where you should see the objective to maximize T2 by Changing the Variable Cell range of G2:J6, those are the yellow shaded cells you were manually playing around with. Click the Solve button at the bottom and those will start changing automatically.
|
I must have solver installed already because as I change the values in those cells, the ROI and Win% changes and the spreadsheet reacts in a way that makes sense.
But I don't know exactly what I am asking when I change those numbers.
Let's say I put a "1" in cell G2, what am I asking?
If I put a 99 in there I get the same result.
What if I put a "1" in G2 and H2?
What if I put a "1" in G2 and G3?
__________________
"Unlearning is the highest form of learning"
|
|
|
11-14-2022, 03:52 PM
|
#53
|
Registered User
Join Date: Nov 2012
Posts: 1,542
|
Quote:
Originally Posted by classhandicapper
I must have solver installed already because as I change the values in those cells, the ROI and Win% changes and the spreadsheet reacts in a way that makes sense.
But I don't know exactly what I am asking when I change those numbers.
Let's say I put a "1" in cell G2, what am I asking?
If I put a 99 in there I get the same result.
What if I put a "1" in G2 and H2?
What if I put a "1" in G2 and G3?
|
I suspect you don't have it installed. The sheet would react that way whether you have the Solver add-in installed or not. If you go to the File tab > Options > Add-ins you should see 'Active Application Add-ins'. If Solver isn't there go to Manage Excel Add-Ins and place a check mark next to Solver Add-in. Now you'll see it show up as an option on the ribbon at the top (Data tab). The key to the sheet as an example is that the way it is setup is what allows solver to actually help you find the values. If you don't run it then you'd have to sit there manually trying various values for a very long time.
Last edited by MJC922; 11-14-2022 at 03:53 PM.
|
|
|
11-14-2022, 08:19 PM
|
#54
|
Registered User
Join Date: Nov 2012
Posts: 1,542
|
Quote:
Originally Posted by classhandicapper
I must have solver installed already because as I change the values in those cells, the ROI and Win% changes and the spreadsheet reacts in a way that makes sense.
But I don't know exactly what I am asking when I change those numbers.
Let's say I put a "1" in cell G2, what am I asking?
If I put a 99 in there I get the same result.
What if I put a "1" in G2 and H2?
What if I put a "1" in G2 and G3?
|
You aren't really asking anything if you do that, you're telling... If you put a 1 in G2 you're saying I (Classhandicapper) believe a 1.00 should be the point value of the horse with the fastest FR1 in each race. By not changing any other weights you can observe that alone did help your wpct by 3% as it placed a win bet on each of those, they all end up ranked first on the four factor average in column V. Trying it on FR2 instead, notice the same boost to wpct but that characteristic 'only' lost 7% on the sample. However because it lost (any negative roi) solver would then 'try' some other value immediately somewhere because one of the constraints that I've set on this solver run is that we want to see the most winners and yet still achieve a positive ROI on the sample.
Last edited by MJC922; 11-14-2022 at 08:21 PM.
|
|
|
11-14-2022, 11:13 PM
|
#55
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Quote:
Originally Posted by MJC922
I suspect you don't have it installed. The sheet would react that way whether you have the Solver add-in installed or not. If you go to the File tab > Options > Add-ins you should see 'Active Application Add-ins'. If Solver isn't there go to Manage Excel Add-Ins and place a check mark next to Solver Add-in. Now you'll see it show up as an option on the ribbon at the top (Data tab). The key to the sheet as an example is that the way it is setup is what allows solver to actually help you find the values. If you don't run it then you'd have to sit there manually trying various values for a very long time.
|
I did already have the Solver add in. I just forgot how to run it. It has been years. When I ran it, I got a bunch of values in those F2-J6 cells, but it's not 100% clear to me what they mean or how I could convert that information into a workable model using just that data, let alone a bunch more. I'll have to play with it some more. Thanks for the start.
__________________
"Unlearning is the highest form of learning"
|
|
|
11-15-2022, 09:34 AM
|
#56
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Quote:
Originally Posted by classhandicapper
I did already have the Solver add in. I just forgot how to run it. It has been years. When I ran it, I got a bunch of values in those F2-J6 cells, but it's not 100% clear to me what they mean or how I could convert that information into a workable model using just that data, let alone a bunch more. I'll have to play with it some more. Thanks for the start.
|
By the way, I remembered how I used Solver previously.
For racing, I had run some regression testing on several factors. I still have those spreadsheets.
But there was a period I was betting more basketball than I do now. Kevin Pelton gave me a spreadsheet that ran Solver to calculate SRS power ratings for Basketball Reference. I added some factors to it and used to run it everyday to produce season long ratings, last 10 game ratings, last 20 games ratings etc.. I still have that one also. I just have to refresh my memory. Thanks again to everyone.
__________________
"Unlearning is the highest form of learning"
|
|
|
11-17-2022, 01:52 PM
|
#57
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Here's an interesting one.
Suppose I had a spreadsheet with 5 factors calculated inside my database.
1. Class Rating
2. Speed Rating
3. Consistency rating
4. Running Style (0-5 with 0 being a pure speed and 5 being a deep closer)
5. Projected Pace Pressure this Race (0-30 rating with 0 being no speed and 30 being a race loaded with speed)
How would you approach testing how to weight each of those factors so the spreadsheet would understand that as the Projected Pace Pressure changes, the optimal Running Style and it's importance would also change?
Maybe that needs to be done separately?
__________________
"Unlearning is the highest form of learning"
|
|
|
11-17-2022, 06:56 PM
|
#58
|
Registered User
Join Date: Nov 2012
Posts: 1,542
|
Quote:
Originally Posted by classhandicapper
Here's an interesting one.
Suppose I had a spreadsheet with 5 factors calculated inside my database.
1. Class Rating
2. Speed Rating
3. Consistency rating
4. Running Style (0-5 with 0 being a pure speed and 5 being a deep closer)
5. Projected Pace Pressure this Race (0-30 rating with 0 being no speed and 30 being a race loaded with speed)
How would you approach testing how to weight each of those factors so the spreadsheet would understand that as the Projected Pace Pressure changes, the optimal Running Style and it's importance would also change?
Maybe that needs to be done separately?
|
A couple of different ways come to mind. For example with the first three factors of class, speed and consistency you could take the rank approach similar to the first example spreadsheet with a 1-4 rank and 'other' for the rest. Then for the running style and projected pace piece of this I'd probably start by trying to handle it using a pair-up. I'd probably start by looking into 'binning' the projected pace total of 0-30 ... throwing out some aircode here IF(AND(RS =0,PP>=0,PP<=5)),then point to one of the cells solver will change...
IF(AND(RS =0,PP>=6,PP<=11)) then point to the next cell down from that one etc...
so that will be five? bins for each running style 6x5 = 30, it's a lot of combinations but anyway that's one way to do something with it. Maybe with testing you'll find that only the extremes really matter, i.e. maybe larger bins and / or fewer running styles actually matter etc. In theory they all matter I know but you're projecting pace and we all know that's bigtime hit and miss, so what might actually make money on a sample could be the very opposite of what people expect.
Another approach could be for example to find the point value of a pairing. So we have our 30 pairings and we have our speed or our class, how are those scaled? Let's say 'speed' for you is a BSF, we take the BSF and we use solver to adjust that by the solver values of the pairings, that way we optimize on winners based upon BSF but the BSF is being adjusted in another column by the paired value and that adjusted BSF first ranked is what we bet.
So that's at least two ways for you to look at it... and maybe neither are good ways but hopefully I've provided some food for thought.
Last edited by MJC922; 11-17-2022 at 07:09 PM.
|
|
|
11-18-2022, 10:31 AM
|
#59
|
Registered User
Join Date: Oct 2001
Location: near Lone Star Park
Posts: 5,152
|
Quote:
Originally Posted by classhandicapper
Here's an interesting one.
Suppose I had a spreadsheet with 5 factors calculated inside my database.
1. Class Rating
2. Speed Rating
3. Consistency rating
4. Running Style (0-5 with 0 being a pure speed and 5 being a deep closer)
5. Projected Pace Pressure this Race (0-30 rating with 0 being no speed and 30 being a race loaded with speed)
How would you approach testing how to weight each of those factors so the spreadsheet would understand that as the Projected Pace Pressure changes, the optimal Running Style and it's importance would also change?
Maybe that needs to be done separately?
|
Just keep in mind that as horses drop in class, their other ratings are sometimes somewhat irrelevant.
__________________
Ranch West
Equine Performance Analyst, Quick Grid Software
|
|
|
12-01-2022, 04:33 PM
|
#60
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,610
|
Here's an issue I am running into running a regression.
I have 3 factors and the finish position. I'm running a regression trying to get the correct weighting of each factor to produce the most winners.
What I think the regression is trying to do is weight the 3 factors to get as close to matching the entire order of finish instead of just trying to maximize the winners.
I don't really care about being more accurate about who is going to finish 6th, 7th, 8th etc.... I just want to maximize winners.
Is there anything that can be done to trick it in some way. I was thinking of keeping all the winners as a 1 and everyone else as a 0 or 99.
(Also, since each field has a different number of horses that could also be confusing it)
__________________
"Unlearning is the highest form of learning"
Last edited by classhandicapper; 12-01-2022 at 04:37 PM.
|
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|