NBA game simulator in Excel spreadsheet - run 1000's Simulation of NBA Basketball Games

[@ExcelbyJoe]

What features would you like added to a game simulator? I am thinking of building it up. Do we add more team stats to look at? if anyone has ideas, email me please.

[@bgunckel]

I would like to see how you would actually use this when comparing against a Vegas line. For instance, maybe you can create your own line (take the average scores from the sims, or maybe consider the percentages a confidence factor?). Anyway, great video... thank you!

[@raydavis4374]

Hey Joe do you have a way to track player effiiency?

[@marquezpye165]

3pt shooting efficiency offense and defense Pace of play Home court adv.

[@famoustreasures1745]

I am 75 and have never ever worked with Excel or anything similar. However, I thought I would give your video a try. While it took me awhile(actually 2 long days) of constant replay I finally succeeded in completing the exercise. I can't say thank you enough! I had some trouble here and there but finally got it almost perfect per your instructions. If you read this the only hiccup for me is that a few teams such as the Lakers and Clippers don't totally compute such as in the Std. deviation I get #N/A even though it is computed in the dev.table? I am pleased regardless. Keep the videos coming. Great Job.

[@xrandy225]

Great job Joe as usual but I would change one thing which is the Team Rankings number. You set up this number by averaging the offensive average points scored and their opponents average points allowed. While this appears to make sense to most people, I think a better representation would be to calculate how much the defensive average points allowed is either above or below the respective league average, then adding that to the offensive team points scored average. I'll use a random example such as Team A averaging 110 points per game and is playing Team B who allows 110 points per game playing in a league where the average points scored per team per game is 100 points. Your calculation would be that Team A would be estimated to score 110 points, (110+110)/2. However, Team B is 10 points below average on defense compared to the league par which would add those 10 points onto the average points total for Team A making the estimate 120 points and not 110. The defense in this game being worse than the average team compared to par would add those 10 points to their opponents offensive average. Conversely if Team B's defense is above average and allows 80 points per game, your calculation for Team A would be 95 points, (110+80)/2. However, Team B is 20 points above average on defense compared to the league par which would subtract those 20 points from the average points total for Team A making the estimate 90 points and not 95. The defense in this game being better than the average team compared to par would subtract those 20 points from their opponents offensive average. Since the value of a point here or there does indeed matter when attempting to find an algorithm to beat the spread, I'm sure this type of offensive calculation for each team in an upcoming game would better mimic reality thus giving you better overall projections.

[@ExcelbyJoe]

hey, thanks for the comment! This does really make sense to come up with a better version of adjusted score. The one I used was mainly for simplicity to learn the spreadsheet. But this will help get more accurate simulations, thanks!

[@Chacha-mo4me]

@@ExcelbyJoe is it possible for you to make a video that includes this? to be more accurate. thank you

[@warrenlavoie4148]

Definitely will use this.. once I get use to all the functions LOL...Great Video!Thanks

[@ExcelbyJoe]

Glad it was helpful!

[@mtstans]

you can click down on the "load" button to bring up a load to option if ya want to load the web table to current sheet

[@troymarkowski8964]

Please make more of these. Like building off of this model to include the complex formulas such as pace, Offensive Rating, Defensive Rating, and how to weight those as you see appropriate to get a finer tuned model. Idk if that made sense but I loved the video

[@troymarkowski8964]

Also I was doing exactly what you were doing formula wise and getting wild deviations like team totals of over 200 or 38 for example things that are wildly implausible. My guess is how you acquired your standard deviations for the teams is where I messed up.

[@ExcelbyJoe]

you have to make sure you dont include any blanks or future games without scores in your standard deviations. I just made a video about getting nba team score standard deviations and it will be out next werek on my channel. keep an eye out!

[@JT-ce8wk]

@@ExcelbyJoe I second this, if you were to make an update to this with the more advanced stats that would be amazing 🤩

[@rodtastic4292]

Awesome video, finally someone explaining how to use some functions for me! Have you ever had trouble importing data from websites? I started to make my first model (trying with WNBA) and a lot of the advanced data is on weird sites. Thanks and keep the videos coming!

[@ExcelbyJoe]

Yes I have. Its tough to automate data pulls on many sites because websites probably set up their tables that way. There are some good sites that you can, you just gotta search for them. thanks ! glad you liked the video..

[@paytonclyde4820]

instaBlaster...

[@njc11275]

Great video. Just allot to follow for me. I am going to try this with my football database I built and see what happens. I am curious on the results.

[@FadeClown]

Link isn't working, was hoping to find the sim equations. Any help on that ?

[@xahlent]

Hi, I was wondering how you would make one of these simulators for the UFC. If you have any advice could you let me know?

[@jeffhernandez7594]

Great video and really helpful. How often are you updating the standard deviation? after every night? again, thanks super helpful!

[@annadad2023]

Joe, can you make this model but instead of excel use Google sheets?

[@nickrodio8415]

