Charting Negative Values Next To Quartiles In Excel
What if we want to compare a fund’s performance to a group of its Morningstar category peers?
It’s easy enough to take the group’s minimum value, maximum, average and median. We can then look at where our fund’s performance sits among these basic calculations.
Here’s ARKK’s 1-Year return (dot) charted over the Morningstar “Mid-Cap Growth” category min, max and median.
Ouch! The fund’s return aligns with the worst return experienced in its category. Let’s look at the 5 Year.
In this case, ARKK is around -2%. It isn’t the worst fund.
Portfolio management is also a competition between fund managers. Were the competitors evenly distributed between the minimum and median? Or were many clumped closer to one or the other quartile? That is, did ARKK have a lot of company, excusing its performance a bit, or were other funds closer to the median making ARKK more of an outlier.
To give us better insight in how the fund performed against its real-world alternatives we can group the category’s funds into quartiles. We’ll split them up into four groups and measure the range of each group into four sets of quartile minimums and maximums.
Here’s the kind of chart we want to see.
ARKK is in the middle for the 1st (worst) quartile. We know the top three quartiles hold 75% of ARKK’s competitors. That it’s in the middle of the 1st quartile suggests 87% of ARKK’s alternatives were better.
Graphing a histogram around our fund shows that ARKK is well below where all the action is.
To recap, we often want to answer two questions about a fund’s performance. One, where does it stand, given the range of returns. Two, how did it do relative to its competitors if we class them into groups (in this case quartiles, but we could have used quintiles, etc.)
Excel does not make it easy to chart that data alongside negative fund returns. But it can be done.
Let’s look at Excel charting basics. Visually, the Excel chart that comes closes to showing quartile is the stacked-column chart. If our return scale runs from 0 to 100% then we could easily chart these side by side in Excel.
Before we tackle the negative returns problem, let’s be clear about what we’re looking at when we look at our stacked-columns.
Although stacked-columns can begin with negative numbers, they would end at the zero line.
Fortunately, our primary interest isn’t the zero-based scale of the stacked-column chart, but the proportional size of each quartile (box) next to the observed fund returns.
We don’t need to see the scale of the stacked-bar chart, only the size of the boxes (quartiles) next to the fund returns.
The approach I’ve used it to create a second scale for our fund returns, one that is a zero-based positive scale (our audience will never see it). Let’s say our fund returns range from -19.57% to 8.53%? Our second scale would range from 0% to roughly 28%
It’s difficult for my brain, at least, to keep these different scales in my head at the time time.
Our first quartile box above is 6.06. Why isn’t it 0? The answer is that we’re ONLY measuring the distance between returns of funds at the bottom of our first quartile with that at the bottom of the second (or top of first). The bottom one had a -19.57% return, and the bottom of the second quartile had -13.5.
By using an intermediate trick, of adding the absolute value of the smallest return (-19.57 to +19.57) we can be sure that EVERY fund return value will be positive. Again, this is a background scale no one ever sees. Once we have positive numbers we can easily calculate the proportional distance between the first and second quartiles (and others) regardless of their negativity (haha).
That proportional value is 6.06 and we will visually judge the size of it next to the stacked-columns scale of 0 to 28.
Let’s pick a fund in the category above to see our end result
And let me turn on our invisible scale for our quartile boxes
All the charts above were created in Excel, but I used VBA to control the whole process. If you’re interested in technical stuff contact me at my max at maxdatabook email.
Microsoft has finally been making an effort to increase Excel’s functionality. Hopefully one day this will be in a standard chart and we won’t have to hurt our brains over it.
If you know of an easier way to do this please let me know!
