Playing with Excel Pie Charts: Asset Allocation Visualizer

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
User avatar
neurosphere
Posts: 2363
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC
Contact:

Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby neurosphere » Wed Jan 11, 2017 1:03 pm

Hi all, I stumbled across this post at some point (viewtopic.php?t=106505) which got to me to thinking and tinkering...

I use a simple pie charge in excel to illustrate possible asset allocations to clients, with a very simple interface where one can set the desired ratios (e.g. stocks/bonds, us/international, us large/ us small value, TIPS/Nominal, and if applicable state vs national munis) and it will automatically update the chart.

But just for fun, I've been trying to create a tool in excel which will allow one to enter many different types and subtypes of asset classes, with "cascading" outer rings showing the added granularity.

It's a work in progress, but here is an example:

Image

I didn't really stress for now whether REITS is its own asset class or a subset of small or value, and there is no gold or commodities, etc. But I kinda like the way this looks. If really has super-slided-and-diced portfolio like the one in the example, the labels get a little crowded, but as long as one has at least 3% as the smallest slice (on the outer ring), it's not too bad.

Currently, if one simply wants to create a static picture, it's not too hard to do in excel (although it took me a while to figure out how to set up the cells). Making it dynamic in order to play with how different allocations might appear, and ensuring each ring has the correct labels (and each ring adds up to 100%) is trickier and something I hope to work on and shares sometime soon (which may be years or may be never). :D It's also hard to decide how to break down bonds (corporate vs treasury, quality, duration, savings bonds?, munis? state vs national munis?, etc) to account for any potential portfolio.

Anyway, I mostly just wanted to share what I thought was a pretty picture.
-- Real name: Sotirios Keros. If you have to ask "Is a Target Retirement fund right for me?", the answer is yes.

batpot
Posts: 568
Joined: Thu Jul 11, 2013 8:48 pm
Location: Seattle

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby batpot » Wed Jan 11, 2017 1:27 pm

That's some advanced excel'n there.
cool figure.

I created a similar spreadsheet that I use to re-balance annually. The pie chart is all of 4 pieces.

Have 2 cells to enter current 401k and Roth values, and it shows me how to reallocate my 401k to meet my AA (Roth remains 100% VTSAX).
I only use 4 funds in my 401k: S&P 500 index, Russel 2k index, International Index, Bond Index.
My spreadsheet only has 3 editable variables; ratio between stocks:bonds, large cap:small cap (with the assumption that VTSAX is always "correct"), and US:International.

User avatar
midareff
Posts: 4666
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby midareff » Wed Jan 11, 2017 1:30 pm

I use pies in excel to cover many things from income to expenses, AA, alpha against benchmark and so forth. Interesting work you have there but might it be a bit too complex for a client to comprehend?

User avatar
neurosphere
Posts: 2363
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC
Contact:

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby neurosphere » Wed Jan 11, 2017 1:42 pm

midareff wrote:I use pies in excel to cover many things from income to expenses, AA, alpha against benchmark and so forth. Interesting work you have there but might it be a bit too complex for a client to comprehend?


Ha! Definitely NOT meant for clients! :shock:

This is meant for extreme slicers and dicers on Bogleheads.

My goal is to include "all" asset classes, and any asset class not used would "disappear" (i.e. a "zero" for that asset class would delete the wedge and the label). So the chart above includes a spot for each asset class, just as an example.

Up until this morning, the most complicated graph I had for a suggested portfolio to a client was the one below. You can see I just included US and International stocks in the same "ring" for simplicity.

Image
-- Real name: Sotirios Keros. If you have to ask "Is a Target Retirement fund right for me?", the answer is yes.

batpot
Posts: 568
Joined: Thu Jul 11, 2013 8:48 pm
Location: Seattle

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby batpot » Wed Jan 11, 2017 2:27 pm

One recommendation: use shades of the same color for sub-asset classes/outer rings.

Will make the whole thing easier to read.

User avatar
midareff
Posts: 4666
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby midareff » Wed Jan 11, 2017 5:42 pm

neurosphere wrote:
midareff wrote:I use pies in excel to cover many things from income to expenses, AA, alpha against benchmark and so forth. Interesting work you have there but might it be a bit too complex for a client to comprehend?


