Skip to content

About

End-to-end mutual fund analytics platform — ETL pipeline, SQL database, performance metrics, and Power BI dashboard for 40 AMFI schemes.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

23 Commits

Folders and files

Repository files navigation

Mutual Fund Analytics Platform

A data engineering and analytics project which covers end-to-end data ingestion, cleaning, SQL database design, performance analytics, and interactive dashboards for 40 AMFI mutual fund schemes.


Project Overview

Domain Mutual Fund / Fintech
Data Source AMFI India, mfapi.in, NSE/BSE Public Data
Technologies Python, SQL, Power BI, Pandas, Matplotlib, Plotly, Streamlit
Schemes Analysed 40 Real Fund Schemes
Total Records 87,000+ rows across 10 datasets
Duration 7 days

What This Project Does

  • Ingests live NAV data from the mfapi.in REST API for 40 fund schemes
  • Cleans and validates 10 datasets (87,000+ rows) — handles missing values, date parsing, duplicate removal
  • Designs and populates a SQLite star schema database with 4 tables
  • Computes performance metrics: CAGR, Sharpe ratio, Sortino ratio, Alpha, Beta, Max Drawdown
  • Builds a composite Fund Scorecard (0–100) ranking all 40 schemes
  • Delivers a 4-page interactive Power BI dashboard with slicers and drill-through
  • Implements advanced risk analytics: VaR (95%), CVaR, Rolling Sharpe, Sector HHI
  • Provides a rule-based fund recommender based on investor risk appetite
  • Includes Monte Carlo simulation (5-year NAV projection) and Markowitz Efficient Frontier

Project Structure

mutual_fund_project/
├── data/
│   ├── raw/                        ← Original downloaded files
│   ├── processed/                  ← Cleaned, merged CSVs + analytics outputs
│   └── db/                         ← mf.db (SQLite, excluded from Git)
├── notebooks/
│   ├── 01_data_ingestion.ipynb     ← ETL + API data fetch
│   ├── 02_data_cleaning.ipynb      ← Data cleaning pipeline
│   ├── 03_eda_analysis.ipynb       ← EDA + 9 charts
│   ├── 04_performance_analytics.ipynb ← CAGR, Sharpe, Alpha, Beta, Drawdown
│   └── 05_advanced_analytics.ipynb ← VaR, cohort analysis, recommender, HHI
├── scripts/
│   ├── etl_pipeline.py             ← Master ETL script (auto-runs via Task Scheduler)
│   ├── live_nav_fetch.py           ← Fetches live NAV from mfapi.in
│   ├── compute_metrics.py          ← Computes CAGR, Sharpe, Drawdown for all funds
│   ├── recommender.py              ← Fund recommender by risk appetite
│   ├── monte_carlo.py              ← Monte Carlo NAV projection (5 years)
│   ├── markowitz.py                ← Markowitz Efficient Frontier optimisation
│   ├── email_report.py             ← HTML weekly performance report generator
│   ├── generate_report.py          ← PDF final report generator
│   └── generate_slides.py          ← PowerPoint presentation generator
├── sql/
│   ├── schema.sql                  ← CREATE TABLE statements
│   └── queries.sql                 ← 10 analytical SQL queries
├── dashboard/
│   ├── streamlit_app.py            ← Interactive web dashboard (Streamlit)
│   ├── page1_industry_overview.png
│   ├── page2_fund_performance.png
│   ├── page3_investor_analytics.png
│   └── page4_sip_market_trends.png
├── reports/
│   ├── Final_Report.pdf            ← 15-page project report
│   ├── Presentation.pptx           ← 12-slide presentation deck
│   ├── data_quality_summary.md     ← Data quality findings
│   └── charts/                     ← 12 exported PNG charts
├── data_dictionary.md              ← Column definitions + business context
├── requirements.txt                ← Python dependencies
├── .gitignore
└── README.md

Setup & Installation

1. Clone the repository

git clone https://github.com/5tack5mith/Mutual-Fund-Project.git
cd Mutual-Fund-Project

2. Install dependencies

pip install -r requirements.txt

3. Fetch live data

py -3.14 scripts/etl_pipeline.py

4. Run the Streamlit dashboard

py -3.14 -m streamlit run dashboard/streamlit_app.py

5. Rebuild the SQLite database

Run notebooks in order:

  • 01_data_ingestion.ipynb
  • 02_data_cleaning.ipynb

Datasets

File Description Rows
01_fund_master.csv 40 AMFI fund schemes with details 40
02_nav_history.csv Daily NAV prices 2022–2026 46,000
03_aum_by_fund_house.csv Quarterly AUM per fund house 90
04_monthly_sip_inflows.csv Monthly SIP industry data 48
05_category_inflows.csv Monthly inflows by fund category 144
06_industry_folio_count.csv Industry folio counts 21
07_scheme_performance.csv Fund performance metrics 40
08_investor_transactions.csv Investor buy/sell records 32,778
09_portfolio_holdings.csv Fund stock holdings 322
10_benchmark_indices.csv Nifty 50, 100, BSE SmallCap daily values 8,050

All data sourced from publicly available AMFI India publications and the mfapi.in REST API.


Database Schema

SQLite star schema with 4 tables:

dim_fund ──────┐
               ├── fact_nav
dim_fund ──────┤
               ├── fact_transactions
dim_fund ──────┤
               └── fact_performance
  • dim_fund — fund details (amfi_code PK, fund_house, category, expense_ratio)
  • fact_nav — daily NAV prices (amfi_code FK, date, nav)
  • fact_transactions — investor records (investor_id, amfi_code FK, transaction_type, amount)
  • fact_performance — metrics (amfi_code FK, return_1yr, sharpe_ratio, aum_crore)

Key Findings

  • SBI Mutual Fund manages ₹12.5L Cr AUM in 2025 — largest AMC in India
  • Monthly SIP inflows tripled from ₹11,517 Cr (Jan 2022) to ₹31,002 Cr (Dec 2025)
  • ICICI Pru Midcap tops the Fund Scorecard at 85.1 — best risk-return balance
  • Small Cap funds carry highest VaR (-2.39%) but also highest 1yr returns (64.88%)
  • 97.8% of investors show irregular SIP patterns — average gap of 64.9 days vs 30 expected
  • Banking (19.2%), IT (13.4%), Pharma (12.0%) account for 44.6% of equity portfolios
  • Industry folios doubled from 13.26 Cr to 26.12 Cr between 2022 and 2025

Bonus Challenges Completed

  • B1 — Scheduled ETL via Windows Task Scheduler (weekdays at 8 PM)
  • B2 — Streamlit web app as Power BI alternative
  • B3 — Monte Carlo simulation (5-year NAV projection with uncertainty bands)
  • B4 — Markowitz Efficient Frontier (portfolio optimisation for 5 funds)
  • B5 — Automated HTML email report generator

Tech Stack

Category Tool
Language Python 3.14
Data manipulation Pandas, NumPy
Visualisation Matplotlib, Seaborn, Plotly
Web dashboard Streamlit
Database SQLite + SQLAlchemy
Statistics SciPy
BI Dashboard Power BI Desktop
Reporting ReportLab, python-pptx
Version control Git + GitHub
Scheduling Windows Task Scheduler
API mfapi.in (free, no auth required)

License

This project is for educational purposes only as part of the Bluestock Fintech Data Analyst Internship. All data is sourced from publicly available AMFI India and mfapi.in sources. Not financial advice.

About

End-to-end mutual fund analytics platform — ETL pipeline, SQL database, performance metrics, and Power BI dashboard for 40 AMFI schemes.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages