Some Excel for those of us who are a bit more Visual

[Jellico73]

Hi All,

Being relatively new to FM, I've been looking for some tools/ideas to help me track player development.  Now, either my search-fu has failed me, or there really isn't a lot out there.

I myself am more of a visual type of person, but then that only works if you remember to go and look every so often to see whats changed amongst your players attributes.  Yes, a colored arrow moving up or down is nice, but it's not to detailed, 

And while this is a nice presntation of information:

 

trying to use it to track one player at a time is annoying.

Luckily, there's Excel.

Here's what I do.

First, you set up an 'Attributes' view in squad.  Like this:

Then under the FM menu, you're going to select "Print Screen", and save as a webpage.  I have a reminder set up for the first of each month, so I save the file as 'MonthYear', ie 'May2023'

Open excel, drag the recently exported html file in, and after a little editing, you will get something like this:

 

NOTE:  IT IS VERY IMPORTANT THAT THE ATTRIBUTE COLUMNS IN YOUR SPREADSHEET REMAIN IN THE SAME ORDER FROM TAB TO TAB.  

Why yes, I did learn this the hard way.

 

So, the above is a screenshot of my roster from May, 2021.

This a screenshot of my roster from May, 2023:

Take a look at the guy at the very bottom, Zeca Gomes.  In May of 2021 he was a 16 year old D(L).  Some tutoring and first team playing time later, he's really improved.  I can tell by the amount and gradations of green, and lack of orange and red.

The key to this is Conditional Formatting.

First, you highlight your attribute data.  I usually go a few lines more because you can add or lose players, and your roster size will always change.

In my case, I go from C2:AL98

Once highlighted, you go to Home ->Conditional Formatting -> New Rule.

This equation is the basis for all your formatting:

=C2<(VLOOKUP($A2,'May2023'!$A$2:$AL$82,COLUMN(),0)-1)

What this is saying, in laymans terms, is look at the value of the cell in C2.  If the value of the cell in C2 associated with the name in A2 (on the sheet names May 2023 in the data contained within A2:AL82, and while your at it also  compare all the other values in the columns associated with the name in A2 as well), minus 1, is less than the value in C2, format the cell in this color.  It should look like this:

You use the formatting option to choose your color/fill/font schema.

The rest of the changes are just variation on the first formula.

=C2<VLOOKUP($A2,'May2023'!$A$2:$AL$82,COLUMN(),0) will return results that are 1 less than the number being looked at.

=C2>VLOOKUP($A2,'May2023'!$A$2:$AL$82,COLUMN(),0) will return results that are 1 more than the number being looked at.

=C2>(VLOOKUP($A2,'May2023'!$A$2:$AL$82,COLUMN(),0)+1) will return results that are 2 more than the number being looked at.

The only other thing I do is make sure the formulas are in the following order:

So, going back to the above, if you look at Zeca Gomes, in two years his Cor, Cro, Dri, Fin, Fre and Hea have all gone up by +1, while his Lon has gone up by +2

And the nice thing is about this formatting is that if a persons name isn't found, it's not going to screw anything up.  If I were to remove Zeca Gomes from my May2021 tab, in May 2023 he would have no color changes.

I've found this handy in that if I am training a specific attribute, once it raises by +1 or even +2, I can immediately stop training it and move onto a different attribute.  It also let me know that if after three months of training a particular attribute, if it doesn't change it probably won't, so don't waste any more time training on it.  

You can add as many columns to the right as you want, i'm looking at adding the training columns and a column for tutoring myself, in addition to personality.

If you think this is something you can use, I've attached a basic version of the spreadsheet.  Play around with it, use it, add to it if you have improvements.

If you've got a method you use, by all means lets share.

Thanks for looking and commenting,

 

Jellico73

 

SquadAttributesTestSheets.xlsx

[oakesypvfc]

This looks super useful and once I am able to get some decent youngsters in on my save, I will probably find it handy as a visual aide.

Top work 

[Sir_Liam]

I do this for my players to check how they're developing and use the exact same method with the print screen to web page too.

It doesn't look as good as yours mind, and I only get the data at 3 points in the year, but it's good for seeing how players are developing over a year, which helps in terms of deciding what to do with them, and in recognising how high their professionalism might be as consistently low development will probably indicate low professionalism.

[Jellico73]

49 minutes ago, Sir_Liam said:

I do this for my players to check how they're developing and use the exact same method with the print screen to web page too.

It doesn't look as good as yours mind, and I only get the data at 3 points in the year, but it's good for seeing how players are developing over a year, which helps in terms of deciding what to do with them, and in recognising how high their professionalism might be as consistently low development will probably indicate low professionalism.

I agree.  What I have right now is monthly tabs, and then a three month comparison tab.   The nice thing is as long long as you're referencing the right tab, you can do three month, six month, year comparisons and so on.

Eventually what I would like to be able to do is click on a player, select the months in question, and get a radial graph representation of the changes.  That's a bit beyond my skills at the moment though, but I am working on it.

Thanks,

 

Jellico73

[lukeeggy]

Anyone got one for this year?

[Jellico73]

Spreadsheet works for any year, just have to make sure how you export the team with attributes.

[jmb1403]

This is amazing, I used to do this for comparing players for their positions.

I'm just wondering say for example where it says position, if you put "Box to Box Midfielder" for example. It would fine an average for the key attributes for that position. Then you can also set filter to compare them with?

[Jellico73]

On 27/03/2019 at 08:02, jmb1403 said:

This is amazing, I used to do this for comparing players for their positions.

I'm just wondering say for example where it says position, if you put "Box to Box Midfielder" for example. It would fine an average for the key attributes for that position. Then you can also set filter to compare them with?

No, the spreadsheet doesn't do that.  I haven't seen/found one that does that for newer version of FM, the last one I saw was for FM16.

It could be done, but the behind the scenes stuff would take a very long time for me to complete.

[Jellico73]

@jmb1403, check out this thread too.

 

 

[Nobby_McDonald]

Christ my head hurts. I like it simple.

[jmb1403]

On ‎30‎/‎03‎/‎2019 at 18:31, Jellico73 said:

No, the spreadsheet doesn't do that.  I haven't seen/found one that does that for newer version of FM, the last one I saw was for FM16.

It could be done, but the behind the scenes stuff would take a very long time for me to complete.

Sorry yeah I did realize yours didn't do that, I have recently started a course in excel so was looking to implement it myself.

I used to use spreadsheets to compare players in the past