Spreadsheet for European Cups - Feedback & Suggestions

[eye]

This is a project I was planning to do lot of years ago but due to lack of time couldn't make it. Finally I found the needed time so I am presenting it here.

Link for spreadsheet: https://docs.google.com/spreadsheets/d/ ... =629117286

The spreadsheet is working almost automatically. It gets data from Bert's site (country ranking, club points) and tables of domestic leagues from soccerway and the only that needs to be done manually at the moment is the input of domectic cup and european cup winners and few league spots that are decided by h2h playoffs. I am trying to find a way this to be done automatically too but even if I don't find it this is not big deal since the required manual input is minimum.

The main principal we are using at such projects is that seeded clubs (clubs with higher coefficient) will qualify to next round. I am also using this principal but I also extended the use of it to everything. This means that in every cup (domestic or european) as winner is considered that club with the higher coefficient of the remaining clubs in competition (eg currently CL winner is considered Liverpool, Coppa Italia winner is considered Juventus etc). This is a huge advantage to the functionality of the spreadsheet since as long the club with higher coefficient is not eliminated it will still give correct results even if I haven't performed the manual update the cup competitions. This principal will also be used for the domestic leagues that have not started in the new spreadsheet for next season which will be availiable the following days.

The spreadsheet is using the UEFA's general access list and regenerates it automatically depending on the qualified clubs. We don't have any official info on how this regeneration is done for every case so I have implemented it based on what we already know for sure and on how we assume UEFA regenerates the access list. This regeneration of the access list is the most powerful functionality of spreadsheet since getting correct results is a matter of few seconds in every case. If for example UEFA decide to remove the ban of Russian clubs or decide to ban clubs from other country all I would need to do is link the Russian league to spreadsheet or unlink the other country from spreadsheet which is matter of reconds.

In theory everything should be working fine but the complexity of the spreadsheet is too high and practically it is impossible to test it myself on every "weird" situation we might have during season so it is very likely bugs to be detected. If you notice any please inform me here. I would also like to hear ideas on what changes - additions you would like to see

[Club-Mate]

@eye excellent table and very sophisticated use of Spreadsheet. Beyond this a nice explanation of the whole procedere so an interested user with some knowledge can follow this easily. Plenty of work spent to create this good looking sheet at the beginning but much more less work to update it weekly and herewith error reduction. That's the sense of such a project.
After @stadion's sheet the next highlight. I am lovin' it!

[Club-Mate]

what I like here is the correct spelling of all clubs! not Slavia Prague (there is no such club in Praha) but Slavia Praha; not Austria Vienna (sounds like a tourist destination) but Austria Wien the football club.
The date is still missing, otherwise transparent and simple.

[eye]

what I like here is the correct spelling of all clubs! not Slavia Prague (there is no such club in Praha) but Slavia Praha; not Austria Vienna (sounds like a tourist destination) but Austria Wien the football club.
The date is still missing, otherwise transparent and simple.

I am using the names Bert is using at this site for 2 reasons. First because the spreadsheet will be mostly seen from users of this site and second because it was easier way to me to name clubs. Still there is an exception. If the club has not received ranking points the last 5 years so it is not listed at club points I am keeping the name soccerway is using at their tables. If Bert has a file of names of the clubs for every league and wants to share it I could use it also for the clubs that are not listed at club points. In any case the credits for the naming should be given to Bert and not to me.
As for the date that is missing which date do you mean ?

[bert.kassies]

If Bert has a file of names of the clubs for every league and wants to share it I could use it also for the clubs that are not listed at club points. In any case the credits for the naming should be given to Bert and not to me.

For clubs that ever took part in European football I use this list: https://kassiesa.net/uefa/clubs/. For new clubs there is no list, and I use soccerway or wikipedia, but always try to strip a sponsor name. And I am very conservative at name changes. One change that will be made next year is to finally change Dinamo Kiev to Dinamo Kyiv. The old name was still used mainly in this part of Europe, but that is changing now.

[eye]

If Bert has a file of names of the clubs for every league and wants to share it I could use it also for the clubs that are not listed at club points. In any case the credits for the naming should be given to Bert and not to me.

For clubs that ever took part in European football I use this list: https://kassiesa.net/uefa/clubs/. For new clubs there is no list, and I use soccerway or wikipedia, but always try to strip a sponsor name. And I am very conservative at name changes. One change that will be made next year is to finally change Dinamo Kiev to Dinamo Kyiv. The old name was still mainly used in this part of Europe, but that is changing now.

I will use this extended list at next season's spreadsheet which is actually running at the moment

[eye]

A new sheet called "Current Status" is added. You can see there the top 8 of each league and the number of the remaining matches in the league, the cup winners and english league cup winner and the number of clubs that are still at each competition and also the european cup winners and remaining clubs at each one of these competitions. Again as cup winners are considered the clubs with the most ranking points amongst the remaining clubs of each competition. when the number of remaining clubs at a cup is 1 then competition is over and the named club is the winner (will be in green).
All the league results are automatiacally updated except at the leagues that have are knock out play offs (Netherlands, San Marino). The knock out play offs and all cups (including european) are manually updated so there might be a delay depending on my free time which is usally not much

[amirbachar]

If Bert has a file of names of the clubs for every league and wants to share it I could use it also for the clubs that are not listed at club points. In any case the credits for the naming should be given to Bert and not to me.

For clubs that ever took part in European football I use this list: https://kassiesa.net/uefa/clubs/. For new clubs there is no list, and I use soccerway or wikipedia, but always try to strip a sponsor name. And I am very conservative at name changes. One change that will be made next year is to finally change Dinamo Kiev to Dinamo Kyiv. The old name was still mainly used in this part of Europe, but that is changing now.

I will use this extended list at next season's spreadsheet which is actually running at the moment

