Track Your Mutual Fund Portfolio using Excel [India Only]
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 we have posted a file on tracking mutual funds using excel. Today we are going to release an upgrade for that file.
How the Mutual Fund Portfolio Tracker Works?
- We use Excel Web Queries (a powerful external data feature in excel) to get 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 data is delimited using ; as a separator, I have used some formulas (mainly FIND, MID and LEFT formulas) to split the text in to fund name and latest NAV.
- I have used fuzzyText UDF (user defined formula) so that we can search against this list even when you have a spelling mistake in the fund name. For more information see fuzzy text search using excel.
- In the main portfolio sheet, as soon as you type a fund name, we search against the list to see if any fund matches the one you bought. At this point, we use the fuzzyText UDF so that you can spell in anyway you want (as long as it closely matches with the fund name). Once a match is found, we show the latest NAV for that fund in the tracker worksheet. And of course, we use VLOOKUP to find the NAV.
- Rest is easy, you can figure out between sips of coffee.
- The file is protected, but there is no password. So go ahead and poke around it to learn how the whole thing works.
- Even though the file works for Indian Mutual Funds only, you can easily build a similar model for US or UK or Any other country. All you need is a public source of fund data and a little web query.
Changes from previous version
- The formulas are more robust. Earlier version (available here) has some limitations.
Selecting a fund is much more simpler. You need not scroll thru an insanely large in-cell dropdown. Instead, just type the fund name and thanks to fuzzyText UDF, the correct fund name will be found.
- I have updated the webquery properties, so that formulas get refreshed automatically.
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. But I use 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 future needs against my current holdings to see if I need to invest more.
What about you? What is your favorite way to keep 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 start saving early for your retirement
- Tracking Stock Quotes and Other investments using Google Spreadsheets
- Stock quotes in excel
- Household Budget Templates – Free Download
- More articles and howtos on personal finance using excel
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« How to Find Dates of Public Holidays using Excel
|Best of Pointy Haired Dilbert – 2009 »