ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
MF Analysis Tool — User Guide
2
Automated mutual fund scoring for the Indian equity market · Data: mfapi.in (AMFI) · No manual data entry required
3
4
 0 · Creator & Disclaimer
5
 Created by: Ravi S Ghosh | Web: www.ravisghosh.com | Email: mail@ravisghosh.com
6
7
 DISCLAIMER: This tool is for personal analysis only. I am not a financial adviser. Mutual fund investments are subject to market risks. Use this data at your own discretion; the creator assumes no liability for any financial losses.
8
9
 1 · Quick Start
10
 Follow these steps the first time you use this tool:
11
12
 Step 1 · Run First-Time Setup
13
 Click 📈 MF Updater → ⚙️ First-Time Setup to create all required sheets and this Guide.
14
15
 Step 2 · Add your funds
16
 Open the Funds sheet. Add or remove fund names — one per row. Names can be informal; the script fuzzy-matches to official AMFI names.
17
18
 Step 3 · Start the refresh
19
 Click 📈 MF Updater → 🚀 Start / Resume Refresh. Runs in batches of 50 funds every 1 minute. For 4,000 funds, first run takes 2–3 hours.
20
21
 Step 4 · Watch progress
22
 Open the Progress sheet. Each fund's status appears as it is processed: ✅ computed, ⚠️ review needed, ❌ failed.
23
24
 Step 5 · View results
25
 Open the Analysis sheet. Funds appear incrementally, ranked by Final Score. ▶ columns are formula-driven — edit weights in row 1 to instantly re-rank without re-fetching.
26
27
 Step 6 · Set daily refresh
28
 Click 📈 MF Updater → ⏰ Enable Daily Auto-Refresh for automatic 6 AM updates. Already up-to-date funds are skipped instantly.
29
30
31
 2 · How To Use the Sheet
32
 Use these strategies to filter and identify high-quality investment opportunities:
33
34
 Longevity · Filter by Years Active
35
 To ensure stability, filter a specific year column (e.g., 2020 for 5 yrs, 2015 for 10 yrs, etc.) and uncheck '--'. This hides funds without a sufficiently long track record.
36
37
 Peers · Sort by Category Rank
38
 Sort by 'Category Rank ▶' (Z→A) to instantly identify funds that are outperforming their specific AMFI category peers.
39
40
 Alpha · Sort by Final Score
41
 Sort by 'Final Score ▶' (Z→A) to view the top-rated funds across the entire 6-factor weighted model.
42
43
 Selection · Filter by Fund Type
44
 Filter the 'Category' column (e.g., Small Cap or Flexi Cap) to compare and analyze only the top-tier funds within your target asset class.
45
46
 Analysis · Talk to AI
47
 Download the final filtered sheet with your chosen funds, delete all sheets except analysis and holdings, and upload in any Gen AI tool to further analyze.
48
49
 3 · Sheets Reference
50
51
SheetPurpose & notes
52
FundsYour fund universe. One fund name per row. The script fuzzy-matches to official AMFI names. Add new funds any time and run a refresh.
53
54
MappingMaps each name to an AMFI scheme code. Col C = Scheme Code (drives all NAV fetches). Col G = Expense Ratio — fill from AMFI for the expense penalty in scores. ✅ = high-confidence, ⚠️ = review.
55
56
ReturnsOne row per fund. Computed from live NAV: annual returns % (2011 onward), TTM, CAGR (3/5/10/15/20 Yr), Std Dev, Sharpe, Sortino. Updated incrementally.
57
58
AnalysisRanked view. ▶ columns are Google Sheets formulas — edit weights in row 1 to re-rank instantly. Sorted by Final Score after each run.
59
60
Holdings ✎Fill cols A (Folio) and B (Fund Name). Auto-fills all score columns with lookup from Analysis sheet.
61
62
ProgressLive batch log. Row 3 = current run status. Rows 6+ = per-fund results. Green = ✅, amber = ⚠️, red = ❌.
63
64
GuideThis sheet. Re-generate via 📈 MF Updater → 📖 Regenerate Guide Sheet.
65
66
 ✎ = maintained manually by you
67
68
 4 · How Data is Fetched
69
70
 Source: mfapi.in — free AMFI API, updated daily. No API key required. Raw NAV is never stored — fetched in memory, computed, then discarded. Only the ~35-column result row is written to Returns.
71
72
 SKIP mode
73
 Updated today. No API call — completes in milliseconds.
74
75
 PARTIAL mode
76
 Row exists, updated earlier this year. Fetches only last 13 months (mfapi.in ?startdate=). Refreshes TTM, current-year return, risk metrics. Prior years preserved.
77
78
 FULL mode
79
 No row yet, or row from a prior year. Fetches complete NAV history and computes all metrics from scratch.
80
81
82
 5 · Scoring Formulas
83
84
 Annual Return Weights
85
 Each year gets a linear ramp weight from FIRST_YEAR onward. TTM always gets the highest single slot. Edit weights directly in row 1 of the Analysis sheet — all ▶ columns recalculate instantly.
86
87
 weight[i] = (i + 1) / sum(1, 2, 3, …, N+1) where N = number of complete years, slot N+1 = TTM
88
89
 Weighted Annual Return ▶
90
91
 Wtd Annual = EXP( SUMPRODUCT(weights × LN(1 + returns)) / SUMPRODUCT(weights) ) − 1 − Expense Ratio
92
93
 Calculates the weighted geometric mean of annual returns using natural logarithms (LN) to accurately reflect compounding. ISNUMBER() guards ensure missing years (--) are skipped. Expense ratio is subtracted at the end.
94
95
 Weighted CAGR ▶
96
97
 Wtd CAGR = SUMPRODUCT(ISNUMBER(cagrs) × cagrs × cagr_weights) / SUMPRODUCT(ISNUMBER(cagrs) × cagr_weights)
98
99
Period · WeightRationale
100
3Yr (30%)Most important — recent compounding track record