***ProZone's FM10 Squad Analysis Tool*** (Requires MS Excel 2007)

[updated on 16 DEC 2009] Downloaded 882 times to date

Welcome to this thread…

I have seen many posts in this forum recently requesting advice on how best to employ a particular player. What Role would Player X be most suitable for?

I have developed this Squad Analysis Tool to quickly determine the solution, plus do the same for coaching staff as well!

----------

There are now two versions of the FM10 Squad Analysis Tool available, to cater for two types of Football Manager:

1) If your management style is to design a formation to accommodate your players depending on their Best Role then this is the link you need (v.4):

http://www.filefront.com/15150123/ProZone%27s%20FM10%20Squad%20Analysis%20Tool%20v.4.xlsx

2) If your managment style is to choose a formation and then find the Best Role for your players within that formation then this is the link for you (v.5):

http://www.filefront.com/15150135/ProZone%27s%20FM10%20Squad%20Analysis%20Tool%20v.5.xlsx

----------

Both downloads include the default Man Utd squad and staff to demonstrate what the tool produces.

Text and numbers in black are required to be input by the user. Text and numbers in blue are the Squad Analysis Tool output. Please be careful not to delete anything in blue when removing or replacing data otherwise the tool will no longer function correctly. Also, do not highlight and drag data, always copy and paste! Simply delete the Man Utd data and write in your own. I have deliberately left the spreadsheet unlocked to allow you to tailor it yourself. I like to colour certain columns I consider important.

Users are required to manually input the attributes data found on each Player Attributes screen. This is not a cheat tool it just uses the information already available on screen and helps to make sense of it.

The calculation process is relatively simple. The attributes highlighted on the Tactics Screen for all the different Roles available in FM10 are used to determine an average score for each Role. I’ve produced it this way to be compatible with this year’s Tactics Creator. All attributes have identical weighting. The scores are compared in order to distinguish the Best, 2nd Best and 3rd Best Role for each player.

When I say all the Roles, there is a catch! MS Excel 2007 is only capable of making so many calculations in any given cell. So, I’ve had to break things down a bit. Therefore, players in a squad need to be placed in the relevant sections; Goalkeepers, Full/Wing Backs, Central Defenders (inc. SW), Wingers, Central Midfielders (inc. DM and AMC) or Strikers. This means that if a player is natural at GK and ST then you will have to enter the attributes data in both the Goalkeeper and Striker sections. A simple way of comparing them against each other is described next.

To the left of each player name you will see the Best Role column arranged [Number] [Text] [Number]. Take Carrick as an example, the Best Role column reads: [2, Deep Lying Playmaker, 78]. The tool has determined from Carrick’s attributes that Deep Lying Playmaker is his Best, or equal Best, Role (No surprises there!). How can we tell it’s his equal Best Role?

The 2 indicates there are two Roles which Carrick is equally adept at playing, the equal Best Role is then shown in the 2nd Best Role column. In most instances players are best suited to a single role in which case the tool outputs ‘-‘ and the 2nd Best Role result is truly the Role the player is 2nd best at.

The 78 is a percentage calculation of how good Carrick is in playing this role. If Carrick had 20 scores in all the attributes relevant to the Deep Lying Playmaker Role then this value would of course be 100. It is also a useful way of comparing two players who have a similar Role. For example, if Player A reads [-, Full Back, 65] and Player B reads [-, Full Back, 76] then Player B is a better player in the Full Back Role.

The percentages for Staff work slightly different in that 100% is the lower limit required for a 5 Star rating.

I would strongly encourage you all to use this tool for what it is intended to do which is identify the best Role for any given player. In the example I have given comparing Player A and Player B, the 65 and 76 scores give a quick indication as to who might be better.

Consider now that both Player A and Player B are AMCs and are totally identical e.g. both read [-, Advanced Playmaker, 79]. There are many combinations of values which can produce this 79 result but if Player A has Passing: 20 and Off The Ball: 14 and Player B has Passing: 8 and Off The Ball: 15 then Player A is 99.99% more likely to perform better playing the Advanced Playmaker Role. The better FM players will still use their own judgement in directly comparing similar players.

Finally, I’d like to say thanks to Lam for being the first to test this tool and share his comments. Have a go! Enjoy! I hope it is as useful for you as it has been for me.

James (ProZone)

Edit:

Another huge credit to Lam for developing a method to get the Player Attributes directly from FM Genie Scout. This offers a considerable time saving when entering the Player Attributes into the Tool, especially if you want to update your squad during the season, end of season etc. The whole process is reduced to a simple copy and paste exercise! The link to the step by step guide on how to do this is here:

