Needle in the Haystack - The best-performing mutual fund

Mutual funds were supposed to solve a problem commoners face while investing - Not having enough knowledge or insights to identify the best stock/debt instrument to invest in, for best returns of course. However, it ended up creating the equivalent problem of its own and the ever elusive question - Which is the best mutual fund to invest in among the ten thousand odd mutual funds?

The answer changes depending on what data you look at.

• Sort by 10-year return → one set of funds wins.
• Sort by 5-year return → rankings change completely.
• Look at recent momentum → another list appears.
• Look at risk-adjusted returns → yet another answer emerges.

And once you start searching for answers from the "experts", discussions quickly move toward:

• Asset allocation
• Goal-based investing
• SIP discipline
• Diversification

All important topics — but the original question still remains: Among thousands of mutual funds, which ones are actually worth considering?

The Problem - Existing Solutions and Their Limitations

For reasons beyond my understanding, the omnipresent free hand of market always provides a solution but the one that is not perfect.

Platforms like - Value Research Online, Morningstar India, ET Money are very useful for researching funds, comparing a few schemes, checking returns and ratios, etc. But I found one important limitation  - They help compare selected funds, but do not fully help discover whether those are even the best funds to compare.

There are also excellent data-backed approaches like Freefincal but many advanced tools are paid, static, not automatically refreshable, difficult to customize.

And hence the quest to build an Open, Transparent, Free Forever, Refreshable, and Customizable mutual fund analysis system in Google Sheets. 

The Goal

The goal was simple - Build an open system that can fetch data for thousands of mutual funds, rank them using formulas of my choice, and allow complete customization of the analysis.

• Automatic refresh
• Transparent formulas
• Editable weights
• Risk-adjusted metrics
• Category-aware ranking
• Portfolio-aware tracking
• Most importantly - No black box.

The Solution

I built a Google Apps Script-based system that automatically creates and manages a complete mutual fund analysis setup inside Google Sheets. The system:

• Fetches NAV data automatically
• Processes thousands of funds
• Calculates return and risk metrics
• Ranks funds using configurable formulas
• Supports automatic refresh
• Highlights existing holdings

The scoring currently includes:

• Weighted annual returns
• CAGR-based scoring
• Sharpe ratio
• Sortino ratio
• consistency score
• Downside protection
• Momentum score
• Category percentile ranking
• A composite Final Score to compare funds across.

Key Limitation - One limitation of this system is that the analysis is primarily based on annual returns instead of rolling returns. Rolling returns would be more robust, but computing them across thousands of funds requires significantly more processing and complexity than a spreadsheet-oriented solution can comfortably handle.

This project intentionally prioritizes simplicity and always-free tool over institutional-grade analytics.

How to Setup

1. Using the Spreadsheet Directly

• Go to the Mutual Fund Analysis.
• File > Make a copy > Store in your Google Drive
• You will find a Quick Start Guide on the first sheet.
• Skip First-Time-Setup if you don't want to reset everything and keep the all the existing data.
• Start Refresh to fetch latest data.
• Explore as you wish.

2. Using the Code

• Download this Apps Script code.
• Create a blank Google Sheet in your Google Drive.
• Open Extensions → Apps Script.
• Paste the code & Save.
• Refresh the Google Sheet.
• You will see a new menu - MF Updater.
• Click MF Updater - First-Time Setup.
• You will see "Authorization required", click OK.
• You will see "Google hasn’t verified this app", click Advanced > Click "Go to Untitled project (unsafe)" > Select All > Continue
• This will grant the permissions required to run the scripts.

3. Getting the List of Funds

• I have curated an exhaustive list of equity funds. You can get these from Fund or Analysis sheets of my Mutual Fund Analysis.
• Get it from AMFI Website.

Request for Suggestions

This project is meant to be open and customizable. If you have suggestions around approach, formulas, scoring system, please do share your thoughts or updated code. Your contribution will make this system more robust.

Tweet

Save on Delicious

Pin It