Excel LADZ
patreon
Excel LADZ - NHL Model
🕑 Added 2024-03-05 01:10:08 +0000 UTCG'day, lads
UPDATE: Due to a change in the Hockey Reference Power Query within the file, I've made a small change to the model. Teams that have qualifies for the Finals will now have an asterisk, *, next to their name.
Attached to this post is the Excel LADZ - NHL Model. Navigate to the 'Result' worksheet, where you can select the two teams for the match.
To Refresh the Data, go into the 'Data' tab and select the "Refresh All" icon. This will update the model's connection to the internet, and import the latest NHL stats!
If you have any questions/suggestions lads, don't hesitate to let me know on Patreon or Discord π
Comments
Mike Poropat
Thanks, because I'm also trying to learn and understand these excel sheets, I am curious why 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.
Excel LADZ
G'day, Mike! Welcome π I'll get this fixed for you. I haven't released the 2024-25 version of the NHL Model yet, where I include the 2024-25 stats & standings. Perhaps when I upload this, your Power Query issues will be solved. I'll upload this model very shortly (1-3 days), along with an article summarising the changes I've made. Apologies for the inconvenience π I had to wait until the season started for the Hockey Reference website to publish the relevant 2024-25 datasets π₯
Mike Poropat
I'm trying to just update the power query to pull from the new standings and it's coming in, but its coming in with all the headers and menu items as data, messing up the cells and formulas. Even when I try to just replace the connection you already established with the identical connection for the 2023-2024 standings, it still does this. Is there something you're doing to format that connection so that only the relevant data from the standings and expanded standings tables are being imported? To be clear, if I refresh the data it updates to the most recent 2023-2024 standings data, but if I edit the query and use the same exact data set, it still brings in all the menus and such. The only yellow arrow button it allows me to click is one for the entire website. I imagine that's the problem, but I don't know if that's something on my end or what.
James
I switched gears. I am doing MLB now, since it is mid season, so model should have no artificial issues with players resting, etc.
James
Will do!
Excel LADZ
Gβday lad! Thanks for the feedback, I reckon youβre right - this is currently a team based model, so player ins & outs are not accounted for. So βmeaninglessβ games where squads change cannot be taken into account when compiling the probabilities. Of course, there is a particular amount of volatility over a short time span - this can explain really good/bad luck over a couple of days. Let me know how it goes lad π
James
I used the model over the last several days of NHL games, and the model is losing more than winning. Might be a bad time of year for the model, as playoff teams are playing down now that they made the playoffs. I will continue to track the model vs games, to see if it evens out over a week or two.