http://www.filefront.com/15164039/Instructions-for-Exporting-Player-Attributes-from-FM-Genie-Scout-10.pdf

For those of you who don't have Excel 2007, I am reliably informed (thanks ForestFan67!) that this tool works with the opensource software OpenOffice available here:

http://www.openoffice.org/ (See post #35 for additional info.)

Alternatively there is a 60 day free trial of Office 2007, which includes Excel 2007, here:

http://trial.trymicrosoftoffice.com/trialukireland/product.aspx?sku=3082920&culture=en-GB

Link to new thread: ProZone's FM10 Squad Analysis Tool ***New & Improved***

Hey all,

I have tested this and it is very very usefull.

Some of the major benefits I have established already over some of the other great tool out there is that it allows you to compare your players to the whole of your team. You can see everyone at once (depending on your use of the worksheets)

The other usefull tool is that I have started changing training programs AND player positional training for some youths when it showed that they might be better in other roles. I found this particularly usefull as on many occassions I am unable to look 'past' the poor attributes of a youth player. Here it is presented nicely in a % is which hard to miss !!

LAM

ps.... you need microsoft xl.

So when i input Dzeko's stats in, the best position for him was in fact a Poacher according to your spreadsheet. Intriguing indeed, as i always set him up as a targetman.

So when i input Dzeko's stats in, the best position for him was in fact a Poacher according to your spreadsheet. Intriguing indeed, as i always set him up as a targetman.

Firstly, thanks for trying it out

Something to remember is that everyones game will be slightly different. On mine, at the very start, Dzeko is best as a Target Man (Attack) and second best as a Poacher.

This actually raises a very interesting point and is something else I am currently working on... training. The way a player is trained will modify his attributes. What we need therefore are training schedules for each Role. (I recently posted in a thread by rekluse on this, Justified has also posted something similar). Once we identify a player's Best Role we need to ensure that any training schedule is suitable for the Role and doesn't turn him into something we don't desire.

Anyway, back to your original comment. Dzeko's Poacher and Target Man rating might be quite close. If you unhide columns AQ to BF you can check how close the scores are in the table. On my save the difference is only 1%.

Great tool, thanks very much:thup:

Looks like a sweet tool. Anyway you could get this to work with OpenOffice? I can't edit in new player values

The latest versions of the tool allow you to see the scores for the 2nd Best Role and 3rd Best Role meaning you don't have to unhide any columns to see this information as per post #4.

Looks like a sweet tool. Anyway you could get this to work with OpenOffice? I can't edit in new player values

It will only work with Microsoft Excel 2007 I'm afraid.

I know nothing about OpenOffice so wouldn't even know where to begin to make it work.

That's a shame. Good work though.

ProZone, I have tried to use your programme but Excel is from a different planet as I am a pc "dummy". You probably know more about Open Office than I know about anything regarding a pc. Is it possible for you to tell me in very simple terms how to delete Uniteds information and how to input my teams? It is not a problem if you can't do this. I know it must be rather time consuming. Kind regards.

Forgive the double post/spam. ProZone, I have after two hours figured out how to delete Uniteds data. I have begun to input my teams data. I have another stupid question. I am physically writing down all of my teams players, all 24 of them and all of their attributes. Something is telling me that there has to be a faster way of doing this or is my way the only way.I figure that it will take me about 5/6 hours to complete, if I don't make any mistakes. Can you please enlighten me?

Forgive the double post/spam. ProZone, I have after two hours figured out how to delete Uniteds data. I have begun to input my teams data. I have another stupid question. I am physically writing down all of my teams players, all 24 of them and all of their attributes. Something is telling me that there has to be a faster way of doing this or is my way the only way.I figure that it will take me about 5/6 hours to complete, if I don't make any mistakes. Can you please enlighten me?

As far as I can see, no, there is no faster way. Merely window your FM10, unmaximise Excel, move the two windows around so you can see both the stats and where your typing. Then just enter the stats using the number pad and the right arrow.

Found inputting the stats for each player only takes about a minute anyway. Looks a good tool mate

Forgive the double post/spam. ProZone, I have after two hours figured out how to delete Uniteds data. I have begun to input my teams data. I have another stupid question. I am physically writing down all of my teams players, all 24 of them and all of their attributes. Something is telling me that there has to be a faster way of doing this or is my way the only way.I figure that it will take me about 5/6 hours to complete, if I don't make any mistakes. Can you please enlighten me?

