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
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
No comments:
Post a Comment