FiveCentNickel in the US recently took a look at how to track your portfolio in Google Docs, so I thought I’d have a look and see if it worked for UK shares and funds.
Google Docs is an online alternative to spreadsheet software such as Microsoft Excel or OpenOffice Calc, and one of the most useful features are the built-in functions which allow you to pull in data from other Google sites or around the web.
To achieve this particular task of monitoring the value of shares and funds, the GoogleFinance function is the one we’ll use within a new Google Spreadsheet. Here’s Google’s explanation of how to use the function:
You can get stock market quotes and other data updated to your spreadsheets by using the GoogleFinance function. To use the GoogleFinance function, enter the following formula in a spreadsheet cell:
Syntax: =GoogleFinance("symbol"; "attribute");
whereÂ"symbol"represents the stock symbol of the company or mutual fund you’re looking for (like GOOG, QQQ, XXXXX, and so on), andÂ"attribute"represents the type of market data that you want (like price, volume, and so on). If the attribute is left blank, price is returned by default.
I’ve put together a quick sample spreadsheet so you can see this in action and to test how well this performs for UK-based shares and unit trust funds.
How to monitor your own portfolio in Google Docs
First you’ll need a Google Account, and once registered, open a new Spreadsheet from within Google Docs.
Many of the functions that you get in desktop spreadsheet software are available in Google Docs. For the GoogleFinance function to work, you need to find a unique reference code for each share or fund in your portfolio . To do this, search for its name in Google Finance:
When you click on the correct item in the list, you’ll be taken to its data summary page. As well as showing the price, performance charts and other useful data, what we’re looking for here is its unique code, so that we can add it to our spreadsheet. This should now be shown in the search box:
Once you’ve got the code, you can use it in the GoogleFinance function within your Google Doc Spreadsheet:
Although we only require the price to value our portfolio with this simple spreadsheet, there are other attributes that you can get if you want to do some more in depth analysis, such as volume of shares traded, high and low prices. You can read more on the GoogleFinance function page on the Google Docs support site.
You’ll need another formula to multiply the price by the number of units held (which you’ll need to manually update when they change to keep your valuations as accurate as possible)  to get the value of each holding; I’ve also divided this by 100 and formatted the number to give a nicely presented value in £ where applicable, rather than pence which is what the share prices tend to be displayed in. This seems to vary between shares/funds, so keep an eye on this to make sure you get the correct value.
That gives you the basic steps to getting the automatic price updates working, but the are many other options, such as adding extra data and formatting the styles, that you could use to get the portfolio showing exactly as you’d like to see it.
The default security setting is to keep this spreadsheet private, so that only you can see it, and need your Google username and password to access it. In most cases you’ll want to keep the security set at that, especially if you include sensitive data in there, such as plan numbers.
But if for example, you wanted to share the spreadsheet with a group of friends (or, for example, a share club), then you can allow select others to see and/or edit it, or indeed if you really want to, you can open it up to the whole world by making it public and show them hat a great trader you are.
Whilst the Google Finance link copes with individual shares traded on the London Stock Exchange pretty well, there seems to be a few issues with the prices of some of the funds within their database. Whilst a search for a fund, such as Jupiter Income Trust, returns a result, the price is showing as zero; useless for our overall valuation. I’m unsure whether this is a temporary or long-standing problem.
It’s worth mentioning that it is possible to track your portfolio directly in Google Finance without importing the data to Google Docs – we’ll look at how to do this in a future post.
This method has the advantage of being easier to add shares/funds and having easy access to charting and other data, such as adding transaction history, but the spreadsheet method above allows for greater control on how the portfolio is displayed and a more fuller view of your wealth, as you could add other assets such as a house, cash holdings or an expensive painting (we all have those in our portfolio, don’t we?). You may also want to split it up into shares/funds, by tax year etc, add how much you invested to show a profit and loss, flexibility that the Google Docs method allows you.