Year calendar and planner 2015 and Month-calendar Jan.

#29 Year calendar and planner for 2015 and Month-calendar January in Excel and PDF

In this post the new year calendar and planner for 2015 and the month-calendar for January 2015, see fig.1-3. For the Spanish readers: the calendar includes all holidays (so it´s the 'calendario laboral 2015').
The graphic I used in this Excel, this time about the new year 2015, which is the year of the (green) sheep in the Chinese horoscope, is from this site with public domain cliparts:
http://www.clker.com/clipart-green-sheep.html

Fig.1: 2015 year-planner

Fig.2: 2015 year-calendar

Fig.3: Jan. 2015 month-calendar


Downloads:


#Mirror 1 (PDF file):
https://es.scribd.com/doc/251216011/Calendar-201501

 #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

http://1drv.ms/1JWMNPk

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

http://goo.gl/eq5Mpq

http://goo.gl/rmWopZ

Month-calendar for December and 2015 year-calendar in Excel

#28 Month-calendar for December and 2015 year-calendar in Excel 

In this post the new month-calendar for December 2014, with week-numbers (see fig.1) in my multi-language (English, Spanish, Chinese and some more languages) Excel. The Excel also has the year-calendar for 2015 (see fig.2). And soon I´ll make a new version which includes the 2015- year-planner.
The graphic I used in this Excel is from this site with public domain cliparts, this time about Xmas.

http://www.clipartlord.com/category/christmas-clip-art/reindeer-clip-art/

, thanks for sharing!

      fig.1 2014-Calendar month Dec.

           fig.2 2015 year-calendar

Downloads:

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

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

Statistics result 10-km run Corre por el niño 2014

#27 Statistics result 10-km run Corre por el niño 2014

On Sun. 9 Nov. I participated in the 4th ed. of the 10K run "Corre por el Niño 2014" (#CorrePorElNiño), together with 1827 other people. (In total there were 7000 runners, the rest participated in the other 4 km run. This run was organized by hospital Niño Jesus (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info  about this road-race, see:

http://correporelnino.wordpress.com

https://twitter.com/CorrePorElNino

https://www.facebook.com/pages/Carrera-Popular-Hospital-Ni%C3%B1o-Jes%C3%BAs/212488895455880

Every participant of the 10k run gets a chip, and in this race the bruto (gun) time and netto (chip) time are measured. The organization decided to use the bruto time as the oficial time (as prescribed by IAAF), although I think the netto time would be better because of the large number of participants, not everybody can start at the start/finish line at the same (gun-)time. For me for example it took 20 sec. after the gun to reach the start/finish line. And the final ranking raised also some doubts: the netto time of the fastest woman (33:47 min, and bruto time 37:03) was better than that of the fastest man (36:05 min. (netto and bruto time)), which probably is incorrect, as was noticed here:

http://www.forofosdelrunning.com/index.php?topic=6709.30  

And I think it´s a pity that in the ranking-table the columns 'gender' and 'category' (age-classes) are missing.
But of course the most important thing was the hospital raised about 70.000 euro (every participant pays 10 euro). For  some of my photos of the race (and that of 2013), see:

https://picasaweb.google.com/103278159654062440102/CarreraDelNino2013Madrid

(one of them has Pedro Delgado on it, the former Tour de France winner, who participated in the run to promoto it).

After the race I wondered how good (or bad) was my ranking (my bruto-time: 53:33 min., netto: 53:13, about half as slow as the world-record for the 10k road-race (26:44 min)...). The ranking was published here:

http://www.cronococa.com/ResultadosRunning.aspx

and I saw I ended at 828th place (my name is in blank because I registered last minute, on the day of the race). And on this site:

http://www.runedia.com/cursa/201418490/carrera-popular-corre-por-el-nino-10k/2014/

I saw that my ranking corresponds to 45%-percentile. And this site also had a nice histogram of the times of the participants, which gave me the idea to do the same in Excel, see fig.1 for the result.

    fig.1: Distribution of finish-times runners

How did I make this? First, I copied the data from the CronoCoca-website, see fig.2, blue columns. Then, I added the orange columns. First, the bruto time in seconds, and the (integer-)values of this column were input for the FREQUENCY-funcion (1st parameter, 'data-array') which calculates the freq. of the values in this data-array. Then I created another table with the 'bins', time-intervals of x minutes (I made 2 versions, with x=2 and x=5), for which I also calculated the equivalent in seconds, which is the 2nd  parameter, 'bins array') of the FREQUENCY-funcion.The results is the column 'Freq.' (see table in fig.1), for which I created a bar-chart (histogram) (see left graph in fig.1). The next column is the cumulative frequency and the last columns the relative cumulative frequenc, or 'percentile rank'. For a more detailed description of Excel and distribution-functions, see:

http://exceluser.com/formulas/frequency-distribution-five-ways.htm

fig.2: Table with all finish-times

For how you can calculate percentiles (quartiles) in Excel (see fig.2, 2nd orange column), using e.g. the  statistical functions PERCENTRANK.INC, PERCENTILE, QUARTILE, see:

