Thanks Harsha. After checking with you on the other thread, I started tracking my portfolio in it. I also added some more features based on my need. Thought I will contribute back, from where I got this sheet. I can also try adding any more interesting features if there is any request.
Here is what I have added.
- Added charts for different category/classification.
- Added a standard brokerage calculation. This might need more improvement if you have multiple brokers.
- Noticed that the date format in transaction sheet was in US format which caused flux in CAGR calculation. Fixed it by going to File->Spreadsheet Settings->locale
- Changed cash calculation automatically.
- Moved the results in holdings page to a separate tab called history and it gets recorded automatically every day which will allow tracking at a later stage. Again this might need more tweaking.
How to use it:
- Update the transactions sheet(area in yellow) with actuals.
- Make sure “code” column is correct in both transactions and holdings. The sheet uses google finance so better check if the code returns the right company. I noticed that including “NSE:” or “BSE:” before the symbol gives correct result. In my case for ITC it gave me a US company with the same code. So be specific to make it accurate.
- If the price is still not accurate use the “Man. price”, which is manual price, to enter right price.
- Update the brokerage charge in “ref” sheet as percentage of transaction value. This will update brokerage in the transaction calculations.
- Update the holdings sheet with stock name and code which you are currently holding.
- Update the “Past holdings” sheet with stocks which you have already exited.
- Update “Cash register” sheet with cash flow details.
- If required add items to watch list for stocks which you would like to monitor.
- Now you are all set to track your portfolio.
Here is the link