Search the Community
Showing results for tags 'excel'.
Found 2 results
I will preface this post by stating that SI generally do not support files that have content created outside of the FM Editor, including Excel. I've used Excel with FM over the past year to help with big projects when it comes to importing a massive amount of data. It can rapidly reduce the amount of time you would otherwise spend in the Editor making the changes manually, and as long as you create the file separately from your main project, and test it in game to ensure that it works, you should find that it works flawlessly. This includes changes to the database for items like histories etc. One example of this is when setting up Fixtures in the editor - this can take a long time and when you have a few leagues you want to do, this can become monotonous (I have probably done a 100 of these over the past few years, and I did all fixtures not included in game for last years EEE release, minus 3 L9 leagues that still announce their fixtures monthly, in other words, the EPL, L7, L8 and 11 L9 leagues - 21 in total for one season while SI had the EFL and ENL covered with 7 leagues). Using excel text formulas you can manipulate the data in released fixture files and tabulate all the necessary details, such as the date, the home team and away team, and the time for the fixture. As an example, here is one original fixture announced for the EPL this year: 13/08/2016 15:00 Arsenal v Liverpool This is how the fixture is formatted by the FA in their original announcement here. The way I tabulated this data was first of all to separate the date, which could then be separated into the day of the week, the day of the month, the month and the year. The formula I used was "=LEFT(A2,10)" to retrieve the information for the date. I could then obtain all the other data by using formulas such as "=LOWER(TEXT(B2,"ddd"))" for the day of the week, "=DAY(B2)" for the day of the month, "=LOWER(TEXT(B2,"mmm"))" for the name of the month and "=YEAR(B2)" for the year. Similar formulas are done for the rest. The trickiest part here is getting the data for the teams separated so that you can find the team Unique ID. The formula "=MID(A2,25,100)" separated the teams from the original data, at which point you can then use formulas to get the home and away team. The " v " is the key part here, as you can use this as the dividing line to separate the data in formulas. I retrieved the home team using "=LEFT(D2,SUM(SEARCH(" v ",D2,1))-1)" and the away team using "=RIGHT(D2,SUM(LEN(D2)-SEARCH(" v ",D2,1))-2)". A separate sheet should be setup with the Unique ID's and the names of the clubs in question - not too hard to get from the Editor and not too time consuming. From here, you can use a VLOOKUP function to get the ID's of the clubs. I used this formula: "=VLOOKUP(J2,[ENG.xlsx]ENG!$A:$B,2,FALSE)", just a simple VLOOKUP to another spreadsheet which has the club name in the first tab and the ID in the second. So my end result is the following: DATE TIME MATCH Day Date Nmonth Year Time Home Team Away Team Home Team ID Away Team ID 13/08/2016 15:00 A.F.C. Bournemouth v Manchester United 13/08/2016 15:00 A.F.C. Bournemouth v Manchester United sat 13 aug 2016 1500 A.F.C. Bournemouth Manchester United 600 680 13/08/2016 15:00 Arsenal v Liverpool 13/08/2016 15:00 Arsenal v Liverpool sat 13 aug 2016 1500 Arsenal Liverpool 602 676 13/08/2016 15:00 Burnley v Swansea City 13/08/2016 15:00 Burnley v Swansea City sat 13 aug 2016 1500 Burnley Swansea City 622 724 etc. So, with this data I can now set up the xml data for the fixtures. Having exported a copy of the Advanced Rules file for England into an xml, I was able to ascertain which data I would need and inputted each xml line into a tab on the first row, and removed the data which would require altering. <record> <integer id="fixture_order" value="0"/> <string id="day_of_week" value=""/> <integer id="day_of_month" value=""/> <string id="month" value=""/> <integer id="year" value=""/> <string id="time" value=""/> <record id="home_team_id"> <integer id="id" value="1752003689"/> <integer id="Ttea" value=""/> </record> <record id="away_team_id"> <integer id="id" value="1635218537"/> <integer id="Ttea" value=""/> </record> </record> These were the first row tabs. As an example, the first cell that would need changing is '<string id="day_of_week" value=""/>'. Using a formula you can change this to find the day of the week easily from the previous worksheet. I used "=CONCATENATE(LEFT(C1,32),fixtures!E2,RIGHT(C1,3))", and similar ones for the rest. As for the cells that didn't need extra data, it was a case of equalling the first cell (e.g. "=B$1"). Once this was done, you could drag the formula all the way down to get the fixtures. I've attached the file to this so you can check out the exact worksheet I used so you can create your own. Lastly, you can use the xml data and paste it into an FM XML file. I just found the EFL fixtures, pasted that into the EPL file and overwrote the main fixtures part with the one from the worksheet. I've attached that file too so you can see the EPL Fixtures in the editor. So why go through all this, it seems like a lot of work from what I've written? If you've had to do this before, you'll know how long inputting fixtures into FM can take. This has taken me from 09:21 when I saved the original fixture list, to 10:04 when I saved the FM XML file with the fixtures formatted. A total of 43 minutes for 380 fixtures. And once you've done this only once, it becomes quicker the second time, and you can use this as a basis to edit other things like records and histories into the game. You could even use it to add players, although I have not done it. You just need to add a few records into the same and save the file as an xml to check the data and how it is formatted. Then you can identify what you need and implement a worksheet with Excel for what you need. I'm hoping to bring out more content like this in the future, please let me know if you found this useful or interesting! And please feel free to ask questions if you think I have missed something or if you think there is another way the work can be improved. EPL Fixtures Workbook.xlsx EPL Fixtures.xml
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