World Cup 2018 Bracket and Spreadsheet

It’s that time of four-year again, when (most of) the world goes nuts over a bunch of guys running around kicking a ball!

Ideally, I would have posted this article a week or two ago, but, well, I had a lot of real work to do. Boo hoo. Hopefully I can get this in under the wire for those of you who have procrastinated in organizing your office pools.

At the very least, there is a bunch of interesting tech involved in this quadrennial’s offering. Starting with the data.

Update: you can now view the latest version of the bracket in your browser. I’ve saved the Visio file to OneDrive for Business/SharePoint, which allows you to view a read-only version of the Visio file. And here is the online version of the Excel file.

Update: be sure to check out the Diary section at the end of this article. It includes ideas and notes of trials and tribulations for this project. Even if you’re not into soccer/football, the story of the data-linking experience should be interesting and instructive.

Web API Data Source

There are 32 teams and 64 matches, occurring in 12 stadia across Russia. That’s a lot of moving pieces to keep track of. And there’s the natural yearning to somehow link the visuals to data that updates automatically.

So I went off to search for some form of web service. There are lots of potential leads, but often they are

No longer in existence
Too complicated
Not free

I finally stumbled upon a JSON file at GitHub that Martin Århof created and plans to maintain. Some links:

The GitHub page for fifa-worldcup-2018 project: https://github.com/lsv/fifa-worldcup-2018
The GitHub source for the data.JSON file: https://github.com/lsv/fifa-worldcup-2018/blob/master/data.json
The link to the actual, updated JSON file: https://raw.githubusercontent.com/lsv/fifa-worldcup-2018/master/data.json
This is what you point to if you’re doing data-connected stuff.

The data structure is great, it has these five main parts, which is more than enough data:

stadiums (stadia?)
tvchannels
teams
groups
knockout

Of course, my ultimate goal was to link this data into Visio. In year’s past, I have used VBA to connect to a web service, but I wanted to keep code out of the deliverables this year. Since Visio “doesn’t do web apis” (yet?), I looked to Excel and PowerQuery. Since Visio can link to Excel data, we should be able to get this thing to update. Hopefully, we won’t have to open up Excel, then open up Visio, but I’m not 100% sure how this is all going to work out, as of this writing.

And depending on your subscription status regarding OneDrive, SharePoint and all of that mess, you might be able to post the Visio and Excel files somewhere up in the cloud, and be able to view a read-only, but updated Visio bracket. If I can figure out a way to share such a thing with the general population, I’ll be sure and add a link to this article!

The Excel File

As I said, I didn’t want to code the data-gathering into Visio, so I headed to Excel, watched a few YouTube videos, and reacquanted myself with the PowerQuery features of Excel. The first step was to attach to the file, which you can do by following this path:

Data tab
Get & Transform Data group
From File > From JSON

I won’t go into the details of what you do then, but Excel had no trouble analyzing the JSON file and understanding its structure. From there, it was up to me to massage that structure and coerce it into creating some useful tables.

As an example, I was able to create a “GroupMatches” tab and table in Excel that has 15 columns of data. In PowerQuery, the steps to get there look like this:

After much fiddling and data coercing, I ended up with an Excel file that had these tabs:

Teams
Groups
Stadia
GroupMatches
KnockoutMatches
TvChannels

By the way, “Channel” only has one “L” at the end. I just spent five minutes correcting that ALL OVER THE PLACE.

This Excel file is pretty useful in and of itself, especially if Martin is able to update it throughout the tournament. So here is a link to just the Excel file if you think you can put it to good use, or are just curious as to how the PowerQuery stuff works when linking to a JSON file:

Download “World Cup 2018 Excel from JSON File” s!Aj0wJuswNyXlhHNyLUGestaRXGtn – Downloaded 3553 times – 103.00 B

The Visio File

The Visio file consists of three pages: Pool, Group Stage, Knockout Phase. The shapes on each page are linked to external data that comes from the Excel file above, which comes from the JSON file on Github. The shapes also have a bunch of ShapeSheet logic that allow for easy assembly of the diagrams, and for letting shapes share certain bits of data between them.

