#!/usr/bin/env python3 """Quarterly gross-margin panel for the BVP Nasdaq Emerging Cloud Index, from audited SEC filings. Definitive build. Decisions, all of them consequential and all recorded: 1. Q4 is never filed as a standalone duration (three 10-Qs + one 10-K). It is derived as FY minus the three quarters nested inside it. 2. Off-calendar fiscal quarters are assigned to the calendar quarter containing the period MIDPOINT, not the period end. Salesforce's Nov-Jan quarter is centred in December and belongs to Q4, not to the following Q1. 3. Cost of revenue that is filed only as components is summed. 4. Every exclusion is named with its reason. Nothing is silently dropped. """ import json, os, datetime as dt OUT = os.path.dirname(os.path.abspath(__file__)) raw = json.load(open(os.path.join(OUT, "edgar_raw_full2.json"))) D = dt.date.fromisoformat REV = ["RevenueFromContractWithCustomerExcludingAssessedTax", "RevenueFromContractWithCustomerIncludingAssessedTax", "Revenues", "SalesRevenueNet"] COST = ["CostOfRevenue", "CostOfGoodsAndServicesSold", "CostOfServices", "CostOfGoodsSold"] # cost filed only as components (AppFolio): summed COST_PARTS = ["CostOfGoodsAndServiceExcludingDepreciationDepletionAndAmortization", "CostOfGoodsAndServicesSoldDepreciationAndAmortization"] GP = ["GrossProfit"] def series(facts, tags, lo, hi, add=False): """{(start,end): val}. Latest filing wins (restatements). Earlier tag in the preference list wins, unless add=True, in which case components are summed.""" best = {} for tag in tags: node = facts.get("us-gaap", {}).get(tag) if not node: continue for unit, rows in node.get("units", {}).items(): if unit != "USD": continue for r in rows: s, e = r.get("start"), r.get("end") if not s or not e or not (lo <= (D(e) - D(s)).days <= hi): continue k = (s, e) cur = best.setdefault(k, {}) if tag not in cur or r.get("filed", "") > cur[tag][1]: cur[tag] = (r["val"], r.get("filed", "")) out = {} for k, tagvals in best.items(): if add: out[k] = sum(v[0] for v in tagvals.values()) else: for tag in tags: if tag in tagvals: out[k] = tagvals[tag][0] break return out def with_q4(q, a): """Derive the unfiled fourth quarter: FY minus the three nested quarters.""" out = dict(q) for (as_, ae), aval in a.items(): inner = sorted([(s, e) for (s, e) in q if as_ <= s and e <= ae]) if len(inner) != 3: continue ends, starts = [e for _, e in inner], [s for s, _ in inner] if ae not in ends: gap = (max(ends), ae) elif as_ not in starts: gap = (as_, min(starts)) else: continue if 80 <= (D(gap[1]) - D(gap[0])).days <= 100 and gap not in out: out[gap] = aval - sum(q[k] for k in inner) return out def qlabel(start, end): """Calendar quarter containing the period midpoint.""" mid = D(start) + (D(end) - D(start)) / 2 return f"{mid.year}Q{(mid.month - 1) // 3 + 1}" panel, excluded = {}, [] for t, c in raw["companies"].items(): f = c["facts"] rev = with_q4(series(f, REV, 80, 100), series(f, REV, 350, 380)) cost = with_q4(series(f, COST, 80, 100), series(f, COST, 350, 380)) # fill periods where cost is filed only as components, without overwriting the # standard tag where a company reports both parts = with_q4(series(f, COST_PARTS, 80, 100, add=True), series(f, COST_PARTS, 350, 380, add=True)) for k, v in parts.items(): cost.setdefault(k, v) gp = with_q4(series(f, GP, 80, 100), series(f, GP, 350, 380)) if not rev and not gp: excluded.append([t, c["name"], "no quarterly us-gaap revenue facts — foreign " "private issuer filing 20-F; gross profit reported annually only"]) continue rows = {} for k in sorted(set(rev) | set(gp), key=lambda x: x[1]): r, cc, g = rev.get(k), cost.get(k), gp.get(k) if g is None and r is not None and cc is not None: g = r - cc if cc is None and r is not None and g is not None: cc = r - g if r and g is not None and r > 0 and 0 < g / r < 1: rows[qlabel(*k)] = {"start": k[0], "end": k[1], "revenue": r, "cost_of_revenue": cc, "gross_profit": g, "gross_margin": round(g / r, 6)} if not rows: excluded.append([t, c["name"], "revenue filed but cost of revenue not tagged " "under any us-gaap element in the window (company-extension " "tags only) — gross profit not derivable"]) continue panel[t] = {"cik": c["cik"], "name": c["name"], "quarters": rows} WIN = [f"{y}Q{q}" for y in range(2022, 2027) for q in range(1, 5)] WIN = [w for w in WIN if "2022Q1" <= w <= "2026Q1"] # a company with revenue but no gross profit anywhere in the window is an exclusion, # not a short history — say which it is for t in sorted(panel): if not any(k in panel[t]["quarters"] for k in WIN): excluded.append([t, panel[t]["name"], "cost of revenue not tagged under any " "us-gaap element in the window (company-extension tags only) " "— gross profit not derivable"]) del panel[t] bal = sorted(t for t in panel if all(k in panel[t]["quarters"] for k in WIN)) partial = sorted(set(panel) - set(bal)) print(f"Window {WIN[0]}..{WIN[-1]} = {len(WIN)} quarters") print(f"Balanced: {len(bal)} Partial: {len(partial)} Excluded: {len(excluded)}" f" Total {len(bal)+len(partial)+len(excluded)}") print("\nPartial (short history — later listing/first US filing):") for t in partial: n = len([k for k in panel[t]["quarters"] if k in WIN]) print(f" {t:6}{n:3}/{len(WIN)} {panel[t]['name'][:40]}") print("\nExcluded:") for t, n, why in excluded: print(f" {t:6} {n[:26]:28} {why[:74]}") json.dump({"built": "2026-08-10", "source": "SEC EDGAR XBRL companyfacts", "window": WIN, "balanced": bal, "partial": partial, "excluded": excluded, "panel": panel}, open(os.path.join(OUT, "panel_final.json"), "w"), indent=1) # flat CSV for publication import csv with open(os.path.join(OUT, "gross-margin-panel.csv"), "w", newline="") as fh: w = csv.writer(fh) w.writerow(["symbol", "name", "cik", "quarter", "period_start", "period_end", "revenue_usd", "cost_of_revenue_usd", "gross_profit_usd", "gross_margin", "in_balanced_panel"]) for t in sorted(panel): for q in sorted(panel[t]["quarters"]): r = panel[t]["quarters"][q] w.writerow([t, panel[t]["name"], panel[t]["cik"], q, r["start"], r["end"], r["revenue"], r["cost_of_revenue"], r["gross_profit"], f"{r['gross_margin']:.6f}", t in bal]) print("\nwrote panel_final.json + gross-margin-panel.csv")