World Cup 2018 Bracket and Spreadsheet

Manual editing of results may be such a pain, it wont be worth it.

Download World Cup 2018 Excel from JSON FileDownloaded 39 times 103 B

You can actually view that file in your browser withthis 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 were close. You can download the Visio file, enter your office pool names, then save Visio and the Excel file to a SharePoint location. Youll 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, youll have a web page that updates automatically! Otherwise, someone will have to update Excel, and perhaps Visiobut I think Visio will update from the Excel file without anybody having to open the VSDX file.

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

Then, on theGroup StageandKnockout Phasepages, the owners name will follow along, so that you can see whos doing well, and whos not.

I hope that the JSON file does indeed get updated as the tournament progresses.

Visio 2013 Business Process Diagramming & Validation

Refresh of the Visio page on the iPad immediately shows the changes!

If youre aShapeSheet Nerd, this works like this:

Set the Excel file to refresh every 60 minutes.

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:

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

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.

Go 3D with Free Isometric Piping Shapes for Visio

Visio 2010 Business Process Diagramming & Validation

Microsoft Visio 2010 Business Process Diagramming and Validation

The Document ShapeSheet also contains aUser-defined ShapeSheet Functionthat allows for (see:

Looks cool, doesnt it? Normally I would post a link, but the free font universe is pretty dodgy. If you want the font, it appears to be calledDusha. Try searching f for Download FIFA 2018 Font Typeface Dusha Font TTF, and be careful what you click! Interestingly, after ten minutes, that font isnt 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.

Each of those cells refers to the text of one of the Pools owner shapes.

[Shift] + [Right-click] Show ShapeSheet

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

I followed the linkto the online Visio diagramon my iPad. I notice that there is a

Your addition of Visio may not support data linking (Visio Standard)

Microsoft Visio 2013 Business Process Diagramming and Validation

Update:you can now view the latest version of the bracket in your browser. Ive saved the Visio file to OneDrive for Business/SharePoint, which allows you to view aread-only version of the Visio 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.

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 Ill have to refresh both Excel, then Visio every day!

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

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

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.

Release the Power of Visio Custom Line Patterns

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

Ok, I opened the online Visio file, clicked Refresh, then saved.

Ideally, I would have posted this article a week or two ago, but, well, I had a lot ofrealwork 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.

I opened the Excel file that is linked to the JSON file (world-cup-query.xlsx.)

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, Ill be sure and add a link to this article!

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 youre interested in Visios ShapeSheet, be sure to acquaint yourself with the LOOKUP and INDEX functions.

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 buildingphoto strip shapesthat 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 Id made mistakes.

Create Visio Flowcharts Programmatically

I hope that I can publish the diagram online somewhere, with the linked data, such that everyone that can access can see it.

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

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

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 shapes Shape Data:

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

If you have Visio Standard, you could even update the scores using Shape Data, since the standard edition of Visio doesnt 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 youd 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 isnt the most fun way to operate.

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

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.

Mastering Data Visualization with Microsoft Visio Professional 2016

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.

Set the Visio file to automatically display the Refresh dialog on File Open.

Here Ive simulated Phils magnificent run with Iran, as they destroy Uruguay, and move on to meet the Socceroos in the Quarter finals:

The Group Stage page has some interesting features. First, each group is aList 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).

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.

The zip file with both the Visio and Excel files:

As I said, I didnt 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:

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

I wont 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 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!

Of course, my ultimate goal was to link this data into Visio. In years 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 doesnt 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 wont have to open up Excel, then open up Visio, but Im not 100% sure how this is all going to work out, as of this writing.

If the data updating isnt working for you, you can still use Shape Data to make it work, but again youll have to mess with team ids. Youll 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 theres 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. Ive done this in the past, but I avoided it this time, hoping the data would handle that.

Mastering Data Visualization with Microsoft Visio Professional 2016

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:

Moving to VB : Strategies, Concepts, and Code

Save the Visio file and Excel file up to a OneDrive for business folder.

Visio 2D, 3D and Virtual Reality

Download World Cup 2018 Bracket DiagramDownloaded 22 times 103 B

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 Irans matches at the top of the group. We can see three green circles (outscoring opponents 55:4), way to go Phil!

I hope that your local copy of the Excel file is automatically detected by the local Visio file, and that the data source cant be found because of a hard-coded path (on my machine!)

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

If youre still on Visio 2010, youre out of luck. I built this in Visio 2016, and I dont have time to make a VSD version (especially since I will probably make updates to the file)

Download World Cup 2018 Excel from JSON FileDownloaded 39 times 103 B

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:

A new feature this year is thePoolpage. 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:

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

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

Windows Presentation Foundation Unleashed

Microsoft Visio 2013 Business Process Diagramming and Validation

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

The match shapes on the Group Stage and Knockout Phase pages then refer to those Document ShapeSheet cells to get the owner of each teams name.

Ive waited an hour to see if Excel would magically update the data. It hasnt, so Ive 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 opened the Visio file and found that the data source was invalid. Thats because I moved all the files to a new directory. Visio too stupid to look in the same directory as the VSDX file.

I hope that Visio can somehow tell Excel to update itself, so that you dont need to first update Excel, then open Visio and update the diagram.

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.

. Boom, the diagram updated!Russia and Uruguay were victorious in their opening matches!

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

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:

In case youre wondering how the flags were created

Data External Data Refresh All

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

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: