I am frequently asked about the tools I use to track investment portfolios. I use Microsoft Money to keep track of our portfolio, the components of which are spread across RRSP, Group RRSP and investment accounts. Microsoft Money is quite good at many tasks (such as keeping track of transactions) but is useless when it comes to others. One major shortcoming is the lack of useful reporting on the asset allocation of a portfolio because bond, large-cap, mid-cap and small-cap are the only asset classes available in Microsoft Money. So, I’ve been using a simple spreadsheet I put together in Google Docs to do just that: provide an asset allocation snapshot of a portfolio.
The spreadsheet takes advantage of stock price information available through the GoogleFinance function. For example, you can use ‘=GoogleFinance(“XIC.TO”, “price”)’ to obtain the recent price of the iShares CDN TSX Capped Composite ETF. For US-listed stocks or ETFs, drop the “.TO” suffix. The Canadian dollar to US dollar exchange rate can be obtained by processing the Yahoo! Finance quote for USDCAD=X. (If you are interested here’s how the exchange rate is obtained: ‘=Index(ImportHTML(“https://finance.yahoo.com/q?s=USDCAD=X”,”table”,1),8,2)’.).
The rest of the spreadsheet is straightforward. The last column shows how much an asset class is below target. When I add some money to the portfolio, I simply buy the asset class that is the most below target.
Updates from readers:
- The “.TO” suffix for Canadian stocks is needed only if the same ticker symbol is used in an US exchange. For stocks such as Bombardier (BBD.B) or Telus (T.A) that have multiple class of shares, their TSX ticker symbols should do the trick. Similarly, for income trusts, simply use their TSX ticker symbol to obtain the price. Example: RioCan’s price can be obtained through ‘GoogleFinance(“REI.UN”, “price”).
- You can also obtain exchange rate is a much simpler manner through ‘=GoogleFinance(“CURRENCY:USDCAD”)’.
- The GoogleFinance function can obtain price of Canadian mutual funds when you prefix the mutual fund code with MUTF_CA. For instance, the price for TD Canadian Index e-Series can be obtained with =GoogleFinance(“MUTF_CA:TDB900”, “price”)
- You can also obtain mutual fund price information in a slightly round-about manner by processing the Morningstar.ca pages. For instance, the price for TDB900 can be obtained through ‘Index(ImportHTML(“https://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=5428″,”table”,15),1,3)’.
