Website data scraping

[DRSteele]

Can someone please help me?

I am trying to use PQ to get the data from this website into Excel. There are no Tables shown in the navigator.

 

[smozgur]

I'll check the other link. I am not sure if it works with XHR/Fetch or not.

Forgot to ask what you need from the new web page. The entire table?

 

[DRSteele]

Forgot to ask what you need from the new web page. The entire table?

Yes, just the entire standings table.

 

[smozgur]

@DRSteele: See if the following M code for the new link helps.

I tried to keep the code clean as much as possible and preserve the applied steps. It is not the best JSON I have seen but not too bad. It just requires browsing to extract the data you need. As long as you keep the fnOverallRecords function at the top (that I use for creating the dash-separated strings from the overall scores), steps will be visible and you can debug the process.

Note: There is a season query parameter in the URL which is currently season=20222023 that I believe specifies the season 2022-2023. It could be used to request different seasons.

let

    fnOverallRecords = (Source) => let
        ConvertedTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumns = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"wins", "losses", "ot"}),
        MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ExpandedColumns, {{"wins", type text}, {"losses", type text}, {"ot", type text}}, "en-US"),{"wins", "losses", "ot"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
        TransposedTable = Table.Transpose(MergedColumns),
        Result = Table.RenameColumns(TransposedTable,{{"Column1", "home"}, {"Column2", "away"}, {"Column3", "shootOuts"}, {"Column4", "lastTen"}})   
    in
        Result,

    Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/standings?hydrate=record(overall),division,conference,team(nextSchedule(team),previousSchedule(team))&season=20222023&site=en_nhl")),
    ConvertedTable = Table.FromList(Source[records], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"standingsType", "league", "division", "conference", "season", "teamRecords"}),
    RemovedOtherColumns = Table.SelectColumns(ExpandedColumn1,{"teamRecords"}),
    ExpandedListColumn = Table.ExpandListColumn(RemovedOtherColumns, "teamRecords"),
    ExpandedRecordColumn = Table.ExpandRecordColumn(ExpandedListColumn, "teamRecords", {"team", "gamesPlayed",  "leagueRecord", "regulationWins", "goalsAgainst", "goalsScored", "points", "row", "streak", "pointsPercentage", "records"}),
    ExpandedRecordColumnTeam = Table.ExpandRecordColumn(ExpandedRecordColumn, "team", {"locationName"}),
    ExpandedRecordColumnLeague = Table.ExpandRecordColumn(ExpandedRecordColumnTeam, "leagueRecord", {"wins", "losses", "ot"}),
    ExpandedRecordColumnTeamRecords = Table.ExpandRecordColumn(ExpandedRecordColumnLeague, "records", {"overallRecords"}, {"overallRecords"}),
    ExpandedRecordColumnTeamStreak = Table.ExpandRecordColumn(ExpandedRecordColumnTeamRecords, "streak", {"streakCode"}),
    AddColumnOverallRecords = Table.AddColumn(ExpandedRecordColumnTeamStreak, "Custom", each fnOverallRecords(_[overallRecords])),
    ExpandedColumnOverallRecords = Table.ExpandTableColumn(AddColumnOverallRecords, "Custom", {"home", "away", "shootOuts", "lastTen"}),
    RemovedColumns = Table.RemoveColumns(ExpandedColumnOverallRecords,{"overallRecords"}),
    SortedByPoints = Table.Sort(RemovedColumns,{{"points", Order.Descending}}),
    AddedColumnDiff = Table.AddColumn(SortedByPoints, "diff", each [goalsScored] - [goalsAgainst]),
    ReorderedColumns = Table.ReorderColumns(AddedColumnDiff,{"locationName", "gamesPlayed", "wins", "losses", "ot", "points", "pointsPercentage", "regulationWins", "row", "goalsScored", "goalsAgainst", "diff", "home", "away", "shootOuts", "lastTen", "streakCode"}),
    Result = Table.RenameColumns(ReorderedColumns,{{"locationName", "National Hockey League"}, {"gamesPlayed", "GP"}, {"wins", "W"}, {"losses", "L"}, {"ot", "OT"}, {"points", "PTS"}, {"pointsPercentage", "P%"}, {"regulationWins", "RW"}, {"row", "ROW"}, {"goalsScored", "GF"}, {"goalsAgainst", "GA"}, {"diff", "DIFF"}, {"home", "HOME"}, {"away", "AWAY"}, {"shootOuts", "S/O"}, {"lastTen", "L10K"}, {"streakCode", "STRK"}})
in
    Result

 

[DRSteele]

