Tracking Investment Portfolio Using Google Docs

I spent a little time tinkering with Google Docs Spreadsheets today, trying to use it to track the asset allocation of my investment portfolio.

GoogleFinance() Function
This function allows you to import data from Google Finance like current stock prices, p/e ratios, or performance. Here is the Google Docs Help page. For example, if you want to pull up the price of a share of Google stock, you’d enter this into a cell.

=GoogleFinance("GOOG"; "price")

Importing Personal Data
I could not find an easy way to import my actual holdings into Google Docs, but I’m not sure if you want that capability due to privacy concerns. Google Docs does allow you to import from RSS or XML feeds. I ended up just manually entering the ticker symbols and number of shares I held for each mutual fund. This means I will have to update my 401(k) funds after each paycheck, and my other holdings when I make a transaction or when a dividend is paid.

Shared Example
Below is a shortened version of my online file, as a quick example.

You can view the full version more easily here. To edit, go to File and click on “Create a copy…”. You can then poke around and change the ticker symbols to your own.

In the full version, I have columns that compare my current asset allocation percentages to my target percentages. This can help people who wish to rebalance when their allocations are off by a certain amount. I can see that I am currently overweight in Emerging Markets due to their recent run-up, and underweight in TIPS. Everything else seems close enough.

The pie charting function seems a little buggy, I couldn’t get it to show the proper labels.

Comments

  1. I personally like CNN.Money’s portfolio set up, it is pretty descriptive and has a pie chart showing your holdings and I also like Smartmoney.com’s portfolio views. From what it sounds like, this GOOG sreadsheet sounds like a hassle to get the same info as on Money.com.

  2. Thanks! I’m going to see if I can set up an evaluation tool for my 401K funds thanks to the historical data function…

  3. I wonder how the GoogleFinance function compares to the MSN Money Stock Quotes plugin for Excel. The “Google Docs Help Page” link you posted doesn’t seem to work.

  4. Probably works about the same, though this is free and online. The Help page link works for me, anyone else can’t see it?

  5. Ah, I think it’s because docs.google.com is blocked at work. Thx.

  6. Yes, the historical data feature is neat. I thought it would be handy to show performance YTD, but I don’t know how to account for dividends. Although now that I think about it again, if dividends are reinvested, it should work.

  7. @Bill – Yes, if you’re just looking to track values, those are fine. This is more geared towards showing asset allocation percentages, as that’s my primary criteria to make any changes. (Or some other type of customization.)

  8. mapgirl says:

    Argh. I wish there was some product out there that added in dividends for you. I would flock to it!

  9. mapgirl – yahoo historical prices include dividend adjustments. Also, if you chart a mutual fund on Morningstar, it will do “growth of $10000″ which includes dividends. If you “compare” a stock to a mutual fund on the mutual fund page, it will do the same thing for a stock. (For some reason, if you just chart a stock, it just does share price, though. Weird.) This is in the “Chart beta” section of Morningstar.

  10. I think what mapgirl wants is for, say, a Google Finance portfolio to be automatically updated with additional share purchases based on dividend payments without having to manually do this. Clearly this would have to be an option one selects, as people who do not have dividend reinvestment would not want this feature. But Google Finance should be able to implement this feature easily enough if enough people need it.

    It seems like this is space that the Yodlee portfolio tracker (or other account aggregators) could explore, but they would never offer all the richness you can achieve with your own spreadsheet. I think the real dream would be for Google Finance to start offering a Yodlee-type service — then the monthly 401k contributions would also be handled automatically, not just dividend payments.

    Personally I still use the MSN Excel plugin for asset allocation. I am not motivated to move to Google Docs just for the historical data, but that combined with cloud computing (access from any machine) make it worth considering. If I am correct, the MSN Excel plugin does not work for MSOffice 2007.

  11. Roberta says:

    Thanks, I so look forward to your email everyday.

  12. I’ve been following the Bogleheads forum for a while, and decided to make my own software to do it locally. It is open-source and runs on Windows, getting it’s data from Yahoo! Finance. The project page is here: http://code.google.com/p/mypersonalindex/

    The Bogleheads dicussion is here: http://www.bogleheads.org/forum/viewtopic.php?t=18086

    It stores historical data and can provide a snapshot between any date periods. You can set up an asset allocation, tax liabilities, check correlations, etc.

  13. Thanks, I so look forward to your email everyday.It seems like this is space that the Yodlee portfolio tracker (or other account aggregators) could explore, but they would never offer all the richness you can achieve with your own spreadsheet.I think what mapgirl wants is for, say, a Google Finance portfolio to be automatically updated with additional share purchases based on dividend payments without having to manually do this. Clearly this would have to be an option one selects, as people who do not have dividend reinvestment would not want this feature.

  14. This seems cool ! But still it’s not the best way of managing our portfolio.. good start from Google!!

  15. The stock quotes feature is helpful when importing to a spreadsheet, but I trade options. Google finance does’nt offer options quotes. I am looking for a way to import quotes so they are updated each time I log into my spreadsheet. Has any one done this? I would need to pull the quotes from cboe or options express website. Yahoo finace has them too but I can find them on google.

Trackbacks

  1. Will Dearman Lifestream » Daily Digest for July 11th, 2009 says:

    [...] Tracking Investment Portfolio Using Google Docs [...]

Speak Your Mind

*