Creating a Portfolio Tracker

Here is a simple way to create an automated, simple and real-time stock portfolio tracker on the cloud, using Google Spreadsheet.

Have a look at this spreadsheet. It has two worksheets - Summary sheet gives an overview of the portfolio and Data sheet is used to obtain real time data.

Let us take a look at the Data sheet first. It pulls data from Sharekhan's website for stock updates (it could be any similar site). For example let us look at Reliance Industries (RIL) in A11. The data corresponding to RIL is updated from Sharekhan's website in cells B11:C19. This done by using a Google Spreadsheet function - ImportHtml(). If you double click on cell B11, you will notice that it has a formula =ImportHtml("http://www.sharekhan.com/News/CompanyBasicQuote.aspx?sskicode=RIL", "table",9). What it does is that it extracts data from table 9 of the URL and puts it in the sheet starting from B11. For L&T and Airtel, i have used the same formula by replacing the sskicode in the URL with corresponding codes from Sharekhan's website. The data updates happen at regular intervals. Once we have the data, it is utilized in the Summary sheet.

In the Summary sheet, only the highlighted cells (Company, Shares, Investment) need to be entered manually. CMP and % Change corresponds to values from the Data sheet (C11 and C15 in the case of RIL). The rest of the values (Current Value, Profit, Profit %, Weight) are obtained by simple mathematical calculations (double click the cells to check the formula).

The last updated date and time is also from the Data sheet, formatted to show date and time separately. I have also added a couple of charts to get a nice visual representation of the portfolio. (Go to: Insert -> Chart)

Please bear in mind that the accuracy of the data and frequency of updates would depend on the website chosen for data updates. There are numerous portfolio trackers available in the internet (Moneycontrol, Yahoo, Google, Rediff, etc.), but this tracker has an added advantage that it can be customized as per one's on liking. Similar sheets can also be created for tracking mutual funds.