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.
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
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)
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.
Would You Rather have the Blue Pair or the Orange Pair)?
(Blue Pair Sums to 610, Orange Pair Sums to 670)
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!
- I Had never, ever, written a formula that required a function – at best I had done A1+B1 type stuff.
- The first time I saw Autofilter in action, I blurted out “whoa! how’d you get those little selector buttons into the cells??”
- 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:
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.
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
“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.
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.
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
The Draft Database App Even Gave Me a Real-Time Dashboard
on My Opponents’ Choices and Needs
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.
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.
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.”
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:
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.
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.