FIFA -> PES stats converter

[yoman38]

(Copy/paste of my evo web thread)

Hello, I tried to create a converter based on FIFA 18 (currently conversion has to be done manually), I would like to get some feedbacks on ratings/calculation.

FM converter is indeed very nice, but I think FIFA database fits more this kind of game

Thanks!

EDIT 25/06: many bugs corrected. Keepers' ratings is too low because of fifa calculation in terms of basic stats (passing/shooting/running,etc), I can't change anything. It looks like bad players in PES are very overrated when it comes to passing and stamina (even a sh***y guy has a 66) so I'll be kinder with low pass/lofted pass and stamina (especially for PES19)

NEW!
Automatic, you don't have to choose international/loan this kind of things. But there is a long loading time:
https://docs.google.com/spreadsheets/d/1bxwHbwCW5hQsk3Dknx_DFjOs9N0X8BIJOCO6b8XF7mQ/edit?usp=sharing


!MAJOR UPDATE!
Now automatized process, just copy paste a sofifa player link (ex: https://sofifa.com/player/182521 ) in the right cell.

You must know if you're player is loaned or not (it's written on his page) and see if he's international or not.
LINK: https://docs.google.com/spreadsheets/d/1sYkMnPwhufxSymq90G0VoQ8S1wyB-RL0allkdLbJy90/edit?usp=sharing

IF SOMEONE IS ALREADY USING IT, JUST COPY IT IN YOUR OWN DRIVE: "Files" -> "Create a copy"

How to choose the right category?

http://image.noelshack.com/fichiers/2018/29/5/1532093015-capture4.jpg

23/06 update
- Tweaks performed on some formulas; should be more efficient.
- Added ALL FIFA traits. They do impact stats, player skills and COM playing style.
- Added COM playing styles.
- Goalkeeper stats converter added.

Old version (manual)
I don't own excel, tell me if it works by downloading it in google drive.
You can use it online or create a copy in your own drive (FILE -> Create a copy -> look for it in your own drive); it will work: https://docs.google.com/spreadsheets/d/1I1mGW9s4mqqr79SmJsJGXsjSNKIW0zbab7u9v3chVqs/edit?usp=sharing
Fill everything under "FIFA player" with correct numbers, next to "skill stars" and "weak foot stars" with the digit only (4 for example), and every coloured squares next to traits with a "yes" (without capital or apostrophe) or leave it empty.
You just have to fill cells with thick borders in fact.

 

[yoman38]

Major update!


Everything is now AUTOMATIC!

 

[yoman38]

Doesn't work atm with player in loan or free agent; new versions will come this month

EDIT: corrected

 

[yoman38]

Big update to formulas. Now consider the position. Only international/non international are up to date, I'll modernize loan/free agent by the end of the week

 

[yoman38]

now all in one file https://docs.google.com/spreadsheets/d/11R9gXmlRc5n-Cs2ysjsj62wgsVkKGePcqUuetaC0KUo/edit#gid=0

 

[beginner]

Looks interesting.

Has anyone tested this???

 

[yoman38]

Well it's not the exact same formulas (older version I think) and skills weren't taken into account yet, but here is an exemple: https://www.youtube.com/watch?v=1L4MpZ55FUw Not the best, I had better results with belgium or even France. Corluka is overatted and Lovren underrated I think, but hey; that's because of stat's spreading.


Btw new update, now it looks better

 

[beginner]

Well it's not the exact same formulas (older version I think) and skills weren't taken into account yet, but here is an exemple: https://www.youtube.com/watch?v=1L4MpZ55FUw Not the best, I had better results with belgium or even France. Corluka is overatted and Lovren underrated I think, but hey; that's because of stat's spreading.


Btw new update, now it looks better

The formulas you use to convert the stats look very complicated. :huh:
Can you explain me, how you analyzed data and created that kind of formulas?
And how do you check, that conversion fits best for each category?

btw: I'm a bit impressed :happy:

EDIT: Can you also explain, why are there different sheets for "loan" and "international" players etc.??? Why do they need different formulas???

 

[yoman38]

The formulas you use to convert the stats look very complicated. :huh:
Can you explain me, how you analyzed data and created that kind of formulas?
And how do you check, that conversion fits best for each category?

btw: I'm a bit impressed :happy:

EDIT: Can you also explain, why are there different sheets for "loan" and "international" players etc.??? Why do they need different formulas???

First of all, the ambition was only to convert fifa stats to PES range (*60/100+40): 0-100 to 40-100.
Then I wanted to use multiple fifa ratings for one PES ratings, because there is less stats in PES. I started with a unique formula then after several tests everything was splitted to work with PES. Also, position is very much taken into account. Traits are important too, but they can only change a stat to +3/-3.

