u/Choice-Theme-821

My portfolio is on Zerodha. My wife's is on Groww. My dad has a physical demat from 2004 linked to some broker he barely remembers the name of. My mom has PPF and a couple of LIC policies where the paperwork is somewhere in a folder nobody's opened in years.

So when someone asks "what are we actually sitting on" my answer is always some version of hold on, let me check four apps and a folder of old statements. And half the time those numbers are three months stale anyway because nobody's updated since the last time someone asked.

I tried getting everyone to just tell me their holdings once so I could add it up myself. Got maybe 60% compliance. My dad forgot he still had two mutual funds from a 2016 SIP that had been quietly auto-debiting for years.

When I finally did piece the whole thing together properly, the number was way off from what any of us assumed. Everyone had been mentally rounding down the old stuff they'd forgotten about and rounding up the recent stuff they check obsessively. The two errors don't cancel out.

Is this just my family or is this basically what everyone's finances look like once you have more than one earning member and more than one broker? How are people with scattered accounts actually staying on top of this?

If you track your portfolio in Google Sheets, you've probably noticed GOOGLEFINANCE works for stocks but returns nothing for mutual funds.

Here's the workaround I use.

AMFI publishes a plain-text file with NAVs for every registered mutual fund in India, updated every business day by 11pm. The URL is always the same:

https://www.amfiindia.com/spages/NAVAll.txt

Step 1: Pull the file into Google Sheets:

In a new sheet (call it NAV_Raw or similar), put this in cell A1:

=IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt")

This imports the entire AMFI NAV list — around 10,000+ scheme rows.

Step 2: Understand the format:

Each row looks like this:

120503;HDFC Top 100 Fund - Growth Option;HDFC Mutual Fund;58.123;58.456;31-Dec-2024

The columns separated by semicolons are:
Scheme Code ; Scheme Name ; AMC Name ; Net Asset Value ; Repurchase Price ; Date

Step 3: Look up NAV by scheme code:

Once you know your scheme code (find it on AMFI's website under "Know Your Scheme"):

=IFERROR(VALUE(TRIM(INDEX(SPLIT(VLOOKUP(scheme_code, NAV_Raw!A:A, 1, FALSE), ";"), 0, 4))), "Not Found")

This finds the row with your scheme code and extracts column 4 (the NAV).

Cleaner approach using a helper column:

In NAV_Raw, add a column B: =SPLIT(A1, ";") — this separates each field.

Then in your main sheet: =VLOOKUP(scheme_code, NAV_Raw!A:E, 4, FALSE)

Limitations:

- IMPORTDATA refreshes every ~1 hour in Google Sheets (not truly real-time)

- Some older/wound-up schemes may be missing

- The file is large (~2MB) so the sheet takes a few seconds to load

This works without any API key, without any add-on, and without paying for anything. The AMFI file is public by regulatory requirement.

If anyone wants their specific scheme code search on mfapi.in, easiest lookup I've found.