Excel Statistics for 20Minutos Blog Awards 2014

#32 Excel Statistics for 20Minutos Blog Awards 2014

NB: Si prefieres leer este blog en español, recuerda que con Google-Blogger (el herramiento usado para este blog) es posible traducir este blog de ingles a español. El 'Google Traductor' esta en el menu a la derecha.

The Spanish newspaper 20Minutos, the 2nd newspaper in Spain, with a daily (free) edition of approx. 1 million, organizes the 9th edition of its Blog Awards ("Premios 20Blogs"), the biggest blog-contest in the Spanish speaking world. The 2014 edition has 7223 participating blogs from (mostly) Spanish speaking bloggers, and they are not only from Spain but e.g. also from Latin America (where BTW 20Minutos is also published in 2 countries: USA and Mexico). Until 2nd of March everybody with a 20Minutos account can vote 20 times, one time for every blog-category, and the blog with the most votes wins the public prize, although the jury decides who will be the winner of the 1st prize: 5000 euro.
My blog exists over 1 year now, and although it´s not in Spanish, I also signed-up my blog, and 20Minutos accepted it. Maybe they accepted that my blog is in English because English is so popular in Spain nowadays, there are so many people doing courses, even MP Rajoy seems to have done the course 'learn the 1000 most used English words'. Or maybe my blog was accepted because I have several post which have something to do with Spain, or Madrid where I live, e.g.:

* 2015 year-calendar with holidays in Spain and Madrid ('calendario laboral'), with Spanish names of weekdays and months, see:
http://worktimesheet2014.blogspot.com.es/2015/02/month-calendar-for-january-2015-in-excel.html

* timesheet to track worked hours (´plantilla de control de horas trabajado', 'los partes'), with integrated Spanish calendar, and e.g Spanish summer-schedule (´horario intensivo'), see:
http://worktimesheet2014.blogspot.com.es/2015/01/timesheet-for-worked-hours-2015.html

* Excel with statistics results 10K run 'Corre por el niño' (organized by children hospital Niño Jesus), see:
http://worktimesheet2014.blogspot.com.es/2014/11/statistics-result-10-km-run-corre-por.html

* Infographic win/loss record of Rafa Nadal, see:
http://worktimesheet2014.blogspot.com.es/2014/10/infographic-winloss-record-rafa-nadal.html

