Optimizing a Fantasy Team in Excel

[wolfpacshakur]

Hi everyone - it's been a long time since I posted here... so long that I needed to make a new account! I am looking for some help/suggestions on how to solve this problem. I want to create an optimization for my NFL Fantasy Draft. To start this is what I would like to figure out. I have the following positions: QB, WR, RB, TE, K, and FLEX (which can be any combination of WR, TE of RB). Each player has an average draft price and a projected season fantasy point total. I want to maximize the fantasy points while keeping the dollar total under a fixed amount (i.e. 200 dollars). So I have lists for each position type of players and cannot have any duplicates (i.e. I can only draft each player once). Interested to hear how people suggest tackling this problem. I am having major issues so any help would be greatly recommended!!

 

[mikerickson]

If you had only two positions QB and WR, it would be fairly easy to list all the possible choices and their cost and point values for each of those pairs of choices.

That could be considered one position (the QB+WR position), then you could repeat that process matching QB+WR "players" with TE players

And then combine the QB+WR+TE with K, etc etc.

This is recursive and straightforward to impliment, the problem is that the number of possible combinations grows super fast.

 

[wolfpacshakur]

I totally understand - it will be taxing to run the iterations to solve this problem. I am thinking something like this would work (linear programming): Using Python and Linear Programming to Optimize Fantasy Football Picks

I have no idea how to set this up. Would you mind looking at my workbook?

 

[Eric W]

About 5 years ago I wrote a macro that does almost the same thing. It has DEF instead of K (defense, kicker), but you should be able to just put your kickers in the DEF column. It'll basically give you all the possible combinations as well as give you the payroll cost. If you then sort the list by payroll, you should get the "best" combinations to choose from.

I don't have time right now to update it, but the macro is here:

Listing all possible combinations with a few constraints

Hi all- My question relates to fantasy football. In excel i have 5 columns (QB, RB, WR, TE, DEF). Each column will have 3-5 individual players. I want to create a list with every possible combination...Except there are a few wrinkles.. #1 there needs to be 2 RB, #2 there needs to be 3 WR, #3...

www.mrexcel.com

 

[wolfpacshakur]

Thanks @Eric W appreciate the help. I am going to give this a try. In the interim though - I see you are indicating that salary is an indicator of value. This assumption to me is not correct and that is the reason I also have projections... If I want to set it up so that it gives me the maximum total fantasy points based on my projections under a given salary cap how would I do that?

 

[lrobbo314]

Here is a link to a YT Video showing a way using the 'solver' add-in. I remember it being a useful tool, except for the fact that it never did help me win any money. Hope you have better luck than I did.

 

[Eric W]

True enough, some players are over/underpaid for their production. The simplest way to incorporate the fantasy points would be to put them in the C/F/I/L/O columns on the input sheet, then put the total on column L of the output sheet, then sort by column L. Then you'd get a list of the highest total points combinations, that are under your budget. Shouldn't be too hard to do, I'll see if I can get to it in the next day or two.

 

[Eric W]

Man, that macro desperately needs a massive rewrite! I've learned a lot in the last few years that would make that much more efficient. But for now, I just added the points columns. Starting here:

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	QB	1	pts	RB	2	pts	WR	3	pts	TE	1	pts	DEF	1	pts		Budget
2	QB1	100	10	RB1	50	10	WR1	110	11	TE1	60	10	DEF1	100	5		650
3	QB2	150	9	RB2	75	10	WR2	75	8	TE2	70	9	DEF2	130	5
4	QB3	125	7	RB3	100	9	WR3	25	6	TE3	80	9	DEF3	93	6
5				RB4	99	8	WR4	85	6	TE4	50	6
6							WR5	47	5
Sheet1

Paste this code to the VBA editor:

