HFBoards Stupid Spreadsheet Tricks - Expected Wins
 Register FAQ Members List Search Today's Posts Mark Forums Read
 Mobile Hockey's Future Become a Sponsor Site Rules Support Forum vBookie
 Notices Please do not post or solicit links to illegal game streams.

 By The Numbers Hockey Analytics... the Final Frontier. Explore strange new worlds, to seek out new algorithms, to boldly go where no one has gone before.

# Stupid Spreadsheet Tricks - Expected Wins

05-04-2013, 05:51 PM
#1
Megalodan
Registered Loser

Join Date: Mar 2011
Location: Drinkscotch Center
Country:
Posts: 975
vCash: 50
Stupid Spreadsheet Tricks - Expected Wins

Basic facts:
• Hockey teams win games by outscoring their opponent; therefore, a simple indicator of a team's seasonal success is the number of goals scored against goals allowed.
• The NHL's 'goal differential' (goals scored - goals allowed) is misleading because it does not account for the quantities of each, only the difference between them.
• A better indicator is "Goals per Goals Against". This ratio indicates the ability of a team to outscore their opponent on any given night, by comparing goals scored directly to goals allowed.

There are several variations and interesting experiments that can be done with this stat, but my favorite is calculating how many games a team can expect to win with their 'goal ratio'.

If we assume that a team with a 1.00 ratio (exactly one goal scored for every goal allowed) will win exactly half of their games, then we can calculate the number of games a team should have won:
Code:
```Expected Wins  = [ ( Goals For per game) / (Goals Against per game) ] * (# of games in season / 2)

note: whether you use "goals" or "goals per game" does not matter since they are (almost) the same and the denominators will cancel out```
If you run that against the 2013 stats (as well as other seasons) you will get a table like the one below. The teams are ranked by the number of wins they had this season. The expected wins for each team is also calculated, and a second ranking is made based on this stat. A 'differential' indicates how many ranks a club 'unfairly' rose or fell from expectations. The numbers are hauntingly accurate.

Please leave any questions, comments, or criticisms for me. I've done this as a hobby for the last few seasons, and this is the first time I've wrote up anything about. Thanks for reading.

 Rank Team GP W L OT G/G GA/G Goals/GA Expected Wins Exp. Rank Rank. Diff 1 CHICAGO 48 36 7 5 3.1 2.02 1.534653465 36.83 1 0 2 PITTSBURGH 48 36 12 0 3.38 2.48 1.362903226 32.71 2 0 3 ANAHEIM 48 30 12 6 2.79 2.4 1.1625 27.90 6 3 4 MONTREAL 48 29 14 5 3.04 2.58 1.178294574 28.28 4 0 5 ST LOUIS 48 29 17 2 2.58 2.38 1.084033613 26.02 12 7 6 BOSTON 48 28 14 6 2.65 2.21 1.199095023 28.78 3 -3 7 LOS ANGELES 48 27 16 5 2.73 2.38 1.147058824 27.53 7 0 8 WASHINGTON 48 27 18 3 3.04 2.71 1.121771218 26.92 9 1 9 VANCOUVER 48 26 15 7 2.54 2.4 1.058333333 25.40 13 4 10 TORONTO 48 26 17 5 3.02 2.67 1.131086142 27.15 8 -2 11 NY RANGERS 48 26 18 4 2.62 2.25 1.164444444 27.95 5 -6 12 MINNESOTA 48 26 19 3 2.46 2.6 0.946153846 22.71 20 8 13 SAN JOSE 48 25 16 7 2.42 2.33 1.038626609 24.93 14 1 14 OTTAWA 48 25 17 6 2.33 2.08 1.120192308 26.88 10 -4 15 DETROIT 48 24 16 8 2.54 2.29 1.109170306 26.62 11 -4 16 COLUMBUS 48 24 17 7 2.4 2.4 1 24.00 15 -1 17 NY ISLANDERS 48 24 17 7 2.81 2.83 0.992932862 23.83 17 0 18 WINNIPEG 48 24 21 3 2.62 2.94 0.891156463 21.39 24 6 19 PHILADELPHIA 48 23 22 3 2.75 2.9 0.948275862 22.76 19 0 20 DALLAS 48 22 22 4 2.67 2.94 0.908163265 21.80 22 2 21 PHOENIX 48 21 18 9 2.52 2.6 0.969230769 23.26 18 -3 22 BUFFALO 48 21 21 6 2.46 2.9 0.848275862 20.36 25 3 23 NEW JERSEY 48 19 19 10 2.29 2.54 0.901574803 21.64 23 0 24 EDMONTON 48 19 22 7 2.56 2.73 0.937728938 22.51 21 -3 25 CAROLINA 48 19 25 4 2.65 3.31 0.80060423 19.21 28 3 26 CALGARY 48 19 25 4 2.67 3.27 0.816513761 19.60 27 1 27 TAMPA BAY 48 18 26 4 3.06 3.06 1 24.00 16 -11 28 NASHVILLE 48 16 23 9 2.27 2.77 0.819494585 19.67 26 -2 29 COLORADO 48 16 25 7 2.38 3.12 0.762820513 18.31 29 0 30 FLORIDA 48 15 27 6 2.27 3.54 0.641242938 15.39 30 0