Suat, thanks immensely for all your work.

I have as much chance of ever understanding all that as I do of playing in the NHL. Your skill and knowledge here are amazing. Wow.

But of course, there is a problem. It has to do with the order the NHL lists the Overall Standings. The NHL has tiebreaker rules which they apply. Then they list the teams in that order on that very webpage. So, our Query here is not sorted in the same order.

The reason I need to get the data from them is that I can't figure out how to reckon the order within Excel. It's easy enough when one or two teams are tied on the early criteria, but when they're still tied, the algorithm says to compare the team's play against one another to determine the winner. When it ends up with teams still tied, they draw lots! That means we're reliant on their ranking! See the bottom of the standings page for the tie-breaker procedure.

 

[MCLIFTO8]

@smozgur
Just wanted to say thanks for the explanation.
I was able to follow along and locate the json data url from within the JS.
Looks like I need to look into importing json data with PQ rather than relying on VBA to solve everything.

 

[smozgur]

Suat, thanks immensely for all your work.

I have as much chance of ever understanding all that as I do of playing in the NHL. Your skill and knowledge here are amazing. Wow.

But of course, there is a problem. It has to do with the order the NHL lists the Overall Standings. The NHL has tiebreaker rules which they apply. Then they list the teams in that order on that very webpage. So, our Query here is not sorted in the same order.

The reason I need to get the data from them is that I can't figure out how to reckon the order within Excel. It's easy enough when one or two teams are tied on the early criteria, but when they're still tied, the algorithm says to compare the team's play against one another to determine the winner. When it ends up with teams still tied, they draw lots! That means we're reliant on their ranking! See the bottom of the standings page for the tie-breaker procedure.

You're welcome, Don.

I actually only looked at the table as raw data and sorted it by the points column as it is presented on the web page. However, it looks like there is more behind it. When I look at the tie-breaking procedure now, I can see problem rule #5. You are right, it is easy to sort data except for rule #5. In fact, the following is the slightly modified code considering the tie-breaking procedure except rule #5. (Also changed the API URL parameters to fetch less data since the division, conference, and next & previous team schedule is not necessary to get necessary data).

let

        fnOverallRecords = (Source) => let
            ConvertedTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedColumns = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"wins", "losses", "ot"}),
            MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ExpandedColumns, {{"wins", type text}, {"losses", type text}, {"ot", type text}}, "en-US"),{"wins", "losses", "ot"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
            TransposedTable = Table.Transpose(MergedColumns),
            Result = Table.RenameColumns(TransposedTable,{{"Column1", "home"}, {"Column2", "away"}, {"Column3", "shootOuts"}, {"Column4", "lastTen"}})  
        in
            Result,

        Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/standings?hydrate=record(overall),team&season=20222023&site=en_nhl")),
        ConvertedTable = Table.FromList(Source[records], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"standingsType", "league", "division", "conference", "season", "teamRecords"}),
        RemovedOtherColumns = Table.SelectColumns(ExpandedColumn1,{"teamRecords"}),
        ExpandedListColumn = Table.ExpandListColumn(RemovedOtherColumns, "teamRecords"),
        ExpandedRecordColumn = Table.ExpandRecordColumn(ExpandedListColumn, "teamRecords", {"team", "gamesPlayed",  "leagueRecord", "regulationWins", "goalsAgainst", "goalsScored", "points", "row", "streak", "pointsPercentage", "records"}),
        ExpandedRecordColumnTeam = Table.ExpandRecordColumn(ExpandedRecordColumn, "team", {"shortName"}),
        ExpandedRecordColumnLeague = Table.ExpandRecordColumn(ExpandedRecordColumnTeam, "leagueRecord", {"wins", "losses", "ot"}),
        ExpandedRecordColumnTeamRecords = Table.ExpandRecordColumn(ExpandedRecordColumnLeague, "records", {"overallRecords"}),
        ExpandedRecordColumnTeamStreak = Table.ExpandRecordColumn(ExpandedRecordColumnTeamRecords, "streak", {"streakCode"}),
        AddColumnOverallRecords = Table.AddColumn(ExpandedRecordColumnTeamStreak, "Custom", each fnOverallRecords(_[overallRecords])),
        ExpandedColumnOverallRecords = Table.ExpandTableColumn(AddColumnOverallRecords, "Custom", {"home", "away", "shootOuts", "lastTen"}),
        RemovedColumns = Table.RemoveColumns(ExpandedColumnOverallRecords,{"overallRecords"}),
        AddedColumnDiff = Table.AddColumn(RemovedColumns, "diff", each [goalsScored] - [goalsAgainst]),
        ReorderedColumns = Table.ReorderColumns(AddedColumnDiff,{"shortName", "gamesPlayed", "wins", "losses", "ot", "points", "pointsPercentage", "regulationWins", "row", "goalsScored", "goalsAgainst", "diff", "home", "away", "shootOuts", "lastTen", "streakCode"}),
        RenamedColumns = Table.RenameColumns(ReorderedColumns,{{"shortName", "National Hockey League"}, {"gamesPlayed", "GP"}, {"wins", "W"}, {"losses", "L"}, {"ot", "OT"}, {"points", "PTS"}, {"pointsPercentage", "P%"}, {"regulationWins", "RW"}, {"row", "ROW"}, {"goalsScored", "GF"}, {"goalsAgainst", "GA"}, {"diff", "DIFF"}, {"home", "HOME"}, {"away", "AWAY"}, {"shootOuts", "S/O"}, {"lastTen", "L10K"}, {"streakCode", "STRK"}}),
        ChangedColumnTypes = Table.TransformColumnTypes(RenamedColumns,{{"National Hockey League", type text}, {"GP", Int64.Type}, {"W", Int64.Type}, {"L", Int64.Type}, {"OT", Int64.Type}, {"PTS", Int64.Type}, {"P%", type number}, {"RW", Int64.Type}, {"ROW", Int64.Type}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"DIFF", Int64.Type}, {"HOME", type text}, {"AWAY", type text}, {"S/O", type text}, {"L10K", type text}, {"STRK", type text}}),
        SortedRows = Table.Sort(ChangedColumnTypes,{{"PTS", Order.Descending}, {"GP", Order.Ascending}, {"RW", Order.Descending}, {"ROW", Order.Descending}, {"W", Order.Descending}, {"DIFF", Order.Descending}, {"GF", Order.Descending}}),
        Result= SortedRows
    in
        Result

