Player Analytics FM14 Style - A Pet Project

*** UPDATE ***

Here's a link for the latest version of the FM player analytics spreadsheet, with a bunch of new data being captured and manipulated. Feel free to give it a go, and shout if you have any issues/suggestions. 

Just remember that this is a macro-enabled Excel workbook, as such you may need to store it in a Trusted Zone or manually enable macros each time you load the document. 

Cheers. 

https://drive.google.com/file/d/0B090aaUk7oDZcmNjRHJ5VE1EaHc/edit?usp=sharing

There won't be an update on the Karlsruhe save this week, in fact due to a lot of social activities falling close together, and some plans that I’m trying to finalise around a short-term World Cup save*,  it might be a few weeks before we get to see Der KSC in European action. My apologies for that.

*(this will have relevance to the Karlsruhe save, all will become clear)

That doesn’t mean I don’t have a little something for you though, and it’s the result of reading recent updates by @MrEdsFM in his fine blog The Coffeehouse: FM Discussion.



Like a lot of people these days, Eds has taken an interest in looking beyond the obvious stats like Average Rating to try and get a clearer idea of who his better performers are, and in a recent update it was interesting to note that he was looking at things like Interceptions and Mistakes to assess his defenders.

To this end he’s created a spread sheet which entails him inputting a lot of manual data, in order to give him the analysis he wants to hand in a single view. I like this idea, particularly because I’ve recently been learning a fair bit in Excel, and I’m always keen to have a reason to put that learning into practice. I believe that most, if not all of these stats can be found in FM14, but not necessarily all on the same screen view. In addition, once you progress to a new season, you’ll lose some of it.

Cue nerd mode, and a couple of hours spent messing about to try and create a usable tool.

The result is an Excel document with a bunch of macros embedded, designed to allow you to input stats after a match is finished and keep a live view of your players’ performances throughout the season. You can keep multiple copies, allowing you to compare previous seasons, and if you’re even remotely Excel savvy then you’ll no doubt have your own ideas about how to pull things together from the different seasons to compare.

CONCENTRATE, HERE COMES THE TECHIE BIT

First up, before we dive in, this is very much a work in progress. I wanted to get something running that showed enough functionality for people to decide if it was worthwhile, without me having to do the whole kit and caboodle on the first run through. You’ll notice that there are less stats available than in Eds’ version, but I’ll address this at a future point if there’s enough demand.

I wrote this on MS Office 2010, and have tested it on 2007 – it works fine on both, I don’t have the facilities to test on older/newer versions of Excel. I have no idea if it would work on Open Office, I’m not sure whether compatibility extends to macros.


Right, with that out of the way a couple of disclaimers and helpful hints:

1. I am very much a novice in terms of Excel and macros, so if anyone wants to go digging around my VBA code then they’ll probably find it quite inefficient. That said, it’s doing just what I want it to.
2. Because of the way that the macros work it’s important that once you’ve input your Player Names (and optional Positions), you do not re-sort the data on the first two tabs in any way – this will likely result in the corruption of your data. What I have done is include a third tab where all of your data will get updated in ‘real time’, and where you can play to your heart’s content. Note that whatever you do here will be overwritten the next time you run the macros.
3. In a similar vein to the “no sorting” comment above, if you add or remove any columns or rows to either of the first two tabs on the document, the macros will ‘break’ and the document will stop working as intended. 

I’m sure that there are ways around points 2 and 3, but I refer you to point 1.

QUICKSTART GUIDE

OK enough of that, what you really want is a quick summary of how to use the thing, right? Simples...

Download the file (link below) and save it to your machine somewhere. Note that macros will need to run therefore, depending on your settings, you may need to save this into a Trusted Zone – Google it if you need to know more about that.

You may also need to ‘Enable Macros’ when you launch it each time.

I’d recommend then making another copy and renaming it (or whatever you want to do in order to retain a backup). You know, in case you go inserting columns and breaking the macros ;0)

Now we’re ready to go...

On the first sheet (Input Data) input your player names and, if you want to, positions. When you input these fields they’ll automatically carry through to the other tabs. Don’t worry, once you input a name it will appear in black text rather than the current grey.

I recommend saving the document at this point, with a file name that reflects the team managed and the season.

After each game simply input the post-match data as follows:

Select the eleven players who started the match by changing the ‘n’ in the “Start?” column to a ‘y’. Note that I’m not collecting any data specific to Goalkeepers, so you may want to exclude them.

For players who come on as subs input the time that they took the pitch in the column labelled “Subbed On (Time)”. Make sure to also input the time that the subbed player came off in “Subbed Off (Time”). You’ll now have a bunch of cells that have turned green – fill them in with the post-match data, it should all be fairly self explanatory.

Please note there’s no validation around numbers of players, times subbed, etc – it’s entirely possible for you to input data that suggests a fourteen man starting lineup, with another fourteen coming on after 10 minutes with no-one leaving the pitch. I’m sure that there are ways to lock this all down, but frankly I have neither the time nor the inclination to go that far.

Now, go to the second tab (Stats Summary) and hit the ‘Update Player Stats’ button. Save your document, you’re done.

The match data on the first tab has been cleared, player histories have been updated, and the latest data from them has been copied onto the third tab (Data Manipulation) where you can do all the messing about you like without fear of breaking anything.

Simply repeat the above process after each match and things will tick along nicely. At the end of the season you can either scroll down on the second tab and hit the Clear All Data button to blank everything (except Names and Positions), or you can create a new file from your original blank one.
That’s all there is to it, if you want to give it a go then please feel free to grab the file from...


my Google Drive (let me know if you have any issues accessing this)


...and let me know what you think. Unfortunately it is still a little labour intensive, and therefore probably not for everyone, but I’m hoping that it does take some of the pain out of manually compiling the whole thing.

If enough people are interested then I’ll consider extending this to include the other key fields from the post-match stats that FM14 throws at you. I may need some help top track some of them down though, for instance I know where to find ‘Mistakes’ in a player’s history, but not in the post-match stats.

That's it, no football this week, but as usual please feel free to drop me a Comment or catch me on Twitter at @flipsix3_FM