Sub Fantasy()
Dim Options(5, 9, 3), Draft As Worksheet, Teams As Worksheet
Dim RBList(100), WRList(100), TEList(100)
Dim RBMin As Byte, WRMin As Byte, TEMin As Byte
Dim Budget As Double

    Application.ScreenUpdating = False
    
    Set Draft = Sheets("Sheet1")
    Set Teams = Sheets("Sheet2")
    Teams.Cells.ClearContents
    Teams.Cells(1, 1) = "QB"
    Teams.Cells(1, 2) = "RB"
    Teams.Cells(1, 3) = "RB"
    Teams.Cells(1, 4) = "WR"
    Teams.Cells(1, 5) = "WR"
    Teams.Cells(1, 6) = "WR"
    Teams.Cells(1, 7) = "TE"
    Teams.Cells(1, 8) = "DEF"
    Teams.Cells(1, 9) = "FLEX"
    Teams.Cells(1, 11) = "Payroll"
    Teams.Cells(1, 12) = "Points"
    
    
    Options(1, 0, 1) = Draft.Columns(1).Find("").Row - 2
    Options(2, 0, 1) = Draft.Columns(4).Find("").Row - 2
    Options(3, 0, 1) = Draft.Columns(7).Find("").Row - 2
    Options(4, 0, 1) = Draft.Columns(10).Find("").Row - 2
    Options(5, 0, 1) = Draft.Columns(13).Find("").Row - 2
    For r = 2 To 10
        Options(1, r - 1, 1) = Draft.Cells(r, 1)
        Options(1, r - 1, 2) = Draft.Cells(r, 2)
        Options(1, r - 1, 3) = Draft.Cells(r, 3)
        Options(2, r - 1, 1) = Draft.Cells(r, 4)
        Options(2, r - 1, 2) = Draft.Cells(r, 5)
        Options(2, r - 1, 3) = Draft.Cells(r, 6)
        Options(3, r - 1, 1) = Draft.Cells(r, 7)
        Options(3, r - 1, 2) = Draft.Cells(r, 8)
        Options(3, r - 1, 3) = Draft.Cells(r, 9)
        Options(4, r - 1, 1) = Draft.Cells(r, 10)
        Options(4, r - 1, 2) = Draft.Cells(r, 11)
        Options(4, r - 1, 3) = Draft.Cells(r, 12)
        Options(5, r - 1, 1) = Draft.Cells(r, 13)
        Options(5, r - 1, 2) = Draft.Cells(r, 14)
        Options(5, r - 1, 3) = Draft.Cells(r, 15)
    Next r
    
    RBMin = Draft.Cells(1, "E")
    WRMin = Draft.Cells(1, "H")
    TEMin = Draft.Cells(1, "K")
    Budget = Draft.Cells(2, "Q")
    
