fbpx
Search
Close this search box.

Mutual Fund Portfolio Tracker using MS Excel

Share

Facebook
Twitter
LinkedIn

Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?  Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.


Download the mutual fund tracker – India now.


NOTE: File updated on 7 July 2018 to fix errors. Download again if you need to.

How to use this mutual fund tracker Excel workbook?

  1. Download and save the file to a folder on your computer (do not leave it in the downloads folder)
  2. Open the file in Excel (you need Excel 2016 / Office 365 to use this file. If you are using older version of Excel, you need free Power Query add-in)
  3. If prompted, enable “External connections”
  4. Go to Data and click on Refresh all
  5. This will fetch updated funds list and latest NAV (Net Asset Value) from AMFI India website.
  6. On the My Funds page, specify the funds you own, units, purchase price and purchase date
  7. The tracker will calculate your return, CAGR (Compounded annual growth rate of funds) and simple return of your portfolio and display it
  8. When you want to see updated value of your portfolio, simply refresh the tracker (Data > Refresh All or press CTRL+ALT+F5)

How this mutual fund tracker is made?

We will use 3 simple excel features to achieve this – Power Query, Cascading Drop downs and vlookup()

  1. First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.When you finish creating the table, it would look something like this:Mutual fund tracker excel - portfolio view (click to enlarge)
  2. Next we will use Power Query to automatically fetch the funds and latest fund value from AMFI website. This site maintains updated funds and values and publishes the list as TXT file at URL- https://www.amfiindia.com/spages/NAVAll.txt We can connect to this page as web URL from PQ and setup some simple rules to clean the data and extract the relevant bits we need. The result looks like this in Power Query editor.Mutual funds list from AMFI - view in Power Query
  3. Once we load this data to Excel, we can build a simple 2 level cascading drop-down system to capture details on the “my funds” page. That way, our drop down will be small and easy to use. Please read cascading drop downs page for details on how to do this.
  4. Finally, based on the fund name, we fetch the NAV and NAV date using, you guessed it – VLOOKUP formula. The rest is easy to calculate.
  5. Now as you input fund names and refresh data, your portfolio gets updated.

Few ideas on how you can enhance this:

  • Add graphs to see visually how the funds are doing
  • Build some VBA to store previous NAV values of your funds so that you can see historical dates

