How the stats began

FootballWebQueryBack in 2011 ChorleyRocks felt he could do better at Fantasy Football by treating it as a game of stocks and shares. Some players (stocks) return more points (dividend) for the money invested in them than others. Chorley thought that combining his knowledge of football with his knowledge of stock trading software could be used to good effect in Fantasy Football.

After a lunchtime on Google, Chorley discovered the work of John Stokdyk, who used Excel to monitor & rate the points return on players. This inspired Chorley enough to look into how stats can improve his Fantasy Football team. And as they say, “the rest is history”.

The stats are a lot more comprehensive now than those early days. Yet seeing as John was the inspiration behind the first stats, we thought we would get in touch.

Hi John, how long have you been playing FF and how did you get into it?

Hi Chorley, I started fantasy footballing around the time they started running free games on sites such as Metro, Yahoo! and A couple of mates got me interested at the beginning, but my fantasy habit became more serious when someone formed a league at work.

At what point did you start to use stats in Fantasy Football?

I’ve always been a bit of a stats freak. I grew up in the USA and love baseball. I taught myself to use Excel by compiling stats for softball teams over here in the 1990s and cooked up a few of my own statistical measures. I got even more interested when I read ‘Moneyball: The Art of Winning an Unfair Game‘ by Michael Lewis about how the Oakland A’s baseball team used stats, which showed how the kind of analyses that underpin fantasy sports can transfer to real life.

What sort of information did you use and how did it help with player and team selection?

Following my softball experiments, I worked out my own fantasy football measurement of points scored per pound spent and used this to guide my early – not always successful – team selections.

The trouble with fantasy sports is that you need to devote a lot of time and attention to be successful – and that’s not something I am always able to do. I might be able to look back at who was successful the previous season – but the top performers always end up being overpriced and players’ form can dip disconcertingly between seasons. What I wanted to find was a way to unearth an undervalued but effective player, who often vary according to the rules of a particular fantasy game.

So you had a ‘player dashboard’! What did that look like and what extra information did it contain?

Ah, the dashboard. It’s become a 33Mb albatross hanging around my neck. It grew out of my work on, but ultimately sank under the weight of 38 weeks’ worth of VLOOKUPS on data for 500+ Premier League footballers.

Accountants are heavy, heavy Excel users who know their way around pivot tables and VLOOKUP. As part of my job I have to check the Excel tutorials<> we publish, which taught me some of these techniques.

I realised that fantasy football was an ideal test bed for an Excel-based key performance indicator (KPI) dashboard. You’ve got a bunch of potential employees and data on which you can compare them. The exercise requires you to decide what the key success driver is and to collect and analyse measurements to support your decision-making.

It was a great spreadsheet training exercise too. When I got hold of the beta version of Excel 2010 I realised I could incorporate the Sparklines charting feature to create visual patterns of the players’ points per pound ratios.

The dashboard is based around a master list of players that can be filtered by team and position, and sorted in ascending/decending order of price, points or ratio. Behind and connected to this master sheet are sheets holding the data from each week’s play. Because the player list was dynamic, depending on their price that week (or new players added in) I used quite a complicated VLOOKUP to identify the player and read along the row to pull the points/pound rating for that week (a score of more than 1 is good going in the leagues I’ve played in). Once I’d built up a sequence of weekly results, I added a new column and inserted Sparklines to make it easier to spot the ones on an upward trend.

The first year I used it, I credited the dashboard with helping me to outperform my colleagues by around 8%, which was enough to help me win the office championship.

This is what it looks like:


Why did you give up using the stats? Did your team do better or worse?

My VLOOKUP-heavy KPI model was too labour-intensive to maintain, and too unwieldy when I attempted to share it with fellow enthusiasts, none of whom showed much interest in getting regular email copies of a 33Mb, multi-sheet workbook.

For the past couple years I have had to rely more on intuition and dumb luck. I came second in the 2011-12 mini league, but like Manchester United bounced back to win the league last season.

Though the dashboard is mothballed at the moment, I continue to collect data and am looking at ways I might be able to make it less cumbersome and easier to maintain. It’s also a very handy way to test collaborative online spreadsheet tools. When I experimented with WindowsLive the file was too big to upload and view in a web browser, and the Office 365 web app also struggled. Other systems like Google can’t support the formulae, so I would have to program my own interactive database and website to share it more widely. When I crack the challenge, I’ll let you know.

Did you know you were the inspiration to this site?

I’m really pleased that all of my experiments haven’t been a total waste of time and that you’ve picked up the baton and built a whole website dedicated to fantasy football data analysis and prediction.

I recently found new inspiration from the book ‘The Signal and the Noise: Why So Many Predictions Fail – but Some Don’t’ by Nate Silver, who links fantasy baseball to successfully predicting US election results and why economic forecasting is so lame. There is a point to obsession about the weekend’s sports results after all.

Thank you John

Thank you.

If your readers are interested in accounts you can find me at


12 thoughts on “How the stats began

  1. So it’s his fault I got dragged into this????? Seriously, nice to know how it started, seems like a good bloke too.

  2. Thanks for the feedback and comments. Following this interview, I have dragged my dashboard out of the archive and am looking to enhance it and make it publicly available via the web. so if you have any ideas how it could be improved, there’s a whole discussion devoted to it on (look under Discussions for Excel Zone). Now that I’m getting a bit more serious about it FFF has really opened up my eyes, so many thanks too for building up such a sophisticated pool of knowledge.

    Two final points:
    1. I’ve seen that Nate Silver is switching from NYTimes (538 political predictions) to ESPN (for more sports-based blogs) – so it would be a really good time to see if he’d do a short interview, and to see if he’s up to the challenge of football prediction!
    2. The picture in the post here is just of the Get External Data from Web bit of the tutorial – I sent ChorleyRocks a copy of what the dashboard should look like and hope he’s got time to switch it. If not, you can also see it in our Excel Zone and if I ever get it to a workable web prototype, I’ll be sure to let you know here.

    • Hi John,

      Sorry for the delay in replying, I’m on vacation. I’ve got the new image & will post it here on Wednesday. Thanks for the tip about Nate, I’ve reached put to him :)


    • John, I couldnt find the discussion about improving your model as you have not provided a link. However, my point is that I also worked on a model last year, and one thing that I realized was that if we consider data for past seasons, then the team that we eventually form is good for last season only. Hence, for the model to be predictive in nature, it needs to take into account something from the near future, which is where the fixtures for the next season should come into play. Let me know what you think, and I am waiting for a copy of your model.

  3. Hi

    I stumbled on this site by accident. I have managed to pull the data and was playing with it in Tableau (it’s a data visualisation tool, I saw a couple of graphics on the Home/Away page), and had a mind to posting something on Tableau Public. It seems, from this page, that you’re a lot further on.

    I’m a Tableau consultant and would be really interested in helping if it might be beneficial. I’d love to get something pretty useful coming out of it.

    Best wishes


Comments are closed.