Visualizing the World Cup history with Excel and Power BI

Visualizing the World Cup history with Excel and Power BI

Rate This
  • Comments 5

This blog post was authored by Marc Reguera, finance director, Microsoft

Besides my family and friends, I have two additional passions in life: soccer and data visualization. Like most kids growing up in Europe, my dream was to become a soccer professional. I wanted this so badly that I actually followed my dream, and I played 3rd division for the Paris Saint Germain’s youth team in France. My father was smart in making sure I could only play soccer if I had good grades.  After an unsuccessful tryout with Bastia in Corsica in 1990, I switched to plan B. I started a career in finance.

Plan B was not bad. I have been fortunate to be at Microsoft in Finance since 1994, and I discovered very early the power of data visualization. I studied the best (Edward Tufte and Stephen Few to name a couple….) and applied this thinking  to my job – I focused on telling stories about data with great visuals and strong narratives.

In the last four  years, our business intelligence (BI) tools have really transformed the way hundreds of finance folks do their job. First Power Pivot came out, which allows “technical dummies” like me to play with millions of records without knowing anything about SQL. Power View was released two years ago, which gave us the ability to create touch enabled, interactive, and dynamic views in minutes. As of today, most of our reviews at Microsoft have evolved from static lectures to dynamic conversations because of Power View. Power Maps brought a 3D local perspective. Power BI enabled us to ask questions against our data in the most natural way using natural language. So, what does this have to do with the title of this blog post? Let me tell you.

I got ahold of a few thousands rows of World Cup data, so I decided to combine my two passions. Using Power Query and Power Pivot I was able to add formulas, join mapping tables and even duplicate records without any external help. Then the fun really began - I started building visualizations with Power View and Power BI. To be honest, I didn't think I would learn anything because I am a bit of a soccer encyclopedia, but I learned a lot.

Here are six amazing insights I learned after exploring World Cup data using Microsoft’s BI tools

  1. There were no 0-0 in the first five World Cup from 1930 to 1954.  The first 0-0 took place in 1958
  2. The World Cup tournament that had the most spectators took place in USA in 1994.  In fact, it was the only time when we had over 3.5M even though only 52 games were played (versus 64 today).  That is because the stadiums in the USA are all big and generated an average attendance per game of approximately 70k while other tournaments peaked at average attendance per game of 50k.  This is why we usually get 3M to attend the world cup (64 games times 50k)
  3. The average goal per game decrease is really driven by the increase by the percentage of games with 1 goal or 2 goals.  Games with 0 goals have actually been flat around 10% since 1958 while the percentage of games with 1 and 2 goals has increased to ½ of all the games played
  4. Italy is the most effective when comparing games statistics.   When looking at cumulative victories/ties/losses, Germany has a much better record yet. Italy has won the world cup 4 times vs. Germany 3 times.  In fact, Germany has the 2nd best record of all teams in the last 4 world cups (just behind Brazil) even though they haven’t been able to win the trophy (In the last 3 world cups, Germany lost in the final or semifinal)
  5. Part of the issue with Germany is that they have never been able to beat Italy the world cup.  They have played 5 games against Italy (lost 3 and tied 2)
  6. If Germany plays Italy, they should be careful in the first 10 minutes and the last 10 minutes as Italy is scores over 1/3 of their goal on average during these periods.  On the other hand, Brazil is the team that scores the least goals at the beginning and end of the games.

I don’t want you to miss out on the fun of playing with these visualizations! I invite you to give try it out yourself. 

Marc Reguera
Finance Director
Microsoft

 

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • It's nicely done. However, there is a flaw when it comes to wing point = 0. On the summary tab, if the win point is 0, there's no way to tell whether the country did play on that year or not, and there is no way to filter even the country did play on that year.

  • This is fantastic!  As a diehard lover of both soccer and data, this is sweet!  Any chance you can make the xlsx available for download?  Thanks!!

  • Cute. Takes forever to load compared to a Tableau equivalent though.

  • A downloadable version will be awesome. This takes a lifetime to load, also doesn't open in a lot of browsers. It is a bit confusing for a layman like me so it'll be easier to study if i could get a xlsx version available.

  • Thanks for sharing interesting stats,Was the outcome of 2014 World Cup extrapolated correctly with the tool ?

    Thanks.  

Page 1 of 1 (5 items)