Toby's FHM Advanced Analytics Tool is here!

[TobyInHR]

https://youtu.be/K_HyhVTZv2U

Very excited to finally be able to share this project with the community. For the last 3 and a half months, I have been developing a "companion" spreadsheet to our favorite spreadsheet hockey video game, and I feel like it is finally at a point where it's ready for everyone to access. This video will go over how to set up the Spreadsheet for your own save, and the basics of using it to analyze players in your league.

The heavy lifting is done by a series of linear regression models to provide projected stats for teams and players, including Expected Points for teams, expected goals and goals against for players, and a replacement-level calculation, allowing for stats like WAR, GAR, and SPAR, similar to Evolving Hockey.

All of this is done solely with in-game data, meaning you only need to import a few CSVs, and the stats that are created will be accurate to your save.

Please let me know if you run into any issues. Currently, the Sheet is only designed to work for standard NHL leagues. I do plan to have a version compatible with custom leagues eventually, but the foundation needs to be laid first in order to get to that point. There is also, unfortunately, a limitation to Google Sheets that may present an issue: projects cannot exceed 10 million cells. This sounds like a ton, but the reality is that FHM CSVs export a ton of information for each league you select when starting your save. As a result, if you have every league selected, you will likely exceed the cell limit while importing your data. To fix this, open the CSVs in Excel, and delete information unrelated to your league. This can be a little daunting, so feel free to reach out to me if you need assistance. In the meantime, an awesome community member is working on a lightweight tool to clean up your CSVs, reducing them to just the necessary data. When that is ready, I'll be sure to include a link to it in the spreadsheet.

If you want to skip the video, go straight to the link in the description to access the Sheet, along with the text instructions on how to set it up.

Enjoy!

EDIT: 12/11/2022 - new version of Spreadsheet (v1.23) available at this link: https://docs.google.com/spreadsheets...it?usp=sharing
Features in v1.23: rebalancing of GAR and WAR models to provide much more accurate statistics for replacement-level comparisons, and ensure there is a higher concentration of players around 0.0 GAR/WAR, rather than 0.0 being a floor for league players.

EDIT: 12/13/2022 - https://youtu.be/1MM2KIk1XE0

A few people have requested this video, and I think it's a great idea. This covers my own personal process for looking at how my players did based on their advanced stats in the offseason, and how I go about narrowing the search for potential trade targets to improve my roster. This specific video will show my thought process heading into the draft, when the free agency pool and trade block have the most options available. In the future, I'll do a similar video when my team is having a difficult time being competitive in the regular season, and how I assess the problem and look to solve it.

EDIT: 1/4/2023 - Major updates to my advanced stat tracker! A new webapp is available to help clean your CSVs prior to loading them, which entirely eliminates the slow loading and occasional freezing of the current Sheet. This is thanks to my switch to using R as my data manipulation program, rather than sheets, which has also provided me with a collection of tools for much more effective modeling for xG, GAR, and WAR! Now, a script will do all the calculations, and export a few CSVs that can be put into Sheets, taking the labor off of Google and improving performance.

Second, I have made the necessary changes to allow for expansion teams to be added! Instructions are in the video. Thanks to the FHM team for the most recent update, which added in a bunch of crazy useful stats! Please use this video to see how to use the new tools, as well as how to add expansion teams. For those interested, I also cover my modeling process later in the video.

https://youtu.be/1TSe41OWWwI

[jcard]

Tremendous work, generous undertaking. I was just thinking today how lacking the game is in stats that give you actionable feedback, particularly as it pertains to roster and lineup on construction decisions- and the actual impact of the game engine systems. Out of curiosity, do you know if the data that can be pulled is tagged by either (1) team / unit /player tactic or (2) player tactical role (To be clear- I am NOT requesting that you add anything to your work, just wondering if that data was available for export).

Once more, a sincere Thank You.

[TobyInHR]

Unfortunately, I don't think there's a way to see tactics in the exported data. That is a great idea though, and could definitely be helpful.

My biggest hope is that this project gains enough traction to warrant an update to the CSVs -- ideally, we could eventually get stats for zone starts (which are in the game, but are not in the spreadsheets), shot location (even just high-danger, low danger), individual Corsi numbers, hits against, and nearly everything else that the real NHL tracks with RTSS data. If that is possible, then the advanced metrics could 100% mirror real life.

[reverist]

Originally Posted by TobyInHR

https://youtu.be/K_HyhVTZv2U

Very excited to finally be able to share this project with the community. For the last 3 and a half months, I have been developing a "companion" spreadsheet to our favorite spreadsheet hockey video game, and I feel like it is finally at a point where it's ready for everyone to access. This video will go over how to set up the Spreadsheet for your own save, and the basics of using it to analyze players in your league.

