{
  "broken_cells_summary": {
    "by_tab": {
      "$+": 1,
      "admin": 2,
      "industry": 5
    },
    "root_causes": [
      {
        "cause": "Yahoo IMPORTJSON returns ERROR (query1 quoteSummary requires crumb+cookie auth since mid-2023)",
        "count": 1,
        "example_addrs": [
          "$+!CH4"
        ]
      },
      {
        "cause": "MATCH against industry!A8:A which is empty until Universe IMPORTRANGE authorized",
        "count": 4,
        "example_addrs": [
          "industry!AE2",
          "industry!M3",
          "industry!B7",
          "industry!C363"
        ]
      },
      {
        "cause": "TRANSPOSE/SPLIT against industry!B2:AL6 empty (cascade from same root)",
        "count": 2,
        "example_addrs": [
          "admin!BE3",
          "admin!BF3"
        ]
      },
      {
        "cause": "SORT/FILTER on setup!$DG$5:$DQ empty (cascade)",
        "count": 1,
        "example_addrs": [
          "industry!C369"
        ]
      },
      {
        "cause": "_silent_iceberg: ~62k formula-bearing-but-empty IMPORTJSON cells in $+ and custom that will fail the moment tickers populate",
        "count": 62195,
        "example_addrs": [
          "$+!CH4",
          "custom!*"
        ]
      }
    ],
    "total": 8
  },
  "effort_rating": "medium",
  "external_dependencies": [
    {
      "alive": false,
      "purpose": "All ~62k fundamentals lookups (EBITDA, EPS, sharesOutstanding, EV, dividendYield, totalCash/Debt, 52w, bookValue, etc.) via IMPORTJSON",
      "replacement": "VPS endpoint backed by secfsdstools flattening SEC EDGAR XBRL into a master!fundamentals tab; rewrite IMPORTJSON cells to VLOOKUP",
      "url": "https://query1.finance.yahoo.com/v10/finance/quoteSummary/"
    },
    {
      "alive": true,
      "purpose": "Per-ticker chart hyperlinks (16,102 occurrences)",
      "replacement": "keep as-is",
      "url": "https://stockcharts.com/h-sc/ui?s="
    },
    {
      "alive": true,
      "purpose": "Per-ticker insider transaction page hyperlinks (7,264)",
      "replacement": "keep hyperlinks; structured data via VPS/OpenInsider scraper feeding master!insider",
      "url": "http://openinsider.com/search?q="
    },
    {
      "alive": true,
      "purpose": "News/profile hyperlinks (621)",
      "replacement": "keep as-is",
      "url": "https://seekingalpha.com/symbol/"
    },
    {
      "alive": true,
      "purpose": "Institutional holder hyperlinks (620)",
      "replacement": "keep as-is",
      "url": "https://whalewisdom.com/stock/"
    },
    {
      "alive": true,
      "purpose": "EDGAR filing search hyperlinks (610)",
      "replacement": "keep as-is",
      "url": "https://www.sec.gov/edgar/search/?r=el#/entityName="
    },
    {
      "alive": true,
      "purpose": "Google search hyperlinks (610)",
      "replacement": "keep as-is",
      "url": "https://www.google.com/search?q="
    },
    {
      "alive": false,
      "purpose": "Yahoo profile page hyperlink (610) - HTTP 500 on landing",
      "replacement": "swap to https://stockanalysis.com/stocks/ or https://finviz.com/quote.ashx?t=",
      "url": "https://finance.yahoo.com/quote/"
    }
  ],
  "importrange_edges_out": [
    {
      "purpose": "Authorize Universe IMPORTRANGE",
      "source_cell": "start!D6",
      "target": "Universe sheet (user-pasted)"
    },
    {
      "purpose": "Authorize Insider Buying IMPORTRANGE",
      "source_cell": "start!D7",
      "target": "Insider Buying sheet (auto-resolved)"
    },
    {
      "purpose": "Authorize Metrics IMPORTRANGE",
      "source_cell": "start!D8",
      "target": "Metrics sheet (auto-resolved)"
    },
    {
      "purpose": "Macro dashboard data",
      "source_cell": "DASH!B1",
      "target": "Metrics!DASH!B1:AB"
    },
    {
      "purpose": "Master ticker metadata pull",
      "source_cell": "setup!B3",
      "target": "Universe!universe!A4:AE"
    },
    {
      "purpose": "Secondary ticker metadata",
      "source_cell": "setup!B5",
      "target": "Universe!universe!A4:AE"
    },
    {
      "purpose": "Tag/sector data",
      "source_cell": "setup!R5",
      "target": "Universe!universe!AF4:AT"
    },
    {
      "purpose": "Insider transactions",
      "source_cell": "setup!AG5",
      "target": "Insider Buying!universeBuys!C4:M"
    },
    {
      "purpose": "Bulk fundamentals mirror",
      "source_cell": "setup!AR5",
      "target": "Metrics!yahoo!B2:AD5050"
    },
    {
      "purpose": "Setup-link autopopulation",
      "source_cell": "admin!E4",
      "target": "Universe!start!B7:F"
    },
    {
      "purpose": "Tags list for filters",
      "source_cell": "admin!W28",
      "target": "Universe!tagsList"
    },
    {
      "purpose": "Inventory probe reports 0 IMPORTRANGE edges because all targets are named ranges (universe/insiderBuying/metricsSheet) not literal sheet IDs",
      "source_cell": "_inventory_note",
      "target": "_inventory_note"
    }
  ],
  "infrastructure_shape": "Three-tier: (1) start tab is the user setup wizard accepting Universe URL; named ranges universe/insiderBuying/metricsSheet auto-resolve. (2) setup tab (5711x124) is the staging chokepoint - 5 IMPORTRANGE formulas pull all upstream data. (3) Display tabs (d/w/$/$+/custom/DASH/industry) read setup via VLOOKUP/QUERY and decorate with ~26k HYPERLINK builders to external sites. Fundamentals layer is 62,195 IMPORTJSON calls hitting Yahoo query1 quoteSummary endpoint via admin!AQ3 base URL. 29 named ranges, 0 charts, 0 filter views (1 in setup), 8 hard error cells, 87,916 ISBLANK-gated empty formulas. No Apps Script (the IMPORTJSON function implies a custom Apps Script that the inventory didn\u0027t capture; 89k formulas without scripts beyond it).",
  "name": "Public Tracker v0.7d",
  "named_ranges_assessment": "29 named ranges, all structurally intact. Critical ones: universe/insiderBuying/metricsSheet/pricesLink (4 IMPORTRANGE entry points - rehab repoints these to a single master sheet); SubmitButton/masterFilter/masterNoError/queryFilters1/queryFilters2/sortColumn1/sortColumn2/ascendingDescending/YearsToAnalyze (UI controls on admin tab); tagsList/setupLabels/subIndustry/portLabels (label/lookup data); customSheetName/customFilter/customNoError plus tab-prefixed siblings (\u0027d\u0027!customFilter, \u0027w\u0027!customFilter, \u0027$\u0027!customFilter) for the per-tab custom view system. No broken refs. Drop nothing.",
  "purpose": "Per-ticker tracking surface (the most-used screen in the SuperSpy DFV stack). User pastes a Universe sheet URL into start!B6; tracker chains IMPORTRANGE through Universe -\u003e Insider Buying -\u003e Metrics, then layers Yahoo Finance fundamentals via IMPORTJSON, insider activity, and daily/weekly/dollar movers across d/w/$/$+/custom/DASH/industry tabs.",
  "questions_for_brad": [
    "Confirm the source-of-truth Universe sheet ID. The AW5 sample value points to \u00271vsb2UfsqPXSfXzH1j-qnK8sDaW37kdKd097MiC7m1PM\u0027 (which url_probe lists under universe_v07d). Is that the canonical Universe to wire up, or is there a newer one?",
    "For the Yahoo IMPORTJSON replacement - prefer option 1 (mass-rewrite to VLOOKUP against pre-flattened master tab) or option 2 (keep IMPORTJSON shape but write an Apps Script wrapper hitting VPS REST API)? Option 1 is cleaner; option 2 preserves the original 62k formulas verbatim if you want minimal sheet-side diffs.",
    "Drop the (old) tabs entirely, or preserve them for archaeology / historical reference?",
    "The custom Apps Script function IMPORTJSON() isn\u0027t visible in the inventory - was the script payload captured separately, or is that a TODO before rehab?"
  ],
  "rehab_strategy": "Keep Tracker v0.7d as the user-facing surface; replace plumbing with one VPS-backed master sheet. Steps: (1) build dfv-master sheet with tabs prices/fundamentals/insider/dash/universe fed by VPS Python service (secfsdstools for SEC fundamentals matching the 13 Yahoo JSON paths in admin!AR3, OpenInsider scraper for insider, FRED/fiscaldata for macro). (2) Repoint universe/insiderBuying/metricsSheet/pricesLink named ranges to the same master sheet ID - collapses 3 IMPORTRANGE auth grants into 1. (3) Mass find/replace the ~62k IMPORTJSON cells in $+ and custom to VLOOKUP/INDEX-MATCH against master!fundamentals - drops Yahoo dependency entirely. (4) Delete the three (old) hidden tabs. (5) Pre-populate industry!A8:A with canonical sector enum to heal the 5 cascading #N/A errors. (6) Keep all HYPERLINK columns as-is (all destinations alive except finance.yahoo.com - swap to stockanalysis.com). No script rewrites, no chart rebuilds (zero charts), no schema redesign.",
  "slug": "tracker_v07d",
  "tabs": [
    {
      "name": "start",
      "notes": "8 formulas, all alive logic; depends on Universe IMPORTRANGE being authorized",
      "purpose": "Setup wizard - user pastes Universe URL in B6, B7/B8 auto-resolve from named ranges",
      "status": "working"
    },
    {
      "name": "DASH",
      "notes": "26 formulas; pulls B1:AB from Metrics sheet via DASH!B1 IMPORTRANGE; HYPERLINK columns to WhaleWisdom/SeekingAlpha alive; visible #REF! values in B3/C3/D3 confirm Metrics IMPORTRANGE not authorized",
      "purpose": "Macro/markets/indices dashboard with multi-portfolio side-by-side views",
      "status": "partial"
    },
    {
      "name": "d",
      "notes": "4977 formulas; structure sound, all blanks gated on Universe authorization; HYPERLINK decorations alive",
      "purpose": "Daily movers (\u003e=10% in a day) sortable view",
      "status": "partial"
    },
    {
      "name": "w",
      "notes": "Same shape as d; depends on setup data",
      "purpose": "Weekly movers (\u003e=10% in a week)",
      "status": "partial"
    },
    {
      "name": "$",
      "notes": "Same shape as d; depends on Insider Buying sheet IMPORTRANGE",
      "purpose": "Insider buying tracker against Universe",
      "status": "partial"
    },
    {
      "name": "d (old)",
      "notes": "Hidden, 306 formulas - cruft, drop in rehab",
      "purpose": "Legacy daily movers tab",
      "status": "broken"
    },
    {
      "name": "w (old)",
      "notes": "Hidden, 4216 formulas - cruft",
      "purpose": "Legacy weekly movers",
      "status": "broken"
    },
    {
      "name": "$ (old)",
      "notes": "Hidden, 1008 formulas - cruft",
      "purpose": "Legacy insider tab",
      "status": "broken"
    },
    {
      "name": "$+",
      "notes": "20,608 formulas, 19,569 are Yahoo IMPORTJSON. CH4 #ERROR is the canonical break. Entire tab non-functional until Yahoo replacement",
      "purpose": "Enhanced insider buys with per-ticker Yahoo fundamentals overlay",
      "status": "broken"
    },
    {
      "name": "custom",
      "notes": "47,644 formulas (largest tab), 42,614 IMPORTJSON. Same Yahoo dependency",
      "purpose": "User-configurable per-ticker view with full Yahoo fundamentals fan-out",
      "status": "broken"
    },
    {
      "name": "industry",
      "notes": "370 formulas, 5 hard-broken (#N/A on MATCH/SORT/FILTER cascading from missing Universe data); will heal with data",
      "purpose": "Sector/industry/sub-industry navigator",
      "status": "partial"
    },
    {
      "name": "toDo",
      "notes": "0 formulas; informational",
      "purpose": "Author\u0027s planned-improvements list",
      "status": "working"
    },
    {
      "name": "setup",
      "notes": "60 formulas; B3/B5/R5/AG5/AR5 are the master IMPORTRANGE bases. AR5 pulls from Metrics yahoo!B2:AD5050",
      "purpose": "Data staging - 5 IMPORTRANGE chokepoints feed everything else",
      "status": "broken"
    },
    {
      "name": "send",
      "notes": "Only 3 formulas; minimal surface",
      "purpose": "Outbound formatting/staging tab (likely for export)",
      "status": "partial"
    },
    {
      "name": "admin",
      "notes": "11 formulas, 2 hard-broken (BE3/BF3 cascade from industry). AQ3=\u0027https://query1.finance.yahoo.com/v10/finance/quoteSummary/\u0027 is the dead Yahoo base. AR3 holds the modules query string",
      "purpose": "Configuration: data source URLs, menu options, auth state",
      "status": "partial"
    },
    {
      "name": "notes",
      "notes": "0 formulas",
      "purpose": "Free-form user notes",
      "status": "working"
    }
  ]
}