Pool

A new feature this year is the Pool page. On this page, all of the participating teams are listed, and each team has an “owner” name listed in blue. You can go through the list and type in the name of your friend or coworker who has chosen that team, or has been reluctantly assigned that team:

Then, on the Group Stage and Knockout Phase pages, the owner’s name will follow along, so that you can see who’s doing well, and who’s not.

If you’re a ShapeSheet Nerd, this works like this:

There are 32 User.OwnerXX rows in the Document’s ShapeSheet
(Shortcut to Document ShapeSheet: [Shift] + [Right-click] Show ShapeSheet)
Each of those cells refers to the text of one of the Pool’s “owner” shapes.
The match shapes on the Group Stage and Knockout Phase pages then refer to those Document ShapeSheet cells to get the owner of each team’s name.
The Document ShapeSheet also contains a User-defined ShapeSheet Function that allows for (see: User.fOwnerFromID)

Group Stage

The Group Stage page has some interesting features. First, each group is a List shape. List shapes make it easier to sort member, because you can drag items up or down in the list, or just use the arrow keys. After I added the match shapes to each group list, I then manually (but easily) sorted the games according to date (and the match ID, which I included as faint, white text).

Each List container is actually linked to data from the Groups table. This this is done so that the Winner and Runner up flags will eventually show up.

Before any matches are played, each match is gray, there are no scores, and no indicated winners. Here’s group B:

Once a match is complete, the data should set the IsMatchFinished flag to True, and enter some scores. Then, the winner’s score and owner’s name will be highlighted in green, the loser in red. If there’s a tie, then the colors will just be gray.

Once the group stage is finished, the winner and runner-up will be set from the data. I’ve faked it here, and we can see that Phil is doing quite well with Iran:

Each match is represented by a single shape. That makes it easy to link to the match data stored in the JSON service. For the Group Stage, each match is a row in the group list. You can select individual matches, and view the shape’s Shape Data:

If you have Visio Standard, you could even update the scores using Shape Data, since the standard edition of Visio doesn’t have the data linking capabilities. The only problem with manual updates is that a lot of the diagram runs off of ids rather than names. So you’d have to enter team ids (which are 1 – 32) in the knockout phase page to get the results to update. The team ids are easily obtained from the Pool page, but still, this isn’t the most fun way to operate.

You can easily re-order the matches in a group list. Just drag a row up or down. Or select a shape and press the Up or Down arrow key. As an example, Phil has arranged all of Iran’s matches at the top of the group. We can see three green circles (outscoring opponents 55:4), way to go Phil!

Since the JSON data set included latitude and longitude for each stadium, I’ve built links to Google maps into each match shape. Just right-click and choose Link: Stadium on Google Maps, then you can take a virtual walk around the stadium!

In case you’re wondering how the flags were created…

First, I assembled a bunch of images from some web page, and gave them appropriate names. Then, I fired up LinqPAD and wrote some C# code to assemble the images into a single strip. That single file can be imported into Visio, then manipulated via the ShapeSheet to show just one frame.

The completed image is shown here in small and large instances:

The technique for showing just one frame of an “image strip” shape is described in the article: Visio Film Strip Shapes: Hurray for Hollywood!.

Since the data source is mostly representing teams and groups and matches using numerical ids, I had to build a bunch of lists using VBA or C# or Excel so that numbers could be converted to names within the ShapeSheet.

At the end of the Excel file, you’ll see a tab with some funny looking entries:

These lists were jammed into the ShapeSheet, so that my shapes could do things like look up team names from team ids, or show the appropriate team flag from just a single id. Since the team names were listed alphabetically, the team ids appear in a strange-looking order. If you’re interested in Visio’s ShapeSheet, be sure to acquaint yourself with the LOOKUP and INDEX functions.

Knockout Phase

