Automated NAV updates for Indian mutual fund portfolio tracking in Google Sheets

Other Questions Google Sheets
1

I want to build a mutual fund tracker that automatically refreshes NAV prices so my portfolio values stay current without manual updates.

I’ve been experimenting with several Google Sheets functions to pull live data:

IMPORTXML
QUERY
INDEX
MATCH

Here’s what I’m working with:

=QUERY(IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt"),"SELECT * WHERE Col1 CONTAINS '"&B2&"'")
=INDEX(ARRAYFORMULA(SPLIT(DataSheet!A:A,";")),MATCH(C2&"*",DataSheet!A:A,0),6)

My goal is to create a dashboard where fund prices update automatically so I can monitor my investment performance in real time.

2

The AMFI approach works really well once you nail the data parsing. I’ve been running a similar setup for two years and found IMPORTDATA + REGEXEXTRACT beats QUERY most of the time. The tricky part is handling those semicolon-separated formats properly. What saved me was setting up a helper sheet to process the raw AMFI data first, then pulling clean data into my main tracker. Quick tip: AMFI updates around 8 PM on trading days, so throw in a timestamp column to track your last refresh. Big advantage over GOOGLEFINANCE? You get every Indian mutual fund, not just Google’s limited selection.

3

yo, i tried GOOGLEFINANCE for my funds and it really simplifies things! just =GOOGLEFINANCE(“MUTF_IN:”&fund_code) is all you need. way better than those AMFI txts, trust me!

4

I ditched IMPORTDATA for Google Apps Script on my MF tracker after too many reliability headaches. The AMFI text file craps out during market hours and throws #N/A errors everywhere. Now my script runs every 30 minutes, grabs the data, parses it clean, and dumps it into a separate sheet. Way better error handling, plus you can build in retry logic when AMFI’s servers are being slow. Performance is night and day too - no more sitting there watching IMPORTXML churn through thousands of rows every time you open the thing. Takes maybe an hour to set up but you’ll thank yourself later.

5

To make your mutual fund tracker update NAVs automatically, you just need to do the following:

  1. Create a new sheet in your file (you can name it “NAV” or anything else you like).
  2. Add two small scripts that will pull the latest NAV data from the AMFI website into this sheet.
  3. In your main portfolio sheet, use the VLOOKUP formula to fetch the current NAV for each fund based on its name.

Important: Make sure the fund names in your portfolio match exactly with the official names used by AMFI. If the names don’t match, the VLOOKUP won’t work correctly.