Public Universe v0.7d

{
  "broken_cells_summary": {
    "by_tab": {
      "admin": 6,
      "db": 435
    },
    "root_causes": [
      {
        "cause": "Stale Yahoo IMPORTJSON failures hardened into db rows (gics col)",
        "count": 231,
        "example_addrs": [
          "db!V366",
          "db!V558"
        ]
      },
      {
        "cause": "Stale Yahoo IMPORTJSON failures hardened into db rows (sector col)",
        "count": 113,
        "example_addrs": [
          "db!Y720",
          "db!Y862"
        ]
      },
      {
        "cause": "Stale Yahoo IMPORTJSON failures hardened into db rows (Biz/longBusinessSummary)",
        "count": 78,
        "example_addrs": [
          "db!J720"
        ]
      },
      {
        "cause": "Bad ticker rows where lookup itself failed",
        "count": 13,
        "example_addrs": [
          "db!C640",
          "db!C862"
        ]
      },
      {
        "cause": "References to deleted tab universeFields",
        "count": 2,
        "example_addrs": [
          "admin!AD6",
          "admin!AE6"
        ]
      },
      {
        "cause": "VLOOKUP chain off start!B8 returns #N/A",
        "count": 4,
        "example_addrs": [
          "admin!AB17",
          "admin!AC17",
          "admin!AD17",
          "admin!AE17"
        ]
      }
    ],
    "total": 441
  },
  "effort_rating": "medium",
  "external_dependencies": [
    {
      "alive": false,
      "purpose": "GICS/SIC/NAICS metadata via IMPORTJSON for non-US tickers (22 cells in submit). ConnectTimeout.",
      "replacement": "secfsdstools sub.json (CIK-\u003eSIC) + local GICS tab as SIC-\u003eGICS map; NAICS via Census crosswalk on VPS.",
      "url": "https://api.quickfs.net/stocks/"
    },
    {
      "alive": false,
      "purpose": "Profile/sector/industry/business summary/price/insider ownership. Assembled at runtime from admin!S6+ticker+admin!T6 in 110 IMPORTJSON cells. Probed dead in kleven_metrics analysis (500/404).",
      "replacement": "VPS enrichment endpoint backed by SEC submissions API + secfsdstools.",
      "url": "https://query1.finance.yahoo.com/v10/finance/quoteSummary/{ticker}"
    },
    {
      "alive": true,
      "purpose": "Live price/currency/marketcap snapshot in submit",
      "replacement": "Keep as fallback; primary = VPS daily price feed.",
      "url": "GOOGLEFINANCE"
    },
    {
      "alive": false,
      "purpose": "Live price deltas for universe!AF:AS, db!K4 prices, start!D7-D14 access pings",
      "replacement": "Single shared VPS-fed master Prices sheet.",
      "url": "IMPORTRANGE to user-pasted Prices/Insider Buying/Metrics/Tracker sheets"
    }
  ],
  "importrange_edges_out": [
    {
      "from": "db!K4",
      "purpose": "Live price array",
      "target_range": "dbPrices!D4:F",
      "target_sheet_id": "via start!B7 (Prices)"
    },
    {
      "from": "universe!AF4",
      "purpose": "15 derived metric columns",
      "target_range": "universePrices!G4:U",
      "target_sheet_id": "via start!B7 (Prices)"
    },
    {
      "from": "start!D7",
      "purpose": "Access handshake",
      "target_range": "admin!AA3",
      "target_sheet_id": "via start!B7 (Prices)"
    },
    {
      "from": "start!D8",
      "purpose": "Access handshake",
      "target_range": "admin!AA3",
      "target_sheet_id": "via start!B8 (Insider Buying)"
    },
    {
      "from": "start!D9",
      "purpose": "Access handshake",
      "target_range": "admin!AA3",
      "target_sheet_id": "via start!B9 (Metrics)"
    },
    {
      "from": "start!D10",
      "purpose": "Access handshake",
      "target_range": "admin!AL3",
      "target_sheet_id": "via start!B10 (Tracker)"
    }
  ],
  "infrastructure_shape": "10 tabs. start = setup wizard with IMPORTRANGE handshake to four downstream sheets (Prices, Insider Buying, Metrics, Tracker). submit = 22-slot data entry form, two rows per slot, ~836 formulas. db = 4789-row append log, 31 columns A-AE, mostly pure values + one IMPORTRANGE in K4 for live prices. universe = single ARRAYFORMULA dedupe-and-sort-by-date over db, plus IMPORTRANGE in AF4 pulling 15 derived metric columns. admin = config (API base URLs, dropdown sources, dynamic Yahoo URL pieces in S6/T6). GICS = static 4-level taxonomy (161 rows). exchanges = Yahoo/Google/QuickFS exchange symbol crosswalk (54 rows).",
  "name": "Public Universe v0.7d",
  "named_ranges_assessment": "All 8 named ranges intact: 4 GICS taxonomy ranges (gicsSector/gicsIndustryGroup/gicsIndustry/gicsSubindustry), 4 admin dropdown sources (yesNo/tagsList/submitButton/confidence). Used purely as data-validation source ranges for submit form. No rehab work required.",
  "purpose": "Ticker database for the SuperSpy/RK stack. User submits a ticker via the submit form; sheet enriches with name/sector/industry/GICS/SIC/NAICS/biz description/price via Yahoo Finance + QuickFS IMPORTJSON, appends row to db, and exposes a deduped latest-record view via the universe tab. Tracker / Metrics / Insider Buying sheets read from universe!A4:AE.",
  "questions_for_brad": [
    "Do you want submit (manual ticker entry form) to stay alive, or should the universe be VPS-managed (auto-populated from a curated SEC ticker list)?",
    "Is the per-user copy model staying, or is the goal a single shared community sheet?",
    "Should we preserve QuickFS/Yahoo column semantics exactly so existing downstream consumers keep working, or is a schema change acceptable?",
    "Are the placeholder Analysis1-4 sheets in start!B11-B14 still planned, or should those rows be removed?"
  ],
  "rehab_strategy": "Treat db cols A-Z as canonical schema. Map fields to VPS master: ticker -\u003e SEC cik_tickers; name -\u003e secfsdstools sub.name; sector taxonomy -\u003e SIC-\u003eGICS map (local GICS tab is reusable); Biz -\u003e SEC submissions JSON description; price/cur/mkt cap/exch -\u003e VPS daily snapshot in Prices sheet; insider ownership -\u003e OpenInsider rollups; gics/sic/naics -\u003e SEC sub.json + Census crosswalk. Replace all 110 IMPORTJSON calls \u2014 VPS writes db rows directly via Sheets API, OR point IMPORTJSONs at a VPS enrichment endpoint. One-pass backfill of 422 stale #REF! cells in db!V/Y/J using SEC-derived data. Repoint start!B7 to a single shared VPS-fed Prices sheet. Delete admin!AD6/AE6 (universeFields tab gone). Decide whether to ship vetted IMPORTJSON Apps Script or migrate fully off it.",
  "slug": "universe_v07d",
  "tabs": [
    {
      "name": "start",
      "notes": "Works only if user pastes their own URLs. Templates point at SuperSpy\u0027s published copies.",
      "purpose": "Setup wizard for downstream sheet URLs",
      "status": "partial"
    },
    {
      "name": "submit",
      "notes": "All IMPORTJSON Yahoo + QuickFS calls fail. GOOGLEFINANCE still works for major US tickers.",
      "purpose": "22-slot ticker enrichment form",
      "status": "broken"
    },
    {
      "name": "db",
      "notes": "4789 rows of intact data; 422 stale #REF! cells in cols V/Y/J from historical Yahoo IMPORTJSON failures.",
      "purpose": "Append-only ticker submissions log, 31 columns. Source-of-truth raw data.",
      "status": "partial"
    },
    {
      "name": "universe",
      "notes": "Local ARRAYFORMULA in A4 works. AF4 IMPORTRANGE to Prices sheet fails when start!B7 unset.",
      "purpose": "Canonical deduped view of db, joined with derived price metrics",
      "status": "partial"
    },
    {
      "name": "toDo",
      "notes": "Free-text",
      "purpose": "Author notes",
      "status": "working"
    },
    {
      "name": "notes",
      "notes": "",
      "purpose": "Free-text notes",
      "status": "working"
    },
    {
      "name": "features",
      "notes": "Static lookup table",
      "purpose": "Reference matrix of country/exchange data coverage",
      "status": "working"
    },
    {
      "name": "admin",
      "notes": "AD6/AE6 reference dead universeFields tab (#REF!). API Keys column empty \u2014 no credential leak.",
      "purpose": "Configuration: API key slots, dropdown sources, Yahoo URL pieces",
      "status": "partial"
    },
    {
      "name": "GICS",
      "notes": "161 rows, no formulas",
      "purpose": "Static GICS-2018 taxonomy",
      "status": "working"
    },
    {
      "name": "exchanges",
      "notes": "Static lookup, ~50 global exchanges",
      "purpose": "Yahoo/Google/QuickFS exchange symbol crosswalk",
      "status": "working"
    }
  ]
}