Oescus,

Having tested this tool, I did discuss with PZ that it would be ideal if someone (other than him or I for that matter) was able to create another program that either A) Allows this XL chart to access to draw data from the save game.... ie something like FMTRTE or B) Something that simply prints off the skills from your team.

This is certainly beyond me and despite PZ's apparently outstanding XL skills, sadly he has admited that it is beyond him too.

I really hope that someone from the editors forum can assist here as this is a great tool, and it shouldnt be unused just because of the data entry.

Anyone know anyone that might be able to help PZ here?

LAM

Forgive the double post/spam. ProZone, I have after two hours figured out how to delete Uniteds data. I have begun to input my teams data. I have another stupid question. I am physically writing down all of my teams players, all 24 of them and all of their attributes. Something is telling me that there has to be a faster way of doing this or is my way the only way.I figure that it will take me about 5/6 hours to complete, if I don't make any mistakes. Can you please enlighten me?

Yes I know it can be time consuming. I actually have a dual monitor set-up so I can have the tool and Excel open side by side. I realise this is not going to be the case for everyone though.

Trust me, if there was a faster way I would have included it. I think 5/6 hours is a bit steep though, shouldn't take that long. I also think that the time spent will be worth it in terms of ensuring players perform to their best.

As Lam says, we discussed during testing that it would be good to create/acquire a program to grab the attributes from the game database. My programming skills are limited and we both hope that someone with the relevant skills might see this thread and take on the challenge.

Amazing tool. Great work and great addition.

Amazing tool. Great work and great addition.

Many thanks! Good feedback makes the time spent developing it worthwhile

I will just echo all the positive comments its very useful when playing in the lower leagues

Anyone know much about googles XL features. Will this work in there or is it to complicated?

Might give some people the ability to use it when they do not have XL at home

This tool is ideal for tweaking tactic and finding perfect roles for the players.