Hey Joe this video is awesome! Im looking into buying your NFL simulation model. I was wondering if the model allows for all football (Ex. NFL and NCAA) and also if I buy it do you just send me the xlsx file or would I acquire it a different way?

[@ExcelbyJoe]

Hi. The football one only allows for NFL. It only has their standard deviations in there and is set just for the football salaries. And yes when you buy it, you would get an Excel file.

[@nickrodio8415]

@@ExcelbyJoe okay thank you!

[@nickrodio8415]

@@ExcelbyJoe If you don’t mind me asking another question, I am confused on what the difference is between clicking the delete button to “simulate” games once the spreadsheet is complete compared to when you calculated one game on the “Simulation” tab and then drag that to however many simulations wanted, basically what I’m asking is what is the output of data after clicking delete?

[@lep751]

i wish there was a million like button i can press this helped me a lot

[@fcpark954]

Very interesting...Did you beat bookmaker with this simulator?

[@leonardorabbachin7015]

Great video, helped a lot!

[@carlodellamea932]

Great job!

[@niccatnitee8313]

what about stats such as scoring differential, strength of schedule , off & def rating ?

[@spazbitte]

Hey Joe. How would you do this for a golf tournament?

[@sjroyley]

Thanks for the video - really helpful. I have tried to use it for Soccer and NHL which are much lower scoring games and I am getting negative numbers for some scores. Is there any way to avoid this?

[@ExcelbyJoe]

Yeah that can happen because the standard deviation could push it to negative, but you would just need to use the MAX function to eliminate this. do something like =MAX(your formula,0) This was it will put the higher of 0 or your formula. so if your std dev formula is -1, it will put 0 as this is the max number of the two.

[@sjroyley]

@@ExcelbyJoe Worked like a charm. Cheers

[@jakegray2785]

Did it end up being at all accurate for either of those sports?

[@bryantndongmo4780]

@@jakegray2785 probably not

[@bibbitybobbity4823]

Great video thank you

[@Jay-ri9nv]

WHERE DID YOU FIND THE STANDARD DEVEATION FOR EACH TEAM OR DID YOU CALCULATE THAT YOURSELF

[@ExcelbyJoe]

I calculate that myself. I don’t know if any site that does it.

[@jakegray2785]

Can pretty much any laptop run these simulations in excel? Im goin to be getting a nice expensive computer in about 6 months, but wanted to get something to have in the mean time. I was just planning on getting a $2-3 hundred dollar laptop but wanted to be sure it was able to do it first

[@ExcelbyJoe]

yeah if its a windows laptop with excel on it, yeah pretty much any laptop can run this. its not as memory intensive as the optimizer tools, so there shouldnt be any problems.

[@jakegray2785]

@@ExcelbyJoe Thanks Joe. By the way I love the videos and appreciate them so much, very informative and simple to follow.

[@marvinkrantz5426]

Wait. Where do you get the standard deviation numbers from?

[@ExcelbyJoe]

You need to get prior fantasy days and calculate the std dev in excel with those past scores. I have a video on using that function. I do not know of any place that provides std dev though.

[@zacharyshindel1709]

@@ExcelbyJoe Do you still know a website that releases all the outcomes of every nba game so that std dev can be calculated?

[@ExcelbyJoe]

@@zacharyshindel1709 www.bigdataball.com but you gotta pay a subscription.

[@zacharyshindel1709]

@@ExcelbyJoe Ok thanks, you don't know any that are free?

[@ExcelbyJoe]

@@zacharyshindel1709 not really. Maybe some sites would post daily game stats but you d have to copy and paste them daily. Big data ball gives all the gsme logs. If you sign up for my sim tool membership, it has updated standard deviations when you pull the data. Unfortunately I don’t know if any good free source.

[@jdilone2377]

how do u setup the excel spread sheet

[@kcl7676]

Nice!!!!👍

[@Dakota7819]

How did you get the standard deviation?

[@ExcelbyJoe]

I found all the game scores online from this season and then used STDEV.P function in Excel to find the teams standard deviations. I should have mentioned that in the video. thanks!

[@Dakota7819]

I did that but keep getting a number around like 12.00-12.59 nothing less of more is that right?

[@ExcelbyJoe]

@@Dakota7819 well you have to do it for each team. did you just run it for all the teams at once? i have a video coming out in a few days on running st dev for a large array like this for if you wanted to update these numbers. you have to put sa formula like =STDEV.P(IF(E:E=E2,C:C)) but depends on which columns are your team names and which are your scores. hope that makes sense. if not let me know

[@Dakota7819]

@@ExcelbyJoe I am running like one teams all home scores and using STDEV.P and then the location of the team scores so get the standard deviation but everything is coming back as like 12.3 or higher

[@ExcelbyJoe]

@@Dakota7819 email me the file excelbyjoe at gmail id have to see what you did because im not sure what is differerent. thanks

[@dovydasneverauskas66]

any chance you can do this for MLB?

[@ExcelbyJoe]

Yep, its on my list to. thanks!

[@coachotalksbasketball8242]

Great video , thank you very much!