As the FPL phone app is useless at times and doesn't even work properly when you look at the head to head teams I knocked up my own FPL spreadsheet. It looks at the API to get live data and gets the 16 players league score info for the teams. It includes who has the bonus points at that moment in time as again the FPL take ages to update after the game. It works well enough and gives you up to date scores rather than waiting the hour plus for the app to update to add any bonus points. This is a basic version of what it looks like and I just auto upload the table online so I can view it on my phone at any time.
The only problem I have now is the swapping of players if subs are required as 1 or more of the 11 initially selected players haven't played any minutes. Again this takes an age for the FPL to update after the game has finished but it's possible to look at the player status at any moment in time and make subs depending on that status, even during live games. The goalkeepers are easy to do as it's just a one on one direct check to see if they needs changing but other 10 selected outfield players are a bit more complicated due to the formation requirements and limits.
This is an example of a team with 15 squad players with various bits of info that I think is enough to work out the team of 11 players to be counted and the are what the columns show.
A - 15 squad players
B - Player name
C - Team they play for (irrelevant)
D - Position they play
E - Fixture (irrelevant)
F - Whether the game has been played or not (I don't think this is relevant as minutes played by player will determine if they can be in the FINAL 11)
G - How many minutes the player has played
H - How many points the player has
I - Player status to either be in FINAL 11 or on BENCH
Every squad has the following number of players in a position.
2 Goalkeepers
5 Defenders
5 Midfielders
3 Forwards
The 11 players who are in the final team has to have the minimum of the following.
1 Goalkeeper
3 Defenders
3 Midfielders
1 Forward
So ignoring the goalkeepers, all the formations allowed for Defenders, Midfielders, Forwards are as follows.
3, 4, 3
3, 5, 2
4, 3, 3
4, 4, 2
4, 5, 1
5, 3, 2
5, 4, 1
In the example table of players above it uses the formation is 3, 5, 2 and that all have played at least 1 minute in their game so it says PLAYED in column F. The only subs allowed are ones that would change the formation to any of the above. Subs who have played minutes are brought in if any of the squad players from #2 to #11 who have not yet played a minute. One thing to note is that if a sub is a FORWARD and there is a FORWARD who has played 0 minutes then the straight swap would be made first to keep the same formation. There are 3 of these in the example and the subs need to be looked at in order of #12 first then #13 then #14 and then #15. The subs don't have to match the position if the new formation created is a valid one so for example a forward could come in for a midfielder in the team above.
In the team above, squad players 1, 4, 5 & 8 haven't played so they need substituting if it's possible. That's only if there is a valid sub to be swapped with them or else they just stay in the FINAL 11. The first sub to be looked is the goalkeeper but that's easily done using a simple formula as there's only 2 goalkeepers to compare so that's SUB 1 done. The first oufield player who didn't play is #4 which is DEFENDER 3. The SUB 2 required for #4 defender has to look at first available sub which is #13 FORWARD 3. However that would mean a formation of 2,5,3 which isn't a valid formation. That means #13 is to be skipped for SUB 2 but may be valid for SUB 3 & 4 checks. The next available sub is #14 DEFENDER 4 so it's a like for like position so this is a valid sub so it is to be made. The formation is still the left as 3,5,2 for SUB 3 checks.
The next player to be subbed #5 MIDFIELDER 1 so needs to look at the first sub available which is #13 FORWARD 3 again. This sub would allowed as the new formation would be 3,4,3.
The final player to be subbed #8 MIDFIELDER 4 and the last sub available is #15 DEFENDER 5. This is allowed as the new formation would be 4,3,3. That means the final team would look like this below as all 4 subs have been used. The SUBS column H would simply say FINAL 11 or BENCH without any reordering of players as there's no real need for it to be reordered. The total team points are simply added up only if the player is in the FINAL 11 and they're highlighted in light red in both tables.
There are probably many permutations of subs depending on how many are required due to whether players have any minutes or not. Ignoring goalkeepers, there could be more than 3 outfield players who don't play any minutes. Those 3 are replaced using valid subs with formation limits and the others players who don't play any minutes would stay in the final team of 11 players. This can happen for example if some games for the players #2 to #11 have not yet played but players #13 to #15 have played. Players #13 to #15 would be subbed in temporarily to show the potential team but if one of #2 to #11 play then it may mean players #13 to #15 are then put back on the bench. I can work it all out in my head yet it sounds complicated to explain. I think it's an easy process if you consider the player state at that moment in time such has their match played yet, have they played any minutes etc, what position are they etc.
Again, ignoring the goalkeepers, there could be another way to look at the outfield players. Starting with SUB 2 and checking if there's a space for them. SUB 2 is a FORWARD so it first has to look at a direct swap but the 2 FORWARDs in the team have both played minutes. If one FORWARD had 0 minutes then it would be a straight swap but there isn't so now it needs checking to see if the FORWARD can replace a DEFENDER or MIDFIELDER. It would look for the first player in the list on 0 minutes and then check if they can be replaced and keep it a valid formation. In this example it would be DEFENDER 3 it would find first but that would mean a formation of 2, 5, 3 so it isn't valid. It would then find MIDFIELDER 1 next and this is a valid sub and the new formation would be 3, 4, 3. The next sub would be for SUB 3 which is a DEFENDER and this would be a straight swap for DEFENDER 3 keeping the formation 3, 4, 3. The final sub would be for SUB 4 which is a DEFENDER and it would find MIDFIELDER 4 and make the final formation 4, 3, 3.
Reading all my waffle again before posting make it look complicated but I don't tihnk it is when you work it out manually, it's just I can't think how to do it in Excel. I hope I've explained this enough without confusing people too much and I would prefer any suggestions that could use formulas in cells and extra columns aren't a problem. I got so far doing it but using multiple columns which got messy! I'm not really that experienced in VBA though I have used it to manually change code I've found to suits my needs many times in the past.
| FPL TEAM EXAMPLE.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| N | O | P | Q | R | S | |||
| 24 | WEEK | 13 | 23/10/2022 19:20 | |||||
| 25 | # | HOME | PTS | PTS | AWAY | |||
| 26 | 1 | TEAM 1 | 30 | - | 49 | TEAM 2 | ||
| 27 | 2 | TEAM 3 | 52 | - | 59 | TEAM 4 | ||
| 28 | 3 | TEAM 5 | 47 | - | 54 | TEAM 6 | ||
| 29 | 4 | TEAM 7 | 64 | - | 64 | TEAM 8 | ||
| 30 | 5 | TEAM 9 | 48 | - | 47 | TEAM 10 | ||
| 31 | 6 | TEAM 11 | 67 | - | 63 | TEAM 12 | ||
| 32 | 7 | TEAM 13 | 57 | - | 61 | TEAM 14 | ||
| 33 | 8 | TEAM 15 | 75 | - | 69 | TEAM 16 | ||
SUBBING EXAMPLE | ||||||||
The only problem I have now is the swapping of players if subs are required as 1 or more of the 11 initially selected players haven't played any minutes. Again this takes an age for the FPL to update after the game has finished but it's possible to look at the player status at any moment in time and make subs depending on that status, even during live games. The goalkeepers are easy to do as it's just a one on one direct check to see if they needs changing but other 10 selected outfield players are a bit more complicated due to the formation requirements and limits.
This is an example of a team with 15 squad players with various bits of info that I think is enough to work out the team of 11 players to be counted and the are what the columns show.
A - 15 squad players
B - Player name
C - Team they play for (irrelevant)
D - Position they play
E - Fixture (irrelevant)
F - Whether the game has been played or not (I don't think this is relevant as minutes played by player will determine if they can be in the FINAL 11)
G - How many minutes the player has played
H - How many points the player has
I - Player status to either be in FINAL 11 or on BENCH
| FPL TEAM EXAMPLE.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | SQUAD # | PLAYER NAME | TEAM | POSITION | FIXTURE | FIXTURE PLAYED? | PLAYER MINS PLAYED | POINTS | ||
| 2 | 1 | GOALKEEPER 1 | CHELSEA | GOALKEEPER | CHELSEA - MAN UTD | PLAYED | 0 | 0 | ||
| 3 | 2 | DEFENDER 1 | ARSENAL | DEFENDER | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 6 | ||
| 4 | 3 | DEFENDER 2 | MAN CITY | DEFENDER | MAN CITY - BRIGHTON | PLAYED | 90 | 2 | ||
| 5 | 4 | DEFENDER 3 | LIVERPOOL | DEFENDER | NOTT'M FOREST - LIVERPOOL | PLAYED | 0 | 0 | ||
| 6 | 5 | MIDFIELDER 1 | CHELSEA | MIDFIELDER | CHELSEA - MAN UTD | PLAYED | 0 | 0 | ||
| 7 | 6 | MIDFIELDER 2 | MAN CITY | MIDFIELDER | MAN CITY - BRIGHTON | PLAYED | 90 | 3 | ||
| 8 | 7 | MIDFIELDER 3 | ARSENAL | MIDFIELDER | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 2 | ||
| 9 | 8 | MIDFIELDER 4 | BRIGHTON | MIDFIELDER | MAN CITY - BRIGHTON | PLAYED | 0 | 0 | ||
| 10 | 9 | MIDFIELDER 5 | MAN UTD | MIDFIELDER | CHELSEA - MAN UTD | PLAYED | 90 | 3 | ||
| 11 | 10 | FORWARD 1 | MAN CITY | FORWARD | MAN CITY - BRIGHTON | PLAYED | 90 | 13 | ||
| 12 | 11 | FORWARD 2 | ARSENAL | FORWARD | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 1 | ||
| 13 | 12 | GOALKEEPER 2 SUB 1 | LIVERPOOL | GOALKEEPER | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 6 | ||
| 14 | 13 | FORWARD 3 SUB 2 | LIVERPOOL | FORWARD | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 2 | ||
| 15 | 14 | DEFENDER 4 SUB 3 | NOTT'M FOREST | DEFENDER | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 1 | ||
| 16 | 15 | DEFENDER 5 SUB 4 | CHELSEA | DEFENDER | CHELSEA - MAN UTD | PLAYED | 90 | 5 | ||
| 17 | TOTAL POINTS | 30 | ||||||||
SUBBING EXAMPLE | ||||||||||
Every squad has the following number of players in a position.
2 Goalkeepers
5 Defenders
5 Midfielders
3 Forwards
The 11 players who are in the final team has to have the minimum of the following.
1 Goalkeeper
3 Defenders
3 Midfielders
1 Forward
So ignoring the goalkeepers, all the formations allowed for Defenders, Midfielders, Forwards are as follows.
3, 4, 3
3, 5, 2
4, 3, 3
4, 4, 2
4, 5, 1
5, 3, 2
5, 4, 1
In the example table of players above it uses the formation is 3, 5, 2 and that all have played at least 1 minute in their game so it says PLAYED in column F. The only subs allowed are ones that would change the formation to any of the above. Subs who have played minutes are brought in if any of the squad players from #2 to #11 who have not yet played a minute. One thing to note is that if a sub is a FORWARD and there is a FORWARD who has played 0 minutes then the straight swap would be made first to keep the same formation. There are 3 of these in the example and the subs need to be looked at in order of #12 first then #13 then #14 and then #15. The subs don't have to match the position if the new formation created is a valid one so for example a forward could come in for a midfielder in the team above.
In the team above, squad players 1, 4, 5 & 8 haven't played so they need substituting if it's possible. That's only if there is a valid sub to be swapped with them or else they just stay in the FINAL 11. The first sub to be looked is the goalkeeper but that's easily done using a simple formula as there's only 2 goalkeepers to compare so that's SUB 1 done. The first oufield player who didn't play is #4 which is DEFENDER 3. The SUB 2 required for #4 defender has to look at first available sub which is #13 FORWARD 3. However that would mean a formation of 2,5,3 which isn't a valid formation. That means #13 is to be skipped for SUB 2 but may be valid for SUB 3 & 4 checks. The next available sub is #14 DEFENDER 4 so it's a like for like position so this is a valid sub so it is to be made. The formation is still the left as 3,5,2 for SUB 3 checks.
The next player to be subbed #5 MIDFIELDER 1 so needs to look at the first sub available which is #13 FORWARD 3 again. This sub would allowed as the new formation would be 3,4,3.
The final player to be subbed #8 MIDFIELDER 4 and the last sub available is #15 DEFENDER 5. This is allowed as the new formation would be 4,3,3. That means the final team would look like this below as all 4 subs have been used. The SUBS column H would simply say FINAL 11 or BENCH without any reordering of players as there's no real need for it to be reordered. The total team points are simply added up only if the player is in the FINAL 11 and they're highlighted in light red in both tables.
| FPL TEAM EXAMPLE.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 23 | SQUAD # | PLAYER NAME | TEAM | POSITION | FIXTURE | FIXTURE PLAYED? | PLAYER MINS PLAYED | POINTS | FINAL 11 /SUBS | ||
| 24 | 1 | GOALKEEPER 1 | CHELSEA | GOALKEEPER | CHELSEA - MAN UTD | PLAYED | 0 | 0 | SUB 1 | ||
| 25 | 2 | DEFENDER 1 | ARSENAL | DEFENDER | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 6 | FINAL 11 | ||
| 26 | 3 | DEFENDER 2 | MAN CITY | DEFENDER | MAN CITY - BRIGHTON | PLAYED | 90 | 2 | FINAL 11 | ||
| 27 | 4 | DEFENDER 3 | LIVERPOOL | DEFENDER | NOTT'M FOREST - LIVERPOOL | PLAYED | 0 | 0 | SUB 3 | ||
| 28 | 5 | MIDFIELDER 1 | CHELSEA | MIDFIELDER | CHELSEA - MAN UTD | PLAYED | 0 | 0 | SUB 2 | ||
| 29 | 6 | MIDFIELDER 2 | MAN CITY | MIDFIELDER | MAN CITY - BRIGHTON | PLAYED | 90 | 3 | FINAL 11 | ||
| 30 | 7 | MIDFIELDER 3 | ARSENAL | MIDFIELDER | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 2 | FINAL 11 | ||
| 31 | 8 | MIDFIELDER 4 | BRIGHTON | MIDFIELDER | MAN CITY - BRIGHTON | PLAYED | 0 | 0 | SUB 4 | ||
| 32 | 9 | MIDFIELDER 5 | MAN UTD | MIDFIELDER | CHELSEA - MAN UTD | PLAYED | 90 | 3 | FINAL 11 | ||
| 33 | 10 | FORWARD 1 | MAN CITY | FORWARD | MAN CITY - BRIGHTON | PLAYED | 90 | 13 | FINAL 11 | ||
| 34 | 11 | FORWARD 2 | ARSENAL | FORWARD | SOUTHAMPTON - ARSENAL | PLAYED | 90 | 1 | BENCH | ||
| 35 | 12 | GOALKEEPER 2 SUB 1 | LIVERPOOL | GOALKEEPER | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 6 | SUB 1 | ||
| 36 | 13 | FORWARD 3 SUB 2 | LIVERPOOL | FORWARD | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 2 | SUB 2 | ||
| 37 | 14 | DEFENDER 4 SUB 3 | NOTT'M FOREST | DEFENDER | NOTT'M FOREST - LIVERPOOL | PLAYED | 90 | 1 | SUB 3 | ||
| 38 | 15 | DEFENDER 5 SUB 4 | CHELSEA | DEFENDER | CHELSEA - MAN UTD | PLAYED | 90 | 5 | SUB 4 | ||
| 39 | TOTAL POINTS | 44 | |||||||||
SUBBING EXAMPLE | |||||||||||
There are probably many permutations of subs depending on how many are required due to whether players have any minutes or not. Ignoring goalkeepers, there could be more than 3 outfield players who don't play any minutes. Those 3 are replaced using valid subs with formation limits and the others players who don't play any minutes would stay in the final team of 11 players. This can happen for example if some games for the players #2 to #11 have not yet played but players #13 to #15 have played. Players #13 to #15 would be subbed in temporarily to show the potential team but if one of #2 to #11 play then it may mean players #13 to #15 are then put back on the bench. I can work it all out in my head yet it sounds complicated to explain. I think it's an easy process if you consider the player state at that moment in time such has their match played yet, have they played any minutes etc, what position are they etc.
Again, ignoring the goalkeepers, there could be another way to look at the outfield players. Starting with SUB 2 and checking if there's a space for them. SUB 2 is a FORWARD so it first has to look at a direct swap but the 2 FORWARDs in the team have both played minutes. If one FORWARD had 0 minutes then it would be a straight swap but there isn't so now it needs checking to see if the FORWARD can replace a DEFENDER or MIDFIELDER. It would look for the first player in the list on 0 minutes and then check if they can be replaced and keep it a valid formation. In this example it would be DEFENDER 3 it would find first but that would mean a formation of 2, 5, 3 so it isn't valid. It would then find MIDFIELDER 1 next and this is a valid sub and the new formation would be 3, 4, 3. The next sub would be for SUB 3 which is a DEFENDER and this would be a straight swap for DEFENDER 3 keeping the formation 3, 4, 3. The final sub would be for SUB 4 which is a DEFENDER and it would find MIDFIELDER 4 and make the final formation 4, 3, 3.
Reading all my waffle again before posting make it look complicated but I don't tihnk it is when you work it out manually, it's just I can't think how to do it in Excel. I hope I've explained this enough without confusing people too much and I would prefer any suggestions that could use formulas in cells and extra columns aren't a problem. I got so far doing it but using multiple columns which got messy! I'm not really that experienced in VBA though I have used it to manually change code I've found to suits my needs many times in the past.
