📅 Published on: 2024-11-24 09:30:06

⏱ Duration: ( seconds)

👀 Views: | 👍 Likes: [vid_likes]

📝 Video Description:

Download: https://www.patreon.com/posts/excel-ladz-2024-116432682 Basketball Reference: …

🎙 Channel: Excel LADZ

🌍 Channel Country: [channel_country_name]

📂 Tags:

[vid_tags]

🕵️‍♂️ Transcript:

good day Lads in this video I’ll show you how to build a basketball model to analyze probabilities for the money line spread and total points for each matchup using team stats if you wanted to save time or join the EXL lad Sports modeling Community then you can download this model for daily analysis as well as the player based NBA model shown here by clicking the first link in the description below to go to the XL Lads patreon enjoy the video Lads starting off with a blank spreadsheet Lads you’re going to name this raw data and in this worksheet you’re going to use a power query to connect the model to the website basketball reference so that the model is able to update its stats automatically every day instead of you having to copy and paste data which may take a long time so basketball reference I’ve got the link in the description below this website’s got the conference and division standings as well as per game for and against stats for each team so I’ll do the power query right now because this process is different depending on which version of excel you’re using as well as your computer so if you don’t know how to do that search up a tutorial on YouTube it’s got the per game stats for each team as well as their per game opposition stats so how many points that team concedes each game so once that power query has been established you’re going to make a new worksheet and name this NBA team stats and in this worksheet I’m going to collate all the stats in In The Raw data worksheet because the power query process is so tricky this may be presented in different ways depending on your version of XL so the main thing here is collecting all the stats and if you have to use a different formula to what I’m using in the next couple of minutes you may have to do that so from cell A1 across to G1 put the text Team 2 points4 3 points4 free throws for and then two points three points and free throws against so for the teams just grab a list of the teams from the raw data worksheet here it will be raw data b81 to b110 and I’m just going to copy and paste the values over the top so that it’s purely just text rather than an array now I need to pull in the two pointers made per game for every team uh in the range A2 to a31 as well as the three-pointers free throws and then how many they can seed each game as well so for the cavali is I can use the formula equals V lookup the lookup value will be Cleveland in A2 put a dollar sign in front of the column A so that I can drag it down and across the table array will be b81 to Y 1110 and then the column index number is going to be 10 I want to return the 10th column in this array which will be the twoo column in column K here so false for an exact match and the Cavaliers score on average 30.6 two pointers per game so far this season draged that across to D2 now for the three poins four that’s in column 7 of the ray b81 to Y 110 of the raw data worksheet and the free throws four is in column 30 so drag B2 and D2 all the way down to row 31 to fill that out for all the teams now I’ve got to do the same thing for the two pointers three-pointers and free throws conceded each game by each team in column A so that’s the same exact formula V lookup same lookup value the team you’re finding the team in the table array this time scroll down for the per game opposition stats in B 115 to Y 144 and on the basketball reference website this is just the table below the team per game stats column 10 Again comma false for an exact match and the Cavaliers concede less two pointers than they score which is a good thing so drag that across and again same thing the threep pointers will be in the seventh column of that array and the free throws against will be in the 13th column so drag E2 and G2 down to row 31 to fill that out for every team and now I’ve got each team’s stats that’ll be useful for the model I need to make an average those so in cell a33 put the text average and then beside it in b33 use the function function average and then it’s just simply the average of cells B2 to b31 so close the bracket and then drag that across the column G reduce that to one decimal place and then each value should be the same for free throws for and against and for the other methods of scoring if there’s a slight difference that’s just due to a rounding error now make a new worksheet Lads and in here name it NBA team ratings so in here I’m going to rate a team based on how well it scores three points two points and free throws as well as how well it concedes them relative to The League average team so from A1 across put the headers team twoo attack rating two-point defense rating three-point attack three-point defense free throw attack and free throw defense now in A2 I’m going to copy and paste the teams from the NBA team stats and again I’ll just need the list of teams in exactly the same spot and in a fashion similar to what we did in the NBA team stats worksheet I’ve got to format this so it looks good and then find the attack and defense ratings for all the methods of scoring for each team in the range A2 to a31 starting off with Cleveland in cell B2 I’m going to use the formula equals V lookup my lookup value is going to be the team in column A so in this cell it’s dollar sign A2 put the the dollar sign in front of the column so I can drag the formula down and across for the table array it’s going to be A2 to g31 in the NBA team stats worksheet absolute reference that comma I want to choose the second column that’s the 2.4 in column B and then put false for an exact match so close the bracket and then divide by the average in cell b33 so this formula returns Cleveland’s average 2.4 per game and then divides that by the average team in the league and how often they score two pointers and we can see here Cleveland score about 10% more two-pointers on average every single game so I can do the exact same thing for the defensive rating so here it’s a v lookup formula I’m looking up A2 Cleveland within the A2 to g31 range the column I want to return is the fifth in column e this time that’s the number of two pointers Cleveland concede per game I want an exact match close the bracket and then divide by the average in so e33 absolute reference that too and then drag that down to column 31 so Cleveland have a relatively good attack rating they score 2 point is 10% more than the average team and a good defensive rating too a defensive rating of 0.96 means they concede two pointers at a rate 4% less than the league average team so drag the formula from from C2 across to G2 and then all I’ve got to do is change the columns here so um for the three-point attack rating three and then divide by c33 for the three-point defense rating um looking up the 3point is conceded in column six of that array and then dividing by the average in cell f33 and then I’ll do the same thing in column F I’m looking up column 4 and then dividing by d33 and then for G it’s also the same exact change I’m looking up column 7 and dividing by cell g33 so press enter on that and then you can drag those formulas down to row 31 and then the average rating for each column so b c d e f and g should be about one I’m also going to do a home advantage of 2.5% in cell b33 and that’s proven to be historically accurate next Lads I’ll create a new worksheet called dashboard and in here we’ll be able to select two teams see their expected points in the matchup as well as the probabilities for the three main Bing markets the money line spread and under over total points so after I’ve created the new worksheet merg and center the Cs B2 to D3 put the text away team in there do the same for E2 to K3 in here will be able to select the team from L2 to M3 that’ll be where the expected points are and then from N2 to O three the win percentage for the away team and copy and paste that into row five for the home team now before I’m able to get the expected points and the win percentage for each team I’ve got to be able to select the two teams in E2 and E5 so after you’ve applied color scales in cells N2 to N5 to highlight the team with the most likely chance of winning in green select cells E2 to E5 go into the data Tab and then in data valid allow a list our list will be the list of teams to choose from so that can be anywhere but I’ll go equals NBA team ratings A2 to a31 is my range so press okay and then once you do that in the top right hand corner of the cell after you’ve selected it I can click the drop down list and then select the team here by scrolling down my first team will be the Atlanta Hawks and then I can choose their oppos position so this game is the Hawks at the Bulls and obviously the expected points in columns l& M and the win percentages for both teams in columns n and O will update based on the teams you select how good they are and their strengths and weaknesses so after you’ve done that merge and Senter the Sals A8 to 08 and put the text money line copy and paste that in a rows 13 and 19 and put the headers spread and under over now for the teams put equals E2 and equal E5 in A10 and a11 and win percentage and odds next to it do the same thing in a16 and a17 and then put the text threshold under over for the under over Market there so now that I’ve created the formatting for the dashboard worksheet I can go ahead and make a new worksheet called simulations in here we’ll simulate one game between the Hawks and the Bulls and then repeat that process 2500 times to learn about the distribution of results for that game and from there we can determine how often the Hawks win out of 2500 trials how often the Bulls win and how often the points go under over the total as well as how the spread behaves so make the teams in F1 and G1 equal to dashboard E2 and E5 then in cell E2 put the text regular time first we’ve got to simulate the regular time score between the walks and the Bulls and then if need be it can go to overtime 1 2 and I’ll cap it at three overtime periods in this model but going past that is pretty unlikely anyway and probably won’t happen in any of the 2500 trials so to start I’ll need to get the expected points for each team to show in the dashboard and to calculate that I need to start by finding the expected free throws three points and two points each team scores in in the game so in A2 to A3 put the text away and Home Advantage below that in A5 to A6 away and home expected two points then away and home expected three points then a way and home expected free throws Now using all of the multiples above it’s possible to find the away and home expected points and those figures will put back into the dashboard worksheet when we work them out now after you put all that text in we can calculate the away and Home Advantage we put earlier that the total Advantage for the home team is 2.5% so we’ll use a multiplier so that it goes both ways it reduces the away teams expected two points three points and free throws and increases the home team’s scores by the same effect so for the away Advantage it’s 1 / the square root of 1 plus 2.5% in sell b33 of the NBA team ratings and for the Home Advantage it’s the reciprocal just the square < TK of 1 + 2.5% so once I reduced that to three decimal places I can move on to the away team’s expected two points for the game and I can solve that with an xlookup function or a v lookup function if you’re using an older version of excel so that’s equal to V lookup Open Bracket my lookup value will be the away side because I’m finding the away expected two points and I’m looking up F1 in the table array A2 to g31 so I’m matching the Atlanta Hawks wherever they are in A2 to a31 to their attacking twoo rating in column b or the second column in that range and then multiplying that by another vlookup function where I search up the Bulls the home side in the same table array so A2 to g31 and then I returned the Bulls or the homeid two point defensive rating so essentially it’s the attacking rating for the away team multiplied by the defensive rating of the home team then I’ve got to multiply by the average two points made per match that’s in b33 in NBA team stats 27.6 before I multiply by the wayside Advantage which is technically a disadvantage of 0.988 so the formula for finding the aw side or home sides expected two points three points and free throws is their attacking rating in that metric multiplied by the opposition’s defensive rating in that metric multiplied by the average number League wide and then multiplied by the away or Home Advantage depending on if they’re the away or home team I can copy and paste that formula into cell B6 and change the home side or the away side to the home side side so F1 goes to G1 G1 goes to F1 and the advantage goes from B2 to B3 so when I press enter the Atlanta Hawks are expected to score 10 more two-pointers than the Chicago Bulls in this match I can do the same thing in the way expected three points formula in cell B8 this time though I’ve got to change the column index numbers so that instead of looking up the attack and defensive two-point ratings I’m looking up the attack and defensive three-point ratings so it’s in the same array but I’m looking up the ratings in columns four and five and then multiplying by the average three pointer is made by a team each game and then of course multiplying for the away side by the away advantage and sell B2 of this worksheet so when I press enter the Hawks are expected to score 12.2 three pointers in this match against the Bulls Pace that into the cell below again change F1 to G1 and G1 to F1 so we’re looking up the hom Sid’s attacking rating and multiplying by the aways side’s defensive rating and then change the away advantage to the Home Advantage so here the Bulls have quite a significant 3point advantage and I’ll just do the same thing for the expected free throws of course the column numbers will change from 4 to 5 to 6 and 7 uh column 6 and 7 in A2 to g31 are the free throw attack and defense ratings and then I’m multiplying by the average number of free throws made per match that’s the scale and then multiplying by the away Advantage for the away team so again copy and paste that into the cell below change F1 to G1 and G1 to F1 and then change the advantage from way to home if this is moving too fast Lads just pause the video and then write down this formula for yourself now the away expected points is a really really easy calculation it’s equal to the expected two pointers * 2 plus the expected three pointers made time three plus the expected free throws made Time 1 or by itself and so I can do the exact same thing for the home expected points B6 * 2 + B9 * 3 + B12 and then we can see the Chicago Bulls are expected to win this match by a slight amount now while they’re expected to win the Bulls we can’t just say they win every single time uh there’s an element of luck in every single basketball game so we’ve got to simulate these results based on the expected values for the three methods of scoring so to start we can simulate the Atlanta Hawks uh regular time score by using the binomial inverse function where there’s 10,000 trials the probability s is the expected two points divid div by 10,000 the number of Trials and then the alpha argument is a Rand function Open Bracket close bracket and then multiply that by two to find the expected or simulated points rather coming from the expected number of two pointers made so I can do the same thing for the expected number of three pointers I can simulate that using the binomial inverse function and then multiply it by three to get the number of points coming from three pointers in this simulated match and then I’ll do the exact same thing for the away free throws so in this game the Atlanta Hawks scored 112 points but when I simulate it it changes every single time because the number of two-pointers three-pointers and free throws are simulated along the binomial distribution in this match and then for the Bulls I just need to copy and paste the formula into cell G2 and then change the values from the away sides expected values to the home sides so B6 / 10,000 B B 9 / 10,000 and b12 / 10,000 and when I press enter on that I get an a simulated score of 109 to 108 so this game doesn’t need to go to overtime but you know if it was 108 to 108 the game doesn’t just end there I’ve got to come up with a formula to simulate five more minutes of playing time if the regular time score is still the same and to do that I’ll use an IF function where if F2 is equal to G2 put a dollar sign in front of the columns f and g then return a binomial inverse function where there’s 10,000 trials and the probability s will be open bracket 5 / 48 close bracket time B5 / 10,000 comma and then a Rand function for the alpha and close the bracket there multiply by two these are the simulated number of points deriving from two pointers in this simul at overtime one period now add the simulated number of points coming from three pointers and of course for the probability s multip by 5 / 48 which returns 5 minutes of simulated points now add the binomial inverse function for the free throws made as well divid by 10,000 comma Rand for the alpha argument closer bracket there and then I’m going to add F2 so whatever score they get in that 5 minutes is of course added to the score they got in regular time and you know if F2 is not equal to G2 then just leave the score in F2 so I can drag that formula across and down to cell G5 and then the scores in cells F6 or rather F5 and G6 are the final scores for that game so the scores say the same if if they’re not equal in F2 and G2 but if they are equal then 5 minutes of overtime is played if they’re still equal then another period of overtime is played so now I can simulate this single game result 2500 times to get an idea of the distribution and to find the probabilities for the three betting markets I have in the dashboard worksheet so put the two teams in CS J1 K1 in I2 WR the text trials and below it I’m going to spill 1 through to 2500 and that just represents trial 1 trial 2 all the way down to trial 2500 now in cell J2 I’m just going to go equal F5 in K2 equal G5 and that’s the simulated score for this match now I’m going to simulate this simulated score 2500 times using a data table so select this range go into Data select what if analysis and in your data table for the selected range put a column input cell outside of the selected data set and then once you press okay all the simulations will load and uh we can analyze them now of course going one by one the Hawks just lose this game uh they win the next one they win the next one they win the next one uh and then they lose this game 111 to 150 they lose the next game 107 to 23 so you can see all these results are simulated according to the expected values in column B that we calculated and whenever I press uh refresh all or press backspace or alter another cell the simulations will actually update now that we’ve finished that worksheet I can go back into the dashboard and in cell L2 I can pull in the expected points for the away team in this matchup and that’s just equal to the cell in simulations with the away team expected points that’s b14 and then in L5 it’s just equal to l15 in the simulations work sheet the sell below that this figure will never change it’s just the expected value or the average number of points you’d expect the Hawks and Bulls to score in each simulated game now to pull in the probability of the Hawks winning the match I’m going to count the amount of times they beat the Bulls in each of the 2500 simulations in the simulations worksheet so to do that used the some product function open the bracket put a double negative then open the bracket again we’re counting the amount of times that in Sals j3 to J 252 the scores for the Atlanta Hawks are greater than the corresponding cells in K3 to k252 the score the Bulls got close the bracket twice that’s how many times the Hawks beat the Bulls now divide by the number of Trials 2500 to get a probability and in this lot of 2500 simulations the Hawks won 46.76 of the time now because their simulations they naturally change so each time you press enter or update a sell uh there’ll be some variation so for the Bulls it’s just less than the sum of which should be exactly equal to one unless there’s any games that go past overtime 3 very unlikely but if I update the 2500 simulations again there’s a slight change and uh with 2,500 simulations this may change from you know about. 5% to a percentage Point each time but the more trials you do the less this variation um or variance becomes now if I change the matchup to the Blazers at the Rockets then not only does the expected points update but also the win probabilities for each team so I can now fill out the three markets and find the true odds for all of them uh for the Blazers they’ve got a win percentage of 22.2% I’m now going to return either The American or decimal odds for that probability according to what you want so in cells Q2 to R3 merg Center those cells and put the text odds do that for the same thing in S2 to T3 but instead there I’m going to go into the data Tab and using data validation I’ll make a list where you you can choose from American or decimal odds to display in cells C10 and C11 next to the win probabilities for the Blazers and Rockets I’ll select American odds first and to bring in the American odds for the Blazers and Rockets winning this match I’ll need to use the following formula so in so C10 put eals Open Bracket our test is whether S2 absolute reference this is equal to American in speech marks if that’s true then we’ll return the formula for the odds now this is another IF function we’ve got to determine if B10 the win probability is less than or equal to 0.5 if it is then it’s Beach marks plus space Clos to beach marks and use the round function where the number is equal to after two brackets 1 / B10 close the bracket -1 close the bracket multiplied by 100 now comma the number of digits you’re rounding to is zero and then close the bracket if that’s false or the number in B10 is greater than 0.5 then return a negative sign space in speech marks and minus round open bracket -00 / two opening brackets 1/ div by B10 close the bracket minus one close the bracket rounding to zero digits close that bracket twice if all of that is false then it’ll be decimal odds which is equal to 1 / the probability in B10 so the Blazers are at + 352 in c10 and the rockets as the favorites are – 368 so now for the spread we’ve got to choose a threshold and this is applied to the home team so if it’s a positive value we’ll give the home team an advantage right now we’ll handicap the home team 16.5 points so the threshold in b14 is -6.5 so that will make the probability of the Blazers and Rockets covering the spread at around 50% each and so to calculate that in sell B16 for the Blazers considering this Advantage use the sunct function where after two negatives Open Bracket the simulations in j3 to J 252 minus the threshold we set in the dashboard worksheet in cell b14 so make sure you absolute reference that by putting a dollar sign in front of both the column and row has to be greater than the simulations in the next column over or the simulations for the Rockets so that’s equal to simulations K3 to K 252 two closing brackets divide by 2500 the number of Trials and the Blazers um using the 16.5 Point disadvantage for the Rockets have a and change a couple of things so the formula will change to uh the simulations in j3 to J 252 have to be less than uh the simulations in K3 to k252 plus the threshold in b14 so when I press equal um or enter rather then the Cs in B16 and B17 update and the sum has to be equal 2 1 now for the odds I can just copy and paste cells C10 to C11 into c16 and c17 and when I update the probabilities because they’re so close together the variance will be about again 1% or so now for the under over I can also set a threshold in so B20 at 2 131.5 points now copying the formula in cell B16 I can paste that into B22 and get rid of the threshold of b14 the formula is going to be j3 to J 252 plus K3 to k252 has to be less than B20 for the under so the points of the Blazers and Rockets in each game has to be under 231.50 points and this happens 75% of the time when I copy and paste that formula into B2 3 the only thing I have to change is instead of it being under it has to be over the threshold so when I press enter on that I can get the over probability and again the sum of which has to be equal to exactly one because it’s 231.50 there can’t be any uh instances where it’ll be exactly equal to 2 131.5 it’s a decimal uh so if I change it to 26.5 because that’s virtually the sum of the expected points above the probabilities converge to about 50% each so if I change the teams now for any matchup the money line spread and under over will update for the Clippers and Kings the kings are the slight favorite um when the spread is changed to -6.5 points it’s uh in the clip is favor however when I change it to minus 3.5 then it updates accordingly now for the Pacers versus another team let’s select the buck uh the buck become the favorite here they’ve got a 60% chance of winning and again all the markets update so Lads if you want to change the odds to decimal just go ahead and select decimal in the drop- down list insult S2 and then all the odds change to decimal formatting so I’ll reduce this to two decimal places as well as copying the odds into cells c22 to 23 now if you want to update the power query go into the data Tab and select the refresh all icon this will update the model and bring in the newer stats from basketball reference instead of you having to copy and paste the stats in every day thanks for watching Lads if you want to download this model then make sure you click the first link in the description below

🔗 Watch on YouTube

🚀 Related Hashtags: #ANALYSE #Basketball #Games #Win #OverUnder #Spread #Betting #EXCEL #TeamBased #Tutorial

Disclaimer: This video is embedded directly from YouTube. All rights to the video and content belong to the original creator, Excel LADZ. For more details, please visit the original source: https://www.youtube.com/watch?v=RNEzFTiR7uU.