It doesn`t take long to input data, if you open game in window mode...it took me 1/2 hour for my 1st team.

The guys that make FMRTE or Genie scout should include this tool in the future releases.

As far as I can see, no, there is no faster way. Merely window your FM10, unmaximise Excel, move the two windows around so you can see both the stats and where your typing. Then just enter the stats using the number pad and the right arrow.

Thanks for your help, Chasjs and the others. I have managed to figure out Chasjs method. I have finished all the data input in around one hour. I am now looking for the link to print it all out on my printer. I can't find any way to print it all out. Anyone know, please?

Is there any chance someone could save this in excel 2003 so i can use it. It doesnt seem to change the player roles when i enter new attributes

If you have the newer version of office then its the big button in the top left and then that opens a new window in which you should find print.

Thank you for this great tool ProZone

or even just the formula's would be brilliant cause this seems like a fantastic tool to be able to use

I just uploaded this to google docs and it does seem to work there, so that's an option for those of us without Excel.

just downloaded and typed in my safc first team, very good tool and its shown me that im not playing the best players that are availiable

Is there any chance someone could save this in excel 2003 so i can use it. It doesnt seem to change the player roles when i enter new attributes

I'm sorry to say that this tool absolutely won't work in Excel 2003.

The reason is that Excel 2003 has a very low limit on the number of logical functions you can type into any one cell. The 2007 version has allowed me to build this tool with features I just wasn't able to implement in Excel 2003. Believe me, if I could have made it compatible with earlier and more readily available versions of Excel I would have done.

There is a 60 day free trial of MS Office 2007, which includes Excel 2007, here:

http://trial.trymicrosoftoffice.com/trialukireland/product.aspx?sku=3082920&culture=en-GB

Definitely worth a try I'd say...

What does the "Coaching Style" mean on the staff sheet.

What does the "Coaching Style" mean on the staff sheet.

It's just info. which appears on the Coach Attributes page.

It is not used in any calculation but I have noted it because I personally like, for example, to assign staff with Coaching Style: Attacking to attacking training duties.

Great tool! Congrats on your work!

Open Office is great and free alternative to Microsoft Office and this file works with it

Great tool! Congrats on your work!

Open Office is great and free alternative to Microsoft Office and this file works with it

Thanks! I will add this info. to the OP

Link to this thread should go to topic with other relevant links.

Hmm, when I tried it in OpenOffice, it didn't work, as I couldn't edit the players' values. Macaga said the same above.

ATTN OPEN OFFICE USERS HAVING TROUBLE

I believe I have found a solution for those (like myself) who are trying to use Open Office and cannot enter new data. I say I believe I've figured it out because I have not finished entering my entire squad, but so far so good. Here's the steps I took:

1) Open ProZone's spreadsheet with Open Office. The default Man Utd. data should appear.

You will now have to change the validity rules for the area of the spreasheet where the user inputs the player attributes (Corners thru Strength; Columns G thru AP).

2) Highlight the entire area (Col. G - AP) by placing your cursor in column G and then left click, hold, and drag your cursor all the way through column AP. The highlighted area should now turn a pale blue.

3) With the area highlighted, click on DATA on the menu bar at the top of the spreadsheet.

3) Select VALIDITY under the DATA menu and you get a tabbed dialogue box. It should automatically come up with the CRITERIA tab open.

4) The first option is ALLOW and will read "whole numbers". Click on the drop down arrow and select "all values". Click OK.

At this point you should be able to delete the Man Utd. data and begin inputting your own squad's attributes. Hope this helps anyone having problems. I also hope I've explained it in a manner that is easily understood. If not, I apologize...I'm no technical writer!

You will need to change the validity rules for each section of the spreadsheet (ex. first team, reserves, U-18's, etc.)

Yes I know it can be time consuming. I actually have a dual monitor set-up so I can have the tool and Excel open side by side. I realise this is not going to be the case for everyone though.

Best thing to do is to either make FM windowed and adjust the size, or make Excel windowed with FM behind it, so that you can easily copy the numbers across.

Anyway, very good project this James. I'm just downloading it to have a play with it now. Excellent work.

Regards,

C.

If you have the newer version of office then its the big button in the top left and then that opens a new window in which you should find print.

My thanks to those of you who took the time to assist me with my Excel problems, especially to Chasjs and lam [how did I miss that button?]. I think that I have everything sorted now. Kind regards.

I just renamed players and changed stats in Open Office and it worked fine.

My thanks to those of you who took the time to assist me with my Excel problems, especially to Chasjs and lam [how did I miss that button?]. I think that I have everything sorted now. Kind regards.

........... How did you think I knew it was there

PZ - some great feedback here...... good job Sir!!

LAM

terrific tool that helped me determine the quality of players i had in my QPR side - players that i am unfortunately extremely unfamiliar with

thanks a lot prozone. appreciate the effort u put into this

Brilliant tool! Really fantastic. Well done sir.

And as someone said, would love for this sort of calculation to be taken across to FMRTE or GenieScout.

PZ - some great feedback here...... good job Sir!!

I'm happy it's been so well received! Over 500 downloads now!

Thanks for your input too Lam

And as someone said, would love for this sort of calculation to be taken across to FMRTE or GenieScout.

Just FYI. I have posted in the Editors forum to try and drum up some additional programming support - especially for trying to grab Player Attributes live from the database.

So far a big fat zero response

I deliberately left this tool unlocked so the FM community could take ownership of it and take it to the next level.

Many thanks, PZ, for making this tool available. Simply brilliant!

As far as adding this to an app that grabs the values in real-time (or from a save-game), that shouldn't be a huge job, and something I might be willing to do if time alllows ... Is there a dotnet API for accessing in-game/save-game data (I vaguely remember seeing something like that on here)!??!

Anyone else planning to do this or is it a case of (as Princess Leia would say) "Help me John Mikel Obi Wan, you're my only hope"?!?

Well.... Mr Obi Wan......... does that answer your question? or do I need to go on.

As you have pointed out, I dont think its hard, one of my old financial directors used to do this stuff for fun, but its WAY beyond me!!

If you could spare the time Mr King Obi Wan, then I think there would be many many people here very appreciative.

LAM

Thanks for taking the time to make the tool, helped me alot to set up my strikers effectivley.

Prozone i have a tip for ur tool.

You can export the data that prozone tool needs using fm scout 2010 and then import in prozone tool.

This procedure work very well in a very similar project called "tactic creator" that one or two mates have done in our comunity (www.fmfans.it).

Just thought of another great use for this.

Input figures of players that you are looking to purchase and see how they actualy fit into the team.

Bear in mind though, whatever their points, you may be bring them into do a specific job, if thats the case then this is not the tool for it. However, if you simply beleive that they are just a good player, then use it to define their role and see how it fits.

LAM

Just thought of another great use for this.

Input figures of players that you are looking to purchase and see how they actualy fit into the team.

I always run any potential new signings through the tool before buying, same with coaching staff too. Good spot