Sunday, November 29, 2009

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.

6 comments:

Srinivas Girigowda said...

great tip

Ganesh said...

Thanks Srinivas!

Ajay Gupta said...

Thanks for this useful tip.

Ganesh said...

Thanks Ajay!!

SHRISTOCKTIPS said...

Share Market Investment made profitable by SHRISTOCKTIPS- Get NSE/BSE Tips via SMS and Yahoo Messenger. We give daily stock market tips for future and cash segments. As we said to make sell position at a higher level of 5450-5500 who sold, made a very handsome profit in NIFTY. Now again we proved that whatever the marketSHARE MARKET, STOCK TIPSwe will get very handsome profit. Now for the coming week traders can make a buy position in NIFTY around 5500-5550 for the target of 5750-5850 with keeping the stoploss of 5450. Because now Govt. is trying to make some arrangements to make the rupee stronger. So our strategy is to buy all NIFTY 50 stocks with the stoploss of 5450 in NIFTY. Last Thursday & Friday there is not any kind of sell signal in the market. This shows there is strong buying at the lower level. So keep a plus position and book profit when you think it is up to your expectation. To get our trial you can visit our website.
Regards
SHRISTOCKTIPS TEAM

Farhan Khan said...

When you do a technical analysis then genuine tips are generated which are very profitable and helps you to trade in a correct way and try Stock Tips for more profit.