Excel LADZ
patreon
Excel LADZ - 2024-25 NHL Model
🕑 Added 2024-10-10 19:22:03 +0000 UTCG'day, lads! Attached to this post is the 'Excel LADZ - 2024-25 NHL Model'. In this spreadsheet, I've calculated each team's ATT & DEF Ratings over a trailing 20-game window. For example, if a team has only played 5 games in the 24-25 season, their Ratings for Game 6 will consist of 25% 24-25 data (5/20) and 75% of 23-24 data (15/20). Obviously, when more than 20 games have been played in the current season, a team's strength will be based entirely on their performance in the current year 🔥
To refresh the data, go into the 'Data' tab and select the "Refresh All" icon. This will update the spreadsheet's connection to the website 'Hockey Reference' and bring in the most up-to-date stats 👍
Please let me know if you have any questions, lads! Articles on the mathematical & statistical side of the model are soon to come!
Comments
Excel LADZ
G'day, Tony! Great pick up mate 🔥 I really like that idea, so if it's alright, I updated the file to that range in the team selection boxes. Thanks for the comment 💪
Tony O'Hora
'Allo LADZ! One slight adjustment to the NHL file. I changed the data validation for the team selection boxes to 'Raw Data!AO80:AO11' which makes the choices alphabetical if you prefer it that way.
Excel LADZ
Hello, Ada! To do this, you'd need to replace the 'Hockey Reference' Power Query in the "Raw Data" worksheet with a Power Query from ESPN. Unfortunately, the process of a Power Query is different for both your version of Excel AND your computer. So there's no definitive guide I can give you if you aren't familiar with the process 😢 However, it usually is very straightforward. A search of "Power Query from Web in Excel" on YouTube should help a lot for Windows users 👍 Once the ESPN stats are in, you would need to update the table located in A1:J33 of the "ATT DEF Ratings" worksheet. Not only the stats, but also the team names and formatting may be different in the ESPN data. So of course, care is required in calculating formulas for all of that. Good luck, Ada! If you need anymore help, don't hesitate to message me through Patreon or Discord 🔥
Ada Lovelace
Hello! Hockey reference standings and stats are outdated compared to ESPN. How do I change the data so it pulls from ESPN instead?
Excel LADZ
Hi Mike, thanks for the comment 🔥 Power Query has got to be the most powerful, but frustrating tool, in Excel. It’s frustrating in the sense that the process of creating a Power Query changes based on your version of Excel AND your computer. I use Excel for Mac, which is notoriously weaker than Excel on Windows for building queries. As a result, I can’t pull in individual tables in a webpage. I have to run a ‘Web Query’ and I virtually only get access to the first table on the website. With all the tables in this model, they’ve been either the first or only table located in the link I’ve used for the Power Query, allowing my Max to bring it in. All this information might be useless if you’re not using Mac. But, my advice would be to pull in all the tables you need using your method of Power Query. As it is likely to be formatted differently to my version, you then will have to update the formulas that directly rely on the data pulled in. For example, each team’s ATT & DEF Rating depends on their Goals for and Against - pulled in from the Raw Data. I hope this helps a bit mate, unfortunately it’s a very tricky process in Excel 👍 Of course, you can message me through Patreon or Discord with more specific issues you run into while building a model 😃
Mike Poropat
I tried to update the old one by just adjusting the reference data that this was pulling from, but I couldn't replicate the raw data page. I noticed even on the updated one your raw data page is different than mine. I believe the issue is that I couldn't import the individual tables on the reference website, and instead had to import the entirety of the website, which led to the import of all the headings an menu items. I can't figure out how you imported those individual tables though, assuming I'm correct. Just curious as I'm trying to learn how to create stuff like this on excel and you have been incredibly helpful and knowledgeable on that!
LVBadFun
You are a legend! Thank you!
Excel LADZ
Hello, Nate 👋 Perhaps further into the season 🔥 I'm planning on making the player adjustments for the NFL first, and then I'll likely find a way to implement squads into this model. I'll keep everyone updated 😃
Excel LADZ
G'day, mate! No, it doesn't affect any of the formulas. With the way that I brought in these stats using a Power Query, I don't think there is a way to directly fix that problem. However, if you really wanted to see those stats in particular, you could create a new version of the Hockey Reference dataset in a new worksheet. In here, you could convert the dates back to their original value by either switching the formatting, or, if that doesn't work, devising a custom formula. I hope that helps mate 👍
Nate Contreras
any chance you'll update this to include player stats?
LVBadFun
The raw data sheet is converting the data into dates. Does that affect any of the formulas for predictions? Is there a way I can fix that?
Excel LADZ
Absolutely, lads! I'll add this to the NHL Model (and other models) over the next few days. There's a formula I used in the NFL Model that used an IF function to differentiate between 'American' odds and 'Decimal Odds'. Here it is: =IF($Q$1="American",IF(C10<=0.5,"+ "&ROUND(((1/C10)-1)*100,0),"- "&-ROUND(-100/((1/C10)-1),0)),1/C10). Basically, cell Q1 is the selection of the odds type: American or Decimal. C10 is the corresponding probability you're converting to odds; e.g. 42.1% = + 137 in American odds 👍
Matthew Moscatello
I would like to do the same.
Luther Bennett IV
I want to convert to "American" odds, can you help me with that?
