Copy of T-Bill Ladder Builder 2.1 (Public)

{
  "author_attribution": "SuperSpy, T-Bill Ladder Builder v2.1, last update 28 Dec 2023 per \u00271. Learn\u0027!B1",
  "cross_sheet_dependencies": {
    "importrange_edges": [],
    "internal": [
      {
        "from": "TBillRateImport!A4:M",
        "occurrences": 36,
        "to": "TreasuryData (VLOOKUP latest, 30/60/120/180/360d ARRAY_CONSTRAIN AVERAGE)",
        "via": "VLOOKUP and ARRAY_CONSTRAIN"
      },
      {
        "from": "DailyTreasuryRates!A4:N",
        "occurrences": 9,
        "to": "TreasuryData (current vs historic curve panel cols I-AY)",
        "via": "SORT/SORTN/VLOOKUP-by-date-offset"
      },
      {
        "from": "TreasuryData!curr column",
        "to": "Admin!C2:C7 (30d avg APY by tenor)",
        "via": "direct ref"
      },
      {
        "from": "Admin!B2:C10",
        "to": "T-Bill Ladder!J3:J33 (hypothetical row rate fallback)",
        "via": "VLOOKUP on term string"
      },
      {
        "from": "T-Bill Ladder!B,F,G,H,I",
        "occurrences": 22,
        "to": "Dashboard rollups",
        "via": "SUMIF/SUMIFS/COUNTIFS/INDEX-MATCH"
      },
      {
        "from": "2. Setup!D1, D5",
        "to": "Dashboard!C16:C20 (tax computations)",
        "via": "direct ref"
      },
      {
        "from": "Admin!combinedIssueDates and per-tenor named ranges",
        "to": "T-Bill Ladder!K3:K33 (issue-date validation)",
        "via": "COUNTIF + INDEX/MATCH"
      },
      {
        "from": "T-Bill Ladder!E1 (firstIssue) and I1 (displayWeek)",
        "to": "T-Bill Ladder!L1:HK1 weekly header sequence",
        "via": "SEQUENCE + WEEKDAY arithmetic"
      }
    ]
  },
  "effort_rating": {
    "blockers": [],
    "estimated_writer_days": "0.5",
    "level": "low",
    "rationale": "Two endpoints, two tabs, two timestamp cells, zero formula rewrites. Workbook is well-structured with no IMPORTRANGE entanglements, no external IMPORT* formulas, and the integration surface is exactly two ranges. Hardest part is verifying coupon-equivalent reconstruction matches Treasury published values to 2dp.",
    "risks": [
      "fiscaldata bill-rate granularity may be monthly-aggregated rather than daily - may need a different endpoint (daily_treasury_bill_rates) or accept monthly resolution",
      "Coupon-equivalent day-count convention could drift across auction calendars (28/56/91 vs actual)"
    ]
  },
  "error_count": 27,
  "external_dependencies": [
    {
      "endpoint": "treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield",
      "notes": "Probe in _url_probe.json. SPA destination breaks IMPORTHTML / regex scrapers. Likely original target of TreasuryRates.gs.",
      "probe_status": "200_alive_but_redirects",
      "redirect_to": "home.treasury.gov/policy-issues/financing-the-government/interest-rate-statistics?data=yield",
      "tab": "DailyTreasuryRates_legacy"
    },
    {
      "endpoint": "treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=realyield",
      "notes": "Probe in _url_probe.json. Same SPA fragility.",
      "probe_status": "200_alive_but_redirects",
      "redirect_to": "home.treasury.gov/...?data=realyield",
      "tab": "n/a"
    },
    {
      "endpoint": "home.treasury.gov/sites/default/files/interest-rates/yield.xml",
      "notes": "Probe in _url_probe.json. XML feed still publishing - most likely current source the v2.1-updated TreasuryRates.gs is hitting (explains why DailyTreasuryRates is fresh through 5/6/2026).",
      "probe_status": "200_alive",
      "tab": "DailyTreasuryRates"
    },
    {
      "endpoint": "treasury.gov billrates textview (inferred)",
      "notes": "TBillRates.gs source. Stalled at 8/8/2025. Treasury retired or moved the per-tenor bill bank-discount/coupon-equivalent table that fed this tab.",
      "probe_status": "not_in_probe_likely_dead",
      "tab": "TBillRateImport"
    },
    {
      "endpoint": "api.fiscaldata.treasury.gov/services/api/fiscal_service/v2/accounting/od/avg_interest_rates",
      "notes": "JSON, no auth, stable. avg_interest_rate_amt by security_desc (Bills 4/8/13/17/26/52-week) replaces TBillRates.gs scrape. Coupon-equivalent computable client-side as CE = (365 * BD) / (360 - BD * days).",
      "probe_status": "confirmed_working_per_brief",
      "tab": "TBillRateImport_replacement"
    },
    {
      "endpoint": "api.fiscaldata.treasury.gov/.../daily_treasury_yield_curve_rates",
      "notes": "Direct replacement for TreasuryRates.gs. Column mapping is 1:1 with existing headers.",
      "probe_status": "confirmed_working_per_brief",
      "tab": "DailyTreasuryRates_replacement"
    }
  ],
  "external_url_unique": 0,
  "fiscaldata_mapping": {
    "DailyTreasuryRates": {
      "column_map": {
        "bc_10year": "L (10 Yr)",
        "bc_1month": "B (1 Mo)",
        "bc_1year": "G (1 Yr)",
        "bc_20year": "M (20 Yr)",
        "bc_2month": "C (2 Mo)",
        "bc_2year": "H (2 Yr)",
        "bc_30year": "N (30 Yr)",
        "bc_3month": "D (3 Mo)",
        "bc_3year": "I (3 Yr)",
        "bc_4month": "E (4 Mo)",
        "bc_5year": "J (5 Yr)",
        "bc_6month": "F (6 Mo)",
        "bc_7year": "K (7 Yr)",
        "record_date": "A (Date)"
      },
      "endpoint": "v2/accounting/od/daily_treasury_yield_curve_rates",
      "target_range": "DailyTreasuryRates!A4:N",
      "timestamp_cell": "DailyTreasuryRates!B1 (auto-recomputes via =MAX(A4:A))"
    },
    "TBillRateImport": {
      "alternative": "Optionally retire this tab entirely - DailyTreasuryRates 4Mo/2Mo/3Mo/6Mo/1Yr columns cover most tenor needs; only the BD/CE split is unique to TBillRateImport.",
      "column_map": {
        "13wk_bank_discount": "F",
        "13wk_coupon_equivalent": "G (compute CE for 91 days)",
        "17wk_bank_discount": "H",
        "17wk_coupon_equivalent": "I (compute CE for 119 days)",
        "26wk_bank_discount": "J",
        "26wk_coupon_equivalent": "K (compute CE for 182 days)",
        "4wk_bank_discount": "B",
        "4wk_coupon_equivalent": "C (compute CE = (365*BD)/(360 - BD*28))",
        "52wk_bank_discount": "L",
        "52wk_coupon_equivalent": "M (compute CE for 364 days)",
        "8wk_bank_discount": "D",
        "8wk_coupon_equivalent": "E (compute CE for 56 days)",
        "record_date": "A (Date)"
      },
      "endpoint": "v2/accounting/od/avg_interest_rates filtered to security_desc IN (4-Week Bills, 8-Week Bills, 13-Week Bills, 17-Week Bills, 26-Week Bills, 52-Week Bills)",
      "target_range": "TBillRateImport!A4:M",
      "timestamp_cell": "TBillRateImport!B1 (auto-recomputes via =MAX(A4:A))"
    }
  },
  "formula_count": 4174,
  "importrange_edges": 0,
  "name": "Copy of T-Bill Ladder Builder 2.1 (Public)",
  "named_ranges": 13,
  "purpose": "Personal Treasury-bill bond-ladder construction and tracking workbook for retail TreasuryDirect / brokerage users. Records purchases (CUSIP, par, price, term, issue/maturity), computes per-holding realised yield, projects maturity cash flow on a weekly grid out to 2034, and rolls up portfolio value, weighted realised return, monthly/quarterly/annual interest, and next-maturing tranche on a Dashboard tab.",
  "rehab_strategy": {
    "approach": "Replace the two bound Apps Scripts (TBillRates.gs, TreasuryRates.gs) with either VPS Python writers hitting api.fiscaldata.treasury.gov via Sheets API, or a single Apps Script time-trigger calling UrlFetchApp on the same fiscaldata endpoints. No formula changes required - existing workbook keys off column layout in the two import tabs which is preserved.",
    "apps_script_alternative": {
      "pattern": "Single dailyUpdate() function calling UrlFetchApp.fetch(fiscaldataUrl), JSON.parse, getRange().setValues() into both import tabs. Bind ScriptApp.newTrigger(\u0027dailyUpdate\u0027).timeBased().everyDays(1).atHour(7).create().",
      "rationale": "Preserves the original \u0027auto-runs daily after first manual trigger\u0027 UX described on 2. Setup!B9. Avoids VPS dependency entirely if user wants the workbook to be self-sustaining. Removes textview.aspx scrape brittleness."
    },
    "edge_cases": [
      "Coupon-equivalent reconstruction must match Treasury published values to 2dp - verify the exact day-count convention (28/56/91/119/182/364 vs actual auction tenor) against a reference date before going live",
      "fiscaldata avg_interest_rates is monthly-aggregated; for daily bill rates may need v2/accounting/od/daily_treasury_bill_rates (verify endpoint name) to match the daily granularity TBillRateImport had",
      "Update v2.1 changelog or add v2.2 entry on Update Log noting the fiscaldata cutover",
      "Public template error cells (27) will self-resolve once a real user enters live holdings - do not patch the formulas"
    ],
    "tabs_to_keep_as_is": [
      "1. Learn",
      "2. Setup",
      "T-Bill Ladder",
      "Dashboard",
      "Other Tools",
      "Admin",
      "TreasuryData",
      "Update Log"
    ],
    "tabs_to_rewrite_writer_side": [
      "TBillRateImport",
      "DailyTreasuryRates"
    ],
    "writer_steps": [
      "Hit fiscaldata daily_treasury_yield_curve_rates endpoint, sort_by=record_date desc, page through ~1300 rows of history",
      "Reshape JSON to 14-column wide layout matching DailyTreasuryRates header schema",
      "Hit fiscaldata avg_interest_rates filtered to Bills tenors, paginate full history",
      "Compute coupon-equivalent per row from bank-discount: CE = (365 * BD/100) / (360 - (BD/100) * days_to_maturity), interleave into 13-column wide layout",
      "Push to DailyTreasuryRates!A4 and TBillRateImport!A4 via Sheets API spreadsheets.values.update",
      "Existing =MAX(A4:A) timestamps in B1 of each tab refresh automatically",
      "Optionally clear the bound Apps Scripts to prevent double-writes"
    ]
  },
  "sheet_id": "1aPL6Wm3DKdSZ5tjEk9vLaqhfGUpEYkCJ4nRGk8_fBmc",
  "slug": "tbill_ladder_v21",
  "tab_count": 10,
  "tabs": {
    "1. Learn": {
      "errors": 0,
      "formulas": 0,
      "notes": "Static T-bill primer. Mentions TBillRates.gs and TreasuryRates.gs by name as the data writers.",
      "role": "documentation",
      "rows": 41
    },
    "2. Setup": {
      "errors": 0,
      "formulas": 0,
      "notes": "D1 = max marginal income tax rate (24% in copy); D5 = estimated taxes Yes/No. Both feed Dashboard tax-projection rows. B11 explicitly names the two .gs scripts that populate the import tabs.",
      "role": "config",
      "rows": 12
    },
    "Admin": {
      "charts": 4,
      "cols": 44,
      "errors": 11,
      "formulas": 3238,
      "notes": "78% of all formulas. E:J = canonical Treasury auction issue dates by tenor (4/8/13/17/26/52-week) through 2034. K = combined sorted issue dates (combinedIssueDates named range, 1151 rows from 11/3/2022 to 6/13/2034). C2:C7 = 30-day-avg APY by tenor consumed by ladder hypothetical-row VLOOKUP. Errors N6:W6 are rolling-average ratios with empty numerators.",
      "role": "compute_spine",
      "rows": 1152
    },
    "DailyTreasuryRates": {
      "cols": 14,
      "errors": 0,
      "formulas": 1,
      "notes": "Headers row 3: Date, 1Mo, 2Mo, 3Mo, 4Mo, 6Mo, 1Yr, 2Yr, 3Yr, 5Yr, 7Yr, 10Yr, 20Yr, 30Yr. B1 = Last Import Date = 5/6/2026 (CURRENT). Populated by TreasuryRates.gs. Only formula is B1=MAX(A4:A).",
      "role": "data_feed_yield_curve",
      "rows": 1337
    },
    "Dashboard": {
      "charts": 4,
      "errors": 12,
      "formulas": 26,
      "notes": "All errors are #N/A from INDEX/MATCH on next-maturing tranche (C24:C28, C30:C34) and #DIV/0! on rate-of-return (C8, C13). Data-state, not logic.",
      "role": "summary_dashboard",
      "rows": 35
    },
    "Other Tools": {
      "errors": 0,
      "formulas": 0,
      "notes": "Static link panel.",
      "role": "links",
      "rows": 6
    },
    "T-Bill Ladder": {
      "cols": 219,
      "errors": 3,
      "formulas": 862,
      "frozen_cols": 10,
      "frozen_rows": 2,
      "notes": "Holdings blotter rows 3-33 (TBillLadderRows=31). Wide weekly grid L:HK across 208 weeks (TBillLadderCols=208). E1=firstIssue (named), I1=displayWeek (named). Errors D37/D39/J39 are #DIV/0! from trailing-365d SUMIF denominators.",
      "role": "primary_input_and_visualiser",
      "rows": 39
    },
    "TBillRateImport": {
      "cols": 13,
      "errors": 0,
      "formulas": 1,
      "notes": "Headers row 3: Date, 4/8/13/17/26/52 WEEKS BANK DISCOUNT + COUPON EQUIVALENT pairs. B1 = Last Import Date = 8/8/2025 (STALE). Populated by TBillRates.gs (not in export). Only formula is B1=MAX(A4:A).",
      "role": "data_feed_bill_rates",
      "rows": 894
    },
    "TreasuryData": {
      "charts": 2,
      "cols": 51,
      "errors": 0,
      "formulas": 46,
      "notes": "VLOOKUPs latest TBillRateImport row, computes 30/60/120/180/360-day rolling averages via ARRAY_CONSTRAIN(AVERAGE,...). Right-hand panel (col I onward) presents current vs N-days-ago Treasury yield curve sourced from DailyTreasuryRates.",
      "role": "yield_aggregation",
      "rows": 1336
    },
    "Update Log": {
      "errors": 0,
      "formulas": 0,
      "notes": "v2.1 entry: \u0027Updated TreasuryRates script so it no longer times out and updates about 97% faster\u0027 confirms script-driven import.",
      "role": "version_history",
      "rows": 11
    }
  },
  "what_broken": [
    "TBillRateImport last updated 8/8/2025 - 9 months stale. TBillRates.gs likely scraping treasury.gov textview.aspx?data=billrates which redirected to home.treasury.gov SPA in early 2024 and broke naive scrapers",
    "27 error cells, all data-state artefacts of the empty public template (no holdings older than 365 days from TODAY=2026-05-08): T-Bill Ladder D37/D39/J39 #DIV/0!, Dashboard C8/C13 #DIV/0!, Dashboard C24:C28/C30:C34 #N/A, Admin N6:W6 #DIV/0!",
    "Apps Script source files (TBillRates.gs, TreasuryRates.gs) not present in scripts/ export - bound script content not surfaced by Drive getContent without OAuth scope"
  ],
  "what_works": [
    "DailyTreasuryRates is fresh through 5/6/2026 - TreasuryRates.gs is still pulling Treasury yield curve successfully",
    "Mathematical core sound: 1477 SUMIF + 101 SUMIFS drive maturity-bucket cash-flow rollups; ARRAYFORMULA + REGEXREPLACE parse term strings cleanly",
    "Admin!E:J hard-coded Treasury auction issue-date schedule runs through 6/13/2034 - reference data, doesn\u0027t rot",
    "13 named ranges intact (firstIssue, displayWeek, combinedIssueDates, FourWeek, EightWeek, ThirteenWeek, SeventeenWeek, TwentySixWeek, FiftyTwoWeek, TBillTerms, TBillLadderRows, TBillLadderCols, yesNo)",
    "Dynamic week-scrolling header on T-Bill Ladder!L1:HK1 driven by displayWeek named range - 208 weeks of forward visualisation",
    "Zero IMPORTRANGE entanglement, zero external IMPORT* formulas - clean self-contained workbook"
  ]
}