Problems with MF Tracker?

  • I cannot refresh data: You need internet connection. You also need Power Query for Excel (this is available by default in Excel 2016 or above, Office 365. If you have an older version of Excel, download Power Query add-in for free.
  • I see some error during download / refresh: The AMFI people have been inconsistent with their file formatting. It could be a weekly issue (ie every Friday they might be publishing some extra data.) Try again in a day or two. If the problem persists, post a comment so I can suggest a work around.
  • Some other problem: Please post a comment so I can look in to this for you.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

264 Responses to “Mutual Fund Portfolio Tracker using MS Excel”

  1. xd03x says:

    Many thanks for this, will save me a lot of time 🙂

  2. Chandoo says:

    @xdo3x .. thanks, welcome to PHD 🙂

  3. Gouri Sankar says:

    hey, i am too looking for last 3 days to get the same for stock quotes. but i did not get any right source. I do remember that once you posted simillar thing long back using myiris.com site. I just came here to check that and found this good post too. thanks.

    Any way, do you have any web source that can retrieve all stocks (it's great if we can specify a list of stocks) and retrieve the LTP bla, bla.., the main aim is that should done using minimum n/w resources. 🙂 You may expect, how frequiently a share holder tries to refresh.

    Any help would be highly appricieated.

  4. Chandoo says:

    @Gouri ... Getting live stock quotes from a web page could be tricky, have you tried nse / bseindia sites? they have web services exposed to grab the quotes in real time.
    Another way is to use yahoo finance, but again requires some bit of vba coding to parse the quote out.

    You best bet is to use google spreasheets and use the google functions to query google finance (oof! thats a lot of google in a sentence). But you need to know the BSE Stock Code to do this. I will be posting a google spreadsheet method for tracking stock portfolios in few days. Meanwhile if you come across an interesting way of doing this without lot of VBA / UDFs feel free to share it with me and other readers 🙂

    • Srinivasan says:

      Hi Chandoo,
      An Amazing site. I need one help. Is there anyway I can track my latest share prices in BSE and NSE and also maintain my share data in an excel sheet.

      regards
      Srinivasan

  5. Chandoo says:

    @Gouri - You can use this source for the BSE 500 Stocks, I guess this covers pretty much 90% of most of the portfolios..
    http://finance.indiamart.com/markets/bse/bse500.html

  6. aloke says:

    whoa. are those mutual fund returns on your sheet correct? any mutual funds you recommend?

  7. Chandoo says:

    @aloke - hey, they are just fictitious. Those kind of returns in this market, well, we can only manage them inside an excel... Btw, I use valueresearchonline.com's rating to decide where to park my money, may be you can take a look their ratings. Btw, welcome to PHD, hope you liked my site.

  8. Sastry says:

    This is fantastic. Am beginning to play around with this. Any ideas how, to handle SIPs, if i am buying a fixed set of MFs every month? Currently, all i can think of is having one row for each fund X each month..... Is there a more efficient way, you can think of? any Pivot tables kind of thing that can make it more efficient?

    Thanks a tonne.

  9. Chandoo says:

    @Sastry... thanks a lot, welcome to PHD 🙂
    hmm, SIPs, they are a tougher lot, I wanted to create the portfolio without indulging in VBA, and to know how much a SIP made, we need to constantly store the NAV as of past SIP dates in some other table. Instead of solving that problem, I just ignored it in my sheet.

    But yeah, you have a point, it would be lot nice if we can get SIP tracking this sheet. Currently there are no reliable ways to get historical NAVs on the web for Indian MFs (for US MFs you could use google finance functions / API or a bunch of other web services), may be I will create another sheet with some VBA to store NAVs as you refresh so that we can use it to track SIPs, but I dont know when I can get it done. Meanwhile if you are planning to do it, share it with our readers and I will buy you a donut and coffee 🙂

    good day!

  10. Sastry says:

    Chandoo... i havent figured a way yet.. but because SIP involved a fixed sum of amount per month, I have ignored the historical NAV's in my excel sheet... Instead, what i have done is - One column for my 'monthly budget'... one for 'number of months'... one column for "no. of units held".... So the historical NAV is kind of irrelevant. The trouble though, is i need to keep keying in the 'Number of Units" every month... but i guess that is not too difficult.... So, my donut and coffee????

  11. Chandoo says:

    @Sastry - wow, thats a neat method there. You have earned your reward, drop me a note if you are in / around columbus, I shall treat you 🙂

  12. Sastry says:

    Halfway around the globe in SE Asia... will remember to drop in a message when i am a wee bit closer... ;-))

    Cheers

  13. GWBE says:

    Hey this was exactly what I've been looking for to track my portfolio. Thanks a bunch for sharing this.
    If you get around to London, here's a coupon for a meal at my place! 🙂

  14. Dear Visitors,

    This blog is really nice and informative. We are pleased to know this blog is really helping people. Its our pleasure to post informative content on this useful blog created by webmaster.

    Its like NSE and BSE
    both are running out of volumes due to weak stock market trend from the beginning of 2008. We have seen new highs of Nifty and Sensex in the past and now they are trading on lower sides which is hampering the confidence of traders and investors. Fall in BSE
    and NSE
    resulted in lost of around 60% - 70% portfolio of investors though traders are minting money in week market too.

    One point to be noted and learned is that people are investing money by ignoring all fundamentals and technical aspects. We request all of investors and traders that Invest money wisely its hard earned money do research work only then invest if you don't have time to do research work let professionals do it for you.

    Think twice thrice before investing blindly without backed by technical and fundamental research.

    Have a Nice trading days ahead.

    Regards

    SHARETIPSINFO TEAM

    +91 9891655316
    +91 9899056796
    +91 9891890425

  15. Sandeep Kothari says:

    Good Job. Its Fantastic and very very usefull. I was looking for some thing like this. I had one more query. Do we have some thing similar for Stock Market for Shares.

  16. Chandoo says:

    @Sandeep ... welcome to PHD, thanks for the compliments... Just follow up the comments above, my reply to Sankar (comment # 5) - you can get the stock quotes (live) for the BSE 500 shares from http://finance.indiamart.com/markets/bse/bse500.html, just use the above method to create a sheet to track your share performance.... If you get enough time, share it with our other readers.. We appreciate your efforts... 🙂

  17. sankari says:

    hi,

    Kudos to your excellent effort....

    i've downloaded your excel sheet & refresh the data

    IIn the Latest Nav sheet, in cell no B5083 I find this
    104638;Reliance Long Term Equity Fund -Dividend Plan Dividend Option;11.8933;11.4176;11.8933;29-May-2008

    This is not reflected in E column. Hece am not seeing this fund in the drop down list.
    sankari

  18. Mahadi says:

    How to contact you? I want portfolio tracker on my website...

    Thanks

  19. Chandoo says:

    @Sankari ... welcome to PHD, thanks for visiting us...
    hmm, interesting, I have checked the sheet once again, thanks for pointing this out, there was an error in the formulas pasted after row 5013, I have uploaded the sheet once again. Download it and try, you shouldnt see this problem. Let me know if this persists... 🙂

    @Mahadi ... Thanks for visiting phd. ... what do you mean by "I want portfolio tracker on my site", if you are planning to include it on your site as a downloadable, go ahead and do that, just leave a link to my site and this post. btw, you can contact me on chandoo dot d at gmail dot com, or leave another comment and I will email you...

  20. sankari says:

    thanks for your quick response. will update u once i 've uploaded all my funds

  21. aditya says:

    Hi, an excellent tool for many investors....
    Is there any way I can fetch data for a specific date ... say on 5th June, I need data for the 30th may or something??

  22. captsri says:

    HI
    Try this on the address link:
    http://finance.yahoo.com/d/quotes.csv?s=BBDB.TO+NT.TO+GE+MSFT&f=snl1d1t1ohgdr

    you wl get the below in excel sheet or directly on the webpage. now u can use this raw data to make yr excel sheet.

    BBD-B.TO BOMBARDIER INC CL 8.84 06/05/2008 4:10pm 8.97 8.97 8.66 0 N/A
    NT.TO NORTEL NETWORKS C 8.21 06/05/2008 4:10pm 8.3 8.39 8.12 0 N/A
    GE GEN ELECTRIC CO 31.06 06/05/2008 4:00pm N/A N/A N/A 1.18 14.35
    MSFT MICROSOFT CP 28.3 06/05/2008 4:00pm N/A N/A N/A 0.43 16.44

    what each codes stands for is as follows(- how is that for a easy way to get info. remember the name is yahoo finance has to be used
    CHeers.)

    a Ask
    a2 Average Daily Volume
    a5 Ask Size
    b Bid
    b2 Ask (Real-time)
    b3 Bid (Real-time)
    b4 Book Value
    b6 Bid Size
    c Change & Percent Change
    c1 Change c3 Commission c6 Change (Real-time)
    c8 After Hours Change (Real-time)
    d Dividend/Share d1 Last Trade Date
    d2 Trade Date
    e Earnings/Share e1 Error Indication (returned for symbol changed / invalid)
    e7 EPS Estimate Current Year
    e8 EPS Estimate Next Year e9 EPS Estimate Next Quarter
    f6 Float Shares g Day's Low h Day's High
    j 52-week Low k 52-week High g1 Holdings Gain Percent
    g3 Annualized Gain g4 Holdings Gain g5 Holdings Gain Percent (Real-time)
    g6 Holdings Gain (Real-time) i More Info i5 Order Book (Real-time)
    j1 Market Capitalization j3 Market Cap (Real-time) j4 EBITDA
    j5 Change From 52-week Low j6 Percent Change From 52-week Low k1 Last Trade (Real-time) With Time
    k2 Change Percent (Real-time) k3 Last Trade Size k4 Change From 52-week High
    k5 Percebt Change From 52-week High l Last Trade (With Time) l1 Last Trade (Price Only)
    l2 High Limit l3 Low Limit m Day's Range
    m2 Day's Range (Real-time) m3 50-day Moving Average m4 200-day Moving Average
    m5 Change From 200-day Moving Average m6 Percent Change From 200-day Moving Average m7 Change From 50-day Moving Average
    m8 Percent Change From 50-day Moving Average n Name n4 Notes
    o Open p Previous Close p1 Price Paid
    p2 Change in Percent p5 Price/Sales p6 Price/Book
    q Ex-Dividend Date r P/E Ratio r1 Dividend Pay Date
    r2 P/E Ratio (Real-time) r5 PEG Ratio r6 Price/EPS Estimate Current Year
    r7 Price/EPS Estimate Next Year s Symbol s1 Shares Owned
    s7 Short Ratio t1 Last Trade Time t6 Trade Links
    t7 Ticker Trend t8 1 yr Target Price v Volume
    v1 Holdings Value v7 Holdings Value (Real-time) w 52-week Range
    w1 Day's Value Change w4 Day's Value Change (Real-time) x Stock Exchange
    y Dividend Yield

  23. [...] read: Create stock / mutual fund portfolio tracker in excel using web lookup queries Tags: america, Analytics, business, excel, how to, microsoft, personal finance, portfolio tracker, [...]

  24. [...] If you want to import live data from web to your worksheets, you can use Excel web queries. This is a powerful tool using which you can create worksheets that can fetch data from web (or network resources) and display in your worksheet for you to process them. You can findout more about these by learning to create a mutual fund portfolio tracker sheet. [...]

  25. Rajaguru says:

    Wow..wonderful effort and the quality of content is really informative and useful..

  26. Dear Visitors,
    This blog is really nice and informative. We are pleased to know this blog is really helping people. Its our pleasure to post informative content on this useful blog created by webmaster.

    http://www.shareinfoline.com provides you with the best Indian stock market tips..that too for very less amount and with maximum sureity ...There tips are really doing wonders in the world..At this point of time they are the most trustworthy players ...

    Do mind my words and just check http://www.shareinfoline.com

  27. Ajay Kr Dhamija says:

    Please tell my how to get historical NAV of more than 90 days from amfiindia.com. On their site they have data only for 90 days. We have to repeat the procedure for so many times

    Thanks

  28. Dear visitors,

    Recent global melt over is causing problems worldwide. We have witnessed major downfall in NIFTY and in SENSEX. In just two days Nifty crashed like house of sand. There are rumors that Nifty will see lower circuit and all, still we strongly suggest all that
    don't follow rumors go as per technical analysis. Still buying at this stage should be avoided due to bad sentiments Nifty has
    taken a V turn and has entered into a bearish trend.

    Let once Nifty take U turn only then it will be right time to enter for Investors for value buying. Traders never mind if market is
    bullish or bearish as they can mint money either way.

    Again we will say don't follow rumors follow market trend.

    For any doubt please feel free to ask us.

    Thanks

    Regards

    SHARETIPSINFO TEAM

  29. Regi says:

    Good article. I wrote a similar code a year back to track MF Nav's and to also keep historical NAV's (on Java). The excel sheet seems good. Had a quesion on the vlookup, would the vlookup give u a perfect match ? I am guessing the fund name in the first sheet should exactly match the fund name as per amfi records ?

    BTW... Is anyone here interested in creating a portal dedicated for Indian MF. mail me at regigeorge_regi at yahoo dot com

  30. Chandoo says:

    @Ajay... You may have to visit individual fund sites to get historical NAVs. Agree, it is a limitation with AMFI. Another approach could be to use a site like valueresearchonline.com or myiris.com to fetch the NAVs (not sure how you can do this from excel unless you are subscribing to their database by paying them some money)

    @Regi: You are right. Vlookup needs exact spelling to fetch the correct fund name. If you want to perform fuzzy search read this : http://chandoo.org/wp/2008/09/25/handling-spelling-mistakes-in-excel-fuzzy-search/

  31. Novice says:

    Hi Chandoo,

    Nice one.

    I have a question though. Can it be modified to fetch my daily trade data from one of the online brokers. It is a https site with login and password.

    Please advise. I would like to "retain" your services if you can custom build one for me :-).No offence meant.

    Regards

  32. Chandoo says:

    @Novice: As far as I know you cannot get private web pages (the ones that need login / password to access) using Excel's web queries. You can overcome this using VBA though.

  33. NK says:

    Chandroo,

    The problem post cell 5012 still seems to be there - i.e. lookups post this row do not seem to work. Could you advise what we should do to fix this?
    Thanks.

  34. [...] when you have multiple pivot tables or web query lookups it is painful to refresh each table to get the correct data. Debra from Contextures Blog shows how [...]

  35. kedar says:

    Hey chandu, this is really fundoo.... good excel sheet , great to track any MF portfolio, however doesnt it need an upgrade to include SIP investments also.?
    This can be done using another sheet maybe where SIP can be tracked seperately and then linked to the main sheet ......

    this would make this wonderful.....

  36. Alex says:

    Advise use next tool-Excel file error: data may have been lost,which is free as how as i remember,program save important information,such as graphics,statistic and mathematics,program will help you to recover valuable information and avoid its losses,tool scans your broken worksheet,then gets the data from this document,will help you to repair damaged files in Microsoft Excel sheet recognizable format,repair file Excel this file is not in a recognizable format, Excel showing this file does not in a recognizable format, or Microsoft Excel worksheet this file is not in a recognizeable format, it is an Excel file error: data may have been lost: Microsoft
    Excel impossible read file,tool performs a scan of your corrupt excel files not recognizable format and attempts to recover all available data.

  37. Ben says:

    This is very nice. Be aware though that for return calculations to be accurate you must account for income distributions (dividends and interest). This sheet only tracks change in NAV which is only part of the return calculation. For example a bond fund may show no change in NAV over the measured time-frame, but have a positive return due to the monthly interest distributions. This spreadsheet ignores those distributions.

    • Chandoo says:

      @Ben.. totally agree with you. Return calculations for MFs is a complicated task. Apart from the things you have mentioned there are other peculiarities like SIP, STP, annualization of return values. I have provided a basic framework for gathering MF data and measuring the performance. Once you have the data any set of performance metrics can be derived.

      Btw, are you aware of any good formulas to take care of dividends etc ? let me know and I will sponsor a donut for you 🙂

  38. cas says:

    dear chandoo,
    sir ..i am new to this world/pc , and i read your helps regarding creating excel , but all went above my head ,...i would request you to kindly make an excel which downloads qaily closing prices + last announcements...
    as per my logic in excel sheet column
    a = security name, b=new name , c=puchase date ,d=purchase price ,e=purchase qty,f=today close,g=profit/loss,h=last annouincements [ divi/bonus/split] ...sir ..excel will update , new name + today close+last announcements.....
    please add if iam kissing something
    i am ready/happy to pay the charges ..
    Thank you

  39. [...] Tracking your Mutual Funds and Stocks using Excel - Download included [...]

  40. Rahul says:

    The NAV tracker for Mutual fund is very good. If there is anything similar for shares of Indian Market please send the link. It will be helpful.

  41. NY Guy says:

    The sheet you have created is very helpful. Thanks for that. I have noticed that from last few days the file http://amfindia.com/portal/upload/downloadnav.txt is unavailable. Any idea? Any other alternative?

  42. NY Guy says:

    Never mind. It has a new URL now. Found it here: http://www.amfiindia.com/NavReport.aspx?type=1.

  43. Chandoo says:

    @NY Guy: yes, they have recently revamped the site and changed lot of urls. I will correct the portfolio tracker and post it once again. thanks for sharing the new url with all of us.

  44. Raman says:

    Hi,

    How e can change the source of data http://www.amfiindia.com/NavReport.aspx?type=1 to our existing excel file?
    Raman

  45. Chandoo says:

    @Raman, go to the sheet where downloaded NAVs are pasted and change the web reference from there. It is a simple step. I will upload another version of this excel when I get some time to edit it.

  46. sunita says:

    Good ! I have downloaded Portfolio Tracker on MF . I am unable to update NOW NAVs . While updating the Excel File I get an error everyday to update NOW Nav.

  47. sunita says:

    How to change Web Reference in the Latest NAV DAta ? Pl help

  48. Chandoo says:

    @Sunita.. you need to change the web reference to http://www.amfiindia.com/NavReport.aspx?type=1. You can find out how to do this from:

    http://r1c1.blogspot.com/2006/09/using-web-queries-to-import-data-to.html

    let me know if you have some issues in doing so.

  49. [...] Mutual Fund & Investment Tracker [...]

  50. Shweta says:

    I have started using MF Portfolio Tracker which is good and useful.

    In Latest NAV Data, I have edited the file as "NAV0" which updates NAVs for Open Ended as well as Close Ended NAVs. The Problem is :

    In the "Portfolio Sheet" if I choose any Scheme which pertains to LESS THAN CELL No. 6518 from the "Latest NAV Data" Sheet , it works fine and the NOW NAV displays the correct Value.

    If I choose a Scheme which belongs to the CELL GREATER than CELL 6518 from the "Latest NAV DATA", then the NOW NAV in the "Portfolio Sheet" shows the words "not found".

    How to acheive the correct NAV in the "Portfolio Sheet" if the Scheme is from CELL greater than 6518 in 'Latest NAV Data" ?

  51. Chandoo says:

    @Shweta... The sheet assumes there are no more than 6518 funds. That is why it is failing. It was created more than an year ago and in the last 16 months there are few hundred new funds. You can just change the formula references in the main sheet to include a larger range.. say upto 10000. Let me know if you are not able to do it.

  52. Shweta says:

    In the formula reference, I cannot find any range.

    =IF(C9="","",IF(ISERROR(VALUE(VLOOKUP(C9,fund_navs,3,FALSE))),"notfound",VALUE(VLOOKUP(C9,fund_navs,3,FALSE))))

    Where should I insert the range as 10000. ?

  53. Shweta says:

    Some Funds' NAV doesn't change or they do not upload the Latest Nav Data at AMFI. This disturbs the Comparison. Can I insert a column near NOW NAV which also shows the NOW NAV DATE ?

  54. Avinash says:

    Can any one help me to uderstand what is the formule or method for preparing list of Fund Name referring to other sheet. I am preparing spreadsheet based on this excell for tracking My Stocks.
    thanks

  55. Vinayak says:

    Thanks a bunch for the excel!

  56. [...] 28 DEC 2009 – An upgrade to the Indian Mutual Fund Tracker Excel Workbook [...]

  57. [...] (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 [...]

  58. Namit says:

    Hi

    It was exactly the kind of thing I was looking for. Great work.

  59. Santosh says:

    Hi,

    Thanks a lot for your effort to make our life easier. Can you tell me whether this will show the exact value of the fund with market ?

    Regards,
    Santosh

  60. Ramhesh says:

    Hi,
    Today i downloaded Mutual Fund Tracker V4 when i tried to use it i could not able to find Picklist details of Mutual Funds. Even if i type the Mutual Fund name then values are not calculated.
    Can you please help me in using this tracker

  61. Avinash says:

    Hi Ramesh,
    can you please let us know where did you find the Mutual Fund Tracker V4 ? can you Plz post the link. I can help you about that.
    Thanks

  62. sankaran says:

    Hi, Thanks a million for the MF tracker.
    I have just started using and am finding it really exciting.
    What about a similar tracker for equity?
    Thanks.

  63. Ramhesh says:

    Hi Avinash,
    Thank you for your reply, I got this file from below link.
    http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/MF-Portfolio-Tracker-India-v4.0.zip

    I noticed this link from Download option in Chandoo Blog only.
    Can you please check and comment hoe to resolve this issue
    BR
    Ramhesh Babu RD

  64. samantha says:

    i'm amazed about ur passion in Excel..thank you men,& one help i try to download the mutual fund nav for past 3years from the amfiindia.com ,but i couldn't able to view the nav report ...will u tell me the process if u could know my friend..

  65. Jill says:

    Thank you for sharing. I am trying to make my own sheet, and I am having problem getting my current value.
    Current Value (J3)=Current NAV(I3) * Unit(F3)
    My current NAV is link to a data on another sheet by Vlookup, and that data is an external link which update automatically daily.
    I simply use formula in cell J3 =(I3*F3), and it shows " #VALUE! "
    What did I do wrong?

  66. Hui... says:

    @Jill

    What values are in I3 and F3?

  67. Sanjivani says:

    Hey wl u plz help me, how i get freshly NAV in my excel sheet through newly updated format by Valueresearch???????????? plz help me.
    Thanks

  68. pradeep says:

    Thank you very much for the tool.

  69. subash says:

    AMF web site has changed the format of the NAVs text file with new character inserted ;-;-;
    hence the format is not working properly.can you help me change required for the nav

  70. Ramhesh says:

    Hi Chandroo,
    I am also facing the same issue as reported by Mr Subash. Can you forward the details for correcting the Excel sheet.

  71. Ramhesh says:

    Hi,
    I had found a short cut method to enable NAV auto update.
    Open Latest NAV excel sheet, There you can find ;-;- symbols, I had replaced thos symbol with blank space. Noticed NAV is getting autoupdated.

    This shortcut method can be tried until solution from Chandroo

  72. Y says:

    Hi All,

    Answer is simple Please make below changes:
    In Sheet "Latest NAV Sheet" , Column E , Cell "E10" ,
    Change the formula from =IF($D10="","",LEN(D10)+1)
    to =IF($D10="","",LEN(D10)+5) , ie: change the +1 in the end to +5

    Copy paste the formula till the end of the column. Press F9 and done.

    It worked for me.

    @Chandroo Sir, Please update the excel file as well in the webpage, Thanks.

    Regards,
    Y (MFSheet Loyalist)

  73. ramhesh says:

    Hi All,
    I had made changes as instructed by Y, It is working fine now.
    Thank you for providing the solution

  74. ramhesh says:

    Hi,
    Now i am facing an peculiar issue, two of my MF NAV is not getting updated from Latest NAV sheet, Those funds are Kotak Gold ETF and Sundaram Energy MF. When checked in Latest NAV sheet i noticed NAV on 10.01.2012 is getting updated. But the details is not getting transferred
    Need help in solving this issue

  75. subash says:

    The issue is again the AMF provided NAVs contains "INF......" for dividend payout this empty for growth options funds, one way is goto the mutual fund you are looking and remove it until the excel sheet is updated. Y's suggestions lacks ISN.... considersation for dividend payout and reinvestment.

  76. Y says:

    No Problem Buddy,

    Please enter below formula in cell E10,

    =IF($D10="","",(IF(MID(B10,9,2)=";-",LEN(D10)+5,IF(MID(B10,8,2)="IN",IF(MID(B10,21,2)="IN",LEN(D10)+27,LEN(D10)+16)))))

    Copy paste the formula till the end of the column. Press F9 and done.
    You are all set.... Any issues let me know

    @ramhesh , this should work for you , if it doesn't let me know. Also check if your formulas are consistent from the top cell to the lat one.

    Regards,
    Y (MFSheet Loyalist)

    • Ramhesh says:

      Hi Mr Y,
      Thank you for sharing the details, I tried copy the formula and pasted it in E10, After validating the formula getting error message as

      " The Formula you typed contains an error"
      Can you please check this issue.

  77. Venkatesh Kumar says:

    AMFI has made some changes in their file 'nav.txt'. So the excel file is not giving the correct NAV in the appropriate column. Can you please upload the modified excel file. I am very beginner in excel. Sorry for asking this. Thanks & regards

  78. Sandip Rami says:

    Dear Chandoo,

    Last 10 to 15 Days your Mutual Fund Tracer Can not Work Properly.

    Pls solve my Query And Reply me.

    It's Important for me and my portfolio.

    I am waiting for your reply.
    Thx

  79. way2profit says:

    Get Indian stock market tips covering cash tips, future tips, nifty trading idea, commodity and option trading tips

    • LKSingh says:

      First of all, Let us applaud Chandoo for such a beautiful and helpful site. Since the Nav.txt is changed and hence it is causing all sorts of issues. Any way Chandoo will get it done, but till then following are the steps which can help.
      1. Open Latest NAV Sheet.
      2. Replace ;-;-; with ;
      3. Replace ;-; with ;
      4. Replace Inf0***; with blank
      5. Replace Inf1***; with blank
      6. Replace Inf2***; with blank
      7. Replace Inf3***; with blank
      Continue this till Inf9***; with blank

      This should solve your problems temporarily. Hope this helps 🙂

  80. DILIP says:

    I tried as per Y says and LKSingh Says, yet it is not working.

    • LKSingh says:

      Dilip it is working, i have tested and posted. Looks like you have not replaced with blank, may be with word blank you may have replaced. I can modify instead as shown below
      1. Open Latest NAV Sheet.
      2. Replace ;-;-; with ;
      3. Replace ;-; with ;
      4. Replace ;Inf0***; with ;
      5. Replace ;Inf1***; with ;
      6. Replace ;Inf2***; with ;
      7. Replace ;Inf3***; with ;
      8. Replace ;Inf4***; with ;
      9. Replace ;Inf5***; with ;
      10. Replace ;Inf6***; with ;
      11. Replace ;Inf7***; with ;
      12. Replace ;Inf8***; with ;
      13. Replace ;Inf9***; with ;

  81. DILIP says:

    I failed to perfomr this exercise repeated but not succeed.

    Is it possible to repost/resend the excel file link.

  82. DILIP says:

    Thanks Mr. Singh.

    I failed to perform as suggested.

    Is it possible for you to repost/resend the excel file link.

  83. Ramhesh says:

    Hi,
    I had made changes as instructed by Mr. L K Singh apart from those 13 steps we need to replace ;INF--***; with :

    -- symbol indicates there are other INF numbers mentioned for other Mutal Fund AMC need to check for those details in Latest NAV and we need to replace it.

    We have to do lot of manual intervention for seeing the correct portfolio details.
    What to do we are not so professional in Excel as Mr Chandroo.
    Let us see whether we will be getting new template.

  84. shalini says:

    No need to replace values.
    The Nav values are available under Sales Price column.
    Just refer to the sales Price column L in you calculations.

    If you want , You can clean up the sheet :
    -> Update headings based on new schema (B4)
    -> Copy Paste values In Column D and E to new columns
    -> For Last column copy the values from current column

  85. Abhijit says:

    As pointed out earlier in the post the format of the NAV data has changed. You may try doing the following to correct the sheet.

    1. Open "Latest NAV Sheet" worksheet
    2. Click on cell D12
    3. In the formula bar paste the below formula. You may want to copy the below in notepad, then do a copy/paste

    =IF(ISERROR(LEFT(B12,SEARCH(";",B12,SEARCH(";",B12,SEARCH(";",B12)+1)+1)-1)),"",LEFT(B12,SEARCH(";",B12,SEARCH(";",B12,SEARCH(";",B12)+1)+1)-1))

    4. Once you complete the step 3, copy cell D12
    5. Select column D12, all rows will get selected
    6. Do paste special and select formulas.

    You should be ready to go. It worked for me 🙂
    Good luck!!

  86. Abhijit says:

    Ensure that you save the sheet after you are done with the changes so next time when you open, it will work correctly.

  87. Ganesh swamilingam says:

    Hi,

    Thanks Chandoo for this amazing excel based portfolio. From past few days, I am not able to refresh new data to my portfolio. As we are facing problems of format of the NAV data has changed in amfi. Also tried to do the suggestion given by Abhijit and earlier members but not worked. So please update the version as it would be useful to everyone.

  88. Moni says:

    HI chandoo,
    the tracker is not working since we entered 2012. pls guide me to correct this.
    regards
    moni

  89. Chandoo says:

    Hi all..

    AMFI has changed the format of NAV file. I will be posting an updated version of this file tomorrow. Thanks for your patience.

    • Ganesh swamilingam says:

      Hi Chandoo,
      It has been so many days waiting for new version still there is no update about the file.:(

  90. Suresh says:

    Use Moneycontrol.com for tracking Stocks, mutual funds, SIPs, ULIP's and lot more, it's absolutley free

  91. amit patel says:

    Hi Chandoo,

    We are waiting for new version of Mutual fund portfolio tracker in excel as the current version is not giving proper data. Hope it will be beneficial for all the users. Thanks for this wonderful tracker once again.

  92. Abhijit says:

    The change i suggested works for me. Not sure why it wouldn't work for you. The post does not allow me to attach a file, else i can attach it.

  93. RAMANATHAN ADVOCATE says:

    CHANDOO,
    YOUR IDEA IS TO BE APPRECIATED.VERY SPECIAL THANKS FOR THE CREATIVITY

  94. nirmish says:

    hi

    I too am using this excell sheet since long, please let me know from where to download the latest fixed version.

    Thanks Chandu for efforts

  95. Ganesh swamilingam says:

    Thanks a lot Chandoo for ur updated version. It is now working and for great efforts:)

  96. nirmish says:

    hi Chandu,

    Its working for me, great.

    Thanks a Lot.

    Regards
    Nirmish

  97. Ramhesh says:

    Hi All,
    I am using this Mutual Fund excel sheet for the past two years.
    I need help and suggestion on two thoughts
    1) I want to know how can i calculate difference of NAV amount of any mutual fund between yesterday and today's NAV.
    2) I want to maintain historical values of mutual fund NAV's on monthly basis to see P/L
    Can any one help me in suggesting formula

  98. Pk84 says:

    Hello Chandoo

    Your Portfolio Tracker is just awesome. Wanted to know if you are aware where I can get the latest NAV data for all the US mutual funds. There are close to 18,000 US mutual funds, I can see the fund perfomances and everything on morningstar.com but wanted it for analysis in excel, thats where I find your tool most useful. Any suggestions if I have to feed the NAV data for US funds in your model which website I should use, similar to AMFI data in India.

    Thanks a ton.

    PK

  99. Uttam says:

    Chandoo,

    Thanks a lot for the update.

    I need some help to export data to excel from following site.

    http://www.equitymaster.com/stockquotes/mkt-stats/result.asp?itemgroup=8&fromgroup=2&limit=25

    I am having the same problem for new icicidirect site as well.

    Thanks once again for helping people like me to gain excel knowledge.

  100. Suresh says:

    Hi Chandoo
    How can we create ULIP NAV tracker. Will you suggest the any URL

  101. Kannan says:

    Hi Chandoo
    Is there an excel sheet to track ULIP.

  102. Jayesh Shah says:

    Hi Chandoo, thanks but looks like the macro is picking up the wrong values. I tried

    DSP BlackRock Money Manager Fund - Regular Plan - Growth

    Reliance Money Manager Fund-Retail Plan Growth Option

    Birla Sun Life Income Plus (Growth)

     

    these and it picked up lower values as compared to what is listed in the database. IS there an easy way to select the fund rather than scrolling down? It is a large list!!

    thanks 

  103. Lakshmipathi says:

    HI,
    i tried HDFC TOP 200 , It is not updating latest nav. In NavPivot sheet  teh latest value is not updating.

  104. Jayesh Shah says:

    The new vernon works with Excel 2007. Mine has updated the values. Thanks!

  105. Surajit Pramanick says:

    Thanks a lot for uploading "MF Portfolio Tracker".
    Similar way I was trying to create "Equities Portfolio Tracker", but I am unable to find any source data in Tabular format for importing through "Excel web quarry". I am unable to find any single page source for all NSE or BSE listed shares to import like AMFI website for Mutual Fund.
    Have you created anything for Indian Equity market? or can you help me in this regard?

  106. gj says:

    piviot table not updating  in excel 2003 version plz help

  107. Mark Lim says:

    Hello! Thank you for sharing this file. However, I was wondering how I could get the local NAV details in my country (Philippines)? Pardon if this has been answered already but would appreciate a simple instruction on how to do it.

    Many thanks. 

  108. Mayank says:

    is there a way to track dividends in this ?

    thanks
    Mayank 

  109. For writing a phd your background should be science.

  110. Saurabh says:

    Dear Chandoo,

    Need your help on this..
    Need this workbook
    Tracking your India stock portfolio using MS Excel (Similar to
    Mutual Fund Portfolio Tracker using MS Excel)

    Thanks

  111. AnanthaKrishnan says:

    Hi Chandoo,

    Great Site. MF Portfolio tracker is awesome. Thanks.

    Best, AK

  112. MrGoldinvest says:

    Nice breakdown on how to create an excel sheet. I'll definitely be using this as my basis the next time I create an excel sheet.

  113. Mahesh says:

    I am using Mutual fund tracker for quite long time but yesterday while refreshing the data on sheet, the Raw data on Latest NAV sheet gets deleted. please fix at the earliest

  114. k.b.sankaran says:

    I have also been using the Excel (2003) portfolio tracker for quite some time now. off late the NAVs are not getting updated. It comes up with a message "unable to open http://amfiindia.com/spages/NAVO.txt". Not sure if this is due to a change in the url to "http://portal.amfiindia.com/spages/NAV0.txt". I thought that I could edit the web query but could find a way to locate and edit the web query.Kindly have a look.
    KBS

  115. Ramhesh says:

    Hi,
    Follow the below link to see steps i had updated on 13th Oct 2013 and 13 Nov 2013 to correct the issue
    http://chandoo.org/wp/2009/12/28/mutual-fund-tracker-excel/#comment-454511

  116. Shafi says:

    Derar Sir, I am a user of this tracker that you created. It was working excellent. But when I installed MS Office 2013 in my laptop last Sunday, the tracker stopped working. Whenever I open it, a pop-up box appears, telling that 'Reference is not valid'. The 'Latest NAV sheet' is getting the updated data. But, 'Portfolio sheet' and 'NAVPivot' sheets are not updating. I tried many options. Still no clue. Could u please advise. Yours faithfully, Shafi

  117. Jdogzilla says:

    Hi, have updated the MF tracker. Wanted to share with all. Please follow the link below. Would be interesting if someone can figure out how to calculate XIRR for each fund assuming we will sell today at current NAV price.

    https://drive.google.com/open?id=0B9mqr_Xv4tiEWERPTGJJMGFnS00&authuser=0

  118. Arbab says:

    I don't understand why you put the Avg unit value, What is the significance in different type of funds, kindly explain.

  119. Onkar says:

    Cannot find all the funds and fund options that I have invested in..

  120. Jeetesh says:

    Dear Chandoo,

    I want to compare the previous and current day NAV form the same AMFI website for the specific Mutual funds. The result of the comparison i need is
    1.Per Day movement
    2. Yearly Return

    Kindly assist on this request.

    Best Regards,
    Jeetesh J

  121. Madhuri says:

    Hello friends firstly thanks for every one for this community and share best experience and knowledge of stock market. Stocks are appearing by companies in order to raise capitals and are bought by investors in order to acquire a portion of the company.With the help of Stock Tips service Provider India you will determine about stock is profitable for you.
    best nifty option tips provider in india

  122. raj says:

    I have tried to download your file MF Tracker, but am unable to Refresh Data, I have also changed the WebLinks but everytime Data cannot be Refreshed and NOW VALUES : #Value!. Please help

  123. Anil says:

    i am using MS office 2013 , your MF portfolio tracker is not working in the ms office version pl help how to refresh the data

  124. Srinath says:

    Thank you very much for providing the Mutual Fund tracking Excel sheet. It has been very useful to track most of the mutual Funds.

    I am facing a small problem in terms of a particular mutual fund which is getting displayed as part of your pop up menu and also not available in the web query file. Could you please find out and let me know the solution for this.

    The fund name is UTI MNC Fund - Growth
    I have also provided the link to this fund on moneycontrol.com

    http://www.moneycontrol.com/mutual-funds/nav/uti-mnc-fund/MUT023

    Thanks in advance.

    Srinath

  125. Srinath says:

    Dear Chandoo,

    Further to my earlier request, the statement should read as It is NOT getting displayed as part of the pop up menu.

    It is not available at all in the list of Mutual funds that is being taken from the web site.

    Regards,

    Srinath

  126. yuvani verma says:

    Hello everyone this is the best platform to share best experience & knowledge of stock market.To get best
    bullion tips to the best advisory.

  127. ANKIT says:

    Hi Chandoo,
    I haven't seen any recent comments from you on this blog even though there are lot of questions unanswered in this post.

    Let me know if you are still maintaining this site. I would like to seek your help on MF tracker.

  128. Ramhesh says:

    Hi Chandoo,
    Mutual fund tracker is not working properly getting script error message in Excel "An error has occured in the script on this page" which refers to URL:https://connect.facebook.net/en_US/fbevent.js.

    Current NAV file details in data connection query frrm AMFII website used to fetch NAV details
    https://amfiindia.com/spages/NAVII.txt

    This link can be accessed separately in browser however this not working in excel query.
    Need your help in resolving this issue

  129. Ramhesh says:

    Hi Chandoo,
    I had found root cause of the issue why Mutual Fund tracker is getting "Value#" error. From 3 Jul 2018 on wards AMFII website doesn't shows "Sales Price" of mutual funds.
    Where as in NAV Pivot excel sheet if we select "Sales Price" then excel sheet is not working once the "Sales price" is unchecked then mutual fund tracker is working.

    Also current NAV file which we should use "https://www.amfiindia.com/spages/NAVAll.txt" for data connection.
    Due to issues from AMFII website mutual fund tracker is not getting updated correctly

  130. Ankur Rathi says:

    Can you tell me, Which mutual fund is best?

  131. Joy says:

    I am also not sure which one will be best.

  132. Deepak says:

    Hi Chandoo,

    Is there a way to pull the market indicies for the various MF benchmarks. I am finding that Moneycontrol does not have all of them. What I do (for the ones I have) for my SIPs if list of the SIPs dates and amounts, use the index values for those same dates to calculate and compare bench mark returns. Thx

  133. shafff says:

    Hi, I have a history data on NAV price where I would like to use to make a mutual fund tracker. From where should I begin first? Thank you in advance

  134. VINOD says:

    Hi Chandoo,

    Your website is awesome for EXCEL .

    I downloaded spread sheet for MF Tracking. The problem faced by is that I am working on Excel 2007 which does not have a power query addin.
    Please guide how to install power query add in for MS Excel 2007 and source for that.

    This is required for making full advantage of downloaded MF Tracker.

    Regards,
    Vinod

  135. Lokesh kumar Pandey says:

    Thanks for this valuable info on mutual fund. I will try this.

  136. Lokesh kumar Pandey says:

    I am a beginner . And wanted to learn whatever i could about the funds. I have a got some essential information by this posts thank for it... Keep updating us....

  137. Pankaj kumar gupta says:

    Hi Chandoo
    Is there an excel sheet to track ULIP

  138. Ankur says:

    Hi Chandoo

    While refreshing the sheet, the following error message is getting displayed:

    The Csv.Document paramter 'Columns' is invalid.
    Let me know if there is a known workaround.
    Thanks for putting this sheet in the public domain, really useful for tracking

  139. eminentit says:

    I have read your blog its very attractive and impressive. I like it your blog.
    Power BI training in marathahalli bangalore

  140. sikander says:

    Dear I got this error
    Expression.Error: The Csv.Document parameter 'Columns' is invalid.
    It was working in my excel 2013 after adding power query, now i change the excel to 2016 now this error is coming
    How to fix this

  141. abhi singh says:

    is ms excel is good for other uses

  142. abhi singh says:

    can you tell me more about this

  143. Hi Chandoo,

    Is there a way to pull the market indicies for the various MF benchmarks. I am finding that Moneycontrol does not have all of them. What I do (for the ones I have) for my SIPs if list of the SIPs dates and amounts, use the index values for those same dates to calculate and compare bench mark returns.

  144. Hi Chandoo,

    Is there a way to pull the market indicies for the various. I am finding that Moneycontrol does not have all of them. What I do (for the ones I have) for my SIPs if list of the SIPs dates and amounts, use the index values for those same dates to calculate and compare bench mark returns.

  145. venkat says:

    hi Chandoo,
    I have downloaded the sheet and when i go for the refresh and it says the below error
    initialization of data source failed.
    check the database server or contact database admin, make sure external database available. can you help me out.

  146. Hindi Kahani says:

    Your blog is too good, you have written a wonderful article..
    Motivational stories in hindi for success

  147. PANKAJ KUMAR GUPTA says:

    Sir,
    Can you guide me how to track my ULIPs NAV in excel

  148. Rahu tripathi says:

    Hello brother , the problem is .
    I have downloaded the sheet and when i go for the refresh and it says the below error
    initialization of data source failed. What to do

  149. Jaideep Tibrewala says:

    Hi all, have created a Google Sheet version of this with some modifications. Please feel free to copy the sheet and use as you see best. Thanks to Chandoo once again for setting this up.

    Note that the Google Sheets version does work a bit slower than excel. I'll post a link to the Excel version too a bit later.

    https://docs.google.com/spreadsheets/d/1WTd-CBfSX8WuNyqjuhBJZ2H9BcVakmEDWQcLaykjGlw/edit?usp=sharing

    • raj says:

      can you just make changes in the option of selecting of fund name will it be able to type funds name as its very difficult to find fund to enter .

    • raj says:

      as i am not able to find reliance nifty index fund in list , just make changes such that fund can be found easliy.

    • raj says:

      JAIDEEP your sheet has some error it only updates nav of latest date .i.e when i downloaded it ,it got updated the nav of same day but after few days it still shows the nav of same date.
      i downloaded it on 1st april
      when i refreshed it on 3d april it still shows nav of 1st april
      you can see column right down where its shown latest nav , thats need to be updated.

      • Jaideep says:

        Hey Raj, updating the values on google sheets takes 1-2 minutes. Its a bit slower than using excel. Plus you only get NAVs of the PREVIOUS WORKING DAY and not of the previous day. I just opened my sheet now and got updated with the NAVs for the 2nd of April. Share your email and I'll send you the xlsx version.

  150. raj says:

    i am using microsoft excel 2010 version ,when i downloaded the file it was not able to refresh as it said check the database server or contact administrator ,make sure external database is available and the try operation again.
    i even installed power query but even that is not g etting installed

  151. Sachin Tripathi says:

    Hi Chandoo,
    I have just downloaded the xls file for mutual fund return from your website.
    i am getting error as below
    [Expression.Error] The Csv.Document parameter 'Columns' is invalid.

  152. Harsh G says:

    Hi

    Thanks for the excel sheet of mutual funds, but i would like to tell you that there are new funds in India which are not in your list like , IDFC MfF's , axis focused 25 equity fund etc.

    I would like to request you to kindly update your lists.

    Regards
    Harsh G

  153. Prakash Rathod says:

    Hi the excel shows an error with updating the data
    "Initialization of data source failed"

    I am really looking forward to receive help from you

    i cross checked data for Aditya Biral Sun Life Tex Relief 96 Growth fund and the current NAV figure did not match

    you help is appreciated.

  154. Jaideep says:

    Here is a link to an working excel version of the google sheet I had shared earlier. Feel free to use/modify. If you make any great enhancements to the sheet, do share back with me so I can incorporate them into the same. https://drive.google.com/file/d/1W_tHyDg9DWfaW9rTRJWgY-Z95uOQK6i2/view?usp=sharing

  155. YASIR IFTEKHAR says:

    HI CHANDOO,

    I was trying to make my own sheet( with the help of your sheet), after importing data, how to arrange the sheet, there are a lot of unnecessary rows. manually its task to delete the row.

  156. Krishna Solanki says:

    I am looking for spread sheet for ULIP Funds. Please share if you have related to ULIP fund.

  157. SIMON says:

    Dear Chandoo, All,

    Good Day

    Thanks for the wonderful excel tracker have been using it for some years. Unfortunately in the older version the pivot table gets corrupted. I keep a copy of the original file and reenter all the data again and it works till the next time it crashes.
    I downloaded your 2018 version but its giving some csv error. Can you or anyone else help me in fixing this problem. As you would have understood by now I am just a user with not much knowledge of excel- so please explain in layman's terms.

    THANKS IN ADVANCE FOR YOUR ASSISTANCE

    brgds/simon

  158. varun says:

    sir, i have download MF tracker excel sheet from your web site . i enables the external content nd dowloaded PQ files. when i click to data at refresh all. it displays message that INITIALIASATION OF DATA SOURCE FAILED. i m not able to correct it. pls help me. i want one excel file as mf tracker.

    Varun Kumar
    Indian Navy

  159. DILIP says:

    I have tried to download the file from google drive and unable to open the file.

    It is giving following error message.
    " sorry we could not find c: \user\loca\microsoft\windows\inetcatch\ie\vdi13uc2\pk's_Utility_tool_v1,01(10).xlam it is possible that it deleted, renamed or deleted.

    and then after a new sheet is opening but not the downloaded google sheet.

    kinldy help me how to solve.

  160. Gopal Singh says:

    Thanks for sharing this helpful & wonderful post. I really appreciate your hard work. This is very useful & informative for me.
    Thanks for sharing with us. Thanks a lot.
    Regards?
    Adviceduniya

  161. Raghu says:

    Thanks for sharing this helpful & wonderful post. I really appreciate your hard work. This is very useful & informative for me.
    Thanks for sharing with us. Thanks a lot.
    Regards?
    RaghuVeer

  162. Ayush says:

    Unable to refresh the data; it says credentials are required to connect to the web source. Using Office 365. Please help.

    Thanks 🙂

  163. Uday says:

    I have been looking for something like this but can't download it due to restrictions in my office. I have one query though. My understanding is that this basically fetches the NAV values from the AMFI website for the funds I have invested in and neatly presents the values based on my portfolio.

    This means that I have to wait for each fund house to provide the NAVs for each scheme to AMFI at the end of the day (whenever the cutoff time is), and then this sheet in turn fetches that from the AMFI website.

    Question: Is it possible to have this ready right after 3:30 pm when the market closes and calculate the NAV ourselves? For example, if I have invested in Axis Bluechip, I know the names of all the companies in the portfolio and their closing stock prices at 3:30 pm, plus the expense ratios and all other technical information of the fund, so is there a way to fetch them live, calculate the NAV ourselves and get my portfolio values right after 3:30 pm?

    • Jaideep says:

      Hey Uday, What do you hope to gain by getting your portfolio value at 330pm vs say 930pm? You can't trade in MFs after 330pm anyway. So any decision you take will only be executed the next day, when you have updated NAVs from AMFI anyway. MFs are not for day traders, unless you are trading very large volumes in liquid schemes.

  164. awesome information thank you so much for sharing

  165. Rohit Saraf says:

    Thanks for the superb excel file. However, I am facing some problem in the file. Actually the refresh worked for few months but now it is not working. In other words, 'refresh' is disabled whenever I open the file. could you please suggest me the way around. Further, can you suggest me something so that on particular day the purchase section is updated automatically based on sip date.

  166. AKhilesh says:

    HI Chandoo,

    Have downloaded this file and not able to refresh. I am using Excel2016.

    Getting this error" [Expression.error] The Csv.Document .parameter'Columns'is invalid

    Can you check and upload the new working file.
    Best regards'

  167. Sanjeev Sah says:

    Really You Are Amazing

  168. Aakash R says:

    Hi, what steps did you use to clean the data after getting it from AMFI website using PQ. The final format in your sheet and data from AMFI are quite different. Pls help

    Thx

  169. Colin Kent says:

    I have been trying for a long time to create a graph of my UK mutual funds in excel 2016, but have always failed. I have no trouble with shares but codes for mutual funds baffle me. and example is: HL Multi-Manager Equity & Bond Trust. I would be eternally grateful if you could help me.

    Kind regards
    Colin Kent

  170. Rabindra says:

    The NAVs are not getting updated in Mutual Fund tracker excel file and shows following errors:
    [Expression.Error]2arguments were passed to a function which expects 1
    [Expression.Error]The CSV document parameter 'Columns' is invalid

  171. Ramki says:

    Hi Chandoo
    Being a senior citizen my investments are in FD and it becomes difficult to track which one is maturing and which one has already matured.Do you have any excel tracker ?

  172. Naveed says:

    Thanks for sharing the mutual fund information

  173. sankaran says:

    Hi Chandoo,
    I am still looking for a Indian stocks portfolio tracker in excel similar to your excellent MF portfolio tracker. Help please?

  174. Gaurav P says:

    Hello Guys,
    i downloaded Mutual fund Excel file and trying to refresh all, but its not allow me to do that.
    My Q is this Excell sheet will work and do i trust on this NAV.
    pls guide me and if u ve any more option pls share with me

  175. Rakesh T says:

    Thank you sir, you have given very good information, and would personally recommend it to my friends. I am sure you will post similar thanks.

  176. Srinivas R Varaganti says:

    Hi Chandoo,

    I have few investment in Indian MF's (I am a NRI- USA), and i am using your tracker for my MF Investments, I subscribed to a new MF (NFO) - Axis Special Situation Fund NFO, units will be allotted soon, so my question is will the funds list update with the new funds or do we have to manual update it.

    Thanks

    Srinivas R Varaganto

  177. Vikas says:

    Hi, while calculating the return there is some high mismatch in the mutual fund returns shown in my statement and in the your excel...in your excel it is calculated based on CAGR formula while in mutual fund statement they are calculating via XIRR(internal rate of return)...which one is correct ??

  178. Sanchari Barman says:

    I am having some problem in building the VBA to store previous data for my funds. Can you please give me the code to do this?

  179. Hetal says:

    Hi,
    i have downloaded MF tracker, but how to enter the data for the part sale of the units... if i am selling some units or have sold full units, how to calculate the realized profit

  180. Abhinay says:

    The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?

  181. SRK says:

    i am preparing a mutual fund tracker of my own. I created the cascade list as per the above instructions. However, in case of wrong combination of fund house and fund name it is not showing any color Indication alert. Pls help me out if i missed anything here.

  182. Srinivas R Varaganti says:

    Hi Chandoo,

    I have been using the excel MF tracker from 3 years, today when i opened my file, it crashed. i recovered the file but it is not updating the nav's (last update is on day before yesterday) i have lot of data on the file, like my shares and bonds. It is lot of work to enter the data again on new (downloaded from your website) file. Any way/idea to fix it.

    Thanks

    Srinivas

  183. Anurag Tiwari says:

    Hi,
    Well using this from last few months now, loving the overall feel. Slight issue with the NAV updating to the current price(I have the power query installed and also done with updating it). Is there any other updated file?

    Thanks
    Anurag Tiwari

  184. D. Dutta says:

    Hi,
    I am using this.
    However, please note that
    SBI Equity Hybrid - Regular Plan - IDCW
    is not in your data base.
    So this fund is not updating.
    Request you to look into this
    Thanks

  185. Debabrata Dutta says:

    Hi,
    Is it possible to get the mutual fund positions for a certain date, say 31/March/202i, in your mf tracker.
    If this can be done, please let please let me know the procedure.
    Thanks

  186. Raghavan says:

    It would be nice if it was possible to track MFs based on a transaction statement. So every time I do a transaction, bur or sell, the MF tracker should update the correct no. of units held after each transaction and update the same with current NAV.

  187. Swarn Jain says:

    very good article, this article is very helpful for me. Thanks

  188. pakainfo says:

    The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?

  189. Guessing Forum Very Nice Brother Very Useful Article Thanks for share this information

  190. MARUTHAPPAN says:

    hi
    i have a mf sip, do you have any tracker please advice.

    thx
    MARUTHAPPAN

  191. RAM says:

    Wonderful site, you have shared a very good article. thanks.

  192. ram says:

    Wonderful site, you have shared a very good article. thanks

  193. Codenotes says:

    The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?

  194. Prashant Thankey says:

    Hi Chandoo,

    I tried using the excel based mutual fund tracker made by you.

    I'm getting following two errors from Excel:

    (1) [DataSource.Error]Web.Contents failed to get contents from 'http://10.20.1.222:3128/' (407): Proxy Authentication Required

    (2) [Expression.Error] Acyclic reference was encountered during evaluation

    I request you to kindly guide me how to solve these two errors.

    Thanks in advance!

    -Prashant Thankey

  195. Akhilesh says:

    Hello,
    Really nice work with the mutual fund tracker. Could you please tell me how to pull only 2-3 mutual funds form the source instead of updating the whole list which seems inefficient (or is it?)
    My idea is to get only those funds which i am interested and keep a history of NAV changes. of those funds every day

  196. GARY FABRIKANT says:

    hi i am in use can i make your tracker track mutual funds in usa ? gary

  197. Rajan Mehrotra says:

    While using Mutual Fund Tracker, I encountered a problem on entering MF of Quant.
    In the Fund House, the name does not appear. In the drop down. What should I do.

  198. The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV.

  199. Mitan says:

    I have enabled as you have suggested but I am getting the following error. I have Windows 10 and Office 2016

    [Expression Error] The Csv.Document parameter 'Columns' is Invalid.

  200. mondal says:

    Thanks man thats a good information. thanks for sharing

  201. Suyash Singh says:

    it would be fantastic if it was possible to track MFs based on a transaction statement. Any ideas on how to handle SIPs, if I am buying a fixed set of MFs every month.

  202. Kiran says:

    Firstly, thank you for the Mutual fund Tracker. I downloaded it to my system and entered my mutual funds data. But i found that it works only for lumpsum investments and not for SIPs as it requires me to insert all my SIP investments data month-wise.

    For example:- i started investing in ICICI prudential SIP with Rs.1000 per month from May-2020. I need to insert total 20 months data to get exact current value.

    Kindly look into this. or let me know if there is any other way out to insert all my SIP data (monthly investments)

  203. nirmal singh says:

    nice brother super information

  204. jaga kumar says:

    oh i forgot this,nice to meet you friend

  205. News Menk says:

    This is great and helpful post.

  206. Prabu Sundararajan says:

    Thanks for your valuable information, this even works after few years which means - you have done a fantastic work.

    I am planning to track my MF investment using your tracker.. But i need more technical latest information like exit load, expense ratio, rating - how is it possible in same tracker ??

  207. Arpit Gupta says:

    Wow It's Really nice

  208. Hi all, I'm an active user of this MF sheet, and also created a version of this on Google Sheets. Looks for my posts above. I am currently building a Direct plan RIA platform for Mutual Funds called Glide Invest. Do check it out at https://www.glideinvest.com and would love to see you sign up on it.

  209. jaga kumar says:

    love content with concept thank for sharing this

  210. Abhay says:

    Wow It's really nice information

  211. umesh says:

    Hi,

    Thanks for the wonderful sheet, i was using it for last 2 years. However, due to permenant change by AMFI the file has stopped working since 14 June 2022. Please look into it. Thanks.

    Umesh

  212. Sandeep says:

    Last one week the 'Fund Name' is causing a data validation error. Is there an update which will solve it? Sandeep

  213. Anand says:

    Hi Chandoo,

    I am using your tracker for quit sometime and its was useful. Its not working now when i refresh the excel. Have you got a query from other users and you able to resolve the issue. Kindly share if you have latest version. Thank you.

  214. Anand says:

    Hi Chandoo,
    Appreciate your effort and help on sharing this excel. Will you be able to further help us to continue with your excel as its getting error out or just leave a message us to look for other options. we wish to continue with yours as its good and easy to maintian our portfolio's. Thank you.

  215. Send says:

    Everybody requesting query KINDLY NOTE that this website was last updated on July 6, 2018 as mentioned at the top. IT SEEMS Mr. CHANDOO IS NOT LOOKING AFTER THIS SITE ANY MORE. THEREFORE DO NOT WASTE YOUR TIME TO POST A QUERY TO SAVE YOUR TIME.

  216. SEEMA MAHESHWARI says:

    sir,
    please tell me to manage sip' return and accounting in your mutual fund tracker.
    as we know ,buying of sip is on monthly basis ,so how to fetch data of sip i.e its nav ,units ,date,and rate of return and total cost of invesment.

    thanks and regards

  217. PRASHANT says:

    how Will mutual fund tracker will calculate price for subsequent purchase.

    Will it be able to compute gain/(Loss) on sale/Redemption of Units.

    Further CAMS & Other investment house calculates Average NAV for computing returns

  218. Mohit says:

    am using Mutual fund tracker for quite long time but yesterday while refreshing the data on sheet, the Raw data on Latest NAV sheet gets deleted. please fix at the earliest

  219. Anirudh says:

    hi, thanx for tracker ,
    but there is one problem i'm facing is , if i've purchsed nav on different dates in one mf scheme.. so the investment amount is changing

  220. Ravi says:

    Hi,

    is there any possibility to get 52week High/Low NAV of said fund (along with yesterday NAV)?

  221. Rahul Sharma says:

    how Will mutual fund tracker will calculate price for subsequent purchase.

    Will it be able to compute gain/(Loss) on sale/Redemption of Units.

    Further CAMS & Other investment house calculates Average NAV for computing

  222. Newssow says:

    get 52week High/Low NAV of said fund (along with yesterday NAV)? get 52week High/Low NAV of said fund (along with yesterday NAV)?

  223. Masoom says:

    I am using Mutual fund tracker for quite long time but yesterday while refreshing the data on sheet, the Raw data on Latest NAV sheet gets deleted. please fix at the earliest

  224. Hanif Kulad says:

    Do you have excel to tracker SIP. I have downloaded Mutual Fund tracker India, in that Lumpsum investment tracking is easy.

    Please let me know if you have any SIP tacker.

    Thank you in advance.

    Hanif.

  225. Jinvani says:

    I am planning to track my MF investment using this tracker, but i have to learn excel

  226. Rakesh says:

    Apparently the query to website has been removed as the data doesn't refresh. Is there another version which works/

Leave a Reply