Analytics has taken over the sports industry, and data is crucial in fantasy sports, including fantasy basketball. While most fantasy apps provide a wide variety of data and analytics, they have limitations, such as not being able to customize how you view and interact with the data.
In the past few posts, we covered how to pull NBA player stats with an API from RapidAPI and pull our specific league data using the Sleeper API. However, the Sleeper API doesn’t have endpoints to get current matchups or locked points yet, so we’ll have to enter those manually.
While it does take some effort to maintain these, we’ll use Python to make the process as easy as possible. We have an Excel spreadsheet for entering the lineup, and we’ll use our stats data to automatically fill in the stats and schedule for the current week. That way we simply have to keep the lineup updated and add an x next to games that are locked in.
In this post, we’ll show you the process we made, including everything from creating the Excel template to reading and writing the data with Python.
Excel File
Template Structure
We’ll start with the Excel file we made for the lineup, which is formatted to mimic the matchup screen on the Sleeper app. This lists out the different positions vertically with our team on the left, the opponent on the right, and team totals at the top.
Each lineup position has it’s own set of boxes for each player’s schedule and stats. To update these, we’ll start by manually adding the name of each player in each position. Then, Python uses the player name to lookup the schedule/stats for that week and adds it to the corresponding boxes. Finally, we’ll add an x next to each locked game, and an Excel formula gets the locked total based on where the x is at.
The Excel formula is a set of if statements that checks the cells in the bottom row and returns the first total where it isn’t blank.
We also added conditional formatting to the cell that highlights locked totals with an orange background.
To keep the history across past weeks, we broke out each week into a separate sheet. We’ll start with a Template sheet with all the formatting and simply duplicate and it for each new week.
Note that you can duplicate a sheet in Excel by right clicking the sheet name, selecting "Move or Copy...", checking the "Create a copy" box, and clicking "Ok".
Updating the Spreadsheet
Overall, the update process looks like this:
- Update lineup in Excel
- At the start of the week, duplicate and rename the Template sheet
- Add player names in each of the lineup positions (be careful of typos)
- Python adds stats/schedules
- Python finds each player by name and adds their schedule/stats
- Mark locked games in Excel
- Add an
xnext to games that have been locked in
- Add an
- Python saves locked points
- Python combines all the locked totals and saves to a CSV
- Repeat as needed
Data Prep
Excel File
With the Excel file created and our lineup updated, we can read it into Python using the openpyxl package. Note that you’ll need to close out of the Excel file before running this (or else you’ll get a ‘Permission denied’ error).
CSV Files
Next, we’ll import a few of the CSV files with the NBA stats data. We’ll start with the Players dimension table, which defines each NBA player by PLAYER_ID. Note that the player name is split between first and last name columns, so we’ll concatenate them together for easier filtering later.
We’ll also import the Games dimension table, which defines each NBA game by GAME_ID. Note that date/time columns aren’t always formatted correctly from the CSV, so we’ll explicitly make sure that GAME_DATE is formatted as a Pandas timestamp.
Finally, we’ll import our Stats fact table with total fantasy points by player and game. We’ll also make sure that GAME_DATE is formatted correctly as a Pandas timestamp.
Player Schedules
With the raw stats data loaded in, we need to do some transformations to get it in the right format. We need the full schedule broken out by player with fantasy points added for past gamed, which we can do by re-formatting the Games data and joining in the players and stats data.
Python Functions for Excel Data
With the stats data setup, it’s time to update the spreadsheet accordingly. To keep the code organized, we’ll define some modular functions that focus on a specific pieces of the process.
get_player_id
Our first function looks up a player name and id from a given position in the spreadsheet. The player id will be useful for filtering other datasets later.
update_player_stats
The second function updates the schedule/stats for a given player/week in the spreadsheet based on the schedule data. It takes the wb object and implements cell changes within it, but the changes aren’t saved to the actual Excel workbook until the wb.save() method is called at the end.
get_locked_points
The third and final function reads the locked totals for a given position from the Excel doc. Note that it’ll return NA if the player hasn’t been locked in yet.
Applying the Functions
Finally, we’ll put everything together in a set of nested for loops that iterates through each week, team, and player. We’ll call the update_player_stats to add all the schedule and stats for each position. Then, we’ll call the get_locked_games function to get the locked totals and append them all to a single dataframe. Finally, we’ll save the locked points dataframe to its own CSV and save all the workbook changes with the wb.save() method (note that the workbook needs to be closed when you run this cell to avoid the ‘Permission denied’ error).
Conclusion
While manual entry in Excel is never fun, we utilized Python to make it as easy as possible. In the next post, we’ll add this to our other data sources to help make better team management decisions.
Feel free to download the full Excel template and Jupyter notebook of Python code to try it out yourself:
If you found this guide helpful, please drop a comment below with any questions or feedback you have. Your input helps us improve future posts and inspire new ideas.
Thanks for joining us today, and we hope to see you next time!