There is one thing that I am curious about. Does the web page normally sort the data by using the tie-breaking procedure (including #5)? I can't see that, because none of the tied teams falls into rule #5 since previous rules already decide the sorting. If so, then the sorting procedure should be executed either in the associated JavaScript module or stored somewhere in the JSON (If I was the developer of that application, I would have sorted it on the server and returned the JSON that includes a sorting key). I didn't check either the JavaScript module or the JSON document in detail, but could you please tell me if the presented data on the web page is sorted as it is explained by the rules including #5? I am a bit confused about this because the web page also allows sorting the data in the table manually.

 

[smozgur]

@smozgur
Just wanted to say thanks for the explanation.
I was able to follow along and locate the json data url from within the JS.
Looks like I need to look into importing json data with PQ rather than relying on VBA to solve everything.

@MCLIFTO8: Glad to hear it helps.

JSON parsing functions have never been implemented in VBA despite the entire world has been using JSON for a long time. In fact, nothing serious has been implemented in VBA for ages. However, it will be always a "you can do everything" programming language but we should certainly use Power Query (actually M language) features, in fact even combine with VBA, in order to make life easier.

 

[DRSteele]

You're welcome, Don.

I actually only looked at the table as raw data and sorted it by the points column as it is presented on the web page. However, it looks like there is more behind it. When I look at the tie-breaking procedure now, I can see problem rule #5. You are right, it is easy to sort data except for rule #5. In fact, the following is the slightly modified code considering the tie-breaking procedure except rule #5. (Also changed the API URL parameters to fetch less data since the division, conference, and next & previous team schedule is not necessary to get necessary data).

let

        fnOverallRecords = (Source) => let
            ConvertedTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedColumns = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"wins", "losses", "ot"}),
            MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ExpandedColumns, {{"wins", type text}, {"losses", type text}, {"ot", type text}}, "en-US"),{"wins", "losses", "ot"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
            TransposedTable = Table.Transpose(MergedColumns),
            Result = Table.RenameColumns(TransposedTable,{{"Column1", "home"}, {"Column2", "away"}, {"Column3", "shootOuts"}, {"Column4", "lastTen"}}) 
        in
            Result,

        Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/standings?hydrate=record(overall),team&season=20222023&site=en_nhl")),
        ConvertedTable = Table.FromList(Source[records], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"standingsType", "league", "division", "conference", "season", "teamRecords"}),
        RemovedOtherColumns = Table.SelectColumns(ExpandedColumn1,{"teamRecords"}),
        ExpandedListColumn = Table.ExpandListColumn(RemovedOtherColumns, "teamRecords"),
        ExpandedRecordColumn = Table.ExpandRecordColumn(ExpandedListColumn, "teamRecords", {"team", "gamesPlayed",  "leagueRecord", "regulationWins", "goalsAgainst", "goalsScored", "points", "row", "streak", "pointsPercentage", "records"}),
        ExpandedRecordColumnTeam = Table.ExpandRecordColumn(ExpandedRecordColumn, "team", {"shortName"}),
        ExpandedRecordColumnLeague = Table.ExpandRecordColumn(ExpandedRecordColumnTeam, "leagueRecord", {"wins", "losses", "ot"}),
        ExpandedRecordColumnTeamRecords = Table.ExpandRecordColumn(ExpandedRecordColumnLeague, "records", {"overallRecords"}),
        ExpandedRecordColumnTeamStreak = Table.ExpandRecordColumn(ExpandedRecordColumnTeamRecords, "streak", {"streakCode"}),
        AddColumnOverallRecords = Table.AddColumn(ExpandedRecordColumnTeamStreak, "Custom", each fnOverallRecords(_[overallRecords])),
        ExpandedColumnOverallRecords = Table.ExpandTableColumn(AddColumnOverallRecords, "Custom", {"home", "away", "shootOuts", "lastTen"}),
        RemovedColumns = Table.RemoveColumns(ExpandedColumnOverallRecords,{"overallRecords"}),
        AddedColumnDiff = Table.AddColumn(RemovedColumns, "diff", each [goalsScored] - [goalsAgainst]),
        ReorderedColumns = Table.ReorderColumns(AddedColumnDiff,{"shortName", "gamesPlayed", "wins", "losses", "ot", "points", "pointsPercentage", "regulationWins", "row", "goalsScored", "goalsAgainst", "diff", "home", "away", "shootOuts", "lastTen", "streakCode"}),
        RenamedColumns = Table.RenameColumns(ReorderedColumns,{{"shortName", "National Hockey League"}, {"gamesPlayed", "GP"}, {"wins", "W"}, {"losses", "L"}, {"ot", "OT"}, {"points", "PTS"}, {"pointsPercentage", "P%"}, {"regulationWins", "RW"}, {"row", "ROW"}, {"goalsScored", "GF"}, {"goalsAgainst", "GA"}, {"diff", "DIFF"}, {"home", "HOME"}, {"away", "AWAY"}, {"shootOuts", "S/O"}, {"lastTen", "L10K"}, {"streakCode", "STRK"}}),
        ChangedColumnTypes = Table.TransformColumnTypes(RenamedColumns,{{"National Hockey League", type text}, {"GP", Int64.Type}, {"W", Int64.Type}, {"L", Int64.Type}, {"OT", Int64.Type}, {"PTS", Int64.Type}, {"P%", type number}, {"RW", Int64.Type}, {"ROW", Int64.Type}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"DIFF", Int64.Type}, {"HOME", type text}, {"AWAY", type text}, {"S/O", type text}, {"L10K", type text}, {"STRK", type text}}),
        SortedRows = Table.Sort(ChangedColumnTypes,{{"PTS", Order.Descending}, {"GP", Order.Ascending}, {"RW", Order.Descending}, {"ROW", Order.Descending}, {"W", Order.Descending}, {"DIFF", Order.Descending}, {"GF", Order.Descending}}),
        Result= SortedRows
    in
        Result

