Portfolio management in spreadsheets

Following the post on portfolio construction I received a question from someone about how to setup a worksheet to obtain ‘real time’ values for shares and managed funds.

It’s quite straight forward as it’s a standard function in Excel. What you need to do is go to the Data menu in Excel and choose Import External Data, then New Web Query




In the box that appears navigate your way to whatever website you want to use e.g. ASX for share prices, or say Platinum’s managed fund prices

Let’s say we’re going to the ASX:



Enter in your stock codes in then once you get there tick whichever of the yellow boxes contains the information you want

Once you import the data you can set options around when it refreshes and so on so it’s reasonably flexible. The downside is that a lot of the data pulled down by some of the queries is more than you need – I put my ASX data onto a separate sheet and then use VLOOKUP formulas on my portfolio sheet to find what I want.

It’s obviously easier than entering numbers in manually and having to navigate to several different fund manager websites. Although my margin lender provides online portfolio access the managed fund prices are usually well out of date and I also have shares that aren’t in the margin loan portfolio.

Thanks again for your feedback, keep it coming!