Excel LADZ
patreon
Excel LADZ - 2024 NFL Model
🕑 Added 2024-09-03 11:06:35 +0000 UTCG'day lads! Attached is the 'Excel LADZ - 2024 NFL Model'. Select two teams in the "Dashboard" worksheet, and using the ATT/DEF Ratings automatically generated in the "Team Ratings" worksheet, an Expected Score and markets for the Moneyline, Spread and Total Points will be calculated.
To update the stats in the model, go into the 'Data' tab and select the "Refresh All" icon. I do this at the end of the YouTube video ๐
The model uses a trailing 12-game system to evaluate teams and their strength. Currently, player lineups are not included in the model. However, this is a feature likely to be added in soon - a change in a Quarterback can have a significant impact on a team's chances.
Right now, the model is entirely team-based. In the next few weeks, player lineups are likely to be incorporated into the model ๐ฅ
Please let me know if you have any questions!
Comments
Randy Reed
Hi, since you are updating this NFL model weekly as you said in another post, can you please add the date to the name of the file so that we know exactly when you last updated the file? Right now, I can't tell if it is updated or not.
Excel LADZ
G'day, Jonathan! Apologies, I initially missed your comment ๐ If you ever think I've missed something, feel free to message me privately through Patreon or Discord! I love to answer everybody's questions, but it's easy for some posts to slip through the radar at first. To answer your question, I'm not sure. If you have the latest version of Excel, the base version should be working. I work on a Mac, have Office 365, and it works for me. The "Legacy" version of every file uses old functions to replace more efficient array formulas in the base models. As a result, it should work for everybody - regardless of your computer or version of Excel. The two files perform the same though - it's just that the "Legacy" versions are always slightly less efficient in its calculation time (e.g. it takes 10 seconds to run 1,000 simulations instead of 3). If the "Legacy" files are working, I wouldn't worry at all. I would double check that your version of Excel supports array functions like XLOOKUP, LET, SORT and FILTER. These are some of the functions I use in the normal models ๐ฅ Let me know if that helped, Jonathan ๐
Jonathan Preston
can someone explain why the legacy spreadsheets are the only ones working for me? Is it because I aim on a MAC? I have the latest version excel?
Justin Rice
Are player lineups going to be incorporated?
Flynn Michael
Thanks for the information, and no worries about the timing of your reply. I might try the more abbreviated look back to see the impact on the projections / wagering outcomes for a few weeks and get back with you. Also looking forward to the NBA season and its model.. Thanks Again !
Excel LADZ
Hello, Flynn! Apologies for the late response. If you think I haven't seen your comment, don't hesitate to send me a little reminder or a private message through Patreon ๐ For now, I'll leave the model how it is. In my opinion, only 6 or 7 games is too small of a sample size for the Team Ratings to be accurate. Although, it can be very easily changed to 6 games if you'd like ๐ฅ in columns N-R of the "Team Ratings" worksheet are each team's ratings. These ratings are used to calculate each team's projected score and subsequent markets. To change the ratings from a 12-game look back to a 6-game lookback, replace every instance of the number 12 with the number 6. This is done in cells N2:R33 as well as N35:Q35. Hopefully that helps, lad ๐ช
Excel LADZ
Hello, Nick ๐ฅ Apologies mate. I've tested the NFL Model from my computer, and there's no issues for myself. Usually, Excel crashes when the calculations become too much - e.g. too many simulations. Try re-downloading the model from this post (try the 'Legacy' model if you haven't already) and give it another go. If that doesn't work, I can attach a version of the file with less simulations which may reduce the stress on your computer. Of course, the only downside to this is there will be more variation with each batch of simulations due to a smaller sample size ๐ Let me know how it goes lad ๐
Al Smithee
Hey Lad - for some reason, the NFL model is crashing Excel when I try and update data. NHL is running fine and dandy and I assume NBA will be just fine... but NFL doesn't work. Any help would be appreciated!
Flynn Michael
If not, is there an easy way for individuals to update the applicable formulas to reflect the desired changes I have described?
Flynn Michael
Now that the NFL season is well underway and injuries / trades / effect of bye weeks have more of an impact on projections than last seasonโs data, have you considered updating the calculations to using a rolling 6 - 7 game โlook backโ rather than the 12 game currently utilized ?
Jacob Labontรฉ
Okay no problem, let me see if i can figure it out on my end!
Excel LADZ
Yep! However, it's a different type of model. Here, I calculate each team's ATT (Attack) and DEF (Defence) Ratings, and then use those to simulate a team's points scored in a matchup. In the 'Warren Nolan - College Football ELO Model' uploaded on the Patreon, these ratings aren't calculated. This is because collecting ATT & DEF Ratings for college football teams are difficult, due to the vastly different strength of schedule between teams as well as high player turnover. Rather, ELO Ratings from the website 'Warren Nolan ELO Ratings' are imported into the spreadsheet, and used to find the Win % of each team in a matchup using a formula. It's a much simpler model, but usually captures the strength of each team pretty well ๐ฅ
Charles
Is there a version for Collage Football?
Excel LADZ
G'day, mate! The #NAME? error in Excel suggests that Excel doesn't recognise the name of the function being used. For example, the 2013 version of Excel (Excel 2013) doesn't recognise the LET function. This is because LET is a part of the new array functions released to Excel 2021 and later. To solve this issue, I've created a version of the file that uses formulas compatible with nearly all versions of Excel. Nothing is mathematically different, I've just swapped the newer & more efficient array formulas with older alternatives. This is the 'Legacy' file, which can be downloaded in this post ๐ Let me know if this solves your problem lad ๐
Excel LADZ
Hi mate, welcome! Unfortunately, I don't think so. The model relies on its connection to the NFL Website through a Power Query. This may be interrupted by running the file through Numbers or Sheets. Apologies for the inconvenience ๐ข
Excel LADZ
Hi mate, very shortly I'm uploading a quick how-to-use video that covers how to select two teams and see the predicted results. Before that, if you have a read of the description in this post, as well as watch the last two minutes of the YouTube video, you'll see how I use the model ๐ Please don't hesitate to ask anymore questions lad ๐ฅ
Excel LADZ
G'day, Charles! Welcome ๐ฅ The probabilities for each marketโmoneyline, total points, and spreadโare calculated experimentally rather than using a single formula. I run 5,000 simulations in the model to observe the distribution of outcomes. For instance, if the Bengals beat the Patriots in 2,000 of the 5,000 simulations, the model gives the Bengals a 40% win probability. Each time the data is refreshed, the 5,000 simulations are rerun, which may yield slightly different results. In the next set of simulations, for example, the Bengals might win 2,050 games, resulting in a 41% win probability (2,050/5,000). Over time, as more simulations are run, the law of large numbers ensures that the results will converge toward the true probabilities. This means that with enough trials, the percentage will stabilise (have less variance), giving us more reliable estimates for each market ๐
Charles
Why are the Simulation numbers changing after refreshing the data
Charles
Is there instructions on how to use the NFL Model system
Jacob Labontรฉ
New subscriber, excited to use this tool for the remainder of the season. Just curious, as I am running this on a Mac is it compatible with Numbers app, and or Google Sheets. Cheers
Tanner Bender
Hey man, I downloaded the model but when I enable editing i get a name error on most of the content. Is there a way to fix this? thank you
Flynn Michael
Just back from holiday and watched the excellent video. I simply needed to update my Excel version - everything working perfectly well now. Thanks again !
Vasilis
Thanks!
Excel LADZ
G'day, mate! Welcome ๐ฅ Yes, there is. This link may be helpful: https://support.patreon.com/hc/en-us/articles/212052266-Getting-Discord-access
Vasilis
Hey! Random question here, is there a discord server for paid customers? Cannot find anything
Flynn Michael
Guess Iโll need to wait for the YT tutorial- I do not have either data tab or refresh all icon available
Excel LADZ
Hi, Flynn! Yes, it does update with new stats weekly. To do this, go into the 'Data' tab and select the "Refresh All" icon. This will refresh the spreadsheet's connection with the internet and bring in the most recent stats. I would update the spreadsheet at the conclusion of each week ๐
Flynn Michael
Does the model data update automatically with most recent week information and if so when ?
ic1uc1
turns out i had to use legacy on laptop, works now.
ic1uc1
when i put 46.5 in B19 i get over 8.16 and under 91.84. must be me, lol.
Excel LADZ
Hi lad, that's great ๐ฅ Video will definitely be up shortly. With regards to the totals, please make sure you adjust cell B19, which is the "threshold". For example, with the Saints at the Cowboys today, the over/under is set at 46.5 points. When you change cell B19 to 46.5, the Over & Under probabilities change to pretty much 50/50 for that game. Apologies if this wasn't clear. I'm releasing an article today or tomorrow that covers all of this and gives a quick tutorial on how to use the model ๐
ic1uc1
look forward to the video since i do not make any sense as to the spread sheet numbers. when i put in the totals it gives a high chance on every game of it being under total points.
Excel LADZ
Hello, Binoy! Apologies, I will upload a full how-to-use tutorial onto the Patreon very soon. Right now I'm filming the YouTube video for the model, so plenty of information is yet to come ๐ Right now, the model doesn't import the odds or spread from any betting websites. The probabilities are generated from the simulations. So, in B10 and B11 you have the ability to type in your own spread and see how the probabilities adjust. For example, typing in '-10.5' in B10 should give the Bills around a 30% chance to cover according to cell C10. The over/under points threshold in cell B19 is also adjustable. Setting this to 70.5 points should yield an over probability of 18-19% for the Bills game. Please let me know if this helps lad ๐
Binoy Sheth
Looking forward to using this! thank you for all your models! I refreshed the data and for tonights game for the bills at the dolphins, it will no update the spread as well as odds....the current spread is dolphins -2.5, however the model is fixating it at -2.5. Could you please advise the best way to help update this?
Flynn Michael
Very much appreciated ๐๐ฐ
Excel LADZ
G'day, Flynn! That's a great point; I agree. I live in Australia, and over here it's very much Home vs Away. However, on American websites and sportsbooks (e.g. FanDuel) it is Away/Home. I'll make this change today, along with adding in a feature to simulate overtime in a match. Thanks again ๐
Flynn Michael
Very much looking forward to utilizing this updated and expanded model for the rest of the season! Thanks for all the work and efforts!
Flynn Michael
New model looks great; however, in the โUpcoming Fixturesโ section of the dashboard the home and away team column headings should be reversed from โHome / Awayโ to โAway / Homeโ as NFL teams are usually presented. In addition the home team is typically listed below the visitor in rows 1/2 and 3/4.
Excel LADZ
G'day, Nick! Yes, the stats have been uploaded onto the NFL website now. I'm releasing the model as we speak! Article + YouTube Video + Legacy version (compatible with older versions of Excel) are soon to come ๐
Al Smithee
Hey LADZ! Do you know when the week 2 model will be dropped now that stats should be in from week 1?
Excel LADZ
Thanks a lot, Jeff ๐ Please, don't hesitate to let me know if you have any questions ๐ฅ
Jeff Walston
New subscriber here, just wanted to say that I really appreciate all the work you put into these models.
