Sankey generalized

This evening I attempted to generalize my circular Sankey VBA to accommodate different diagram types. It turns out, generalizing the program only required modifying a single loop (earlier version is presented in previous in post).

What is required from the end-user is that they create the lines they want on the template, give them a name, and add the relevant count to the data tab. I tested this versions by creating a piece of an energy flow diagram presented at Excel Hero. I didn’t seek to replicate this with any accuracy, but just used the concept as of something different than the original grades changes example. In my first attempt, I created the curved lines by grouping a few straight lines. This works in the sense that the grouped line can be referenced and the width adjusted as a single object. It doesn’t work in the sense that the lines are not actually seamlessly connected. I actually like how this looks.

So I gave it another try using a freeform line and that worked fine.

But that did not entirely represent a true Sankey as the lines are essentially separate and are not breaking off a single line.  To address this, I broke up the main line into a few segments and decreased the width of each segment as other lines forked off it. These calculations are simple to add to the data worksheet. Still this is a bit choppy. The lines connect in odd places. In this case, a few minutes of manual movement of the lines cleaned things up nicely. It would be nice if the lines connected with each other correctly, but I don’t see myself creating a whole lot of these diagrams on the fly. Any time these are created there will need to be some manual decisions regarding where to place the nodes and how they should be connected. An algorithm could be developed (although maybe not by me), but I think for many applications there will be a need for manual grouping depending upon the desired message and to improve clarity. At this point I have automated basically everything I would want to automate in regards to Sankey creation, which may not be saying much, given that I have rather limited use of Sankey diagrams.

Template

Data

Green requires data entry, the rest is calculated.

Diagram pre-processing

Diagram couple minutes of manual modification

Coincidently, I ran across a diagram that looks remarkably similar to my circular Sankey two days after posting it. The purpose of Martin Wattenber’s diagram is entirely different, not to mention far more sophisticated and apparently was created 9 years. If you are looking for lame Excel visualizations decades after they could be considered novel, this is the place.

Note: As usual, if you want the workbook, request it.