The Making of FM squad analyzer (spreadsheet)

[Zanman777]

I've always felt there should be way more tools in Football Manager to analyze teams - especially our own. The data is all there, and the sky is the limit. Sports Interactive would make a major breakthrough in the series if they ever made Football Manager mod-friendly, exposing some kind of API to process the game data. It would help tremendously to play the game, and open limitless possibilites. The commnity contributions to the game (via tools, scripts, you name it) would simply explode, making the game exponentially more fun to play. For example, I'd really love to be able to view my players' attributes in a way that provided cues as to how good the attributes are in league standards. Lower league management is very different from top league management - having such a possibility would help gain perspective when managing different teams. With this in mind...

...I created a Libreoffice Calc document ("Excel") into which you can paste the attributes of the players from your Football Manager squad.
The main purpose of this spreadsheet is to color the attribute cells of the players according to how good / bad the attribute is for the league of your team. For example, if a player has 14 acceleration and the max average acceleration on the league is 12, I want the cell to turn green. On the other hand, if the min average acceleration were to be 16 I would want the cell to turn red.

Spoiler

Just a reminder of how to export the squad data from Football Manager:

Spoiler

• From your squad screen, click FM -> Print Screen.

• Select "web page", click OK.

• After you've saved the file, open it (eg. with a browser). Select all the rows and columns and copy (CTRL + C).

• You can now paste (CTRL + V) the data into a spreadsheet, like the one I'm talking about now!

 

So anyway, the spreadsheet I'm making is meant to help you interpret your squad's attributes by comparing it with the league's average attributes.

Spoiler

 

This is where the various tabs come in. There are tabs to insert the average attributes on the league. More specifically, there is one tab to insert the average league attributes for each general position:

• Goalkeepers
• Defenders
• Midfielders
• Strikers

Spoiler

 

For each attribute, there is a max average value and a min average value, that you can find out ingame via Team report -> comparison.

Spoiler

 

The tab where you insert the min and max average attributes for each position looks like this (goalkeeper tab displayed as an example):

Spoiler

 

--

The essential Libreoffice Calc feature that will allow me to color the player attributes the right way is "Conditional formatting". This is what will allow me to make Calc color the cells for me, depending on how good or bad the attributes are in league standards. 

Spoiler

 

The hardest part will be to have each cell compare its value with the right "minimum" and "maximum" values (the ones from the league, that is).

The threshold values depend on the attribute we're talking about, but also on the player's position. For example, if the cell is a defender's acceleration, we might want to compare this value with the league's defenders acceleration. So, we need to figure out a way to embed on the Calc sheet an option to pick each player's position. Only then will we be able to tell Calc whose attributes will we compare the player with.

This is where the first column comes in:

Spoiler

 

In this column, we can choose which position Calc should assume for each player.

--
 

Ok, so, so far we have our squad attributes on a sheet. We have a way to pick the assumed position for each squad player. And we have the min and max league attributes for each position. Cool! So all we have to do now is compare the attributes from each player with the attributes of the league players in the same position. Right?
....
Well...

What if, instead of comparing our defender's speed with the league defenders speed, we could compare our defender's speed with the league's strikers speed?

That would make more sense, right?

We could then predict if our defender would likely be outrun by most strikers. Honestly, only rarely do defenders from opposing teams challenge for a ball..
 

This is where the problem gets hairy. Very hairy. We need tofigure out a way to store, on the Calc sheet, what we will compare each position's attribute with.  Best explained with examples:

• A defender's acceleration shall be compared with the league strikers' acceleration
• A striker's height shall becompared with the league defenders' height
• etc

This is where the last tab comes in:  "Configurations". This tab holds the position profiles - where we program what each attribute from each position will be compared to.

Spoiler

 

The possible options for position profiles are inserted on the first column. In this case, we have:

• A profile for goalkeepers
• A profile for defenders
• A profile for midfielders
• A profile for strikers

Spoiler

 

Then, for each profile (which corresponds to one row on the sheet), we specify where to get the comparing value for each attribute (which corresponds to one column on the sheet).

Spoiler

 

Now, back to the Team data, the sheet that holds our squad's information anda ttributes. There, the conditional formatting will be set up to find the comparing value from the right cell (determinedin the "Configurations" tab), depending on the cell's attribute (first cell from that column) and the assumed position for that player (first cell from that row).

Spoiler

 

