Keeping Track of Your Portfolio

If you don't know where you are, financially speaking, it is difficult to make sensible choices. You need to keep an up-to-date idea of your expenditure, your income and the performance of your savings and investments.

Sooner or later you will want to develop tools to keep track of your portfolio of savings and investments, including purchase price and date, yield and - the most important bit of information - how much income they are producing.

A spreadsheet is the easiest tool for this: the main options are the ubiquitous (but paid-for) Excel or the free OpenOffice Calc (which I have not used). Even if you are a bit new to spreadsheets, it will be worth the effort to build your own customised version. As all portfolios are different, so it stands to reason that the spreadsheets that track them will vary (although there will be common features, obviously).

For those of you that are comfortable with spreadsheets there is almost no limit to the data you can skim - free of charge - from the Internet.  A couple of articles in the Motley Fool (here and here) give some pointers on how to skim data from financial websites, either by using Google documents or directly from websites.

My own spreadsheet consists of a number of sheets, including:

• data download
• summary of the portfolio of quoted securities (shares, gilts, bonds)
• data on cash investments (cash bonds, 'easy access' accounts, current account)
• summary of all income 
• separate sheets for each gilt/commercial bonds, as these are a bit more complicated to keep track of
• a 'trend' summary sheet that picks off totals from the other sheets and which I periodically save to keep a history of my investing
• a graph (based on the 'trend' sheet) showing the performance of the various investment accounts/types over time
• a 'moving average' graph that shows if I'm making or losing money over the medium term
• a 'wealth' sheet summarising all assets, including house, cash, pensions, savings, investments, etc.
• a 'history' sheet showing details of all purchases and sales
• a sheet of links and passwords

On my 'download' sheet I have data for each company (using the Excel command Get External Data, locating the relevant web page and and ticking the data group that I want - you have to do it to understand what I'm talking about). For example, for CWW (Cable & Wireless Worldwide) I collect data from the Yahoo page  - similarly data for all the other holdings are included further down the sheet. When this is all set up I download data from Yahoo into my portfolio spreadsheet using Excel by pressing one button on my 'ribbon' (or the command 'update all data').

I then set up links in the spreadsheet to copy (automatically) relevant data from this 'download' page to my main summary sheet that calculates the total value of my portfolio of quoted securities, including shares, gilts and corporate bonds.

The other sheets are also interlinked, to minimise the amount of data entry I need to do.
 
There can be problems, particularly when the target websites change their designs, which can throw the data collection process completely. This usually means you have to edit your spreadsheet. However, as my spreadsheet is quite complicated anyway (e.g. calculating income, etc), I find I am editing it quite often anyway. Buying a new security means I have to set up the data download and link it to the rest of the spreadsheet.

I find getting values for commercial bonds is a little more difficult, as I haven't found a way of downloading data directly from Bondscape and I don't know of any other sources.  So I do this manually, as I do the details for cash and cash savings.

It has taken me quite a while to get the spreadsheet the way I like it - but I will probably keep trying to improve it!

Good luck with your spreadsheet.



I am not a financial advisor and the information provided does not constitute financial advice. You should always do your own research on top of what you learn here to ensure that it's right for your specific circumstances.