There is one thing that I am curious about. Does the web page normally sort the data by using the tie-breaking procedure (including #5)? I can't see that, because none of the tied teams falls into rule #5 since previous rules already decide the sorting. If so, then the sorting procedure should be executed either in the associated JavaScript module or stored somewhere in the JSON (If I was the developer of that application, I would have sorted it on the server and returned the JSON that includes a sorting key). I didn't check either the JavaScript module or the JSON document in detail, but could you please tell me if the presented data on the web page is sorted as it is explained by the rules including #5? I am a bit confused about this because the web page also allows sorting the data in the table manually.

The page sorts it according to all their tie-breaker rules no matter how the user sorts it. Your new code is what I did to your original code, that is, sorted it according to a cascading set of columns. But then rule#5 gets in the way.

I tried to do it in Excel by using a grid and a bunch of formulas. That was complex enough with two tied teams, but when there were more tied teams, I gave up in frustration.

 

[smozgur]

Ok, just as I thought (and the right way), returned data is already sorted correctly on the server. I just needed to find the correct end-point.
By the way, I am taking my word back about the JSON quality. This is a complicated but beautiful API. If I was a hockey/sports fan then I am sure I would have spent so much time with this API.

You don't need to worry about sorting with the following code - the tie-breaking procedure is completely applied just as it is returned on the web page. It is not too much different than the previous code but it is better to send it as the entire code to help future readers as well.

let
        fnOverallRecords = (Source) => let
            ConvertedTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedColumns = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"wins", "losses", "ot"}),
            MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ExpandedColumns, {{"wins", type text}, {"losses", type text}, {"ot", type text}}, "en-US"),{"wins", "losses", "ot"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
            TransposedTable = Table.Transpose(MergedColumns),
            Result = Table.RenameColumns(TransposedTable,{{"Column1", "home"}, {"Column2", "away"}, {"Column3", "shootOuts"}, {"Column4", "lastTen"}}) 
        in
            Result,

        Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/standings/byLeague?hydrate=record(overall),team&season=20222023&site=en_nhl")),
        ConvertedTable = Table.FromList(Source[records], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"standingsType", "league", "division", "conference", "season", "teamRecords"}),
        RemovedOtherColumns = Table.SelectColumns(ExpandedColumn1,{"teamRecords"}),
        ExpandedListColumn = Table.ExpandListColumn(RemovedOtherColumns, "teamRecords"),
        ExpandedRecordColumn = Table.ExpandRecordColumn(ExpandedListColumn, "teamRecords", {"team", "gamesPlayed",  "leagueRecord", "regulationWins", "goalsAgainst", "goalsScored", "points", "row", "streak", "pointsPercentage", "records"}),
        ExpandedRecordColumnTeam = Table.ExpandRecordColumn(ExpandedRecordColumn, "team", {"shortName"}),
        ExpandedRecordColumnLeague = Table.ExpandRecordColumn(ExpandedRecordColumnTeam, "leagueRecord", {"wins", "losses", "ot"}),
        ExpandedRecordColumnTeamRecords = Table.ExpandRecordColumn(ExpandedRecordColumnLeague, "records", {"overallRecords"}),
        ExpandedRecordColumnTeamStreak = Table.ExpandRecordColumn(ExpandedRecordColumnTeamRecords, "streak", {"streakCode"}),
        AddColumnOverallRecords = Table.AddColumn(ExpandedRecordColumnTeamStreak, "Custom", each fnOverallRecords(_[overallRecords])),
        ExpandedColumnOverallRecords = Table.ExpandTableColumn(AddColumnOverallRecords, "Custom", {"home", "away", "shootOuts", "lastTen"}),
        RemovedColumns = Table.RemoveColumns(ExpandedColumnOverallRecords,{"overallRecords"}),
        AddedColumnDiff = Table.AddColumn(RemovedColumns, "diff", each [goalsScored] - [goalsAgainst]),
        ReorderedColumns = Table.ReorderColumns(AddedColumnDiff,{"shortName", "gamesPlayed", "wins", "losses", "ot", "points", "pointsPercentage", "regulationWins", "row", "goalsScored", "goalsAgainst", "diff", "home", "away", "shootOuts", "lastTen", "streakCode"}),
        RenamedColumns = Table.RenameColumns(ReorderedColumns,{{"shortName", "National Hockey League"}, {"gamesPlayed", "GP"}, {"wins", "W"}, {"losses", "L"}, {"ot", "OT"}, {"points", "PTS"}, {"pointsPercentage", "P%"}, {"regulationWins", "RW"}, {"row", "ROW"}, {"goalsScored", "GF"}, {"goalsAgainst", "GA"}, {"diff", "DIFF"}, {"home", "HOME"}, {"away", "AWAY"}, {"shootOuts", "S/O"}, {"lastTen", "L10K"}, {"streakCode", "STRK"}}),
        ChangedColumnTypes = Table.TransformColumnTypes(RenamedColumns,{{"National Hockey League", type text}, {"GP", Int64.Type}, {"W", Int64.Type}, {"L", Int64.Type}, {"OT", Int64.Type}, {"PTS", Int64.Type}, {"P%", type number}, {"RW", Int64.Type}, {"ROW", Int64.Type}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"DIFF", Int64.Type}, {"HOME", type text}, {"AWAY", type text}, {"S/O", type text}, {"L10K", type text}, {"STRK", type text}}),
        Result= ChangedColumnTypes
    in
        Result

 

[DRSteele]

Sweet! The only thing more astonishing than your technical abilities is the fact NJ Devils lead the league.

When you fire up the webpage for overall standings, there is an index number to the left of each team - THAT is their official ranking after tie-breaker rules have been applied; they use it to sort all the tables.

I used to be able to get the ranking index data from a zombie website, but it seems to have been slain for good.