Excel 2007 NFL Drive Chart

| 15 Comments | 0 TrackBacks

Ever eat popcorn watching the Super Bowl?


When I was developing this chart, it made me think of popcorn, since chart series were popping on and off the screen just like popcorn in a hot kettle. So I've decided to call this kind of chart a Popcorn Chart. A couple weeks ago I was following a game on the NFL site with their Flash application. Their Flash app is called a Drive Chart. It shows the game, play by play, either historically or real time. 

This was the first time I had ever seen their app and a few seconds into the game, I started thinking how I would go about recreating the app in Excel 2007. Here's a screenshot of my Excel 2007 version:

NFL_Drive_Chart.png
You can click on it to see the image in full size. The chart is interactive and will show you play by play how the Saints spanked the Cardinals in week 19. 



For a frame of reference, here is NFL.com's Flash app that was the source of my inspiration:


To find the Flash app, click on the blue DRIVE CHART tab at the bottom of the GAME CENTER (top of page).

This is not a trivial chart that you can create by pressing F-11 on your keyboard. This is a compound chart driven by a bunch of formulas. The entire field of play is drawn with error bars. The chart has 69 chart series that pop on and off and scroll. Different series represent different sorts of plays for each team. The game clock is an inverted trapezoid shape. Most everything above the clock is highly formated worksheet cells. The chart can be run without VBA enabled, but you will have to click on a scroll bar to advance the game. If you enable macros, the chart can display (and pause, and rewind) the game automatically at various speeds. Finally it employs a nice visual element where the legend which is actually another chart fades in when you hover the mouse over the golden key.

For now, the chart is limited to displaying games that have already occurred, but for next season I would like to add a module to grab the plays as they happen off of the NFL site and allow the user to view live games, possibly several at the same time, with several copies of the chart on one sheet.

It's possible to learn many techniques for advanced charting from this one application. I am happy to share my tutorial on constructing this chart in Excel 2007, but just realize that it will take a couple of hours for you to complete the chart!

Here's the tutorial.


Reblog this post [with Zemanta]
If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/8

15 Comments

The link to the tutorial seems to be broken...

Sam:

Sorry the tutorial wasn't complete yet.

It's live now.

Please try again.

Daniel Ferry
excelhero.com/blog

Hi Daniel:

My friend Andy Pope recommended you to me as the right person to reach out to share with me your "best in practice" templates for vendor profiling or scorecarding -- if you have any thoughts I would love to explore how they would support my vendor management role. Thanks!

Flo

I am smitten by the way you embraced this topic. It is not often I come across a blog with attractive articles like yours. I will make a note of your feed to stay up to date with your succeeding updates.Just stunning and do preserve up the effective work.

Very interesting read. Thanks.

I found this very helpful. I have been learning the ropes on my own and taking advice wherever I can get it.

Keep it up, I'm really digging these long posts, lately I have more and more time to read through your blog and I am just loving it!

Thanks for sharing! I created a variation of this that I want to print out and everything looks perfect. However, when I print it out and on the print preview screen the background color bleeds through from the 0 to 3.5 yard line on both ends of the field. I also downloaded the workbook and it appears to do the same thing. Any insights?

@Kennie-

That's certainly strange!

It's a bug in Excel and I can confirm that it's happening on my computer as well, but I never noticed it until you pointed it out - so thanks for that. It's funny, but I never tried to print it...

For some reason the width of the error bar being used as the grass is incorrectly displayed by the print driver (it's a little too narrow). I have no idea how everything else could be in proper proportion and this one element not, but as much as I love Excel, there is a very long list of little things like this that don't work quite right.

The fix is simple.

Just extend the width of that error bar that acts as the grass, i.e. make it wider until it prints properly. Now doing this will require you to add two more lines to serve as the ZERO yard lines on each side of the field. And this is done in the same way as the five-yard lines, but they'll need to be their own series in the chart because you should make them wider than the five-yard lines.

After that, it should print fine. Let me know how you get on.

Regards,

Daniel Ferry
excelhero.com/blog

Daniel - Thanks for looking into it. I added the Goal Lines as a new series and experimented with the width of the grass. There was a small amount of grass between the goal lines and the endzone. I tried different solutions and settled on cheating a little by making the end zones a little wider. I changed the grass to 620pt and the endonzes to 78.25. This worked for me. Now I have an issue with the center logo not showing up on the plotter. It comes through on 11x17 so it may just be the plotter I'm using. Thanks again for the help!
KM

Just to pass along a solution to printing issues; The best solution I have found for printing this out is to save as a PDF file. This will eliminate most of the issues.

@Kennie-

Very good suggestion.

Does doing so eliminate the need for new goal lines, etc.?

Regards,
Daniel Ferry
excelhero.com/blog

A terrible Villa performace. It's been coming for a while. 3 points from 4 games. Same old story in the back-end of the season

I have been blogging since 2007. For years I made a few bucks here and there with Adsense and some affiliate sells, but I wasn't making enough to quit the day job for sure. I tried getting traffic from Adwords, SEO, Yahoo, article marketing, social sites, and even email. The problem was that I just couldn't get enough traffic to really make anything. Then I found out about a new way to buy traffic for so freaking cheap that it was nuts. If you're sick and tired of only getting a handful of visitors to your blog, check this site out. You will go from a few dozen visitors a day to thousands for pocket change ---> Cheap Traffic Secret

Hi
Where may I find the part 2 of this tuto?
BR
Eric

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on February 3, 2010 7:46 PM.

5 And 3 Is 1 was the previous entry in this blog.

NFL Drive Chart Tutorial - Part 1 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.