The Knockout Phase page displays a typical bracket tree. The initial 16 teams match group winners against group runners-up. This part of the tournament is single-elimination, and the winners progress towards the middle of the page:

If the data updating isn’t working for you, you can still use Shape Data to make it work, but again you’ll have to mess with team ids. You’ll also have to be sure to set IsMatchFinished to True, and set the MatchWinner to the id of the team that won. There are also fields for HomePenalty and AwayPenalty in addition to HomeResult and AwayResult for setting the goals scored, since knockout matches must have a winner, which leads to penalty-kick shootouts. But there’s no ShapeSheet logic for determining winners based on scoring. Nor is there any ShapeSheet logic for feeding the winners of matches to the next-round match. I’ve done this in the past, but I avoided it this time, hoping the data would handle that.

Here I’ve simulated Phil’s magnificent run with Iran, as they destroy Uruguay, and move on to meet the Socceroos in the Quarter finals:

The knockout-round shapes are conceptually very similar to those of the group stage. Indeed, I started with a group row shape, and massaged it with the ShapeSheet until it worked. These shapes are also linked to a different table in the Excel file: KnockoutMatches.

Make no mistake, assembling a document like this is still not an easy thing to do. There is still a lot of ShapeSheet craft involved in creating the match shapes, as well as in linking the team owner names from the Pool page to all of the match shapes. The flag shapes require building photo strip shapes that can react to team ids, along with a bunch of lookup lists. I used VBA macros and LinqPAD C# code snippets to help in building formulas, generating lists, and updating things after I’d made mistakes.

Epilogue

There are still a few unknowns regarding this download, but if everything goes well, it should be pretty neat (except that the USA isn’t in the tournament, boo hoo again!) So as I push the “publish” button, things that I’m considering or worry about include:

I hope that the JSON file does indeed get updated as the tournament progresses.
I hope that Visio can somehow tell Excel to update itself, so that you don’t need to first update Excel, then open Visio and update the diagram.
I hope that your local copy of the Excel file is automatically detected by the local Visio file, and that the “data source can’t be found” because of a hard-coded path (on my machine!)
I hope that I can publish the diagram online somewhere, with the linked data, such that everyone that can access www.visguy.com can see it.
I hope that I can do the same with the Excel file, so that the end result is a read-only web page with updated Visio graphics, the result of between 0 and 3 mouse clicks.
If you’re still on Visio 2010, you’re out of luck. I built this in Visio 2016, and I don’t have time to make a VSD version (especially since I will probably make updates to the file)
Your addition of Visio may not support data linking (Visio Standard)
Manual editing of results may be such a pain, it won’t be worth it.

As I get more information about these issues, I will update the article. If you encounter any weirdness, please let me know in the comments below, or shower me with praise (if you must) as well!

Diary

2018.06.15

After two matches played, the data source IS being updated, hooray! I ran some tests to see how my stuff was updating, or not:

I opened the Visio file and found that the data source was invalid. That’s because I moved all the files to a new directory. Visio too stupid to look in the same directory as the VSDX file.
I changed the path for all the data sources in the Visio file to reflect the new path, I was then able to refresh. Nothing changed.
I opened the Excel file that is linked to the JSON file (world-cup-query.xlsx.)
On the Data tab, I clicked Refresh All. Lo and behold, the data updated!
Returned to Visio and hit Data > External Data > Refresh All. Boom, the diagram updated!Russia and Uruguay were victorious in their opening matches!

So the process is easy, but not super-convenient. However, it is still way better than writing code to get this done. I decided to try a couple more things:

Save the Visio file and Excel file up to a OneDrive for business folder.
Set the Excel file to refresh every 60 minutes.
Set the Visio file to automatically display the Refresh dialog on File > Open.
You can actually view that file in your browser with this link. I did run into problems with browsers being logged in to OneDrive with different accounts, so perhaps copy the link to a different browser if you have difficulties.So we’re close. You can download the Visio file, enter your office pool names, then save Visio and the Excel file to a SharePoint location. You’ll have to redirect the Visio data sources to the cloud-stored Excel file. Then, if there is some magic way to get the Excel file to update from the JSON, you’ll have a web page that updates automatically! Otherwise, someone will have to update Excel, and perhaps Visio–but I think Visio will update from the Excel file without anybody having to open the VSDX file.

