Trying to do a Hockey Standings sheet...

hockeyfan1985

New Member
Joined
Apr 6, 2018
Messages
11
Hello, I was seeing if anyone would know any formulas or how to do a last ten games formula. Like a win-loss-ot loss. Also need a formula for a win/loss/ot streaks. Also if you have any other formulas please list for standings sheet. I have a sheet for the schedule and a 2nd for standings. I know my basics but just looking for some of the tougher ones.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,504
Office Version
  1. 365
Platform
  1. Windows
I have a detailed model of the NHL. It works by using PowerQuery to download the scores from a website for all games (1271 for the 2018 season) into an Excel Table. Once it's downloaded, the entire NHL schedule can be used as the source data for every type of automated calculation imaginable, including all those streaks and the standings.

Do you know how to use PQ?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,504
Office Version
  1. 365
Platform
  1. Windows
OK. Let's begin. We'll have to do this in small steps that work with my layout, otherwise I'll become all confused and my head will ache.

Create a sheet called Q_Data. Create a blank query called Q_Scores_18_HR and paste this MCode into the blank query. 'Close and Load to' and put it in Q_Data in cell A40. Your table should end up in cells A40 to H1311.

Then we will name this as an ExcelTable T_Scr and begin to add many columns to that ExcelTable to create more essential data.

let
Source = Web.Page(Web.Contents("http://www.hockey-reference.com/leagues/NHL_2018_games.html")),
Data1 = Source{1}[Data],
#"Changed Type2" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Att.", "LOG", "Notes"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Visitor", "Home", "G", "G2", ""}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"G", "VScr"}, {"G2", "HScr"}, {"", "type"}, {"Home", "Host"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [VScr] = null then null else if [type] = "SO" then "S/O" else if [type] <> null then [type] else "R" ),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Result"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"type"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Scoreline", each [Visitor] &" " & [VScr] &" - " & [Host] &" " & [HScr] &" "&[Result]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"VScr", Int64.Type}, {"HScr", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Index", "Gm"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Result", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each true)
in
#"Filtered Rows"
 
Last edited:

hockeyfan1985

New Member
Joined
Apr 6, 2018
Messages
11
Thank you, I'm just not understanding that.
 

Forum statistics

Threads
1,171,648
Messages
5,876,652
Members
433,205
Latest member
jabin1991

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top