' Case 1 - The Flex player is RB
    Erase RBList, WRList, TEList
    Call Combos(RBMin + 1, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin, Left("123456789", Options(4, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 1, Teams)
    
' Case 2 - The Flex player is WR
    Erase RBList, WRList, TEList
    Call Combos(RBMin, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin + 1, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin, Left("123456789", Options(4, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 2, Teams)
    
' Case 3 - The Flex player is TE
    Erase RBList, WRList, TEList
    Call Combos(RBMin, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin + 1, Left("123456789", Options(4, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 3, Teams)

' Sort

    Teams.Sort.SortFields.Clear
    Teams.Sort.SortFields.Add2 Key:=Range("L2"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Teams.Sort
        .SetRange Range("A:L")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.ScreenUpdating = True

End Sub
Public Sub CheckAndDisplay(Options, Budget, RBList, WRList, TEList, MyCase, Teams)
Dim ResLine(1, 12), Payroll As Double, Pts As Double
Dim QB As Byte, RB As Byte, WR As Byte, TE As Byte, DF As Byte


    For QB = 1 To Options(1, 0, 1)
        For DF = 1 To Options(5, 0, 1)
            For RB = 1 To RBList(0)
                For WR = 1 To WRList(0)
                    For TE = 1 To TEList(0)
                        
                        'QB
                        ResLine(1, 1) = Options(1, QB, 1)
                        Payroll = Options(1, QB, 2)
                        Pts = Options(1, QB, 3)
                        
                        ' RB
                        ResLine(1, 2) = Options(2, Mid(RBList(RB), 1, 1), 1)
                        Payroll = Payroll + Options(2, Mid(RBList(RB), 1, 1), 2)
                        ResLine(1, 3) = Options(2, Mid(RBList(RB), 2, 1), 1)
                        Payroll = Payroll + Options(2, Mid(RBList(RB), 2, 1), 2)
                        Pts = Pts + Options(2, Mid(RBList(RB), 2, 1), 3)
                        If MyCase = 1 Then
                            ResLine(1, 9) = Options(2, Mid(RBList(RB), 3, 1), 1)
                            Payroll = Payroll + Options(2, Mid(RBList(RB), 3, 1), 2)
                            Pts = Pts + Options(2, Mid(RBList(RB), 3, 1), 3)
                        End If
                        
                        ' WR
                        ResLine(1, 4) = Options(3, Mid(WRList(WR), 1, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 1, 1), 2)
                        ResLine(1, 5) = Options(3, Mid(WRList(WR), 2, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 2, 1), 2)
                        ResLine(1, 6) = Options(3, Mid(WRList(WR), 3, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 3, 1), 2)
                        Pts = Pts + Options(3, Mid(WRList(WR), 3, 1), 3)
                        If MyCase = 2 Then
                            ResLine(1, 9) = Options(3, Mid(WRList(WR), 4, 1), 1)
                            Payroll = Payroll + Options(3, Mid(WRList(WR), 4, 1), 2)
                            Pts = Pts + Options(3, Mid(WRList(WR), 4, 1), 3)
                        End If
                        
                        ' TE
                        ResLine(1, 7) = Options(4, Mid(TEList(TE), 1, 1), 1)
                        Payroll = Payroll + Options(4, Mid(TEList(TE), 1, 1), 2)
                        Pts = Pts + Options(4, Mid(TEList(TE), 1, 1), 3)
                        If MyCase = 3 Then
                            ResLine(1, 9) = Options(4, Mid(TEList(TE), 2, 1), 1)
                            Payroll = Payroll + Options(4, Mid(TEList(TE), 2, 1), 2)
                            Pts = Pts + Options(4, Mid(TEList(TE), 2, 1), 3)
                        End If
                        
                        ' DEF
                        ResLine(1, 8) = Options(5, DF, 1)
                        Payroll = Payroll + Options(5, DF, 2)
                        Pts = Pts + Options(5, DF, 3)
                        
                        If Payroll <= Budget Then
                            ResLine(1, 11) = Payroll
                            ResLine(1, 12) = Pts
                            r = Teams.Columns(1).Find("").Row
                            For c = 1 To 12
                                Teams.Cells(r, c) = ResLine(1, c)
                            Next c
                        End If
                        
                    Next TE
                Next WR
            Next RB
        Next DF
    Next QB
            
End Sub


Public Sub Combos(TotCount, ListIn, ListOut, ResultAr)


    If Len(ListOut) = TotCount Then
        a = ResultAr(0)
        a = a + 1
        ResultAr(a) = ListOut
        ResultAr(0) = a
        Exit Sub
    End If
    
    For i = 1 To Len(ListIn) + Len(ListOut) + 1 - TotCount
        Call Combos(TotCount, Mid(ListIn, i + 1), ListOut & Mid(ListIn, i, 1), ResultAr)
    Next i


End Sub

Note that the input and output sheets are set to Sheet1 and Sheet2. Run the Fantasy macro. This will generate all the possibilities, then sort the results by points. This could generate many thousands of possibilities, so I'd recommend just entering your top few picks in each category.

Good luck!