Our training for today here at The Data School was to complete one of more Workout Wednesdays: the weekly challenge (previously set by Mr. Kriebel) set by Rody Zakovich and/or Luke Stanke This is the one I was tasked to do: Week 45: Stock Portfolio
In my eyes, there were two parts to this exercise:
1. Make The Calculations
I won’t go through the LOD calculations here; that isn’t the purpose of the blog. However, if you want to see how they’re set up, you can download Andy’s workbook.
You then want to normalise the figures so each stock price is assigned a value between 0 and 1 (0 being the 52-week low value, and 1 being the 52-week high value). This ensures that you can compare stocks, keeping them all on a normalised axis.
The main problem was the next part, and thankfully Ravi was able to help out and lead me to right answer!
2. Make the Chart With (Seemingly) Three Marks On One Axis
A) The Problem
I was stumped for a while on this part because it seemed as though there were three separate marks on the chart:
- The grey bar (could be a regular bar or a gantt bar)
- The blue circle
- The black vertical lines at either end of the grey bar
Firstly, I knew I couldn’t use reference lines or bands, because the tooltips show when you hover over the black bars and the grey bar:
You can dual axis to get any two of them, but not all three. I tried combining the grey bar and the black vertical lines, as they both could be gantt bars, and then creating a dual axis with the circle. However, I couldn’t figure out a way to size the grey bar and the black bars differently.
So what can we do?
B) The Solution (thanks Ravi)
The answer is the shapes mark. Add measure names (in this case, current stock price, 52-week low price, 52-week high price) to shape and colour.
You can then select a circle shape for the current price, and a pipe – or vertical bar – shape for the black bars.
Two shapes, one mark type.
You can then create a dual axis with the grey bar and there you have it!