Our sister site AccountingWEB.co.uk and John Stokdyk lift the lid on the Excel 2010 KPI dashboard he used through the football season to pick his winning fantasy team. (Editor’s note: And by football, he’s referring to soccer.)
Now the football season is almost over, I’m crowing not about Chelsea’s triumph in the Premier Leauge, but ExcelZone Athletic’s somewhat less lucrative victory in the company Fantasy Football league.
It was a tightly fought contest in which I found it difficult to shake off the persistent challenge of my own Manchester United equivalent, Chic Geek City. But a consistent 8%-plus advantage over the course of the season was enough to see me through with a margin of 230 points.
What I can now reveal to my frustrated colleagues is that an Excel KPI dashboard made a key difference to my team’s success. Since the same techniques can transfer to wider business scenarios, this article sets out my methods and shows how Excel 2010 and its new Sparklines feature helped me win.
The Moneyball factor
Anyone who read Michael Lewis’s book, Moneyball, on the low-budget success of the Oakland Athletics baseball team will know that fantasy sports present a classic performance management challenge.
In Fantasy Football, points accrued by players during the season are based on different factors such as the goals, tackles and “assists” where a footballer makes the final pass to a goal scorer. These statistics are recorded and compiled into weekly totals.
Lost most similar simulations, the Yahoo! Fantasy Football league
we played allocated each team manager a £100m budget to buy players, with prices fluctuating each week according to their on-field performance and the demand for their services.
Guided by the principles of Oakland A’s manager Billy Beane, I recognised that success lay in getting as many points per pound as I could from my players. Doing so required a certain amount of arbitrage in the marketplace to identify undervalued players who could contribute the most to my team.
Goal-scorers and creative attacking players such as Didier Drogba, Wayne Rooney and Frank Lampard win the most fantasy football points, and consequently cost the most. Teams also need to include less expensive defensive players and goalkeepers to fit within the budget cap. The following information in the fantasy game’s database held the key to the performance drivers in my dashboard:
- Total points scored
- Player cost
- Average points per week
- Points scored per pound invested
The fantasy game’s database provided all this information. Not only was it available to all the other players, the sheer mass of data made interpretation difficult. To secure the best quality players at the lowest price in a constantly changing market, I needed an indicator that could help me track the most effective prospects against the market average to snap them up early when their prices were still low.
I decided that along with total points scored, the best KPI was each player’s average weekly points per pound cost. This measure would help me spot those players who players were defying the market norms on a consistent, improving basis.
Assessing this measure for 250+ players each week was not easy, but in-cell Microcharts and conditional formatting for their total points would give me the visual aids I wanted. The first tool I tried in tandem with Excel 2003 was Nanocharts
a free add-in from the How-to website. However I found the interface difficult to use and the results erratic and unstable. I had almost abandoned hope for my Fantasy Dashboard when I got my hands on the beta version of Excel 2010, whose built-in sparklines coped smoothly with the task.
Getting the data
During the first couple of weeks I collected the summary data on the top 250 players by cutting and pasting (Edit-Paste-Special-Unformatted text) from the Fantasy League website. I recorded a macro to eliminate extraneous text, standardise the players’ names and split data on their positions and teams into separate columns (using Data-Text to Columns) so I could analyse and sort them on those criteria.
After four weeks I had enough data to carry out my first experiments in Excel 2003. But one week the Paste Special-Unformatted text option didn’t work. Instead, I used the Data-Get External Data-From Web command to highlight the players’ key data and import it (12 at a time) into a statistical sheet for each week. When you click the Get External Web Data icon, it asks for a URL. When you see the page, simply click the small yellow arrows beside the data table you want and click the Import button.
Formatting the data
The information that came across contained a few extraneous phrases such as “sell this player” which were eliminated by a Search and Replace macro: Start the Macro recorder, Search and Replace for the key phrase, leave the Replace box empty and click Replace all; then stop the recorder.
Returning to the Macro Record option again (buried at the far right end of the View tab in Excel’s Ribbon menu) I carried out another set of search/replaces to put commas in front of the phrases “Goalkeeper”, “Defender”, “Midfield” and “Forward” as well as the first few letters of some of the team names. The commas would act as my delimiters when it came to splitting the text into columns.
To save time I built up my macro in steps – manually cutting and pasting the search strings for each team into the macro code I had originally recorded.
I then pasted in the commands from a separate macro I recorded to run the Text to Columns command. After a few trials and errors, I had a macro that would shuffle the player data into shape in about 20-30secs.
Then all I had to do was add an extra column, enter my “WkAvPts/£” formula (Average Points/Market price) in the top cell and control click to fill the formula down the player list.
Constructing the dashboard
Because the figures and player order changed each week, I had to go through the same data collection and cleansing process each time and find a way to link all the weekly data locations into the master dashboard. The various AccountingWEB tutorials on VLOOKUP
came to my assistance.
As long as the player names were consistent, I could use VLOOKUP on the KPI dashboard to call in the “WkAvPts/£” value each week to build up a sequence of performance data points. I used Simon Hurst’s advice to eliminate “N/A” error messages when VLOOKUP couldn’t find a player. Giving each sheet a Range Name helped the formulas work at one point and reminded me that I was looking at the most up to date data (PremWk2, PremWk3 and so on).
After more trial and error, here is the syntax that eventually worked:
As Simon explained, the first part of the IF(ISERROR argument checks to see if the VLOOKUP() function returns an error. The VLOOKUP then seeks a match of the value in cell 2 of the PremWk2 sheet and calls in the value of the 13th cell along the same row. If it does not find a match (quite common with the data I was using, the formula inserts a zero as indicated by the figure after the first two right hand parentheses. If no error is found, the second VLOOKUP() function brings back the desired value.
To update the Dashboard each week, I would have to add a new column to take in the new data and then change the “PremWk” range number in the VLOOKUPs for each of the Dashboard’s main indicators (current cost, total points, the current week’s points total and the all important WkAvPts/£ value). As the season progressed, the dashboard had to run 10 or more VLOOKUPs for the 250+ players I was tracking in database. More than once the sound of my laptop’s cooling fan accompanied the weekly number crunch.
Creating the KPI indicators
With a few weeks’ data gathered and my beta copy of Excel 2010 installed, I added a new column in the Dashboard worksheet and clicked the Sparklines option within the Insert menu tab.
The simplest of Line charts served my needs exactly to show a running indicator of each player’s WkPtsAv/£ in column H, based on the figures contained in the weekly totals displayed in the columns to its right ($I$2:$T:255 in this instance).
Then I selected the Total points column and chose a suitable Conditional Format from the Home menu tab (A Data Bar type with a blue Gradient Fill).
Throughout the season, I constantly scoured the Dashboard to spot high scoring players whose sparklines were heading upwards. Initially it bothered me that the default axes for the sparklines were not fixed, so I set the minimums and maximums to be 0 and 2 respectively. Once I had a significant pool of data, I decided that the Automatic options were visually more instructive.
From Excel 2010 sparklines to glory
A good deal of luck as well as footballing knowledge goes into a successful Fantasy Football team and I was extremely lucky to pick a team early on that was spearheaded by three of the seasons ultimate top scorers: Didier Drogba, Fernando Torres and Darren Bent (unlike real teams, you can choose three centre forwards in Fantasy Football if you want).
But I quickly found that the KPI dashboard was a huge help in spotting good value prospects – often unsung defensive heroes in the lesser teams such as Birmingham’s Roger Johnson, or busy attacking midfielders such as Hull’s hyperactive Stephen Hunt.
Almost as useful as the sparklines was the ability to filter the players by position and team. It became very clear that the big Chelsea, Manchester United and Liverpool stars were overvalued and that the best prospects were in less glamorous, but overachieving teams – Birmingham, Aston Villa and Fulham early on, Tottenham and Everton towards the end of the season. Clicking a single player category for two or three target teams in the Filter options gave me an opportunity to compare the players head to head – and then consider how many games and who they would be playing in the forthcoming weeks.
By the halfway point, I had established a 10% lead over my closest rival and congratulated myself on proving my statistical and managerial theories. Despite losing key players to international commitments and injuries, I was able to nurse my funds and players to carry the momentum through to the season’s end. As the spring arrived, I have to admit I became quite lazy about the weekly data collection and analysis.
But Sir Alex Ferguson would no doubt warn that complacency is the enemy of consistent high performance. Now that my league rivals know the secret of my success, I will need to improve my metrics and KPIs for next season.
I am also aware that the exercise exposed huge gaps in my Excel knowledge, but gave me the opportunity to improve them by working out the solutions through trial and error. It also proved to me that the Excel 2010 upgrade catered exactly for my management requirements.
Suggestions for improving the data gathering, formatting and presentation of my dashboard would be very welcome. Any readers able to do so will earn the thanks not just of me, but Fantasy Football players everywhere.