SoccerDude: Lord Of Time

What do you think of my new title? Quite snazzy, huh?

So, from now on you can stop addressing me as SoccerDude or that guy over at Football Trader's Path or that wanker. No, from now on, it's a three-way fight between Dr. Who, Rod Taylor in H.G. Wells' The Time Machine and my good self.

The reason? Well, I have gone and invented a real life time machine, and I'm willing to let you all come along for the ride. That's right. I want you to strap yourselves in and let's embark on a fantastical journey together.


Okay, I'll stop with all the bollocks and start talking some sense. Seeing as you’ve all been such a good bunch of readers since I started my little blog, I thought it was high time that I gave you all a little treat. My blog has only been going properly for ten months or so, but in that time I’ve been lucky enough to build up a solid readership, which I’m really very grateful for. Initially, when I first started posting entries at the end of last year, there were days when I received no visitors at all and I felt like I was just writing into the ether – which indeed I was.

Since that time, however, I’ve managed to steadily build up a regular number of readers and, based on current projections of 8,000 to 9,000 visitor per month I'm heading for around 65,000 to 70,000 hits in my first year. Personally, considering the appalling slow start I made, I think that’s pretty good.

Anyway, with all you long-suffering regular visitors to my blog, I've decided that a little reward is well overdue. It’s just my way of saying “thank you” for popping in. Unfortunately I don’t have a chocolate cake large enough to go around all of you (and judging by some of your waistlines, that may be a blessing), so instead I’ve built a time machine from an old Excel workbook to share with you all – absolutely free of charge, and simply offered out of the kindness of my heart. You can’t say fairer than that, can you?

What’s it all about?
A few years ago, someone posted on one of the forums, asking for any links that showed the league table at any point during the season. These “snapshots”, the poster suggested, could be handy for some types of analysis. Well, I didn’t respond to the poster as I knew of no such web site, but in my mind I heartily agreed that this would be a cool thing to have at hand. So with that in mind, I decided to create my own version of what this poster was talking about, using the data available from football-data, some stickyback plastic and a small dose of Excel. It didn’t take very long, was fun to do and – speaking personally – I’ve found it quite a useful little tool. Thus was born the League Table Time Machine.

In truth, I did build this time machine quite some time ago but it was never properly finished and I allowed it to fall into disrepair, but I recently stumbled upon it hiding in one of my folders and thought I'd take another look. I liked it, so I've spruced it up a bit, combed its hair, cleaned its milk-bottle glasses and neatly-pressed its short trousers. In other words I've given it a little freshen up.

How it works:
The Time Machine can be downloaded towards the bottom of this post. First-off, when you open the workbook, you’ll have to enable macros within Excel, if you don’t have it enabled by default. Nothing will really work properly if you don’t do that. I originally wrote it at work when they had Office 2002, and so for compatibility with any of your old versions, I’ve left it in that format. If you decide to save it in 2007 or later format, do remember to save it with a .xlsm extension.

Within the workbook, I’ve created several worksheets named 2004-05 up to 2011-12 and I’ve plonked league data from the football-data website inside them. To show you how things work, I’ve put some Bundesliga data in the 2004-05 sheet and League One data in the 2006-07 sheet. All the rest contain Premier League data. I’ll explain later why I’ve done that shortly, but regardless of that you should be aware that these are all data-only worksheets and you can change or add to these as you like, so long as you stick with the worksheet naming convention. Alternatively you could simply copy-and-paste different data from different leagues into the existing worksheets. It really doesn’t matter.

The “brains” behind the whole thing is in the “Time Machine” worksheet. When you open the workbook onto this sheet, you will see a yellow section at the top. In that yellow section are three things that you need (and are able) to change. You don’t need to fiddle with anything else. These three controlling items are:

• The Year field (cell C2). Once you click into the cell, it will become a dropdown menu. This allows you to select one of the available datasheets mentioned previously. When you select a sheet, The Time Machine will automatically size the league table appropriately and display the league name (in G6). This is the reason that I placed different league data in the 2004-05 and 2006-07 sheets. If you select those years,  you will see the league resize automatically and the name of the league change.
• The “Selection” field (cell C3). This states whether you want to view the league table as overall points, or only the home table or only the away table. 
• The “Time Slider” field (cell K2 to M2). This is the actual time machine in action. Just grab the slider and move it right and left, or you can click the arrows to move one day at a time. 

