How to track one's portfolio effectively?

Q&A: Questions & Answers Portfolio Q&A
21

I am still trying to input all my numbers which i have in google finance now. Here are things i was looking for :

1). Can we get the sensex value in 3rd col. based on the date input in 2nd column via google formula ?

2). I see we have 2 sections, 1 for stocks which one holds now and another for which are already sold. I was thinking how to deal with the situation, where we part sell a stock, and then add again ?

Ideally I would like to even retain my past sell transactions. Any ideas ?

22

Hi Raj,

1).

I have not yet tried it, as I try to enter manually the SENSEX value at the time of purchase of the stock. If you fetch the historical SENSEX value from the date, then you will only get the closing value of the SENSEX.

But if you are still interested in it, try the following.

Using GoogleFinance to show historical data
The GoogleFinance function also allows you to easily show historical stock data, allowing you to track the performance of any stock across a certain time period. To show historical data, type=GoogleFinance(“symbol”, “attribute”, “start_date”, “num_days|end_date”, “interval”)into a spreadsheet cell. Youâll find details about the syntax of this formula below:

  • âSymbol" and âattributeâ follow the same rules as above
  • âStart dateâ is the day youâd like to start showing information from
  • “num_days” | “end_date” [Optional] can be either the end date for the time period over which you want to see historical data, or the number of days from the start date. Any number less than 50 is considered to be num_days. Otherwise it is considered an end_date.
  • “interval” specifies granularity at which the stock data is shown, either daily or weekly. You can enter either âDailyâ or â1â if you would like daily information and either âWeeklyâ or â7â for weekly data.

Further, there are lot more possibilities opened because of lots of available attributes. Play with them.

Here are a few more examples of attributes you can access using the GoogleFinance function:

  • price: market price of the stock.
  • priceopen: the opening price of the stock for the current day.
  • high: the highest price the stock traded for the current day.
  • low: the lowest price the stock traded for the current day.
  • volume: number of shares traded of this stock for the current day.
  • marketcap: the market cap of the stock.
  • tradetime: the last time the stock traded.
  • datadelay: the delay in the data presented for this stock using the googleFinance function.
  • volumeavg: the average volume for this stock.
  • pe: the Price-to-Earnings ratio for this stock.
  • eps: the earnings-per-share for this stock.
  • high52: the 52-week high for this stock.
  • low52: the 52-week low for this stock.
  • change: the change in the price of this stock since yesterday’s market close.
  • beta: the beta value of this stock.
  • changepct: the percentage change in the price of this stock since yesterday’s close.
  • closeyest: yesterday’s closing price of this stock.
  • shares: the number of shares outstanding of this stock.
  • currency: the currency in which this stock is traded.

2).

The only option here is enter the sold quantities in the second part. And once you add again, then make a new entry (record) in the first part.

23

Raj,

There is another function to get the SENSEX closing value ona given date. Try the following…

=INDEX(GoogleFinance(“sensex”, “close”,B3) ;2;2 )

This will return the closing value of sensex on the date, which is entered in the Cell B2.

Hope it helps.

24

If haven’t done already, Please check out free portfolio manager at http://morningstar.in/. I earlier checked portfolio tracker of moneycontrol, icicidirect, google finance, yahoo finance, rediff etc. but portfolio manager at morningstar is though bit clumpy but by far most comprehensive and efficient one I have experienced.

25

Abhishek, Thanks a lot for posting this information. I had a ‘complex’ excel sheet, which looked like a trade book to calculate the CAGR. Liked your idea of NAV and converted my excel sheet to NAV based one.

26

I follow this NAV based approach since past 5 years, and managing this is very easy. One can give this a try.

http://prdntinvestor.blogspot.in/2012/10/measuring-portfolio-performance-nav.html

1 Like
27

@Manish_Garg)- Thank you for sharing an excellent piece of work. I am looking at it and thinking of adopting it.

@Raj_Panda - I like your idea of getting the sensex value based on date, let us know if you could get it to work.

I have a feeling that if we put our collective effort into it, this can be useful to many valuepickrs. Like one of the misses pointed out by Raj, can there be a way to show one’s portfolio gains including the realized gains? I think if there is another sheet there is picking up values from both sections to manipulate, it may be possible. Another approach in my opinion would be keep one long transaction history with buys and sells. The sell rows should fetching current values but should freeze at the sold price. I’d like to know your views…

28

the second approach could be a modification to the price formula to add an IF statement. IF the column buy/sell is “SELL”, then get the value of a field ie last price or else get the present value using=GoogleFinance(“MANGLMCEM”; “price”)