The clubs that are closer to a 1.00 goal ratio are more likely to rise/fall in the standings unfairly. I hypothesize that this fluctuation is based on the number of overtime games these clubs play, and their ability to take wins out of them.

 05-04-2013, 05:54 PM #2 Bear of Bad News Mod Supervisor HFBoards Escape Goat     Join Date: Sep 2005 Location: Windsor Posts: 5,526 vCash: 663 You might like this paper by Alan Ryder: http://www.hockeyanalytics.com/Resea...babilities.pdf
 05-04-2013, 05:55 PM #3 Megalodan Registered Loser     Join Date: Mar 2011 Location: Drinkscotch Center Country: Posts: 975 vCash: 50 hell yeah i will. i knew this was too easy.
 05-04-2013, 09:28 PM #4 Bear of Bad News Mod Supervisor HFBoards Escape Goat     Join Date: Sep 2005 Location: Windsor Posts: 5,526 vCash: 663 I figured it'd be right up your alley - when I discovered the Pythagorean Theorem, I bet I spent weeks with that plus Excel. Now I use it on my site to develop "support-neutral" wins and losses for goaltenders, such as: http://hockeygoalies.org/bio/roy.html http://hockeygoalies.org/bio/hasek.html http://hockeygoalies.org/bio/brodeurm.html (under regular season statistics, and postseason statistics, respectively)
05-07-2013, 04:37 PM
#5
SHO NUFF*

Join Date: Nov 2006
Location: Las Vegas
Posts: 145
vCash: 500
Quote:
 Originally Posted by Taco MacArthur You might like this paper by Alan Ryder: http://www.hockeyanalytics.com/Resea...babilities.pdf
In Ryder's pythagorean formula for hockey, he's using an exponent of 1.86. As this paper was written in 2004, he's accounting for ties. How would you adjust this exponent now that ties are no longer a factor?

05-07-2013, 06:07 PM
#6
Mod Supervisor
HFBoards Escape Goat

Join Date: Sep 2005
Location: Windsor
Posts: 5,526
vCash: 663
Quote:
 Originally Posted by SHO NUFF In Ryder's pythagorean formula for hockey, he's using an exponent of 1.86. As this paper was written in 2004, he's accounting for ties. How would you adjust this exponent now that ties are no longer a factor?
I haven't thought about it too much (and I actually just use 2 as my exponent in my work). For what I do (support-neutral goalie stats), I decided that it's preferable to use the standard formula (which assumes that all games are two-point games), because it puts Henrik Lundqvist on an even playing field with Patrick Roy on an even playing field with Jacques Plante.

The most straightforward way that I could think of would be to:
• Remove OT goals (and shootout effects) from the base totals.
• Use those goals and goals against to generate standings assuming no three-point games.
• To estimate the allocation of "third points" (from OT and SO) fit a regression model against OT/SO goals.
• Put it all back together.

