Creation of a Portfolio Dashboard

Creation of a Portfolio Dashboard

Situational awareness is vital to successfully manage an investment portfolio, and creation of an investment dashboard is an easy way to stay on top of critical information.  For me, I have found that Excel can be a very powerful tool for management of an investment portfolio.

The first step is to create a summary page, or the dashboard.  To do this, first identify what high level information you would like to be aware of at a glance.  For example, I have identified that I like to have the overall value of my portfolio, the average monthly distributions, yield and asset allocation information at my fingertips.  Using some neat tricks within Excel (Smart Art Tools and links to data content) I have created a nifty dashboard for myself that looks like this:

portfolio dashboard


Powering the calculations that generate this dashboard are several spreadsheets containing detailed information on all of my investment data.  Because I hold investments in several account types I use a matrix system with one master summary table fed by information from identical tables representing each of my investment accounts, with each contained on separate spreadsheet tabs.  I like visual cues to capture my attention to where it is needed, so I utilize conditional formatting in several areas to make critical information really stand out to me.  Excel can do some very powerful things if the effort is put in to make it work. Makes me feel like a pro too.

One thing I have not done yet, but am considering, is harnessing Excel’s ability to gather information such as stock quotes directly from the internet so that no manual input is required until I make a trade or add funds into an account.

Here is an example of the summary sheet that I use:

portfolio dashboard summary sheet

In this example it shows that overall the bulk of the portfolio is in a healthy range that requires no action; however, some areas such as Fortis and Thomson Reuters stand out.  Based on my rule of no action until the minimum $500 trade threshold is met, the only action I would implement to correct this scenario is a sale of 16 shares of Fortis.

Of course, with Excel or similar programs, the investor can tailor the information displayed to meet whatever individual needs seem most important.  Regardless of the tool chosen, the obvious benefit of having real time awareness of your investments will allow for you to spend your time analyzing data, rather than collecting it.