Excel LADZ
patreon
Accounting For Player Strength - Alternative Methods
🕑 Added 2024-03-01 04:01:40 +0000 UTCG’day lads! In the best sports models, the lineup of a team is considered. How you factor in changes to a lineup within a model is very important, and in this article I’ll give you some strategies!
JOIN the best Excel Sports Modelling Community by becoming an Official Member of the Excel LADZ Patreon! Receive downloadable access to every model as well as technical posts, like this, which will improve your understanding of sports modelling. You also have the ability to directly message me lads, so I can help you with any problems!
Player-Based Model
In the best and most accurate sports models, the strength of a team is calculated by considering the strength of each of its players. While in a defensive context the overall strength of a team might be considered (as the quality of a player’s defence is very hard to quantify), Player-Based Models are able to effectively account for squad ins & outs.
For example, consider the Excel LADZ NBA Model. A team’s simulated score is the sum of each player’s simulated points. There’s also a defensive adjustment applied to each player by calculating the overall opposition defence strength (multiple = opposition average points conceded during season/points conceded average throughout league).
In the above simulation, the New York Knicks had a score of 118. Running 1,000 of these games yields an average (or expected) score of 110 for the Knicks, giving them a Win Probability of 44.7% (winning 447 out of 1,000 trials against the Warriors).
However, you may notice the injured players excluded from the Knicks lineup:
- Julius Randle
- Mitchell Robinson
- OG Anunoby
When included in the lineup, they improve the Knicks’ Expected Score and consequently their Win Probability.
A Player-Based NBA Model is possible due to the statistics available. The split and accuracy of every player’s shots is recorded, and is able to be simulated to project points scored.
Furthermore, there is reliability to these stats. NBA players take hundreds of shots every season. Due to the Law of Large Numbers, a player’s shot percentages will converge to the true value because of the huge amount of data generated each season.
However, when there is less frequent data, the output of the model can become more volatile.
Download the Excel LADZ NBA Model here: https://www.patreon.com/posts/2023-24-excel-93076132
Player-Based Model - Volatile Data
In the process of building an NHL Model, I decided to use a player’s Goals Created to calculate their value to the team. While this model worked well for two teams close in ability, the model overestimated the difference between a relatively good and bad team.
In the above example, the Canadiens are playing the Panthers. The Panthers are overwhelming favourites according to this model, with an 86.22% chance of winning. This is significantly higher than the bookmaker’s projection of 75%.
The reason for this discrepancy is the ‘Goals Created’ stat. While NBA players shoot and make hundreds of shots throughout the season, good NHL players only create about 0.2 goals per game. As a result, a sudden flurry of GCs for a player drastically increases his value even if the Goals Created can be attributed to pure luck (and vice versa).
Consider an NHL player who has contributed 2 Goals Created over 10 games. His contribution towards his team can be valued at 0.2 goals. However, in Game 11 suppose he scores 2 GC’s. Now that he has 4 Goals Created over 11 games, his contribution rises to 0.36 goals, nearly doubling his worth from one good game. On the other hand, a dry spell could undervalue a player.
In conclusion, the Goals Created stat by itself is not powerful enough to be the sole input into a model. This was proved by backtesting the model.
Now, if I switch to a team-based model, the output of the model becomes more accurate and realistic (as proven & evident through backtesting).
Here, the team’s Goals Scored and Goals Conceded stats are considered and used to come up with the Expected Goals Figure. While the results are more accurate, this model can’t quantify the change in a team’s strength when a player is injured (like in the NBA Model).
Team-Based Models with a Player Adjustment
Good news lads, there is a compromise! It is possible to build a model that takes into account a team’s overall strength, but make adjustments to those figures when considering the quality of a team’s lineup.
In this example, I’ll use a model to predict the winner of an Australian Football League (AFL) match. In AFL, a team can either score a Goal (worth 6 points) or a Behind (worth 1 point). Building a model using team-based statistics, I get the following Attack (ATT) and Defence (DEF) Ratings for each team in each scoring method (Goals & Behinds).
***If you don’t recognise/understand ATT & DEF Ratings, have a read of this lads: https://www.patreon.com/posts/excel-ladz-7-88895696 ***
Let’s consider the Goal ATT for the Lions in our example. Let’s pretend the Lions have their starting forward injured, and need to replace them. Obviously, this should reduce the Goal ATT Rating for the Lions. However, we still have two questions we need to answer: how, and by how much?
To assess the individual strength of each player in the Lions’ lineup, I can grab their Average Fantasy Score (called SuperCoach in Australia) throughout the season. The sum of all attacking players’ Fantasy Scores will equal the Team Attacking Fantasy Score. In this way, I’m using individual stats to come up with a total team score. The results are below:
There is a noticeable pattern here; the higher the SuperCoach ATT, the higher the Goal ATT. I can plot this with Excel and then find an equation (with an intercept of 0).
In the equation produced above, I can input the team’s ATT SuperCoach Sum going into a game and output an expected Goal ATT. This allows me to proportionally scale a player’s absence/appearance in a lineup.
For example, the Lions have a Goal ATT of 1.14 and a SuperCoach ATT of 831. Suppose a good player with a score of 80 is not playing and is replaced with a weaker player that has an expected score of 30. This would reduce the Lions’ SuperCoach ATT from 831 to 781.
With a SuperCoach ATT of 831, the Lions have an Expected Goal ATT of 1.08 (831 * 0.0013).
With a SuperCoach ATT of 781, the Lions have an Expected Goal ATT of 1.01 (781 * 0.0013).
Taking 1.01 / 1.08, the Lions have experienced an Expected Reduction of 0.93. This can be applied as a multiple to the Lions current Goal ATT Rating. 1.14 * 0.93 = 1.06. Therefore, the Lions new Goal ATT Rating going into the match has been reduced from 1.14 to 1.06.
Thanks for reading lads, if you have any questions let me know here on Patreon or on Discord.
Comments
Excel LADZ
Hi lad, there should be a solution to this 💪 I would have to insert some sort of text function (e.g. TEXTBEFORE, LEFT, etc) to pick up the first position defined in a value like PF-C. This altered function could return the TEXTBEFORE the "-", which would give "PF". Then, Achiuwa would be automatically be pulled into the PF starting position. TEXTBEFORE may have to be replaced by something else for the Legacy version of the file, but it would be a similar process 👍 Thank you for bringing this to my attention lad, I'll make sure the file is patched in the next couple of days to reflect the change. I'll let you know 🔥
Carlos Torres
Hey lad !!! Quick question ... every time I refresh the NBA model's "Raw Player Data", I've had to manually change (in Col. C) the positions to one or the other when the player has played in different positions throughout the season. For ex., Precious Achiuwa has played with both TOR-NYK this season (as both a C and PF) - therefore, when looking at the "results" tab, it won't pull Achiuwa into the PF starting position based on his MP, because it can't define whether if he's a C or PF. So I manually go into the Raw Player Data tab and modify each player (not many of them) to the latest team/position played so that the Results tab pulls him over correctly. I'm hoping that this description helps you follow me... and not sure you could have the file do that automatically? Well, anyway ... the file is GREAT !!! Just wanted mention that small adj I do to the file. THANKS again!
Excel LADZ
Hi lad, that's great 🔥 Also, welcome! I recommend messaging me on Discord or through Patreon Direct Messages for more sports modelling discussion 👍
Bobby
I also have Bullpen game logs and team game logs. Once again, would be happy to share those excel workbooks with you.
Bobby
Please know that I do NOT want anything monetarily, but would love to work with you on baseball. I have game logs for past years on every pitcher that started a game both standard stats and advanced. Let me know.
Excel LADZ
Hi lad, thanks for commenting 🔥 With regards to Baseball, you're absolutely right. Before the season starts it's a priority to add in pitcher stats so the model becomes more accurate. I'm also investigating building a completely player-based MLB Model, similar to the Excel LADZ Model. So I'll keep everybody updated with that 👍 In the NBA model, you can use the TEXTBEFORE function to filter these out. For example, IF(LEN(player position)>2, TEXTBEFORE(player position, "-"), player position). This formula would return PF for something like PF-C, and for all single positions like PF, C, P it would leave it as is 😃
Excel LADZ
Hi lad, that's definitely something I could release in the future 🔥 For example, a player's FIFA Rating could be considered as the metric for the strength of a player. Then, squad ins and outs could be quantified by the change in the sum of FIFA Ratings for the team's Attack and Defence (like in the above article) 👍
Kankundiye Speciose
How about soccer, could you create soccer model which also consider players stats?
Bobby
In the NBA Model, how do you make changes when a players position returns PF-C? In Baseball, why is there is no accounting of starting pitching?
Excel LADZ
That’s awesome lad 🔥
Excel LADZ
Definitely lad 👍
JMP SS
Loving the videos. I've been sports trading the past year and am looking to create models etc. Your info is great. I built & added your poisson football sheet to my Web scraper this week, works great. Learning some great excel skills.
Maksims Hromcovs
Great explanation. Thanks a lot. I hope soon we can see NHL model :)
