top of page

How I Built an NBA Player Prop Model - Part 1

Writer: Excel LADZExcel LADZ

G’day, lads!


I’m excited to announce the release of the ‘Excel LADZ - NBA Player Prop Model’! This model generates daily predictions for player points, rebounds and assists across all games. It leverages a variety of stats to calculate expected values for each prop market and applies probability distributions to produce “fair odds”, helping you spot value in the markets.


Want to use this model every day? Become an Official Member of the Excel LADZ Patreon! Membership grants you access to this model and every other one featured on YouTube, including the NBA, MLB, NHL and European Football Model.



  1. Importing Data


As with all my models, I aim to eliminate the need to manually copy and paste stats for each game. Not only is manual entry time-consuming, but it also increases the risk of errors in the model. Instead, I automate daily data updates by connecting the spreadsheet to the internet using Power Query in Excel.


The process for setting up a Power Query varies depending on your computer and Excel version. As a Mac user, I rely on a Web Query, which is less efficient than the “For Web” Power Query option available to Windows users. However, it still gets the job done by ensuring the model fetches new stats automatically each day.


To start, I need a dataset containing every NBA player’s stats for the current season. A great website for this is the ‘Per Game Player Stats’ from Basketball Reference (link below).



Once I’ve used a Power Query to connect my spreadsheet to Basketball Reference, the dataset will include every player’s information - such as their team, points, assists and rebound stats - as shown below.



To estimate a player’s expected points, assists and rebounds for a specific game, I’ll start with their season averages for each stat. These averages serve as a baseline, but adjustments are necessary to account for contextual factors such as the strength of the opposing team.


For example, if a player is facing a team that excels in defensive rebounding, it’s reasonable to expect their offensive rebound numbers to decrease in that game. Similarly, a player’s assist or points could be influenced by the opposing team’s defensive efficiency or pace of play.


To make these adjustments, I’ll incorporate additional datasets that reflect team-based performance. These datasets include:


TeamRankings NBA Team Opponent Assists per Game: https://www.teamrankings.com/nba/stat/opponent-assists-per-game

TeamRankings NBA Team Opponent Points per Game: https://www.teamrankings.com/nba/stat/opponent-points-per-game

TeamRankings NBA Team Possessions per Game: https://www.teamrankings.com/nba/stat/possessions-per-game

TeamRankings NBA Team Offensive Rebounding %: https://www.teamrankings.com/nba/stat/offensive-rebounding-pct

TeamRankings NBA Team Defensive Rebounding %:


To ensure the model can match stats across datasets, I’ll create a worksheet called “Team Names”. This worksheet will act as a conversion table to reconcile differences in team names between Basketball Reference and TeamRankings. For example, Basketball Reference lists the Lakers as the “Los Angeles Lakers” while TeamRankings refers to the same team as the “LA Lakers”. By listing these names in a table, the model will be able to integrate data from both sources, enabling accurate data matching and analysis.


  1. xRebounds

As previously mentioned, each player’s xPoints, xRebounds, and xAssists will start with their season averages and then be adjusted using relevant multiples based on factors like the opposing team’s strength. To streamline the process of selecting a player and their matchup for prop markets, I’ll create a new worksheet named “Dashboard”.


This worksheet will include a dropdown menu to select a specific player and their opposition, a Home/Away option to specify whether the player is playing at home or away (as this can impact performance), and xMP (expected minutes played), which is the player’s season average for minutes played and will serve as a baseline for further adjustments.



The first adjustment I’ll create is the “Home Advantage” multiplier to account for differences in player performance based on game location. Historical data indicates that players perform slightly better at home due to factors like familiar environments, supportive crowds, and reduced travel fatigue. To model this, I’ll use an IF function to apply a small adjustment.


If the Home Status in cell N4 is “Yes”, the multiplier will be the square root of 1 + 0.01 (a 1% increase), reflecting the slight edge of playing at home. If the game is played away, the reciprocal of this value will be used. Using the square root ensures that the adjustment remains symmetrical. The result is a total relative advantage of exactly 1% for players in home games compared to away games.


Next, I’ll use the XLOOKUP function to return Embiid’s average offensive and defensive rebounds for the 2024-25 season. By referencing the “2024-25 NBA Player Stats” worksheet, I’ll look up Embiid’s name and return his offensive and defensive rebounds in two separate formulas.


Thirdly, I’ll calculate a multiplier representing the percentage of Embiid’s average minutes he is expected to play. This is done by dividing the manually adjusted minutes in cell N2 by his average minutes retrieved using the XLOOKUP function. For example, if another player is injured and Embiid is expected to play more than his average 29.5 minutes, I can manually update cell N2. The multiplier will then adjust accordingly, reflecting the updated percentage of his average minutes.


Next, I’ll calculate the PACE  multiplier for the game. The PACE multiplier adjusts for the expected number of possessions in the game based on the pace of play for both teams. In basketball, PACE is a statistical measure of the number of possessions a team averages per 48 minutes. A higher PACE indicates a faster tempo, with more scoring opportunities, while a lower PACE indicates a slower, more methodical style of play.


For example, if Embiid’s 76ers are playing a team that averages a significantly higher number of possessions per game, we can expect the 76ers to also have more possessions in that game. This adjustment helps account for variations in team play styles, which directly affect individual player performances.


Before calculating the PACE multiplier multiplier, I’ll create a cell labelled ‘Player Team ID’ to pull in the team code for Embiid (or the selected player) from the Basketball Reference dataset. This team code will be essential for the formula, as it allows us to cross-reference the team’s PACE with their opponent’s PACE to calculate the multiplier accurately.


Once the team code is in place, I’ll calculate the PACE multiplier using the formula:



This formula adjusts the expected possessions in the game by combining the Team PACE and Opponent PACE into a geometric mean, which represents the average tempo of the matchup. Dividing this by the Team PACE normalises the adjustment, indicating how much faster or slower the game is expected to be compared to the player’s team’s usual pace.


The PACE adjustment is critical because possessions drive most on-court statistics, including rebounds. If the multiplier is greater than 1, it signals a faster game tempo, leading to more possessions and increased opportunities for rebounds. If the multiplier is less than 1, it indicates a slower tempo, reducing the number of possessions and thus the chances for rebounds. 


Lastly, I’ll add an offensive and defensive rebound multiplier to adjust for the opponent’s strength in conceding rebounds. For the offensive rebound multiplier, I’ll use the following formula:



This equation adjusts for the likelihood of securing offensive rebounds in a game based on the opponent’s defensive rebounding efficiency. The numerator in the equation represents the proportion of missed shots the opponent typically allows to be converted into offensive rebounds. Dividing this by the league average offensive rebounding percentage creates a multiplier that adjusts a player’s offensive rebound projection. For example, if the opponent has a high defensive rebounding percentage, the multiplier will be lower, indicating fewer offensive rebound opportunities.




Similarly, the defensive rebound multiplier adjusts for the likelihood of scoring defensive rebounds based on the opponent’s offensive rebounding efficiency. A lower opponent offensive rebounding percentage raises the multiplier, indicating more defensive rebound opportunities, while a higher percentage increases it.


With all the multipliers calculated, I can now determine the selected player’s expected rebounds (xRebounds). This value is calculated by combining the expected offensive rebounds (xOffensiveRebounds) and expected defensive rebounds (xDefensiveRebounds), both adjusted using the respective multipliers.





Thanks for reading lads! In the next article, I’ll explain how I calculated the xPoints and xRebounds for each player. If you liked this explanation, become a member of the Excel LADZ Patreon for exclusive access to all models and in-depth statistical insights like this!

 
 
bottom of page