How to track your mutual funds & stocks in google sheet?

Google Sheets is one of the easiest free tools to track your stocks, ETFs, and even mutual funds. With built-in finance functions like GOOGLEFINANCE, you can create a real-time portfolio tracker.

Below is a step-by-step method for both stocks and mutual funds.

1. Open Google Sheets

2. Track Stocks Using GOOGLEFINANCE Function

Google Sheets provides real-time and historical stock data.

Basic Formula

=GOOGLEFINANCE("NSE:TCS", "price")

This fetches the current price of TCS from the NSE.

Common Attributes You Can Track:

Attribute Formula Example What It Shows
Price =GOOGLEFINANCE("NSE:RELIANCE","price") Live price
52-Week High =GOOGLEFINANCE("NSE:INFY","high52") 52-week high
52-Week Low =GOOGLEFINANCE("NSE:INFY","low52") 52-week low
Market Cap =GOOGLEFINANCE("NSE:HDFCBANK","marketcap") Market cap
PE Ratio =GOOGLEFINANCE("NSE:TCS","pe") P/E ratio

3. Create a Stock Portfolio Table

Create headings like:

| Stock | Exchange | Quantity | Buy Price | Current Price | Value | Profit/Loss |

Then use formulas:

Current Price:

=GOOGLEFINANCE(B2, "price")

(Where B2 contains “NSE:TCS”)

Value:

=D2 * E2

Profit/Loss:

=(E2 - C2) * D2

Google Sheets updates prices automatically.

 Important Note: GOOGLEFINANCE Does Not Support Indian Mutual Funds Directly

Unlike stocks, Google Finance does not provide Indian mutual fund NAVs.

But you can still track mutual funds using one of these methods:

4. Track Mutual Funds Using AMFI (NAV API Method)

You can pull NAV automatically using AMFI data.

Step 1: Find Mutual Fund Code

Go to AMFI India NAV page and find the 6-digit scheme code
Example:

  • SBI Small Cap Fund (Direct) → 125354

Step 2: Use IMPORTXML to Fetch NAV

Use this formula in Google Sheet:

=IMPORTXML("https://www.amfiindia.com/spages/NAVAll.txt","//text()")

But this returns all data — so we filter using the scheme code.

Use FILTER Formula for Specific Fund

=INDEX(FILTER(IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt"),
SEARCH("125354", IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt"))),1)

This pulls the latest NAV for scheme code 125354.

 Easier Method (Recommended): Use Google Apps Script for Mutual Funds

Because mutual fund URLs update daily, Apps Script is more reliable.

If you want, I can generate a ready-to-use script.

5. Manual Mutual Fund Tracking (Simple Method)

If you don’t want formulas:

Create headings like:

| Fund Name | Units | NAV | Current Value | Gain/Loss |

Manually update NAV from:

  • Groww

  • Moneycontrol

  • Value Research

  • AMFI

Value:

=Units * NAV

Gain/Loss:

=(Current NAV - Purchase NAV) * Units

6. Track Portfolio Asset Allocation Automatically

Create categories:

| Asset | Investment | Current Value | Allocation % |

Then use:

Allocation %

=C2 / SUM($C$2:$C$10)

You get live charts showing:

  • Equity %

  • Debt %

  • Gold %

  • International exposure

7. Track Historical Stock Price Charts

You can create charts directly from Google Sheets:

Formula:

=GOOGLEFINANCE("NSE:TCS","price",DATE(2024,1,1),TODAY(),"DAILY")

Insert → Chart → Line chart
This shows historical price movements.

8. Use Portfolio Tracker Templates (Free)

If you want, I can generate a custom Google Sheets Portfolio Tracker Template that includes:

✔ Stock auto-price tracking
✔ Mutual fund NAV fetch
✔ Profit/Loss calculations
✔ Asset allocation charts
✔ SIP tracking
✔ Dashboard view

Just tell me:
Do you want me to create a Google Sheet template link for you?

Conclusion

Google Sheets is one of the most powerful free tools to track your:

  • Stocks (real-time via GOOGLEFINANCE)

  • Mutual funds (via AMFI data or manual updates)

  • SIPs

  • Asset allocation

  • Total portfolio value

  • Profit & loss

Leave a Comment