Free Excel Spreadsheet for Mutual Funds

#1
Currently I only invest in Mutual Funds and was looking around for a free Excel spreadsheet that could track all my Mutual Funds? However, I did not find any, and I developed one myself. Kindly check out the attached excel spreadsheet and please do give me your frank opinion/suggestions.

This spreadsheet is only for beginners like me. This version now has the capability to pick up live NAVs from valueresearchonline.com
 
Last edited:
#2
hi, this excel sheet is good one, but how to add additional m.f products and also update the nav's from valueresearch, can there add other mf products etc.
 

karthikmarar

Well-Known Member
#3
Hi Indianguru

Seems to be a nice one. Why "only for beginners"? Seems to be good enough for anyone with a mutual fund portfolio. Please do add some explanations on adding new funds etc. Also I see two set of purchased units, Amount and date columns....

Anyway it is very nice of you to share it with the forum. Thanks

regards

karthik
 
#4
Thanks for your comments. There are many features that I would like to add, but it's the usual excuse - lack of time.

If you want to add a Mutual Fund say under the category "Equity Diversified", just insert a row between 2 companies and copy the relevant formulas. The second one is slightly difficult to explain, but I will try -

a. Open your Excel portfolio sheet and select a cell where you would like to import the live NAV sheet from www dot valueresearchonline dot com

b. In Excel select Data/Import External Data/New Web Query. In the dialog box that pops up, give the url as www dot valueresearchonline dot com

c. When Excel loads the page, enter your Username and Password and Login

d. Click on Portfolio and on the page that displays select Views: Fund's NAV High Low

e. You will see a table. Click the yellow arrow box next to it. When you do, Excel turns the arrow into a check mark and changes the yellow to green. Next, click the Options button in the top-right corner of the dialog box. In the Web Query Options box, click "Full HTML formatting" (unless you're a plain-text kind of person). Including the HTML formatting lets your table keep any Web links within it, and the table will be prettier. Click OK to return to the New Web Query dialog box, then click Import.

f. Next, Excel presents the Import Data dialog box, which lets you specify the location for your data in the current worksheet or choose a new worksheet. Once you're satisfied with the destination, click Properties. The important setting here is "Refresh control" in the External Data Range Properties dialog box. If you want Excel to refresh your data automatically, click "Refresh every" and choose a frequency. Unless you're going to monitor your data constantly, the default 60 minutes is a good option. If you prefer to update only when you open the file, choose "Refresh data on file open." If you don't want automatic updating, leave both boxes unchecked. (You can always update manually.) Click OK to lock in the settings.

g. Back in the Import Data dialog box, click OK to import the valueresearchonline info. Excel retrieves the table and opens the External Data toolbar, which you can use to make changes to your query.

That's all there is to it. As long as you have a live Internet connection, Excel automatically updates the information according to the interval you set in the External Data Range Properties dialog box.

Hope this helps.

sangsham said:
hi, this excel sheet is good one, but how to add additional m.f products and also update the nav's from valueresearch, can there add other mf products etc.
 
#5
I have just answered your previous question in the above post. I have purchased from a Mutual Fund company thrice, every 6 months. To track the prices etc at different intervals, I have provided those columns.

Thanks for your kind comments.

karthikmarar said:
Hi Indianguru

Seems to be a nice one. Why "only for beginners"? Seems to be good enough for anyone with a mutual fund portfolio. Please do add some explanations on adding new funds etc. Also I see two set of purchased units, Amount and date columns....

Anyway it is very nice of you to share it with the forum. Thanks

regards

karthik
 
#6
dear indianguru, you have really done the lot efforts for doing this excel sheet. some of queries you replied, but still there is problem which i face and could not get in detail. It will be highly appreciated... if you suggest the procedure for add new funds.. i have tried much as per u r suggestion, but problem comes in your
e) You will see a table... but could not see the yellow arrow box next to it... where it is located on.. and could not get about excel turns yellow to green. pls help for the same
so, I could not add new fund... and get data. I am already user of the valuereseach and having created my portfolio there. Thanks in advance and help pls. SHAM
 
#7
You must have Microsoft Excel 2002 or above. Kindly let me know which version of Excel you are using.

sangsham said:
dear indianguru, you have really done the lot efforts for doing this excel sheet. some of queries you replied, but still there is problem which i face and could not get in detail. It will be highly appreciated... if you suggest the procedure for add new funds.. i have tried much as per u r suggestion, but problem comes in your
e) You will see a table... but could not see the yellow arrow box next to it... where it is located on.. and could not get about excel turns yellow to green. pls help for the same
so, I could not add new fund... and get data. I am already user of the valuereseach and having created my portfolio there. Thanks in advance and help pls. SHAM
 

Similar threads