http://best-excel-tutorial.com/55-advanced/219-calculate-percentile

http://www.excel-easy.com/examples/percentiles-quartiles.html

As I said, my percentil and ranking was 45% and 828 (see table of fig.1, the orange row). In the histograms with bin-widths of 5 minutes (fig.1, graph Cumul. Rel. Freq.) you can see that my time (53:33 min.) falls between the percentiles of bins 0:51-0:53 and 0:53-0:55 min., so between 41% and 51%. And if you look at the same graph in sheet-2, with the (bigger) bin-width of 5 min., it shows my time is between the 29% (bin 0:45-0:50) and 51% (bin 0:50-0:55). So the smaller the bin-width, the more exact is the percentile-estimation. For how to determine the optimal bin-width, see:

http://stats.stackexchange.com/questions/798/calculating-optimal-number-of-bins-in-a-histogram-for-n-where-n-ranges-from-30

The RunEdia-site also had some other statistics, like mean and standard deviation of the finish-times, things which can also be calculated in Excel with the statistical-functions (AVERAGE, STDEV), see sheet-2 and fig.3 and this site:

http://office.microsoft.com/en-us/excel-help/statistical-functions-HP005203066.aspx

 fig.3: Statistics finish-times

The distribution of the times of the runners looks like a normal distribution  (a bell-shaped curve) and to test this, I used the statistical function NORM.INV to calculate the 3 quartiles Q1, Q2, Q3 and compared them with the 'real' Q1,Q2, Q3, and as you can see, Q1 and Q2 of the normal distribution are quite close to that of the real distribution. Another test for 'normality' of the distribution which I did was the SKEW-function, with a result of 0,3, and according to this site:

http://help.gooddata.com/doc/public/wh/WHAll/Default.htm?#MAQLRefGuide/NormalityTesting-SkewnessAndKurtosis.htm

that indicates the distribution is approximately symmetric (like a normal distribution). The fact the skew is positive means that the distribution-function has a tail, which means in this case (10K run) that there are more runners that are slower than the mean time (54:50 min.) then that there are runners that are faster then the mean time.

For this race I used the Runtastic Android-app to measure my km-times, which you can see here:

http://www.runtastic.com/sport-sessions/347242237

I also included these results (km-times and speed) in my Excel, see fig.4

  fig.4: my km-times and speed

This graphic has besides my speed als the height(differences) during the race (to get the absolute height, you must add ca. 667m, the hight of Madrid (above sea-level). For a description how to combine 2 graphs in 1 graphic (in this case: area-chart for height) and line-chart for speed), see:

http://blogs.office.com/2012/06/21/combining-chart-types-adding-a-second-axis/

For this post I want to thank (Crono)Coca, the company responsable for the time-registration in this 10K run, for answering my questions about the time-ranking table, and José, my running/training-partner, for reviewing the statistics of my Excel.

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

- statistics for a 10K run (very profesional!):
http://cnr.lwlss.net/RealData/

-Q/A distribution times 5K run:
http://www.letsrun.com/forum/flat_read.php?thread=4612823

- graphics for distributions:
http://flowingdata.com/2012/05/15/how-to-visualize-and-compare-distributions/

-histograms and (normal) distribution functions in Excel:
http://peltiertech.com/Excel/Charts/Histograms.html
http://exceluser.com/formulas/statsnormal.htm

- race-timing and discussion bruto vs netto finish times and funny anecdote:
http://www.aimsworldrunning.org/race_timing.htm
http://www.iaaf.org/news/news/sometimes-rules-can-be-complicated-to-explain

Downloads:

#Mirror 1 (PDF file):

https://es.scribd.com/doc/247957028/Statistics-10km-Run-CorrePorElNino2014-in-Excel

 #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

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

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

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

Calendar 2014 November in Excel in Chinese

#26 Calendar 2014 November in Excel in Chinese

I made a new version (v13) of my Excel Calendar (see fig.1), which is almost the same as v12, but now with the month-calendar for November, and I also added Chinese day and month names. I don´t know anything about Chinese, except that it is a CJK-language and I thought that if I have Korean as a language in my Excel Calendar, I should also include Chinese as the most spoken language in the world. For more info about CJK-languages and special characters in MS Excel (Office), see my post with a calendar in Korean:

http://worktimesheet2014.blogspot.com.es/2014/01/calendar-2014-v5-korean-part-12.html

This month´s item is 'La carrera del niño 2014', a run organized by hospital Niño Jesus, to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info see:

http://correporelnino.wordpress.com

And if you find this calendar a bit boring, you can decorate it as you like, see fig.2 for an example of a 'kids-version'.

 

fig.1 2014-Calendar month Nov., in Chinese

fig.2 (photo of) Excel-Calendar, kids-version

Downloads:

#Mirror 1:
https://drive.google.com/file/d/0BywxxSJoaUYxRy1ULUxwTnBSZWs/view?usp=sharing

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

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