Periodic Table of Investment Returns

BlackieHamel · Jan 19, 2019

Is this possible in Excel?

Callan Associates, among others, has for many years published a year-by-year listing of what investment returns were by asset class. The point is to demonstrate the importance of diversifying, because no asset class outperforms others from year to year. See

https://www.valuewalk.com/2017/03/the-alternative-callan-periodic-table-of-investment-returns/

for an example; there are many on the web.

I'd like to produce a similar chart, but to compare results by investment provider, not asset class.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]-0.9%[/TD]
[TD]12.1%[/TD]
[TD]19.1%[/TD]
[TD]4.5%[/TD]
[TD]-2.3%[/TD]
[TD]14.6%[/TD]
[TD]8.8%[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]-0.4%[/TD]
[TD]9.4%[/TD]
[TD]14.7%[/TD]
[TD]2.1%[/TD]
[TD]0.2%[/TD]
[TD]10.5%[/TD]
[TD]8.3%[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]-2.8%[/TD]
[TD]10.9%[/TD]
[TD]14.5%[/TD]
[TD]1.0%[/TD]
[TD]-1.4%[/TD]
[TD]11.8%[/TD]
[TD]7.1%[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD]-0.4%[/TD]
[TD]11.1%[/TD]
[TD]15.5%[/TD]
[TD]1.7%[/TD]
[TD]-2.9%[/TD]
[TD]13.3%[/TD]
[TD]7.9%[/TD]
[/TR]
[TR]
[TD]Company E[/TD]
[TD]-1.8%[/TD]
[TD]10.5%[/TD]
[TD]15.5%[/TD]
[TD]2.4%[/TD]
[TD]-1.4%[/TD]
[TD]13.3%[/TD]
[TD]9.1%[/TD]
[/TR]
[TR]
[TD]Company F[/TD]
[TD]1.1%[/TD]
[TD]11.1%[/TD]
[TD]18.2%[/TD]
[TD]8.1%[/TD]
[TD]-2.3%[/TD]
[TD]11.2%[/TD]
[TD]9.0%[/TD]
[/TR]
[TR]
[TD]Company G[/TD]
[TD]2.1%[/TD]
[TD]11.4%[/TD]
[TD]19.8%[/TD]
[TD]5.5%[/TD]
[TD]-2.0%[/TD]
[TD]14.9%[/TD]
[TD]8.0%[/TD]
[/TR]
</tbody>[/TABLE]

This is just a part of a bigger data set.

The idea would be for Excel to (1) assign a color code to each Company and highlight its result in that color for each year; and (2) automatically sort the results from best to worst in each year.

If this is not something Excel can do, do you have an idea of another program to do it? Thanks!

Blackie

shg · Jan 19, 2019

If you start with a three-column list (company, year, results), you can do the sorting in the wink of an eye:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][td="bgcolor:#C0C0C0"]

C

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td="bgcolor:#F3F3F3"]

Company

[/td][td="bgcolor:#F3F3F3"]

Year

[/td][td="bgcolor:#F3F3F3"]

Result