Unfortunaly I don't own excel and I face some troubles with importxml; that's why I ended up with different sheets. Because with =IMPORTXML(H16;"//li") I don't import only the stats, but also the squares just above with country/on loan/international rep and all this stuff. I don't need it (except weak foot) but I don't know how to import in more precise ways. Help required!:no:

 

[ggdaris]

First of all, the ambition was only to convert fifa stats to PES range (*60/100+40): 0-100 to 40-100.
Then I wanted to use multiple fifa ratings for one PES ratings, because there is less stats in PES. I started with a unique formula then after several tests everything was splitted to work with PES. Also, position is very much taken into account. Traits are important too, but they can only change a stat to +3/-3.

Unfortunaly I don't own excel and I face some troubles with importxml; that's why I ended up with different sheets. Because with =IMPORTXML(H16;"//li") I don't import only the stats, but also the squares just above with country/on loan/international rep and all this stuff. I don't need it (except weak foot) but I don't know how to import in more precise ways. Help required!:no:

i used this formula =IMPORTXML(B3,"//ul") , beacuse if you said, the other formula import many things not necesary i think

here my own result, thanks for the idea, now i can use to do some teams i want and are in fifa :happy:

 

[yoman38]

i used this formula =IMPORTXML(B3,"//ul") , beacuse if you said, the other formula import many things not necesary i think

here my own result, thanks for the idea, now i can use to do some teams i want and are in fifa :happy:

Thanks for your formula, but I face the same problem: with an international player crossing for exemple will be in A10 and with a free agent in A5

 

[wyffy]

Thanks for your formula, but I face the same problem: with an international player crossing for exemple will be in A10 and with a free agent in A5

Try with =IMPORTXML(B3,"//a")
I think the stats positions will be the same in all of cases.

 

[yoman38]

Try with =IMPORTXML(B3,"//a")
I think the stats positions will be the same in all of cases.

Yep, it doesn't import the useless text but unfortunaly even if there's a crazy amount of numbers they are not matching with stats

 

[wyffy]

Yep, it doesn't import the useless text but unfortunaly even if there's a crazy amount of numbers they are not matching with stats

Ok...
Then, why not scraping stat by stat, or all you want?
Do you know how Xpaths works?

For example, if you want to know the Crossing stat, put in a cell:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[1]/span")

If you want the Finishing stat:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[2]/span")

...

 

[ggdaris]

Ok...
Then, why not scraping stat by stat, or all you want?
Do you know how Xpaths works?

For example, if you want to know the Crossing stat, put in a cell:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[1]/span")

If you want the Finishing stat:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[2]/span")

...

oh that be great, can you give a formular to take only this values

this is my stats converter

 

[yoman38]

Ok...
Then, why not scraping stat by stat, or all you want?
Do you know how Xpaths works?

For example, if you want to know the Crossing stat, put in a cell:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[1]/span")

If you want the Finishing stat:
=IMPORTXML(B3;"//div[3]/div[1]/div[1]/ul[1]/li[2]/span")

...

I tried it..unfortunaly too much loading time..Hmm excel would be nice

 

[wyffy]

oh that be great, can you give a formular to take only this values

Data:
=IMPORTXML(B3;"//div[1]/div[1]/div[@class='meta']")

Nation:
=IMPORTXML(B3;"//div[1]/div[1]/div[1]/a/@title")

Image:
=IMAGE("https://cdn.sofifa.org/players/10/18/" & EXTRAE(A1;(HALLAR("player/"; B3;2))+7;50) & ".png";1)

 

[wyffy]

I tried it..unfortunaly too much loading time..Hmm excel would be nice

You can load all the data with =IMPORTXML(B3,"//a") in a dirty sheet and hide it.
Import only the data you want in the main sheet with ='Hoja 2'!A1 in each cell

 

[ggdaris]

Data:
=IMPORTXML(B3;"//div[1]/div[1]/div[@class='meta']")

Nation:
=IMPORTXML(B3;"//div[1]/div[1]/div[1]/a/@title")

Image:
=IMAGE("https://cdn.sofifa.org/players/10/18/" & EXTRAE(A1;(HALLAR("player/"; B3;2))+7;50) & ".png";1)

You are a genius man!! see this

Thanks a lot!! :happy::happy:

 

[yoman38]

You can load all the data with =IMPORTXML(B3,"//a") in a dirty sheet and hide it.
Import only the data you want in the main sheet with ='Hoja 2'!A1 in each cell

But with =IMPORTXML(B3,"//a") the abilities like crossing or so on aren't in the same square depending of loan/international...So I'm stuck

Still it's now better but I need to edit my sheet because of the updates like +1/+3 for some stats...I'll have to leave an empty square between each abilities. It will be long to edit it..I have to do it.