Subject: Transfers database

There are two different databases, with their own topics:

1. this one, Transfers database - contains info about all transfers made after 2021-12-27, useful for estimating player's market value.

2. database from the other topic, Players database - contains info about all the players from sokker, mostly useful for scouting players for national teams or "spying" on your opponent's players' skills (from their last transfers).

---

To open the database you need two things: the program to browse the database and the database file itself. Database contains transfers made after 2021-12-27.

Link to the program DB Browser for SQLite for browsing transfers database
Link to the transfers database file

Two minute video with everything you need to do to open the database on Windows 10, open video in new tab to see it in good resolution:

1. Download the installer of the database browser program from the first link, select "DB Browser for SQLite - Standard installer for 64-bit Windows".
2. Install the program (as shown in the video)
3. Download the database file from the second link in this thread (transfers.db) by right-clicking on the file and selecting "Download..." -> "Standard Download"
4. Launch the DB Browser for SQLite program
5. Click the "Open Database" button.
6. Select the downloaded transfers.db file (depending on your browser settings, it will usually be located either in the "Downloads" directory or on desktop)
7. Disable the panels on the right side of the program, they are not needed
8. Go to the "Browse Data" tab

If anyone has any problems make sure you do everything as shown in the video. It only lasts two minutes, it's worth watching it before you start asking questions.

There are 3 tables in the database:
1. transfers, the table into which I collect all the information, it is not the most handy to browse, but I will describe what data it contains:
- id - row number in the database
- pid - id of the player in the sokker
- name - the first and last name of the player
- country - id of the player's country
- age - age of the player (at the time of transfer, the same applies to the rest of the columns)
- height - player's height
- weight - player's weight (multiplied by 10 to avoid decimal values)
- season - season in which the transfer took place
- week - week of the season in which the transfer took place
- price - the amount (in euros) for which the player was sold (NULL means that the transfer has not yet ended or the player has not been bought by anyone)
- form - the form of the player (numerically, like all others)
- stamina - player's stamina skill
- pace - player's pace skill
- technique - player's technique skill
- passing - player's passing skill
- keeper - player's keeper skill
- defender - player' defender skill
- playmaker - player' playmaker skill
- striker - player's striker skill
- tactical_discipline - player's tactical discipline
- experience - player's experience
- teamwork - player's teamwork
- injury - remaining days of injury on the day of the player's transfer, 0 means no injury (available for transfers from 2022-09-12, NULL for earlier transfers means no information, because I didn't collect it earlier)
- value - player's value (in euros)
- wage - player's salary (in euros)
- transfer_date - ending date of the transfer
- transfer_time - ending time of the transfer
- transfer_date_finalised - the day on which the transfer was transfered to the winner of auction
- starting_price - the amount (in euros) for which the player was put on the market place, NULL means that at the time of data collection the player was bidded by someone
- seller_id - id of the club that sold the player
- buyer_id - id of the club that bought the player
- is_finished - takes value 1 if the transfer has already been completed (the player has been checked in or no one has bought the player), value 0 if the transfer has not yet ended or the player has not yet been transfered to the winner of auction.

2. transfers_with_sumskills_eur - table containing data that make it easier to compare transfers; some column names are additionally abbreviated to make it display more nicely in this view, not spread so wide like the first table:
- defSS - sum of skills defender + pace
- defSides - sum of skills technique + playmaker + passing
- midSS - sum of skills defender + pace + technique + playmaker + passing
- wingSS - sum of skills pace + technique + playmaker + passing
- attSS - sum of skills pace + technique + striker
- attSides - sum of skills defender + playmaker + passing
- all_skills - sum of all eight skills (including stamina)
- pid - player id in sokker
- name - the first and last name of the player
- ctry - id of the player's country
- season - season in which the transfer took place
- week - week of the season in which the transfer took place
- price - the amount (in euros) for which the player was sold (NULL means that the transfer has not yet ended or the player has not been bought by anyone)
- stam - player's stamina skill
- pace - player's pace skill
- tech - player's technique skill
- pass - player's passing skill
- gk - player's keeper skill
- def - player's defender skill
- play - player's playmaker skill
- str - player's striker skill
- tact_disc - the player's tactical discipline (useful for checking whether the player is a fresh junior from the junior school, in which case there should be 0 there)
- form - the player's form
- age_now - current age of the player; useful when a new season has started and we want to check transfers from the last few weeks for a given age. For example, if we enter =22 in the "age_now" filter and >2022-08-20 in the "transfer_date" filter, we will get transfers of 21-year-olds from the end of the previous season and 22-year-olds from this season (at the time of this writing it is 2022-09-20 and there was an age change a few days ago).
- transfer_date - ending date of the transfer
- tr_time - ending time of the transfer
- starting_price - the amount (in euros) for which the player was put on the market place, NULL means that at the time of data collection the player was bidded by someone

3. transfers_with_sumskills_pln - same as transfers_with_sumskills_eur, only prices in are in PLN

How to use it:
1. I recommend the table transfers_with_sumskills_eur
2. In the "Filter" fields you can filter the results:
- If you want the exact value, then enter there the text preceded by the = sign, for example, if you want hopeless goalkeepers, then in the filter in the gk column enter =1. If you enter just 1, it will find everyone who has any 1 in the number, that is, also skills 10-18. Usually it does not matter, for example, with age or season, because there it is impossible to misuse it as the values are always two digits
- You can search "more" and "less", e.g. >7 in the week column will find us players sold later than the seventh week
- You can search "from to," in which case you need to enter two values separated by a tilde, e.g. if you want to find goalkeepers with a skill from excellent to outstanding then enter 10~12 in the filter in the gk column.
- You can sort by columns, to do this you need to click once or twice on a name of the column (one click to sort from the lowest value, two clicks to sort from the highest value)
- Filtering and sorting can be reset with the buttons next to the table selection (in order: table selection, refresh button, clear filters button, clear sorting button)
3. At the end of the video I showed an example filtering: 21-year-old players sold after the seventh week of the last season (62), sorted by the highest sumskill for the striker.

Notes:
- The database should be updated daily, but to keep it up to date you need to download an updated database file for yourself on a regular basis. The link to the database file itself should not change, you can bookmark it in your browser.
- On the day when the players' skills change (Thursday's increases and Sunday's decreases), I also try to update it if the player is still on the TL, but if the player is sold in the morning I may not catch his changes (e.g., an older player sold at 9 AM on Sunday).
(edited)

.

I will write it again. Thank you my friend <3

You're welcome, have fun using it :)

Btw. questions to all interested: should I put both of my databases (this one, and the other one - Players database) in the same topic?
(edited)

---

Noce, I will use to train my neural network model to predict players price