The heavy lifting is done by a series of linear regression models to provide projected stats for teams and players, including Expected Points for teams, expected goals and goals against for players, and a replacement-level calculation, allowing for stats like WAR, GAR, and SPAR, similar to Evolving Hockey.

All of this is done solely with in-game data, meaning you only need to import a few CSVs, and the stats that are created will be accurate to your save.

Please let me know if you run into any issues. Currently, the Sheet is only designed to work for standard NHL leagues. I do plan to have a version compatible with custom leagues eventually, but the foundation needs to be laid first in order to get to that point. There is also, unfortunately, a limitation to Google Sheets that may present an issue: projects cannot exceed 10 million cells. This sounds like a ton, but the reality is that FHM CSVs export a ton of information for each league you select when starting your save. As a result, if you have every league selected, you will likely exceed the cell limit while importing your data. To fix this, open the CSVs in Excel, and delete information unrelated to your league. This can be a little daunting, so feel free to reach out to me if you need assistance. In the meantime, an awesome community member is working on a lightweight tool to clean up your CSVs, reducing them to just the necessary data. When that is ready, I'll be sure to include a link to it in the spreadsheet.

If you want to skip the video, go straight to the link in the description to access the Sheet, along with the text instructions on how to set it up.

Enjoy!

Thanks so much for this! Send my regards to Tibbs.

[JeffR]

Thanks for doing this - when I get back from vacation I'll take a closer look and we'll see what extra stuff we can add to the csv's - adding more things to them is on the to-do list, but there's not much definition of exactly what we will/can add yet.

[landru22]

Something I'd like to see added to the csv is a column containing the team a player has played for in that particular game in the BoxScore csv.

I've made a rudimentary BoxScore creator and if a player moves to another team at some point during the season the BoxScore will show his current team instead of the team he played for that game.

[tomkmb]

This is really fantastic, thanks for taking the time to create and share it.

[TobyInHR]

Enjoy your vacation Jeff!

The biggest additions to what the CSV exports would be:

- Simple shot location data: to make it simple, just seeing stats for high-danger shot attempts and low-danger shot attempts would work (that is, shot attempts from the slot, and from outside the slot).

- Types of goals: not sure if this is tracked in the game data, but whether a goal was scored off a rebound, deflection, or clean.

- Individual shot attempts/shot attempts against: this one is major. We get Corsi For, which is awesome, but having the data for each player would allow for Regularized Adjusted Plus Minus calculations, which is my *dream* lol.

- Zone Starts: this data is in the game, so I'm not sure why it doesn't show up in the CSVs. But it's an important metric when weighting CF and CA, since players with more DZ starts will likely have an inflated CA, and vice versa.

- Hits against: how many times a player gets hit provides insight into his possession skills, and possession is fundamental to WAR.

I won't pretend to know how easy or difficult it is to add each of these, but it at least provides an idea of what might be out there that isn't currently included in the CSVs. Also, an option to limit the CSV data to the league that contains a player-controlled team would be massive, assuming that would be easier than creating a new interface to select which leagues are and are not exported; instead, there are just two options: "Export World" and "Export League." Especially if the league option does not export retired players haha.

Please hit me up if you have any questions, suggestions, or information that may be of use! I have no plans to stop working on this any time soon, and the more big brains working on it together, the better.

[jcard]

Originally Posted by TobyInHR

Enjoy your vacation Jeff!

The biggest additions to what the CSV exports would be:

- Simple shot location data: to make it simple, just seeing stats for high-danger shot attempts and low-danger shot attempts would work (that is, shot attempts from the slot, and from outside the slot).

- Types of goals: not sure if this is tracked in the game data, but whether a goal was scored off a rebound, deflection, or clean.

- Individual shot attempts/shot attempts against: this one is major. We get Corsi For, which is awesome, but having the data for each player would allow for Regularized Adjusted Plus Minus calculations, which is my *dream* lol.

- Zone Starts: this data is in the game, so I'm not sure why it doesn't show up in the CSVs. But it's an important metric when weighting CF and CA, since players with more DZ starts will likely have an inflated CA, and vice versa.

- Hits against: how many times a player gets hit provides insight into his possession skills, and possession is fundamental to WAR.

I won't pretend to know how easy or difficult it is to add each of these, but it at least provides an idea of what might be out there that isn't currently included in the CSVs. Also, an option to limit the CSV data to the league that contains a player-controlled team would be massive, assuming that would be easier than creating a new interface to select which leagues are and are not exported; instead, there are just two options: "Export World" and "Export League." Especially if the league option does not export retired players haha.

Please hit me up if you have any questions, suggestions, or information that may be of use! I have no plans to stop working on this any time soon, and the more big brains working on it together, the better.