The formula I wrote to make all that processing, unfortunately, is ridiculously long, unreadable and... well, a mess. IT was the only way I figured out to solve the problem I had, though.

Spoiler

Anyway, I'm going to attach the spreadsheet file as it is now, so you can explore it, fiddle with it, and whatnot. It's the .ods file. Libreoffice is better to open it, there might be some incompatibilities with Microsoft Office.

At this moment, the sheet is working - player attributes higher than the league max average attribute are lighted green; lower than the league min average attribures are lighted red. players with only one prefered foot are lighted red; players with proficiency on both feet are lighted green.

• There is one caveat though - if a league's minmax attribute column is left blank, the value is assumed to be "0" by Calc. So any possible attribute value (1-20) will be higher than the league's max, and the cell will be lighted green. Argh... I will need to figure out a workaround. If you have any suggestions, they will greatly appreciated.

There are more details, caveats and whatnot, but for now I'll keep the post like this, without digging even more into the nitty gritty. The post is very long already.

 

I hope you find this somewhat useful and inspiring. 

Cheers, everyone

FM data processor final 2.ods

Edited November 29, 2019 by Zanman777

[forameuss]

There was very brief chat about an API, although that may have been for FM LIve right around the time it started its death rattle.

It's a really untapped market, I believe. Maybe they're reluctant to hand over much control in that way, which is their perogative, but I think you could end up with some really great stuff.  

With a cynic's hat on, perhaps they worry that people might build a "better" version of something that's in-game (better being subjective of course) so want to keep everything in-house.  Who knows.

[LordAEM]

So what View are you using to get all the attributes in? Like im looking but i only see options of partials. and the screenshot in the spoiler is way too small

[Andros]

Would love FM to have more options in getting the average ratings for the league.  I mean what to classify a FB? A DM? A winger? Are they defenders, midfielders attackers?  I guess it depends on your system.  Whilst I can change the classification in your spreadsheet (or make new ones for my team), I can't then compare them to other team's average.  

 

I wonder if something can be done with player descriptions and comparing them?  I know this would require huge amounts of work, but say I would get all the "attacking full backs" for the league and getting the attributes for them and creating your own average?  Then perhaps they may also need to be divided into whether they are developed or developing (or star, first team, backup etc) in order to get an idea of how they compare to other players that are in a similar stage of development rather than having the average attribute changed by youth players etc.

 

I am fully aware that means going through every team and analysing them! Ouch! You seem to love creating spreadsheets for analysis (including role analysis!), any chance of a spreadsheet that automatically analyzes every team and creates user changeable averages? (just kidding!)  

[LordAEM]

a Full Back is a Defender, a DM a midfielder (Defensive midfielder), an AM is an attacking Midfielder

 

 

 

 

[Andros]

35 minutes ago, LordAEM said:

a Full Back is a Defender, a DM a midfielder (Defensive midfielder), an AM is an attacking Midfielder

 

 

 

 

but are they really? a FB in an attack role is a defender?  Can the FB Att be grouped in with a CB? An anchor man DM is a midfielder? Just like a Just like a CM attack? an AM Advanced playmaker is the same as a shadow striker? 

 

Therefore, when averaging defenders, a team with FB that are on defend duty will have different averages than those that have attacking Fbs.  In addition, maybe crossing on defenders averages about 9, but for a Cb it is 7, for a FB it is 12.  An average winger may have crossing at 14 whilst an IF has 11.

 

So my argument is more in-game categories in the position comparison would be beneficial.  I like the work that Zanman777 is putting in, I think we just need more data to make it work better.  If the game doesn't have the tools for this, then perhaps the community can start creating the analysis.

[LordAEM]

Well what you can do is filter your squad. Just to show Defenders/Midfielders/Attackers. That way you know what the different players are.

[LuukBlank]

Is there anyway that you have can upload this again?

Could really help me

[LuukBlank]

Question,How do you implement your own data without removing the conditions?

[DementedHammer]

On 30/11/2019 at 00:17, forameuss said:

There was very brief chat about an API, although that may have been for FM LIve right around the time it started its death rattle.

It's a really untapped market, I believe. Maybe they're reluctant to hand over much control in that way, which is their perogative, but I think you could end up with some really great stuff.  

With a cynic's hat on, perhaps they worry that people might build a "better" version of something that's in-game (better being subjective of course) so want to keep everything in-house.  Who knows.

I'd love an API, even if it was a read-only one. There would be so many useful things that could be done with easy access to FM game data.