Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board

Go Back   Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board > Thoroughbred Horse Racing Discussion > General Handicapping Discussion


Reply
 
Thread Tools Rate Thread
Old 02-18-2019, 02:57 PM   #1
mikesal57
Veteran
 
mikesal57's Avatar
 
Join Date: Sep 2003
Location: NEW YORK CITY
Posts: 3,670
Excel's Solver

Does anyone have experience with Solver?


EX:

Say I have 3 columns of raw numbers and the last is were the horse won or lost...

How would you config solver ?

Thxs


Mike
mikesal57 is offline   Reply With Quote Reply
Old 02-18-2019, 08:20 PM   #2
098poi
Registered User
 
Join Date: Jul 2006
Posts: 5,594
I never knew about this. Interesting.


098poi is offline   Reply With Quote Reply
Old 02-18-2019, 09:19 PM   #3
mikesal57
Veteran
 
mikesal57's Avatar
 
Join Date: Sep 2003
Location: NEW YORK CITY
Posts: 3,670
Quote:
Originally Posted by 098poi View Post
I never knew about this. Interesting.


https://www.youtube.com/watch?v=K4QkLA3sT1o
How would you use it?
mikesal57 is offline   Reply With Quote Reply
Old 02-18-2019, 11:14 PM   #4
098poi
Registered User
 
Join Date: Jul 2006
Posts: 5,594
I am not sure but if you had some formula related to making a speed or pace rating and were looking for a certain result it would tell you what one of the variables needed to be. You need to have a formula, it won't just do something with raw data. I am a basic Excel user and I find this interesting that's all. I can't help much beyond that.
098poi is offline   Reply With Quote Reply
Old 02-19-2019, 08:49 PM   #5
JerryBoyle
Veteran
 
Join Date: Feb 2018
Posts: 845
I don't use Excel, but I do use linear & non linear programming solvers in python. One task that jumps to mind is solving the proper kelly bet sizes (or bet sizes which maximize expected profit) considering $ bet on each combination, track take, and your estimates for the combinations. I'm guessing you could do the same using Excel's solver. Obviously, this requires that you want to use kelly and that you have an odds line.
JerryBoyle is offline   Reply With Quote Reply
Old 02-21-2019, 05:34 PM   #6
MJC922
Registered User
 
Join Date: Nov 2012
Posts: 1,506
I've used it extensively actually but at least the way I use it the data must be prepped in a certain way. For example ranking is key for what I do, and so you need formulas defined in excel to generate the ranking on a per race basis, it will then change dynamically during the processing as solver optimizes the weights. Not all that complicated to set it up if you have experience with VBA however it's kind of out of reach for people who are just taking a look at this from afar.
__________________
North American Class Rankings
MJC922 is offline   Reply With Quote Reply
Old 02-21-2019, 06:25 PM   #7
mikesal57
Veteran
 
mikesal57's Avatar
 
Join Date: Sep 2003
Location: NEW YORK CITY
Posts: 3,670
Your right MJC......

It needs to be "prepped" ....

its not a plug in and results happen....

you need to run the data thru "R" and then fill in the templete

This is from CX Wong's book "Precision"

The whole idea is to get "probable odds" from the data....not was what I thought it was.

Mike

Attached Images
File Type: jpg Capture.JPG (93.1 KB, 46 views)
mikesal57 is offline   Reply With Quote Reply
Old 02-22-2019, 01:22 AM   #8
JJMartin
Registered User
 
JJMartin's Avatar
 
Join Date: Jun 2011
Posts: 586
Quote:
Originally Posted by MJC922 View Post
I've used it extensively actually but at least the way I use it the data must be prepped in a certain way. For example ranking is key for what I do, and so you need formulas defined in excel to generate the ranking on a per race basis, it will then change dynamically during the processing as solver optimizes the weights. Not all that complicated to set it up if you have experience with VBA however it's kind of out of reach for people who are just taking a look at this from afar.

Can you provide an example?
JJMartin is offline   Reply With Quote Reply
Old 02-22-2019, 07:14 PM   #9
MJC922
Registered User
 
Join Date: Nov 2012
Posts: 1,506
Quote:
Originally Posted by JJMartin View Post
Can you provide an example?
Not easily without posting a file a lot of which has some stuff I don't want seen, doing a youtube video would be best but I don't have time for that. Anyway I can talk through it a bit.

In row one starting in column B I have headers for various factors going across the top. In A2 I have Ranked 1, A3 I have Ranked 2, and so on Ranked 3 then Ranked FH and Ranked RH, then Empty / Unknown in A7.

Then in A8 I have the row headers starting for the dataset itself. So A8 is RaceID and B8 is RankFactor1, C8 is RankFactor2 and so on.

Then in A9 begins the dataset so it's some specific horse in a race, and the ranks are shown from B9 through I9. J8 through Q8 are all headers Score1, Score2, the next row down are all point values, these cells have formula in them along the lines of IF(B9=1,B2

What that means is essentially if that horse in that race had a ranking of 1 for that factor then populate this cell with whatever point value is in B2.

R9 has total points for all of the factors defined so it's a sum of the horse's scores on the factors to the left of it. If you had more factors of course this would be positioned more to the right and the entire dataset would begin lower etc it's just how I set it up for solver.

What solver then does is you're going to set that up to change those point values in the range from B2 to in this case I7 that range is defined in the solver where you specify 'by changing variable cells'. Set them all at 0 to start with and I like to run it set to evolutionary.

You obviously need something to optimize on. The important thing here would be to have a column for finish position (in this sheet I had it in column V) and then I have some column letter S in this case named GroupRank that is populated with formula like =RANK.AVG( i.e. of that first horse's total points(R9) in the group of horses in that race, which was from R9 to R13

What you're looking for here is when a horse has a group rank of 1 and the finish position is also 1 then that returns a $ amount and of course counts as a win.

So the total wins vs bets are easy enough to calculate win% I have a label WPCT in V1 and cell V2 the actual Win%, Win% in this case was defined as my 'set objective' cell to Maximize in Solver. It doesn't have to be Win%, you could optimize on ROI or anything odds related, though I would use caution there, as there's a ton of fitting to noise in payoffs, I would want a massive dataset if I were fitting to ROI. Anyway, you also have to set some constraints on point values so Solver stays within reasonable bounds and then just let it run until it gets happy.

The devil in the details all rests in the GroupRank column formulas, as long as you get past that you can start to play around.
__________________
North American Class Rankings
MJC922 is offline   Reply With Quote Reply
Reply




Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

» Advertisement
Powered by vBadvanced CMPS v3.2.3

All times are GMT -4. The time now is 04:31 AM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Copyright 1999 - 2023 -- PaceAdvantage.Com -- All Rights Reserved
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program
designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.