Compulsive Data Crunching Disease Pt1 (Relevant Even if You Don’t Like Football, I Promise!)

 
image

This spreadsheet-wielding Scrooge was the logo of my fantasy football team (“Mighty Ebenezer”) circa 2001.  Yes, I had this art custom commissioned for that purpose.
Yes, that is really, REALLY sick.  And simultaneously PURE FREAKING AWESOME.

Let’s start here:  Football Season is Spreadsheet Season.  Yes, even more spreadsheets than usual, even for me.   And football season starts tonight here in the US.  So it’s high time I tell you this story.

Even if you don’t care about American Football, or sports in general, I suspect you may still find the story itself to be interesting.

Deep down, this story is actually about a trait shared by probably everyone reading this.  And there is a TWIST in this story, a BIG one.  So go get another cup of coffee.  I will wait.

Scene:  Rob Collie, 1996-1997.  And Lots of Losing.

My Microsoft Badge Photo – Taken at New Employee Orientation, July 22, 1996My Microsoft Badge Photo – Taken at New Employee Orientation, July 22, 1996

I started at Microsoft in July 1996, fresh out of college.  I was raw, lazy, and unskilled.  I had never really applied myself to anything.  I had a computer science degree but had scrupulously avoided learning how to program.  I got the job largely because I was decent at logic puzzles, which were the MS interview du jour.

In August, one of the manager types on my hallway was starting a fantasy football league.  I had no idea what that meant, and I wasn’t really even a football fan at that point in my life, but it sounded social, and I needed to get to know people, so I said yes.

That first year of fantasy football taught me one important life lesson:  I was really, REALLY bad at fantasy football.  I finished dead last out of 12 people playing.

The following year, we did it again with the same people.  Of course I improved, this time finishing ELEVENTH out of 12.

Statistically speaking it was pretty clear that I Sucked, Big Time, at fantasy football.

The Core Analytics Problem of Fantasy Football

image

A “Cheatsheet” from 2002 Illustrates the Core Conundrum of Fantasy Football:
Clearly, Jeff Garcia is a “Better” Choice than Daunte Culpepper.
BUT is he “Better” than Marshall Faulk and Randy Moss, Too?

I’ll keep this brief:  Fantasy football is a lot like picking stocks, except that you are required to pick a certain number of players of each type.  (Imagine your stockbroker telling you that you needed to pick one Tech stock, three Energy stocks, three Manufacturing stocks, and one Agricultural stock, and then live or die by that portfolio of eight stocks for the next year.  If you replace stocks with players, and industries with player positions, that’s fantasy football in a nutshell).

And let’s say you just KNOW, with absolute certainty, who the best players are of each type.  You have separate lists of each type, ranked 1 to N, like the ones pictured above.  (In reality, no one has this knowledge, but everyone thinks they do).

But even with your incredible omniscience, you still have a BIG decision to make:  who do you pick first?  If you pick the top Quarterback, someone else is likely to take the best Wide Receiver, and someone else will get the best Running Back.  How do you decide?

In the beginning, we all just guessed.  Or we picked the Best Quarterback first, since that’s what the real NFL teams would do.  If it’s good for the NFL teams, it was good for us right?

In my third year of playing, 1998, I was about to turn our fantasy football league, and my life, upside down.

“Value Based Drafting” For the Win!
(…and a Lifelong Dependency on Spreadsheets)

image

OK, NOW We’re Getting Somewhere – the Dropoff Curves for each Position are VERY Different!

Denis Leary once said that the trouble with marijuana is NOT that it leads to stronger drugs, but that it leads to carpentry.  (The full-time pursuit of constructing better devices through which to smoke said marijuana.)

Similarly, the “trouble” with Fantasy Football is NOT that it leads to a football obsession.  The “problem,” in my experience, is that it leads to increasingly more sophisticated spreadsheets, and eventually to a career therein.

