ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Toby's Tremendous Tracker for Franchise Hockey Manager
2
Thank you for checking out this labor of love! My goal with this data analysis tool is to provide you with some of the modern advanced hockey stats availabile in the real NHL. Below, you will find a brief description of each stat. If you have any suggestions, please send them to me via Discord. You can find me, as well as information about this sheet and development process, in the OOTP Discord Server, FHM room.
3
4
One of my primary goals with this tool is to make it accessible with very little user-effort. As such, the Sheet is only designed to be compatible "out of the box" with standard NHL saves, but if you have a general understanding of the formulas used, you should be able to tweak them to analyze another league. After the foundation of this project has been laid, I plan to make it easy to adapt to custom leagues.
5
HOW TO USE:
6
Step 1: When you get to a point where you'd like to utilize this spreadsheet, in FHM click World > Export CSV. I recommend simming at least 20 games, and always exporting at the end of the regular season.
7
Step 2: Navigate to your FHM Saved Games folder (By default it is located in Documents\Out Of the Park Developments\Franchise Hockey Manager 9\saved_games
8
Step 3: Locate the folder for the save you just exported and open it, then open the folder called import_export
9
Step 4 (optional): Create a new folder named for the season you just exported (2022-23, for example). I do this to keep the CSV folder clean and organized, and to retain data from prior years.
10
Step 5: On this spreadsheet, click file > Save a copy. This will allow you to have your own copy of the spreadsheet to edit.
11
Step 6: Import each CSV to the matching tab listed below.
12
IMPORTANT! On every page, follow these steps to import your data: Select the top left cell (A1). Click File > Import > Upload. Drag the corresponding file into the window. Under "Import Location" select "Replace Data at Selected Cell." Under "Separator Type" select "Custom" and type a semicolon into the Custom Separator box that appears. You can leave the "Convert text to numbers, dates, and formulas" box checked. Click Import Data.
13
Tab NameCSV
14
TeamStatsteam_stats.csv
15
TeamRecordsteam_records.csv
16
TeamLinesteam_lines.csv
17
PlayerInfoplayer_master.csv
18
SkaterStatsplayer_skater_stats_rs.csvMake sure to grab the "rs" stats file, not the playoffs or preseason CSV.
19
PlayerRatingsplayer_ratings.csv
20
The following sheets are hidden, but do not require any action on your part: NHLStats, VGK and SEA Stats, Active Roster Stats, and Utility Page.
21
Sometimes, the page will stop responding when compiling all the data. If this happens, just click "leave" on the popup. Refresh the page, and you should be good to go. Sheets seems to freeze after the data is imported, but before it can be displayed.After you have one Sheet compiled with all your data, you can just repeat step 6 for any future exports (replace data at selected cell, do not combine numbers). However, if you wish to use a newer version of the spreadsheet, you will need to save a new blank copy.
22
23
Using the Advanced Metrics page
24
This is the main hub of this project. From here, you can select a team from the dropdown to analyze players on their active roster as of the in-game date of your export (injured players or players on the AHL team will not be accessible).

There is also a small amount of league-wide data displayed on this page, regardless of the team selected, including Lucky/Unlucky Teams, and league-leaders in WAR and xG, both based on position (FWD or DEF). 		Luckiest/Unluckiest Teams - This tool uses the data from your league to determine how many goals are equal to a single standing point, estimates how many points each team should have, based on goals scored, then finds the teams with the widest margins above or below expected.

Player Comparison - This tool allows you to select 3 players from the active roster and view their Box Score, GAR/WAR, and On Ice stats. These categories attempt to mirror the tools provided by Evolving Hockey for current NHL stat analysis.

Box Score stats are the quantifiable, standard stats for each skater.
GAR/WAR stats are the expected and actual stats of a player compared to a replacement level player, which is explained below.
On Ice stats are the stats of the selected team when the selected player is on the ice.

Line Comparison - This tool allows you to view each of the lines of any NHL team as of the in-game day you exported your CSV. This means there may be empty positions, or players replaced due to injury. Note: any On Ice stats are averages between the three players on that line, while any Box Score stats are a summed total of all 3 players.



25
Advanced Stats explained
26
First, know that this is just an attempt to quanitfy player value based on certain offensive and defensive stats. This is, by no means, intended to be a perfect recreation of advanced stats in hockey today. However, much of the inspiration behind the presentation and models used come from Evolving Hockey. Please show them some support if this kind of thing interests you. If you have suggestions on how to improve the data used, or if there is additional data that you would like to see, please let me know on Discord or Reddit.
27
Stat NameFormulaDescription
28
GAR
(Goals Above Replacement for Even Strength Offense)		(Even Strength Goals) - (replacement level player's even strength goals)Goals Above Replacement (GAR) attempts to identify how many goals an easily-available player to every NHL team can score for any team that season. The 23rd percentile is currently used in hockey as the replacement level. This means a player must score more goals than the bottom 23% of players in the NHL to have a GAR of 0. Every goal above 0 is a goal that a player scored above the replacement-level player. This stat is now dependent on a player's position (a replacement-level forward usually scores more goals than a replacement-level defenseman; this is considered when determining GAR).

A player must have played 60 minutes in the NHL for his goals to be part of the equation, which is why I recommend simming at least 20 games into a season before using this tool.

Your league's replacement level is determined by taking the 23rd percentile of offensive metrics and run through a linear regression model to determine the xG for a player with those statistics.
29
WAR (Wins Above Replacement, per 60 minutes TOI)(G/60 - GF/60) / (Ratio of Goals to Wins)The same linear regression data as above is used to determine the number of goals per 60 minutes a replacement player is expected to have, minus the goals he is expected to give up, and divided by the number of goals that equal 1 win in your league. That number is then subtracted from the actual G/60-GA/60 of every player to determine how many more wins they contribute to their team per 60 minutes played.
30
xG, xGF, xGA (expected goals, goals for, and goals against)(Player's key offensive/defensive metrics) * (linear regression coefficients)Using the above linear regression model, this spreadsheet will examine every player's TOI, SOG, CF, CA, SB, Hits, Takeaways, and a number of other stats, then compare each number to the total goals scored by each player, providing an estimate of how many goals or goals against a player is expected to have as of that moment, based on the entire NHL.
31
xWAR (expected WAR, per 60 minutes TOI)(xG/60 - xGA/60) / (ratio of goals to wins)The same calculation for WAR is performed, but this time, it uses xG and xGA, to provide the number of wins a player should have contributed per 60 minutes of TOI.
32
TIBBS% (Takeaways Increased By Blocked Shots)(Takeaways/60) + (iSB/60) / (Corsi against/60) + (on ice SB/60)

Expressed as a percentage		This metric combines a player's individual takeaways and his own blocked shots, divided by the player's Corsi Against (all shot attempts taken by the opposing team while the player is on the ice) plus his team's blocked shots/60 while he's on the ice. The goal of this stat is to provide a quantifiable defensive contribution by a player: the percent of offensive possessions that are disrupted when he's on the ice.

An imperfect measurement, as we cannot see how many takeaways occur while a player is on the ice, only his own takeaways, but it's a start. The theory is that a CA shot is unblocked when it's taken -- it's not blocked until, well, it's blocked. So a player who allows a shot, regardless of whether it gets blocked, still allowed the shot. However, if the shot is blocked, or if the player records a takeaway, we can generally assume it ended the opposing team's possession, or at the very least, disrupted it.

Also, Tibbs is my cat's name, and he insists on being involved in everything I do, one way or another.
33
Update History:
- 12/2/2022: Fixes to player position issues that would result in certain stats not displaying properly. Adjustments to WAR and SPAR calculation to normalize TOI between replacement player and selected player. Fixes to league leaders in xG.

-12/10/2022: v1.23 now available! https://docs.google.com/spreadsheets/d/1f0GEi40VxQV4AqYLXZrWoJRNtjToHJrGlvw7yxXFcn8/edit?usp=sharing
34
35