Recently I setup a league for fantasy basketball through ESPN.com. As the league manager I wanted to give some stats to my league on who had the best draft. I decided I would take the season projections that ESPN has on every player and total them for each team. That way we could see who is predicted to do the best throughout the season. I could do this manually by looking up each player and recording their season points projection. That would have taken forever though and I wasn’t interested in spending my entire day analyzing my fantasy league.
So here’s what I did:
1. Opened a new Google Spreadsheet and wrote a formula to get all of the teams on one sheet. Each team in the league has a unique URL that displays their team information. So my team is located at http://games.espn.go.com/fba/clubhouse?leagueId=131029&teamId=1&seasonId=2011. All the other teams in the league can be found at the same URL, but the number after the “teamId=” is different. The formula I need to use is the “importHTML” function in Google Docs. This allows you to pull any data that’s visible online (and it will be updated every time you open the spreadsheet). So here’s the function I write to get all of my players listed in my spreadsheet.
Make sure to have the URL and table in quotes and the last number is just the number of the table on the page that you’d like to import if there are multiple tables on one web page. Now I have all my players listed on my sheet and I’ll open a second sheet to bring in my rankings/prediction data.
2. I’ll write the same importHTML function to bring in the data from ESPN’s season predictions. These are located at http://games.espn.go.com/fba/tools/projections?leagueId=131029. I’ll write that same function:
Now I have all of the projections in a second spreadsheet. To overlay the projections on each team I’m going to have to write a vlookup query. That means I’m asking the spreadsheet to find a player’s name from my team on the projections list and grab the total points from that same row. So here’s what I’ll do in the first column next to my team data.
=vlookup(Cell with player’s name from my team, entire range of projections data, column with season projections, FALSE)
Then I’ll have projections for each player and I can sum them for each team so I know which teams in my league are the favorites and which ones might struggle.
If you’d like you can take a look at the fantasy basketball spreadsheet that I built for our league