HFBoards

Go Back   HFBoards > General Hockey Discussion > By The Numbers
Mobile Hockey's Future Become a Sponsor Site Rules Support Forum vBookie Page 2
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

Reply
 
Thread Tools
Old
05-04-2013, 06:51 PM
  #1
River City Rumble
Registered Loser
 
River City Rumble's Avatar
 
Join Date: Mar 2011
Location: Drinkscotch Center
Country: Djibouti
Posts: 816
vCash: 500
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 WL OT G/G GA/G Goals/GAExpected WinsExp. RankRank. Diff
1CHICAGO4836753.12.021.53465346536.8310
2PITTSBURGH48361203.382.481.36290322632.7120
3ANAHEIM48301262.792.41.162527.9063
4MONTREAL48291453.042.581.17829457428.2840
5ST LOUIS48291722.582.381.08403361326.02127
6BOSTON48281462.652.211.19909502328.783-3
7LOS ANGELES48271652.732.381.14705882427.5370
8WASHINGTON48271833.042.711.12177121826.9291
9VANCOUVER48261572.542.41.05833333325.40134
10TORONTO48261753.022.671.13108614227.158-2
11NY RANGERS48261842.622.251.16444444427.955-6
12MINNESOTA48261932.462.60.94615384622.71208
13SAN JOSE48251672.422.331.03862660924.93141
14OTTAWA48251762.332.081.12019230826.8810-4
15DETROIT48241682.542.291.10917030626.6211-4
16COLUMBUS48241772.42.4124.0015-1
17NY ISLANDERS48241772.812.830.99293286223.83170
18WINNIPEG48242132.622.940.89115646321.39246
19PHILADELPHIA48232232.752.90.94827586222.76190
20DALLAS48222242.672.940.90816326521.80222
21PHOENIX48211892.522.60.96923076923.2618-3
22BUFFALO48212162.462.90.84827586220.36253
23NEW JERSEY481919102.292.540.90157480321.64230
24EDMONTON48192272.562.730.93772893822.5121-3
25CAROLINA48192542.653.310.8006042319.21283
26CALGARY48192542.673.270.81651376119.60271
27TAMPA BAY48182643.063.06124.0016-11
28NASHVILLE48162392.272.770.81949458519.6726-2
29COLORADO48162572.383.120.76282051318.31290
30FLORIDA48152762.273.540.64124293815.39300

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.

River City Rumble is offline   Reply With Quote
Old
05-04-2013, 06:54 PM
  #2
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
You might like this paper by Alan Ryder:

http://www.hockeyanalytics.com/Resea...babilities.pdf

Doctor No is offline   Reply With Quote
Old
05-04-2013, 06:55 PM
  #3
River City Rumble
Registered Loser
 
River City Rumble's Avatar
 
Join Date: Mar 2011
Location: Drinkscotch Center
Country: Djibouti
Posts: 816
vCash: 500
hell yeah i will. i knew this was too easy.

River City Rumble is offline   Reply With Quote
Old
05-04-2013, 10:28 PM
  #4
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
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)

Doctor No is offline   Reply With Quote
Old
05-07-2013, 05:37 PM
  #5
SHO NUFF*
 
Join Date: Nov 2006
Location: Las Vegas
Posts: 146
vCash: 500
Quote:
Originally Posted by Taco MacArthur View Post
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?

SHO NUFF* is offline   Reply With Quote
Old
05-07-2013, 07:07 PM
  #6
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
Quote:
Originally Posted by SHO NUFF View Post
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.

Doctor No is offline   Reply With Quote
Old
05-07-2013, 08:42 PM
  #7
SHO NUFF*
 
Join Date: Nov 2006
Location: Las Vegas
Posts: 146
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?

SHO NUFF* is offline   Reply With Quote
Old
05-07-2013, 08:47 PM
  #8
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
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?

Doctor No is offline   Reply With Quote
Old
05-07-2013, 09:22 PM
  #9
SHO NUFF*
 
Join Date: Nov 2006
Location: Las Vegas
Posts: 146
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.

SHO NUFF* is offline   Reply With Quote
Old
05-07-2013, 09:28 PM
  #10
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
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.

Doctor No is offline   Reply With Quote
Old
05-07-2013, 09:43 PM
  #11
Caeldan
Moderator
 
Caeldan's Avatar
 
Join Date: Jun 2008
Country: Canada
Posts: 7,275
vCash: 500
Quote:
Originally Posted by Taco MacArthur View Post
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 %?

Caeldan is online now   Reply With Quote
Old
05-07-2013, 09:46 PM
  #12
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
Quote:
Originally Posted by Caeldan View Post
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.

Your conclusion is correct.

Doctor No is offline   Reply With Quote
Old
05-07-2013, 10:16 PM
  #13
lakai17
Registered User
 
lakai17's Avatar
 
Join Date: Aug 2006
Country: Canada
Posts: 11,062
vCash: 50
I go by knowledge of the game. Coaching systems and goaltending.

lakai17 is offline   Reply With Quote
Old
05-07-2013, 10:26 PM
  #14
Doctor No
Mod Supervisor
Retired?
 
Doctor No's Avatar
 
Join Date: Sep 2005
Posts: 23,966
vCash: 500
Quote:
Originally Posted by lakai17 View Post
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.

Doctor No is offline   Reply With Quote
Old
05-08-2013, 07:47 PM
  #15
n00bxQb
Registered User
 
n00bxQb's Avatar
 
Join Date: Jul 2010
Country: Canada
Posts: 2,082
vCash: 500
Quote:
Originally Posted by Caeldan View Post
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 07:55 PM.
n00bxQb is offline   Reply With Quote
Reply

Forum Jump


Bookmarks

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -5. The time now is 09:39 PM.

monitoring_string = "e4251c93e2ba248d29da988d93bf5144"
Contact Us - HFBoards - Archive - Privacy Statement - Terms of Use - Advertise - Top - AdChoices

vBulletin Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
HFBoards.com is a property of CraveOnline Media, LLC, an Evolve Media, LLC company. 2014 All Rights Reserved.