If these boards had an advanced stat measuring Contribution Per Post, well suffice it to say I would propose-a la Corsi, etc.- calling Toby.

[drummerJ99]

Sounds interesting..doing a custom game right now so this wont work.

But If I decide to do a standard NHL one in the near future is it best to use it on google sheets or export and download as a ecel file? Have always had excel so don't know much about google sheets.

[JeffR]

Originally Posted by TobyInHR

Enjoy your vacation Jeff!

Will do (see attached).

I think most of those requests will be doable, I'll ask Sebastian to have a look and see what issues there might be with recording the ones we don't do now. I know we'll have to check a couple of things to make sure they can be handled by both the 2D and classic engines, e.g. deflected shots are explicitly handled in 2D, but I think there may be a level of abstraction in the old engine that might make them difficult to record. Could be wrong, though.

[tomkmb]

Originally Posted by JeffR

Will do (see attached).

I think most of those requests will be doable, I'll ask Sebastian to have a look and see what issues there might be with recording the ones we don't do now. I know we'll have to check a couple of things to make sure they can be handled by both the 2D and classic engines, e.g. deflected shots are explicitly handled in 2D, but I think there may be a level of abstraction in the old engine that might make them difficult to record. Could be wrong, though.

Where else in the world would you rather be.........whilst thinking about CSVs?

[fredbeene]

Originally Posted by TobyInHR

https://youtu.be/K_HyhVTZv2U

Very excited to finally be able to share this project with the community. For the last 3 and a half months, I have been developing a "companion" spreadsheet to our favorite spreadsheet hockey video game, and I feel like it is finally at a point where it's ready for everyone to access. This video will go over how to set up the Spreadsheet for your own save, and the basics of using it to analyze players in your league.

The heavy lifting is done by a series of linear regression models to provide projected stats for teams and players, including Expected Points for teams, expected goals and goals against for players, and a replacement-level calculation, allowing for stats like WAR, GAR, and SPAR, similar to Evolving Hockey.

All of this is done solely with in-game data, meaning you only need to import a few CSVs, and the stats that are created will be accurate to your save.

Please let me know if you run into any issues. Currently, the Sheet is only designed to work for standard NHL leagues. I do plan to have a version compatible with custom leagues eventually, but the foundation needs to be laid first in order to get to that point. There is also, unfortunately, a limitation to Google Sheets that may present an issue: projects cannot exceed 10 million cells. This sounds like a ton, but the reality is that FHM CSVs export a ton of information for each league you select when starting your save. As a result, if you have every league selected, you will likely exceed the cell limit while importing your data. To fix this, open the CSVs in Excel, and delete information unrelated to your league. This can be a little daunting, so feel free to reach out to me if you need assistance. In the meantime, an awesome community member is working on a lightweight tool to clean up your CSVs, reducing them to just the necessary data. When that is ready, I'll be sure to include a link to it in the spreadsheet.

If you want to skip the video, go straight to the link in the description to access the Sheet, along with the text instructions on how to set it up.

Enjoy!

WOW. YOU should be on the dev team.
You are a savior.

OOTPB is barebone stats for what is supposed to be a stats based game.

I hope they recognize your talent and incorporate your product into the game.

I

[fredbeene]

Originally Posted by TobyInHR

Enjoy your vacation Jeff!

The biggest additions to what the CSV exports would be:

- Simple shot location data: to make it simple, just seeing stats for high-danger shot attempts and low-danger shot attempts would work (that is, shot attempts from the slot, and from outside the slot).

- Types of goals: not sure if this is tracked in the game data, but whether a goal was scored off a rebound, deflection, or clean.

- Individual shot attempts/shot attempts against: this one is major. We get Corsi For, which is awesome, but having the data for each player would allow for Regularized Adjusted Plus Minus calculations, which is my *dream* lol.

- Zone Starts: this data is in the game, so I'm not sure why it doesn't show up in the CSVs. But it's an important metric when weighting CF and CA, since players with more DZ starts will likely have an inflated CA, and vice versa.

- Hits against: how many times a player gets hit provides insight into his possession skills, and possession is fundamental to WAR.

I won't pretend to know how easy or difficult it is to add each of these, but it at least provides an idea of what might be out there that isn't currently included in the CSVs. Also, an option to limit the CSV data to the league that contains a player-controlled team would be massive, assuming that would be easier than creating a new interface to select which leagues are and are not exported; instead, there are just two options: "Export World" and "Export League." Especially if the league option does not export retired players haha.

Please hit me up if you have any questions, suggestions, or information that may be of use! I have no plans to stop working on this any time soon, and the more big brains working on it together, the better.

OMG....this is better than expected and MY WISH LIST!!!
FHM takes is fan base seriously

OOTPB still stuck with 20 year old reports and no intent on improving..

