Fantasy football – linear programming in Excel

Oct. 08, 2012 | by Edward Farragher

The term ‘LP’ can have a number of meanings; ‘Linguistic Profile’, ‘long play record’, or for those who have studied certain disciplines of mathematics; ‘linear programming’.

Linear programming is a method which involves optimising a mathematical function subject to certain constraints, where the function and constrains are all linear.

The Excel Solver Add-in can solve these types of problems and is defined as a:

‘What-if analysis tool that finds the optimal value of a target cell by changing
values in cells used to calculate the target cell’

The Solver Add-in and linear programming have a wide variety of applications including, financial planning, production planning, and also, picking a premier league fantasy football team.  The Solver Add-in is a powerful tool and is not widely used in the field of SEO; so I am writing this post to provide an insight into how it can be applied and show that it is a useful add-in.

In mid-August I was told that the iCrossing Fantasy Premier League was entering it’s fifth season, so I decided that this would be an ideal opportunity to show an application of the Solver Add-in.  The Official Fantasy Premier League which hosts the league has all the football players which are available to be picked, along with the total points they accumulated last season.  To turn this into a linear programming problem, a team can be created which maximises the total points from last season.

Each player costs a certain amount and the rules, or constraints, of the league are:

  • The total cost of the team must not exceed £100m
  • There cannot be more than three players from one team
  • There must be:
    • 2 Goalkeepers
    • 5 Defenders
    • 5 Midfielders
    • 3 Forwards

To form the linear programming model, each player is assigned a binary value, 1 or 0.  A value of 1 indicates the player is to be selected in the team, whereas a value of 0 indicates the player is not.  The Solver add-in will change this value to maximise the total points accumulated last season subject to the given constraints.

The Excel Solver Add-in does have one significant restriction; the maximum number of variable cells is 200.  There are over 500 players in the premier league; therefore I installed an add-in titled ‘OpenSolver’.  This add-in does exactly the same as the Excel Solver Add-in; however the 200 maximum does not apply.  Another useful feature which ‘OpenSolver’ has is that it gives you the option of automatically highlighting the cells included in the model.

Here is a screen clipping of the table in Excel, with the cells highlighted in pink which the add-in will change:

Excel Solver Table

The values in columns F and G will update automatically according to the corresponding value in column H, or whether a player is selected.  The sum of column F will be maximised by changing the values of column H, subject to the sum of column G being less than or equal to 100.  So, for example, if Vermalen was selected to be in the team, the cell H28 would be changed to 1, and consequently, F28 to 132 and G28 to 7 due to the formulas in these cells.

There is another table in the worksheet which considers the other constraints.  Including a cell which sums the total players for each team and has a constraints of being less than or equal to three.  Likewise, a cell for each position (GK, DF, MF and FW) which is subject to the constraint that it must equal the relevant value (2, 5, 5 and 3).

Here is the model which is inputted into the ‘OpenSolver’ add-in:

Model in OpenSolver

  • Objective cell: This cell contains the sum of the points accumulated for the selected team.
  • Variable cells: These are the cells which will be changed where each player has a unique cell assigned to them.
  • Constraints:
    1. The variable cells must be binary, where 1 and 0 represents if a player is selected or not, respectively.
    2. This cell contains the total number of goalkeepers selected and it must equal 2.
    3. This cell contains the total number of defenders selected and it must equal 5.
    4. This cell contains the total number of midfielders selected and it must equal 5.
    5. This cell contains the total number of forwards selected and it must equal 3.
    6. This cell contains the total cost of all the players that are selected in the team and it must be less than or equal to 100.
    7. This range of 20 cells contain the total number of players selected within each team, and each cell must be less than or equal to 3.

Once all the constraints were inputted into the add-in, I clicked ‘Save Model’ then ‘Solve’ OpenSolver - Solve Button, and in a fraction of a second, the optimal fantasy team is picked.  I filtered out the ‘0’s to leave the ‘1’s to see the chosen team, and then submitted it online:

 

Squad in Fantasy Football

The captain and vice captain were assigned as the two players who had received the most and second most points last season respectively.  The players on the bench were determined as they scored the least points in their position in the fantasy team.

The team appeared very strong with the top goal scorer of last season; Van Persie, along other key players of last season’s Premier League.  There were three Swansea players picked which is not surprising due to their strong performance last season with lesser known (and consequently cheaper on fantasy football) players.

The next decision I had to make was to pick a team name; I decided to call them ‘Crying Lightning’, after the Arctic Monkeys song.  I then entered into the iCrossing league.

One transfer is allowed each gameweek, therefore the model was adapted to select which player to transfer to maximise the current seasons total point accumulation.  After the first week, Evra was replaced with Ivanavic.  Evra did not get any points in the first week whereas Ivanvic got 24, making him the top performing player of the week, so the transfer was sensible.

It is now the end of gameweek 6 and ‘Crying Lightning’ are a disappointing 23rd out of 25.  Even after bringing in key players including Ivanovic, Cole and Hazard from Chelsea, they have been unable to climb higher than 17th.

I recently attended the BrightonSEO conference and one key point made was that when performing natural search optimisation, you shouldn’t ‘chase the algorithm’, as you will always be one step behind and only ever close the gap on competitors.  This same theory can be applied here.  With this linear programming method for selecting a fantasy football team, it only considers past data and the team will always be the ‘optimal team last week’. Therefore more accurate models could be produced which consider more factors such as each team’s next opponent and other players influence which are not in the selected team.

I will write a follow up blog at the end of the season to see how the team performs after making the weekly transfers.  I hope this has been interesting and useful in uncovering an area of Excel which you may have been unaware of.  The performance of ‘Crying Lightning’ in the iCrossing league just goes to show that it’s difficult to model football performance on mathematical formulas.  However, I believe this actually just highlights the exceptional standard of fantasy football here at iCrossing.

 

Be Sociable, Share!

    Comments (5)

    • Travis

      This post is exactly what I'm looking for! I'm trying to convert it for use with American Football, but I'm struggling to get things working correctly. Would you mind emailing me the Excel spreadsheet?Sep 12, 2014 09:08 pm

    • Calum

      Hi I just came across this and I have been looking for something like this for the start of the new season, I wondered if you could possibly email me a copy of the spreadsheet as there are parts of the Open Solver I don't quite understand. Thank you!Jul 23, 2013 04:04 pm

    • Phil

      This is a great post! Would you mind emailing me a copy of your spreadsheet?Jul 15, 2013 01:47 am

    • David

      Nice.Oct 10, 2012 12:17 pm

    • Adam Boulton

      Great post, love this. Nice illustrating a slightly inaccessible concept (for non-techies) with a real world example :)Oct 8, 2012 11:28 am

     
    Please note: the opinions expressed in this post represent the views of the individual, not necessarily those of iCrossing.

    Post a comment