Message Boards Message Boards

0
|
6700 Views
|
3 Replies
|
0 Total Likes
View groups...
Share
Share this post:

Convert this excel sheet to mathematica for fantasy football?

Posted 9 years ago

I've made an excel sheet (2007 and above with solver add in) that uses some type of an optimization to pick the players in daily fantasy sports football.

Just in case you're unfamiliar with daily fantasy sports, it's a sports betting where you choose various players and depending on the numbers they post you win or lose. The player are assigned salaries in dollars and points (ffpg), and you have a total of $45000 to buy the players to elicit the most possible points. And players are given points for scoring, running, receiving etc.

So it's an optimization problem. You want to maximize the number of points a group of players can achieve.

Rules are you get $45000, 1 Quarter back B, 2 Running Backs. 3 Wide Receivers, and 1 Tight end for total of 7 players. You can't have more than 4 players from one team.

Is there a way to convert this excel sheet to mathematica? I ask because the solver routine in excel is really weak and not giving too many solutions.

And use the optimization tools available in mathematica? What are the steps involved here?

Attachments:
POSTED BY: bored dude
3 Replies
Posted 9 years ago

Ok maybe I ought to just ask this way. There are following lists (columns) that form the array (n x m) shown in that excel sheet. (The length n of this array varies every week as the number of games and players chosen differ)

  1. Play list which varies from 0 or 1.This lists determines which salaries and points gets added up. (left most column 1) and satisfies the requirement of only 7 players playing. Basically choose only 7 entries to have the value of 1 and rest the value of 0.

  2. Player list. names of the players.(column 2, GregWardjr being the first entry)

  3. Salary list, which lists how much you have spend in order to play the particular player. This is why there is a USD 45000 cap on salary total. (column 3)

  4. FFPG points. This is the number of points players average per game and determined by various factors, passing, rushing, TDs, etc.(column 4)

  5. Position list, It has 4 types of entries, QB, WR, RB, TE. The team is allowed 1 QB, 2 WRs, 2RB and 1 TE. (Column 5). This list combined with the play list in column 1 satisfy that rule.

  6. Then the array of 4 x m dimension that contain 1 or nothing (or 0) This matrix is needed in order to solve the constraint of having to only choose 1 QB, 3 WRs, 2RBs, and 1 TE. (columns 6-9)

Then the function that I need to maximize is

the sum of [1QB's ffpg points + 3 WR's ffpg points + 2 RB's ffpg points + 1TE's ffpg points]

While being constrained on the total amount of salaries for the player which usd45000.

In Excel it's pretty easy to implement this by using solver add in which only involves making those columns and feeding the constraints into the solver.

In Mathematica, the job of the solver has to be coded in. For instance, I need a way to vary the first column from 1 to 1 only 7 at a time with 1 entry of 1 going to 1 QB, 3 entries of 1s going to 3 WRs, 2 entries of 1s going to 2 RBs, and 1 entry of 1 gong to 1 TE.

Then list of 1s and 0s, needs to be dot product with the list of the player's salary and then the ffpg points.

so let's consider the following example. Let's say you have 2 QBs, 6 WRs, 4RBs, and 2TEs. (Total number of players are in usually hundreds as you can imagine with multiple games a day, but for clarity sake let's keep it simple.)

Table[{qb[i], wr[i], wr[j], wr[k], rb[i], rb[j], te[i]}, {i, 2}, {j, 1, 4}, {k, 1, 6}] // Outer[List, #] & // Flatten[#, 3] &; % // Length

will yield 336 unique combinations for which the sums have to be performed on both the salaries under usd 45K constraint, (for which if it fails, the sum of the ffpg points doesn't need to be performed) and the ffpg points in order to maximize the sum of ffpg.

So the Mathematica's Maximize function has to run on the sum of the ffpg points which will be constrained by the salary cap of the team of 1 players.

Excel has limitations and it's only giving me the same set of players and it's limited to 200 total entries for the solver add in, which means i can only choose from 200 players.

Within Mathematica I can Seed it differently to get a many different combinations as local maxima and this is valuable information in playing fantasy sports.

I hope that clears it up a bit. I don't have the newsest Mathematica, so that function doesn't work.

edit. formatting apparently this forum treats the dollar sign as something special.

POSTED BY: bored dude

Hi,

I don't fully understand your question, but you can use:

SemanticImport["C:\ .......\...\thu fri cfb solver.xlsx enter image description here

POSTED BY: Jos Klaps

I just imported your sheet into Mathematica by drag-and-drop and it looks fine. If that doesn't work for you, try Import[pathOfSheetFile]. As for optimization I am no expert.

POSTED BY: Gianluca Gorni
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract