In one sentence: this document describes exactly what the workbook and the updater must do — every tab, every number, every data source — so that anyone could rebuild NetWorth from scratch, in any language, without seeing the code.
Version: covers v1.0 – v1.4 · Status: normative. Reverse‑engineered from the original template and extended with the approved features in PLAN.md.
This document is the product. The Python in src/networth/ is its
reference implementation; a port in any other language is conformant if it
satisfies this spec. Where the workbook and this spec disagree on a cosmetic
detail, this spec wins. When behaviour changes, the spec changes in the
same commit.
| You want to… | Go to |
|---|---|
| Understand the design rules | §1 Scope & principles |
| Know what every tab/column is | §3 Workbook specification |
| Fetch or parse a data source | §5 Data contracts |
| Reproduce a calculation | §6 Algorithms (pseudocode) |
| Know what the updater does, in order | §7 Updater behaviour |
| Ship it | §8 Packaging · §9 Portability checklist |
Section numbers are stable references — code comments cite them (e.g. "SPEC §6.10").
- Local-only. All computation happens on the user's machine. Network access is limited to HTTPS GETs of public market data (§5). No server, no account, no telemetry, no upload of any user data, ever.
- Excel workbook as UI and datastore. The user's only interface is one
.xlsxfile. They enter holdings into input cells and may freely add, delete and sort data rows. Nothing in the flow may break because of normal data entry. - The workbook is a build artifact. A generator program produces the entire workbook from code; an updater refreshes it via the round-trip model (§7). Maintainers never hand-edit a shipped workbook's structure.
- Non-developer end users. Refreshing prices must be a double-click action on both Windows and macOS, with no runtime dependencies beyond the shipped executable.
- Deterministic & testable. Given the same inputs (user rows + fetched data + date), generation is reproducible.
- Input cell/column — user-entered; must round-trip unchanged through an update. Visually distinct (§3.2).
- Computed column — in-sheet Excel formula; recalculates live in Excel.
- Updater-written value — plain value computed by the updater at run time (prices, NAVs, XIRR); goes stale until the next run.
- Owner / Person — a family member name; the partitioning key of every holding row.
- Master sheet — machine-managed lookup list (MF_Master, Stock_Master, and in v1 Bank_Master); never edited by hand.
The generator takes a configuration (defaults in parentheses):
| Key | Meaning |
|---|---|
persons |
ordered list of family member names (sample: Amit, Priya, Rahul); max 10 shown on the Dashboard matrix |
locale.date_format |
display format for dates (dd-mm-yyyy) |
inflation_default |
Dashboard inflation input default (7 %) |
expected_return_default |
v1: Dashboard expected-return input default for the FY-end estimate (10 %) |
row_budgets |
max data rows per sheet: Equity 137, MutualFunds 60, MF_SIP 500, FixedDeposits 100 (v1.7.2 — was 50; families ladder many FDs), PPF/Bonds ≥ 30, By Scrip 150 (v1.7.1 — auto-synced, must hold every distinct held ISIN) |
sample_data |
whether to include the fictional sample rows (on for the released template) |
Person names appear in three places that must stay consistent: the Dashboard
matrix input cells, one per-person sheet each, and the person columns of
By Scrip. The generator derives all three from persons.
Every per-class surface is derived from ONE ordered registry; adding an asset class means adding a registry row plus its sheet writer/reader/computes — never editing the Dashboard/person/History logic:
| Field | Meaning |
|---|---|
key |
stable identifier; the per-class attribute on XIRR/History records |
label |
header text everywhere (Dashboard, person sheets, History, Settings) |
value_col / owner_col |
the SUMIFS ranges the Dashboard/person totals read |
sheets |
sheet group hidden together when the class is off |
person_rows |
data rows of the class's person-sheet block |
default_enabled |
new-workbook default (classic five = Yes; later classes = No) |
has_xirr |
blank the allocation-table XIRR cell when false (e.g. Cash) |
Registry order since v1.4.3 (12 classes — Settings rows 4–15 exactly):
Equity (sheets: Equity, By Scrip, Dividends; 40 person rows) → Mutual Funds
(MutualFunds, MF_SIP; 20) → Fixed Deposits (FixedDeposits; 15) → PPF (PPF,
PPF_Ledger; 10) → EPF (EPF; 10; default off) → Bonds (Bonds; 15) → Gold &
Silver (Gold_Silver; 10; default off) → NPS (NPS; 10; default off) →
Property / Cash / Insurance / Other (all on the shared Manual_Assets sheet
via class_filter; no person block; default off; Cash has
has_xirr = false). "Property" was labelled "Real Estate" before
v1.4.3 — readers accept the old label wherever labels are matched
(Settings rows, Manual_Assets Class cells, the History header, the
allocation table) so old workbooks read seamlessly.
Reference sheets (v1.4.3): the four masters (MF_Master, Stock_Master, Bank_Master, NPS_Master) plus the Corporate_Actions audit tab form a fixed REFERENCE set whose visibility is driven solely by the Settings "Reference lists" switch (§3.14), hidden by default. Every dropdown and INDEX/MATCH formula resolves against hidden sheets, so nothing breaks — the tabs are simply out of a first-time user's face.
Enablement (normative — CHANGED in v1.4.3): the user's Settings choice
wins: shown = enabled, rows or no rows. A class switched off is hidden
and excluded from every displayed number — Dashboard matrix and
allocation, person sheets, Projection / FY-expected (§6.8), the portfolio
XIRR (§6.2), and new History snapshots record 0 for it (§6.11). Data is
never deleted: its sheets are hidden, never omitted — openpyxl reads
hidden sheets, formulas keep resolving, and flipping Yes brings everything
(and its numbers) back. Awareness is mandatory whenever an off class holds
rows: the Dashboard carries a one-line amber notice (merged I1:P1 —
Hidden, not counted: <labels> — switch on in Settings to include.) and
the updater prints one matching summary line naming each such class with
its measured value. Surfaces driven by the enabled set: Dashboard matrix
columns (Total and Expected-@-FY columns shift left), allocation-table rows
and pie range, person summary rows and holding blocks (stacked from row 14
in registry order, each person_rows deep, one blank row between blocks),
and chart series. The History sheet's COLUMNS still include any class with
nonzero recorded history — data preservation, §6.11 — only its chart series
is dropped. (The pre-v1.4.3 rule was enabled OR has_data; v1.4.3 made the
user's choice authoritative.)
Keep + gloss (v1.5.1, normative). Domain terms are correct and are never
renamed or softened in any user-facing text — headers, banners, comments or the
Guide (compute, NAV, ISIN, XIRR, corpus, PRAN, UAN, SGB,
coupon, Face Value, ex-date, …). Instead, every jargon-bearing header
carries a plain-language hover comment that leads with the term and
explains it (e.g. "XIRR = your return a year - it counts WHEN you invested…";
"ISIN = the code that identifies it, on your statement…"). Shared gloss strings
live in generate.py (_G_XIRR, _G_ISIN, _G_NAV, _G_CURVAL,
_G_NETCHG) so the same term is explained the same way on every sheet, and the
Guide ends with a short "Words you'll see" glossary. Because only comments and
banner help-text changed, nothing the reader matches on moves.
| # | Sheet | Kind | Purpose |
|---|---|---|---|
| 1 | Dashboard | mixed | family net worth, per-person × class matrix, XIRR, inflation, charts |
| 2 | Projection | computed | 20-year corpus trajectory table + line chart |
| v1.3 | Settings | input (§3.14) | per-class Yes/No + Target % + drift tolerance |
| 3… | one per person (e.g. Amit) | computed | that person's allocation + pie chart |
| … | Equity | data entry | stock holdings |
| v1.6 | Equity_Sells | data entry (§3.20) | one row per realised share sale (default off, CG switch §3.14) |
| … | MutualFunds | computed summary | one row per (owner, scheme), derived from MF_SIP |
| … | MF_SIP | data entry | one row per MF purchase/redemption |
| … | MF_Master | reference (hidden) | AMFI scheme list (~14k rows); Reference-lists switch §3.14 |
| … | Stock_Master | reference (hidden) | listed-stock list (~4.5k rows); Reference-lists switch |
| v1 | Bank_Master | reference (hidden) | bundled Indian bank list (Bank Name, Type; sorted; §3.11) |
| … | FixedDeposits | data entry | FDs |
| … | PPF | data entry | PPF accounts |
| v1.1 | PPF_Ledger | data entry | one row per PPF deposit (optional; §6.10) |
| v1.3 | EPF | data entry (§3.17) | EPF accounts — passbook balance + rate accrual (default off) |
| … | Bonds | data entry | corporate/other bonds |
| v1.3 | Gold_Silver | data entry (§3.15) | SGBs + physical gold/silver at the daily rate (default off) |
| v1.3 | NPS | data entry (§3.16) | NPS accounts — units × daily NAV (default off) |
| v1.3 | NPS_Master | reference (hidden) | NPS scheme list (§3.16); Reference-lists switch |
| v1.3 | Manual_Assets | data entry (§3.18) | hand-valued assets: Property / Cash / Insurance / Other (default off) |
| … | By Scrip | computed | family-wide exposure per stock |
| v1 | Corporate_Actions | reference (hidden, §6.7) | fetched + manual corporate actions and their effect; Reference-lists switch |
| v1.2 | Dividends | mixed (§3.13) | FY dividend ledger — auto + manual rows, by-month chart |
| v1.6 | Capital Gains | computed at build (§3.21) | STCG/LTCG per FY, grandfathering, sell-planning (default off, CG switch) |
| v1.6 | Tax_Rules | input (§3.22) | the capital-gains rate table, editable in the workbook — a Budget change is an Excel edit, not a release (default off, CG switch) |
| v1.7 | Import_Map | mixed (§3.23) | folio/account → person mapping + already-imported files (never-nag); Reference-lists switch |
| v1.1 | History | updater data | one net-worth snapshot per day (§6.11) |
| … | Guide | static text | 2-minute manual |
Defined names (workbook scope):
MF_SchemeList = MF_Master!$B$4:INDEX(MF_Master!$B:$B, COUNTA(MF_Master!$B:$B)+2)
Stock_NameList = Stock_Master!$B$4:INDEX(Stock_Master!$B:$B, COUNTA(Stock_Master!$B:$B)+2)
Bank_NameList = (v1) same pattern over Bank_Master
- Title row 1 per sheet: bold sheet title, e.g.
EQUITY HOLDINGS. - Hint row 2 (where present): one-line grey instruction text.
- Header row 3: bold on grey fill. Data starts at row 4. (Dashboard and person sheets have their own layouts, §3.3/§3.5.)
- Input cells: blue font; the “fill me” cells of the Dashboard are pale yellow. Computed cells: default font on light grey. This contrast is a spec requirement; exact shades are implementation-chosen.
- Dates display as
dd-mm-yyyy; money as thousands-separated with 0–2 decimals; percentages with 1–2 decimals. - Red/green (v1): conditional formats on every Net chg., Day chg., Return % and XIRR column/cell: value > 0 → green font (optionally pale green fill); value < 0 → red; blank → untouched. Amber fill marks degraded data: stale price (§6.5), delisted scrip, or FMV-fallback cost (§6.6). Colours must also work when a row is inserted/sorted (apply to the whole column range, not per-cell).
- Cell comments carry field help on headers (e.g. “Redemption = negative Amount”). Comments are part of the template; implementations must preserve the ability to regenerate them (openpyxl cannot — see CLAUDE.md).
- Tab colours (v1.4.3): the tab strip is colour-coded so it explains
itself at a glance — navy
#1F4E79for the overview tabs (Dashboard, Projection, Settings), teal#31859Cfor person tabs, blue#4472C4for every data-entry tab, grey#A6A6A6for the automatic tabs (By Scrip, Dividends, History) and the reference sheets, gold#BF8F00for the Guide.
Layout. Since v1.3 (R10) the class columns are the effective-enabled set
in registry order (§2.1); <T> below is the Total column (first after the
classes, G with the classic five) and <X> the Expected-@-FY column after
it. <L> is the last allocation-table row (19 + #enabled).
| Cell(s) | Content | Kind |
|---|---|---|
| A1 | FAMILY PORTFOLIO — NET WORTH TRACKER |
static |
| I1:P1 (merged) | v1.4.3 hidden-money notice — present only when a switched-off class holds rows: Hidden, not counted: <labels> — switch on in Settings to include. (amber) |
generator-written |
| I2:P2 (merged) | New here? The Guide tab (last tab) walks you through everything. |
static |
| A2 / B2 | As on / =TODAY() |
computed |
| A3 / B3 | Family net worth / =<T>16 |
computed |
| E3 | inflation % p.a. input (default 7) | input |
| B4 | Portfolio XIRR across all classes | updater-written |
| E4 | real return =IF(B4="","",(1+B4)/(1+E3/100)-1) |
computed |
| F4 | verdict =IF(B4="","",IF(B4>E3/100,"Beats inflation ✓","Below inflation ✗")) |
computed |
| row 5 | headers Person, <enabled class labels…>, Total, Expected @ 31-Mar-<FY> |
static |
| A6:A15 | up to 10 person names | input (pre-filled from persons) |
Reading the people back (v1.7.6). This matrix is the one place a reader
may rely on fixed row numbers, which makes it the one place a user's edit
can be misread: deleting the unused person rows (something §1 explicitly
permits) slides TOTAL and the headings under it UP into A6:A15, where
they were read as family members and given their own person sheets. The
rule is therefore: scan A6:A15, skip blanks, and STOP at the first cell
that is a known Dashboard heading — TOTAL, Person, Asset class,
As on, Family net worth, Portfolio XIRR, anything starting
Dividends FY / Allocation by / Net worth by, or any reserved sheet
name (§3.1). One implementation serves both the reader and the updater's
cheap peek, so the console prompt can never offer a heading as a person. A
file that already carries such a "person" drops it on the next read, is
reported in plain words, and the regeneration restores the canonical
layout — the junk tab disappears, real tabs and rows are untouched.
| B6:…15 | per class: =IF($A6="","",SUMIFS(<class value col>, <class owner col>, $A6)) | computed |
| <T>6:15 | =IF($A6="","",SUM(B6:<last class col>6)) | computed |
| row 16 | TOTAL + column sums | computed |
| A17 / B17 | Dividends FY <label> cell (§3.13; present when Equity is enabled) | computed |
| A18 | Allocation by asset class | static |
| A19:G19 | headers Asset class, Value, XIRR, Actual %, Target %, Drift, Rebalance hint | static |
| A20:B<L> | one row per enabled class; Value =<class col>16 (data-bar CF) | computed |
| C20:C<L> | per-class XIRR (blank when has_xirr is false) | updater-written |
| D20:D<L> | Actual % =IF(<T>16=0,"",B20/<T>16) | computed (v1.3, R11) |
| E20:E<L> | Target % =IF(Settings!C<row>="","",Settings!C<row>/100) — <row> is the class's registry Settings row, stable regardless of what is enabled | computed |
| F20:F<L> | Drift =IF(E="","",D-E) — green within ±tolerance (Settings B17), red outside, untouched when no target | computed |
| G20:G<L> | =IF(E="","",IF(ABS(F)<=tol/100,"On target","Move ₹"&TEXT(ABS(F)*<T>16,"#,##0")&IF(F>0," out"," in"))) — indicative, pre-tax, class-level | computed |
| v1: D2/E2 | Expected return % p.a. label + input (default 10) for the FY-end estimate | input |
| <X>5, 6:15 | Expected @ 31-Mar-<FY> header + per-person values (§6.8) | updater-written |
| <X>16 | total =IF(SUM(...)=0,"",SUM(...)) | computed |
All of D–G are live formulas — drift updates the moment a holding is edited, no updater run needed (the glanceable property, §6.13).
Charts on Dashboard: pie "Allocation by asset class", column "Actual vs Target %" (series D and E over the class labels; v1.3/R11), bar "Net worth by person", line "Net worth over time" and stacked area "Net worth by class over time" (both over History, §6.11).
Chart placement (normative, all sheets): floating charts must never sit
over a data column. Dashboard charts anchor at the first column AFTER the
person × class grid plus one spacer — derived from the number of shown
classes (chr(ord("A") + n + 4)), so switching on more classes slides the
charts right instead of hiding grid columns; the Actual-vs-Target chart
sits 8 columns further right. Charts below the tables keep the same left
edge.
Row 4 to row 24 (n = 0…20), columns:
| Col | Formula (row for year n) | Meaning |
|---|---|---|
| A | =YEAR(TODAY())+n |
calendar year |
| B | =Dashboard!$B$3*(1+Dashboard!$B$4)^n |
corpus at portfolio XIRR |
| C | =Dashboard!$B$3*(1+Dashboard!$E$3/100)^n |
corpus growing at inflation (break-even line) |
| D | =B/(1+Dashboard!$E$3/100)^n |
real (inflation-deflated) value of B |
Chart: line, “Corpus trajectory — portfolio return vs inflation (20 years)”, series B and C (and optionally D) over A. Everything on this sheet is live formulas — no updater involvement.
| Cell(s) | Content |
|---|---|
| A1 | <Name> — PORTFOLIO |
| A2/B2 | Owner / the person's name (single source for this sheet's formulas) |
| A3/B3 | Net worth / =B11 |
| A4/B4 | (v1.6, only when equity is enabled) Dividends FY <fy> / =SUMIFS(Dividends!$I:$I, Dividends!$A:$A,"<fy>", Dividends!$B:$B,$B$2) — this person's share of the Dashboard B17 family total. <fy> comes from the build's today (§6.16), never the wall clock, so the SUMIFS filters the same FY the dividend rows are tagged with. A Manual dividend row with a blank Owner counts in B17 but in no person's B4 — the updater warns and the B17 gloss says so |
| A5:C5 | headers Asset class, Value, # holdings |
| A6:A10 | Equity, Mutual Funds, Fixed Deposits, PPF, Bonds |
| B6:B10 | =SUMIFS(<class value col>, <class owner col>, $B$2) (same column map as Dashboard) |
| C6:C10 | =COUNTIF(<class owner col>, $B$2) |
| A11:C11 | Total + sums |
Chart: pie “ — allocation” over A6:B10, anchored at I4 — right of the holding blocks, which occupy columns A–G and only grow downward (the §3.3 charts-never-over-data rule).
Header row 3, data rows 4…1503 (v1.7: was 253; v1.6.2 raised from 140 — one row per purchase lot is the norm, and the updater refuses to run rather than lose a typed row to the budget, §7 step 5). Columns:
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | person name |
| B | ISIN | computed | =IF($C4="","",IFERROR(INDEX(Stock_Master!$C:$C,MATCH($C4,Stock_Master!$B:$B,0)),"")) — blank means “no master match”, user may overtype an ISIN manually (validation is non-blocking) |
| C | Scrip | input | type-ahead dropdown over Stock_NameList (§3.12) |
| D | Quantity | input | as-purchased (raw) quantity |
| E | Avg. cost | input | as-purchased average cost/share; v1: may be left blank → FMV fallback §6.6 |
| F | Closing Price | updater | last close by ISIN |
| G | Prev. close | updater | previous close |
| H | Closing Price Date | updater | bhavcopy date used (a real date cell — the stale-price amber conditional format keys on TODAY()-$H4>7) |
| I | Cur. val | computed | =IF($D4="","",$D4*IF($S4="",1,$S4)*IF($F4="",$V4,$F4)) (v1: adjusted qty §6.7; v1.7.4: falls back to the user's V price only when the exchange quotes none) |
| J | Invested | computed | =IF(OR($D4="",$E4=""),"",$D4*$E4*IF($T4="",1,$T4)) — × the v1.4 demerger Cost factor, blank = 1 |
| K | Net chg. | computed | =I−J guarded |
| L | Day chg. | computed | =IF(OR($G4="",$D4=""),"",$D4*($F4-$G4)) |
| M | Cost date | input | drives per-row return annualisation & XIRR cashflows |
| N | XIRR (per row) | computed | =IF(OR($M4="",N($J4)=0,$I4="",TODAY()<=$M4),"",($I4/$J4)^(365/(TODAY()-$M4))-1) — simple two-flow annualisation |
| v1: O | Qty today | computed | =IF($D4="","",$D4*IF($S4="",1,$S4)) — post-split/bonus share count, the demat view; feeds By Scrip and the person sheets |
| v1: P | Avg cost today | computed | =IF(OR($D4="",$E4=""),"",$E4*IF($T4="",1,$T4)/IF($S4="",1,$S4)) — cost per share in today's share terms; × the v1.4 demerger Cost factor so a post-demerger row matches the docked basis a broker app shows |
| Q | Key | computed helper | =IF($A4="","",$A4&"#"&COUNTIF($A$4:$A4,$A4)) stable per-owner sequence id |
| v1: R | Flags | updater helper | FMV (§6.6 fallback), MERGED→<name> / ISIN→<isin> (row priced via a successor, §6.15), DEMERGER:<old_isin>@<ex_date> (an appended child row) — flags round-trip regeneration. When a row carries both a restructure flag and FMV they are joined with `" |
| v1: S | Adj factor | updater-written | split/bonus and merger-ratio multiplier since Cost date (§6.7/§6.15, chain-aware); blank = 1. Cur. val and Day chg. use Quantity*IF($S4="",1,$S4)*price |
| v1.4: T | Cost factor | updater-written | demerger cost retention (§6.15): the parent keeps cost_pct/100 of its cost basis, the rest moves to the appended child row; blank = 1. The user's Avg. cost cell is never rewritten |
| v1.7.4: V | Price if unlisted | input | the user's own price per share, used ONLY while F (the exchange close) is empty — unlisted/pre-IPO holdings value correctly today and switch to the market price by themselves the day the security lists, with no edit. model.effective_price(row) is the single definition the sheet formula, the totals, XIRR, projections and the capital-gains view all share. Never written by the updater |
| v1.7.5: W | Tax type | input (dropdown) | how the Capital Gains tab must treat this holding: blank/Equity = shares and equity ETFs (§112A family), Debt = bond/debt ETFs, Gold-Silver = listed bullion/overseas ETFs. model.equity_tax_bucket() maps it to a bucket key and is forgiving of wording (gold/silver/bullion/overseas → mf_other; debt/bond → mf_debt; anything unrecognised → equity, the safe historical default). ONLY §6.16 reads it — valuation, XIRR and the totals ignore it entirely |
| v1.7.1: U | Qty as of | import-written, hidden column | the date this row's Quantity is stated AS OF (§6.18). A broker HOLDINGS file reports the post-split/bonus count, so the corporate-action window for S/T starts here, not at Cost date — without it, history the broker already counted would re-apply (the ×5/×15 Qty-today bug). Blank for typed rows (their Quantity is as-bought). Round-trips like any input |
Below the data block, one updater-written cell holds the equity-class XIRR (the row after TOTAL — N1505 at the v1.7 budget; the legacy template's N142). v1 additions: status/staleness amber flags (§6.5), FMV-fallback marking on E (§6.6), adjustment columns (§6.7).
MF_SIP — one row per purchase, SIP instalment or redemption (redemption = negative Amount). Header row 3, data rows 4…3003 (v1.7: was 1003; raised from 503):
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Fund House | computed | INDEX(MF_Master!$A:$A, MATCH($C, MF_Master!$B:$B, 0)) guarded |
| C | Scheme Name | input | type-ahead dropdown over MF_SchemeList |
| D | ISIN | computed | INDEX(MF_Master!$C:$C, MATCH($C, …)) guarded |
| E | Date | input | |
| F | Amount | input | negative = redemption |
| G | NAV on date | input | |
| H | Units | computed | =IF(OR($F4="",$G4=""),"",$F4/$G4) |
J1/J2: label Portfolio MF XIRR + updater-written value.
MutualFunds — one row per (owner, scheme); the user enters only A and C
(and, since v1.6, optionally M Tax type: an Equity/Debt dropdown feeding
§6.16 — blank counts as Equity; the M3 comment says so in plain words):
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B / D | Fund House / ISIN | computed | master lookups as above |
| C | Scheme Name | input | dropdown |
| E | Units | computed | =SUMIFS(MF_SIP!$H:$H, MF_SIP!$A:$A,$A4, MF_SIP!$D:$D,$D4) |
| F | Avg cost NAV | computed | =H/E guarded |
| G | Current NAV | updater | AMFI by ISIN |
| H | Invested | computed | =SUMIFS(MF_SIP!$F:$F, …) |
| I | Cur. val | computed | =E*G guarded |
| J | Net chg. | computed | =I−H |
| K | Return % | computed | =J/H guarded |
| L | XIRR | updater-written | true XIRR from that (owner, ISIN)'s MF_SIP cashflows + current value |
| M | Tax type | input | Equity/Debt dropdown (non-blocking) feeding §6.16; blank counts as Equity |
| N | Key | helper | as in Equity |
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Bank / Institution | input | v1: type-ahead dropdown over Bank_NameList, free text still allowed |
| C | FD No. | input | |
| D | Principal | input | |
| E | Rate % p.a. | input | |
| F / G | Start / Maturity Date | input | |
| H | Comp./yr | input | compounding periods per year (4 = quarterly) |
| I | Value as on today | computed | =D*(1+(E/100)/H)^(H*YEARFRAC(F, MIN(TODAY(),G))) |
| J | Maturity Value | computed | same with YEARFRAC(F,G) |
| L | Key | helper |
| Col | Header | Kind |
|---|---|---|
| A | Owner | input |
| B | Institution | input |
| C | Account No. | input |
| D | Current Balance | input |
| E | Balance as-on | input (date) |
| F | Rate % (ref) | input (default 7.1; v1: generator pre-fills current rate from data/ppf_rates.csv) |
| G | Notes | input |
| I | Key | helper |
No ledger in v1 — value grows at Rate% from the as-on date for XIRR and FY-end purposes (contribution ledger is roadmap).
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Issuer / Bond | input | |
| C | ISIN | input | |
| D | Qty | input | |
| E | Face Value | input | |
| F | Buy Price | input | per unit |
| G | Current Price | input (updater fills when the ISIN trades on the exchange) | |
| H | Coupon % p.a. | input | |
| I | Maturity Date | input | |
| J | Invested | computed | =D*F guarded |
| K | Cur. val | computed | =D*G guarded |
| L | Net chg. | computed | =K−J |
| M | Buy Date | input | required for XIRR; rows without it are skipped |
| N | Key | helper | |
| v1: O | Maturity Value | computed | =IF(OR($D4="",$E4=""),"",$D4*$E4) — redemption at face. Cumulative/zero bonds: set H = 0 and Face Value = redemption amount |
| v1: P | Coupons till maturity | computed | =IF(OR($D4="",$E4="",$H4="",$I4="",$I4<=TODAY()),"",$D4*$E4*($H4/100)*YEARFRAC(TODAY(),$I4)) — simple, non-reinvested |
v1: bond XIRR (per row and class) includes coupon cashflows: +D*E*(H/100)/f
on each coupon date from Buy Date to today (f = coupon frequency, default
annual) — see §6.3.
By Scrip — data rows from 4; A ISIN (input or updater-synced from Equity),
B Scrip lookup, C =SUMIF(Equity!$B:$B,$A4,Equity!$O:$O) total qty (Qty
today terms), one column per configured person =SUMIFS(Equity!$O:$O, Equity!$B:$B,$A4, Equity!$A:$A,"<Person>"), last column Cur. val
=SUMIF(Equity!$B:$B,$A4,Equity!$I:$I). Auto-sync (v1.7.1): on every
update, each distinct ISIN actually held on Equity (resolved via
isin_override or the master lookup, quantity non-blank) that has no By
Scrip row is APPENDED (display name from Stock_Master, else the typed
scrip), sorted by name among the additions. Add-only: user rows are never
edited or removed — a no-longer-held scrip simply shows 0, the user may
keep or delete it. When the sheet lacks room a plain warning says not
every held stock got a row (the budget is 150 data rows, §2).
MF_Master — A1 title, A2 hint, D2 Refreshed: + E2 date (updater),
row 3 headers Fund Name, Scheme Name, ISIN, data from row 4,
sorted by Scheme Name (ordinal, case-insensitive). Source: AMFI (§5.1).
Stock_Master — headers Symbol, Stock Name, ISIN, same layout, sorted by
Stock Name. Merge policy is add-only (§6.4). v1 adds Status and
Last Traded columns (§6.5).
Bank_Master (v1) — headers Bank Name, Type, sorted by name, seeded from
data/banks_in.csv (RBI scheduled banks + major SFBs/co-ops). Static; only
release updates refresh it.
Guide — a designed, in-workbook manual driven entirely by GUIDE_ROWS
(guide_text.py); the renderer (_write_guide) turns row kinds (title,
section, legend, step, kv, bullet, tip, text, footer, space) into a page with a
navy title banner, colour-cycled section bars, numbered step badges and a swatch
legend. The title banner is frozen (freeze_panes(2, 0)) so it stays in view
while scrolling. Plain, non-technical language throughout (house rule). Sections
(kept deliberately short and scannable): the colour rule, a 4-step start, a
"where does each thing go?" table, what the updater quietly handles, "make it
yours" (show/hide · add a person · targets), the optional privacy switches, a
short "words you'll see" glossary (XIRR, ISIN, NAV, PRAN/UAN, SGB, ex-date)
that lets the sheets keep their exact terms while staying self-explanatory, and
"good to know". Covers inputs-vs-computed colours and the v1 amber flags
(stale/delisted/FMV-estimated).
List validation with:
=OFFSET(<Master>!$B$3,
IFERROR(MATCH($C4&"*", <NameList>, 0), 1), 0,
MAX(1, COUNTIF(<NameList>, $C4&"*")), 1)
- Begins-with filtering: typing a prefix then opening the dropdown shows only matching entries. Requires the master sorted by that column.
- Interaction is two-step by Excel's design: the window formula evaluates against the cell's committed value, so the user must type the prefix, press Enter, then re-open the dropdown (arrow click or Alt+Down on the selected cell). Excel offers no live suggestions while typing in a validation cell (only recent Microsoft 365 builds add native autocomplete). The input tip and Guide must state this two-step flow explicitly.
showErrorMessage = false(non-blocking): users may keep free text (e.g. a delisted scheme); the lookup columns then stay blank, which downstream formulas treat as "fill ISIN manually".- Input tip on the cell explains the behaviour.
- Applied ranges: Equity C4:C1503, MutualFunds C4:C113, MF_SIP C4:C3003
(v1.7.0 budgets; always the sheet's LAST_ROW);
v1 adds FixedDeposits B4:B over
Bank_NameList.
FY dividend ledger: one row per dividend event × owner. Title r1, hint r2 (plain-language: rows fill in automatically; amounts are estimates, amber), header r3, data r4..203.
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | FY | updater / input | Indian financial year of the ex-date, e.g. 2026-27; the updater backfills a blank FY on Manual rows from F |
| B | Owner | updater / input | one row per owner holding the stock at ex-date |
| C | Scrip | updater / input | Stock_Master display name |
| D | ISIN | updater / input | |
| E | Type | dropdown | Interim / Final / Special (non-blocking validation) |
| F | Ex-Date | date | |
| G | Rate ₹/share | updater / input | parsed from the announcement (§5.4) |
| H | Qty @ ex-date (est.) | updater | §6.12 estimate; amber; user-correctable on Manual rows |
| I | Est. amount | computed | =IF(OR(G="",H=""),"",G*H) — amber (estimate; the exact credit is on the bank statement) |
| J | Source | updater | Auto / Manual |
| K | Details | updater / input | announcement free text |
Lifecycle (normative). On every update run: Auto rows whose ex-date falls
in the current FY are rebuilt from the feed + current holdings; all other
rows persist unchanged (prior-FY Auto rows therefore freeze on the first run
after Apr 1 — multi-year record for free). Manual rows always persist and
suppress an Auto row with the same (isin, ex_date) key — the Type is
deliberately NOT part of the key, because the exchanges word the same event
differently (§5.4 dedupe rule). Current-FY Auto rows of an ISIN the feed
could NOT verify this run are kept, not rebuilt — a one-symbol outage
must never delete income already on the sheet. Re-runs are idempotent. If
the feed is unreachable entirely, the sheet is left as-is. Capacity: the
sheet holds 200 data rows; if an assembly exceeds it, the OLDEST Auto rows
by ex-date give way (prior-FY first, since they are oldest; current-FY Auto
rows only when that is not enough) and the run warns with the dropped
count. Manual rows NEVER give way: if Manual rows alone exceed capacity
the run refuses up front (§7 step 5 refusal semantics) instead of
truncating user data.
By-month chart. Columns M/N rows 4..15 hold the current FY's months
(Apr..Mar) and SUMPRODUCT(rate × qty × month × FY) sums; a column chart
("Dividends by month — FY ") renders them. The current-FY label is
stamped at build time (the updater regenerates the workbook, keeping it
fresh). The Dashboard shows one cell: Dividends FY <label> =
SUMIFS(Dividends!I:I, Dividends!A:A, "<label>").
Dividends do not feed equity XIRR (roadmap; changing return semantics deserves its own release).
The one place the user tunes the workbook. Title r1, hint r2 ("Show? — Yes
shows a tab, No hides it. Nothing is ever deleted…"), header r3
(Asset class, Show?, Target %, Status, Notes), one row per registry class
from r4 (rows 4–15 reserved), then:
| Cell | Content | Kind |
|---|---|---|
| B4:B15 | Yes / No dropdown (non-blocking) — show or hide the class |
input |
| C4:C15 | target allocation %, blank = no target (R11 drift view; header comment says "optional") | input |
| D4:D15 | Shown / Hidden / Hidden - has data (not counted) |
generator-written |
| E4:E15 | note (for the has-data case: rows are saved but not counted; switch to Yes to include) | generator-written |
| A16/B16 | Reference lists — Yes/No for the REFERENCE sheets (§2.1); default No |
input |
| A17/B17 | Capital gains report — Yes/No for Equity_Sells + Capital Gains + Tax_Rules (§3.20–§3.22, v1.6); default No |
input |
| A18 | Balance targets (optional) section label |
static |
| A19/B19 | Drift tolerance (± % points), default 5 |
input |
| A20/B20 | Targets total = SUM(C4:C15), amber when non-zero and ≠ 100 |
computed |
| A21 | Privacy (optional) section label (v1.5) |
static |
| A22/B22 | Privacy mask — Yes/No for the ••• Mask (§3.19); default No |
input |
| A23/B23 | Lock file (encryption) — Yes/No for the at-rest Lock (§3.19); default No |
input |
Reader rules: match class rows by label anywhere in rows 4–23 (tolerant;
"Real Estate" accepted for Property); a missing Reference lists row
(pre-v1.4.3 workbook) ⇒ No; a missing Capital gains report row (pre-v1.6)
⇒ No; missing sheet (pre-v1.3) ⇒ registry defaults; the user's No always
round-trips unchanged. Label-driven matching is what makes the v1.6 one-row
shift invisible to older workbooks. Real form-control checkboxes
are deliberately not used (xlsxwriter cannot write them; LibreOffice
renders them poorly) — the Yes/No validation dropdown is the normative
control.
SGBs price from the merged bhavcopy by ISIN (they trade on the cash market
at ~₹/gram); physical metal values grams × purity × the daily reference rate
(§5.7). Title r1, hint r2 (+ H2/I2 Rates as on stamp, amber trigger),
header r3, data r4..53, TOTAL r55.
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Type | input | dropdown: SGB / Gold / Silver |
| C | Description / Series | input | "SGB 2023-24 Ser II", "Gold coins, 2 x 10 g (24K)", "Silver bar, 1 kg" (header comment carries these examples) |
| D | ISIN | input | SGB only — drives bhavcopy pricing |
| E | Qty (g / units) | input | SGB: units (1 unit = 1 g); metal: grams |
| F | Purity | input | blank = 1 (SGB always 1); 22K = 0.916, 18K = 0.75 |
| G | Buy Price ₹/unit | input | per gram/unit; XIRR outflow |
| H | Buy Date | input | XIRR anchor |
| I | Rate today (auto) | updater | SGB: exchange close; metal: §5.7 ₹/g rate. Amber on METAL rows when the I2 rates-as-on stamp is > 7 days old (SGB rows are exempt — their closes carry their own dates and the share-price staleness rules apply). I2 itself is stamped only when a metal rate actually arrived — an SGB-only pricing day must not refresh it and hide a stale benchmark |
| J | Rate override | input | user's ₹/unit (e.g. the jeweller's board rate) — always wins over I |
| K | Cur. val | computed | =E × (F or 1) × (J else I), guarded; the class value column |
| L | Invested | computed | =E × G, guarded |
| M | Net chg. | computed | K − L, red/green |
| N | Maturity | input | SGB (8 years); blank for metal |
| O | Key | helper |
SGB XIRR includes the statutory 2.5 % p.a. semi-annual coupon computed on the row's Buy Price — a documented approximation (the statutory base is issue price; an extra column is not worth the width).
Units × daily NAV, exactly the mutual-fund mental model. NPS sheet: title r1, hint r2, header r3, data r4..43, TOTAL r45.
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | PRAN | input | free text |
| C | Scheme | input | type-ahead dropdown over NPS_SchemeList (§3.12) |
| D | Scheme Code | computed | INDEX/MATCH on NPS_Master; manual override allowed (plain text beats the formula) |
| E | Units | input | from the CRA statement |
| F | Current NAV | updater | daily NAV by scheme code (§5.6) |
| G | Cur. val | computed | =E × F, guarded; the class value column |
| H | Total contributed | input (optional) | enables the approximate XIRR |
| I | First contribution | input (optional, date) | |
| J | XIRR | updater | approximate two-flow (−H @ I, +G today); header comment states the approximation — a dated-contribution ledger is roadmap |
| K | Key | helper |
NPS_Master: Scheme Code, Scheme Name, PFM + refreshed stamp (E2),
sorted by scheme name (the dropdown sort rule, §3.12), add-only merge
keyed by scheme code (§6.4 pattern). The reader keeps a row when its
Scheme Code is non-empty — PFM is descriptive, and a blank PFM must
never drop a scheme from the master (the MF/Stock masters key on their
ISIN column instead). A REFERENCE sheet since v1.4.3 — visibility follows
the Settings "Reference lists" switch (§2.1), not the NPS class.
Deliberately congruent with PPF's flat path: passbook balance in, accrual out. Title r1, hint r2, header r3, data r4..43, TOTAL r45.
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Establishment / UAN | input | |
| C | Member ID | input | |
| D | Current Balance | input | EPFO passbook closing balance (employee + employer) |
| E | Balance as-on | input (date) | the passbook date |
| F | Rate % | input | blank ⇒ updater fills the latest epf_rates.csv rate |
| G | Notes | input | |
| H | Balance today | computed | =IF(D="","",IF(OR(E="",F=""),D,D*(1+F/100)^YEARFRAC(E,TODAY()))) — flat accrual; the class value column |
| J | Key | helper | person-block lookup |
Exact monthly-run accrual + a contribution ledger is a roadmap follow-up (mirroring PPF's flat-first history); the H header comment states the estimate nature.
One sheet for every hand-valued asset; the Class column routes each row to
its own registry class (Property / Cash / Insurance / Other — each with
its own Dashboard column, allocation row, target and History column via the
class_filter SUMIFS criterion, §2.1). The sheet hides only when ALL four
are off. Title r1, hint r2 ("Things you value yourself… only two numbers
matter"), header r3, data r4..63, TOTAL r65.
| Col | Header | Kind | Definition |
|---|---|---|---|
| A | Owner | input | |
| B | Class | input | dropdown (non-blocking): Property / Cash / Insurance / Other. Matching is case-insensitive — Excel's SUMIFS already is, and the reader canonicalises a typed variant ("property", and the pre-v1.4.3 "Real Estate") to the dropdown label so both sides agree. A value matching NO label counts in no class (only the sheet TOTAL sees it); the updater warns naming the row |
| C | Description | input | "Apartment (self-occupied)", "Savings account balance", "Life policy - surrender value today" (the header comment carries these examples) |
| D | Institution / Ref | input | bank, insurer, registrar |
| E | Invested / Cost | input (optional) | RE: purchase cost; Insurance: premiums paid; enables Net chg. + XIRR |
| F | Cost date | input (optional) | XIRR anchor |
| G | Current value ₹ | input | THE number: market estimate / balance / surrender value; the class value column |
| H | Value as-on | input (date) | amber when > 90 days old — hand-typed values rot silently |
| I | Net chg. | computed | =IF(OR(E="",G=""),"",G−E), red/green |
| J | Notes | input | |
| K | Key | helper |
No per-person holding block (the sheet itself is the overview); person sheets show one summary row per subclass. XIRR per §6.2 (Cash excluded).
Two opt-in layers sharing ONE password, independently switchable (§3.14 rows 21–22 → four legal states). Threat model (stated to users in these words' spirit): the Mask stops shoulder-surfing and casual household snooping in an open file; the Lock protects the file at rest (lost laptop/USB, synced folders, semi-technical snoops). Neither defends a compromised machine (malware/keylogger) or someone who knows the password. Values are NEVER removed or transformed — the workbook stays the data store, and every state round-trips losslessly.
Mask (curtain). A masked build differs only in presentation:
- every non-date number format becomes the literal
"•••";"•••";"•••";@— three explicit sections (a single section would render-•••, leaking the sign); text passes through; dates stay visible. The workbook default format carrieshidden + lockedso format-less cells are covered too. (Never put a num_format into the workbook DEFAULTS — xlsxwriter's numFmtId table collides and masked numbers render as dates.) - every sheet is protected with
selectLockedCellsdisallowed — no selection means no status-bar SUM, no copy-paste, no Go To; thehiddencell attribute blanks the formula bar. The sheet password is derived from the stored fingerprint (the real password may be absent on a keep-masked run); Excel sheet protection is the weak legacy hash — the curtain's REAL check is the PBKDF2 verification below. - every value-derived visual is suppressed (it would leak through •••):
charts (replaced by a grey note), the allocation data bar, red/green
fonts, the day-change icon set — implemented by swapping the xlsxwriter
worksheet class for one whose
conditional_format/insert_chartno-op. - the password is never stored:
pbkdf2-sha256$<iter>$<salt>$<hash>(200k iterations) lives in the constant defined nameNW_Privacy;NW_Masked(yes/no) records the at-rest state. openpyxl returns constant defined names WITH their quotes — readers must strip them. - known residual channels, documented not hidden: Excel's Show-Formulas view and Find-All results pane (manual test matrix), unprotecting a sheet with the derived password. RESET (an explicit interactive action) turns the mask off and clears the fingerprint — data is never lost.
Lock (safe). Standard OOXML Agile encryption — Excel's native
"password to open" (AES-256, SHA-512 KDF), via msoffcrypto-tool:
- at rest the file is a CFB ciphertext container (magic
D0 CF 11 E0); nothing — values, names, structure — is readable without the password. Excel/LibreOffice prompt natively, and an Excel re-save keeps the encryption. - the updater needs the password even to READ the file: wrong password or a headless run exits politely with the file untouched. Scheduled hands-free updates cannot run while Locked (documented).
- write path: the workbook is built to an in-memory buffer, encrypted in memory, and the encryption self-verifies (decrypt-back must equal the plaintext) before the atomic replace — plaintext never touches disk and an encryption failure can never destroy the file. Backups byte-copy the at-rest ciphertext, so backups are encrypted for free.
- the KDF is the encryption's own; no separate fingerprint is needed for verification (decrypt success IS the check). NO recovery exists by design; enabling it warns to write the password down. Enabling the Lock on an unconfirmed password is refused (no lockout by typo): the transition to encrypted happens only in a run where the password was proven (typed and verified, or used to decrypt).
States & flows. mask-only at rest = plain zip with ••• formats;
lock-only = ciphertext with a normal workbook inside; both = ciphertext
with a masked workbook inside. Viewing: a verified password + the user's
explicit choice writes the current build unmasked ("open (viewing)");
the next run (Enter at the prompt), or the offline --lock flag
(read → regenerate, no fetching), puts the mask back. While the mask is
on, a backup taken of an unmasked-at-rest file is named
*.unmasked-backup-*; a masked/locked run purges all of them EXCEPT the
one it made itself this run (v1.6.2: exactly one readable rollback copy
survives one cycle; the next masked run removes it — one warning line says
so), and both backup kinds rotate independently to the newest 10 so a
view-preferring user's readable copies stay bounded even if no masked run
ever comes. (Backup-before-rewrite itself, incl. --lock, is §7 step 4.)
A self-contained realised-sales ledger. The Equity sheet stays the NET what-you-own-now snapshot, so a sale is its own record here; the r2 hint carries the double-entry contract verbatim: "One row per sale — copy the numbers from your contract note. Also reduce the Quantity on the Equity tab: that tab is what you own now; this one is the record of what you sold."
Layout: title r1 / hint r2 / header r3 / data rows 4..EQSELL_LAST_ROW (203).
Columns: A Owner (input; the reader's anchor header) · B ISIN (Stock_Master
INDEX/MATCH lookup, user-overridable) · C Scrip (type-ahead dropdown) ·
D Qty sold · E Buy date · F Buy price ₹/share · G Sell date · H Sell price
₹/share · I Proceeds =D·H (computed) · J Gain ₹ =D·(H−F) (computed,
red/green) · K Notes. No TOTAL row. More rows than the sheet holds → the
§6.16 overflow warning (the extras would be silently lost otherwise). The
J3 gloss explains why this simple gain can exceed the Capital Gains tab's
taxable gain (grandfathering); the F3 gloss says a blank-price sale shows
in the tax view but stays out of XIRR (§6.2).
Units convention (normative): every figure on a sell row is in SELL-TIME share units — exactly what the contract note / broker P&L shows — and the engine never CA-adjusts these inputs. A blank Buy price on a pre-2018-02-01 purchase means "apply the §6.6 grandfathering value" (§6.16); the FMV substitution happens in the report only and is NEVER written back into the input cell (the cell stays blank and round-trips blank). Demerger-child sales keep the original company's buy date (holding period inheritance, §6.15) — the E3 header comment says so in plain words.
Reader: guarded (if "Equity_Sells" in wb.sheetnames — pre-v1.6 workbooks
are a no-op), anchor "Owner", columns 1–8 + 11 by position (9/10 are
formulas and never read).
The tax view, computed at build time from persisted inputs (Equity_Sells
- MF_SIP + the bundled FMV/tax tables + Corporate_Actions) by §6.16 and
written as plain values — never stored on the data model, so regeneration
always reproduces it and round-trip identity is untouched.
build_workbooktakes an optionaltoday(the updater passes its run date; direct callers get the wall clock) and an optional precomputed report (capgains=) so the updater's console figures and the sheet come from ONE computation. When no sale is recorded and the switch is off, the engine is skipped entirely and the sheet carries only its head plus a one-line "nothing recorded yet" hint.
Reads top-down like a story: r2 hint "Worked out for you from Equity_Sells and MF_SIP on every update. Indicative only - for planning, not for filing."; r3 headline = current-FY LTCG headroom with the absolute deadline (e.g. "sell before 31-03-2027") beside a numeric cell; then three stacked blocks — By financial year (newest first: STCG ₹, LTCG ₹, allowance, allowance used, still tax-free, indicative tax STCG/LTCG, at-your-slab gains, debt-fund gains, intraday/speculative gains — the K gloss says they're slab-taxed business income, not capital gains — and "Losses used vs LTCG ₹" (v1.6.1, widened v1.6.2): the total Sec 70 set-off applied against LTCG (§6.16), written ONLY when non-zero (blank otherwise — don't-intimidate; the engine clamps float dust so a normal year truly stays blank) — EXCEPT in masked builds, where the cell is written on every row: a ••• appearing only in loss-harvest years would leak that fact through the mask by its mere presence), What you sold (realised) (FY, owner, what, tax bucket, qty, dates, days held, term, proceeds, taxable cost, gain, plain-words note), What you still hold (if sold today) (the sell-planning helper: gain vs the taxable/grandfathered cost, term, and the exact date each holding turns long-term). Engine warnings render as ⚠ hint lines at the bottom; every caveat (assumed-equity fund, FMV est., at-your-slab) lives in the row's Note column, on the sheet.
Masked-build rules: no charts or data bars anywhere on the pair (the
workbook chart count stays at 10), and no ₹ amount is ever composed into a
text cell — the mask's @ section renders text verbatim, so figures live
only in numeric cells.
The Settings row 17 "Capital gains report" (Yes/No, default No) shows/hides Equity_Sells + Capital Gains + Tax_Rules together, independent of the Equity class toggle (progressive disclosure). Absent row (pre-v1.6 workbook) reads as No.
The capital-gains rate table lives IN the workbook, so a Budget change is an Excel edit, not an app release. An input sheet (blue tab), prefilled with the bundled law on first build; from then on the workbook's rows are the source of truth.
Layout: title r1 / hint r2 (says exactly that: edit a number or add a row
from the date the change applies, then run the update; blank STCG % = at
your slab; deleted shipped rows come back — edit them instead) / header r3 /
data rows 4..TAXRULES_LAST_ROW (33). Columns: A Asset (non-blocking dropdown
equity | mf_equity | mf_debt | mf_other, the TAXRULE_ASSETS whitelist —
a value outside it is kept on the sheet with a warning and never computed
with, so adding a bucket means adding it HERE too; the reader's anchor
header) · B Applies from
(date; the newest row ≤ the sale date wins — that's how mid-year changes
work) · C Long-term after (days) · D STCG % · E LTCG % · F Tax-free
allowance ₹/yr · G Notes. Every header carries a plain-words gloss.
Resolution (normative): the engine uses the bundled tax_rules_in.csv
as DEFAULTS, upserted with the workbook rows by key (asset casefolded, Applies-from date) — an edited row overrides its bundled twin, a new row
joins the set, and bundled rows the workbook doesn't mention stay in force
(so a future release's refreshed CSV still reaches users who never edited).
A row with an unknown Asset or no Applies-from date is never computed
with and never silently dropped: it stays on the sheet, and every run
warns until it is fixed. The same treatment applies to numbers that can
only be typos — a rate outside 0–100, a negative allowance, a non-positive
holding period. Two workbook rows with the same (asset, date) warn and the
lower row wins. Deleting a shipped row is a no-op (the merge restores it).
Reader: guarded (if "Tax_Rules" in wb.sheetnames — pre-v1.6 workbooks fall
back to the bundled CSV alone), anchor "Asset", columns 1–7 by position.
Sample data ships tax_rules = load_tax_rules() so build→read→build is
byte-stable (round-trip identity).
Two small tables on one sheet, both written by the import flow (§6.17) and
user-editable. Rows 4..103 each. Left (columns A–D) — the owner map:
Source, Account / Folio, Name on statement, Owner where Owner is a
non-blocking dropdown over the Dashboard persons; a folio maps once and
never prompts again, and a wrong answer is fixed by editing the cell and
re-running. Rows with a blank/unknown Owner are skipped by imports (with a
warning). Right (columns F–I) — the never-nag file memory:
File, Fingerprint (sha256[:12] of the file bytes), Imported on, Result (imported | skipped); the updater consults it before offering a file
found next to the workbook, and deleting a row is the documented way to
be asked about that file again. A file is only recorded imported when
its content actually landed — a capacity deferral or an unmapped-owner
refusal keeps it offerable (§6.17); a stored Owner that no longer names
a person is ignored and re-asked, and the fresh answer updates the row. Follows the Reference-lists switch
(hidden by default — most users never see it). Reader: guarded
(if "Import_Map" in wb.sheetnames), anchor "Source"; account keyed by
column B, file rows by column F, each table independent. Both lists are
in CAPACITIES (§7 step 5 refusal covers rows read from the sheet).
Run-time APPENDS (new mapping answers / file records) that would overflow
the sheet are dropped with a warning instead — the update itself never
fails over an auxiliary sheet; dropped answers are simply asked again.
The released template ships with fictional holdings for three people (Amit, Priya, Rahul) using real ISINs so the first updater run works end-to-end. MF samples must be real AMFI (Scheme Name, Fund House, ISIN) triples; equity samples real BSE scrips. EVERY asset class carries sample rows (incl. a real SGB ISIN, generic gold coins / 22K jewellery / a silver bar, an NPS scheme from the seeded master, an EPF passbook line, and deliberately generic Property ("Apartment (self-occupied)") / Cash / Insurance / Other rows). Targets sit on the five default-on classes (40/15/20/15/10 — sum 100) so the drift view demonstrates itself. v1.4.3 calm first open: the classic five ship Settings Yes and are all a new user sees; every newer class ships No and therefore hidden, its sample rows waiting inside as a worked example the moment it is switched on. The stored ClassXirr carries figures only for the shown classes (the allocation table lists only those); hidden classes get theirs computed when enabled. Onboarding = replace the sample rows with your own and switch on what you own — nothing needs deleting to keep the workbook tidy.
All fetches: plain HTTPS GET, ≤ 2 retries, per-day local cache (cache/),
graceful degradation (a failed source leaves old values in place and reports
it — never blanks user-visible data).
https://www.amfiindia.com/spages/NAVAll.txt — ;-separated text:
Scheme Code;ISIN Div Payout/ ISIN Growth;ISIN Div Reinvestment;Scheme Name;Net Asset Value;Date
Parsing rules: lines without ; are section headers — the most recent one is
the current Fund House; skip blanks/headers; a scheme yields up to two
(ISIN → NAV) entries (both ISIN columns); NAV N.A. → skip. Date format
dd-MMM-yyyy.
https://www.bseindia.com/download/BhavCopy/Equity/BhavCopy_BSE_CM_0_0_0_<yyyymmdd>_F_0000.CSV
Common-format CSV. Columns are located by header name, tolerantly: ISIN ∈ {ISIN, ISIN_CODE, …}; Close ∈ {ClsPric, Close, LAST, …} (never a column containing “prev”); Prev ∈ {PrvsClsgPric, PrevClose, …}; also read TckrSymb/FinInstrmNm for the master, FinInstrmId (BSE scrip code) for the corporate-actions lookup, and HghPric when building FMV data.
Merge rule (normative). Both exchanges are fetched for the same trade date and merged — dates are never mixed across exchanges. Not published on holidays: try today, then walk back up to 7 calendar days, stopping on the first day where at least one exchange answers; record the date actually used (→ Closing Price Date column). The merged result is:
prices = union of ISINs; on a dual-listed conflict NSE close/prev win
(deeper cash-market liquidity — matches broker apps)
codes_by_isin = from the BSE parse ONLY, retained whenever BSE responded
(NSE's FinInstrmId is not a BSE scrip code)
master rows = deduped by ISIN; NSE symbol preferred for NEW ISINs (it is
what the NSE corporate-actions API needs); the add-only
merge (§6.4) protects existing rows regardless
source label = per RUN, not per cell: "BSE+NSE <date>" (or the single
exchange that answered), plus an NSE-only count in the
console summary
If only one exchange published for the chosen day, the run proceeds single-source: quoted rows update normally, but the §6.5 status escalation is skipped (absence from one exchange is not evidence of anything). Both failing for all 7 days is the only hard failure (updater then degrades gracefully, keeping old prices).
https://nsearchives.nseindia.com/content/cm/BhavCopy_NSE_CM_0_0_0_<yyyymmdd>_F_0000.csv.zip
— zip containing one CSV, same common format, parsed and merged per §5.2.
NSE requires a browser-like User-Agent and a cookie warm-up GET on
https://www.nseindia.com/ first. A 200 response whose body is NOT a valid
zip (NSE serves bot-challenge HTML pages with status 200) is treated exactly
like NSE-unavailable: the day proceeds single-source on BSE — it must never
abort the fetch or discard already-parsed BSE data.
Resilience (v1.7.4) — losing NSE is not cosmetic: a security listed
ONLY on NSE (most ETFs, e.g. SILVERBEES) then keeps yesterday's price
while BSE-listed rows update, with nothing on screen to explain it. One
refusal must therefore not cost the day. The fetch: warms cookies on
https://www.nseindia.com/ and /all-reports (the page a browser
visits before downloading), sends the full browser header set including
Referer, and retries up to NSE_TRIES (3) times, re-warming each time;
each attempt tries the UDiFF archive above and then the legacy layout
…/content/historical/EQUITIES/<YYYY>/<MON>/cm<DDMONYYYY>bhav.csv.zip,
whose ISIN/CLOSE/PREVCLOSE columns the same parser reads. A response may
be a zip or a plain CSV (sniffed by an isin header, so a challenge page
is still rejected). Only after every attempt fails does the day degrade to
single-source.
Per held stock, fetch historical + announced actions from both exchanges and deduplicate:
NSE: https://www.nseindia.com/api/corporates-corporateActions?index=equities&symbol=<SYM>
(cookie warm-up required, like §5.3; free-text field: subject)
BSE: https://api.bseindia.com/BseIndiaAPI/api/DefaultData/w?Fdate=&Purpose=&TDate=
&ddlcategorys=E&ddlindustrys=&scripcode=<CODE>&segment=0&strSearch=S
(Referer: https://www.bseindia.com/ required; free-text field: Purpose;
Ex_date format "28 Oct 2024"; scrip codes come from the daily BSE
bhavcopy's FinInstrmId column — no extra mapping source)
The free text is classified identically for both: Bonus A:B / "Bonus issue
A:B" → BONUS; "split"/"sub-division"/"Stock Split" with "From To
" → SPLIT; "consolidation" → CONSOLIDATION. Dividends (v1.2, R9):
a subject containing "dividend" with a rupee amount ("Rs 8 Per Share",
"Rs. - 5.5000", "Re. 1/-", "₹2.50") yields a dividend record — type from
interim|final|special (default Final), rate in ₹/share. Two guards keep
garbage out of the rate: the currency token must not sit inside a word (the
"re" ending "…Per Share 2024" is not Re.), and any "face value of
Rs.N" phrase is masked BEFORE the rate search, so "Dividend - 300% on face
value of Rs.2/- each" parses no rate at all. Percent-of-face wordings
("Dividend 250%") are skipped and counted only when the ex-date falls in
the current FY — the feeds carry decades of history, and warning about a
2004 record is noise — and the updater reports the count so the user can
add a Manual row. Dividend records dedupe on (isin, ex_date, rate), NSE
wins — deliberately NOT on the type: the exchanges word the same event
differently ("Dividend" → Final on NSE vs "Interim Dividend" on BSE) and a
type-keyed dedupe would double-count it; two genuinely distinct same-day
payouts differ in rate and both survive. Everything else (rights, AGMs,
buybacks) is ignored. The normative contract is the record, not the URLs:
{ symbol, isin, ex_date, type ∈ {SPLIT, BONUS, CONSOLIDATION},
ratio_from, ratio_to, source ∈ {Auto, Manual}, details }
SPLIT/CONSOLIDATION ratio = old face : new face (e.g. 10:2 split → factor 5). BONUS ratio A:B = A new shares per B held (factor 1 + A/B).
Dedupe rule: records from the two exchanges merge on
(isin, type, ex_date) — ex-dates are exchange-synchronised — NSE record
wins. Manual rows on the Corporate_Actions sheet take precedence over an
Auto row with the same key.
Coverage rule (never skip silently, never revert): the fetch reports which ISINs were successfully answered by at least one exchange. Any held ISIN answered by neither MUST surface as a user-visible warning naming the scrip ("corporate actions could NOT be verified for: …"), so an unverifiable holding is a known condition, not a silent gap. One security failing must not abort the sweep, and — critically — must not REVERT that security: the Auto action rows (and current-FY Auto dividend rows, §3.13) of an unverified ISIN survive the rebuild untouched, so an already-applied split's quantities never snap back because one endpoint blocked one symbol on one day. Only an all-security/all-source failure degrades wholesale (keep every existing row). Since v1.4 (§6.15), merged holdings' SUCCESSOR symbols are queried too — the successor's dividends and later actions concern the old-ISIN rows. Mergers/demergers/ISIN reassignments remain out of scope for auto-adjustment from these feeds (no reliable free feed publishes ratios) — the Curated + Manual paths cover them.
| File | Shape | Source |
|---|---|---|
fmv_2018-01-31.csv |
isin, symbol, fmv |
NSE bhavcopy of 2018-01-31 (EQ/BE/BZ series, 1,639 ISINs); FMV = that day's high price (IT Act grandfathering definition). The symbol column enables lookup when a later corporate action reissued the ISIN (e.g. HDFC Bank post-split) |
banks_in.csv |
bank_name, type |
RBI scheduled commercial banks list + major SFB/payment/co-op banks |
ppf_rates.csv (roadmap — ships with the PPF contribution ledger) |
from_date, to_date, rate_pct |
MoF quarterly notifications, historical to present (no official API exists) |
epf_rates.csv (v1.3, R12) |
fy_start, rate_pct |
EPFO annual declared rates, historical to present (no official API — refreshed via releases); the updater fills a blank EPF Rate % with the latest row |
bullion_proxies.csv (v1.3, R13) |
metal, match ∈ {symbol_prefix, isin}, key, grams_per_unit, note |
exchange-traded ₹/gram proxies for the §5.7 fallback (SGB prefix for gold, SilverBeES for silver; GoldBeES was dropped 2026-07-17 — its live grams-per-unit had drifted ~17 % from the nominal 0.01 g, exactly the expense-ratio decay this column warns about); release-refreshed |
tax_rules_in.csv (v1.6) |
asset, effective_from, lt_days, stcg_pct, ltcg_pct, ltcg_exempt_inr, notes |
Indian capital-gains regimes for §6.16, keyed by effective date (Budget 2024 changed rates mid-FY on 2024-07-23); blank stcg_pct = at the user's slab. DEFAULTS only: the workbook's Tax_Rules sheet (§3.22) is upserted over these, so users can apply a Budget change themselves in Excel — releases refresh the CSV for everyone else |
PRIMARY : https://npstrust.org.in/nav-report-excel
(despite the name: TAB-separated text, verified 2026-07-16 —
ID, DATE OF NAV, PFM NAME, SCHEME ID, SCHEME NAME, NAV VALUE;
one row per scheme, latest published day)
FALLBACK: https://npscra.nsdl.co.in/download/NAVReport.csv (same record)
Columns located by header name, tolerantly; delimiter sniffed (tab vs
comma); keyed by SCHEME ID (e.g. SM001003); rows without a positive
NAV are skipped. Feeds the NPS_Master add-only merge and per-row NAV refresh
(code resolved from the row's Scheme Code column — lookup or override). No
API key; failure keeps old NAVs with a summary warning.
The flakiest data in the product, so it must never block a run:
1. PRIMARY : IBJA daily benchmark — https://www.ibjarates.com/
stable span ids: lblGold999_PM (₹/10 g), lblSilver999_PM
(₹/kg); _AM variants earlier in the day. Normalise to ₹/gram.
The rate the bullion trade quotes from; RBI uses IBJA 999 for
SGB redemption. No committed API — parse defensively, return
nothing on any doubt.
2. FALLBACK: market-implied ₹/g = median(close / grams_per_unit) over the
quoted proxies of data/bullion_proxies.csv (SGB tranches ≈
₹/g fine gold; SilverBeES ≈ 1 g) from the bhavcopy already
fetched — zero extra HTTP. Typically 2–4 % below the IBJA
retail benchmark (Guide says so). Only proxies whose implied
rate verifiably tracks the metal stay in the file (§5.5 —
GoldBeES was dropped for unit drift).
3. DEGRADE : both fail ⇒ keep each row's previous Rate today AND the old
rates-as-on stamp (amber past 7 days) + a summary warning.
The stamp advances only when a metal rate actually arrived —
SGB pricing alone never refreshes it (§3.15).
4. The sheet's Rate-override column always wins over the auto rate.
No reliable free feed publishes merger/demerger swap ratios, so the product
ships a curated, release-refreshed file (the ppf_rates/fmv precedent —
keeping it current is an ongoing release duty; anything missed is covered by
a Manual row on Corporate_Actions, which overrides a Curated row with the
same (old_isin, type, ex_date) key):
ex_date, type ∈ {MERGER, DEMERGER, ISIN_CHANGE}, old_isin, old_name,
old_symbol, new_isin, new_name, new_symbol, ratio_from, ratio_to,
cost_pct, details
- MERGER (old security absorbed):
ratio_from:ratio_to= A new shares per B old;cost_pct = 100— cost basis and holding period carry in full (Sec. 47 tax-neutral). - DEMERGER: one row per resulting security, grouped by
(old_isin, ex_date)— a parent-retention row (new_isin = old_isin, 1:1) plus one row per spun-off child (child shares per parent share, the company-notified income-tax cost apportionment). The loader validates Σ cost_pct = 100 per event and fails loudly — a silently wrong split would corrupt capital-gains numbers. - ISIN_CHANGE: 1:1,
cost_pct = 100.
Scope: index-grade events likely to touch retail portfolios (shipped v1.4.0:
the HDFC Ltd → HDFC Bank merger). Rows load as source = Curated, rewritten
from the file each run except the Applied date, which persists (§6.15).
§5.8's file is bundled with the app, which made every newly notified merger
or demerger wait for a new app version — the user's only recourse being
a hand-entered Manual row. Since v1.7.7 the curated list behaves like every
other feed: on each run the updater fetches the project's current
data/restructures.csv and merges it over the bundled copy, so an event
published today reaches every user on their next Update Portfolio, with
no action on their part and no new version.
- Source: the project's own repository over HTTPS (a public file; no user data is sent). ~8 s timeout, never blocking.
- One parser (§5.8) judges both copies, so a fetched row can never be accepted on weaker terms than a shipped one — in particular Σ cost_pct = 100 still holds, or the whole download is refused.
- Distrust-empty (the §5.1 AMFI precedent): a response that is short, is not this file, or carries fewer events than the app already ships is refused wholesale — a truncated download must never remove a merger the user's holdings depend on.
- Merge replaces whole EVENTS, not rows. A demerger is only coherent
as a group: if a correction moves a child to a different ISIN, a
row-by-row union would keep the stale child beside the new one — two
individually valid files combining into a list that apportions more than
100% of the cost and appends a holding that does not exist. So every row
sharing
(old_isin, type, ex_date)with the fetched file is dropped and replaced by it, the merged result is re-validated, and a merge that would break the 100% rule is discarded in favour of the bundled list. A Manual row on Corporate_Actions still overrides everything (§6.15). The run reports how many rows were new, corrected or withdrawn. - A correction that arrives after the event was already applied cannot be applied retroactively — the spun-off rows carry a frozen Avg. cost and the event is stamped Applied (§6.15) — so the run says so plainly rather than letting parent + child quietly stop summing to the original cost.
- The trust gate lives inside the fetcher, not in its caller: one
refresh_restructures(bundled)entry point fetches, floors and merges, so no call site can bypass the rule by omitting an argument. - Any failure — offline, proxy, 404, malformed — keeps the bundled copy, i.e. exactly the pre-v1.7.7 behaviour. A corporate-action refresh can never break an update.
- Timeouts are bounded per phase (connect and read), so an unreachable host cannot double the delay it adds to a run.
- Switched off with
--no-curated-fetch/NETWORTH_NO_CURATED_FETCH, and also by--no-update-check/NETWORTH_NO_UPDATE_CHECK— someone who turned off the version check meant "don't contact GitHub", and should not have to find a second flag. The CLI resolves either by handing the updater the bundled list, the way every other feed is suppressed (§7), rather than a per-feed "don't fetch" flag inside the update itself.
Consequence for the maintainer: keeping curated data current is a commit, not a release. The bundled file is still refreshed at release time as the offline baseline.
Inputs: cashflows [(date, amount)], sign convention outflow < 0, inflow > 0.
guard: < 2 flows, all same date, all same sign, |sum of days| == 0 → null
f(r) = Σ amount_i / (1 + r)^(days_i / 365) days_i from first flow date
solve f(r) = 0 by Newton from r=0.1, fall back to bisection on [-0.9999, 10]
tolerance 1e-7, max 100 iterations; no root → null
null results render as blank cells, never 0 or an error.
Per asset class (skip rows with missing required inputs):
| Class | Outflows | Inflows |
|---|---|---|
| Equity | −Invested @ Cost date (× the §6.15 cost factor when set — 0 is a real value, meaning the parent retained no cost; only blank means 1); v1.6: plus −Qty·BuyPrice @ Buy date per complete Equity_Sells row (§3.20; a blank buy price = the grandfathering path and stays out) | +Cur. val @ today; v1.6: plus +Rate·Qty @ ex-date per Dividends row (all FYs, Auto + Manual, ex-date ≤ today — estimates, §6.12), and +Qty·SellPrice @ Sell date per complete Equity_Sells row. New flows are APPENDED after the per-row pairs (flow order is part of the observable contract) |
| Mutual Funds | one per MF_SIP row: −Amount @ Date (redemptions are +) | +Cur. val @ today per (owner, ISIN) |
| Fixed Deposits | −Principal @ Start | +Value-as-on @ min(today, maturity) |
| PPF | −(Balance discounted at Rate% back to as-on date)… in practice: −Balance @ as-on | +Balance·(1+Rate%)^(days/365) @ today |
| Bonds | −Qty·BuyPrice @ Buy Date (skip if no Buy Date) | +Qty·CurrentPrice @ today; v1: plus each coupon +Qty·Face·(Coupon%/f) on its historical coupon date |
| EPF (v1.3) | −Balance @ as-on (PPF flat path verbatim) | +Balance·(1+Rate%)^(days/365) @ today |
| Gold & Silver (v1.3) | −Qty·BuyPrice @ Buy Date | +Cur. val @ today; SGB rows add each historical semi-annual coupon +Qty·BuyPrice·1.25% (§3.15 approximation note) |
| NPS (v1.3) | −Total contributed @ First contribution (row skipped when either optional input is blank) | +Units·NAV @ today |
| Property / Insurance / Other (v1.3) | −Invested @ Cost date (row skipped when Invested, Cost date or Value is blank) | +Current value @ today |
| Cash (v1.3) | excluded from XIRR entirely (has_xirr false, §2.1) — a balance has no meaningful money-weighted return |
Class XIRR = solver over that class's union. Portfolio XIRR = solver over the union of the enabled classes only (v1.4.3, §2.1) — a switched-off class contributes nothing to the family figure; its per-class value may still be computed (harmless, and ready when re-enabled) but is written nowhere while hidden. Written as plain values to: Dashboard B4, the allocation table's XIRR column, Equity class cell, MutualFunds L column + MF_SIP J2.
v1.6 note: because dividends and recorded sales now enter the equity flows, every user's equity/portfolio XIRR shifts once on the first v1.6 update — said plainly in the release notes and in the Portfolio XIRR hover gloss (Dashboard A4, the label cell beside B4). A typed 0 buy price is a real cost (bonus/ESOP shares) and its round trip counts; only a BLANK buy price (the grandfathering path) stays out of XIRR — that sale still appears in the tax view, and the Equity_Sells Buy-price gloss says so (the one place the two surfaces deliberately differ). Likewise a typed 0 sell price (a write-off) is a real, counted flow. Known limitation (documented, accepted): frozen prior-FY dividend rows of a holding that was later sold and deleted contribute inflows without their buy outflow; recording the sale on Equity_Sells supplies the missing round trip.
From Maturity Date step backwards by 12/f months (f default 1 = annual) to
Buy Date; coupons with date ≤ today enter the XIRR cashflows; future coupons
- redemption feed only the Maturity Value / FY-end figures.
new = fetched list; existing = current master rows (key: ISIN)
for isin in new: if isin not in existing → append (symbol, name, isin)
never rename or delete an existing row (user rows key on the NAME)
EXCEPT (v1.7.4) a PLACEHOLDER row whose name IS its own ISIN — seeded
by §6.15 before the security listed: adopt the feed's real name and
return it to the caller, which relabels any Equity row still showing
the placeholder (those rows price by their own ISIN cell, so this is
cosmetic). Add-only protects names a user CHOSE; an ISIN-as-name was
never chosen, and leaving it would make it permanent.
resort whole table by name (ordinal, case-insensitive) # dropdown requirement
write refresh date to E2
MF_Master is regenerated wholesale from AMFI each refresh (same sort rule) but must also preserve any ISIN currently referenced by a user row even if AMFI drops it (append with its last-known names).
ETFs are seeded into Stock_Master from MF_Master (v1.7.5). An ETF is a
fund that TRADES: people buy it through a broker, hold it in demat and
expect it in the Equity dropdown, so it belongs on both masters. After the
AMFI refresh, every MF_Master scheme whose name contains ETF (as a word)
or exchange traded — excluding fund of fund/FOF, which are NOT traded
— is merged into Stock_Master through the same add-only merge, with a BLANK
symbol (AMFI publishes no exchange tickers; a later bhavcopy row supplies
one). Two consequences, both required:
- ETFs are listed even on a day an exchange refused us, which is what made an NSE-only ETF unpickable before;
- a holding with no symbol and no BSE code cannot be queried for corporate actions, so it is EXCLUDED from the §6.7 "could not be verified" warning (it was never asked about — naming it every run would be pure noise).
ETF price fallback (v1.7.5). After the AMFI refresh, an Equity row whose
ISIN no exchange quoted THIS RUN but which has an AMFI NAV takes that NAV as
its close (stamped with the run's trade date). An ETF's NAV is within a
whisker of its traded price, and a same-day NAV beats a days-old quote. A
real exchange quote always wins: the fallback only fills rows absent from
priced_today, so it can never override the market.
for each held ISIN, at update time:
quoted in the merged bhavcopy → Status=Active, LastTraded=bhavcopy date
absent — SINGLE-SOURCE run → carry the previous status forward
untouched (absence from one exchange is
not evidence; prevents a false Suspended
during a one-exchange outage)
absent from BOTH exchanges:
≤ 21 calendar days → keep last price/status; the live amber
"stale" conditional format fires anyway
once Closing Price Date is > 7 days old
> 21 calendar days → Status=Suspended (amber via status CF)
> 180 calendar days → Status=Delisted (amber via status CF)
Suspended/Delisted rows keep their last price and Closing Price Date; the
updater never overwrites an unquoted row's price, so a manual price typed
into F simply persists. Skipping escalation on single-source days loses
nothing — the thresholds are in days, not runs. v1.4: ISINs consumed by a
restructure carry status Merged/Renamed instead and are EXEMPT from this
escalation (§6.15) — their absence is expected, not distress.
Status + Last Traded live in Stock_Master columns D/E (written only for held
ISINs). Equity surfaces both flags with conditional formats: stale via
TODAY()-$H4>7 on the price cells, suspended/delisted via an INDEX/MATCH
status lookup on the Scrip cell — both live formulas, no stored flags.
For an Equity row with Quantity and Cost date but blank Avg. cost:
if Cost date < 2018-02-01 and Avg. cost is blank:
fmv = FMV by ISIN, else FMV by exchange symbol # ISIN may have been
# reissued post-split
if fmv: write it into E (amber format + explanatory comment),
set the Q-column flag to "FMV"
else: row stays without Invested/XIRR (as today)
The Q flag makes the fallback round-trip regeneration (the cell keeps its amber + comment and is never mistaken for a user-typed cost), and lets the capital-gains report apply the true grandfathering rule — higher of cost vs min(FMV, sale price) — shipped in v1.6, §6.16.
User rows always hold raw, as-purchased Quantity / Avg. cost. Never mutate them. At update time:
for each Equity row (isin, qty_raw, cost_raw, cost_date):
factor = Π over actions a on isin where a.ex_date > cost_date and a.ex_date ≤ today:
SPLIT: old_face/new_face (10:2 → 5)
BONUS A:B: 1 + A/B
CONSOLIDATION: old/new (< 1)
qty_adj = qty_raw · factor
cost_adj = cost_raw / factor
The updater writes factor into the Equity S (Adj factor) column (blank
when 1); the sheet's Cur. val / Day chg. formulas multiply Quantity by it,
while Invested (qty_raw·cost_raw) is unchanged by construction. XIRR and the
FY-end estimate use the adjusted current value. Idempotent: recomputed from
raw + action list every run; a future-dated action has factor 1 until its
ex-date arrives.
Demat view — zero user action: columns O (Qty today) and P (Avg
cost today) re-express the holding in post-action terms (D×factor,
E×cost_factor÷factor — the §6.15 demerger retention applies to the basis
too) so the sheet matches the user's demat/broker app after every
split/bonus/demerger, purely from the Corporate_Actions sheet content. By
Scrip quantities and the person-sheet Equity blocks read O/P (not raw D/E).
The Corporate_Actions sheet is the audit trail: columns
Symbol, ISIN, Type (dropdown), Ex-Date, Ratio From, Ratio To, Factor (=IF(type="BONUS",1+E/F,E/F), computed), Source, Details (+ the §6.15
restructure columns New ISIN, Cost %, Applied and, v1.7.4,
New name, New symbol), data rows 4..203. The two naming columns exist
because a hand-entered restructure previously had no way to say what the
new company is CALLED: _event_name fell through to the raw ISIN, which
then became the child row's Scrip and was seeded into Stock_Master —
where add-only (§6.4) made it permanent. Curated rows always carry a name;
Manual rows now can. Auto rows are rewritten from the
feed each run; Manual rows are user inputs and persist (they also override
an Auto row with the same isin/type/ex-date). Row order & capacity:
Manual and Curated rows are written FIRST — they carry user data and the
§6.15 Applied stamps and must never fall past the last row. If the assembly
still exceeds capacity, the OLDEST Auto rows (by ex-date) are dropped and
the run warns with the count — never a silent truncation. If Manual/Curated
rows ALONE exceed capacity the run refuses up front (§7 step 5 refusal
semantics): a truncated Applied stamp would re-apply its demerger next run
and duplicate the child rows.
FY end = next 31 March ≥ today. Per holding:
FD : same compound formula with YEARFRAC(Start, min(FYend, Maturity))
PPF : Balance·(1+Rate%)^(YEARFRAC(as-on, FYend))
EPF : Balance·(1+Rate%)^(YEARFRAC(as-on, FYend)) (v1.3)
Bonds : Qty·CurrentPrice + coupons falling in (today, FYend] (redemption if Maturity ≤ FYend: Qty·Face instead)
Equity/MF: CurVal·(1+ExpectedReturn%)^(YEARFRAC(today, FYend)) — estimate,
driven by the Dashboard "Expected return %" input
Gold & Silver / NPS: market-linked — same ExpectedReturn% growth (v1.3)
Manual (Property/Cash/Insurance/Other): held FLAT at Current value (v1.3 —
estimating property or surrender-value appreciation would be
false precision; the header comment says so)
Classes switched off in Settings contribute nothing (v1.4.3, §2.1).
Aggregated per person + TOTAL into the Dashboard Expected @ 31-Mar-<FY>
column; the estimate nature is stated in the header comment.
Applied by the generator as column-range conditional formats (see §3.2). Precedence: amber (data quality) overrides red/green on the affected cells.
Official rule: interest accrues each month on the minimum balance between
the close of the 5th and the last day of the month, and is credited on 31
March. A deposit on/before the 5th earns that month; a later one does not.
Rates are bundled (data/ppf_rates.csv, from_date,rate_pct ascending step
table; quarterly since Apr-2016, annual before) and refreshed via releases —
no API exists. No withdrawals modelled (accumulation), so the monthly minimum
is the balance as of the 5th.
ppf_value(deposits, rates, as_of) -> (balance, total_interest):
walk months from the first deposit to as_of
each completed month: interest = (credited + deposits_on_or_before_5th)
* rate(mid-month)/1200 ; accrue
add all the month's deposits to the credited balance
at each 31 March: credit the FY's accrued interest (annual compounding)
balance = credited + interest accrued since the last 31 March
Optional ledger: the PPF_Ledger sheet holds one row per deposit
(Owner, Account No., Date, Amount). An account with matching ledger rows gets
exact balance_today, interest_earned and a real dated-cashflow XIRR,
written by the updater into PPF columns H/I/J. An account with no ledger keeps
today's behaviour: Balance today (H) is the live formula =IF($D="","",$D)
(the typed Current Balance). The updater also auto-fills a blank Rate% (F)
with the current bundled rate. Dashboard and person-sheet PPF totals sum
Balance today (H), so both paths flow through identically. Class PPF XIRR
and the FY-end estimate use ledger accrual where a ledger exists, else the
flat estimate.
The updater records one dated snapshot per run into the History sheet.
Columns are label-keyed, not positional: the header row is Date + the
label of every class that is enabled OR carries nonzero history (recorded
numbers are never dropped by a toggle) + Total (=SUM across that row's
class columns). The reader maps columns back by header label ("Real Estate"
accepted for Property) — unknown labels are ignored, absent classes read as
0 — so a pre-v1.3 workbook (fixed five columns) reads losslessly and old
totals recompute identically. Per-class values are computed in Python to
mirror the Dashboard (equity qty×factor×close, MF units×NAV, FD compound
value, PPF Balance-today, bonds qty×price; FD uses actual/365, a hair off
Excel's 30/360 — immaterial for a trend). v1.4.3: before the snapshot
is stored, every switched-off class is zeroed — "not counted" holds for the
trend too, and the run's one-line warning states the measured value that
was left out. Old rows keep whatever they recorded (they were true then).
One row per calendar day: a re-run on the same day overwrites that
day's row; rows are capped to the most recent HISTORY_LAST_ROW-3. The
Dashboard carries a line chart over History Date × Total plus a
stacked-area chart whose series cover only the currently-shown classes
("Net worth by class over time"). History rows are data — the reader
loads them and the generator writes them back, so they survive
regeneration.
qty_est(owner, isin, ex) =
Σ over Equity rows r where
(r.isin == isin OR resolve(r.isin) as of ex−1 == isin) # §6.15
AND r.owner == owner
AND (r.cost_date is blank OR r.cost_date < ex):
r.qty × chained_adjustment_factor(r.isin, r.cost_date, ex − 1 day, actions)
The CA factor is evaluated as of the day before the ex-date, so a split/bonus between purchase and dividend ex-date adjusts the count, while later actions do not. The chain-aware form (§6.15) makes a lot still keyed to a merged-away ISIN earn the SUCCESSOR's dividends at the merger-adjusted share count. Blank cost-date lots count as held (consistent with the FMV-era treatment, §6.6). Known, documented limitation: there is no sell ledger, so the estimate projects the current rows backwards — rows deleted or reduced after a sale make history wrong. Hence the amber "(est.)" formatting on Qty/Amount, the sheet-hint sentence, and the user's ability to correct the Qty on a Manual row. An event yields one row per owner with qty_est > 0; each row's Est. amount = rate × qty (a live formula).
for each effective-enabled class c with a non-blank Target %:
actual_c = value_c / family_total (live formula)
drift_c = actual_c − target_c (percentage points, absolute)
verdict_c = |drift_c| ≤ tolerance → green, "On target"
otherwise → red, "Move ₹|drift_c × total| out|in"
classes with a blank target show nothing (no drift, no hint, no CF)
sanity: Settings B18 = Σ targets, amber when non-zero and ≠ 100
Tolerance (Settings B17, default 5) is absolute percentage points — relative bands over-trigger on small sleeves (a 2% gold sleeve at 3% is 50% relative drift but irrelevant money). The hint is deliberately class-level, gross and pre-tax (the header comment says so); lot-level tax-aware selling belongs to the capital-gains roadmap item. Everything here is Excel formulas — correct the moment the user edits a holding, without running the updater.
per Gold_Silver row, at update time:
Type = SGB → Rate today = merged-bhavcopy close for the ISIN
Type = Gold | Silver → Rate today = §5.7 layered ₹/g rate for the metal
any rate written this run → I2 "Rates as on" stamp = run date
no rate obtainable → row keeps its previous Rate today; the stamp
keeps its old date; amber CF fires past 7 days
valuation (sheet formula, live): Cur. val = Qty × (Purity or 1)
× (Rate override if set, else Rate today)
The Rate-override precedence is a sheet formula, not updater logic — a user typing their jeweller's rate sees the value change instantly.
Events come from §5.8 (Curated) and Manual rows; a Manual row overrides a
Curated one with the same (old_isin, type, ex_date, new_isin) key —
new_isin is part of the key because a demerger's retention row and child
rows share the first three fields and must track their Applied dates
independently. Curated events are surfaced when their old ISIN is held
directly or via a restructure chain (a demerger announced on a
merger-successor concerns the old-ISIN lots too). Routing/pricing runs
before pricing; demerger child creation runs after the §5.4
corporate-actions refresh (see below). The engine NEVER edits a user cell.
MERGER / ISIN_CHANGE — no new rows.
resolve(isin): follow old→new hops (ex_date ≤ today), cycle-capped at 10
pricing : the row's close/prev/date come from resolve(isin)'s quote
qty factor : the merger ratio (A new per B old = A/B) folds into the
existing Adj factor S via the chain-aware factor — later
splits/bonuses on the SUCCESSOR keep applying to the row
cost basis : untouched — Invested and Cost date carry in full (Sec. 47);
Flags column shows "MERGED→<name>" / "ISIN→<isin>"
status : the consumed ISIN's Stock_Master status becomes Merged /
Renamed, which EXEMPTS it from the §6.5 Suspended/Delisted
escalation (its bhavcopy absence is expected, not distress)
DEMERGER — the one case needing two mechanisms. For each event (parent
row new_isin = old_isin with the retention cost_pct; child rows with
their own new_isin, ratio and cost_pct):
1. Cost factor (column T, recomputed each run like S):
per equity row with cost_date < ex ≤ today:
T = Π retention cost_pct/100 over such events (chain-aware)
Invested = Quantity × Avg. cost × T (the user's cells stay put)
2. Append-once child rows (per owner-lot × child), on the FIRST run where
ex ≤ today and the event's Applied is blank. Runs AFTER the §5.4
corporate-actions refresh — a fresh workbook's sheet holds no history
yet, and a child qty frozen from an incomplete table would be wrong
forever. Matching is chain-aware: a lot demerges when its own ISIN OR
resolve(its ISIN) as of ex−1 equals the event's old ISIN.
qty = lot qty × chained_adjustment_factor(lot_isin, cost_date,
ex−1) × A/B (merger ratios fold into the count)
avg cost = lot raw invested × Π(retention cost_pct/100 of every
EARLIER demerger on the chain, i.e. cost_adjustment_factor
as of ex−1) × child cost_pct/100 ÷ qty (per share) —
a second demerger apportions what REMAINED, not the
original cost, or totals inflate past 100%
cost date = the PARENT lot's cost date (Indian CGT: demerged
shares inherit the holding period)
flag = "DEMERGER:<old_isin>@<ex_date>"
The event's Applied date (persisted on its Corporate_Actions row) is the
single idempotency token: re-runs skip applied events, so a user deleting
a child row (e.g. after selling it) is respected.
safety gates — an event that cannot apply SAFELY is skipped, warned about,
and NOT stamped, so it retries on a later run:
· verification: on a live run, the parent ISIN must be among the ISINs
the §5.4 fetch successfully checked (injected/test data is trusted);
· capacity: all of an event's child rows must fit within the Equity
sheet's data rows — children land atomically per event (a partial
append that retried would duplicate its early rows).
invariant: parent Invested×T + Σ child Invested = original Invested
(to the rupee; conservation is a test), and this holds through
CHAINED events (merger→demerger, demerger→demerger)
The successor's (symbol, name, isin) joins Stock_Master immediately
(add-only compatible — a new ISIN), so appended rows resolve before listing;
an unlisted child simply has a blank price (excluded from day-change, no
escalation) and prices automatically on first bhavcopy appearance. Known
accepted wart: By-Scrip / person-sheet blocks group a merged row under its
old name until the user re-keys it — values are unaffected.
All figures INDICATIVE (planning, not filing); when something can't be
computed correctly it is skipped with a warning — never guessed. Constants:
GRANDFATHER_DATE = 2018-01-31, GRANDFATHER_CUTOFF = 2018-02-01,
DEBT_MF_SLAB_FROM = 2023-04-01 (purchase-date rules live in code, not the
rate table), DUST_INR = 1.0 (FIFO residuals worth less than ₹1 are float
rounding — amounts are paise-rounded, units NAV-derived — never treated as
holdings or oversells).
The engine runs only when the user is using the feature (a sale recorded or the §3.14 switch on); every other build writes the empty Capital Gains sheet and skips the compute. The updater computes the report ONCE and passes it to the build, so console line and sheet can never differ. A typed 0 price is real data (a delisted write-off, a bonus-share cost) — only an empty cell means "not entered", so completeness guards test for None, never falsiness. If more sale rows exist than the sheet holds (§3.20), the extras still count in this run's figures but are dropped from the saved file — warned loudly.
Tax rates come from the workbook's Tax_Rules sheet merged over the
bundled data/tax_rules_in.csv defaults (§3.22 — nothing, including the
₹1.25L exemption, is hard-coded; a Budget change is an Excel edit). Rows
are keyed by asset (equity | mf_equity | mf_debt | mf_other) + effective_from
date — NOT by FY, because Budget 2024 changed the equity rates mid-year
on 2024-07-23 (STCG 15→20 %, LTCG 10→12.5 %, exemption ₹1L→₹1.25L).
mf_debt carries TWO rows: the old regime from 2018-04-01 (lt_days 1095;
both rates blank — STCG at slab, LTCG 20 % with indexation is not modelled
so no amount is shown) and the Budget-2024 regime from 2024-07-23 (lt_days
730) — without the old row, pre-2024 debt-fund sales would mislabel Term
via the 365-day fallback. mf_other (v1.7.5) is the same shape for
LISTED non-equity ETFs (gold, silver, overseas) marked Gold-Silver on the
Equity sheet: the old regime from 2018-04-01 (not modelled, as mf_debt) and
from 2024-07-23 lt_days 365, stcg blank (slab), ltcg 12.5, exempt 0 —
they are listed securities, so long-term arrives after 12 months, they sit
outside Sec 50AA (which covers >65 %-debt funds), and they get NO §112A
allowance. The rule in force is resolved per SALE date
(newest effective_from ≤ sale date); a date no rule covers (pre-FY-2018-19,
the §10(38) era) shows tax "—". stcg_pct blank = taxed at the user's slab
(words, never an amount). Malformed CSV rows raise loudly in the LOADER (a
wrong tax table is worse than none) but the ENGINE degrades that to a
warning and falls back to the workbook's rows alone — one bad bundled row
must never crash every user's update. Invalid WORKBOOK rows warn and are
excluded per §3.22.
fmv_per_share(isin, at): # §6.6 value in `at`-day units
raw = FMV by ISIN, else by exchange symbol
return raw / chained_adjustment_factor(isin, 2018-01-31, at, actions)
EQUITY realised — per complete Equity_Sells row (else warn + skip; a
negative qty or price warns "check the row"; a future sell date warns;
sell < buy warns "check the two dates"). buy = sell (same day) is
**speculative income** (Sec 43(5)): a realised row with bucket
"speculative", term "Intraday", gain = Qty·(Sell − Buy) — slab-taxed
business income shown so nothing is hidden, NEVER mixed into STCG/LTCG,
no tax amount computed, and kept out of XIRR (§6.2: not investment
return, and a zero-duration pair is degenerate for a money-weighted
rate). An intraday row with no buy price warns + skips.
cost/sh = Buy price # 0 is a real cost; None skips
if Buy date < CUTOFF and fmv found:
cost/sh = max(Buy price or 0, min(fmv_per_share(isin, Sell date),
Sell price)) # grandfathering
note "grandfathered"; blank Buy price → note says the 31-Jan-2018
market value (FMV) was used, an estimate
term = Long-term iff (Sell − Buy).days > rule.lt_days (365)
gain = Qty · (Sell price − cost/sh)
MF realised — FIFO per (owner, scheme) over MF_SIP in txn-date order:
purchases (+amount) push lots {date, units, cost/unit};
each redemption (−amount) consumes lots front-first at its NAV
(no NAV and no units override → warn + skip;
residuals — leftover lot or unfilled redemption — worth ≤ DUST_INR
at the sale NAV vanish silently as rounding, anything larger left
unfilled → match what exists, warn the shortfall)
bucket: MutualFunds "Tax type" column. Tax type is a property of the
SCHEME (a fund is equity or debt for everyone): the first non-blank
row wins and conflicting rows warn — never last-writer-wins. No
non-blank row anywhere → mf_equity + note "assumed Equity". Debt lots
bought ≥ DEBT_MF_SLAB_FROM → bucket "slab" (term "At your slab", no
amount); equity-MF grandfathering is NOT computable (no 2018 NAV data
bundled) → actual cost + note "gain may be overstated"; indexation
unsupported.
FY summaries (fy_label; newest first): the equity bucket = equity +
mf_equity, sharing ONE §112A exemption. STCG tax is computed on the NETTED
short-term figure shown beside it: same-FY short-term losses are set off
against the highest-taxed gains first (the taxpayer-favourable order), each
remaining gain at ITS OWN asset's rate — equity vs mf_equity per the row's
bucket, only the §112A exemption bucket is shared — on its own sell date
(handles the mid-FY switch; any uncovered gain ⇒ whole figure "—").
Sec 70 same-FY set-off (v1.6.1 equity-family; v1.6.2 ACROSS buckets) —
computed ONLY inside the FY-end-rule guard (rule-less pre-2018 §10(38)
FYs — LTCG then exempt, losses carry-forward only — keep st_setoff 0,
like their blank tax cells). Per FY, with dust(x) = 0 when |x| < ₹0.005
(float residue must never defeat blank-when-zero or nudge a tax figure):
debt_st_net = dust(Σ NON-EQUITY Short-term rows + Σ slab rows) (Sec 50AA
deems post-2023 debt lots short-term). NON-EQUITY =
mf_debt + mf_other (v1.7.5) — the set-off nets by TERM, so
a bullion ETF belongs in the same head as a debt fund
debt_lt_loss = dust(max(0, −Σ NON-EQUITY Long-term rows)) [Sec 70(3): LT
losses only against LT gains, cross-asset]
debt_st_loss = max(0, −debt_st_net)
excess_st = dust(max(0, −(STCG_netted + debt_st_net)))
— the WHOLE short-term head nets first, both directions: a
debt/slab ST gain absorbs an equity ST loss and vice
versa; only a genuine all-ST net loss spills to LTCG.
Rate-independent (the tax loop skips unknown-rate rows, so
ITS leftover would overstate the excess).
st_setoff = dust(min(debt_lt_loss + excess_st, max(LTCG, 0)))
st_sheltered = dust(min(debt_st_loss, max(STCG_netted, 0))) — the debt
losses absorbed by the equity ST figure, surfaced on the
console so "STCG ₹1L · tax ₹0" never looks impossible.
The STCG tax loop's loss pool = equity-family ST losses + debt_st_loss
(losses offset the highest-taxed gains first, taxpayer-favourable).
Speculative rows feed nothing (Sec 73). Leftover losses are simply unused:
never applied to debt/slab gain DISPLAYS (their raw sums always stay
as-is), never added to headroom, never carried forward.
LTCG: LTCG_eff = max(LTCG − st_setoff, 0), ONE shared derivation (a
FYSummary property in the reference implementation) used by the FY row AND
headroom_now so the two surfaces cannot drift; subtract the FY-end rule's
exemption once from LTCG_eff, residual at the FY-end LTCG rate. Every raw
column (STCG, LTCG, debt, slab) stays RAW — set-offs show only in the
"Losses used vs LTCG ₹" column and the tax/allowance figures. Set-off
against other income heads and carry-forward are NOT modelled (stated on
the sheet, and the updater console prints the set-off beside the raw
figures so its numbers reconcile).
headroom = max(0, exemption − LTCG_eff) — unused excess loss is NOT added
to headroom (conservative, mirroring how a net long-term loss is clamped;
the r3 headline's gloss says so); the current-FY headroom is also computed
when nothing was sold yet (the r3 headline) and uses the same post-set-off
figure.
UNREALISED (sell-planning): per current Equity row — value/cost arithmetic
IDENTICAL to §6.2 (qty·ca_factor·close vs qty·avg_cost·cost_factor) so the
two surfaces can never drift; grandfathered cost in today-units when
applicable; `lt_on = cost_date + (lt_days + 1)` gives the exact date the
holding turns long-term. Per open MF lot (the FIFO remainder) at the fund's
current NAV — carrying the SAME caveat notes as the realised rows
("assumed Equity", pre-2018 "gain may be overstated"): a planning figure
must not hide its guesses.
Fills Equity and MF_SIP from files the user can obtain themselves: the CAMS/KFintech detailed CAS PDF (complete MF transaction history, password-protected) and broker equity CSV exports (tradebook / holdings; parser registry per broker + a generic header-matched fallback). The pipeline is parse → validate → reconcile → preview → write and every stage may refuse; parsers reduce files to normalized records (ImportedSipTxn / ImportedTrade / ImportedHolding inside an ImportBatch that also carries the statement's own per-(folio, ISIN) closing unit balances) and ONE merge engine reconciles batches into the data model — no parser ever touches a sheet.
The never-garbage contract (normative). A wrong number costs more trust than typing ever saved, so garbage must be structurally unable to reach a sheet:
- Triangle identity: an MF transaction is accepted only if
|amount − units × NAV| ≤ max(₹1, 0.1% of amount). Rows that carry units without money (bonus / segregation) are exempt from the triangle but must carry a zero amount AND their fund must have a declared closing balance — units no money proves and no balance can check are refused, not trusted. Accepted ones land as amount-0 rows (valuation counts the units via the units override; the cash-flow return figure ignores the zero leg — a documented approximation). Sign-vs-type: a redemption/switch-out must read as money out and a purchase/switch-in as money in — enforced in the MERGE engine, not any one parser, so a drifted layout in a future parser cannot flip a sale into a purchase. - Balance reconciliation: when the statement declares a closing unit balance for a (folio, ISIN), the parsed history must sum to it within ±0.001 unit. For a CAS the balances are MANDATORY: a balance line that was seen but couldn't be parsed (opening or closing) refuses the fund — an unreadable opening must never default to 0, which would disguise a mid-history statement as since-inception — and a fund whose closing line was never found is refused rather than imported unreconciled.
- Chronology: a negative running-units balance mid-history refuses the fund (sell before buy = ordering or parse corruption). Month names in dates are resolved by the importer's own table, never the OS locale.
- Atomicity per fund: ONE unprovable row refuses that fund whole — funds import completely reconciled or not at all; sibling funds in the same file still import. Every refusal carries a plain-words reason; the preview shows each fund's Σ invested / Σ units with a tick or the reason it was left out — never silence.
- Hardened field parsing: lakh grouping (1,23,456.78), parenthesised negatives, the Indian date shapes (DD-Mon-YYYY, DD-MM-YYYY, …); sanity bounds (dates 1990-01-01..today, NAV > 0, |amount| < ₹100 crore). Anything outside the recognised shapes parses to nothing and quarantines its line — leniency is how garbage survives.
Merge semantics. Statement rows carry exact units into the existing units-override path (never recomputed). Scheme names resolve via MF_Master by ISIN (master name wins so the sheet's INDEX/MATCH lookups work); unknown ISINs keep the statement name plus an ISIN override — which also marks the ISIN as referenced, so the master refresh keeps it. A MutualFunds summary row is created for any (owner, scheme) that lacks one (Tax type left at the default). Two write modes:
- statement-wins (only after an interactive confirmation): for funds
the statement covers, typed rows of that (owner, ISIN) are replaced by
the exact history — the pre-run backup is the safety net; afterwards
the sheet's units for the fund must re-equal the statement balance.
The replace is folio-blind (the sheet stores no folio), so two guards
keep it from deleting history the statement doesn't cover: (a) an ISIN
any of whose folios the PARSER refused (mid-history, unreadable
balance — the batch marks them
partial) is refused whole for every owner, and (b) when the typed rows' net amount exceeds the statement's net amount for the fund by more than 5% + ₹1,000, the statement is presumed to be missing a folio and the typed rows are kept, with a plain-words reason either way. - append-only (the headless default — never destructive without a
human): multiset upsert keyed
(owner, ISIN, date, amount₂dp)with multiplicity, so two genuine same-day SIPs import as two rows while a re-import adds zero. Re-running on the same or a newer statement is always idempotent ("second run adds 0" is a release-gate test).
Capacity: the merge engine pre-counts; an import that would exceed a sheet's row budget is deferred WHOLE with a plain message (the workbook is untouched — mutations to shared rows are only PLANNED until every gate passes — and the update itself continues) — §7 step 5's refusal guards typed rows only. Exception, MF_SIP only and interactive only: when a rough estimate (rows in use + statement rows) says the ledger may overflow, the user is asked UP FRONT whether the oldest years may be rolled up; with that consent, an actual overflow triggers condense_txns retries at financial-year cutoffs — least condensing first, the current FY never rolled — until the import fits (each retry re-runs EVERY gate; condensing conserves Σamount and Σunits per fund, so the closing-balance reconciliation still proves the fund, and the OPENING row's NAV is amount/units so the triangle holds by construction). If even full condensing cannot fit (too many distinct funds), the plain deferral stands. Headless runs never condense. Owner attribution is a persisted mapping folio/account → person (the Import_Map sheet, §3.23); a new account prompts once, an unmapped account on a headless run skips its rows with a warning. Broker files with no client-id column key the answer by the FILE NAME (a blank key would not round-trip, and the question needs a readable label). A stored Owner that no longer names a person (typo, renamed on the Dashboard) never silences the question — it is re-asked and the fresh answer UPDATES the stored row. The never-nag memory only records a file as imported when its content actually landed: a capacity deferral or a fixable refusal (unmapped owner) leaves the file offerable on the next run, so "fix it and run again" always works. Headless runs act ONLY on files passed explicitly via --import — the folder sweep is interactive-only (never a file nobody was asked about). Equity trade netting: §6.18 (v1.7 broker import).
Fund units in broker holdings files (v1.7.1): demat-held funds (broker platforms) are often ABSENT from the CAMS/KFintech CAS, so the holdings file is their only route in. Rows whose ISIN classifies as a fund (§6.18) route to a dedicated merge: each fund lands as ONE opening MF_SIP line — units = the broker balance, amount = units × the broker's average cost, NAV = that average (the triangle holds by construction), dated the run day. Values are right immediately; the return figure counts from the run day until real dates arrive (typed, or a later CAS import whose statement-wins replace covers the fund). Scheme identity resolves by ISIN against MF_Master ONLY — fund names are never fuzzy-matched; an unknown ISIN keeps the file's name plus an isin_override. Gates: a fund with NO average cost is refused (nothing to value the money in); an unmapped account is skipped re-askably; a fund already on the sheet (any MF_SIP rows for that owner+ISIN) is cross-checked against the broker balance — warning on mismatch, never doubled — so re-runs add zero; capacity defers the fund-holdings portion whole. A MutualFunds summary row is ensured per new (owner, scheme).
ISIN classification (v1.7.1) — broker files mix instruments, and each
class has its own honest route; nothing rides in on the accident of a
filled ISIN column. The series digits (the two digits after the 5-character issuer code of an Indian ISIN)
rank ABOVE Stock_Master membership, because the master is built from the
bhavcopy, which also lists traded NCDs — membership alone must never
make a bond "equity". Rules, in order: INF… is equity when the
master trades it (a listed ETF), else a fund unit (→ the
fund-holdings merge, §6.17; fund TRADES are refused per ISIN pointing at
the CAS); series 01 is a share; series 07/08/09 is debt
(NCD/bond — refused, "the Bonds sheet is filled by hand") even when the
master has it; anything else (REITs/InvITs, G-secs, foreign) is equity
only when the master prices it and refused plainly otherwise. A row with
no ISIN at all keeps the "not in the stock list" refusal, which now also
names the holdings-with-ISIN route for funds.
Generic by design: a registry of exact header signatures for verified export layouts (Zerodha tradebook/holdings first), then a fuzzy fallback that matches header CONCEPTS — a tradebook needs symbol-or-ISIN, date, buy/sell, quantity and price; a holdings file needs symbol-or-ISIN, quantity and average price (and no date/side columns); a LEDGER-style transaction register (traditional back-offices, e.g. MoneyMaker) with separate Buy Qty/Buy Rate and Sell Qty/Sell Rate columns parses each row into its buy and/or sell leg. Holdings average-cost matching is strict-before-loose: an explicit header ("Avg. Cost", "Average Price", …) anywhere in the row always wins; the bare back-office labels ("Rate", "Net Rate", "Holding Rate") count as avg-cost ONLY when no explicit header exists — a market-price "Rate" column must never shadow a real "Avg. Cost" column, since a holdings row has no triangle identity to catch the wrong value. XLSX exports parse directly (first sheet → text rows → the same pipeline; the workbook being updated is excluded from discovery and never matches the header sniff). A holdings average of 0 means the broker does not KNOW the cost (demat-converted paper shares): the row lands with a BLANK cost — never ₹0 — and the interactive pre-2018 question may date it 31-01-2018 so the §6.6 value fills in; symbols carrying an exchange series suffix ("AJMERA EQ") resolve against the master's bare symbol. Matching is on headers only; every value still passes the hardened parsers, and an unrecognisable file fails politely naming the headers it saw. Multiple exchange fills of one order collapse to one trade per (account, security, date, side) at the weighted average price. Symbols without an ISIN column resolve through Stock_Master's symbol column; an unresolvable symbol's trades are left out with a warning (never guessed).
Netting (per owner + ISIN, chronological FIFO): buys append lots;
sells consume imported lots first and — ONLY in replace mode, i.e. after
an interactive confirmation — then the sheet's typed lots (oldest cost
date first; a fully consumed typed row is removed, a partial one has its
quantity reduced). Surviving lots land one row each (the lots-are-rows
norm, §3.6) flagged IMPORTED:<source>; consumed (lot, sell) pairs land
on Equity_Sells while the Capital-gains switch is on. With the switch
OFF, netting among the file's own lots still proceeds silently, but a
sale that would consume a TYPED sheet lot is REFUSED (per ISIN) with
advice to turn the switch on first — without a sale record on
Equity_Sells there is no memory that the lot was already reduced, and a
re-import would shrink it again. Refusal gates, per ISIN, atomic:
- any line of the stock that couldn't be read reliably — and when the unreadable line's stock IDENTITY is itself unreadable (no ISIN, no recognisable symbol), there is no ISIN group to poison, so the whole FILE's trades are refused (fail safe, loudly);
- an uncovered sell (the file sells more than it buys — partial history). Two escapes before refusal: import the earlier file(s) in the same run, or — interactively — confirm the shares predate 2018-02-01, in which case the missing buy becomes an OPENING lot at 31-01-2018 with a blank cost, so the §6.6 grandfathering value stands in exactly as for a typed old holding (the sale pair carries that date and a blank buy price; a warning names the assumption). Headless runs never assume — they refuse;
- a corporate action whose ex-date falls INSIDE the imported trade window — raw-unit FIFO across a split/bonus boundary would silently corrupt, so v1.7 refuses loudly and suggests entering the current holding by hand (folding chained_adjustment_factor into the netting is a later release). The same logic guards typed lots: replace-mode netting never consumes a sheet lot when any corporate action postdates the oldest typed lot (typed quantities are raw §6.7 units; broker sale quantities are post-action units — they don't compare).
Idempotency is stateless: an ISIN whose computed outcome (surviving lots as a multiset, and the sells when the CG switch is on) is already on the sheet is skipped whole — re-running the same file adds nothing and consumes nothing. Holdings files cross-check the result (broker qty vs sheet qty in today's share terms — each sheet row scales by its chained CA factor from its own anchor first; warning on mismatch — arithmetic, not luck) and provide the no-history fallback: a holding with no trades and no sheet rows lands as one row with the broker's average cost and NO cost date (amber; excluded from XIRR until the user dates it; pre-2018 paper shares instead follow the §6.6 FMV convention — type 31-01-2018). Capacity: over-cap defers the WHOLE equity import with a plain message; the run continues. Cuts to typed rows are only PLANNED during netting and applied after the capacity gate passes, so a deferred import leaves data byte-identical.
The quantity anchor (v1.7.1, Equity col U) — the engine's core
assumption is "Quantity is stated in units of some date"; for typed rows
that date is the Cost date, but a HOLDINGS file states the post-action
count as of the statement day. Every holdings-imported row therefore
carries qty_asof = the import day, and every corporate-action window
that touches quantities starts at the row's ANCHOR (qty_asof, else
Cost date): the Adj/Cost factor stamping (§6.7/§6.15), demerger child
spawning (§6.15 — an event at or before the anchor spawns NO child,
because the broker file already lists the spun-off holding as its own
row), the dividend-quantity estimate (§6.12 — an ex-date BEFORE the
anchor divides the count back through the chained factor), and the
netting comparability guard (an action after the oldest anchor OR the
first trade date blocks netting against sheet lots). The §6.6 FMV fill
for an anchored 31-01-2018 row divides the official value by the chained
factor from 31-01-2018 to the anchor, so Invested = today's-count ×
per-today's-share value — the same money either way, never ×factor.
Actions with ex-dates AFTER the anchor apply exactly as for typed rows.
One entry point (Update Portfolio), replacing the legacy three scripts:
1. locate workbook (same folder as the executable; default filename, else
the single *.xlsx present; else prompt)
1b. PRIVACY front door (v1.5, §3.19): an encrypted workbook prompts for
the password before anything else (3 tries, each a trial decryption);
headless+locked exits politely, file untouched. The interactive prompts
afterwards read the decrypted in-memory copy. Then, per state: first
enable → set-password flow (twice, ≥4 chars; loud no-recovery warning
when the Lock is being enabled); Lock being turned on with an existing
password → confirm it before anything is encrypted; masked file →
one plain question ("see them this time?", Enter keeps the mask) and,
only on yes, the password — verified on the spot against the stored
fingerprint (3 tries, wrong is never silent; RESET typed here leads to
the separate mask-off confirmation); locked+masked → the same
see-them-this-time question (the password already unlocked the file).
A --lock run skips fetching entirely and just re-masks/re-encrypts
(offline). "No computing" means reproducing the LAST update's view: the
relock build takes its `today` from the newest History snapshot's date
(fresh file with no history → wall clock), so date-derived content
(Capital-Gains terms/FY, the FY labels) cannot shift under a relock that
promised to change nothing. run() and relock() share one
regenerate-atomically implementation so the two paths can never drift.
2. INTERACTIVE (console runs only, i.e. stdin.isatty(); skipped when headless
or --no-prompt): show the current people and offer to add new person
sheet(s). Names entered here (or via repeatable --add-person NAME) are
appended to the person list — regeneration then creates each new person's
sheet, Dashboard row and By-Scrip column automatically (§2). Deduped
case-insensitively; capped at the Dashboard's 10 people. v1.4: then offer
to SHOW/HIDE asset classes — a numbered list of the registry classes with
their current state; chosen numbers flip the Settings Yes/No (§3.14), the
easy alternative to editing the sheet. v1.4.3: the listed state IS the
Settings choice — "shown", "hidden", or "hidden — holds rows (not
counted)" — and the per-toggle confirmation spells out what will happen
(off with rows: "will be hidden — its rows are saved but won't be
counted until you show it again"). Toggling OFF a class that holds rows
also warns in the summary; every run additionally carries the single
hidden-money awareness line (§2.1). Prompting must never hang or break a
run — any error is swallowed.
3. refuse politely if the file is locked/open (detect via exclusive-open
probe); if the file is opened DURING the run, the final atomic replace
fails in plain words too ("close it and run again") and the temp file
is removed (v1.6.2 — a leftover *.new.xlsx must never confuse the
no-argument workbook auto-detection)
4. backup: backups/<name>.backup-YYYYMMDD-HHMMSS.xlsx (keep newest 10);
`--lock` backs up too (§3.19 names the readable-copy purge policy)
5. READ — all input columns of all data sheets (hidden ones too) + persons
+ Settings (§3.14; missing sheet ⇒ defaults) + Dashboard cells
(openpyxl read-only; header row located by matching known header
names within rows 1–5, and dynamic Dashboard/History columns by
header label, so user row edits/sorts never break it).
v1.6.2 — the reader carries a WARNINGS list on the data object,
surfaced with the updater's other warnings (relock() returns
them too): text OR a typed formula in a make-or-break number
cell (the holding is skipped, never guessed); a date before
1980 (serial-0 typos compound balances into nonsense); a
Settings switch that isn't a recognised Yes/No token
(yes/y/true/on/1 · no/n/false/off/0, case-insensitive — ONE
parser, model.parse_yes_no, shared with the interactive peeks
so a prompt can never disagree with the build; garbage falls
back to the row's default with a warning); a person whose name
can't be an Excel tab as typed (the tab is adjusted — ≤31
chars, no []:*?/\\, no leading/trailing apostrophe, no clash
with fixed sheets or other persons — via ONE person_tab_map
the generator, reader and add-person prompt all share; cells
keep the full name and chart refs escape any apostrophe).
MORE ROWS THAN A SHEET'S BUDGET (§3.6) makes the update REFUSE
to run — like the open-file check — so nothing is ever
truncated; the message names each sheet, its count and its cap.
Tax_Rules is measured as it will be WRITTEN (bundled defaults
upserted with the user's rows plus invalid rows, §3.22), not as
read — an app upgrade shipping new default rows must not push
the sheet silently past its cap.
Owner and Gold_Silver Type values are canonicalised
case-insensitively onto the person list / {Gold, Silver, SGB}
so Python joins agree with Excel's case-insensitive SUMIFS.
Future-dated MF_SIP and PPF_Ledger rows are excluded from
flows, FIFO, snapshot and projections until their date arrives
(same semantics as every other class; an all-future PPF ledger
reads as empty and the account falls back to its typed-balance
path). An MF_SIP row whose units can't be known (no NAV and no
units, or a typed 0; a zero/negative NAV counts as missing) is
left out of the return figure entirely
and warned about — by the capital-gains engine when it runs,
by run() itself otherwise. flat_accrual's year-fraction clamps
at 0 (a reversed date pair must never DISCOUNT a balance).
§6.1's XIRR never raises even on absurd spans (year-9999,
serial-0): overflow/underflow degrade to a blank cell.
6. FETCH — AMFI, bhavcopy (BSE+NSE same-day union merge, §5.2), corporate
actions + dividends (NSE+BSE, §5.4; merged holdings' successor
symbols included); per-source failure ⇒ keep previous values,
note in summary. v1.6.2 — DISTRUST EMPTY SUCCESS: an AMFI fetch
carrying fewer than 100 schemes (a 200-OK maintenance page) is
treated as a failure (never wholesale-replace MF_Master from
it); a corporate-actions/dividends response whose JSON lacks
the expected structure counts as a per-security failure (its
kept Auto rows are preserved), and a valid-but-empty list still
counts as checked (genuinely no actions)
7. COMPUTE — restructure routing before pricing and demerger children after
the CA refresh (§6.15), masters merge (§6.4), prices/NAVs by
ISIN, status flags (§6.5), FMV fallbacks (§6.6), corp-action
factors (§6.7), dividend rows (§6.12: rebuild current-FY Auto,
freeze the rest, keep unverified), PPF ledger accrual (§6.10),
XIRR (§6.1–6.3; equity flows include dividends + Equity_Sells
round trips since v1.6), FY-end estimates (§6.8), net-worth
snapshot (§6.11). v1.6: when sells exist or the CG switch is on,
run §6.16 ONCE and pass the result into the regenerate step
(`capgains=`): the console 🧾 line (current-FY STCG/LTCG,
intraday, ST-loss set-off, headroom) and the sheet read the SAME
computation — never compute it twice (§3.21)
8. REGENERATE — build the complete workbook (xlsxwriter): structure from this
spec + user inputs + computed/updater values (`today` passed in
so the §6.16/§3.21 build is deterministic); sheets of
switched-off classes hidden, reference sheets per the
Reference-lists switch, the Equity_Sells + Capital Gains + Tax_Rules trio
per the CG switch (§3.14 row 17), tab colours applied
(§2.1/§3.2);
masked and/or encrypted per the privacy state (§3.19 — the
Lock path builds in memory and writes only self-verified
ciphertext); atomic replace (write temp file, then swap)
9. REPORT — console summary (rows matched/unmatched per sheet, sources used,
XIRR figures, PPF/history/added-people, backup path). v1.4: the
console is a product surface — banner with version, live
"Fetching …" lines during network stages, per-line icons, ANSI
colour when the terminal supports it (NO_COLOR respected; plain
text when redirected; emoji stripped when the console encoding
can't carry them), a highlighted net-worth footer, and an
always-printed version line ("update available" / "on the latest
release" / "couldn't check"). Pause before closing when launched
by double-click — but a pause with no stdin (scheduled/headless
run of the packaged entry, which always passes --pause) proceeds
silently instead of crashing; exit code 0/1
Round-trip invariant (the regression backbone): generate → read → regenerate
with no new data must be semantically identical (same cells, formulas,
validations, charts, formats).
- Reference implementation: Python ≥ 3.10; deps
xlsxwriter,openpyxl,requests(+pytestdev). openpyxl is read-only in this codebase. - End-user artifacts per OS, built with PyInstaller (console app):
Windows
Update Portfolio.exe; macOSnetworth-updaterbinary + aUpdate Portfolio.commandwrapper (cd "$(dirname "$0")" && ./networth-updater; read -p "Done."). First-run notes: Windows SmartScreen “Run anyway”; macOS right-click→Open. The PyInstaller spec MUST bundle everydata/*.csvthe updater reads at runtime (§5.5 — rates, FMV, bullion proxies, curated restructures): the loaders degrade silently on a missing file, so an unbundled CSV disables its feature only in the frozen build, invisible to dev-environment tests (a test asserts the spec's datas list for exactly this reason). - Release zip layout: see RELEASES.md.
- The workbook itself must open correctly in desktop Excel (Windows & Mac) and LibreOffice ≥ 7.x (charts, dropdowns, conditional formats).
An alternative implementation conforms if it:
- produces a workbook matching §3 (sheets, columns, formulas, defined names, validations, charts, visual language);
- implements the §5 data contracts with the same tolerant parsing;
- reproduces §6 algorithms bit-for-bit on the shared test vectors
(
tests/golden files: XIRR values, corp-action scenarios, FMV cases); - honours the §7 updater invariants (backup-first, read-only inputs, atomic replace, round-trip identity, graceful source failure);
- never transmits user data anywhere.