That fateful summer of 1998, I read an article on the web written by Joe Bryant.  It was called Value Based Drafting, and it introduced a mathematical approach to the Core Conundrum of “Which Player Type Do I Pick First, Second, Etc.?”

In short, “VBD” proposes that we pick players based on the predicted “dropoff” in value at each position.

image

Would You Rather have the Blue Pair or the Orange Pair)?
(Blue Pair Sums to 610, Orange Pair Sums to 670)

image

VBD hit me like a lightning bolt.  It was just so stinking obvious.  It was shocking, in a way, that we weren’t all following it already at that point.

But I had a problem.  Joe’s article was just words.  He provided no mechanism for putting it into practice – no tool.

I had to build that mechanism myself.

Of COURSE We Use “VBA” for “VBD!”

I knew I was going to have to use Excel for this.  But I didn’t know Thing One about Excel, even after two years at Microsoft!

  1. I Had never, ever, written a formula that required a function – at best I had done A1+B1 type stuff. 
  2. The first time I saw Autofilter in action, I blurted out “whoa!  how’d you get those little selector buttons into the cells??”
  3. I had not even heard of pivots.

By chance though, my day job had forced me to write a lot of VBA macros.  My first manager had used Excel’s VBA window as a place to write test automation scripts – scripts that did nothing with Excel, but instead tested the software being developed by my team (weird, I know).  And so he had forced everyone else to do the same.

So…  even though VBD was probably “doable” with formulas, I embarked on a VBA Journey:

image

A Sample of My Early VBD Macro Code – Functional Encapsulation, Thorough Comments… 
This was BY FAR the Most Disciplined and “Awake” Code I Had EVER Written

I spent most of my free time that summer writing the Mother of All Spreadsheets.  In my entire life to that point I had never been as focused, as disciplined, or as hooked as I was then.  (This was the first of many steps of “waking up” and becoming an effective human being.)

It wasn’t really a spreadsheet though really – it was a huge batch of macros that happened to use Excel’s grid as a place to enter data and display results.  I don’t think there was a single formula in the whole thing.

image

One of My Early VBD “Spreadsheets” – In Hindsight it Reflected a Very Poor
Understanding of Excel, but Trust Me it was DEVASTATING

The Rise of Crazy Mighty Ebenezer

In 1998, armed with my VBD application, I dominated the league, rocketing from 11th place to 1st.  In 1999, my friend Ben lucked out and beat me in the end, but I still finished 2nd.  I joined other fantasy leagues in parallel and started winning those as well.

My leaguemates and friends who used to make fun of me now said things like “oh great, I can’t wait to see how Rob beats us this year.”  I was now Feared and Vilified.  I had been lousy at evaluating football players.  But I was Lights Out at applying a Formula.  No one stood a chance.

Something that was FUN and EFFECTIVE!  I couldn’t believe those two things could ever intersect.

Just the Beginning

image

“And in the end I’d always offer them databases.”

-the Devil Himself

After a year or two, I realized that my spreadsheet only calculated the “falloff curve” ahead of time, and didn’t react to changing circumstances while we were picking our players.

This was like the “small thermal exhaust port” in my otherwise impervious Death Star.  It was time to fix that up.

 

image

A Weakness:  Between Ranks 9 and 14, There’s a “Flat Tier” at RB But a Dropoff at QB!
We Need Real-Time Feedback as the Draft (Player Picking Process) Progresses!

So it was time for a database app.  Nevermind that I had never used a database before.  I just knew that what I was trying to do NOW was a bit beyond Excel, and required more structure.

image

Yes, This Really Happened.  Lazy, “Hates to Apply Himself and Hates to Learn Things” Rob Collie Built Something Like This in 1999 Without Anyone Pointing a Gun at Him

image

The Draft Database App Even Gave Me a Real-Time Dashboard
on My Opponents’ Choices and Needs

image

There was Also Functionality In The Database App that SIMULATED the Draft from Now Until My Next Pick and Used that Information to Advise Me on What to do Now.
(Predictive Analytics???)