Ha! Definitely NOT meant for clients! :shock:

This is meant for extreme slicers and dicers on Bogleheads.

My goal is to include "all" asset classes, and any asset class not used would "disappear" (i.e. a "zero" for that asset class would delete the wedge and the label). So the chart above includes a spot for each asset class, just as an example.

Up until this morning, the most complicated graph I had for a suggested portfolio to a client was the one below. You can see I just included US and International stocks in the same "ring" for simplicity.

Image



I like it but thing you need an addition or two for the non stock non bond items such as REITs and Commodities.

User avatar
midareff
Posts: 4666
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby midareff » Wed Jan 11, 2017 5:43 pm

midareff wrote:
neurosphere wrote:
midareff wrote:I use pies in excel to cover many things from income to expenses, AA, alpha against benchmark and so forth. Interesting work you have there but might it be a bit too complex for a client to comprehend?


Ha! Definitely NOT meant for clients! :shock:

This is meant for extreme slicers and dicers on Bogleheads.

My goal is to include "all" asset classes, and any asset class not used would "disappear" (i.e. a "zero" for that asset class would delete the wedge and the label). So the chart above includes a spot for each asset class, just as an example.

Up until this morning, the most complicated graph I had for a suggested portfolio to a client was the one below. You can see I just included US and International stocks in the same "ring" for simplicity.

Image



I like it but thing you need an addition or two for the non stock non bond items such as REITs and Commodities. ... and perhaps bank cash.

User avatar
neurosphere
Posts: 2363
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC
Contact:

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby neurosphere » Wed Jan 11, 2017 6:00 pm

midareff wrote:I like it but thing you need an addition or two for the non stock non bond items such as REITs and Commodities.


see my original post. :wink:
neuropshere wrote:I didn't really stress for now whether REITS is its own asset class or a subset of small or value, and there is no gold or commodities, etc.


Yes, REITS and gold/commodities could be added as a non-stock, non-bond asset class. Perhaps then break REITS down into domestic and foreign? Commodities broken down into gold, non-gold PM, pork bellies, etc. 8-)

Any other major asset classes missing?

batpot wrote:One recommendation: use shades of the same color for sub-asset classes/outer rings. Will make the whole thing easier to read.


Good idea. For now I just let excel automatically choose the colors. The problem with the colors is that either excel chooses, or one has to individually select the color of each cell. So each time I change my mind (say, I move REITS from a small stock category into it's own category) I have to select and change each affected wedge individually and change the color manually. So I think the colors will be the very last thing I do. But I agree, "Blue" and various shades of blue might represent US Stocks, "Green" for International, etc.

NS

batpot
Posts: 568
Joined: Thu Jul 11, 2013 8:48 pm
Location: Seattle

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby batpot » Wed Jan 11, 2017 10:02 pm

I found/modified a script that would define colors based on titles after the chart was created, so you just let excel do what it does, then run the script.
Just have to keep the script up to date w/ your chart categories/allocation.

User avatar
midareff
Posts: 4666
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby midareff » Thu Jan 12, 2017 8:34 am

neurosphere wrote:Any other major asset classes missing?




You might consider a slice for cash.. retired people can easily keep a meaningful slice of cash, money market & cash like CD's.

User avatar
neurosphere
Posts: 2363
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC
Contact:

Re: Playing with Excel Pie Charts: Asset Allocation Visualizer

Postby neurosphere » Thu Jan 12, 2017 10:36 am

midareff wrote:You might consider a slice for cash.. retired people can easily keep a meaningful slice of cash, money market & cash like CD's.


Oh yes, right, you had mentioned cash and I forgot to acknowledge it.

batpot wrote:I found/modified a script that would define colors based on titles after the chart was created, so you just let excel do what it does, then run the script. Just have to keep the script up to date w/ your chart categories/allocation.


I've never use a script in excel but would be interested to try it. Can you share it with me? I don't know if this little project is worth the effort to learn scripts, but the knowledge obtained may turn out to be useful elsewhere later.

NS


Return to “Personal Finance (Not Investing)”

Who is online

Users browsing this forum: bayview, Bing [Bot], Palatineman and 43 guests