UFPL Spreadsheet
-
- Grumpy Old Man
- Posts: 1554
- Joined: 01 Oct 2009, 17:59
- FPL:
UFPL Spreadsheet
I created an Excel spreadsheet to assist me with assembling my UFPL team this season, and thought I would share here so that others can use it to help with their August transfers. Download from here.
I'm not an Excel expert and the sheet isn't perfect, but I've found it pretty useful so far. Here's a breakdown of the various sheets included:
'GK'/'DEF'/'MID'/'FWD' Sheets
These sheets contain player lists. Example screenshot shows the midfielders list. Type '1' in column D for any player you want to pick, and '0' for any player not in your team.
Player list may not be 100% up-to-date, but it is easy to edit. For a new player (e.g. Pedro), simply add him, his team and his price to the first blank row. For any player who moves between clubs (e.g. if Mane moves to Man United), just edit the team in column B.
'Team Summary' Sheet
Contains basic information about your team according to the players you picked in the previous sheets - how much of your budget you've spent, how many players from each club, etc. It will tell you if you have picked the wrong number of players in a certain position, or if you have gone over budget.
Note that the pivot table (cell F20) does not automatically refresh if you make any changes to your team. To refresh all pivot tables in the workbook (there are more later), press CTRL-ALT-F5 or click 'Refresh All' on the 'Data' ribbon.
'Fixture Summary' Sheet
This sheet provides an at-a-glance view of which teams have the most MD2/MD3 fixtures. Note that this currently only goes up to GW14, as we don't have fixtures beyond this yet. Also worth noting that it includes fixtures from GW1 onwards, so perhaps not the most useful sheet at this stage, since some of these fixtures have passed. In future I might try to make this sheet configurable to start from GWx.
'Gameweek Summary' Sheet
Here you will find a breakdown of how your squad will look each gameweek - e.g. how many MD1 defenders you have, how many MD2 forwards, etc. Again this only goes up to GW14 currently since we don't yet know fixtures beyond this. This should be a more useful sheet than the previous one. For example, looking at the screenshot below, we can see that this team needs some more MD2/MD3 options for GW7. You can click on the 'GW7' hyperlink to take you to a summary of your team for that week.
Individual Gameweek Sheets
These sheets are labelled 'GW1', 'GW2'..., 'GW14'. Each of these sheets provides a full breakdown of how your squad will look that week - it will tell you each player's matchday and fixture. Ideally you will want a decent selection of players (including one reasonable captain option) each day. In the example screenshot below (GW7), we see that this is not currently the case, so we may want to consider adding WAT/CRY/WBA/EVE players to our squad.
As with all pivot tables in the workbook, these do not automatically refresh. To refresh all pivot tables in the workbook, press CTRL-ALT-F5 or click 'Refresh All' on the 'Data' ribbon.
Also note that the sheet is not designed to handle double gameweeks. Hopefully not an issue since it's unlikely we'll know much about these during either of the transfer windows.
'Fixtures' Sheet
This is where the other sheets look to find information about fixtures. They are accurate as I have entered them as of today, but if any fixtures are moved to a different matchday, this is easily amended by editing column D.
FPL v UFPL Points Comparison Sheets
These sheets are somewhat separate to the rest of the workbook, but I thought I'd include them anyway. They compare 2014/2015 FPL scores to UFPL scores, and sort by % difference (excluding players who didn't score at least 50 points in both variants). This might help to highlight some value picks (e.g. defensive midfielders, or centre backs who get a lot of clearance/block/interception points).
There is a sheet for each position, and an overall summary sheet. As a whole, defenders are treated most kindly by UFPL's scoring system relative to FPL, with strikers the least kindly treated. I'm not entirely sure what conclusions to draw from this in terms of how to assemble the best team, but it's interesting to see the data presented anyway.
I'm not an Excel expert and the sheet isn't perfect, but I've found it pretty useful so far. Here's a breakdown of the various sheets included:
'GK'/'DEF'/'MID'/'FWD' Sheets
These sheets contain player lists. Example screenshot shows the midfielders list. Type '1' in column D for any player you want to pick, and '0' for any player not in your team.
Player list may not be 100% up-to-date, but it is easy to edit. For a new player (e.g. Pedro), simply add him, his team and his price to the first blank row. For any player who moves between clubs (e.g. if Mane moves to Man United), just edit the team in column B.
'Team Summary' Sheet
Contains basic information about your team according to the players you picked in the previous sheets - how much of your budget you've spent, how many players from each club, etc. It will tell you if you have picked the wrong number of players in a certain position, or if you have gone over budget.
Note that the pivot table (cell F20) does not automatically refresh if you make any changes to your team. To refresh all pivot tables in the workbook (there are more later), press CTRL-ALT-F5 or click 'Refresh All' on the 'Data' ribbon.
'Fixture Summary' Sheet
This sheet provides an at-a-glance view of which teams have the most MD2/MD3 fixtures. Note that this currently only goes up to GW14, as we don't have fixtures beyond this yet. Also worth noting that it includes fixtures from GW1 onwards, so perhaps not the most useful sheet at this stage, since some of these fixtures have passed. In future I might try to make this sheet configurable to start from GWx.
'Gameweek Summary' Sheet
Here you will find a breakdown of how your squad will look each gameweek - e.g. how many MD1 defenders you have, how many MD2 forwards, etc. Again this only goes up to GW14 currently since we don't yet know fixtures beyond this. This should be a more useful sheet than the previous one. For example, looking at the screenshot below, we can see that this team needs some more MD2/MD3 options for GW7. You can click on the 'GW7' hyperlink to take you to a summary of your team for that week.
Individual Gameweek Sheets
These sheets are labelled 'GW1', 'GW2'..., 'GW14'. Each of these sheets provides a full breakdown of how your squad will look that week - it will tell you each player's matchday and fixture. Ideally you will want a decent selection of players (including one reasonable captain option) each day. In the example screenshot below (GW7), we see that this is not currently the case, so we may want to consider adding WAT/CRY/WBA/EVE players to our squad.
As with all pivot tables in the workbook, these do not automatically refresh. To refresh all pivot tables in the workbook, press CTRL-ALT-F5 or click 'Refresh All' on the 'Data' ribbon.
Also note that the sheet is not designed to handle double gameweeks. Hopefully not an issue since it's unlikely we'll know much about these during either of the transfer windows.
'Fixtures' Sheet
This is where the other sheets look to find information about fixtures. They are accurate as I have entered them as of today, but if any fixtures are moved to a different matchday, this is easily amended by editing column D.
FPL v UFPL Points Comparison Sheets
These sheets are somewhat separate to the rest of the workbook, but I thought I'd include them anyway. They compare 2014/2015 FPL scores to UFPL scores, and sort by % difference (excluding players who didn't score at least 50 points in both variants). This might help to highlight some value picks (e.g. defensive midfielders, or centre backs who get a lot of clearance/block/interception points).
There is a sheet for each position, and an overall summary sheet. As a whole, defenders are treated most kindly by UFPL's scoring system relative to FPL, with strikers the least kindly treated. I'm not entirely sure what conclusions to draw from this in terms of how to assemble the best team, but it's interesting to see the data presented anyway.
- Mav3rick
- FISO Jedi Knight
- Posts: 20858
- Joined: 20 Jul 2009, 20:35
- FS Record: FPL: 1082, 1201, 1800, 10203
The stats are dark and full of errors.
Re: UFPL Spreadsheet
I don't play UFPL, but that sheet looks like awesome work Silkworm!
- Stemania
- FISO Jedi Knight
- Posts: 20448
- Joined: 27 Aug 2006, 11:54
- Location: On the Iron Throne of xG, the seat of The Crown Prince of the Stat Perverts. Or if not, in the STC!
- FS Record: Best: TFF 321st. FPL 129th. FFS Career HoF Peak 2nd (Live 1st). Ability since lost.
- FPL:
Re: UFPL Spreadsheet
Ha ha, I wish I'd signed up for UFPL now just so I had an excuse to look through all this properly - cracking work.
-
- Grumpy Old Man
- Posts: 1554
- Joined: 01 Oct 2009, 17:59
- FPL:
Re: UFPL Spreadsheet
Thanks gents - it's a shame that UFPL doesn't seem so popular, although can completely understand the reasons why. I'll likely update for next season so you can take it for a spin then
- Monsignor
- FISOhead
- Posts: 500
- Joined: 11 Aug 2015, 01:33
- Location: Montenegro
- FPL:
Re: UFPL Spreadsheet
Amazing work man, thanks!
- Corroded Soul
- Treebeard
- Posts: 182
- Joined: 05 May 2015, 22:56
- Contact:
Re: UFPL Spreadsheet
Astounding work silkworm, I just love stats.
I can't fathom why UFPL isn't more popular either.but it is still fairly new.
I'm completely enjoying this version, safe to say I already prefer the Extra Large to Large size.
I can't fathom why UFPL isn't more popular either.but it is still fairly new.
I'm completely enjoying this version, safe to say I already prefer the Extra Large to Large size.
- andrewschofield66
- Kevin and Perry
- Posts: 18
- Joined: 22 Aug 2013, 16:52
- Location: Hersham
- FPL:
Re: UFPL Spreadsheet
But I bet even Einstein couldn't accommodate a U21/HG field?
-
- Grumpy Old Man
- Posts: 1554
- Joined: 01 Oct 2009, 17:59
- FPL:
Re: UFPL Spreadsheet
Ha, honestly didn't cross my mind. Have never drafted a team that fell down due to that rule before.andrewschofield66 wrote:But I bet even Einstein couldn't accommodate a U21/HG field?
Have saved my 16 transfers, so the spreadsheet will be getting plenty of use during these two weeks as I effectively wildcard!
- thebillfella
- FISO Knight
- Posts: 11409
- Joined: 07 Dec 2006, 13:24
- Location: Republic of Mancunia
- FS Record: 6th FPL 19/20; 1st TFF StartXI 14/15; 8th TFFE11; 18th TFFE12; 1st FISO TFFE11&12; 1st FISO Full Draft 12/13,14/15,19/20,20/21; 1st Block14/15; 1st 5ASChampLge 12/13; 1st TFF Super614/15; 1st Spring17; 1st FISO Div Premx6, Cup, Mirror,Prem&H2H Champ 19/20
- FPL:
Re: UFPL Spreadsheet
Think you need to do one of these for our team
- andrewschofield66
- Kevin and Perry
- Posts: 18
- Joined: 22 Aug 2013, 16:52
- Location: Hersham
- FPL:
Re: UFPL Spreadsheet
thesilkworm wrote:Ha, honestly didn't cross my mind. Have never drafted a team that fell down due to that rule before.andrewschofield66 wrote:But I bet even Einstein couldn't accommodate a U21/HG field?
...then I'm sure Gareth Southgate and Woy will soon be getting in touch.
View Latest: 1 Day View Your posts