22 Oct 2014

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


1 Oct 2014

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