← Back to Curriculum

Intermediate

Building a Portfolio Tracker Using Google Sheets and APIs

A hands-on build course for investors who have outgrown broker dashboards and scattered app screens. You will design and build one Google Sheets tracker that holds your entire portfolio: NSE and BSE stocks priced live with GOOGLEFINANCE, mutual fund NAVs pulled from AMFI and mfapi.in, and FDs, PPF, EPF and SGBs tracked alongside. You will build a clean transaction ledger from your Zerodha tradebook and CAMS or KFintech CAS, compute FIFO cost, realised and unrealised P&L, and XIRR the right way, then benchmark against the Nifty 50 TRI. Finally you will automate it with Google Apps Script: daily snapshots, API calls, alerts, and a dashboard with allocation, STCG and LTCG views and rebalancing flags. Built for experienced retail investors, mutual fund investors and salaried professionals who want a tracker they fully understand and control.

Google SheetsGOOGLEFINANCEMutual Fund NAV DataAMFI and mfapi.inGoogle Apps ScriptBroker APIsXIRRFIFO Cost BasisBenchmarkingAsset Allocation DashboardCapital Gains TrackingRebalancing

MODULES

6

DURATION

4 Hours

TRACK

Stock Market Basics

What You'll Master

Design a tracker around a transaction ledger instead of a static holdings list

Pull live NSE and BSE prices, index levels and history with GOOGLEFINANCE and handle its limits

Fetch mutual fund NAVs from AMFI and mfapi.in and track FDs, PPF, EPF and SGBs in the same sheet

Import trades from a Zerodha tradebook and CAMS or KFintech CAS without double counting

Compute FIFO cost, realised and unrealised P&L, and portfolio XIRR correctly

Benchmark your returns against the Nifty 50 TRI using the same cash flows

Automate daily snapshots, API calls and alerts with Google Apps Script

Build a dashboard with allocation, STCG and LTCG exposure and rebalancing flags

Access Level

LEARNER

Start Learning Now →Sign in to resume

Everything included

Full Text Playbooks

Actionable Exercises

Mobile Reading Mode

Lifetime Updates

Curriculum Breakdown

Chapter 1: Designing the Tracker Before You Build It

3 Lessons

Why Build Your Own Tracker When Console, Kuvera and INDmoney Exist10 min read

Tracker Architecture: Ledger, Holdings, Prices and Dashboard12 min read

Setting Up the Workbook: Tabs, Named Ranges and Data Validation12 min read

Chapter 2: Live Stock Prices With GOOGLEFINANCE

3 Lessons

GOOGLEFINANCE for NSE and BSE: Ticker Formats and Attributes12 min read

Index Levels, Historical Prices and Currency Conversion12 min read

Delays, Errors and Missing Tickers: Building Fallbacks That Hold Up11 min read

Chapter 3: Mutual Funds, Fixed Income and Other Assets

3 Lessons

Pulling Mutual Fund NAVs From AMFI With IMPORTDATA12 min read

Scheme History From mfapi.in Using Apps Script13 min read

Tracking FDs, PPF, EPF, SGBs and Other Manual Assets12 min read

Chapter 4: The Transaction Ledger and Returns Math

4 Lessons

Building the Ledger From Your Zerodha Tradebook and CAS13 min read

FIFO Cost, Realised P&L and Unrealised P&L14 min read

Absolute Return vs CAGR vs XIRR: Getting Portfolio Returns Right13 min read

Benchmarking Against the Nifty 50 TRI With the Same Cash Flows12 min read

Chapter 5: Automation With Apps Script and APIs

4 Lessons

Apps Script for Investors: Your First Custom Function12 min read

Daily Portfolio Snapshots With Time-Driven Triggers12 min read

Broker APIs: What Kite Connect Can and Cannot Do for a Tracker13 min read

Price and Drawdown Alerts by Email11 min read

Chapter 6: The Dashboard and Keeping It Running

4 Lessons

Allocation Charts: Asset Class, Sector and Market-Cap Breakdown12 min read

The Tax View: STCG, LTCG and the Rs 1.25 Lakh Exemption13 min read

Rebalancing Flags Against Your Target Allocation11 min read

Maintaining the Tracker: Corporate Actions, Data Hygiene and Backups12 min read