Here we have a list of players in a team's squad in the left, their xG and xA per 90, and their defensive contribution points per start. This is all last season's data at the moment again which obviously needs updating as we go. Still need to fill in the position of every player and their defensive contribution stats on the very left, just wanted to get a working model up and running.
The middle part is where it all really happens. Stick the expected team xG for and against from the previous sheet along the top in the GW1F, GW1A, GW2F, GW2A etc. part and repeat for the next 7 weeks. I've decided that's about how long I want to look into the future when making transfer decisions. Everything else basically references that, and using the individual player xA, xG and defensive contribution stats it spits out some sort of estimated points tally for each player on the very right for the next 7 weeks.
Flaws/comments
New signings are difficult. Players' stats may well change quite drastically from last season if they signed for a different club. Players moving from clubs outside of the PL are particularly hard to assess at the very beginning. In this Arsenal example I've used Gyokeres' Champions League xG and xA rather than his Portuguese league stats but all I can do is guess. I've used Zubimendi's xA/xG from La Liga as I believe it's a similar standard of league to the PL, and I've used Partey's defensive contribtion points estimate for Zubimendi as he'll playing in a similar position. Everything will start filtering through a few weeks from now and I'll have a much better picture.
Goalkeepers - don't exactly know what I'm doing with them yet. Was going to just use the clean sheet element of their projected points but we all know they can get points from how many saves they make as well and that the goalie with the most clean sheets isn't always the best one to have. Unsure how to proceed here, and whether I want to flesh everything out fully for them as it would require quite a bit more work.
Defensive contribution stats - I've gone with points per start which is somewhat broad. I've not tried to assess which teams players are more or less likely to achieve these points against. Suppose I could try and nail that down but it's a lot of effort.
Subs/playing time - this completely assumes that every player plays a full 90 which of course doesn't happen, and subs are becoming more and more important in football, having increased impact over the years. Still, I'll be trying to pick players who get a lot of minutes and will be looking at that on the FPL website anyway. Nobody really wants someone who goes off after 65' every week in their team, or a player who only starts occasionally.
Penalties - the taker can change! It's why I've not actually included them in the points calculation part. That's just to take a mental note of each week and add a little to a players' projected xG in my head.
Bonus points - I've not included them at all! Wouldn't even know how to begin to predict that as there's so many ways players can earn BPS, it's different depending on position and they've tweaked how it's calculated from last season anyway. Suppose I could do an average bonus points per game or something and I might actually. Anything beyond that is too complcated for me.
It's ugly! - Need to put some colour coding in there now. Did toy around with that a bit just now but I couldn't quite get anything that looked nice and pretty. Can also collapse/hide the columns for a lot of that stuff in the middle once the data is in the sheet each week which would help a lot. Would end up looking more like this:
Learning my way around a spreadsheet and how to write formulas for some of these cells was an experience! What on earth is this?
That would have made no sense to me at all a few weeks ago, it's why I'm oddly proud. Used functions like VLOOKUP too having no idea what that was either before starting.
Will probably add some cumulative next 5 weeks, next 6 weeks and next 7 weeks prediction totals at the end as well as week-by-week to put more a bit more info on it all. That's easily done and I'd be referencing that a lot.