Back to Excel!

Don’t worry though, I was still populating that database app using an export from my Excel VBD spreadsheet.  And there was a lot more Excel work to come.

image

image

An Addict’s “Paraphernalia” – My 2nd and 3rd PivotTables, EVER, Were Used to Design a Better Scoring System for our Fantasy Leagues (of which I was now the administrator)
(And the First Two Pivots I Made Were to Balance the Talent Level Across those Multiple Leagues)

Even MORE Excel!

And then, once the season started, I couldn’t just be DONE with spreadsheets right???  I mean, player performance is in part dictated by their quality of opponent.  What if Player A and Player B have performed about the same so far this year, but Player A has a much weaker “slate” of opponents coming up and Player B has a much stronger slate?

Clearly I would want to try to “steal” Player A from my competitors at that point!  A classic “Buy Low.”  And likewise I would want to try to convince my opponents to acquire Player B from me (assuming Player B was currently in MY portfolio) – a classic “Sell High.”

image

My Strength of Schedule Evaluator – Identifies “Buy Low” and “Sell High” Players

A Led to B, B Led to C… 

When the Office XP release ended with the complete cancelation of the software product my team was building, I used my free time to position myself to “land” on the Excel team for the Office 2003 release.  Because, you know, I wanted to add features to Excel that would further advance my fantasy football numbers addiction.  Muhaha.

Five beautiful years of working on Excel passed.  I got to know the Analysis Services team really well – people like Amir Netz (file that away for now, it’s important).  I got to work on PivotTables, and Tables, and Cube Formulas.

And then, MSN announced they were building a fantasy football website.

I debated this carefully for five minutes, and then I left Excel for the MSN Fantasy Sports Team.

Of course, MSN Fantasy Sports was itself canceled after about 9 months.

But not before I had spent $150k of Microsoft’s money on the Great Football Project – my biggest “football spreadsheet” yet.  Muhaha.

Then the fantasy sports team got folded into Windows Live Search (later rebranded as Bing).  Working on WL Search…  Sucked.  Big Time.  It was just not satisfying work – not after working on Excel and Fantasy Sports.  I had to get out of there.  I started looking around for jobs.

…and then Amir Netz emailed me, at that precise moment, with a question:

image

Gemini = Power Pivot.  And the rest is history.

I saw what they were trying to build and how much someone like me could contribute.

I debated this carefully for five minutes, and then I left WL Search for Gemini.

Gemini was eventually renamed to PowerPivot, and then recently to Power Pivot.

If I had still been on Excel at the time Amir sent that email, I probably would NOT have had the courage to “jump” to Gemini, so I never would have worked on PowerPivot if I hadn’t left Excel for the short-lived Fantasy Sports team.

My path to everything – this site, the book, PowerPivotPro School, my interaction with all of you…  my entire career today…  it all literally comes back to reading that article on VBD.

But also to Compulsive Data Crunching Disease.

Preview of Compulsive Data Crunching Disease – CDCD

Compared to other diseases, CDCD is an epidemic.  It afflicts 1 out of every 16 human beings, or roughly 6% of the world’s population.  It does not discriminate, equally striking people of all demographics – gender, race, religion, economic status, sexual orientation…  pretty much 6% across the board.

CDCD doesn’t care if you are a “techie.”  It doesn’t even care how well you did in High School Math Class, or whether you even liked it.

And it can lay dormant, sometimes for decades, waiting for its chance to strike.

I first noticed symptoms in 1998.  When did it get you?

Let’s talk about this in Part Two.

Preview of the TWIST!

All these years I have credited Joe Bryant with the creation of VBD, and proclaimed myself the Only BI Professional Who Started With Fantasy Football.

This April however, I learned I was not alone.  And all this time, I’ve been worshipping a false god.

Recovering from this existential crisis has taken five full months.  But I am now finally prepared to talk about it publicly.

