Google Spreadsheet for Tracking Portfolio

I will start with thanking @Prdnt_investor as i was inspired by his VP public portfolio tracking spreadsheet that he created and shared in the VP portfolio thread. I also learned calculating NAV from his blog. I also have to credit http://www.investmentmoats.com. I have taken his portfolio tracking spreadsheet as baseline to built this. I have customized on top of his spreadsheet.

The spreadsheet is built to track my portfolio performance in the way i like. The spreadsheet has provisions for adding buy/sell transactions, corporate actions like dividends and splits. you can enter the cash flow movements in and out of your portfolio. The spreadsheet will summarize the performance and calculates CAGR for each stock as well as portfolio. Nav calculation is also built in along with CAGR calculation. you can take a snapshot of Nav every quarter.

You can see the spreadsheet in action by making a copy to your google account from the below link.

light yellow cells are where you need to put in your inputs and light blue cells are auto calculated.

The transactions listed in the spreadsheet are not my actual transactions. While the stocks are actual stocks in my portfolio i took pains to modify the quantities and cash amounts.

I would be happy if this is of any help to the folks in the VP community.

14 Likes

A preview of the spreadsheet is being shown instead of the link. If you want to copy the spreadsheet to your account you can add https:// at the start to the below link and make a copy of the sheet to your account.

Thanks @pidugu for sharing this sheet. I see there in sheet REF there is section for price alert. Can you please throw some light on this …how does this work…How do you get alert when your expected price is triggered

Thanks
Sunny

How can i download this sheet?

You just need to create/clone a copy. It would be save in your google driver

Ref sheet is only for Specifying the different transaction types(buy,sell,div,split,fee) and portfolio types( you can choose to have your own here). Ignore the price alert tolerance. I am not using it but what you asked can be done. you can google for it. You will have to put notifications on cells and write a script to open the spreadsheet every hour or so. this will update the sheet and the notifications on cells get triggered for you to receive alerts.

1 Like

How can we calculate the unit and nav? Do we need to add only transactions and cash flow sheets or we need more changes.

Please read the below blogpost on understanding the nav calculation.

If you want to start nav calculation starting today you can take nav as 10 and calculate units based on that.

If you want to calculate historic nav it will take a little effort. if you notice in the cash flow sheet i am calculating units based on the nav on that day. so you have to add all transactions till that day, calculate the PF value on that day based on prices on that day and then use the nav to put the units.

Some folks are requesting write access to this spreadsheet. You don’t need write access to this sheet. You would have to make a copy of this sheet to your google account. File -> make a copy and edit your copy of the spreadsheet.

1 Like

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

5 Likes

First of all, I would like to sincerely thank Harsha Devulapalli and Sharmi, who have done the fantastic job in coming up with this google sheet.

I have made slight modification and it is working very well. I am facing a challenge with one particular formula in the sheet - relating to CAGR calculation. As per my sheet, CAGR calculation is only throwing up 0% and is not calculating the actual CAGR. I have attached a link to my amended google sheet.

I would highly appreciate, if you can guide me as to what error I am making in the CAGR calculation (Column W) in Holdings sheet.

Thanking in advance.

Google sheet link - my googlesheet link

Regards

I am not able to see the sheet fully with view access. But in general I have noticed that in the transactions sheet the date is not set as date(probably its text data type). Try changing it.

Dear Sharmi
I checked the format of date column (Column A) in ‘Transactions 2’ sheet. Its format is set as ‘date’. I redefined the format as ‘date’ by selecting the range and then using the menu (Format -> Number -> date). Still the same error persists.

I am reattaching a new link to this message, as you had faced challenge in accessing the file. Thank you in advance.

Regards https://docs.google.com/spreadsheets/d/1eTzQhBVDBmEanI8FGsIWNV0hKM8x8y6W3atLcfSGBJs/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1eTzQhBVDBmEanI8FGsIWNV0hKM8x8y6W3atLcfSGBJs/edit?usp=sharing

@pidugu, @sharmi
Thanks for Excels. I have a question with ref to https://docs.google.com/spreadsheets/d/1x9x6U1L3_fv-i8vHsDh2x_qc7swr9aNS6qZiksHWKMk/edit?usp=sharing
Shouldn’t the “Amount” column in “Cash Register” tab and “cash flow” column in “transactions” tab match ? The amount invested on 6/9/2014 (MM/DD/YYYY) as per “transactions” is 66,076 (row2,3,4) where as in “cash flow” it is shown as 1L (row 1). Am I missing something here ?

Amount is what you bought to your bank account to allocate to equities…

Cash Flow is what you used. If you sell you will add to cash and if you buy you will eat from cash…

you dont use all your cash everytime for both to match. cash is also a position if you notice my spreadsheet.

Amount in cash register is how much money you got into your bank account
for allocating to equities where as cash flow is how much of it you used.

you might have bought 2 lakhs but only used 1 lakh of it. 1 lakh will be
your cash. next time you might get only 50K and buy 1.5L. now your
remaining cash is 50K etc…

hi sharmi, i am at loss in figuring out how to use your spreadsheet. please explain in slight detail. you can send an email on vinit77@gmail.com

1 Like

Dear Team,

I am new to VP. I have read the post.
@csteja, @pidugu, @sharmi
if you guys send me the link for the latest file, it would be greatful.

Thanks & regards,
Tejas Doshi

Helpful if the details are also posted here… @sharmi Thanks in advance!

1 Like

CAGR column in the shared sheet shows 0%. Can someone please help?