UEFA Seeding & Qualification based on ClubElo Values - Updates & Feedback

including formats, draws, seedings, etc.
Post Reply
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

UEFA Seeding & Qualification based on ClubElo Values - Updates & Feedback

Post by Stadion »

Hi everyone,

I had originally used dnina10's thread for this, but I don't want to hijack it entirely given that there was some feedback and updates.

Link to spreadsheet: https://docs.google.com/spreadsheets/d/ ... 7MvZu-t1U/
Quick summary:
  • This is a fully automated spreadsheet that requires zero manual input to be updated. It refreshes daily around midnight.
  • The primary sources are Bert's resources (primarily around club coefficients and 10Y rankings) and domestic league tables on Soccerway
  • Given that it is automated, there may be errors -- so if you're after close to 100% reliable information I would suggest using Bert's or dnina10's tables which are manually updated (as far as I know)
  • Apart from tracking near-realtime seeding across all qualification rounds and group stage pots for all 3 UEFA tournaments, there are two additonal tabs:
  • The "CLQ1+2+3 Analysis" tab tracks the clubs currently in the CL qualifying rounds, who the runners-up and third placed clubs are, how many points behind they are, and what their respective UEFA coefficients are. This is to show what the implications would be if there's a shift in champions across these leagues
  • The "Revenue Distribution" tab shows -- based on current standings -- how much each club is set to earn in starting fees, qualification round fees, 10y coefficient fees, and in the case of elimination: elimination fees
The primary purpose of this thread is for any forum participants to provide feedback, ask questions and flag any errors as they appear. I'll also be providing updates here whenever I make any changes to the sheet.
Last edited by Stadion on Tue Feb 06, 2024 01:14, edited 3 times in total.
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

TODO LIST:

- Make sure the correct teams and associations are greyed out/bolded when the access list is rebalanced depending on how title holder spots are used.
Last edited by Stadion on Sat Apr 16, 2022 03:08, edited 2 times in total.
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

Update [14 April 2022]:

The "10Y Coeff Revenue" tab has been renamed to "Revenue" and now tracks all estimated revenue distributed to clubs at the conclusion of the qualifying rounds. I.e. the fixed amounts for group stage performance aren't included, and neither are market pool fees.

Link here: https://docs.google.com/spreadsheets/d/ ... 6&range=A1
Englandmad2007
Posts: 413
Joined: Tue Feb 08, 2022 11:52

Post by Englandmad2007 »

Stadion wrote: Thu Apr 14, 2022 07:42 Update [14 April 2022]:

The "10Y Coeff Revenue" tab has been renamed to "Revenue" and now tracks all estimated revenue distributed to clubs at the conclusion of the qualifying rounds. I.e. the fixed amounts for group stage performance aren't included, and neither are market pool fees.

Link here: https://docs.google.com/spreadsheets/d/ ... 6&range=A1
Non champions path should be League Path
Slava Ukraini!
Image
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

Englandmad2007 wrote: Thu Apr 14, 2022 18:54 Non champions path should be League Path
Updated
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

Request! Could someone please help me confirm what the implications are of the below, provided that we assume Russian clubs will remain banned (and any shifts as a result of that will already be accounted for):

Scenario 1:
- CL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

Ukrainian champions --> CLGS
Danish champions --> CHQ4
Serbian champions --> CHQ3
Greek champions --> CHQ2
Icelandic champions --> CHQ1 (to ensure 32 teams in CHQ1, leaving only 3 clubs in CHPR)



Scenario 2:
- EL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

French #3 --> CLGS
Ukrainian #2 --> NCQ3
Serbian #2 --> NCQ2 (and thereby gains an extra spot in the qualifiers, their remaining spots remain unchanged?)


Please help correct if any of the above is incorrect. Also -- if both scenarios come true, will anything change (in addition to the above)?
User avatar
Club-Mate
Senior Member
Posts: 5104
Joined: Mon Dec 16, 2013 10:20
Location: Virunum - Noricum

Post by Club-Mate »

Stadion wrote: Thu Apr 14, 2022 07:42 Update [14 April 2022]:

The "10Y Coeff Revenue" tab has been renamed to "Revenue" and now tracks all estimated revenue distributed to clubs at the conclusion of the qualifying rounds. I.e. the fixed amounts for group stage performance aren't included, and neither are market pool fees.

Link here: https://docs.google.com/spreadsheets/d/ ... 6&range=A1
great application of Excel by linking of datas from different sheets. In an advanced application like this it is necessary to use Excel at this higher level to minimise errors and what is even more beneficial to save time when repeting this for next time or even for different applications. I like to watch this :applause:
figures often say more than many words.
Englandmad2007
Posts: 413
Joined: Tue Feb 08, 2022 11:52

Post by Englandmad2007 »

Stadion wrote: Fri Apr 15, 2022 08:46 Request! Could someone please help me confirm what the implications are of the below, provided that we assume Russian clubs will remain banned (and any shifts as a result of that will already be accounted for):

Scenario 1:
- CL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

Ukrainian champions --> CLGS
Danish champions --> CHQ4
Serbian champions --> CHQ3
Greek champions --> CHQ2
Icelandic champions --> CHQ1 (to ensure 32 teams in CHQ1, leaving only 3 clubs in CHPR)



Scenario 2:
- EL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

French #3 --> CLGS
Ukrainian #2 --> NCQ3
Serbian #2 --> NCQ2 (and thereby gains an extra spot in the qualifiers, their remaining spots remain unchanged?)


Please help correct if any of the above is incorrect. Also -- if both scenarios come true, will anything change (in addition to the above)?
Looks pretty solid!

On a different note (apologies if this is the wrong place to discuss this matter) I wonder if the Super Cup will be moved from Kazan, no way we can go to Kazan for a game.
Slava Ukraini!
Image
Ed
Posts: 756
Joined: Sat Mar 30, 2013 16:35
Location: Netherlands
Contact:

Post by Ed »

Stadion wrote: Fri Apr 15, 2022 08:46 Request! Could someone please help me confirm what the implications are of the below, provided that we assume Russian clubs will remain banned (and any shifts as a result of that will already be accounted for):

Scenario 1:
- CL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

Ukrainian champions --> CLGS
Danish champions --> CHQ4
Serbian champions --> CHQ3
Greek champions --> CHQ2
Icelandic champions --> CHQ1 (to ensure 32 teams in CHQ1, leaving only 3 clubs in CHPR)



Scenario 2:
- EL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

French #3 --> CLGS
Ukrainian #2 --> NCQ3
Serbian #2 --> NCQ2 (and thereby gains an extra spot in the qualifiers, their remaining spots remain unchanged?)


Please help correct if any of the above is incorrect. Also -- if both scenarios come true, will anything change (in addition to the above)?
Stadion, nice work so far :up: :up:

regarding scenario 1: ISL-CH stays in preliminary round (at least that's what happened in this situation in previous years when the preliminary round in CL existed). Instead ISR-CH also moves to CL-CHq2.

regarding scenario 2: SRB-N2 stays where it is in ECL-q2. Instead TUR-N2 also moves to CL-NCq3 (at least that's what happened in this situation in previous years).

As a consequence extra rebalancing is necessary:
CRO-CW -> EL-q3
SUI-CW -> ECL-q3
MAC-CW and ARM-CW -> ECL-q2
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

Club-Mate wrote: Fri Apr 15, 2022 11:21
Stadion wrote: Thu Apr 14, 2022 07:42 Update [14 April 2022]:

The "10Y Coeff Revenue" tab has been renamed to "Revenue" and now tracks all estimated revenue distributed to clubs at the conclusion of the qualifying rounds. I.e. the fixed amounts for group stage performance aren't included, and neither are market pool fees.

Link here: https://docs.google.com/spreadsheets/d/ ... 6&range=A1
great application of Excel by linking of datas from different sheets. In an advanced application like this it is necessary to use Excel at this higher level to minimise errors and what is even more beneficial to save time when repeting this for next time or even for different applications. I like to watch this :applause:
figures often say more than many words.
The master sheet that sits in the back end feeding the 'public' one is getting incredibly complicated at this point -- but so far it's working well without major hiccups. I have been able to implement a backup solution as well in case domestic league tables are pulled incorrectly. :up:
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

Ed wrote: Fri Apr 15, 2022 12:16
Stadion wrote: Fri Apr 15, 2022 08:46 Request! Could someone please help me confirm what the implications are of the below, provided that we assume Russian clubs will remain banned (and any shifts as a result of that will already be accounted for):

Scenario 1:
- CL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

Ukrainian champions --> CLGS
Danish champions --> CHQ4
Serbian champions --> CHQ3
Greek champions --> CHQ2
Icelandic champions --> CHQ1 (to ensure 32 teams in CHQ1, leaving only 3 clubs in CHPR)



Scenario 2:
- EL title holder qualifies for CLGS via their domestic league

Here's what I *think* will happen:

French #3 --> CLGS
Ukrainian #2 --> NCQ3
Serbian #2 --> NCQ2 (and thereby gains an extra spot in the qualifiers, their remaining spots remain unchanged?)


Please help correct if any of the above is incorrect. Also -- if both scenarios come true, will anything change (in addition to the above)?
Stadion, nice work so far :up: :up:

regarding scenario 1: ISL-CH stays in preliminary round (at least that's what happened in this situation in previous years when the preliminary round in CL existed). Instead ISR-CH also moves to CL-CHq2.

regarding scenario 2: SRB-N2 stays where it is in ECL-q2. Instead TUR-N2 also moves to CL-NCq3 (at least that's what happened in this situation in previous years).

As a consequence extra rebalancing is necessary:
CRO-CW -> EL-q3
SUI-CW -> ECL-q3
MAC-CW and ARM-CW -> ECL-q2
Thank you for all the information -- it's hugely helpful.

Now I have implemented a toggle that automatically rebalances the access list depending on if the CLTH or ELTH (or both) spots aren't used. I'd love to make this 'toggle' public so that anyone viewing the sheet can input CL/EL winners and see how the lists rebalance in realtime. But I don't have a workable solution for that -- yet. :D
barracuda
Posts: 59
Joined: Sun Mar 18, 2018 03:23
Location: Salzburg

Post by barracuda »

Hello @Stadion

great work! But in the "Revenue Distribution" sheet you are not using the correct 10Y Coefficients.

I recommend this site from kassiesa: https://kassiesa.net/uefa/data/method5/ ... -2022.html
User avatar
rmsg
Posts: 188
Joined: Wed Nov 23, 2005 17:26
Location: Coimbra, Portugal

Post by rmsg »

The spreadsheet is not correct concerning Portugal. Sp. Braga (currently 4th place) should be in EL and Gil Vicente (currently 5th place) should be in ECL.
Last edited by rmsg on Fri Apr 15, 2022 16:18, edited 1 time in total.
Ed
Posts: 756
Joined: Sat Mar 30, 2013 16:35
Location: Netherlands
Contact:

Post by Ed »

Stadion, at second thought, another rebalancing possibility is more likely in your scenario 1 + 2.

Normally when the CL-TH spot isn't used in CL-GS then there is rebalancing applied in the CL-q champions path with the result that 19 (instead of the normal 20 losers of CL-q1 and CL-q0) drop to ECL-q2ch and one lucky loser is drawn that drops to ECL-q3ch.

Following that chain of thought it is more likely that, as in your case there are two teams less dropping from CL-q1ch to ECL-q2ch, there will be two lucky losers drawn. Those two will drop to ECL-q3ch instead.
In that case MAC-CW and ARM-CW will stay in ECL-q1 and GRE-CW also moves from ECL-q2 to ECL-q3 (besides SUI-CW).

We haven't seen such a precedent before (now with Russia probably suspended), so it's just my guess...
Stadion
Posts: 435
Joined: Sat Mar 31, 2018 16:13

Post by Stadion »

barracuda wrote: Fri Apr 15, 2022 14:21 Hello @Stadion

great work! But in the "Revenue Distribution" sheet you are not using the correct 10Y Coefficients.

I recommend this site from kassiesa: https://kassiesa.net/uefa/data/method5/ ... -2022.html
Fixed! It was pulling in the 10Y rank from 2024 for some reason.
Post Reply