Very nice

[Club-Mate]

As for the date that is missing which date do you mean ?

sorry my fault there is no date necessary as this sheet is automated and updated anytime I guess. But a newcomer might not know this.

[wannabeontop]

If Bert has a file of names of the clubs for every league and wants to share it I could use it also for the clubs that are not listed at club points. In any case the credits for the naming should be given to Bert and not to me.

For clubs that ever took part in European football I use this list: https://kassiesa.net/uefa/clubs/. For new clubs there is no list, and I use soccerway or wikipedia, but always try to strip a sponsor name. And I am very conservative at name changes. One change that will be made next year is to finally change Dinamo Kiev to Dinamo Kyiv. The old name was still mainly used in this part of Europe, but that is changing now.

I will use this extended list at next season's spreadsheet which is actually running at the moment

Hey, totally unrelated but I'd like to point out that Videoton Fehérvár and FC Fehérvár are actually the same team, running as MOL Fehérvár FC at the moment. But everyone calls them Videoton or, in short, "Vidi".

[eye]

One match remaining for the end of the season (Albanian cup) at all countries so after this match the mission of the spreadsheet has ended. Nobody mentioned any mistake (I found out myself some minors though) so I guess it is working correctly. Still since it came at the very end of season it is very possible that some "mistakes: didn't have the chance to appear so the real test will be with the spreadsheet for the next season which is here https://docs.google.com/spreadsheets/d/ ... sp=sharing

Again the main principal of this spreadsheet is the assumption that the club with higher coefficient will qualify/be above over the club with lower coefficient. To make it more clear:

- For the european cups, the domestic cups and english league cup as winner will always be considered the club with the most ranking points from the remaining clubs in the competition even if the competition has not started.
- For the domestic leagues that have not started the standings will be determined by the rankings points. The club that has most will be considered the winner of league, the club with second most the 2nd placed club etc. For the leagues that have started the real standings will be used.

There are few things that might not be working correctly or might be missing. These are:

- The ranking points of each club are from the previous 4 years. Once Bert adds points for the next season they will be corrected.
- For the same reason the number of remaining clubs at european competitions at Current Status tab is wrong since something like 20 clubs that qualified have not taken part at european competitions the previous 4 years. Again once Bert adds the points for the coming season this will be working correctly.
- I had forgotten the rule about maximum 5 clubs per country at CL so the rebalanced access list the spreadsheet is creating doesn't take account of it. Although I don't think this will ever happen since the spreadsheet is running the whole season it is very likely at some point to have such situation (especially at beginning of season). Probably it is not big deal but I will try to fix it. I just don't know what other changes occur by dropping one more club to EL. If anyone knows I would like to let me know.
- Some of the clubs that appear at top places of the standings at leagues that have not started might not even be at top division. Some might not even exist. This happens because the leagues are sorted based on ranking points so probably some clubs that earned them 3-4 years ago might have lost their place at the top league of their country. I am not going to check such thing because this is a temporary solution and when the league starts and spreadsheet will be linked to sw this will be fixed. Although if anyone informs me for such situation I will replace the club.
- I have implemented the banned club/countries list which will also be used for the clubs that will not obtain license. I only know that Russian clubs are banned so if anyone else knows about other club/clubs I would like to let me know about.
- The number of the remaining matches per league are based on the competition system of previous season. It is likely that some leagues might have decided to change it for this season. If anyone has any info about this I would like to share it also. I would also like to let me know when leagues will start if anyone has such info or if there is a complete table somewhere online.

Finally I am open to any other ideas or suggestions you might have

[eye]

A table of access list is now added at the spreadsheet. Since the changes are done automatically this table gives you a better view of what changes is done to the access list.

[eye]

A major update to the file had been completed. You will not see anything new cause this update had to do with the functionality of the file. The only new you might notice is that it will be refreshing much faster than before. I have set it up to refresh every 15 mins. Although have in mind that the file uses data from external sites for league tables and to Bert's tables for the rankings points so no matter how fast my file is refreshing it requires data to be updated to other sites first. Normally when a match ends you should see the updated data after 15-30 mins at worse case.
Also I tried to avoid the crushing of file in case of error accessing the external sites so normally you should not ever see the #N/A message. If you see it I would like to send me a screenshot of it and the exact time you saw it in order to try to fix it.
Finally soon English and Spanish leagues will start. This is actually the period that the file will be tested since holders of the european trophies are considered clubs from these leagues and the access list will be changing due to their domestic performances. If you notice anything weird I would also like to see a screenshot of it (exact time will help too)

[ffero]

A major update to the file had been completed. You will not see anything new cause this update had to do with the functionality of the file. The only new you might notice is that it will be refreshing much faster than before. I have set it up to refresh every 15 mins. Although have in mind that the file uses data from external sites for league tables and to Bert's tables for the rankings points so no matter how fast my file is refreshing it requires data to be updated to other sites first. Normally when a match ends you should see the updated data after 15-30 mins at worse case.
Also I tried to avoid the crushing of file in case of error accessing the external sites so normally you should not ever see the #N/A message. If you see it I would like to send me a screenshot of it and the exact time you saw it in order to try to fix it.
Finally soon English and Spanish leagues will start. This is actually the period that the file will be tested since holders of the european trophies are considered clubs from these leagues and the access list will be changing due to their domestic performances. If you notice anything weird I would also like to see a screenshot of it (exact time will help too)

Hi eye, great work,

in this spreadsheet for the next season (23-24) is maybe one mistake, austian LASK should have coef. 36 instead of only austrian 6

[eye]

Hi eye, great work,

in this spreadsheet for the next season (23-24) is maybe one mistake, austian LASK should have coef. 36 instead of only austrian 6

Thanks ! I had linked with another LASK from Bert's site but it is fixed now