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(“http://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:

  1. 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”).
  2. You can also obtain exchange rate is a much simpler manner through ‘=GoogleFinance(“CURRENCY:USDCAD”)’.
  3. 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”)
  4. 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(“http://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=5428″,”table”,15),1,3)’.

This article has 39 comments

  1. This is great. I didn’t know that there was a GoogleFinance function.

    I will use this to keep track of my investments from now on!

  2. This post was perfect timing for me. I’m in the process of making major changes to my portfolio, and I wanted to create a spreadsheet of my allocations that would update conveniently with recent asset prices. I didn’t know that Google docs had this built in. Thanks.

  3. Every time you report on things like this, makes me yearn for a simpler portfolio.

    My wife’s is similar with 9-10 holdings, but tracking a tonne of stocks, man… I haven’t come to the conclusion that the actual returns warrant the change though.

  4. Your post triggered my search for the equivalent extension for OpenOffice Calc. I found it and implemented it! The extension is called Get Stock Price and the function is ”=getprice(xiu.to)” for example with the xiu ticker. It takes the data from yahoo finance so write the tickers like you would find them in yahoo finance. Here is the link: http://extensions.services.openoffice.org/fr/project/getStockPrice.
    Works flawlessly for now. Thanks.

  5. Even though, I’m pretty happy with Quicken, I was planning to use something else for reporting my allocation.

    Thanks.

  6. Canadian Capitalist

    @Michael: I believe Excel has a stock price function as well but that’s something I haven’t explored.

    @Sampson: Though I use this to track a passive portfolio, it can be modified to work for portfolios with more components. The Group RRSP at work is invested in pooled funds and there is no public pricing information. I simply update the value manually every quarter or so and add it up with our XIU holdings to obtain the Canadian stock allocation.

    I see your point though. Simplicity is a major factor with my choice of going passive.

    @epicrider: That sounds cool. I’ll add the OpenOffice function to this post.

    @Daniel, @SS Delight: You are welcome. I’m posting on tracking dividends with Google Docs tomorrow.

  7. Exactly what I’m looking for. Thanks.

  8. Works great, but I can’t find a way to get a quote for a symbol that has a ‘.’ in it that is also on the TSX. For example, BBD.B (Bombardier B), which I’ve tried as “BBDB.TO”, “BBD.B.TO”, “BBD-B.TO” and a few other permutations. Same is true for unit trust quotes, which typically have .UN appended to the symbol. Anyone know the syntax for specifying these?

    • Canadian Capitalist

      @Sandy: Try
      =GoogleFinance(“BBD.B”, “price”) for BBD.B

      For income trusts, such as RioCan, this should work:
      =GoogleFinance(“REI.UN”, “price”)

  9. Thanks, never thought to try that, as obvious as it seems in retrospect! :)

  10. I use MS Money also. The old version still works but Microsoft has discontinued updates to the product.

    I have a very similar portfolio to the sleepy portfolio except much less foreign exposure.

    International investing is just not worth the hassles in my opinion. (fees, exchange rates, trouble tracking, handling USD accounts, politics, lack of news-I don’t read Chinese, etc).

    Besides there is high concordance with the Canadian markets anyway.

  11. I’ve used a Microsoft Excel spreadsheet to track everything since circa 1997 when I first bought MS Excel 3.0. I’m not completely comfortable with the concept of tracking everything with online tools, from a combination of both the security aspect as well as a data loss aspect.

    Lately, I’ve been contemplating making the switch over to an Apple computer since my Vista machine appears to be on the verge of catastrophic failure and just having being frustrated with the whole Microsoft domain over the past few revisions of the Windows operating system.

    Do any of others out there have any experience with Apple and can comment on their stability and whether we have to use a spreadsheet, or whether there are financial tools in the Apple domain?

  12. Glad to see the color / presentation are changing in the right direction CC. It’s making for an easier read so far.

    Tangential question re: tracking and stock screening. Anyone notice that The Globe & Mail’s stock screener has had a major makeover? I’m not a fan – as the original was much easier to navigate potential Yes and No candidates. Which screeners are people currently using (which make for a quick / easy analysis of Graham-friendly stocks (low PE / PB and debt levels)? Suggestions welcome.

  13. Canadian Capitalist

    @Randy: I vaguely recall a study by Leith Wheeler that you can obtain all the benefits of diversification with a 30% exposure to foreign stocks. So, it is perfectly reasonable to have some but not too heavy exposure to foreign stocks. You can also add withholding taxes to your list of foreign stock negatives.

    @Phil: I use Google Docs for just the portfolio snapshot. I’m fine with data loss and not very concerned with security because the spreadsheet just lists the securities and # of shares.

    @RMB: This is still a work in progress. I’m working on the code in my spare time, so I have to admit that the progress is slow.

  14. In terms of Excel the function is MSNStockQuote(Tickersymbol,,)

    http://office.microsoft.com/en-ca/excel/HA010346101033.aspx

  15. I have a very similar, slightly more complicated spreadsheet set up in Google Docs as well and it works great! I keep track of the MER, distributions and XIRR as well.

    BTW, there is a built-in function for the currency conversion that’s a little easier than processing Yahoo Finance: =GoogleFinance(“CURRENCY:USDCAD”)

  16. Thanks alot!

    You can get the TD e-Series prices too by processing morningstar.ca pages:

    For TDB902, use ‘Index(ImportHTML(“http://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=5430″,”table”,15),1,3)’,

    For TDB900, use ‘Index(ImportHTML(“http://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=5428″,”table”,15),1,3)’,

    For TDB911, use ‘Index(ImportHTML(“http://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=7012″,”table”,15),1,3)’,

    For TDB909, use ‘Index(ImportHTML(“http://www.morningstar.ca/globalhome/quicktakes/fund_overview.asp?fundid=7008″,”table”,15),1,3)’

  17. Alaa:

    Are those formulas for excel? I didn’t get it to work. Kindly advise.

  18. The formulas are for the spreadsheet in Google Docs. Same as the ones in the article.

  19. I see.

    In Google Docs, I used ‘=GoogleFinance(“MUTF_CA:TDB909”, “price”)’ and it worked like a charm.

    • Canadian Capitalist

      @SS Delight: I’m getting an error for mutual funds if I try with “MUTF_CA:TDB909” or “MUTF_CA:TDB900”.

  20. @Phil: I’m on a Mac and using Quicken 2007 for tracking my finances. It’s pretty good for what it does but the interface is very Windows like. For spreadsheets I use Excel Office 2008 for Mac. You also have other options like the ones mentioned above: OpenOffice or Google Docs.

    As for Mac’s stability, I can say that they are very stable. I’ve had my MAcbook Pro for over 3 years now and it hasn’t slowed down…still runs like it did when I first used it. There are the odd crashes now and then, typically with one application and not the operating system, but in my experience all computers have this problem.

    All in all, I am very happy with my Mac experience. Everything I did on a pc I can do on a Mac. And there are no problems tracking finances using Quicken or any spreadsheet. I say go for it!

  21. Just a note: When copy any formula from the this web page (The GoogleFinance(… or the Index(ImportHTML(… ones) and paste it into Google Docs spreadsheet, you need to fix the double quotation characters.

    Somehow it get changed and you need to overwrite them so the formulas work.

    • Canadian Capitalist

      @Alaa: Funnily, I don’t get an error now. I typed in the =GoogleFinance(“MUTF_CA:TDB900″,”price”) directly without copy-and-paste but I still got an error in the morning but not any longer.

  22. Excellent! But I am wondering for stocks and ETFs, why not just build a Portfolio under Google Finance? It will keep tracks for you automatically.

  23. @Canadian Capitalist: Not sure why you got the error…But glad it’s working now…

  24. Canadian Capitalist

    @DW: Google Finance Portfolio tracker is nice but it is hard to obtain a snapshot of asset allocation. I find Google Docs to be much more flexible.

    @SS Delight: I found out why I got the error. For some reason, I had added ‘;’ at the end. Google Docs flags that as an error.

  25. Is there a way to calculate ROI, gains/loss?

  26. I have my investments with RBC DI– tracking is a pain as there is no good way to track accounts in aggregate (RESP, RRSP, spousal RRSP, business investment account). I use Excel to lump them together to make sure my assess allocation hasn’t drifted too far out of whack. I shell out the $150/year for the Global Investor Gold subscription since I’ve found this gives me the most useful info in terms of ROI, dividends, etc. I’m still looking for the Holy Grail to track my portfolio!

  27. I can’t seem to get the GoogleFinance function to get prices of preferred shares for Canadian banks. For example, the page

    http://www.google.com/finance?q=TSE:E:BNS.PR.J

    gives the price, but no variant of the ticker BNS.PR.J seems to work in the Google Docs spreadsheet GoogleFinance function.

    • Canadian Capitalist

      @Michael: Try processing the TSX page for BNS.PR.J. I found this to get the bid price:

      =Index(ImportHTML(“http://cxa.marketwatch.com/tsx/en/market/quote.aspx?symbol=bns.pr.j”,”table”,6),4,2)

  28. @CC: Worked like a charm. Thanks!

  29. I’m having problems with getting prices in google docs.
    Some times it works some times it doesn’t.
    Example a lot of canadian tickers don’t work either.
    Not very reliable for us canadian users.

  30. I’m trying to set this up and mostly processing Google Finance is working. But… not for CBO (Claymore’s laddered corp bond fund). So I was trying to use Morningstar… but, of course, they’ve redesigned and the formulas above no longer work.

    Because I don’t know how to create them/figure them out, would you be so kind as to create one for me to use as a template? And, the Google Finance link for the dollar exchange has changed to:
    =GoogleFinance(“CURRENCY:USDCAD”,”average”)

    Many thanks

  31. Oh… and… I’d like the spreadsheet to allow allocations that are divisions of percentages, like 5.5%. Is that possible? Many thanks again.

  32. Cancel that… I’ve figured out the subdivided percentages… sorry.

  33. Hi — I’ve been using a spreadsheet in Google Docs based on this one to track my asset allocation for the past several years. It doesn’t seem to be updating the share prices anymore, though…most of the cells just say “Loading” and they never actually update. Is this a known problem and is there a workaround?

    • Hi Brad: I still use Google Docs, except its now Google Sheets. I haven’t noticed any chronic issues. I do see the “loading” issue you mention occasionally but it goes away. I use my Google Sheets across platforms on my MacOS Chrome, Windows Chrome, iOS and Android Sheets Apps. All of them work.

  34. This looks great at first glance, but I have tried it, and notice that it does not combine the names of a single asset as one type; they all remain separate, even with the same name. EG. I have 3 stocks titled “Canadian Stock” , so they show up 3 times on the graph.

    MS Money used to do that, nicely.

    Any ideas how to make them combine and appear as one unit? Or is that beyond the capabilities of this program?

Leave a comment

Your email address will not be published. Required fields are marked *

*