I like the font that the event is using, it has a Russian flare and looks elegant. I was able to find the TTF file online, and downloaded it. The online version of the Visio file now uses it:

Looks cool, doesn’t it? Normally I would post a link, but the “free font” universe is pretty dodgy. If you want the font, it appears to be called Dusha. Try searching f for “Download FIFA 2018 Font Typeface – Dusha Font TTF”, and be careful what you click! Interestingly, after ten minutes, that font isn’t showing when you view the file in a browser. Maybe it never will? Regardless, if you install the file, then download the Visio file from the browser, that version of the file will have the cool font.

Back to the data:

I’ve waited an hour to see if Excel would magically update the data. It hasn’t, so I’ve opened the (cloud) file and clicked refresh, then saved. Now I will wait a while to see if the online Visio file updates without me actually opening it.
…
I followed the link to the online Visio diagram on my iPad. I notice that there is a Refresh Data button at the top-right which is grayed out. Maybe only users with access or the right type of OneDrive accounts are allowed to cause the refresh to happen. The case is the same in Chrome and Opera, where I am not logged in to Microsoft services. I guess I’ll have to refresh both Excel, then Visio every day!
Ok, I opened the online Visio file, clicked Refresh, then saved.
Refresh of the Visio page on the iPad immediately shows the changes!

2018.06.25

Everything is running smoothly so far. The minor gripes that I have are:
- I have to first update Excel (which is linked to the JSON file) and save it.
- Then I have to manually update the Visio drawing and save that.
- Both are saved to OneDrive for Business, and the link to the online Visio diagram then displays the latest results. It would be nice to have one or zero update steps, however.
I’ve had the idea of trying this in Power BI for all of this. Visio can be embedded as a visual in Power BI, but I’m not sure if just any visualization will properly update or not. I assume that Power BI will fetch the latest JSON automatically, but I am not 100% sure there either.

2018.06.26

As group play starts to wrap up, the Groups table in the data is coming in to play. Here we can see that the group lists are indeed updating to show the winner and runner-up on the left and right sides of the list heading.Congratulations to Spain and Portugal!

2018.06.29

The group stage is all finished and it’s time for the knockout-phase to start. The data has updated fantastically from the JSON, and the Knockout Phase page has started receiving new data, and is also updating:
I have been exclusively updating the online Visio diagram, since everybody can see it. Interesting to note: when I saved the file today, with the Knockout Phase page showing, that becomes the default view when visitors follow the link. Makes sense, but good to know!
I experimented with PowerBI a few days ago, but to no avail. You have to configure within PowerBI the fields you want to visualize in Visio, and you choose–from within PowerBI–whether labels and values should appear as text, or whether values should be represented by color. The fancy data-linking that I’ve done in Visio isn’t used in PowerBI. Bummer.
I think there may be a way to link to SharePoint data sources, then have the Visio diagram update automatically from them. That leaves these problems to solve:
- Getting data in SharePoint to update from an external JSON file (there must be a way to do that!)
- Allowing people not in the SharePoint organization to see these updates.

2018.07.02

The knockout-phase games provided a couple of ties yesterday, which means penalty kicks (shootouts) decided the match outcomes. The shapes handle the extra data just fine. The secondary circles hold the penalty-kick tallies:

2018.07.15

And that’s a wrap! Congratulations France (and Michel)! And nice job Croatia, it was a heck of a run!

Downloads

The zip file with both the Visio and Excel files:

Download “World Cup 2018 Bracket Diagram” s!Aj0wJuswNyXlhHRqYuyE1iu8b5iG – Downloaded 2967 times – 103.00 B

Just the JSON-connected Excel file: