Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

#88 Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

Last Sunday 17/12/2023 was the yearly run 'Cross de Invierno' organized by A.D. Ciudad de los Poetas, in the nice park in my neighbourhood Dehesa de la Villa, Madrid.
For more information about this race, see e.g:

http://adcpoetas.blogspot.com/2023/12/xxxix-cross-de-invierno-clasifiaciones.html

https://youtu.be/WMnQjA7h1oE?si=8O8uilxO4RrOw32O

https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2023/20233350/ 

or my previous blog-posts about earlier editions of this run, e.g.:

https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html

For this blog post I made an Excel with the finish-times of the category that I participated in (male, Veterans B), based on this doc/PDF:

https://drive.google.com/file/d/1hcRWlKO9PAoV70GVajWZ79Wn7_YwzCQx/view?usp=drive_link

NB: the table-headers in this doc are in Spanish and abbreviated, but in my Excel, in sheet 'Fields' you can find the English descriptions.

This is my Excel (in desktop version):

 

I've also uploaded this Excel to One Drive and generated the iframe HTML-code to embed this Excel (online version) in this blog (see the bottom of this post).
How-to: https://www.youtube.com/watch?v=uvA-U9FKgPw

In the right bottom histogram-chart you can see that my finish time (00:32:52) is somewhere in the middle, in bin '31:33-33:08' (rank #64 of the in total 113 Veterans B runners).

Steps to make this histogram:

*step 1: calculate finish-time in # seconds, see column O (sheet 'MenAll')

*step  2: for the finish-times (in sec.), generate the bins of the histogram with the 'Data Analysis' function.

How-to: https://www.upwork.com/resources/how-to-make-histogram-in-excel

*step  3: create the bin-labels to use in the histogram (see blue cells in pic above)

How-to:

https://youtu.be/QQGkrYzRbm8?si=t7FeHecvY4Hvyt-M

https://stackoverflow.com/questions/220672/convert-time-fields-to-strings-in-excel

 
*step  4: insert (clustered) column chart (select blue cells in pic above)

On the website of A.D. Poetas you can also find all the photos that were made during the race,
which are a lot (6000). But luckily it has the 'image-search' feature (as e.g. Google Photos), and so I could find easily this nice photo of the run of my kids in this album:

 

or of my race:
https://frutocfotos.barrel.cloud/en-en/album/photo/c731b368-9da4-41d5-b1ed-8bf57adc3734

Thanks A.D. Poetas for the perfect organization again and giving us a nice sporty Sunday morning.

If you enjoyed this post and want to make a small donation, you can do this with the Paypal Donate button (at the top right of this post) or via BuyMeAcoffee .

Downloads

#1: Excel on One Drive (best option, as both are Microsoft products)

#2: Excel on G-drive

 

 Embedded Excel

--

 

--

Results 6K run Dehesa de la Villa 2019, Men Veteran B

#64 Results 6K run/cross Dehesa de la Villa 2019, category Men Veteran B

Last week (15/12/2019) I participated in the running race "XXXVII (37th edition) Cross de Invierno Ciudad de los Poetas - Memorial Javier Martínez Morales", a 6 km race in the beautiful park Dehesa de la Villa in Madrid, organised by the local running club Agrupación Deportiva Ciudad de los Poetas (A.D. Ciudad de los Poetas).

For map-details, see: https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2019/20193350/

For the analysis of the results with Excel, I used the same method as I did last year, see for detailed description:
https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html
NB: for download-details of the Excel, see end of blog. I also uploaded it to Excel Online (on Microsoft Onedrive) so you can also look into it even if you don't have Excel installed.

Besides Excel, I used this time also Flourish for data-vizualization, see e.g. fig.1 for an example of a chart made with this tool. The Vega-Lite charts that Flourish uses have some nice features, e.g:
- online editor Flourish Studio, to which you can upload an Excel with your data
- charts are described in JSON (very compact), see fig.2a-b
-  animation-effect when a chart is loaded
- you can create a 'story' to group charts which can be embedded in a web-page and 'played' (aka Powerpoint) , as the one I made and you can see below fig.1.

fig.1:  Flourish / Vega Lite Boxplot-chart finish-times byclub

..

My Flourish story (click on below arrow to go through 4 charts of this story):

The Flourish/Vega Lite charts are also responsive, so they adapt to the resolution of the device (laptop, smartphone, tablet), see fig.2c


fig.2a: Flourish Studio

https://app.flourish.studio/visualisation/1132053

fig.2b: Flourish Studio

fig.2c: Flourish responsive charts

I also used Excel to analyze the data and my finish-time (30:42 min., so 1842 sec, rank #83) using the Data Analysis menu with options "Descriptive Statistics' and "Rank and Percentile", see fig.3-4 for the result. And in Excel 2016 it is also quite easy to make Histogram- and Boxplot charts, see fig.5-6.


fig.3: Excel 'Data Analysis'-menu,  option "Descriptive Statistics'

fig.4: Exel 'Data Analysis'- menu,  option "Rank and Percentile"

fig.5: Excel Histogram-chart Finish-times (in sec)

fig.6: Excel Boxplot-chart Finish-times (in sec)

And to conclude I wanted to to thank AD Ciudad de los Poetas and all its volunteers for organizing this race, and also for giving all runners a nice memory of the race with all the photos they made and uploaded to Flickr, like this one of me, in white shirt, which I got with another race , of hospital Nino Jesus, and which I also made an analysis for using Power BI as dataviz.tool, see:
http://worktimesheet2014.blogspot.com/2017/11/statistics-result-10-km-run-corre-por.html
What I liked about Flourish is that it is 100%cloud and there is a free edition. When I looked for the opinion of others on Flourish, I found this interesting article in which various dataviz-tools are demonstrated, among which Flouris (and Power BI, Excel and more):

http://www.storytellingwithdata.com/blog/2019/1/24/new-year-new-tools




source: 
https://www.flickr.com/photos/adcpoetas/49241485423/in/album-72157712270066027/


Downloads:

#Mirror 1: Google Drive

https://cutt.ly/freIKwU


#Mirror 2: Microsoft OneDrive

https://cutt.ly/oreI1cX

--

Treemap and Sunburst Excel2016 charts for Tour De France

#57 Treemap and Sunburst Excel2016 charts for Tour De France

In 2015 Tour de France contracted the company Dimension Data to make the event as high tech as possible. As DD said themselves: 

It has completed its big data analytics and digital delivery platform from which the company will deliver real-time information on individual riders for the first time in the history of professional cycling. This major innovation was made possible by Amaury Sport Organisation (ASO) in partnership with the 22 teams participating in the 2015 Tour de France. Highly accurate data will be collected through the use of live trackers under the saddle of each rider. Dimension Data will then process and analyse the data, and make it available to cycling fans, commentators, broadcasters and the media.

When the Tour de France begins on Saturday, the viewing public around the world will be able to follow all 198 riders in 22 teams real-time, and be able to track the speed at which each cyclist is riding, exactly where he’s positioned in the race in relation to other cyclists, and the distance between each rider - all via a beta live tracking website.

And I think they did an excellent job, and also in this year's edition.

On one of their Twitter-posts:
https://twitter.com/letourdata/status/624565612048744448 
 I once saw a graphic (data: total % of time a cyclist was leading in the breakaway group) which I didn't know, and which was something which could not be made with Excel (2013), but a bit later, which the launch of Excel-2016, I learnt that this graph was a Treemap, so now supported by Excel, and I tried to reproduce, see e.g. fig.1

fig.1 Treemap chart wit data from teams and cyclists

Besides the Treemap chart, Excel 2016 had another new graph, the Sunburst chart, see fig.2 for an example

fig.2 Sunburst chart wit data from countries and cyclists

Both the Treemap and the Sunburst-chart are for displaying hierarchical data, so e.g. in Tour de France, cyclists are part of a team, so you can compare the teams performances (total times) and breaking it down to the indivual cyclists performances, as done in fig.1. Another possible grouping is cyclists per country as in fig.2, although this is more artificial, so not something which is considered by the Tour de France, while grouping by teams is of course (there is a prize for the best team). 

And if fig.3 you can see the equivalent of fig.2, but now in Treemap format. Maybe this format is handier if you have less space, like e.g. Dimension Data had, where this chart was part of a dashboard with other charts.

 fig.3 Treemap chart wit data from countries and cyclists

And in fig.4 you can see the original Dimension Data Treemap, which in fact did not have any hierarchical data, just data of cyclists. In this chart it is of course the easiest to compare data of 2 riders, e.g. the 2 best riders in the breakaway-group were Roland and Rodriguez (both in left of chart, with same square-size).

fig.4: Treemap charta with data from cycists.

If you want to read more about Big Data brought by Dimension Data to Tour de France, see e.g.:

http://cyclingtips.com/2015/07/behind-the-scenes-of-dimension-datas-rider-tracking-at-the-tour-de-france/

http://blog.dimensiondata.com/2015/07/culture-of-cycling-technology-shaped-rise-sport/

And for a video explaining more about Treemap charts in Excel, see: 

https://www.youtube.com/watch?v=YXd3wqBCcBM&feature=youtu.be

For me, the new high-tech dimension (also by using onboard cameras) makes the Tour de France much more interesting to follow, and of course the fact that the Dutch are doing very well in TDF2016 (Tom Dumoulin 2 stage victories and Bouke

Mollema #2 in general ranking).

Downloads

Excel

https://goo.gl/lkCXgu

PDF

https://goo.gl/9rDyKAhttps://goo.gl/9rDyKA

Month-calendar for June 2016 in Excel

#56 Month-calendar for June 2016 in Excel

In this post the new month-calendar for June, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is UEFA Euro 2016 France, hold on 10/6 - 10/7. Unfortunately the Netherlands did not qualify, so this time I was not so motivated to make a new version of Match for this Eurocup, or a new Excel for an office-pool, like I did for last FIFA Worldcup. But of course you can do it by yourself, by adapting these 2 files (Access and Excel):

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

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

For a very nice Excel with a EURO 2016 tracker, which automatically calculates the points, who go to next round etc (by using formulas), see:

http://www.excely.com/football/euro-2016-schedule.shtml




fig.1: Month calendar 201606


Downloads:

https://goo.gl/BA0cJU

Month-calendar for May 2016 in Excel

#55 Month-calendar for May 2016 in Excel

In this post the new month-calendar for May, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is Roland Garros, the French Open ATP tennis tournament, 22/5-5/6.

 fig.1: month-calendar May 2016

Downloads:

https://goo.gl/q3lwNW

Month-calendar for April 2016 in Excel

#54 Month-calendar for April 2016 in Excel

In this post the new month-calendar for April, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is 'El Clasico', Barcelona vs Real Madrid, Sat. April 2, in the stadium of Barca, Nou Camp, in which there will be a 'remembrance-mosaic' with the Barca-shirt with number 14´, the number of Johan Cruyff, the Dutch legendary player and coach of this club, who died last March 24. Cruyff was besides for his football also famous for his oracle-like aphorisms, like "every disavantage has its advantage" and his logic like : " I do not believe. In Spain  all 22 players make the sign of the cross before they enter the field, and if it would work, it would always be a draw." , see:

https://citaten.net/zoeken/citaten_van-johan_cruijff.html

--

--
Fig.1: calendar April 2016

Downloads:

https://goo.gl/TPBgJj

Month-calendar for March2016 in Excel

#53 Month-calendar for March2016 in Excel

In this post the new month-calendar for March2016, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is la Volta Ciclista a Catalunya 2016 (in Spain). For more info about this event , see:

http://www.voltacatalunya.cat/es/

fig.1: calendar March 2016

Downloads:

https://goo.gl/2ee8Od

20Minutos Blog Awards 2015 Statistics with Excel and R

#52: 20Minutos Blog Awards 2015 Statistics with Excel and R

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, organizes the 10th edition of its Blog Awards ("Premios 20Blogs"), with 7652 participating blogs from (mostly) Spanish speaking bloggers. 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. And like last year, I'm participating with this blog. 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. For the 2014-blog-competition I made an Excel with some statistics, see:
http://worktimesheet2014.blogspot.com/2015/02/excel-statistics-for-20minutos-blog.html

and for this year's edition I did the same, but this time I made other statistics, and not only with Excel but also with R. My idea was to get into 1 Excel all the blog-records (with data like URL, Author, Title, Description and Category in which blog is participating), and to get all this data, I used the (free) tool https://www.import.io/ for web-scraping the 20Min. website for the blog-contest: http://lablogoteca.20minutos.es/premios-20blogs/

For more info about this great data-extract tool, see e.g.:

http://www.interhacktives.com/2014/03/06/scrape-data-without-coding-step-step-tutorial-import-io/

https://www.youtube.com/watch?v=V8lHaaAWGXc&feature=youtu.be

The result of the import had some 300 duplicate records, but with Excel it's easy to eliminate duplicates, see e.g.:

https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

To this dataset I added some extra calculated fields like country of blog (only posible if domain-name of website has a country-code like "es" (Spain) and blog-platform (Blogger, Wordpress etc), see fig.2a.
NB: My (V)lookup-table with the mapping country-code - country I got from here:
http://www.web-wise-wizard.com/internet-dns-web/internet-iddd-country-codes.html

And with this dataset, I made the statistics which you can see in the figures 2b, 3 etc below, using pivot-tables and pivot-charts. But before getting into more detail about these stats I present in fig.1 a Word Cloud for which I used R (the script is included in the Download-section below). I used R to make the world cloud (instead of using a site like http://www.wordle.net/) to learn a bit about R, and because of it's extensive Text Mining capabilities. Some sites which helped me with this are:

https://eight2late.wordpress.com/2015/05/27/a-gentle-introduction-to-text-mining-using-r/

http://www.webmining.cl/2012/07/text-mining-de-twitter-usando-r/

To make this word cloud, I used the column Description (of a blog), a 'memo-field'.
Not suprisingly, the most used word is... 'Blog', and then comes 'World', 'Life'', 'Ínformation'.

.

fig.1 Word Cloud of Blog-descriptions of all blogs participating in 20Min. Blog-contest.

fig.2a: Data imported in Excel from 20Min. (formula-columns which are not interesting for user are hidden).

fig.2b: total Blogs per category'.

NB: Note that the totals in this chart correspond almost 100% to the ones on the 20Min.site (http://lablogoteca.20minutos.es/premios-20blogs/ ), as it should, but with a difference of 1 (in my stats, category Viajes has 404 records, not 405 as on the 20Min. site, I wander if that is because of an error in my data-extraction from this site or maybe an error by 20Min.?)

fig.3: Total Blogs per Platform

NB: Google's Blogger (domain Blogspot) appear more popular then Wordpress.

fig.4: Total Blogs per Country

NB: for those of you who have never heard of Tokelau: this (is)land offers a 1 year free (tk) domain (for everyone, so not only for the people of TK), after that you have to pay, or they close your site, which happened to my site, http://www.eigersoftware.tk ..

fig.5: Total Blogs per Author


NB:
- this pivot-table report filters authors with >= 7 blogs, for how you can do this filtering, see:
http://stackoverflow.com/questions/12209841/filtering-pivot-table-columns-only-count-if

- 'Grupo LM' has a total of 28 blogs in the competition, and I found out that this is a company which pays bloggers if they send them a blog-post. To see all 28 blogs, double-click on measure-value '28' in the pivot-table, and then Excels opens a new worksheet with the 28 records.
I wonder why the blog-authors who work for LM did not sign up for the blog-contest with their own name.

And to conclude this post, I´d like to say good luck ('mucho suerte') to all my fellow-bloggers in the "Premios 20Blogs" Blog Awards.
BTW: To decide on which blog to vote, I also searched for Éxcel' in my Excel and found several blogs, e.g. this excelent blog, from Argentina (on which I voted):

http://excelmascerca.blogspot.com.es/


Downloads:


#Mirror 1: Google Drive (zip file with Excel and R-script files):

https://goo.gl/ZAf54w


#Mirror 2: Microsoft Onedrive (Excel file):

http://1drv.ms/1REIjjC


#Mirror 3: Scribd.com (PDF file):

https://www.scribd.com/doc/301523553/20minutos-blogcompetition-2015-statistics-with-excel-and-r

Month-calendar for February 2016 in Excel

#51 Month-calendar for February 2016 in Excel

In this post the new month-calendar for February 2016, see fig.1. The Excel also has the year-calendar for 2016 and a 2016-year-planner. Event of this month is Lillehammer 2016 Youth Olympic Games (YOG), the Wintergames for children from 15-18 years. For more info about this event , see:

http://www.lillehammer2016.com/

fig,1 Calendar for month February 2016


Downloads:

https://goo.gl/Ap3yxl

Timesheet for worked hours 2016 and PowerView report with stats

#50 Timesheet for worked hours 2016 and PowerView report with stats

 This post is about the new version (V3) of the Excel I made  to track your worked hours, the "Timesheet 2016". For my post about the previous version (V2), see:

http://worktimesheet2014.blogspot.com.es/2015/04/timesheet-for-worked-hours-2015-v3-with.html

Timesheet-V3 does not have any new features comparing with V2, the only difference is that V3 has the 2016-calendar (V2 was for 2015). So in this post I won´t repeat the explanation about the timesheet, for this see the post of Timesheet-V2 (2015).

But there is one thing new for the 2016-Timesheet: I made a new Excel with a PowerView report with statistics about worked days and holidays at year-, month- and week-level. The Excel with the Timesheet also has a worksheet with statistics, but these are using the 'traditional' pivot-tables, while the new Excel with PowerView uses PowerPivot Tables which has some new nice features which I´ll show below.

To show how this Timesheet works, I made a demo in which I registered check-in and check-out times for all (working-)days in 2016, and also the days off (vacations, sick, personal days).
If you want to use this Excel to track your worked hours, you should delete all my input for this demo (green columns in sheet-2: 'Tabla').

In fig.1 you can see the Excel with the PowerView report.
NB: 
- this report shows Month-stat. for Dec. and Week-stat. for week 50 (which starts on Monday (Spanish: Lunes) 22 (Dec.) (see filter-pane, to the right of report-canvas)
- the report-titles are generated, and have some Spanish:
'por' = by
 'y' = and

And in fig.2 you can see the Excel with the Timesheet-data (fig.2) and -statistics (fig.3).

In the rest of this post, the figures are from the Excel with the PowerView report.

fig.1 Excel with Powerview-report for Timesheet

fig.2 : Excel with Timesheet (table)

fig.3 : Excel with Timesheet statistics

In fig.4 you can see that if you filter in the bar-chart Hours Worked per Month for 1 month , e.g. Dec., then also the other report-element at year-level, the pie-chart Days Off, is filtered for this month.

fig.4: filtering in report (month = Dec.)

In fig.5 you can see the result after I clicked in fig.1 in pie-chart Days Off on the blue-part of the pie-chart, labeled Code2: 'VA' (= vacations) and in fig.6-7 you can see how to drill-down in this pie-chart to Code3: VA0 and VA1 (VA0: vacations-days of last year, VA1: vacations-days of actual year).

fig.5: Pie-chart Days Off (not worked)

fig.6: Pie-chart Days Off , config. drill-down Code2-> Code3 (see field: Color)

fig.7: Pie-chart Days Off , result drill-down Code2-> Code3

To get the data from the Timesheet-Excel (worksheet-2, data-table 'Tabla2')  into the PowerView Report-Excel I used PowerQuery (see fig. 8) and I imported the data in the PowerPivot datamodel. After that I added some DAX-formulas in this datamodel, e.g. to calculate the worked time in hours, with decimals (in the Timesheet-Excel, the time is registered in minutes):

HoursWorked := ROUND([Ttotal3]/60;2)

and to get some more 'friendly names' for fields and field-values in the pivot-table, and in English rather than Spanish, I used e.g. this formula:

IsWorkingDay:=IF([EsLaborable]=1;"Y";"N")

And in the PowerPivot datamodel I also renamed some fields to more 'friendly names' and marked some fields as 'not visible in client', so that the final-user (you) only has the fields which are usefull in pivot tables (e.g. WorkedHours, but not 'Notes').

fig.8: PowerQuery to import Timesheet data  

For the resulting PowerPivot datamodel, see fig.9-10.

fig.9: PowerPivot datamodel with DAX-formula

--

fig.10: PowerPivot datamodel with friendly field-names and hidden fields

As you can see, the new Timesheet statistics, with PowerView, are better then the old ones. Some people call Excel with the PowerBI-plugins (PowerPivot, PowerView, PowerQuery and PowerMap) 'Power Excel', and say 'Power Excel' is the best tool for all kinds of data-analysis, which results in an interesting discussion, see:

http://www.powerpivotpro.com/2015/06/no-more-apologies-excel-is-the-worlds-best-data-tool-period/


Some references: 

* For a (free) course about data-analysis with MS Excel, by my old university, TU Delft (Delft University of Technoloy), see:

https://www.edx.org/xseries/data-analysis-business

* For a video with more details about how to create a timesheet with Excel, see:

https://youtu.be/vYdHi2m4IgE

*For a good video and blog about MS Excel and Powerview (and more Microsoft software), see:

http://www.wiseowl.co.uk/blog/s389/power-view.htm

https://youtu.be/8PUmuud7TYg



Downloads:

NB: The Excel with the Timesheet-data, which is input for the Excel with the PowerView-report, should be in this dir:

C:\tmp\zzz\TimeSheetWorkedHours_2016_DEMO_R1.xlsx


#Mirror 1: Google Drive (zip file with 2 Excel files):

zip-file with 2 Excel files:
https://goo.gl/OQFI0j

PDF:
https://goo.gl/23LLQs


#Mirror 2: Microsoft Onedrive (1 Excel file: Timesheet):

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

http://1drv.ms/1mO95vj


#Mirror 3: Scribd.com (1 PDF file: PowerView report):

https://es.scribd.com/doc/295022256/Excel-TimeSheet-WorkedHours-2016-with-PowerPivot-report-for-Stats

2016 Year calendar and planner in Excel

#49 2016 Year calendar and planner in Excel

In this post the new year calendar and planner for 2016 and the month-calendar for January, see fig.1-3. For the Spanish readers: the calendar includes all holidays in Madrid-capital, so it´s the 'calendario laboral 2016', see:

https://sede.madrid.es/portal/site/tramites/menuitem.1f3361415fda829be152e15284f1a5a0/?vgnextoid=4366addba8200510VgnVCM1000000b205a0aRCRD&vgnextchannel=239be59bdb789210VgnVCM100000171f5a0aRCRD

or this site, which also has a year-calender (in PDF):

http://www.calendarioslaborales.com/calendario-laboral-madrid-2016.htm

My calendar also has the (ISO) week-numbers (week 1 is the week with the 1st Thursday of the year). And it is multi-language (English, Spanish, French, German, Dutch, Italian, Korean, Chinese).
NB: For more about the Korean and Chinese variant, see earlier 'Calendar-posts' (see my blog-posts with tag/label 'Calendar' at the end of this post).


The graphic I used in this Excel, this time about the new year 2016, which is the year of the monkey in the Chinese horoscope, is from this site :

http://thumbs.dreamstime.com/z/chinese-new-year-monkey-year-symbol-calendar-red-figures-illustration-53158214.jpg

And for more info about the Monkey- (leap) year 2016, see:

http://www.guy-sports.com/humor/christmas/chinese_new_year2016.htm

Happy 2016!

fig.1 Month Calendar January 2016

fig.2: Year Calendar 2016

Downloads:

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

https://goo.gl/pij37g

#Mirror 2: Microsoft Onedrive (1 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

http://1drv.ms/1MMB2sr


#Mirror 3: Scribd.com (1 PDF file):

https://es.scribd.com/doc/294441979/2016-Year-Calendar-and-Planner

Statistics result 10-km run Carrera de las Empresas Madrid 2014

#48 Statistics result 10-km run Carrera de las Empresas Madrid 2014

Note 21-12-2016:
For stats (made with R and Power BI) of last edition of this race, see this post:
http://worktimesheet2014.blogspot.com.es/2016/12/carrera-de-la-empresas-2016-madrid.html


Yesterday, so Sun. 13 Dec. 2015, the 10K run "Carrera de las Empresas" was held here in Madrid.
In this 'business run', companies (teams of collegues) compete in 18 categories (dimensions: number of runners in team (2,3,4), gender team-members (men, women, mix) and distance (6, 10 km)). I also wanted to participate with my company, Ilunion (coparate group ONCE), like I did last year, but unfortunately this time I could´t because I was injured. For the 2014 edition of this run I made an Excel with the results but I never published it because of time problems, but I thought to finish it last sunday, so although I did´t participate, my mind was with the race..

In the 2014-edition there were in total about 9000 runners (about 300 teams) from about 800 companies, For more info about the run, see:

http://www.carreradelasempresas.com/

http://www.expansion.com/2014/12/14/entorno/1418574269.html

and for some photos:

http://www.expansion.com/albumes/2014/12/15/xvi_carrera_de_las_empresas/index.html

and for some statistics:

http://www.runedia.com/cursa/201419683/carrera-de-las-empresas-10k-actualidad-economica/2014/

In fig.1 you can see the result, and, as always, in the end of this post you can find the
 download-URL´s of the Excel(s).

fig.1 statistics result (finish times) 10K run

So my Excel only has statistics of the 10K run, which (netto/chip-) finish-times I got from this PDF:

http://estaticos.expansion.com/opinion/documentosWeb/2014/12/23/ABSOLUTA%2010K.pdf

As you can see in this PDF, for each runner his team is normally specified as:
Name company +´'-' + number or letter, e.g. my team was named Ílunion-26.
I wanted to create also statistics about the companies (number of runners per company), so first I transformed the data of this PDF to get the company of a runner, so for my row in this Excel the transformation was: team  Ílunion-26 -> company  Ílunion. Then I calculated in the Power Pivot datamodel, with a DAX-formula 'distinct count' the number of companies (687) derived from the field team (total: 1848), see fig.2.

fig.2: calculating data 'Company' (EquipoDef) from field 'Team' (Equipo).


And then I created a statistic 'total runners per company', see fig.3 for top 25 companies
 (with most runners). NB: I don´t know if 'TR' is a company-name or maybe a dummy-value.

fig.3: total runners per company


I wondered if there was a correlation between the number of runners of a company and the best 'total-times' of a team (of 2,3 or 4 members).  I used the category '10K - 2 (male) runners per team' to test this, see fig.4 for the result, which shows the correlation is weak (-0.15, so far for the max (negative) correlation of -1). And in the graph (scatter-plot) you can see that companies with about 30 runners or more always have a total-time (sum of time of runner 1 (best runner of company) and runner-2 (2nd best runner of company) lower then 5000 sec, which is not the case for smaller companies (in the 'bin' of companies with 2 to 10 runners, the slowest total-time is about 8000 sec.). Although in this category, the winner was a team of a company with only 2 runners, from New Balance. I guess they run on NB-shoes, so this run can be a nice way to get some free promotion...

fig.4: correlation between the number of runners of a company and the best 'total-times' of a team


Downloads:

#Mirror 1: Google Drive (zip files with 2 Excel and PDF files):

https://goo.gl/VUAvVR

#Mirror 2: Microsoft Onedrive (1 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

http://1drv.ms/1O4FnIu


#Mirror 3: Scribd.com (1 PDF file):

https://es.scribd.com/doc/293387909/Statistics-10km-Run-CarreraEmpresas2014-v2-R1

Month-calendar for December 2015 in Excel

#47 Month-calendar for December 2015 in Excel

In this post the new month-calendar for December 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Event of this month is Carrera de las Empresas 2015 Madrid (13/12), a 10K run, a competition between (teams) of companies, so an event where work and sports meet like in this blog. For more info about this event , see:

http://www.carreradelasempresas.com/

http://www.expansion.com/actualidadeconomica/2015/11/02/5633741b46163f905f8b45b5.html

                                          fig.1 Dec.2015 calendar


Downloads:

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

SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

#46: SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

On 21-11-2015, so tomorrow, Saturday, I go to this event: SQL Saturday Madrid 2015, #SQLSatMadrid, where several Microsoft SQL Server and Business Intelligence (BI) MVP´s give some talks, for more information see:

http://www.sqlsaturday.com/459/eventhome.aspx

and for the time-schedule:

http://www.sqlsaturday.com/459/sessions/schedule.aspx


As you can see, there is a total of 27 courses, in 7 time-blocks of 1 hour, in 4 rooms, and for each course the schedule shows this data:

• Course name
• MVP name
• Level (Beginner, Intermediate, Advanced)
• Theme (Big Data, BI, Cloud, DBA), indicated by cell-color
• Language (most are in Spanish but when the course-name is in English , I guess the course is in English)

To ´warm up´ for the sessions about PowerBI, I converted this schedule in a pivot-table and graph, using Power Pivot and Power Query, see fig.1 for the end-result.

 fig.1 Pivot table Courses SQLSat.



To make this Excel, first I copied the Schedule (PDF) to Excel (export to Excel in the tool Nitro PDF) and did some cleaning (delete rows with non-schedule data and 'undo' of cell-merges and delete empty columns), see file 'SQLSaturday459_Madrid2015_Schedule_20151120_v2.xlsx' in Downloads below.
And then I made a 2nd Excel with the statistics, and with PowerQuery I loaded the schedule data from Excel-1, see file 'SQLSaturday459_Madrid2015_Schedule_Statistics' in Downloads and fig.2-3 for some of the steps in the Power Query formula language 'M'. An example of an 'M-formula':

= Table.AddColumn(#"Replaced Value3", "Level_1", each Text.Contains([Value], "Beginner"))

which looks in Excel-1 if in a cell there is the word 'Beginner' (level of course) and if so, sets the value in the Level-1 column to True (and if not to False), for more details of this formula, see:

https://msdn.microsoft.com/en-us/library/mt253324.aspx

fig.2: Power Query import

 fig.3 Power Query formula to Unpivot all columns (Room 1-4') except 1st column ('Time')

And after the Power Query import I did some other things in Excel-2 like adding a Time-block column and Course-Level-column, hiding come columns etc.) to facilitate creating the final pivot-table.
BTW: The Course-Level column I made manually because this information in Excel-1 is not part of the cell (string) value, but color-coded. But probably this could also be automated (so the conversion from cell-color to a (string-)value, see e.g.:

http://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html

I keep it short now, tomorrow I have to get up early, the event starts at 08:30, and that for a Saturday..


Downloads:

https://goo.gl/xVUdoo