Excel 2010 + fantasy football = victory

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.
 
Performance drivers
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:
=IF(ISERROR(VLOOKUP(A2,PremWk2,13,FALSE)),0,(VLOOKUP(A2,PremWk2,13,FALSE)))
 
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.

This blog

AccountingWEB is more than just a U.S. team of journalists and financial and technology experts - we have an international side, too. Members of our British team who publish AccountingWEB.co.uk share their ideas, insights, and perspectives from across the pond.

More from this blog

Bloggers crew

Steve Knowles has spent 25 years in business and practice in the UK, but he also worked in the states and the years haven't dulled his way of seeing an alternative view to everyone else, and every day is a new adventure.

45474

Joel M. Ungar, CPA is a lifelong resident of the Detroit area and a graduate of The University of Michigan. He is a principal with Silberstein Ungar, PLLC, a Top 15 auditor of SEC public reporting companies.

78178

Allan Boress, CPA, with over 25 years as a practitioner and consultant to the accounting profession. Mr. Boress is the author of 12 published books in 6 different languages, including a best-seller, The "I-Hate-Selling" Book.

50529

Larry Perry, CPA, CPA Firm Support Services, LLC, is the author of accounting and auditing manuals, author and presenter of live staff training seminars, and author of webcast and self-study CPE programs. He blogs about small audits, reviews, and compilations.

92054
Sandra Wiley, COO and Shareholder, is ranked by Accounting Today as one of the 100 Most Influential People in Accounting as a result of her prominent role as an industry expert on HR and training as well as influence as a management and planning consultant. She is also a founding member of The CPA Consultant's Alliance. Sandra is a certified Kolbe™ trainer who advises firms on building balanced teams, managing employee conflict and hiring staff.
22239

Maria Calabrese, CIR, Human Resources manager for Fazio, Mannuzza, Roche, Tankel, LaPilusa, LLC in Cranford, New Jersey, Maria's topics revolve around the world of: Mentoring, Performance management, and The "Y Generation," a.k.a. "The whY generation".

57764

William Brighenti is a CPA, Certified QuickBooks ProAdvisor, and Certified [Business] Valuation Analyst, operating an accounting, tax, and QuickBooks consulting firm in Hartford, Connecticut, Accountants CPA Hartford.

83038

Ken Garen, CPA, is the co-founder and President of Universal Business Computing Company (www.ubcc.com), a software development firm of high-volume, high-productivity accounting and payroll technology.

26354

Eva Rosenberg, MBA, EA, is the publisher of TaxMama.com, and author of the weekly syndicated Ask TaxMama column. She provides answers to tax questions from taxpayers and tax professionals worldwide.

66343

Amy Vetter, CPA, CITP is the CPA Programs Leader for Intacct Corporation responsible for leading the CPA/BPO Partners nationally.

35882
Brian Strahle is the owner of LEVERAGE SALT, LLC where he provides state and local tax technical services to accounting firms, law firms and tax research organizations across the United States. He also writes a weekly column in Tax Analysts State tax Notes entitled, "The SALT Effect." For more info, visit his website: www.leveragestateandlocaltax.com
105333
Scott H. Cytron, ABC, is president of Cytron and Company, known for helping companies and organizations improve their bottom line through a hybrid of strategic public relations, communications, marketing programs and top-notch client service. An accredited consultant, Scott works with companies, organizations and individuals in professional services (accounting, finance, medical, legal, engineering), high-tech and B2B/B2C product/service sales.
27600

Rita Keller is a nationally known CPA firm management consultant, speaker, author, mentor and blogger. She has over 30 years hands-on experience in CPA firm management, marketing, technology and administrative operations.

54691
Stacy Kildal is the mom of two fantastic kids, an Advanced Certified QuickBooks ProAdvisor, Certified Enterprise Solutions ProAdvisor, Sleeter Group Certified Consultant, a nationally recognized member of the Intuit Trainer and Writer Network, and co-host of RadioFree QuickBooks.
29533
Michael Alter's blog specializes in providing practical advice to those who seek greater profitability and practice management tactics that enhance deeper client relationships.
33930

Sally Glick, CMO, Principal, Marketer of the Year in 2003 and AAM Hall of Famer in 2007, leads a lively discussion of the constantly expanding roles of marketing and the professional marketers that drive this initiative in accounting firms of all sizes.

103434

The IMA Young Professionals Blog features the insights of IMA’s Young Professionals Committee. Committee members share advice and experiences on careers, continuing education, work/life balance, and other issues affecting young accounting and finance professionals.

35238

FEI Financial Reporting Blog provides highlights from SEC, PCAOB, FASB, IASB, and other regulatory news, including reporting under Sarbanes-Oxley Sect 404. It is written by Edith Orenstein, Director of Technical Policy Analysis at FEI.

114522

Sue Anderson has 30 years of experience in continuing education for accountants. Currently she is the program director for online CPE provider CPE Link.

63317

Jim Fahey is COO of Apple Growth Partners, a regional CPA firm in Ohio. His focus is on the effective and efficient use of technology within the firm by all team members.

41204
Caleb Newquist is the Editor-in-Chief of Sift Media US, overseeing content for both AccountingWEB and Going Concern.
68388

Leita Hart-Fanta, CPA, CGFM, and CGAP is the author of "The Yellow Book Interpreted" and owner of Yellowbook-CPE.com a website devoted to training for governmental auditors.

94520

AccountingWEB is more than just a U.S. team of journalists and financial and technology experts - we have an international side, too! Members of our British team who publish AccountingWEB.co.uk share their ideas, insights, and perspectives from across the pond.

55577