SubscribeTrack Your Mutual Fund Portfolio using Excel [India Only]
Excel is very good for keeping track of your investments. Due to its grid nature, you can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net Asset Values) to see how your investments are doing. A while back we have posted a file on tracking mutual funds using excel. Today we are going to release an upgrade for that file.
Download the mutual fund portfolio tracker excel workbook now.
[Download Excel 2003 compatible version here]
How the Mutual Fund Portfolio Tracker Works?
- We use Excel Web Queries (a powerful external data feature in excel) to get latest Mutual Fund NAVs for all the MFs in India. The list of funds along with their latest NAVs is published everyday at AMFI (The Association of Mutual Funds in India) at http://amfiindia.com/spages/NAV0.txt
- The data is delimited using ; as a separator, I have used some formulas (mainly FIND, MID and LEFT formulas) to split the text in to fund name and latest NAV.
- I have used fuzzyText UDF (user defined formula) so that we can search against this list even when you have a spelling mistake in the fund name. For more information see fuzzy text search using excel.
- In the main portfolio sheet, as soon as you type a fund name, we search against the list to see if any fund matches the one you bought. At this point, we use the fuzzyText UDF so that you can spell in anyway you want (as long as it closely matches with the fund name). Once a match is found, we show the latest NAV for that fund in the tracker worksheet. And of course, we use VLOOKUP to find the NAV.
- Rest is easy, you can figure out between sips of coffee.
- The file is protected, but there is no password. So go ahead and poke around it to learn how the whole thing works.
- Even though the file works for Indian Mutual Funds only, you can easily build a similar model for US or UK or Any other country. All you need is a public source of fund data and a little web query.
Changes from previous version
- The formulas are more robust. Earlier version (available here) has some limitations.
Selecting a fund is much more simpler. You need not scroll thru an insanely large in-cell dropdown. Instead, just type the fund name and thanks to fuzzyText UDF, the correct fund name will be found.- I have updated the webquery properties, so that formulas get refreshed automatically.
Download the mutual fund portfolio tracker excel workbook now.
[Download Excel 2003 compatible version here]
What is your favorite way to track investments?
I rely my bank’s investment tracker tools to get a quick update on my mutual funds and shares. But I use excel to pull data from various sources and analyze it to optimize my portfolio. Using excel’s financial formulas, I can easily find out CAGR or IRR on my investments is and compare it with other options. I also compare my future needs against my current holdings to see if I need to invest more.
What about you? What is your favorite way to keep track of investments?
Related Excel Templates and Articles on Personal Finance
- Find out how much you need for retirement using Excel Goal Seek
- Why you should start saving early for your retirement
- Tracking Stock Quotes and Other investments using Google Spreadsheets
- Stock quotes in excel
- Household Budget Templates – Free Download
- More articles and howtos on personal finance using excel
| ||||
|
| ||||
|
Leave a Reply
 |
