How To Download Mutual Fund Nav Into Excel
Track Your Common Fund Portfolio using Excel [Republic of india But]
Excel is very good for keeping track of your investments. Due to its grid nature, you can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net Asset Values) to see how your investments are doing. A while back nosotros have posted a file on tracking mutual funds using excel. Today nosotros are going to release an upgrade for that file.
Download the common fund portfolio tracker excel workbook now.
[Download Excel 2003 uniform version here]
How the Common Fund Portfolio Tracker Works?
- We use Excel Web Queries (a powerful external information feature in excel) to become latest Mutual Fund NAVs for all the MFs in India. The list of funds along with their latest NAVs is published everyday at AMFI (The Association of Mutual Funds in India) at http://amfiindia.com/spages/NAV0.txt
- The information is delimited using ; as a separator, I have used some formulas (mainly FIND, MID and LEFT formulas) to split up the text in to fund name and latest NAV.
- I have used fuzzyText UDF (user divers formula) and so that we can search confronting this list fifty-fifty when yous have a spelling fault in the fund name. For more than information see fuzzy text search using excel.
- In the master portfolio canvass, as presently as you lot type a fund name, we search confronting the list to see if any fund matches the ane you bought. At this point, nosotros apply the fuzzyText UDF then that you can spell in anyway you want (as long as it closely matches with the fund proper name). Once a match is constitute, we show the latest NAV for that fund in the tracker worksheet. And of class, we utilise VLOOKUP to find the NAV.
- Balance is like shooting fish in a barrel, yous tin can effigy out between sips of coffee.
- The file is protected, merely there is no countersign. And then become ahead and poke effectually it to learn how the whole thing works.
- Fifty-fifty though the file works for Indian Mutual Funds only, you can hands build a similar model for US or UK or Any other state. All you need is a public source of fund information and a little web query.
Changes from previous version
- The formulas are more than robust. Earlier version (available here) has some limitations.
-
Selecting a fund is much more simpler. You need non ringlet thru an insanely large in-jail cell dropdown. Instead, just blazon the fund proper noun and thanks to fuzzyText UDF, the correct fund name will exist found. - I have updated the webquery properties, and then that formulas get refreshed automatically.
Download the mutual fund portfolio tracker excel workbook now.
[Download Excel 2003 compatible version here]
What is your favorite way to track investments?
I rely my bank's investment tracker tools to get a quick update on my mutual funds and shares. Simply I utilize excel to pull data from various sources and analyze it to optimize my portfolio. Using excel's financial formulas, I can easily find out CAGR or IRR on my investments is and compare it with other options. I also compare my futurity needs against my electric current holdings to see if I demand to invest more.
What about you? What is your favorite way to go along track of investments?
Related Excel Templates and Articles on Personal Finance
- Find out how much you need for retirement using Excel Goal Seek
- Why you should showtime saving early on for your retirement
- Tracking Stock Quotes and Other investments using Google Spreadsheets
- Stock quotes in excel
- Household Budget Templates – Free Download
- More than articles and howtos on personal finance using excel
Related articles:
| Written by Chandoo |
Exit a Reply
DOWNLOAD HERE
Posted by: annewhossible.blogspot.com
0 Comments