Free Excel Spreadsheet for Mutual Funds

IndianGuru · #1 18th September 2005, 11:32 AM

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

sangsham · #2 12th October 2005, 08:41 PM

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 · #3 12th October 2005, 09:03 PM

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

IndianGuru · #4 13th October 2005, 07:44 PM

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.

Quote:

Originally Posted by sangsham

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.

IndianGuru · #5 13th October 2005, 07:46 PM

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.

Quote:

Originally Posted by karthikmarar

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

sangsham · #6 14th October 2005, 09:41 PM

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

IndianGuru · #7 14th October 2005, 09:50 PM

You must have Microsoft Excel 2002 or above. Kindly let me know which version of Excel you are using.

Quote:

Originally Posted by sangsham

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

sangsham · #8 14th October 2005, 10:03 PM

i am using excel 2000, whethere it not works there?

IndianGuru · #9 14th October 2005, 10:09 PM

I am sorry, you must have Excel 2002 or any higher version. It does not work for Excel 2000. Kindly upgrade your version.

Quote:

Originally Posted by sangsham

i am using excel 2000, whethere it not works there?

maali_vada · #10 18th December 2005, 01:15 PM

thanks for your work

i am also initerested to do this kind of work

i will contact later

my email id malai_vada@yahoo.co.in

Similar Threads for: Free Excel Spreadsheet for Mutual Funds
Thread	Thread Starter	Forum	Replies	Last Post
Indian Govt. allows Private pension funds to invest in equity market	TATrader	Current Affairs	2	5th September 2009 05:12 PM
FREE Stock Charts with Excel	TATrader	Technical Analysis	6	2nd February 2009 05:57 PM
covert data from excel spreadsheet to metstock format	veneet	Data Feeds	10	30th November 2005 01:25 PM
Hedge funds aggressively buy unlisted firms	TATrader	Current Affairs	0	9th March 2005 08:43 AM
Indian Investor Getting out of Mutual Funds?	TATrader	Current Affairs	0	2nd December 2004 03:45 PM

The Following 4 Users Say Thank You to IndianGuru For This Useful Post:
satishyard (22nd August 2008), scs_r (13th September 2008), vish_therock (30th August 2009)