Show them how it is done FHM!

[TobyInHR]

Originally Posted by drummerJ99

Sounds interesting..doing a custom game right now so this wont work.

But If I decide to do a standard NHL one in the near future is it best to use it on google sheets or export and download as a ecel file? Have always had excel so don't know much about google sheets.

Unfortunately, Excel and Sheets use *similar* formulas, but not *identical* ones, so there is a large amount of editing that would need to take place once you transfer the Google Sheet to Excel. But it's certainly possible, and if someone takes on the work of creating an Excel sheet, I'd be happy to help maintain it so both options exist.

Regarding custom leagues: currently, the sheet is not set up for custom leagues, however, it can certainly be done. Sometimes with very minimal editing -- it all just depends on how your TeamIDs are setup. TeamID is used to isolate the relevant league to the NHLStats tab. By default, NHL teams are IDs 0-35, 4838 (SEA), and 4182 (VGK). This means I can use a filter formula to pull stats where TeamID<36, then have a separate tab where I pull stats where TeamID = 4838 and 4182.

These tabs are necessary because they allow me to find replacement level players for a specific league without relying on hundreds of thousands of "If-then" functions, which result in *significant* lag.

The Sheet can be adapted to custom leagues as long as you are able to easily isolate your teams by ID. Once things are stable with the base sheet, I'd like to work with some folks who do custom leagues so that I can see how the CSVs tend to look, then make a version of the Sheet that is easy to set up for the non-standard saves.

[drummerJ99]

Originally Posted by TobyInHR

Unfortunately, Excel and Sheets use *similar* formulas, but not *identical* ones, so there is a large amount of editing that would need to take place once you transfer the Google Sheet to Excel. But it's certainly possible, and if someone takes on the work of creating an Excel sheet, I'd be happy to help maintain it so both options exist.

Regarding custom leagues: currently, the sheet is not set up for custom leagues, however, it can certainly be done. Sometimes with very minimal editing -- it all just depends on how your TeamIDs are setup. TeamID is used to isolate the relevant league to the NHLStats tab. By default, NHL teams are IDs 0-35, 4838 (SEA), and 4182 (VGK). This means I can use a filter formula to pull stats where TeamID<36, then have a separate tab where I pull stats where TeamID = 4838 and 4182.

These tabs are necessary because they allow me to find replacement level players for a specific league without relying on hundreds of thousands of "If-then" functions, which result in *significant* lag.

The Sheet can be adapted to custom leagues as long as you are able to easily isolate your teams by ID. Once things are stable with the base sheet, I'd like to work with some folks who do custom leagues so that I can see how the CSVs tend to look, then make a version of the Sheet that is easy to set up for the non-standard saves.

Sounds good. I tried it on excel and didn't work well. LOL So messed around with it little bit on google sheets with my 24 team WHA league. And it kinda worked...once I figured out what team ID the NHL team was compared to my WHA team.



But I'd be more then willing to be a guinea pig when it comes to custom leagues.

[reverist]

I have a quick question: I know you said wait until you have played 20 games, but I went ahead and did it after my team had done six games. I figured the top line guys would have 60 minutes by now. But on the expected goals and similar, I get the "VALUE" message. Is it because it needs to be 60 even strength minutes? Scratch that. I see their TOI is literally 58-59 mins. My bad. Awesome tool, I love it so far!

[TobyInHR]

Originally Posted by reverist

I have a quick question: I know you said wait until you have played 20 games, but I went ahead and did it after my team had done six games. I figured the top line guys would have 60 minutes by now. But on the expected goals and similar, I get the "VALUE" message. Is it because it needs to be 60 even strength minutes? Scratch that. I see their TOI is literally 58-59 mins. My bad. Awesome tool, I love it so far!

Yeah, so that's a big reason why you have to play around 20 games in. In order for players to get considered in the regression model, they have to have played 60 minutes. The regression model requires a minimum of roughly 300 forwards and 200 defensemen. If there aren't enough players with 60 minutes played to hit that threshold, you'll see the VALUE error for any stats that require the regression models.

[reverist]

Originally Posted by TobyInHR

Yeah, so that's a big reason why you have to play around 20 games in. In order for players to get considered in the regression model, they have to have played 60 minutes. The regression model requires a minimum of roughly 300 forwards and 200 defensemen. If there aren't enough players with 60 minutes played to hit that threshold, you'll see the VALUE error for any stats that require the regression models.

Understood. Still, what I have is eye-opening. Seeing the luckiest and unluckiest teams, I would have intuitively said I was on the unlucky side. So far, I'm about at expected points. Similarly, I love seeing the WAR and seeing lines/pairings and how they are contributing.

[Infinite_Jester54]

This is so cool! Thanks so much for your hard work!