* Match schedule 2014 FIBA Worldcup Basketball Spain (´Mundial baloncesto') , see:
http://worktimesheet2014.blogspot.com.es/2014/08/worldcup-basketball-2014-match-schedule.html

20Minutos allows you to promote your own blog to attract votes in the Blog Awards, so: if you like my blog, you can vote for it here:

http://lablogoteca.20minutos.es/excel-ejemplos-para-tu-trabajo-deportes-y-mas-50022/0/

To do so, you have to register here:

http://www.20minutos.es/usuarios/registro/

But enough self-promotion, let´s start with what this blog is about: MS Excel. And I thought it could be interesting for all the participants of the Blog Awards to see some statistics about it. Of course 20Minutos also made one, about the number of participating blogs per year, see:

http://blogs.20minutos.es/premios20blogs/vota-ya-por-tus-favoritos-en-los-premios-20blogs-tienes-7-221-candidaturas-entre-las-que-elegir-y-20-votos-para-repartir/

And this is 1(of 4) statistics in my Excel, see fig.1.
NB: in their graphic, the total of 2013 is not correct (6790), or better said, not corresponding to their data on this page (my source):           
http://lablogoteca.20minutos.es/premios-20blogs/ediciones-anteriores/2013/

This graph shows that the number of blogs participating in the Blog Awards increased almost every year, from a bit more than 2000 in 2006 to over 7000 in 2014.

                                               fig.1

                                      fig.2


The chart in fig.2 shows for the 2014 Blog Awards the number of blogs for all 20 blog-categories (except 'Best Blog' and 'Public Prize', because of course all blogs participate in these 2 'categories'). My blog is in category 'Blogosfera' (a 'Rest'-category, so for blogs which don´t fit in any other category) with 933 blogs, although I thought I signed up for category 'Internet etc.', with 502 blogs, so with 'less' competition then in category 'Blogosfera', but still, 501 competitors... Maybe next year I´ll participate with a blog about motors .. (this category has only 48 blogs).

 

fig.3

fig.4

The charts in fig.3 and 4 are similar, both have the 'dimensions' (axis) time and blog-category, but they differ on which axis (x or y) these dimensions are placed. Both have a filter: Rank blog-category <= 3, so it shows per year only the top 3 blog-categories (excluding the 'Rest-category'). In fig.3 you can see that there are 5 categories which every year have the most blogs, and that in the beginning the 'Personal-category' was the most popular but this decreased during the years. And in fig.4 you can e.g. see that before 2011 the 'Personal-category' was the one with ranking 1, but that since then, the 'Culture-category' took over.

How did I make this Excel? I used this page as my data-source:
http://lablogoteca.20minutos.es/premios-20blogs/ediciones-anteriores/2013/

Maybe I could have used the 'copy/paste' method to get the data from this (tabbed) HTML-table in Excel, but I thought it would be nicer to use the Power BI option of Excel, and use the function "Get external data from website", but this didn´t work. But from this site:

https://eagereyes.org/data/scrape-tables-using-google-docs

I learned that Google-Sheets (Google´s Excel..) is better in 'scraping' data from websites then Excel. So I created a spreadsheet with Google-Sheets on Google-Drive (so in the cloud) and this function did the job perfectly:

=IMPORTHTML("http://lablogoteca.20minutos.es/premios-20blogs/ediciones-anteriores/2013/", "table",1)

I shared this Google-spreadsheet here:

http://goo.gl/At8RkU

see also fig.5 

 

fig.5:  Google-spreadsheet with data imported from website 20Minutos

And then I exported (downloaded) this cloud-spreadsheet from Google-Sheets to my 'good-old' desktop Excel (version 2013). And this Excel is the database (´server´) for the Excel with the statistics (´client´), and to 'bind' them, I used an Excel-query, and to get the data of every year, with 1 worksheet (and 'named range') per year in 1 table, I used SQL´s union-operator:

--
SELECT R_2006.Categorias, R_2006.Ganador, R_2006.Concursantes, R_2006.Rank, R_2006.Año
FROM `D:\projects\20Minutos_BlogCompetition2014_v4_input.xlsx`.R_2006 R_2006
union
SELECT R_2007.Categorias, R_2007.Ganador, R_2007.Concursantes, R_2007.Rank, R_2007.Año
FROM `D:\projects\20Minutos_BlogCompetition2014_v4_input.xlsx`.R_2007 R_2007
union

ETC
--

For more info about using Excel as a database, see my post:
http://worktimesheet2014.blogspot.com.es/2014/04/excel-and-relational-databases.html

The 4 statistics in my Excel are made with pivot-tables, about which I wrote in several posts, e.g.:
http://worktimesheet2014.blogspot.com.es/2014/01/timesheet.html


And to conclude this post, I´d like to say good luck to all my fellow-bloggers in the "Premios 20Blogs" Blog Awards.


Downloads:

#Mirror 1: Scribd.com (PDF file):
https://es.scribd.com/doc/255471225/20Minutos-Blog-Awards-2014-some-Statistics

#Mirror 2: MS Onedrive (Excel file):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

-server:
http://1drv.ms/1z8iMEn

-client (with statistics):
http://1drv.ms/1MdCR5o

#Mirror 3: Google Drive (1 zip file with Excel and PDF files):
http://goo.gl/30xyB1

Infographic Win/Loss record Rafa Nadal in Excel

#25 Infographic Win/Loss record Rafa Nadal in Excel

This post has 2 Excels (see paragraph Downloads below):
-Tennis_Input.xlsx: data with match-results (copy this to dir: C:\Temp\)
-Tennis_Statistics.xlsx: has Tennis_Input as its datasource.

The other day I saw in the Spanish sports-newspaper Marca (30/9/2014, pag.68, or see www.marca.com) an 'infographic' (made by Sergio Bartolome) of the win/loss record of Rafa Nadal, from the year of his break-through in the tennis world top (2005) until hist latest Wimbledon (july 2014). The graphic showed wins vs losses on the x-axis, and the years on the y-axis. I was wondering if I could make this graphic in Excel, and from scratch, so not using the numbers form Marca, but some website with the results of all the match-results from Nadal from 2005 until 2014. And the answer is yes, see fig.1 for the end-result (Tennis_Statistics.xlsx).
BTW: the numbers in my statistics are not exactly the same as those in Marco, because in my statistics I included doubles, Marco only has singles.

  
        fig.1: Win/Loss record Nadal


The type of graphic in Marca reminded me of a population-pyramid graph,with on the y-axis the age-ranges and on the x-axis the genders (men on the left and women on the right side of the y-axis), and this graph can be made in Excel, see e.g.:

http://www.uvm.edu/~agri99/spring2004/Population_Pyramids_in_Excel.html

or:

http://chandoo.org/wp/2010/08/03/immigrants-in-denmark/

Comparing the win/loss graph with the population-pyramid graph in this example (of the 1st URL), you have this equivalents:
- men : losses (in Excel-graph: data series 1)
- women : wins (in Excel-graph: data series 2)
- age-classes :  years (in Excel-graph: category)

To get the numbers of the losses on the left side of the y-axis (years), so the negative numbers on the x-axis, without the minus sign, you have to give the cell format 'Number' '0:0', for more details see:

http://peltiertech.com/Excel/NumberFormats.html

or:

http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx

The other part of this Excel is the data-part (Tennis_Input.xlsx). I found all (903) match-results (singles and doubles) of Nadal here:

http://www.itftennis.com/procircuit/players/player/profile.aspx?playerid=100007935

The format of the data on this site is quite structured, but I couldn't use PowerQuery to get the data from the Web into a table in Excel. So I copied all data from the website myself and made a table of it, with the result-data (Win/Loss) in column B and I added column F, with the year (in which the match was played). So these (yellow) columns (B and F) form the table with the input for my Excel with the Win/Loss Record (Tennis_Statistics.xlsx), see sheet-1 ('DataWeb') and fig.2

                                  fig.2 Input-table with data from website


 In sheet-2 ('TableResult' and fig.3) you can see a pivot-table and graph for this table. The pivot-table has 2 values: total number of matches won/lost and the percentage won vs lost.

    

fig.3: pivot-table for input-table with match-results Nadal

And in sheet-3 ('Finals') I used the table of sheet-1 (pink lines) to get the results (won/lost) of the finals Nadal played in 2014, using a 'sparkline' mini-graph of type 'Win/Loss' to display this binary variable (W/L), see fig.4.

 

fig.4 sparkline mini-graph results (W/L) finals Nadal in 2014

Another example of a mini-graph you can see in fig.1, in the table, column ´Total', which shows besides numbers a bar-chart. For more about sparklines/mini-graphs, see:

http://www.vertex42.com/blog/help/excel-help/sparklines-in-excel.html

To get the data from this Excel 'database' (Input) to the other (Statistics), I made an connection of type Excel-Query (SQL). For my post about how to use data from one Excel into another, see:

http://worktimesheet2014.blogspot.com.es/2014/04/excel-and-relational-databases.html

The final part of this Excel (Statistics) was to add some 'decoration', so that if it could  be used for a newspaper like Marca, as readers like an (info)graphic probably more then just a plain Excel-graph. One of the 'decorations' was to use tennis-balls as a fill-pattern for the bar-charts. For how you can do this, see:

http://www.internet4classrooms.com/excel_picto_chart.htm


or:

http://m.youtube.com/watch?v=8um7jaOw_vA

And I added 2 (royalty-free) photos to the Excel, which I got from:

http://pixabay.com/es/rafael-nadal-288554/
http://pixabay.com/es/cancha-de-tenis-tenis-amarillo-443267/

(my thanks to the autors of these photos).

Although infographics are mainly about presentation (visualization of data), I also made sure my Excel passed the 'accesiblity-check' included in Excel (to be sure the information is also accesible for people with a visual handicap), e.g. I added 'alternative text' for the infographic and hyperlinks. For my post about MS Excel and accesiblity, see:

http://worktimesheet2014.blogspot.com.es/2014/01/calendar-2014-v6-accessible-version.html

And to conclude, some links of interesting websites I found while making this Excel:

- about Excel and infographics: 
http://www.excelcharts.com/blog/infographics-data-visualization/

- nice infographic about Nadal in Marca after winning his 9th (!) Roland Garros title:
http://www.marca.com/2014/06/09/multimedia/graficos/1402304176.html

- example of how MS Business Intelligence (BI) products (like Excel is, and its plugins like Powerview) can be used for the tennis-'business':
http://husting.com/2012/09/17/tennis-analytics-with-microsoft-sharepoint-and-powerview/

NB: for my post about MS Excel and BI, see:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-and-business-intelligence.html

 -Tennis and Big Data:
http://www.bigdata-startups.com/BigData-startup/the-australian-open/

- Infographics sports:

http://visual.ly/sports-infographics
 NB: you can upload your infographics to this site, which I did, see:
http://visual.ly/winloss-record-rafa-nadal

http://www.pinterest.com/infographics4u/sports-infographics/
 NB: you can upload your pics to this site, which I did, see:
http://www.pinterest.com/eigeres/infographics-sports/

http://deadspin.com/the-12-best-sports-infographics-of-2013-1484953458


Downloads:


#Mirror 1 (PDF file):

https://es.scribd.com/doc/244207372/Infographic-Win-Loss-record-Rafa-Nadal 

 #Mirror 2 (Excel file):
NB: this site has MS Onedrive, which has 'Excel-Online', so you can view my Excel-files here if you don´t have MS Excel on your PC

- Input
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21245

- Statistics
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21244

#Mirror 3 (Excel and PDF file in 1 zip):

https://drive.google.com/file/d/0BywxxSJoaUYxcVk1emtqZGFqSnM/view?usp=sharing

Match, Access app to track World Cup Soccer 2014

#18  Match, Access application to track World Cup Soccer 2014


Note 29-7-2014:
I updated the Match-database (MDB-file)  with all the match-results and made a backup of the file after every phase, which you can find (as a zip) in par. Downloads. I refer to this MDB with V2 and to the original MDB with V1. In part 2 of this blog you can see the results, in which I used the match Spain - Netherlands (..) to show which match-data you can registrate in Match.



Part 1 (before start Worldcup):

The Excel-reports for the 2014 FIFA Worldcup Brazil from my previous post, see:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-with-match-schedule-for-2014-fifa.html

were generated by Match, a program I made some time ago to track the UEFA Eurocup and FIFA Worldcup competitions, and which I offer you for free to do the same for the 2014 Worldcup (see Downloads below).

I made Match with MS Access 97. MS Access is  (like MS Excel) part of MS Office (although it is not included in the cheaper Office-versions). If you don't have Access on your PC, don't worry, because Match includes the Access (97)-runtime, which means that once you have installed Match, you also have Access (or I should say: you have the Access-components which Match needs, e.g. the Jet database engine). And if you wonder if a program from 1997 still works in 2014, I can tell you I tested it on 3 PC's: 

- Windows XP with Office 97 Profesional (with Access)

- Windows 7 with Office 2010 Starter (without Access)

- Windows 8 with Office 2013 Profesional  Plus (without Access installed)

and on all 3 PC´s Match worked OK.

Although this blog is about MS Excel, I wanted to make an exception in this post and tell you something about MS Access.

Access is a desktop relational database application. See post #13 for more about relational databases:

http://worktimesheet2014.blogspot.com.es/2014/04/excel-and-relational-databases.html

With Access you build quite easily an application, type 'Windows-desktop'. And because all Office-applications have OLE Automation, they can easily 'communicate' with each other, a feature which I used to generate the Excel-reports from Access (Match). And Access it's own reports you can export in e.g. RFT-format, which you can read in MS Word. 

Access offers you the possiblity to split your application in 2 parts: a Front-End (MDE) executable ('client'), which has the user-forms and logic (functions) and a Back-End, the (MDB) database ('server'). I used this for Match, so it has a 'client-server' architecture: Match_v6_FE.mde is the client (Front-End) and Match_v6_BE.mdb (Back-End) is the server.

Let me now tell you something about Match. 

Match is software with which all 'administrative tasks' for a sport-competitions can be automated, like:

 * making a match-schedule 

 * registrate the match-results (goals, players who scored, yellow/red cards etc)

 * calculating ranking teams and topscorer-list 

 * generating reports in both textual and graphical format, e.g. a match-schedule with locations on y-axis and dates on x-axis.
* betting on match-results and teams for rank 1,2,3, 4 in e.g. Worldcup

As I said before, I originally made Match to track Euro/Worldcup competitions, but Match can be used for every simular competition. To illustrate this: once I made a 'tailor made' version of Match for a big school-soccer tournament in the Netherlands see:

http://www.rotarystreetsoccer.nl/

and for some reports generated by Match:

http://goo.gl/yUGMh9

Some statistics of this tournament 'Rotary Street Soccer Oldenzaal': 18 schools and about 300 teams, more than 2000 children who play about 700 matches. 


But now about the Match version for 2014 FIFA Worldcup Brazil. I already filled Match with the whole match-schedule (so not only the matches in phase 1 (qualifications), but also the 'virtual matches' in the final-phases (1/8-final, 1/4-final etc) and all the teams (and all the 32 flags...). So if you want to use Match to track the Worldcup, you only have to enter the match-results (see fig.1 for the form where you must input the match-results) and use the functions to copy the teams who qualified in round N to round N+1. 

fig.1: Form to input match-results (blue fields)

For more details about how to use Match, see these user-manuals:

* User Manual Match for Worldcup 2014:

http://www.scribd.com/doc/227239062/Match-v6-UserManual-for-WorldCup2014

* User Manual Match (general):

https://www.scribd.com/document/119063075/Handleiding-Match-2012-pdf

http://www.scribd.com/doc/29620331/Match-User-Manual-2008

* User Manual for organizing a Worldcup-Pool (betting) with Match:

http://www.scribd.com/doc/30301394/UserManual-Pool-FIFA-Worldcup-2010-v1-2

NB: for an betting -alternative to Match, see the Excel I made for this post:

http://worktimesheet2014.blogspot.com.es/2014/05/pool-for-betting-2014-world-cup-soccer.html

Before this 'release' I tested Match by doing a simulation of the whole tournament, that is: I entered (random) scores for all matches, and I made a backup of the database (MDB-file) at the end of every phase. I included this database-backups, see  Downloads.  So if you want to see the result of my simulation of the Worldcup at the end of phase X, you must change the name of the backup-file for phase X to 'Match_v6_BE.mdb'. In fig.2 you can see how Match looks like before the begin of the tournament And for the final-result of my simulation, see fig.3 (from file 'Match_v6_BE_Phase5_end.mdb'), which shows in my prediction, the winner of this Worldcup will be  ... Brazil, beating Spain with 1-0 (see Match ID 64).

To end this post with Excel again: 

A nice Excel with which you can also track the Worldcup (and which I used to validate the results of my simulation of the Worldcup with Match) is: 

http://www.excely.com/football/2014-fifa-world-cup-schedule.shtml

But whether you use Excel or Access to track the Worldcup, the conclusion could be that MS Office is a great software suite for doing this, and with this blog I wanted to show you another member of the Office family.

And there are also many smartphone apps for the Worldcup. One of them of course by FIFA, but after having seen this video about FIFA:

https://www.youtube.com/watch?v=DlJEt2KU33I

I decided to look for something else, and found this nice (Dutch) app:

http://m.apps.store.aptoide.com/app/market/nl.itnext.ec2012/31/5333597/World%20Cup%202014

And if you think Match could be something for your club, write me an email, with subject 'Request info Match'.


Note 8-6-2014:

During the WorldCup I'll update this blog with the results after every phase, and also include the Match_BE.MDB database file, so if you want to use Match to track the Worldcup, you can use this database.
And maybe I'll publish it also on my new own free TK-domain, from the (is)land of Tokelau :)   

http://www.eigersoftware.tk/



Figures simulation of WorldCup 2014 in Match:

fig.2: After phase 1 (qualifacions)

fig.3: After final 

Part 2 (after end Worldcup)

I use the match Spain - Netherlands (1-5 !) to show what match-data you can registrate in Match.

fig.4: Match details (teams, date,time, location, referee) and result

fig.5: goals

--

fig.6: yellow&red cards

fig.7: line-up and mark (for performance, 0-10).

NB: marks for Dutch players in this match I copied from:

http://www.wtf.nl/wk2014/21201/de-rapportcijfers-van-oranje-1.html

--

And Match can generate a Word-report with all these data in this format:

** match #3:  Spain - Netherlands; 1 - 5 ** 

goals:  *T.27 Alonso X. (Spain) (PNL), *T.44 van Persie (Netherlands) ,  *T.53 Robben (Netherlands) ,  *T.64 de Vrij (Netherlands),   *T.72 van Persie (Netherlands),   *T.80 Robben (Netherlands)   

cards:  *T.25 de Guzman (Netherlands) yellow_card, *T.41 de Vrij (Netherlands) yellow_card, *T.65 Casillas (Spain) yellow_card, *T.66 van Persie (Netherlands) yellow_card 

lineup player_away:  Cillessen Vlaar de Vrij (*T.out: 77),  Martins Indi Blind, de Jong, Janmaat, de Guzman (*T.out: 62),  van Persie(C) (*T.out: 79),  Sneijder, Robben, Veltman (*T.in: 77),  Lens (*T.in: 79),  Wijnaldum (*T.in: 62) 

Details: location:Salvador , date: 13/06/2014 , referee: Rizzoli 
  

Besides this match-details which you can register in Match, on the FIFA-site you can find some more interesting statistics, see:

http://www.fifa.com/worldcup/matches/round=255931/match=300186510/index.html#games

And to finish this part about the Spain - Netherlands match:  for the readers who might wonder what photos were published in the Dutch media after this match, I include this link (my favourite is photo #1, van Persie as a bullfighter):

http://comee-nl.tumblr.com/post/88703089661/de-mooiste-fotos-van-spanje-nederland-wk-2014



And to conclude this post, I´ll show some figures of reports generated by Match V2 (with all results Worldcup) (reports are included in par. Downloads):

fig.8: match-results per group 

fig.9:  All match results (from all phases)

fig.10: Ranking teams per group (end phase 1)

fig.11: End ranking teams

fig.12: Topscorer-list

NB: I only registered the goals made in the match Spain - Netherlands

Downloads :

V1: 

* Application: Match-installer:

http://goo.gl/5dzEiM

* Backup databases simulation of WorldCup 2014 in Match:

https://drive.google.com/file/d/0BywxxSJoaUYxZmc3VmlUdXF3MDg/edit?usp=sharing

V2:

* Backup databases results of WorldCup 2014 in Match:

https://drive.google.com/file/d/0BywxxSJoaUYxXzRhUjRxa1AxVmc/edit?usp=sharing

NB: to view a MDB-file with the Match-application, you must rename the file to: Match_v6_BE.mdb

* Reports results of WorldCup 2014 in Match:

https://drive.google.com/file/d/0BywxxSJoaUYxRFlCZi1VNzJnRHc/edit?usp=sharing

Excel with Match Schedule for 2014 FIFA World Cup Soccer Brazil

#17 Excel with Match Schedule for 2014 FIFA World Cup Soccer Brazil

For the upcoming Worldcup Soccer I made 3 Excels with the match-schedule, in 3 formats:
-1: Calendar (see fig.1)
-2: Table, with times of your timezone (see fig.2)
-3: Matrix (see fig.3).

To create these Excels, I used a database-program that I once made (with MS Acces 97...), Match, which I used since 2004 until now to track the UEFA and FIFA soccer championships.
In a next post I'll write more about Match, but if you already want to know something more about it, see this webite:

http://eigersoftware.tripod.com/match.htm

or for the Excel-reports that I created with Match for the UEFA and FIFA soccer championships from 2004 until now:

http://www.scribd.com/eigersoftware


The source for the Excel match-schedules is from the FIFA site:

http://resources.fifa.com/mm/document/tournament/competition/01/52/99/91/2014fwc_matchschedule_wgroups_22042014_en_neutral.pdf

which is in calendar-format (as in fig.1), and shows for every match this information:
- Match-ID
- Teams and 'virtual teams' for final-rounds (e.g. team A1 is winner (rank 1) of group A)
- Group (A-H)
- Date and time (in local (Brazilian) time
- City (stadium)

fig.1a: match schedule in Calendar format, with Location on y-axis

fig.1b: match schedule in Calendar format, with Group/Team on y-axis

fig.2: match schedule in Table format

fig.3: match schedule in Matrix format

Match schedule 1: Calendar format (see fig.1a, 1b)

Fig. 1a:
This schedule is like the schedule on the FIFA site. The only difference is the way I labeled the matches for the final-rounds, e.g.:
- match 49 on Sat.28-6, 1/8-finals, is in cells R13-R14, marked as 'M49', with 'virtual teams' A1 (winner group A) and B2 (runner-up group B)
- match 63 on Sat.12-7 (final for rank 3/4) is in cells AC16-AC17, marked as 'M63', with 'virtual teams' M62R2 (loser semi-final match 62) and M61R2 (loser semi-final match 61)
- match 64 on Sun.13-7 (final for rank 1/2) is in cells AD40-AD41, marked as 'M64', with 'virtual teams' M62R1 (winner semi-final match 62) and M61R1 (winner semi-final match 61)

On the y-axis, the 2 cities marked with '#' (Cuiaba and Manaus) have local time UTC-4, the rest has UTC-3.

Fig.1b:
In this calendar, the y-axis has data from the Group/Team-table in Match (in contrast with fig.1a, which had data from the Location-table). Besides, I put a filter on this column, which in fig.1b is applied to group B, so that the schedule only shows the matches for teams in this group, like the Netherlands and Spain (which were the finalist of the Worldcup in 2010...).

So the Calendar-report is like a pivot-table: In Match you can choose which data (Location or Group/Team) you want on the y-axis. On this website you can find another very nice example how you can present the match-schedule from different viewpoints (location, group, team, date):

http://www.marca.com/deporte/futbol/mundial/calendario/schedule.html

Note: I included this schedule only in Download-Mirror #1.

Match schedule 2: Table format (see fig.2)

Note: The flag-icons come from: 

http://www.famfamfam.com

The table in this report has 2 time-columns: 

-  the local (Brazilian) time, which is (for most cities) UTC-3 or (for 2 cities) UTC-4 (see above). 

- 'your' time, so the time in your time-zone, which is a parameter in the Excel, see cells F3-G3, where you can fill the 'offset' of your timezone in hours (F3) and minutes (G3) with respect to Greenwhich Mean Time (GMT/UTC), e.g.: for CEST (Central European Summer time, which is the time in summer for most European countries), you should fill: 2 (F3) and 0 (G3), which means: UTC + 02:00.

To find your UTC-time, see e.g. : 

http://www.thetimenow.com/utc/universal_coordinated_time

http://www.timezoneconverter.com/cgi-bin/tzref.tzc

So if you live in the CEST-time zone, the 1st match you can see at TV at  22:00 (12-6).

Besides this table, the Excel also has several pivot-tables, which were usefull for me to check if I entered the match-data correctly in my program Match. E.g.: table 2 in sheet 3 shows that in every group the number of matches is 6. Another pivot-table shows the number of matches per city.

And the map with the countries which participate in the Worldcup 2014 (on bottom of sheet) I made with Powermap, see my previous post:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-powermap-and-world-cup-soccer.html

Match schedule 3: Matrix format (see fig.3)

This schedule shows in a compact way for all the matches per group where and when they will be played.

Another (non-Excel) report with the match-schedule generated by Match you can see in fig.4.

fig.4: MS Acces report: match schedule per group

Note 30-5-2014:

The post for Match (my program which generated the Excels in this post) I just finished, see:

http://worktimesheet2014.blogspot.com.es/2014/05/match-access-app-to-track-world-cup.html

Downloads

#Download-Mirror 1 

NB: site has Excel Web-app, if you don't have Excel installed

Table-format:

https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21219

Calendar-format:

- Calendar with Location-view

https://onedrive.live.com/redir?resid=3F963E9F1A42D952!218&authkey=!AM4mkOPfWYKvNQo&ithint=file%2c.xlsx

- Calendar with Group/Team-view
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21225

Matrix-format:

https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21217

#Download-Mirror 2

(Excels + PDF in 1 zip)

https://drive.google.com/file/d/0BywxxSJoaUYxQjVsLXRCYjJyOW8/edit?usp=sharing

#Download-Mirror 3

Table-format: 
http://www.scribd.com/doc/226701649/Excel-with-Match-Schedule-FIFA-Worldcup-2014-format-Table

Calendar-format:

http://www.scribd.com/doc/226701647/MatchSchedule-FIFA-Worldcup2014-Soccer-Calendar

Matrix-format:

http://www.scribd.com/doc/226701646/MatchSchedule-FIFA-Worldcup2014-Brazil-groups

Access-reports (matches per group):

http://www.scribd.com/doc/226701648/Match-Schedule-FIFA-Worldcup2014-iBrazils