Saturday, September 17, 2016

Tracking My Stock Portfolio with an iPad and Excel

I have tried everything from Quicken to Morningstar Portfolios to Apple Numbers to Google Sheets, yet I always tend to come back to good old Microsoft Excel to monitor my investment portfolio. 

In addition to the above, I have also looked high and low around the web for solutions; people who have set up Excel or Google Sheets versions of portfolio trackers I could copy.  There is a lot of great portfolio trackers out there done by individual bloggers.  Check out this one by Old School Value or Portfolio Slicer or No More Waffles Dividend Tracker.

Those are all awesome options for tracking a portfolio.  However, for my own personal needs none of them gave me exactly what I wanted to track myself.

In the end, I realized that the only way to get exactly what I wanted would be to create a portfolio tracker myself. 

Things I Knew I Wanted To Include


My first step was to figure out exactly what I wanted my portfolio tracker to include.   Here are the things that I wanted to make sure were available in the tool I created:
  • Super simple to use
  • Microsoft Excel based due to more advanced charting and look of Excel versus Google Sheets
  • Track performance against the S&P 500 and S&P/TSX Composite Index (Canadian index) from a simple return perspective
  • Track the portfolio and individual holding book and market values
  • Graphically see the performance for each allocation element in my portfolio
  • Organize the portfolio in accordance with the well-research strategies I use
  • Track my pension and employer accounts, which do not all have tickers associated with the investment holdings
  • Track asset allocation percentages against asset allocation targets
  • Track cash balances
  • Ability to use the Excel spreadsheet on both a desktop and iPad
  • Automatically update stock prices, both on the desktop version and my iPad
Most of these requirements are pretty basic and don't require any special skills.  In fact, most of it only requires an organization solution; how I set up the spreadsheet to display the information will solve most of these problems.

The trickiest one to solve is the ability to use this on an iPad and desktop and update stock prices automatically across both systems.  The desktop version of Excel allows users to automatically update stock prices with the "Webservice" function, however because of the permissions in the iPad, Excel for the iPad is not allowed to get external date.

I found a solution which I will talk about in a minute. 

Things I Knew I Didn't Want


The second step was noting what I didn't want, or need, the Excel portfolio tracker to do.
  • Calculate the compound annual growth rate (CAGR)
  • Track the inflows and outflows (only worry about book and market values)
  • Tax recording - brokers help with this anyway, and most is in tax protected accounts
  • Track each individual transaction (this takes way too much time)
The one that may surprise people is that I didn't want to track the CAGR.  The reason is not that I don't care what my CAGR is, but more that with wanting to keep things simple.  CAGR requires an input of all the deposits and withdrawals and I have found in the past that I am not great at staying on top of that.

I found that by simply keeping track of each account's book value, I can calculate my return and get a good sense of how portfolio is doing.  In addition, by including the market prices for the S&P 500 and S&P/TSX I can also compare how I am doing against the market.

The Portfolio Tracker Design

With all the wants and not-wants incorporated, here is what I came up with to track my portfolio.  I will show each section and explain what it is showing.  Please note that I have changed the values to protect my actual portfolio values so some of the numbers may not make sense!

Portfolio Graphs

The portfolio graphs provide a visual for the two types of metrics I wanted to track in my excel portfolio tracker. The first was tracking my asset allocation.

I track asset allocation with cash and without cash.  My portfolio is broken down into Canada, Global, and US buckets.  In each of these buckets I hold cash.  As a result of this, I show two graphs related to asset allocation: a cash-based allocation which includes this cash amount and a pure asset allocation which shows the cash allocation separately.

Here is what the asset allocation graphs look like:

Performance Graphs

The performance graphs show how each of the strategies I use in my portfolio are performing.  I like to track performance like this as from time to time each part of the portfolio will perform better than the others.  This allows me to review this over time.

The performance graphs are also broken down into percentage return and dollar return.  I like to show both just out of interest.  The strategy performance view will show me on a dollar basis how that investment strategy is performing on a dollar value basis.

Here are what the two performance graphs look like:

Investment Strategy Excel Performance Graphs

Comparison to Market Performance

It is important to me that the portfolio beat the stock market.  Otherwise, I would be better off just buying an index ETF like SPY and saving myself a lot of time.

I track my performance based on the two currenct types I hold in my portfolio: Canadian and U.S.

My U.S. holdings include all investments made in U.S. dollars, including my Global allocation.  As a result, I compare the result of my U.S. and Global holding against the U.S. market performance.

This may seem funny - why don't I compare my Global portfolio results to a global benchmark.  The first reason is simplicity and the added value that would provide.  With the U.S. being the largest market in the world, I better make sure I at least keep up with that market.  The added value I get by being global diversified is important, but at the end of the day I want to beat the SPY.

I also compare my Canadian holdings - which is based on the Beat the TSX methodology - to the Canadian S&P/TSX Composite index.  Again, I want to make sure I am beating the Canadian market over the long term so tracking the performance of this strategy against that benchmark is important.

Here is what this table looks like.  The last column shows if I am beating the market (over) or if the market is beating me (under).

excel portfolio tracking market performance

Individual Holdings

The individual holdings section of the spreadsheet lists all of the individual holdings, divided up into each strategy I use and includes the following details:
  • Holding Name
  • Current Price
  • # of Shares
  • Market Value
  • Book Value
  • $ Return
  • Simple Return
  • % of Portfolio
  • % of Portfolio - Non-Cash
All I need to do when holdings are added or removed is update the cash balance, update the number of shares, and enter the book value.  Since I have the stock prices updated automatically, the market value is calculated when I open the Excel portfolio tracker.

Here is what the individual holding section looks like:

Company Investment Plans

The trouble with company sponsored investment plans, at least here in Canada, is that often the investment choices are only available to the plan holders and therefore there is not a way to automatically update the market prices.  For example, the defined contribution pension plan uses a target date segregated fund which is not available to the public.

The solution is actually simple.  I keep these holdings separate and simply enter the actual account value twice a month when the transactions are completed within the accounts.  I am also able to get good performance statistics right from within the accounts so I always know how they are performing.

Here is what this section looks like, which is not much - only the current values are used:

Getting Stock Quotes with Excel Using an iPad 

As mentioned earlier, getting stock prices to automatically update in the iPad version of Excel is difficult.  Apple, with all the control they love to keep, make it hard for apps to get external data.

The solution I found was to not actually use a formula based call to external web sites to get the data (like most people do from Yahoo! Finance).   Instead I use a free Excel Add-In called Stock Connector.

Granted it is a bit buggy on the desktop side, it works nicely on the iPad.  The set up is easy; enter the ticker and select a cell where the price needs to be updated.  Once you open the Excel portfolio tracker on the iPad, you simply open the Stock Connector add-in and it refreshes all the prices in your sheet.

The one downside is I am having is the desktop version can have difficult connecting to Yahoo.  However, since I am using this more and more on my iPad it has not been an issue.


This excel spreadsheet is not going to work for everyone.  As I said earlier, I think that because everyone has slightly different needs, most of the options available out there are not going to work for everyone.

So instead, I created my own using the sections above.  It works for me and does everything I need it to do.

No comments:

Post a Comment