Do I sound crazy?

29

Since yesterday evening i have been exploring thehttp://www.mprofit.in/download-investor.php(Investor) - free version. It’s free for retail investor if portfolio is less than 50 lac.

Must say am pretty impressed with the ease with which i could import all my past transaction into this software. Basically just need to keep all the contract notes (PDFs) in one place and then use the import function.

I have used different demat accounts over last 3 years and never thought it would be so easy to get them all in one place and see the performance over this time period. The software provides a XIRR function for all the portfolio’s put together. But i think we need to manually add each dividend received. It supports the functionality to add split and bonus data.

Overall i am very impressed and would recommend.

30

Raj, mprofit need a windows machine, unfortunately I don’t use windows so I think I will have to skip that. Hope they come up with a linux/android version soon.

31

Raj,

I tried importing data from icici-direct. I need to manually map each and every script, which is a big pain. Again you need to manually add bonus/split/dividends, another big pain. I feel moneycontrol do a better job as compared to mprofit.

I need a SW which does little bit more intelligent processing for me like smart reporting with roe/roce/sales/np growth number for all of my stocks in a smart manner. Think, I feel I need to write the software myself one day :slight_smile:

32

Hi

I found edelweiss provides different portfolio views in terms of management effectiveness, valuation, margins, growth etc. It was useful. Also they have good snapshot of how portfolio is performing against the input cost. Recently they even added the capability for adjustments for bonus/split. Also they have different capabilities like annualized returns, dividend received, taxation view etc.

Thanks and Regards

Sudheendra

33

@Subash, manually mapping of script is one time for each script transacted which i think is ok, given the lack of uniformity in code between NSE/BSE etc. change of codes (company name which keep happening) like Piramal Healthcare is now Piramal Enterprise etc…

For split,bonus,dividend yah… i was hoping it will do on it’s own. But even google finance hasn’t proved reliable to me on that front.

I think mprofit has the taxation and many other reports option, am still exploring :slight_smile:

34

After doing all the work to make your PF work for you with mprofit, there are chances that they make it paid or bring down the PF value to 25L etc. I am for making a google sheet work for us. Adding one txn when you come to know about splits and dividents shouldn’t be big deal, we are not that passive are we?

35

@subash_nayak, do you think we can take out the money control data and just add the portfolio returns part manually? If that is possible, we just need to export the data once a year and then us a summary sheet to calculate and show the CAGRs.

Did you say you use a combination of moneycontrol + excel to get your PF view in reply to my original post or was it someone else?

36

@Subash_Nayak, I was right, you got this right long ago, would you like to share your sheet?

37

I think mprofit is worth a look at this stage.

In particular i liked the following features:

1). The import function is very strong supporting multiple brokerage houses with diff format like pdf,excel,html etc… which means, don’t have to worry to enter all details like stamp duty,STT,brokerage etc… and importing all the contract notes once in a month or so using few clicks is all i need to do. Accepts contact notes password.

2). Has support for adding most of the corporate actions like meger, demrger,bonus,split, company name change etc…

3). The report section is very strong, can see the %tage allocation to stocks and many other things. It’s big section with many options and need time to explore.

4). Can see the short/long terms gains/losses , even on quarterly basis which is important for advance tax payment (when applicable).

5). Supports calculation of tax implication for a considered sell position.

Well, all this can be done in excel. But will take time & effort ?

Their license is around 2k annually which will be applicable if the portfolio is >50 lacs.

I think this concern is genuine - if they reduce this portfolio limit sometime in future. Then we have to pay the fees.

Personally i am looking for a reliable tool to handle all the above features, willing to pay a reasonable amount for peace of mind.

1 Like
38

Got a phone call from Mprofit guys, asking about my experience with their software. Pleasantly surprised :slight_smile:

39

Raj, All looks excellent and I’d like to give it a try but don’t want to pay the windows license cost to just install mprofit. I will think if there is any way I can get this sorted to have a windows box.

40

This may have already been discussed, but has anyone figured out how to download CMPs directly into Excel? This would save me a lot of time tracking my portfolio.

Some observations if one doesn’t mind:

Excel has an XIRR function that can calculate the stock’s CAGR and is easily usable. =XIRR is the function if anyone wishes to try.

Also using Excel’s pivot function gives you a lot of flexibilty in manipulating the data into a form that you would like it to be in. You essentially only need one data tab (similar to how Screener.in does it) with which you can pull data off.