Could be an interesting project for someone with a few hours to kill and an Excel spreadsheet.

 05-07-2013, 07:42 PM #7 SHO NUFF*   Join Date: Nov 2006 Location: Las Vegas Posts: 145 vCash: 500 Ryder's using an exponent under 2 to account for ties because it reduces the expected winning percentage for a given ratio of GF to GA in the formula. With the elimination of ties, the exponent should now be adjusted to a higher number. It would seem at the very least that 2 becomes a more accurate figure. Do you think if one were to crunch the numbers it would be over 2?
 05-07-2013, 07:47 PM #8 Bear of Bad News Mod Supervisor HFBoards Escape Goat     Join Date: Sep 2005 Location: Windsor Posts: 5,526 vCash: 663 Well, first things first - with three-point games involved, the formula won't work at all (regardless of exponent chosen), since for a team with equal GF and GA, the formula will always project a winning percentage of 50%. The question then becomes - once you adjust the formula, what happens to the optimal exponent?
 05-07-2013, 08:22 PM #9 SHO NUFF*   Join Date: Nov 2006 Location: Las Vegas Posts: 145 vCash: 500 I'm interested in more of a game by game basis than a season long win/point expectation. I 'm trying to determine the most accurate Pythagorean Win Percentage based on my scoring estimates for each team on a per game basis.
 05-07-2013, 08:28 PM #10 Bear of Bad News Mod Supervisor HFBoards Escape Goat     Join Date: Sep 2005 Location: Windsor Posts: 5,526 vCash: 663 For single-game win probabilities, I'd start with something like Bill James' Log5 formulas: http://www.chancesis.com/2010/10/03/...igins-of-log5/ You'll have to make similar hockey modifications, since this was developed initially for baseball.
05-07-2013, 08:43 PM
#11
Caeldan
Moderator
Whippet Whisperer

Join Date: Jun 2008
Country:
Posts: 13,544
vCash: 100
Quote:
 Originally Posted by Taco MacArthur Well, first things first - with three-point games involved, the formula won't work at all (regardless of exponent chosen), since for a team with equal GF and GA, the formula will always project a winning percentage of 50%. The question then becomes - once you adjust the formula, what happens to the optimal exponent?
Just random idea... would it be possible to convert to points % rather than win %?

Then I *think* an equal GF and GA would end up around 56% in terms of points %?

05-07-2013, 08:46 PM
#12
Mod Supervisor
HFBoards Escape Goat

Join Date: Sep 2005
Location: Windsor
Posts: 5,526
vCash: 663
Quote:
 Originally Posted by Caeldan Just random idea... would it be possible to convert to points % rather than win %? Then I *think* an equal GF and GA would end up around 56% in terms of points %?
I'm using "winning percentage" and "points percentage" interchangeably, which is a bad habit of mine when talking hockey.

 05-07-2013, 09:16 PM #13 lakai17 Registered User     Join Date: Aug 2006 Country: Posts: 16,789 vCash: 50 I go by knowledge of the game. Coaching systems and goaltending.
05-07-2013, 09:26 PM
#14
Mod Supervisor
HFBoards Escape Goat

Join Date: Sep 2005
Location: Windsor
Posts: 5,526
vCash: 663
Quote:
 Originally Posted by lakai17 I go by knowledge of the game. Coaching systems and goaltending.
Completely irrelevant to the thread, unless you choose to tie it in to numbers somehow.

05-08-2013, 06:47 PM
#15
n00bxQb
Registered User

Join Date: Jul 2010
Country:
Posts: 2,511
vCash: 500
Quote:
 Originally Posted by Caeldan Just random idea... would it be possible to convert to points % rather than win %? Then I *think* an equal GF and GA would end up around 56% in terms of points %?
Over the past 5 seasons, a game goes to OT/SO 23.794% of the time (1342 times in 5640 games)

Average points for regulation = 1 (2 points for win, 0 points for loss. 2/2 = 1)
Average points for overtime/shootout = 1.5 (2 points for win, 1 point for loss. 3/2 = 1.5)

So you could break it down to 0.76206*GP*(GF/GA)+0.23794*GP*(GF/GA)*1.5.

With an even goal differential, it calculates to 0.76206*82*1+0.23794*82*1*1.5 = 91.756 points or a 55.949% Point Percentage.

Great guess w/ 56%

Last edited by n00bxQb: 05-08-2013 at 06:55 PM.

Forum Jump