Portfolio Value Calculation - Using Excel Import External Data

To calculate the portfolio value which consists of number of stocks, unit trust and others derivatives, and it could be in different currency, Excel provides the easiest and faster way to perform the job in less than a minute. It is done by linking the spreadsheet to external internet data, e.g. Yahoo Finance (currency, stock price), Investment-linked website, unit trust price list etc.

Step by step:
0. Create a Data Sheet. Then, tabulate the number of your unit trust or share or currency as per table below.

1. a) Excel 2003: Select a cell. On the Menu Bar, select Data - Import External Data.

1. b) Excel 2007: Select a cell. On the Menu Bar, select Data - From Web.

2. This will open the "New Web Query" dialog box. Your web page will be displayed. In the address block, manually type in e.g. http://finance.yahoo.com/q?s=5066.kl, and click the "Go" button.

3. a) Just as if you were searching for a quote on Yahoo!® Finance, type in the ticker symbol for the stock you want a quote on. This will be the ticker symbol quote that will be imported into MS Excel. On the dialog box, scroll down to the "Last Trade:" and check the check box that highlights "Last Trade", "Time", "Change", etc. Then at the bottom/right of the dialog box, click "Import". Another "Import Data" dialog box will appear, asking where you want to put the data.

3. b) If you want to extract data from a unit trust website, said Pruaccess,http://pruaccess.prudential.com.sg/prulinkfund/viewFundPricing.do. Then the result will be imported to spreadsheet as below.

4. Finally you may link the desired data to the portfolio calculation table.
5. For updating your portfolio you just need to click on the Refresh button.
  
Advance:
It can be used to calculate your net worth by compiling the subtraction of asset to the liability. By using this Excel template, daily calculation on the net worth can be done within a few minutes only.