As you pull the slider to the right, you might notice it’s a bit “laggy”, and there is some delay before the tables start to react. In some cases the delay can be quiet pronounced. I’ve reduced the raw data down to the bare essentials but, beyond that, there’s not much I can do about this so you’ll just have to put up with it. Anyway, regardless of that, when you do drag the slider, several things start happening at once:

• The Date field (C6) starts changing 
• The Week No field (C7) will also change if moved far enough 
• The league table will react, and teams will move up and down as you move through the season. 

Once you get beyond five matches, there is also another (perhaps lame) feature that I’ve added-in. This is the Prediction area, which you’ll find in columns Q and R. It’s fairly rudimentary in its calculations, simply extrapolating each team’s current points tally throughout the season to what they should theoretically get at the end of the season. The Q column shows the points the predictor thinks they will get, and the R column shows how far away from reality that prediction is. For example, if I go to the 2011-12 year and move the “Time Slider” across so that the date moves to 31 December 2011, I can see Man City sitting on top of the table and their points tally at that moment in time was 45. The huge brain of the predictor comes up with the stunning prediction of 95 points at the end of the season, but the R column is showing a red -6 figure. This means that Man City actually finished with 6 points less than the predictor suggests. If one team actually finish with more points than the predictor suggests, the figure will be green.
As I say, it’s a little bit lame, but you may find it helpful in some way.

Example of Predictor being overly-generous (by 6 pts) to Man City

What use is it?
Okay, so what is the purpose of this fabulous time machine? Initially, when you start playing around with the slider, it feels like fun and it’s quite cool to zoom through the season and see how each team rises and falls within the league, but there may also be some more practical uses for it. For example, you may want to consider using it to:

• Check which teams regularly have good/bad form when playing at home/away. You can snapshot particular months and then change through the years to see if there is any variation. 
• Pick a spot in the season (last day of the year is a common one), see where each team is and then see how they fare at the end. If picking a spot and then clicking through the years, do check that the date hasn’t moved on a couple of days when you change years. 
• Check how far out the predictor is when one/two/three months away from the end of the season. If only two or three points out, perhaps this could be useful. 
• Spot trends of overperforming/underperforming teams. The red and green cells in the R column column could be helpful here, allowing you to spot early point tallies that will probably not be sustained. Use these to analyse the current season. 
• Spot any positional trends that could be used. For example, 10th place at the end of the year rarely seems to improve on the predicted result. 
• How often do teams escape relegation with one/two/three months of the season remaining? 

So on and so forth. I'm sure you lovely intelligent people get the idea. Just play around with it and see if any little scenarios pop-out at you.

Improvements:
How could I improve my time machine? Well, there’s quite a few things I could do really. I may at some point get around to doing them, but who knows:

• Make the year selection based on the worksheets actually present. Currently, I’ve just created a list of years and those sheets may, or may not, exist. If it doesn’t exist, a warning is issued, but it would be better to knock-up a bit of VB to grab the list of available sheets instead. 
• Write it in C# or C++ 
• Have the data automatically downloadable, instead of having to add it all in manually. 

Limitations:
This time machine, although completely fabulous (as I'm sure you'll agree), is generally intended to be run on previous year's data, rather than the current year. It will of course work on the current year, but there are some considerations.

First, because it creates a dynamic list of team names from the raw data, the manner in which it does so means that, unless each team has played at least five matches, the table won't populate properly. That's something to be aware of as you may say, "Huh? How come there are only 17 teams in the Premier League?" It will be because not enough games have been played.

Also, the second part of the Predictor will of course be meaningless as it has no way of knowing what each team's final points tally is going to be.

One other limitation is that this table is not going to work for the Scottish Premier League, which runs a split league format after 33 matches have been played. The overall table doesn't make any sense after that point.


Download:
Okay, so, here it is for your delectation. The workbook is called LeagueTableTimeMachine.xls, and I’ve popped it in a zip file.  Hopefully it will prove to be useful to some of you, but after downloading it and deciding it’s a pile of crap, don’t be embarrassed to delete it from your PC.

I promise not to get upset.

Download The Time Machine HERE

__________________________________

By the way, what a cracking match tonight between Real Madrid and Man City. Amazing at the end.