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.
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.
Stadion wrote: ↑Thu Apr 14, 2022 07:42Update [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.
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 wrote: ↑Thu Apr 14, 2022 07:42Update [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.
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
figures often say more than many words.
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.
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
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 wrote: ↑Thu Apr 14, 2022 07:42Update [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.
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
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.
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
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.
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.
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...
