Back to work
Live in production2025–2026Product Manager & Full-Stack Engineer (pipeline, database, frontend, infra)80% complete

Fundalysis

Indian mutual fund analytics: 14 years of monthly portfolios from 37 fund houses, ~72M rows in a 15 GB Postgres, NAVs since 2006, what each fund paid for each share, and an MCP server for AI agents.

Summary

Fundalysis turns the monthly portfolio disclosures that every Indian asset management company publishes on its own website, each in its own Excel layout, into one queryable history. It covers every holding of 1,951 schemes from 37 fund houses from September 2012 to today (169 monthly snapshots, 16,000+ securities), tracks every position change month over month, works out the average price each fund paid for each share through splits, bonuses, rights, mergers and demergers, and adds 37M daily NAVs since 2006, AMFI's AUM and returns, NSE/BSE and foreign share prices, and fund manager histories. The data lives in a 15 GB PostgreSQL 18 database (54 tables, ~72M rows, 160 indexes) fed by a ~20k-line Python pipeline and nightly jobs on AWS. A Next.js 16 app serves scheme, stock, fund house, manager and sector pages, and a 17-tool MCP server exposes the same service layer to AI clients.

Target user

Indian retail investors, analysts and research teams who want to see what fund managers actually hold and trade, month by month: which funds bought a stock, at what average price, who runs each scheme and how it performed, without a paid terminal. Plus AI agents (Claude, ChatGPT, Cursor) that call the same data over MCP.

§ 01Stack
01Primary
Next.jsReactTypeScriptPostgreSQLPrismaPython
02Infrastructure
DockerAWS ECSAWS ECRAWS CodeBuild-hosted GitHub Actions runnerAWS SSM Parameter StoreAWS S3AWS CloudWatch logs + metric alarm to SNSTerraformnginx-proxyDigitalOcean managed PostgreSQLpg_cron
03Integrations
31 AMC websitesAMFINSE and BSE archivesShinobiDataYahoo Finance via curl_cffiECB FX ratesFyersModel Context ProtocolZalo
04UI / Frontend
Tailwind CSSshadcn/uiRechartsTanStack Table + VirtualMotionZod
§ 02Key features
  1. 01

    Built a monthly ingestion pipeline (31 scripted AMC downloaders plus hand-fetched houses) that cleans and parses 110,000+ disclosure files (29.9 GB of Excel, PDF and XML, archived in a versioned S3 bucket) into 4.9M+ normalized holdings across 1,951 schemes and 16,000+ securities, covering September 2012 to today.

  2. 02

    Precomputed 5.2M month-over-month position changes (new, added, reduced, sold, exited) and six monthly rollup tables (fund x sector, house x stock, house x sector, house x industry, stock, house), so 'which funds bought this stock last month' is an indexed read, not a join over 14 years of holdings.

  3. 03

    Computed what each fund paid on average for each share it holds (4.7M rows) from 10.9M daily NSE, BSE and foreign prices, carrying cost through 61,900 corporate actions: exact split factors, bonuses, rights at issue price, mergers, and demergers split by market value on the ex-date. 98%+ of cost is priced at actual exchange closes.

  4. 04

    Loaded 37.2M daily NAVs for every AMFI scheme code since April 2006 and 1.7M AMFI return figures, with an SQL return function filling the gaps AMFI leaves, and nightly NAV and price jobs on AWS that post a run summary to the team chat.

  5. 05

    Built fund manager histories from scheme summary and SAI filings: 498 managers, 4,752 tenure records, career moves between houses, AUM by lead manager, performance against peers, and 370 matched LinkedIn profiles, with filings always winning over web data.

  6. 06

    Shipped an MCP server with 17 tools (search, schemes, holdings, changes, allocation, average cost, NAV and AUM history, returns, managers, industries, share prices) at /mcp, calling the same service functions as the website, with a call log for usage analytics.

  7. 07

    Kept every judgment call auditable: 982 stock merges, 120 scheme merges, 110 dated renames and every fund-to-AMFI link are logged and reversible, and each data decision carries a dated plain-English note that the frontend shows next to the figure.

  8. 08

    Wrote the monthly runbook (17 steps in 3 phases, with a run log) and an anomaly checker, so a month's data load is a repeatable process instead of tribal knowledge.

