Excel LADZ
patreon
Excel LADZ NBA Model | Overtime & Live Win Probability
🕑 Added 2023-12-30 21:19:56 +0000 UTCG’day, lads
In this video I ran through 3 major changes to the NBA Model. I’ll summarise how I did them below.
JOIN the best Excel Sports Modelling Community by becoming a member of the Excel LADZ Patreon! Receive downloadable access to every model (including the Excel LADZ NBA 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!
xFGA+FTA Change
In the previous formula to simulate a player’s points, xFGA+FTA was kept constant. That is, the simulated amount of 3PT’s, 2PT’s and FT attempts always combined to be equal to the player’s xFGA+FTA. However, this shouldn’t be the case as a player’s usage in a game often fluctuates.
As a result, F12 (xFGA+FTA for the starting PG) is now simulated using the formula: BINOM.INV(10000, F12/10000, RAND()).
The BINOM.INV function runs 10,000 trials, with the probability of success for each trial being the xFGA+FTA (F12) divided by 10,000 (number of trials). The RAND() function then simulates a random number of FGA+FTA for the player based on the inverse of the binomial cumulative distribution function.
Overtime Change
If a game is tied after 48 minutes of Normal Time, 5 minutes of Overtime is played. This process continues until a winner is established at the end of a period. In this model, I included a maximum of 3 Overtime periods (Columns O - Q).
Overtime can be thought of as a game in itself; just 5/48 the length. As a result, simulating a player’s points in Overtime should use the same formula. However, you must scale down a player’s xFGA+FTA to 5/48 of the Normal Time formula. This can be captured through an IF function.
IF the sum of the Home Team Normal Time Points (Column N) is equal to the sum of the Away Team Normal Time Points, then the formula runs 5/48 of the match again through the BINOM.INV function. If not, then “ ” is returned. This means the cell is left blank and no value is recorded if the game finishes in normal time.
Live Win Probability
To capture the Expected Points and Live Win % in real time, the same concept can be applied. If 3 quarters have been played, then 75% of the match has already been decided, leaving 25% left to be played. Therefore, the Pts. Sim formula can be scaled down 75% to reflect only one quarter being left to simulate (as its outcome hasn’t been decided/played yet) in the match.
I’ve managed to capture this “scale” by inputting the current quarter in cell L3 of the ‘Live Win Probability’ worksheet as well as the quarter time left in cell L6. Then, I’ve used a combination of the VALUE, RIGHT and LEFT functions to read these cells and translate the minutes left in the game to a fraction over 48.
I decided to apply this function directly to a player’s xFGA+FTA in cell F13 in order to make the formulas more readable. Like I did in the overtime columns (O - Q), this function could also be attached to each F13 to scale down a player’s expected shots left in the match. However, this would make the formula extremely long.
As there’s only 5:00 left in the image above, Russell’s xFGA+FTA is scaled down from 14.0 (able to be seen in the Result worksheet) to only 1.5. This will automatically impact the Pts Sim. formula and will result in the simulated number of points for a team naturally decreasing as time runs out.
I also had to make a change to the Overtime cells, changing F13 to Result!F12. This is because the 5/48 change should be applied to a player’s full 48 minute xFGA+FTA figure, not the scaled one present in Column F of the ‘Live Win Probability’ worksheet.
Notes
- You may notice in the intro to the video, the teams change but the players from Rows 13 downwards do not. When recording this, I forgot to change the teams from the ‘Result’ worksheet. Please make sure that when you’re changing the matchup, change it from the ‘Result’ worksheet and NOT ‘Live Win Probability’.
Thank you for reading lads, JOIN to become a lad!
Comments
ricky coolige
How do I do the "Power Query" for the NBA ?
Jorge ibañex
If I do it that way but I get an error and it remains in values only
Excel LADZ
Hello! You can 👍 Go into the 'Data' tab of Excel, and select the "Refresh All" icon. This will refresh the Power Query's connection with the internet, and bring in updated data 💪
Jorge ibañex
Hello boy, I have a question, can you update data from this excel?
Excel LADZ
G'day lad 😃 Unfortunately, if a power query requires a password there's most likely no way to use it unless you know the password - believe me, I've tried 😆 I would make sure that the adjustments to a player's xMP are being made in the 'Player Working' worksheet in column B. These changes will then flow through to the "Live Win Probability" page. The formula there is just to make sure that the total sum of xMP for the team is 240. You can write over this formula if you want, but you've got to make sure that your sum equals 240 - otherwise you're giving a team an unfair advantage. If you continue having problems with the different spellings between datasets, consider making a table. In one column, the names in my dataset. In another column beside it, the matching names from your dataset. That way you can use a VLOOKUP to match the names from your source, to the model's source, and make the stats flow more automatically. Of course, this depends if getting all these player's names is easy enough and doesn't involve too much manual work to start with. One of the updates I'm going to make in the future to the NBA Model is getting the injuries and minute projections loaded into the model automatically. So in time, I'm definitely exploring this 🔥
Anthony Palmiotto
Hello lad, I'm tryin to tweak the NBA model to account for minutes projections rather than historical minutes played and had a few questions 1. I'm trying to do power query on a site that requires a log in...is there any way to get around this or do I just have to manually download projections as a CSV and upload? 2. I've worked through the inevitable errors that arise from different spellings of players names but I'm stuck on having my minutes projections flow through to the "Live Win Probability" page. It's easy enough to replace the starters minutes but I'm getting jammed up for the rest of the team since you have formulas to calculate those players minutes. How would I go about making the tweak needed to utilize my own minutes projections?
jonathan
I'm also looking for free data sources for fields in Excel and wanted to build another model for player props for MLB
jonathan
I'm looking for a template for microsoft excel 2016 pro. I'm looking for major league baseball probablity of team winning and what score will be in first inning or score in the first 5 innings.
Excel LADZ
G'day lad 🔥 Yes, you're right 👍
Anthony Palmiotto
And I'm assuming even though that post is dated 12/22 it has these changes mention in this post?
Anthony Palmiotto
Is the most recent version of this in the 12/22 post and called "Excel LADZ - 2023-24 NBA Model Win Probability.xlsx"
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!
Mike Forte
I was trying to pull it from https://www.numberfire.com/nba/daily-fantasy/daily-basketball-projections . They seem to have a pretty good projection of minutes.
joeguerby .
Can you share the link where you get the projected of minutes for all players... I can give it a try. I can tell you the player's name on fbref is slightly different of other sources like nba.com. If you can share your file or the link to donwload it dayli I think that I can help
Excel LADZ
Hi lad, thanks for watching 🔥 There's a few different ways a home advantage can be factored into a contest. Firstly, a home multiple can be applied to player stats. For example, a 5% total advantage can be given to the number of xFGA+FTA for the home team in a match. Take the matchup Bucks @ Cavs. Each player on the Cavs could have their xFGA+FTA multiplied by the square root, while every player on the Bucks would have their xFGA+FTA be decreased by that amount (reciprocal of SQRT(1.05) ) so that the total advantage for the Cavs is 5%. Another option is each team having a custom home advantage as determined by historical data (e.g. taking into account number of points scored/conceded at home versus their neutral stats in the last five years). Of course, there is the option of using a combination of a flat multiple and a custom home advantage for each team through using a scale (e.g. 50/50 each). I hope that helps lad 👍
Jackson Ewers
Great content! How would you recommend factoring in a team being home/away? I followed all of your videos, but thought it would be neat to someone bring home/away into the equation
Mike Forte
Is it possible to add an additional tab where we can bulk load in projected minutes for all players? I usually get a csv file with all players for the day and their projected minutes and manually enter the minutes. I've tried to use the xlookup for the players working tab and have gotten the minutes to come in from the csv date but I constantly get a bunch of errors when the simulations start to run.
Excel LADZ
Hi lad, apologies if I haven't been specific enough. The 'Excel LADZ - 2023-24 NBA Model Win Probability.xlsx' from Dec 22 contains the 'Win Probability Worksheet', except it's named 'Result'. I combined the two worksheets into one here, because two separate lots of 1,000 simulations for each team (like I did in the video) tends to slow down the model and decreases the functionality of the file. If you are using live data, you can enter the Expected Score and Current Time into the 'Results' worksheet and the Win %'s will update. However, if you are working out pre-game win probabilities, then the Current Score needs to be set to 0-0 and the Time Left needs to be set to 1 & 12:00 (indicating 12 minutes left of the first quarter). So yes, this post is technically the finished sheet lad 👍
Swq Qwerty
Still different file, so I downloaded Excel LADZ - 2023-24 NBA Model Win Probability.xlsx from Dec 22, and its different file than in your video, it is missing live win probability sheet and nothing looks the same in simulations sheet(no changes like in the video), You mention before that this is finished sheet, so Im not sure why Im getting wrong sheet,I tipple checked it and downloaded it multiple times.... can you take a peek ? Thank you
Swq Qwerty
Thank you
Excel LADZ
No worries lad. The change had already been implemented into the 23-24 NBA Model, so you don’t need to make any changes 👍 That change was more for the people who don’t have download access to the vid 😃
Swq Qwerty
hI laD, since you changed the formula , is this replacing original Excel LADZ - 2023-24 NBA Model.xlsx from Dec 2 update, sorry for confusion,
Swq Qwerty
Thank you
Excel LADZ
Yep, that's the finished one lad. It's a standalone worksheet and not directly connected to the normal model by adding in a worksheet (like I did in the most recent video). This is because the 2,000 simulations made the model too slow and so I didn't want everybody to lose functionality. However, the 'Live Win Probability' worksheet allows you to do both. If you put 1st quarter, 12:00 remaining with a Current Score of 0-0 then it would operate as the normal Result worksheet 👍
Excel LADZ
Hi lad, this formula can be seen in 28:00 of the original 'PREDICT NBA Games With Probability | Excel Tutorial' video on the channel. The 5 players with the highest MP use 100% of the MP. Of what's left over (in terms of 240-SUM(top 5 minutes) is divided between the 8 bench players according to their own average. This is why a bench player's xMP if often adjusted down from their MP, as there is less 'minutes remaining' than the sum of all the bench players. I hope that helps lad.
Swq Qwerty
I only see one from December 22, did you uploaded the finished one?
Steven Emerson
Question for ya, how do you make sure the xMP on the results sheet always adds up to 240?
Swq Qwerty
I see it, thank you
Excel LADZ
Hi lad, the file is attached to the previous post for members only 👍
Swq Qwerty
Missing download links
