"""Why adding up every debt tag on a balance sheet overstates net debt. This is the evidence behind /guides/net-debt-overlapping-debt-tags/. It is kept in the repo and published so the claims on that page can be checked rather than taken on trust. Re-run it and the JSON regenerates; if a number on the page and a number in the JSON disagree, the JSON is right and the page is out of date. US-GAAP has several debt tags, and they nest. LongTermDebt is the whole long-term debt including the part due within a year; LongTermDebtNoncurrent and LongTermDebtCurrent are its two halves. DebtCurrent is everything due within a year, which already contains LongTermDebtCurrent, ShortTermBorrowings and CommercialPaper. A filer can tag the total and the parts in the same balance sheet, and most large ones do. Sum every tag that answers and the same dollar is counted two or three times. For the same 20 large filers as the earlier guides, this script reads each debt tag at the latest annual balance-sheet date only (a tag whose newest fact is from another date is listed as stale and left out), then compares: naiveSum every debt tag present at that date, added together ruleDebt one non-overlapping path through the hierarchy (see rule_debt) siteDebt what this site computed until 3 November 2026 (one long-term tag + one current tag). Kept as the record of the error; the stock pages and the calculator now use rule_debt. Leases are reported but kept out of all three. Whether operating leases are debt is a modelling choice, not a tagging error. Usage: python research/net-debt-survey.py Writes: research/net-debt-survey.json """ import io import json import os import time import urllib.error import urllib.request from datetime import datetime UA = "Fairvalue Radar (privacy@fairvalueradar.com)" HERE = os.path.dirname(os.path.abspath(__file__)) ANNUAL = ("10-K", "20-F", "40-F") COMPANIES = [ ("AAPL", 320193), ("MSFT", 789019), ("NVDA", 1045810), ("AMZN", 1018724), ("GOOGL", 1652044), ("META", 1326801), ("TSLA", 1318605), ("JPM", 19617), ("XOM", 34088), ("JNJ", 200406), ("WMT", 104169), ("PG", 80424), ("KO", 21344), ("PFE", 78003), ("INTC", 50863), ("CVX", 93410), ("T", 732717), ("BA", 12927), ("GE", 40545), ("MRK", 310158), ] DEBT_TAGS = [ "DebtLongtermAndShorttermCombinedAmount", "LongTermDebt", "LongTermDebtNoncurrent", "LongTermDebtCurrent", "DebtCurrent", "ShortTermBorrowings", "CommercialPaper", "LongTermDebtAndCapitalLeaseObligations", "LongTermDebtAndCapitalLeaseObligationsCurrent", ] LEASE_TAGS = ["OperatingLeaseLiability", "FinanceLeaseLiability"] CASH_TAG = "CashAndCashEquivalentsAtCarryingValue" ANCHOR_TAG = "Assets" # Read and reported, but left out of the counts on the guide page. NOT_COMPARABLE = { "JPM": "bank: at the balance-sheet date it tags only ShortTermBorrowings among these tags; " "its long-term funding is not reported under LongTermDebt, so no total can be built here", "TSLA": "tags only LongTermDebt and DebtCurrent; whether LongTermDebt already includes the " "current portion cannot be settled from the tags alone, so neither total is asserted", } # what build-stock-pages.py used until 3 November 2026, in its order SITE_LT = ["LongTermDebtNoncurrent", "LongTermDebt", "LongTermDebtAndCapitalLeaseObligations"] SITE_CUR = ["DebtCurrent", "LongTermDebtCurrent", "ShortTermBorrowings"] def fetch(url, tries=4): req = urllib.request.Request(url, headers={"User-Agent": UA, "Accept": "application/json"}) for attempt in range(tries): try: with urllib.request.urlopen(req, timeout=30) as r: return json.loads(r.read().decode("utf-8")) except urllib.error.HTTPError as e: if e.code == 404: return None time.sleep(1.5 * (attempt + 1)) except Exception: time.sleep(1.5 * (attempt + 1)) return None def annual_instants(cik, tag): """end date -> fact, annual forms only, latest accession wins.""" time.sleep(0.15) d = fetch("https://data.sec.gov/api/xbrl/companyconcept/CIK%010d/us-gaap/%s.json" % (cik, tag)) if not d: return {} units = d.get("units", {}) rows = units.get("USD") or next(iter(units.values()), []) by = {} for f in rows: if f.get("form") not in ANNUAL or not f.get("end") or f.get("start"): continue prev = by.get(f["end"]) if not prev or f.get("accn", "") > prev.get("accn", ""): by[f["end"]] = f return by def rule_debt(v): """One path through the hierarchy, never a total plus its own parts.""" short = v.get("ShortTermBorrowings") if short is None: short = v.get("CommercialPaper") if v.get("DebtLongtermAndShorttermCombinedAmount") is not None: return v["DebtLongtermAndShorttermCombinedAmount"], "DebtLongtermAndShorttermCombinedAmount" if v.get("LongTermDebtNoncurrent") is not None and v.get("DebtCurrent") is not None: return v["LongTermDebtNoncurrent"] + v["DebtCurrent"], "LongTermDebtNoncurrent + DebtCurrent" if v.get("LongTermDebt") is not None: return v["LongTermDebt"] + (short or 0), "LongTermDebt + short-term borrowings" if v.get("LongTermDebtAndCapitalLeaseObligations") is not None and v.get("DebtCurrent") is not None: return (v["LongTermDebtAndCapitalLeaseObligations"] + v["DebtCurrent"], "LongTermDebtAndCapitalLeaseObligations + DebtCurrent") if v.get("LongTermDebtNoncurrent") is not None: return (v["LongTermDebtNoncurrent"] + (v.get("LongTermDebtCurrent") or 0) + (short or 0), "LongTermDebtNoncurrent + LongTermDebtCurrent + short-term borrowings") if v.get("LongTermDebtAndCapitalLeaseObligations") is not None: return (v["LongTermDebtAndCapitalLeaseObligations"] + (v.get("LongTermDebtAndCapitalLeaseObligationsCurrent") or 0) + (short or 0), "LongTermDebtAndCapitalLeaseObligations + current + short-term borrowings") if short is not None or v.get("DebtCurrent") is not None: return (v.get("DebtCurrent") if v.get("DebtCurrent") is not None else short), "current debt only" return None, None def site_debt(newest_by_tag): """The old build-stock-pages.py logic: per field, the tag whose newest fact is newest.""" def pick(tags): best = None for t in tags: f = newest_by_tag.get(t) if f and (best is None or f["end"] > best[1]["end"]): best = (t, f) return best lt, cur = pick(SITE_LT), pick(SITE_CUR) return lt, cur def main(): out = {"readOn": time.strftime("%Y-%m-%d"), "source": "SEC EDGAR XBRL companyconcept, annual forms, instant facts", "note": "All figures in USD at the latest annual balance-sheet date (the newest annual " "Assets fact). Leases are reported but excluded from every debt total.", "companies": []} for ticker, cik in COMPANIES: rec = {"ticker": ticker, "cik": cik} if ticker in NOT_COMPARABLE: rec["comparable"] = False rec["why"] = NOT_COMPARABLE[ticker] assets = annual_instants(cik, ANCHOR_TAG) if not assets: rec["state"] = "no annual balance sheet" out["companies"].append(rec) continue anchor = max(assets) rec["balanceSheetDate"] = anchor rec["form"] = assets[anchor].get("form") at_date, stale, newest = {}, {}, {} for tag in DEBT_TAGS + LEASE_TAGS + [CASH_TAG]: by = annual_instants(cik, tag) if not by: continue newest[tag] = by[max(by)] if anchor in by: at_date[tag] = by[anchor]["val"] else: stale[tag] = max(by) debt_vals = {t: at_date[t] for t in DEBT_TAGS if t in at_date} rec["debtTagsAtDate"] = debt_vals rec["staleDebtTags"] = {t: stale[t] for t in DEBT_TAGS if t in stale} rec["leases"] = {t: at_date[t] for t in LEASE_TAGS if t in at_date} cash = at_date.get(CASH_TAG) rec["cash"] = cash naive = sum(debt_vals.values()) if debt_vals else None rule, path = rule_debt(debt_vals) rec["naiveSum"] = naive rec["ruleDebt"] = rule rec["rulePath"] = path rec["naiveOverRule"] = round(naive / rule, 2) if naive and rule else None rec["overstatement"] = (naive - rule) if naive is not None and rule is not None else None # internal check: does the total equal its halves? if all(t in debt_vals for t in ("LongTermDebt", "LongTermDebtNoncurrent", "LongTermDebtCurrent")): rec["ltdEqualsHalves"] = abs(debt_vals["LongTermDebt"] - debt_vals["LongTermDebtNoncurrent"] - debt_vals["LongTermDebtCurrent"]) <= 0.01 * debt_vals["LongTermDebt"] lt, cur = site_debt(newest) s = {"ltTag": lt[0] if lt else None, "ltEnd": lt[1]["end"] if lt else None, "curTag": cur[0] if cur else None, "curEnd": cur[1]["end"] if cur else None} site_val = 0 for t, f in ((lt or (None, None)), (cur or (None, None))): if t and f and abs((datetime.fromisoformat(f["end"]) - datetime.fromisoformat(anchor)).days) <= 45: site_val += f["val"] s["siteDebt"] = site_val or None s["doubleCounts"] = bool(lt and cur and lt[0] == "LongTermDebt" and cur[0] in ("LongTermDebtCurrent", "DebtCurrent")) rec["site"] = s rec["netDebtNaive"] = (naive - cash) if naive is not None and cash is not None else None rec["netDebtRule"] = (rule - cash) if rule is not None and cash is not None else None out["companies"].append(rec) print("%-6s %s tags=%d stale=%d naive=%s rule=%s x=%s site=%s dbl=%s path=%s" % ( ticker, anchor, len(debt_vals), len(rec["staleDebtTags"]), naive, rule, rec["naiveOverRule"], s["siteDebt"], s["doubleCounts"], path)) io.open(os.path.join(HERE, "net-debt-survey.json"), "w", encoding="utf-8").write( json.dumps(out, indent=1)) print("\nwritten: research/net-debt-survey.json") if __name__ == "__main__": main()