Basic Spreadsheet Building: NBA
There’s one word that should be on your mind when building your NBA spreadsheets (or for any sport) and that’s organization. The key to making your day-to-day spreadsheet process more efficient comes with organization and by being concise with the data points you want to reach. I’ve written, deleted and rewritten this lesson 20 times by now because building your own spreadsheets can be done in so many ways. At the end of the day, having a good understanding of the most effective functions in Excel as well as keeping your file organized will be the most important variable in creating a good NBA spreadsheet.
There are a few basic functions you’ll need to know when building your spreadsheets. For the purpose of these lessons, we won’t get into macros or anything too complex. Believe it or not you can make a professional level spreadsheet without using any of those.
VLOOKUP Functions
These will be the most important functions you use on a day to day basis. The VLOOKUP function allows you to reference statistics from multiple sheets within your spreadsheet and bring it into one condensed sheet. Here’s an example of a typical VLOOKUP function:
=VLOOKUP (A2,PlayerStats!A:Z,5,FALSE)
In this function, A2 is the cell you are searching for a value for. Typically that will be the name of a specific player of team. PlayerStats!$A:$Z is the range where you’re searching for that statistic, with PlayerStats! being a different sheet from the one you are using to organize the data and A:Z being column references. The “5” is the column number we want to return a statistic for. Since we’re searching between columns A and Z, the 5th column will be the statistic from Column E of the PlayerStats sheet. Last, the “FALSE” just ensures that the function will only search for exact matches.
You can use VLOOKUP functions when organizing player salaries, matchup stats, general stats or pretty much anything you need to stay organized.
Creating a Player List
Another thing you can do to keep your spreadsheet more organized is to create a player list that you can reference in other sheets. This can be done simply by grabbing the stats from the RG Player Stats pages or by grabbing them from any source that lists player name, player position and team. I set my player list up so columns A-C of the PlayerList sheet include all players at all positions. Then columns E-G are point guards only, I-K are shooting guards, M-O are small forwards, Q-S are power forwards, U-W are Centers, Y-AA are all guards (PG+SG), and AC-AE are all forwards (SF+PF).
Exporting Salaries
What we do at RotoGrinders every day is export all of the salaries on each site to a single sheet within a spreadsheet. Chances are, you don’t play on every single site so instead just work on organizing the sites that apply to you. Here are some notes about each site’s export function:
- FanDuel – They don’t have an export function, so you can either copy then right click in Excel and “Paste HTML Formatting” to get a clean set of salaries, or if you use Google Chrome you can use Table Capture and then grab the salaries using that tool.
- DraftStreet – When pasting the DraftStreet CSV file into excel, they separate first and last names in their downloadable file. Combining them is simple, just make sure to have an automated column with this function, assuming you paste the columns from DraftStreet’s CSV into Cell B1:
Cell A2 = B2&” “&C2
Where B2 is the first name and C2 is the last name from the CSV
- FanThrowDown – Like DraftStreet, FanThrowDown separates first and last names so you should follow the same steps for those.
- FantasyFeud – You can copy and paste these salaries to excel manually or use the Table Capture app in Google Chrome as mentioned above. When you paste these salaries in, there will be a large number of spaces before the player names. If you copy and paste the spacing from one sample player, then select the column with player names and hit CTRL+F then choose “Replace” and paste in the spacing you copied, then hit “Replace All” it will remove all of the spacing.
The other sites are basic copy and paste into the file.
Paste Day Sheet
Setting up your schedule is another critical part to making your spreadsheet more efficient. In the attached file, there is a schedule sheet along with a PasteDay sheet that organizes the matchups so you can later reference them easily with VLOOKUP functions. I won’t go too much into how this is set up, but if you’re interested you can see all of the functions used by looking through the spreadsheet.
Growing Your Spreadsheet
You can add sheets for any stat you want to include. Whether you want to pull stats from NBA.com, ESPN or any other source, you just have to make sure that you include the ability to lookup names with the same spelling across all sources. For example, if ESPN lists the Lakers as “LA Lakers” and CBS has them as “L.A. Lakers” you need to decide on one spelling and have a VLOOKUP column so the correct spelling is included on the given paste sheet.
From there the options are endless. In the next two lessons, I’ll discuss how to build projections into a spreadsheet and then how to build optimal lineups from there, but many people also like to just list the important stats in an organized fashion and build lineups off of that. The possibilities are really infinite, so just make sure you get a solid understanding of Excel and you can work from there.