How to Find Dates of Public Holidays using Excel | Best of Pointy Haired Dilbert – 2009 |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
39 Responses to “Track Your Mutual Fund Portfolio using Excel [India Only]”
Hi Chandoo,
Thanks for the update. I have been using your tracker and find it really cool. Could you elaborate on the update? What has changed/ what has been added?
Cheers
Vaibhav
@Vaibhav.. sorry for not being clear. I have added a new section to the above post mentioning some of the key changes to the template since earlier version.
Hi Chandoo,
Thanks for the updated file. I have also created a file to track my Shares with some graphs
but I was not able to pull data for Mutual funds.
Thanks a lot again.
Couple of things, dont know if you can pull out data but here goes:
1) you need to calculate IRR s in addition to absolute gain/loss %
2) you have to atleast show benchmark returns say sensex or the nifty, say annual. If you can show it for the same time period as of when you are holding the fund, thats the ideal
. And 30 year bond yield. (maybe you can just pull out 1 year returns of each of these from another source?. From current data its best to force benchmark ETF s in your file, row 3612)
Cool interface. And sorry for the additional work
Hi,
Trying to use the excel sheet of Mutual fund tracker, but its not getting automaticaly refreshed and it says that automatic refresh fail also when i tried to insert my mutual fund the drop down list didnt come up, request you to please help me in using the excel sheet.
Hi,
Trying to use the excel sheet of Mutual fund tracker, but its not getting automaticaly refreshed and it says that automatic refresh fail also when i tried to insert my mutual fund the drop down list didnt come up, request you to please help me in using the excel sheet.
@Srickant… good points. I use the IRR analysis to see the fund performance all the time. I kept the sheet simple, but we can easily add the concept.
@Mehul… Are you connected to internet? the file should refresh automatically. Also, there is no drop down. You have to type the name. Also, enable external connections and macros, otherwise the file wont work.
Dear Chandoo, i am connected to Net all the time as i am using it from my office, i had tried writing the name too but it has not helped yet, request you to kindly mail me if possible the working file that you are using on my mail id mehul.here@gmail.com
@Mehul… I am using the same version that is uploaded here and it is working fine for me. The file already has few fund names (as examples). You need to write the names of funds as listed in AMFI website (you can remove vowels or slightly mis-spell them). Also, check if you are able to visit this page from office… http://amfiindia.com/spages/NAV0.txt
Hi Chandoo
I liked your tracker.
I have a question though. In the Portfolio Sheet, how have you restricted the navigation to go to cell M2 for example?
Dear Chandoo,
How to track divident history, or ant redemption?
Dear Chandoo,
How to keep a track of weekly dividend reinvestment funds( ICICI / UTI ) etc..weekly / fortnightly dividend reinvestment funds ?
How to keep a track of returns based on varying NAV and weekly dividend reinvestment, over a large period
Hi Chandoo
I was using your MF potfolio since last 1 year version 2 now all of sudden start giving errors
(when i am enabling update) today i have again download new version 4 and started putting the name of fund
1. its not allowing to copy paste from the nav sheet the name of funds
2. when I enabled the update latast its giving the same error #value#
Is there any prob ……….if not than can u please help me
bcoz I liked ur excel portfolio very much
Thanks & Regards
Mukund
rathimukund@rediffmail.com
[...] an excellent MS Excel workbook on tracking Mutual Funds from Chandoo’s Excel Blog: Link. You can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net [...]
Hi, Your Excel Tracker file gives bad/corrupted zip file. What’s going on?
When I type any name the pick list is not coming. what to do? I have already unprotected the sheet.
Hi Chandoo
I was using your MF potfolio since last 1 year version 2 now all of sudden start giving errors
(when i am enabling update) today i have again download new version 4 and started putting the name of fund
1. its not allowing to copy paste from the nav sheet the name of funds
2. when I enabled the update latast its giving the same error #value#
Is there any prob ……….if not than can u please help me
bcoz I liked ur excel portfolio very much
Thanks & Regards
Mukund
rathimukund@rediffmail.com
Chandoo you rock!
Chandoo, this file clearly does not work. I am new to your site but get the feeling the files are not updated with details around how to resolve issues to get the file working.
I have developed a Mutual Fund Portfolio Manager in Excel that maintains entire record of the investment and also gives annual rate of returns for each such investment. Any of your readers who desire to evaluate the Portfolio Manager may contact me at sudhem65@rediffmail.com. Thanks.
Hi, am able to refresh the NAV list however while typing the fund names in the portfolio sheet the fund names do not come up. Is something amiss??
With Excel 2010 , it is not working. Even default funds are showing “#N/A”. How to fix it?
Hi Chandoo,
Very impressed with the stuff on this website.
i have been using the mf tracker and would like to add another table that logs the weekly nav so that i can judge its growth in a graph and compare to the index This will help me judge the performance of the fund.
Pls advice me on how do i add this.
Thank you
NOT WORKING…
Ex: sbi gold fund – growth plan is missing & not updating…..
Whenever the xls file is opened…. it gives error prompt “The following data rage failed to refresh: latestMFNavs Continue to refresh all?
When I click “OK”…. nothing happens.
Hey Chandoo,
Came across your site and was pleasantly surprised.
Awesome stuff this. Do you have a stocks portfolio tracker as well. I tried a couple of them earlier but none seem to work too well or were so cumbersome that going to Moneycontrol was easier.
Would love to hear from you if you have a solution.
Cheers,
Hi Chandoo, I have been using your MF portfolio tracker for several years without any issues. Last few days, the tracker is not working, I believe some changes in the AMFI data. Can you please check and help us on this.
I’ve also reported the same thing on 4th jan…… haven’t recieved any reply to it still…..
Even for me the same error is happening when I enable the data connection for fresh data [as told by Radesh and gsvirdi].
i found the solution of it. I think recently there is some changes in AMFI’s NAV file. they have added INE number with mutual fund scheme.
those who getting the error have to copy any paste below link into their Latest NAV’s file in column E10. and drag the same upto the end of the column in entire sheet.
=IF(ISERROR(FIND(CHAR(171),SUBSTITUTE(B10,”;”,CHAR(171),3))),”",FIND(CHAR(171),SUBSTITUTE(B10,”;”,CHAR(171),3)))
after doing this change your nav will be updated and it works nice.
If any body get any error pls write it here.
Dear Aanad,
Still it’s not working. Earlier the fuzzyText in Column E was finding the text value (like nf204k01471), but with this formula of ur’s it is giving a neumerical output (like 22). I’m wondering how can it work???
With the existing Formula =IF(C10″”,VALUE(MID(B10,C10+LEN(D10)+2,FIND(“;”,B10,C10+LEN(D10)+2)-C10-LEN(D10)-2)),”") it’s giving an error #VALUE!
The above formuls should be replaced/overwritten in [strong]column C[/strong]…… As columns B & C are hidden by author so I was not able to find out the correct place for the formula.
Thx so much Anand for the solution, now the tracker file is working again.
Sir,
Using old version & satisfy with that , but currently in Jan-2012 its not working; Any help
just follow my above post and your old file will work too.
Hi, I have downloaded “MF Portfolio Tracker- India v3.0″. I am not able to input the funds in the Portfolio Sheet in the file. I have to input funds like HDFC Equity/HDFC Tax Saver/Sundaram Tax Saver and other funds in which I have invested. I am not sure about the Fuzzy Text. Its not showing in the dropdown. I am using excel 2010.
Please help !!!
Dear Varun,
It happens….. the fund name is there but still it does not appears in the cell. Manually searched from the sheet “NavPivot” and copy-paste the cell contents in the Portfolio Sheet.
Plz forgive Excell, and Chandoo for this inconvenience
GS Virdi.
Thanks Chandoo,
I found the fund names in the dropdown….. Its little inconvenient but its working….
Thanks for the templete. Its really helpful.
Varun
Thank you Chandoo,
The file is a great way to track the investments.
One doubt…how to track SIP?
Regards.
Ajay
Great Tool, since Jan 2013 started am not able to track the Fund “SBI MSFU CONTRA-DIVIDEND” in the excel. NAV is not found. Pl advice
Very cool tool. Great work. Thanks
Rahul