See you Tuesday, fellow CDCD sufferers.

19 Responses to Compulsive Data Crunching Disease Pt1 (Relevant Even if You Don’t Like Football, I Promise!)

  1. Awesome Rob!

    I love Joe Bryant’s daily emails. I didn’t realize how far back he went.

    My Dynasty team has sucked since I won it (and then Kurt Warner and Brian Westbrook both retired or started sucking) but I am totally rebuilding around a solid WR core. But with a 12 team keep 10 + 2 Taxi, we redraft 10 slots each year and NO RBs are available. It all comes down to rookie evaluation and finding opportunities for young players that will step into a more prominent role in a couple years. Crazy.

    You’re such a complete geek it totally rocks!

    • powerpivotpro says:

      Yeah those dynasty leagues are SUPER hardcore. I don’t play in those – it’s difficult to even use stats to your advantage really, it all comes down to football knowledge alone. I’m not going to DVR college games from Division II and then sit there studying them for hours :)

  2. Jeff Lingen says:

    Hilarious! I too have the same affliction but mine stems from developing a player statistics database I built for an NHL team back in the late nineties.

    I really enjoyed this, thanks for sharing!

    • powerpivotpro says:

      Somehow I am not surprised that Geoff and Jeff have the disease, and it expresses itself in the sports arena from time to time.

      A few guys from the Access and Excel teams at Microsoft made an app for the Seahawks a long time back, and that app was in use for many years. May still be, I don’t know.

  3. I don’t think I’ve ever read an article that describes me as well as this. I was a huge sports fan growing up, but purposely stayed away from fantasy sports for a while because I thought it was lame. As soon as I agreed to join a league I was hooked and spent hours building spreadsheets. I even started the website http://www.spreadsheet-sports.com. Now my day job is as a Manager of Business Intelligence where I’m using your book and resources from PowerPivotPro school to build analytics solutions for our clients. So, thanks for the inspiring article – I’m looking forward to reading part 2.

    • powerpivotpro says:

      Spreadsheet Sports! What an absolutely Badass site, can’t believe I’d never discovered it. Bryan I am glad to know that people like you are out there :)

  4. I always enjoy hearing how others got hooked on technology for the same reason I did: an obsession to become better at fantasy football.

    • powerpivotpro says:

      You are probably going to punch me for saying this Tom, but I don’t think I knew that FFL was your tech “hook.”

  5. John H says:

    This is quite possibly the best thing you have ever written, Rob.

    Unfortunately, I can’t seem to locate where to download the example?!?

  6. gohawks8 says:

    Maybe I do need to write a blog documenting my secret workbook of predicting NFL game winner each week ATS (when I have time). I’ve drawn first blood last night as my bet on Broncos-7.5>Ravens winning $9.09 (on risking $10).

  7. Lucas Wetzel says:

    “I debated this carefully for five minutes.” – Perhaps my new favorite quote. Think I might have to start using this one myself. :)

    • powerpivotpro says:

      Lucas I am seeing double. I just moderated a comment from you on the PowerPivotPro School site! :)

      In truth I think I debated leaving Excel for the fantasy sports job for a couple weeks. But the decision to leave Bing for Gemini was less than five minutes.

  8. Avichal says:

    Rob, great article. Came a little bit too late.Past week I refused to join Fantasy Football league with my 9 year old son, who is turning into an encyclopedia of football, because I didn’t want to be crushed by him. Maybe I’ll follow VBD and stand a chance. Muhaha.

  9. shikagoland says:

    Despite loving football I have never had even the smallest urge to play fantasy football…until now.

    I’m going to make my co-workers cry next season.

    That aside, I just discovered the twitter account for ESPN’s stats and info team and and I’m just like…THIS IS THE DREAM, MY FRIENDS.

  10. Mattew Braun says:

    What is the twitter account if you don’t mind? I am in several leagues and crunching numbers may help me.

Leave a Reply