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?
- Download and save the file to a folder on your computer (do not leave it in the downloads folder)
- 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)
- If prompted, enable “External connections”
- Go to Data and click on Refresh all
- This will fetch updated funds list and latest NAV (Net Asset Value) from AMFI India website.
- On the My Funds page, specify the funds you own, units, purchase price and purchase date
- The tracker will calculate your return, CAGR (Compounded annual growth rate of funds) and simple return of your portfolio and display it
- 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()
- 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:
- 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.
- 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.
- 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.
- 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.
271 Responses to “Mutual Fund Portfolio Tracker using MS Excel”
Many thanks for this, will save me a lot of time 🙂
@xdo3x .. thanks, welcome to PHD 🙂
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.
How do you offset a profit gain for when you add money to a mutual fund?
@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 🙂
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
@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
whoa. are those mutual fund returns on your sheet correct? any mutual funds you recommend?
@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.
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.
@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!
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????
Hi Sastry, i was about to ask this question to Chandoo and you covered it. This sheet is useful to track a monthly value of each sip. So, if i have 5 SIP's i need to make 5 entries. We can make a pivot to sum up each of this.
The other way of doing this is enter the total value of SIP amount invested till date and the current value of it in a seperate excel sheet and use the rate formula to know the growth %. Check the article given below.
https://economictimes.indiatimes.com/wealth/invest/heres-how-you-can-calculate-returns-on-mutual-fund-sip/articleshow/53841350.cms
@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 🙂
Halfway around the globe in SE Asia... will remember to drop in a message when i am a wee bit closer... ;-))
Cheers
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! 🙂
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
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.
@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... 🙂
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
How to contact you? I want portfolio tracker on my website...
Thanks
@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...
thanks for your quick response. will update u once i 've uploaded all my funds
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??
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
[...] 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, [...]
[...] 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. [...]
[...] Original post by Chandoo [...]
Wow..wonderful effort and the quality of content is really informative and useful..
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
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
[...] Looking for a way to track stock / MF performance from excel, read this. [...]
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
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
@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/
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
@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.
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.
[...] 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 [...]
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.....
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.
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.
@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 🙂
[...] Track your investments using Excel [...]
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
@cas: Welcome to PHD and thanks for sharing your ideas. I will be sending a separate e-mail to you about my consulting service details.
[...] Tracking your Mutual Funds and Stocks using Excel - Download included [...]
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.
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?
Never mind. It has a new URL now. Found it here: http://www.amfiindia.com/NavReport.aspx?type=1.
@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.
Hi,
How e can change the source of data http://www.amfiindia.com/NavReport.aspx?type=1 to our existing excel file?
Raman
@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.
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.
How to change Web Reference in the Latest NAV DAta ? Pl help
@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.
[...] Mutual Fund & Investment Tracker [...]
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" ?
@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.
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. ?
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 ?
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
I got the link to do that:
http://www.contextures.com/xlDataVal05.html
Thanks a bunch for the excel!
[...] 28 DEC 2009 – An upgrade to the Indian Mutual Fund Tracker Excel Workbook [...]
[...] (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 [...]
Hi
It was exactly the kind of thing I was looking for. Great work.
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
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
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
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.
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
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..
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?
@Jill
What values are in I3 and F3?
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
Thank you very much for the tool.
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
Hi Chandroo,
I am also facing the same issue as reported by Mr Subash. Can you forward the details for correcting the Excel sheet.
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
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)
Hi All,
I had made changes as instructed by Y, It is working fine now.
Thank you for providing the solution
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
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.
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)
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.
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
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
Get Indian stock market tips covering cash tips, future tips, nifty trading idea, commodity and option trading tips
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 🙂
I tried as per Y says and LKSingh Says, yet it is not working.
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 ;
Use FInd and Replace by pressing Ctrl F and do the above replacements.
I failed to perfomr this exercise repeated but not succeed.
Is it possible to repost/resend the excel file link.
Thanks Mr. Singh.
I failed to perform as suggested.
Is it possible for you to repost/resend the excel file link.
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.
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
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!!
Ensure that you save the sheet after you are done with the changes so next time when you open, it will work correctly.
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.
HI chandoo,
the tracker is not working since we entered 2012. pls guide me to correct this.
regards
moni
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.
Hi Chandoo,
It has been so many days waiting for new version still there is no update about the file.:(
Use Moneycontrol.com for tracking Stocks, mutual funds, SIPs, ULIP's and lot more, it's absolutley free
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.
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.
CHANDOO,
YOUR IDEA IS TO BE APPRECIATED.VERY SPECIAL THANKS FOR THE CREATIVITY
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
Thanks a lot Chandoo for ur updated version. It is now working and for great efforts:)
Mr. Ganesh,
Do you help me locate the updated version?
@All.. The updated version is posted online now. You can get it from here:
http://chandoo.org/img/d/MF%20Portfolio%20Tracker-%20India%20v3.0.zip
(For Excel 2003 users: http://chandoo.org/img/d/MF%20Portfolio%20Tracker-%20India%20-xl2003%20v3.0.zip )
hi Chandu,
Its working for me, great.
Thanks a Lot.
Regards
Nirmish
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
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
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.
I got the answer. Thanks.......with paste special command & refreshable web query.
Hi Chandoo
How can we create ULIP NAV tracker. Will you suggest the any URL
Hi Chandoo
Is there an excel sheet to track ULIP.
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
HI,
i tried HDFC TOP 200 , It is not updating latest nav. In NavPivot sheet teh latest value is not updating.
The new vernon works with Excel 2007. Mine has updated the values. Thanks!
[...] Mutual Fund Portfolio Tracker [for India only] [...]
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?
piviot table not updating in excel 2003 version plz help
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.
Can you please email me a file that updates Philippines NAV data?
is there a way to track dividends in this ?
thanks
Mayank
For writing a phd your background should be science.
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
Hi Chandoo,
Great Site. MF Portfolio tracker is awesome. Thanks.
Best, AK
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.
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
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
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
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
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
Thanks Bro.... It works great now...
I don't understand why you put the Avg unit value, What is the significance in different type of funds, kindly explain.
Cannot find all the funds and fund options that I have invested in..
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
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
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
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
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
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
Try as UTI - MNC Fund (UGS 10000)-Growth Option and see if it shows up.
[…] http://chandoo.org/wp/2008/04/18/mutual-fund-portfolio-tracker-using-ms-excel/ […]
Hello everyone this is the best platform to share best experience & knowledge of stock market.To get best
bullion tips to the best advisory.
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.
@Ankit
Chandoo is still very active.
He may have just missed these posts
@Ankit... I have developed v3.0 of this last weekend. I will update this post with new instructions and workbook soon.
Hi, it seems in the latest version (MF Portfolio Tracker- India v3.0.xlsx) web query need to be updated to . Could you please check and update the excel
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
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
Can you tell me, Which mutual fund is best?
I am also not sure which one will be best.
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
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
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
Thanks for this valuable info on mutual fund. I will try this.
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....
Hi Chandoo
Is there an excel sheet to track ULIP
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
I have read your blog its very attractive and impressive. I like it your blog.
Power BI training in marathahalli bangalore
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
is ms excel is good for other uses
can you tell me more about this
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.
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.
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.
Your blog is too good, you have written a wonderful article..
Motivational stories in hindi for success
Sir,
Can you guide me how to track my ULIPs NAV in excel
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
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
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 .
as i am not able to find reliance nifty index fund in list , just make changes such that fund can be found easliy.
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.
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.
rajpal741852@gmail.com
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
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.
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
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.
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
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.
I am looking for spread sheet for ULIP Funds. Please share if you have related to ULIP fund.
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
Try this ... https://drive.google.com/file/d/1W_tHyDg9DWfaW9rTRJWgY-Z95uOQK6i2/view?usp=sharing
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
Hi Varun.. AMFI the source system for this data has changed their website structure. I will update the post to share a new template soon.
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.
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
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
Unable to refresh the data; it says credentials are required to connect to the web source. Using Office 365. Please help.
Thanks 🙂
Try this ... https://drive.google.com/file/d/1W_tHyDg9DWfaW9rTRJWgY-Z95uOQK6i2/view?usp=sharing
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?
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.
awesome information thank you so much for sharing
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.
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'
Really You Are Amazing
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
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
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
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 ?
Thanks for sharing the mutual fund information
Hi Chandoo,
I am still looking for a Indian stocks portfolio tracker in excel similar to your excellent MF portfolio tracker. Help please?
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
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.
nice post
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
Hi Srinivas...
If you go to Data ribbon and click on Refresh all, it should fetch new funds list automatically.
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 ??
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?
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
The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?
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.
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
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
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
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
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.
very good article, this article is very helpful for me. Thanks
The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?
Guessing Forum Very Nice Brother Very Useful Article Thanks for share this information
hi
i have a mf sip, do you have any tracker please advice.
thx
MARUTHAPPAN
This is amazing !
Wonderful site, you have shared a very good article. thanks.
Wonderful site, you have shared a very good article. thanks
The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV. Can you please help?
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
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
hi i am in use can i make your tracker track mutual funds in usa ? gary
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.
The IDFC Multicap Fund name has changed. Due to that the Tracker is not updating the NAV.
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.
Thanks man thats a good information. thanks for sharing
This is amazing !
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.
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)
nice brother super information
oh i forgot this,nice to meet you friend
This is great and helpful post.
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 ??
Wow It's Really nice
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.
love content with concept thank for sharing this
Wow It's really nice information
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
Last one week the 'Fund Name' is causing a data validation error. Is there an update which will solve it? Sandeep
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.
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.
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.
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
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
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
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
Hi,
is there any possibility to get 52week High/Low NAV of said fund (along with yesterday NAV)?
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
get 52week High/Low NAV of said fund (along with yesterday NAV)? get 52week High/Low NAV of said fund (along with yesterday NAV)?
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
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.
I am planning to track my MF investment using this tracker, but i have to learn excel
Apparently the query to website has been removed as the data doesn't refresh. Is there another version which works/
Were you able to solve the issue?
It was refreshing for me fine until recently. Now even though it is June 28th, it shows June 21 data. Refreshing does not help.
Sir
Namaskar...
I need a formula for auto populating dates for the next month based on SIP date and that too based on working date for my Mutual Fund investment. e.g., SIP date 10-12-2018 the next working date is 10-01-2019, 11-02-2019 etc.,
I used workday(edate(.....))
I am not getting the desired result.
Looking forward for your guidance.
Regards
V S Venkat
I am unable to refresh the data. Getting an error as under:
Microsoft.mashup.container wants to use your confidential information stored in "Microsoft.Office.Excel.ProtectedDataServices" in your keychain To allow this, enter the "login" keychain password.
Password ________
Always allow ______ Deny ________ Allow ________
Please help
Hello
When I am entering "Years Invested for" annualized Rate of return is not generating
<>
can you elaborate on this? I am getting a semi-colon csv kind of data in one single column. if i use text to table i am unsure if the data is refreshed when i want to get fresh data from the amfi site.
thanks for your help.
-vin
Hi Chandoo, Thanks for the excel sheet for managing the MF at one place. It is awesome. I find the file is becoming very slow to work on it. Will it be due to refreshing each time? and background process? Request a solution please. Thank you in advance.
Namaste,
You have prepared a wonderful excel sheet for benefit of all of us. Many Thanks.
I cannot Find the fund: "Edelweiss midcap 150 momentum 50 index fund direct growth" in the excel sheet. Please help!