Jump to content
Sports Interactive Community
magicmastermind124

Excel and the FM Editor - FM Editing

Recommended Posts

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

Share this post


Link to post
Share on other sites

Yep, a script is going to be much better than Excel - but Excel is a bit more accessible for most people.  I'd like to see how you do it in a script though, I'm sure others would be interested too!

Share this post


Link to post
Share on other sites

All you need is "in.txt" file with tabulated data. Just run script and "output.txt" file will be created.

I dont use this anymore because this is for Windows. Also i use some text editors for all my manual work.

Credits to Prodo from FM-Balkan 

script.bat.zip

Share this post


Link to post
Share on other sites

I can see how this would be useful! I'd still use Excel obviously to get the data.  I quite like being able to have the data in Excel though for future proof purposes as the format of the xml files and the Database can change.

Share this post


Link to post
Share on other sites

i also have some unix scripts for complex data files-if you need any help im here. 2 heads are smarter than 1 :D

Share this post


Link to post
Share on other sites

I don't need the help, I'm just trying to get tutorials out here for people to use and learn.  I'd love it if there were guides for writing these sorts of scripts for FM.

Share this post


Link to post
Share on other sites

do you have some idea for kits? google forms are good solution for all other information but kits are problem.  i want to find some easy way to get info about LL team kits and if i use google forms its complicated for people to choose from 50 options - kit design, foreground colour, bgnd colour... best way is to create some application similar to kit designer in editor but i dont know how to do this. 

Also its everything about information - transfer that information to xml is not problem at all. this can be done in 3 ways.

1.with that script above- this is easiest solution for begginers

2.with my unix scripts -this is easy for me but probably more difficult for begginers but with some tutorial this can be usefull

3. directly from excel with "xml tools" add -on  - this is also easy way with some easy tutorial

Share this post


Link to post
Share on other sites

Is it possible to create XML files for anything you put into the editor?  I have a semi-working "robot" that takes in a huge amount of data then translates that into how a user would enter it into the editor, but translating it into an XML file instead would be much quicker and cleaner.

Share this post


Link to post
Share on other sites

Urgh! I spent a rather long time using Notepad to create the correct fixtures and results for the Czech League and now I see there is a simpler way of doing it!!

Share this post


Link to post
Share on other sites
On 8/24/2016 at 18:01, Chesters86 said:

Urgh! I spent a rather long time using Notepad to create the correct fixtures and results for the Czech League and now I see there is a simpler way of doing it!!

It's okay, you can still use those results in Excel if you were to do it that way.  It's just a case of learning a few new formulas to manipulate the data.

 

With regards to the scripts, like I mentioned earlier it would be great to have a tutorial on this much like what I have done in the OP of this thread. I may take some time this weekend to teach myself and see if I can become comfortable in it to help others.

Share this post


Link to post
Share on other sites
12 hours ago, krlenjushka said:

@metal_guitarist javascript

@forameuss its possible. upload your "robot" so maybe we can change it for you

It's nowhere near uploadable state.  It works, it's just tedious and prone to error if it runs "too fast".

I investigated the XML files the past few days, seems the better option.  Can easily take in an excel line representing a database object and generate an XML through Python that would be editor readable.  It's actually quite a nice format it uses.  Just a matter of getting them working together.

Share this post


Link to post
Share on other sites

Has anyone used this method to add players or clubs? If so, would that person mind sharing how they did it?

Thanks!

Share this post


Link to post
Share on other sites

Hi all,

Sorry for the thread dredge, but does anybody know what the rules are around the <large id="db_unique_id" and <integer id="db_random_id" attributes?

I'm working on a tool to bulk import records, so I will need to generate these values on the fly. Obviously, the values need to be unique within the file, but there seem to be some other rules (incrementing an existing value by 1 didn't work).

Share this post


Link to post
Share on other sites

"db_unique_id" is unique id for every recored you add to your file - club, player, city...  BTW when i add files like this - i use editor visible UID.

"db_random_id" is random UID used for every change you made in your file - this is here in case you use multiple files. i never use this line when i create files from excel and i never had any issues.

Share this post


Link to post
Share on other sites

Cool. Thanks for the information. Since I posted I've written a unique ID generator, and it seems to be working well. (I imported 100 random new cities as a test and everything worked correctly.) 

Edited by DementedHammer

Share this post


Link to post
Share on other sites
1 hour ago, DementedHammer said:

Cool. Thanks for the information. Since I posted I've written a unique ID generator, and it seems to be working well. (I imported 100 random new cities as a test and everything worked correctly.) 

if your tool can be used in linux - i will be happy to see how it works. I have script for windows and its 100% but im to lazy to load windows everytime i need something - and i cant make similar thing in linux because my skills are not that good :D

Share this post


Link to post
Share on other sites
12 hours ago, krlenjushka said:

if your tool can be used in linux - i will be happy to see how it works. I have script for windows and its 100% but im to lazy to load windows everytime i need something - and i cant make similar thing in linux because my skills are not that good :D

At this stage it is Windows only, but I could potentially use Mono to convert it. 

Share this post


Link to post
Share on other sites
4 minutes ago, DementedHammer said:

At this stage it is Windows only, but I could potentially use Mono to convert it. 

Mono?

Share this post


Link to post
Share on other sites

Ok i will try.

 

EDIT: thanks to your advice - i tried to find solution for Mono and i managed to found solution for my script. It was quite easy TBH. I can run it with wine cmd command without any issues. Its not with Mono but who cares :D

Thanks anyway!

Share this post


Link to post
Share on other sites
8 minutes ago, DementedHammer said:

Awesome. Glad that I could help indirectly. (You've helped me a lot so it's nice to be able to return the favour in some way.) 

Btw you can add "random uid" line if you want to be sure. I used random number generator for similar things.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...