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

Calendar 2014 October in Excel

#24 Calendar 2014 v12, for month Oct.

I made a new version (v12) of my Excel Calendar (see fig.1), which is almost the same as v11 (see post #23), but now with the month-calendar for October (and I deleted the animation and macro, so now it is in xlsx-format, not xlsxm).
This month´s item is BiciMad, the renting of electric bicycles in Madrid, for more info see:

http://www.madrid.es/portales/munimadrid/es/Inicio/Ayuntamiento/Movilidad-y-Transportes/Oficina-de-la-bici/Madrid-en-bici?vgnextfmt=default&vgnextchannel=655b19927c278210VgnVCM2000000c205a0aRCRD

 

fig.1

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


Downloads:


#Mirror 1 (Excel and PDF file):

httprive.google.com/file/d/0BywxxSJoaUYxQjFZUU1JUWxnNFE/edit?usp=sharing


#Mirror 2 (PDF file):

http://es.scribd.com/doc/241548579/Month-Calendar-201410-October

Calendar 2014 v11, for Sept.

#23 Calendar 2014 v11, for month Sept.

I made a new version (v11) of my Excel Calendar, which is the same as v10, but I updated the month-calendar for September, and included the days in this month of a big sport-event here in Spain: Spain 2014 FIBA Basketball Worldcup, see fig.1. If you are interested in this event, I made an Excel with the match-schedule, see post:

http://worktimesheet2014.blogspot.com.es/2014/08/worldcup-basketball-2014-match-schedule.html

fig.1: Calendar month Sept.

Downloads:

#Mirror 1 (PDF file):

http://es.scribd.com/doc/238226627/Month-Calendar-201409-Sept

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

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

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

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

Worldcup Basketball 2014 Match Schedule

#22  Worldcup Basketball 2014 Match Schedule


Note 14-9: Update Excel after final, V6
  Note 10-9: Update Excel after matches of today, V5

Note 9-9: Update Excel after matches of today, V4

Note 8-9-2014:
I updated this Excel after the 1/8 finals with the 1/4 finalists (V3).

Note 6-9-2014:
Now the group-phase has ended and the 16 1/8 finalists are known, I updated this Excel, to which I refer below with v2. Besides, I also included a bracket (tournament schema in tree.from for finals), see fig.2.

Spain was counting down for the Spain 2014 FIBA Worldcup Basketball , but now it has finally started! And in Madrid, basketball is everywhere now, for some photos I made, see:

https://www.flickr.com/photos/46731978@N00/sets/72157647082847479/

I made an Excel with the match-schedule for this event, see. fig.1. It is in the same style as the Excel I made for the Soccer Worldcup, see post:

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

so see that post for more info about the format of the match-schedule.

To make this Excel, I used:

http://fiba-2014.com/fiba-2014-schedule-fixtures-matches/

and

http://en.wikipedia.org/wiki/2014_FIBA_Basketball_World_Cup

which has the data about the matches and teams in table-format, which made it easy to import it using PowerQuery, about which I wrote in this post:

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

In Download-mirror #2 you can see in Excel v0 the result of this import. To come to the final version of the match-schedule, was more work then I thought, mainly because the data in the table was not 100% structured (e.g. date and time in 1 column) and even had some errors. To prevent this kind of errors, I made some pivot-tables (see sheet3-5 in my Excel) which has checks like: all groups have same number of games, at 1 time and place there is only 1 game (no 'over-booking') etc.

Well, I hope with my Excel you´ll enjoy the Worldcup Basketball even more.
Go Gasol, go ÑBA!

NB: Some links with more info about the Worldcup:

http://www.fiba.com/spain2014

http://www.fiba.com/basketballworldcup/2014

http://as.com/especiales/baloncesto/mundial/2014/calendario.html

http://www.marca.com/deporte/baloncesto/mundial/calendario.html

fig.1: Match Schedule Worldcup Basketball

fig.2  Match Schedule Worldcup Basketball in bracket-fomat

NB: to create this bracket, I used this Excel-template from the MS website:

http://office.microsoft.com/en-us/templates/tournament-brackets-TC001018637.aspx

Downloads:

#Mirror 1 (PDF file):

http://es.scribd.com/doc/238226655/Spain-2014-FIBA-WorldCup-Basketball

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

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


#Mirror 3 (V1: 1 zip file with Excel and PDF files, V2: bracket-schema and 1/8 finalists):

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

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

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

V4:
https://drive.google.com/file/d/0BywxxSJoaUYxYTFXRGZPQmluZjA/edit?usp=sharinghttps://drive.google.com/file/d/0BywxxSJoaUYxYTFXRGZPQmluZjA/edit?usp=sharing

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

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

Calendar 2014 v10: with week-nr and new month-calendar

#21 Calendar 2014 v10:  with week-nr and new month-calendar

I made a new version (v10) of my Excel Calendar, which is the same as v9 (see post #19) + :

- year-calendar with ISO-week-numbers, see fig.1
NB: I recently learnt that the use of week-numbers is very common in Switzerland, see:

http://vowe.net/archives/008475.html

- new month-calendar, which has besides the actual month also the previous and next month, and some extra things to make the calendar less grey.., see fig.2

fig.1 Year calendar with week-numbers, numbers before Mon. and after Sun., between ( and )

fig.2: Month-calendar

Downloads:

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

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


#Mirror 2 (1 zip file with Excel and PDF files)

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

Vacations planner in Excel

#20 Vacations planner in Excel

The Excel in this post is a vacations planner. It has all dates of 2014 and for every resource (team-member) you should mark with a number which date(s) he wants to take his vacation (by team-member) and if that is possible or not (by team-boss), like this:
 1: applied (yellow)
 2: aproved (green)
 3: refused (red)

Below you can see a small example of a company with 2 employers (John and Jane) which have to agree about their vacation-dates, in a way there is always at least 1 person at the office.

fig.1: on 3-2-2014 John and Jane wanted the same day off and Jane 'won'

fig.2: in August John and Jane both want to take a 2 week vacations, and there were no planning-conflicts

fig.3: On 31-12-2014 John and Jane both want to take a day off and they didn´t decide yet who can go

Downloads

#Mirror 1:

NB: this site has MS Onedrive, which has 'Excel-Online', so you can view my Excel-calendar here if you don´t have MS Excel on your PC

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

#Mirror 2:

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

Calendar 2014 v9: year/month/week-calendar in Excel

#19 Calendar 2014 v9: year/month/week-calendar in Excel

I made a new version (v9) of my Excel Calendar, which is almost the same as v8 (see post #10), only I added 2 sheets with a month and week-calendar (v8 only had a year-calendar), see fig.1 for a photo of this calendar (printed and sticked on blackboard).

fig.1: printed Excel calendar (year/month/week)

The month-calender I made from the year-calendar, by copy/paste a month of the year-calendar (a 7x6 table in sheet Calendar-2) to sheet tMonth.

fig.2: month-calendar (e.g. July 2014)

The week-calendar is a table with the hours (from 07:00 to 23:00, assuming the other hours you don´t have anything to do) and the 7 weekdays (so without dates) and per day it also has 5 'free fields' to note things which should be done that day but not at any specific time.

fig.3 week-calendar template

For the v9 calendar I also made 2 sub-versions:
- v.9a: Excel for Madrid capital (with local holidays)
- v.9b: Excel for everywhere else then Madrid-capital  (without local holidays)

Rember the Excel-calendar is multi-language (7 possible languages), just select in sheet-1 (Info) in cell A19 your language. In par. Downloads I included a Spanish version of v9a and an English version of v9b in PDF-format (I printed the Excel with a program that can print to PDF (so generate a PDF-file instead printing it on paper), e.g. CutePDF, see:

http://www.cutepdf.com/


Downloads

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

- v9a:
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21231

-v9b:
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21232


#Mirror 2 (1 zip file with Excel and PDF files)

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