In 2000 I wrote an Excel spreadsheet which I called the Euro 2000 Wallchart. I called it this because come every major tournament, everyone I knew would get their poster, or newspaper pullout and chart England’s (and other teams) performance throughout the competition.
There was nothing spectacularly original with this idea – I saw one done for World Cup 98. I made it better and look smarter. But there were a couple of flaws which I had fix along the way.
I made a conscious decision to do two things with the spreadsheet: I didn’t want it to contain macros, because they can be nasty and untrustworthy if you don’t know who’s sending it; and I wrote it in such a way that the curious could delve into it to see what I did. I’m not an Excel whizz. Another reason I didn’t put any macros in was because I’m rubbish at macros!
I never distributed beyond close friends, but these things end up flying around the world, and to this day I receive a steady trickle of email about it, asking for adaptations for their leagues etc.
I didn’t get around to producing a World Cup 2002 version, for reasons that I’ll come onto in a minute. But others did modify mine, leaving alone the properties page (which is the only page that gives an email address).
This year we’re on Euro 2004, and at least three different sets of people have adapted the sheet that I wrote for the new competition. But they’ve locked it up to some extent so that you either can’t enter details into the sheet beyond a certain point, or can’t see the formulas used. I’m beginning to get emails about this.
I’d love to help, but as I didn’t make the changes, I’m at a bit of a loss. I have used a password cracking macro (from here) to get into these sheets. Use the following depending on what version of the program you’re using.
1. If there are two small Euro logos at the top of the page, one over the dates and times of the first round matches, and one over Grupo A/Group A, then try using AAAAAAABABAw in Tools > Protection > Unlock Workbook.
2. If the map of Portugal is to the right of Group A and the spreadsheet is titled Euro 2004 Wallchart, then try using this password: AAAAAABBAAB7.
3. Another version is modified by “yakyak” and is unprotected by password. It has a larger Euro logo spanning the top of the sheet across from the first round games to group section.
In either case, you then should go to Tools > Options, select View, and check the Row and Column Headers, Horizontal Scroll bar, and Vertical Scroll bar, as well as Sheet Tabs.
If you then look at the columns, there’ll be a gap between at least two of the columns alphabetically. Highlight the columns either side of this gap (ie. Cols T:BV) by clicking on the letters themselves, right hand click, and choose Unhide. This should reveal the inner workings of the spreadsheet. There are three sets of three sorts, each changing the teams’ points, goal difference and goals for.
The reason for not putting together a new version of the spreadsheet was simple: I couldn’t get one working properly on the group round. If a group goes to a three or even four way tie, the mathematics and permutations of what’s involved get awfully complicated, with matches played between the tied teams going into their own mini-league. I have seen someone do this, but didn’t want to rip off their code.
In fact the workings that I employed are those used in traditional leagues, like England’s Premier League. It sorts teams first by points, then by goal difference, and then by goals scored.
But UEFA doesn’t use this system. UEFA’s rules are outlined here, but in summary if two or more teams finish level on points, they’re divided by looking at the results between those teams:
1. Points between the teams involved. (e.g. if two teams are on the same points, but one beat the other in the group round, that team goes higher)
2. Goal difference between the teams involved.
3. Goals scored between the teams involved.
4. Goals scored between the teams involved.
5. Goal difference in all games.
6. Goals scored in all games.
7. Coefficient points from World Cup and Euro competitions.
8. Fairplay so far (ie. Yellow cards etc)
9. Drawing of lots.
Oh and if the final group game between two teams ends in a draw and they have the same number of points, goals scored and goal difference, they ignore most of the above criteria and go to a penalty shoot out.
You try coding that lot into an Excel sheet!
I may still put together something for this year, but there’ll have to be some degree of originality involved before I do it. And, er, quite a lot of work!
Read on in the extended entry to learn more about the sort techniques involved to produce your own league table.
UPDATE: I will publish more about this at a later stage, but I’d recommend using the official UEFA spreadsheet which can be found here since it does follow all the competition’s rules regarding splitting teams. Once I’ve fully understood how it works, I’ll publish a full explanation.
The sort technique used in the worksheets that I’ve put together does not use macros, but will only work with a group of four or fewer teams. If you have larger groups, then you really need to use macros and either use the built in Excel Sort function.
There are several different sort algorithms, and at a later point, I’ll detail there some more in here, and hopefully supply some answers. Come back later, or leave me a comment.
Comments
13 responses to “Euro 2004 Spreadsheet”
Hi Adam,
Somehow happened on your website whilst randomly surfing and saw the above entry.
I have a feeling we’ve previously exchanged emails, but I get so many that it’s hopeless keeping track of them all, so forgive me if I have you confused with someone else!
After reading your entry, I thought you might like to know that I’ve just finished my Euro2004 sheet at http://www.michaelwray.co.nz – where I believe I’ve got the criteria responding correctly to the new penalty shootout rule…. although UEFA are vague about if the new rule only applies if 2 teams are tied or whether it would apply to split 2 of 3 tied teams should they meet the criteria!
Regards,
Michael.
PS “..Sussex Stationers (great name)..”
Why great name? Could it be the allusion to Sussex? Are you from the same county as me?
Hi Michael,
I’ll go into more detail soon, when I’ve completely understood how they’ve done it, but in the meantime UEFA have actually put their own spreadsheet together for this year’s tournament.
It can be found at http://www.euro2004.com/Fanzone/Freebies/forecast/index.html
In the meantime, congratulations on some very fine spreadsheets at http://www.michaelwray.co.nz
just hit your website. Its nice to see someone sharing information for once. Great stuff
Hi Adam,
if you make a sheet about the Euro2004, send mee a copy.
Tanks
Roger
(from Portugal)
Greece and Russia seemed a bit screwed up. Just type in 2-0 to portugal for the first result and check the table.
I haven’t actually put any of the spreadsheets together this year.
I’d recommend using the UEFA version of the spreadsheet as the only fully working one I’ve found. You can download it from here: http://www.euro2004.com/Fanzone/Freebies/forecast/index.html
Great write-up. Explains it all.
The problem mentioned by Fred looks like:
the version of Eur02004 that found its way to me needs the password AAAAAABBAAB7, then go to cell L9 and replace the contents (Russia) with =BK11
Re-protect and done.
Thanks, Tony
I am using your euro 2004 xls to run a competition at work. Something simple such as for each correct score people get 3 points and a correct result but wrong score 2 points.
It all works ok until the knockout stage when a couple of difficult people have said they think it will be a draw! This could be a valid result as my competition is based on the playing time result.
My solution was to give the team that they think will win (the penalty shoot out) a fraction of a goal.
This may be something to think about for future xls – if you do any more
Otherwise the Xls is great and I don’t have any problems
Hi Adam Great info on this page will be back to check it out again. Do you have the password for the EURO 2004 forecast spreadsheet that you recommend above?
Hi Adam,
Thanks for the great spreadsheet, has taken me a while to find the password to unlock the sheet but still.
Come on England
Would it be possible to mail any future sheets for major footballing events – I understand that nearly everyone has asked this but would be most grateful.
Keep it up
Cheers
Sean
Hi Adam,
Thanks for the great spreadsheet, has taken me a while to find the password to unlock the sheet but still.
Come on England
Would it be possible to mail any future sheets for major footballing events – I understand that nearly everyone has asked this but would be most grateful.
Keep it up
Cheers
Sean
Please can you help me to automatically update my premiership table, similar to the Euro 2004 forecast. There are about 20 teams and I want the table order to be sorted automatically.
I have seen the formulas as used in the world cup forecast but I am having problems translating the formulas to suit the premiership table.
Can I send the table and the formulas for help.
Hi Adam,
Great work with the spreadsheets.
How do you manage to crack the password on protected Excel sheets/workbooks?
Jet