Mutual Fund Portfolio Tracker using MS Excel

Posted on April 18th, 2008 in Featured , ideas , Learn Excel , personal finance , technology - 133 comments

Excel base mutual fund portfolio tracker

Would you like to spend next 5 minutes learning how to create an excel sheet to track your mutual fund portfolio?


Download the mutual fund portfolio tracker excel workbook now.

[Download Excel 2003 compatible version here]


NOTE: File updated on 7-FEB-2012 to fix errors. Download again if you need to.

We will use 2 simple excel features to achieve this – web queries and vlookup()
[click here to learn more about web queries in excel]

  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:
    excel spreadsheet mf portfolio tracker table format
  2. Next, we will use web-queries to load the fund-names and the corresponding latest NAVs in a separate sheet. I have queried Association of Mutual Funds India [AMFI] – Latest Mutual fund NAV page since all my investments are in India. If you are in US or some other country you can query corresponding fund house / financial info aggregator sites (like google finance) to get the data. Remember to set “Refresh data on file open” on to get fresh data whenever you open the your tracker excel sheet.Since AMFI returns data in a text file with ; as delimiter, I had to parse the fund names and navs out of it using a combination of search(), left() and mid(). I will not get in to the details of how its done since you may have to process your data differently depending on source.Finally when the processing is done, we will have a table in the second sheet with all fund names and latest navs.
  3. Now, all we have to do is create lookup formulas (well just vlookup()) to get the latest NAV to our tracker table based on the entered fund name.
    • Assuming the fund name in which you invested is in cell “c1″,
    • Assuming the fund data is in table “sheet1!c1:d6000″ with “column c” containing the fund name and “column d” containing latest NAV,

    The formula for latest NAV can look like this:

    =vlookup(c1,sheet1!c1:d6000,2,false)

    Remember to use false for last parameter since fund names may not be sorted in alphabetical order on your source web page.

    Now we will repeat this formula for all the rows in latest nav column. I have built my portfolio tracker to track 20 funds at a time. Also, you can simplify formulas using named ranges.

  4. Finally we will write formulas for,current value = latest nav * units held
    profit/loss = current value – purchase value
    profit/loss % = “profit/loss” / purchase valueYou can add some conditional formatting to beautify the table (like turning text blue for profits and red for losses etc.)
  5. Thats all, you have now created a real-time mf portfolio tracker. It would look something like this when done:
    mutualfund portfolio tracker excel sheet
    You can do the same for stock portfolios, commodities etc. You just need a web source that gives you latest data and five minutes of free time

Feel free to Download the mutual fund portfolio tracker excel workbook now.

[Download Excel 2003 compatible version here]

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
  • Instead of doing plain % of profit / loss, compute realistic growth of your funds using date of purchase, risk free rate of return etc.
Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

133 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 :)

  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.

  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

Leave a Reply