Importing Stats from Basketball Reference with Excel Power Query

Years ago when I was actively competing in NBA Daily Fantasy Sports (DFS), getting player stats into my custom Excel tool was not so easy. At first, the technology available at that time required that I import the data using a the very clunky Web Query method in Excel (see screenshot below).

The Web Query was not at all user-friendly. I then opted to write code in Visual Basic for Applications to perform this step for me. This worked until I discovered the beauty of Excel Power Query. Power Query is incredibly powerful but what I like about it is how easy it makes importing data from websites into Excel. In this blog post, I'm going to walk you through how to set up an Excel spreadsheet to help you import data from Basketball Reference using Excel Power Query. This setup will make it possible for you to import data for a specific date.


First things First


Excel Power Query first became available in Office 2010 version but I believe it was an add-in. In version 2016, In Excel 2016, Microsoft integrated the Power Query technology and experience into Excel, which doesn't require the add-in. So depending on your version of Office, it may or may not be available and if it is, you will have to determine whether you need the add-in or not.


Make the Connection to Basketball Reference Daily Leaders

  1. Open a new workbook in Excel.

  2. While on Sheet1, click on the Data tab (I'm using Excel 2019, so hopefully your version has the Data tab as well).

  3. Under the section "Get & Transform Data", select "From Web" and leave "Basic" checked.

  4. Paste this URL into the blank area: https://www.basketball-reference.com/friv/dailyleaders.fcgi

  5. The Navigator dialog box will appear with Display Options in the left-hand section:

  6. Select the Display Option that references Players Table. It will be preceded by a number, which indicates how many players had stats for the most recent game date provided by Basketball Reference. So for example: 120 Players Table for games played on 2/3/2022.

  7. Once you make your selection, the stats will preview on the right-hand section of the dialog box and by default should show you the stats in the Table View vs the Web View.

  8. Now let's load the data to your worksheet by doing the following:

  9. Click on the ▼ to the right of the Load button and then select "Load To".

  10. In the middle of the dialog box, select Existing Worksheet and leave it set to =$A$1

  11. Click OK.



Setting up the Dynamic Game Date Variable for the Basketball Reference Query

  1. Create a new sheet in your workbook and name it Setup.

  2. Create an input cell to store a date. For example, let's say in cell B1 you enter a date of 2/3/2022. The goal here will be to import player stats for NBA games that were played on Wednesday, February 3, 2022.

  3. In cells E1 - E3, you will need to create formulas that will dynamically extract the parts of the date you entered in cell B1.

  4. in cell E1, enter the formula =YEAR(B1)

  5. in cell E2, enter the formula =TEXT(B1,"mm")

  6. in cell E3, enter the formula =DAY(B1)

  7. Your setup should look like the below picture:


Setting up the Dynamic URL Variable to Basketball Reference

  1. After entering your date variables, you'll need to setup the string for the URL that will point to the Basketball Reference page that has the stats for the date you are looking for.

  2. Enter this formula into cell H1 of your setup sheet: =HYPERLINK("https://www.basketball-reference.com/friv/dailyleaders.fcgi?month="&E2&"&day="&E3&"&year="&E1&"&type=all")

  3. Then test the link to make sure it works and brings to you to the URL.

  4. Next create a named field called BBallRefURL. You can create a named field by selecting the cell, selecting the Formulas tab, and then select "Define Name" under the "Defined Names" section. Type BBallRefURL in the "Name:" field and click Ok.


Setting up the Dynamic Connection to Basketball Reference

  1. Now let's rename Sheet1 to newData. This sheet will serve as your temporary sheet so that each time you refresh Basketball Reference data, you will only refresh it in this sheet.

  2. In column AB of your newData sheet, enter a column name in cell AB1 called GameDate. This column will pull in your date variable in the Setup sheet. In cell AB2, enter =Setup!B1 and then hit the F4 key to make this an absolute cell reference. Now hit enter and in cell AB3 you'll see the icon for AutoCorrect Options. Click on it and then select "Overwrite all cells in this column with this formula." Now you have all records in that table with the game date that they correspond to.

  3. Now right click on the sheet name newData and select "Move or Copy".

  4. Then select "Create a copy."

  5. After creating your new sheet, change the sheet name to allData.

  6. Select all cells in your sheet by holding Ctrl and A keys together. Now copy all cells by holding Ctrl and C together. Finally, right-click anywhere on your sheet and select Paste Special ► Paste Values 123. This will make sure everything in this sheet are values without formulas.

  7. Click on the Data tab above and select "Queries and Connections". It will bring up a dialog box on the right (if it's not already open). The query in the allData sheet should be highlighted dark green on the right. Right-click on the query and Rename it to allData.

  8. Then select the query right above it and right-click and rename it to newData.

  9. Now right-click on the newData query and select Edit. This will bring you into the Power Query Editor.

  10. Under the Home tab and in the Query section, select "Advanced Editor". Copy the code below and overwrite all of the existing code in the "Advanced Editor":

let
    QryParam = Table.FirstValue(Excel.CurrentWorkbook(){[Name="BBallRefURL"]}[Content]),
    Source = Web.Page(Web.Contents(QryParam)),
    Data0 = Source{0}[Data]
in
    Data0

Final Steps

  1. Once you've pasted the above code into the "Advanced Editor", select "Done".

  2. You will be prompted with a warning that says "Information is required about data privacy". Click Continue.

  3. Next, a dialog box will appear required Privacy Levels be set. Since this data is not private information, you can click on the box that says "Ignore Privacy Levels checks for this file...". Then click Save.

  4. Finally, select File ► Close and Load.

  5. Now you can try out your dynamic inputs. Try to find another game date besides 2/3/2022, where there were NBA games played. In this example, let's try 2/2/2022, since we know for sure the NBA had games that day. Go to your Setup sheet and enter 2/2/2022 in cell B1 of your Setup sheet.

  6. Now go back to your newData sheet and select any cell inside of the table. Right-click on the table and select Refresh.

  7. If you then select all of the newly refreshed data (excluding the headers), you can copy that data to your allData sheet as values only. This will ensure that each time you refresh your data and transpose it to the allData sheet, it will bring over the date values too and not reference the formula to your Setup sheet date.

  8. Voila, you now have a dynamic and automated tool that will help you bring player data into your tool and allow you to easily aggregate player stats each day.


Thanks for reading and I hope this post helped. Please leave a comment if you enjoyed it.

Featured Posts
Posts are coming soon
Stay tuned...
Recent Posts
Archive
Search By Tags
Follow me
  • LinkedIn
  • YouTube