Step 1 — Consume Mass Quantities (Import Fantasy Football Stats)

<–Intro           Step 2–>

The Coneheads from Saturday Night Live (1977) would “consume mass quantities,” by gulping entire six-packs and bags of chips.  With the Excel spreadsheet, we can consume mass quantities of fantasy football stats, and properly digest this data into useful information for the big draft.

I only gather data of the last three seasons.  Older data loses its value.  Use less than three seasons of data, and we might as well pick players based on last season’s performance.

Since I’m in Yahoo! leagues, I found the Yahoo! statistics page.  It has all of the basic elements needed to begin building our Excel spreadsheet (Yards, TD’s, Fumbles, etc.).

First, filter the online data by Position, View by, Year and Timeframe.  Always set the Players filter to “All Players”, if given the option.

 excel fantasy football spreadsheet stats nfl draft cheat sheet

Figure 1-1

Import the raw data directly from the webpage by clicking the Data tab, then the From Web command in the Get External Data group, as follows:

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-2

Copy the URL from your browser (above), and Paste it into the Address field (below), and click Go.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-3

Then, select only the statistics table by clicking its yellow arrow.  The yellow arrow changes into a green check mark.  Now, click Import.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-4

Your imported data should look like this on your Excel spreadsheet.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-5

You can now repeat this step another 51 times (3 seasons x 17 weeks = 51 times), for each position.  Yikes!  Or, just get a FREE copy of my NFL stats.  I have already done this tedious task for you.  This is my least favorite part of the process, too.

Next, change the Games column header from “G” to “Week”, and add a column for the “Season” (e.g. 2011).  There’s a nice blank column that’s ready for you in column D.  So, just type “Season” into D2.  Enter and auto-fill the Week and Season for each player.  This step is key to creating PivotTables by Week and Season in later steps.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-6

I like to categorize each data column, in order to avoid confusion between rushing yards and receiving yards, etc.   Simply, add detail to the column headers, such as “Rushing_Yds” and “Receiving_Yds” as follows:

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-7

Now, we can clean up the data set by Deleting the empty columns, and Row 1.  Let’s also change the tab’s title from “Sheet1” to “RB Raw Data” by double-clicking it.

From:

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-8

To:

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 1-9

Did you really gather and scrub the entire data set yourself?  You data-stud, you!

Note: Remember to TRIM away the extra spaces from players’ names.  Also, account for duplicate names, like Adrian Peterson (MIN) and Adrian Peterson (CHI).  Or, you can just use my free data set.  I’ve already scrubbed it for you.

<–Intro           Step 2–>

fantasy football cheat sheet Excel spreadsheet pivottable vlookup rank tier raw data stats statisticsfantasy football cheat sheet Excel spreadsheet pivottable vlookup rank tier raw data stats statisticsExcel Fantasy Football Book iBook iBookstore iTunes iPad Cheat Sheet NFL

Advertisements

13 responses to “Step 1 — Consume Mass Quantities (Import Fantasy Football Stats)

  1. Where is the free downloads box?

    • Hi, Brandon. Thanks for visiting!

      The Free Downloads box is on the right side of each blog page, below the Table of Contents.

      Enjoy, and good luck this season!

      –John

  2. I may be blind but I still do not see it, below the table of contents I see Links and Blogroll. This is exactly what I have been looking for, I have been trying to create my own forever. Thanks for all the help

    • hmmm… that’s odd. Maybe, the box widget doesn’t appear on all browsers. (I’ll have to create a workaround for that.)

      In the meantime, I see your gmail address here, Brandon. I’ll just email you the file.

      Thanks for the feedback.

  3. Thanks a ton! It may be the Server I am on. But thanks a ton for this info!

    • You’re welcome, Brandon.

      The files should be in your gmail inbox any moment now.

      I have also created the workaround for the free downloads, using text-based links in the page’s body.

      Thanks again for your feedback.

  4. Update: The Free Downloads box widget has been removed. Free NFL Stats are still available by clicking here: https://excelfantasyfootball.wordpress.com/2012/01/02/get-free-nfl-stats-in-excel/

  5. Having issues with instructions for the excel file. I added data from web, but after the first time, excel blacks out the ability to do it. How do I repeat 51 more times?

    • That’s odd…and frustrating, Jeremy. I’m not sure why Excel would stop you from importing more data. This is the first time that I’ve heard of such an issue.

      I would suggest downloading the free ready-made data set.

      Also, Excel has a Help feature that might offer a troubleshooting solution. Click the “?” at the top of your Excel window, then search for a potential solution.

  6. How many downloads have you had so far? This idea is brilliant!

  7. This is great! I actually found the same Yahoo site a while back ago and used it in a similar manner, but the fact that you’ve compiled all of the data for everyone is amazing. It’s definitely a bit of a pain to import all of that data by yourself. With that said, I wanted to make one comment and/or ask one question… how did you manage to combat the issue of Yahoo not providing rushing statistics for WRs? Personally, I would have rather had them give us rushing info for WRs than the kicking and punt return info. For example, in week 17 of the 2013 season, Cordarerelle Patterson (Vikings WR) had 2 rushes for 54 yards and a TD. I realize that this doesn’t happen all that often, but in that particular situation it was a significant source of fantasy points, and it’s not accounted for when using the Yahoo search results. I’m just trying to create the most accurate analysis as possible. Any response or comment on this would be greatly appreciated.

    • Thanks, John!

      Yeah, I would prefer WR rushing stats too. However, they’re simply not available on the Yahoo! webpages yet. So, I merely do without those stats for now.

      For what it’s worth, Yahoo! does provide stats on Reception Targets now.

      Good luck and have a great season!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s