§ 03Hardest problems
  1. Each of 37 fund houses publishes its own Excel layout, and the layouts change over the years: header rows move, sections get renamed, units switch between lakh and crore, % of NAV sits next to a derivative-exposure column that looks just like it. A coverage audit found the parser silently dropping whole sections (one house's '(Note 1)' header emptied 23 schemes for almost three years; REITs, InvITs, foreign shares and arbitrage legs were missing across several houses). Fixed the parser with regression cases for each failure, then reloaded all history additively: the fill only inserts, never updates or deletes, and tags every row with a run id per house so any reload can be traced or undone.

  2. A naive average cost shows a fake 50% loss on the day of a 1:1 bonus and fake trades around every split. The cost engine reads each fund's own two consecutive reports, applies the exact split factor between them, buys rights shares at their issue price up to the entitlement within 90 days, moves 100% of cost on a merger, and splits cost on a demerger by each company's market value on the ex-date, the way fund valuation policies allocate it. Two independent audits of the MCP answers found the false losses, and the rule set was rebuilt from them.

  3. The laptop's upload to the managed database ran at 130-200 KB/s and the machine slept on battery, killing multi-hour loads. The holding-changes rebuild was moved into the database itself: the script hands its SQL to pg_cron as one job in one transaction, so a dropped connection can never leave half-written data. Bulk writes go through psql COPY in 250-fund batches, and the long nightly and on-demand jobs moved to a CodeBuild-hosted GitHub Actions runner next to the data.

  4. Production shares a 25-connection managed Postgres with other products, so each process gets exactly one connection and there is no Redis (a deliberate cost choice). Profiling showed the cold reads were the problem, not the queries: one sector page touched ~47k pages of the AMFI AUM table. Wide composite indexes on the hot rollups, reads bounded to the months a view needs, a search index rebuilt in the background, managers' schemes fetched on open and links built in the browser took sector pages from 15-32 s to 1.3-5.8 s on first load, a fund manager's full history from 7.1 s to 0.55 s, and one view from 242 ms to 84 ms. One widened index made things slower (340 to 726 ms) and was reverted.

§ 04What I learned
  • L01
    Measure before indexing.

    The same index-widening that cut one table's cold reads by 10x doubled another's latency, because it changed the access path from one probe per code to a range scan. Every index change shipped with a before/after number, and the one that lost was rolled back.

  • L02
    Make data loads additive and auditable.

    'Insert only, tag every row with a run id' turned the scariest operation, reloading 14 years of history into a live database, into a routine one. The same rule applies to merges and renames: a logged, reversible row beats an ad-hoc UPDATE every time someone asks why a number changed.

  • L03
    Push the work to where the data is.

    Moving the rebuild into pg_cron and the long jobs onto a cloud runner fixed both the slow link and the half-written-data risk in one move, without adding any infrastructure that needs its own upkeep.

  • L04
    One service layer, many front doors.

    Pages, the public API and the 17 MCP tools all call the same functions, so adding the AI surface needed no second copy of any query.

§ 05By the numbers
database size gb
15
database rows approx
72500000
database tables
54
database indexes
160
prisma models
53
sql migrations
47
fund houses with portfolios
37
schemes with portfolios
1951
securities
16160
monthly snapshots
169
holdings rows
4953514
holding changes rows
5191935
nav rows
37248872
stock price rows
10886247
average cost rows
4721520
corporate actions
61900
amfi return rows
1742717
raw source files
110378
raw data gb
29.9
scripted amc downloaders
31
fund managers
498
manager tenures
4752
mcp tools
17
page routes
18
api route handlers
15
github actions workflows
4
typescript lines
44470
python lines
20479
test files
36
commits
733
merged prs
35