Flowery's Industry Screener

{
  "broken_cells_summary": {
    "by_tab": {
      "Morningstar": 1,
      "SIC": 3
    },
    "root_causes": [
      {
        "cause": "All 4 errors collapse to one upstream cause: universe IMPORTRANGE not authorised in this snapshot",
        "count": 4,
        "example_addrs": [
          "Morningstar!O2",
          "SIC!H1",
          "SIC!J1",
          "SIC!K1"
        ]
      }
    ],
    "total": 4
  },
  "effort_rating": "low",
  "external_dependencies": [
    {
      "alive": false,
      "purpose": "Universe!A3:A tickers, F3:I sector/group/ind/sub, W3:W SIC, Y3:Z Morningstar",
      "replacement": "Repoint at new DFV-Terminal master universe tab",
      "url": "IMPORTRANGE -\u003e universe sheet (admin!I3)"
    },
    {
      "alive": false,
      "purpose": "Tracker setup!DG4:DQ industry-page metrics + B4:DJ4 header row",
      "replacement": "Repoint at new DFV-Terminal master tracker tab",
      "url": "IMPORTRANGE -\u003e tracker sheet (admin!I2)"
    },
    {
      "alive": true,
      "purpose": "11 sector landing page HYPERLINKs in GICS!DR2:DR12",
      "replacement": "Keep",
      "url": "https://digital.fidelity.com/prgw/digital/research/sector/detail/{sector}"
    }
  ],
  "importrange_edges_out": [
    {
      "from": "setup!A2",
      "purpose": "tickers",
      "target_range": "universe!A3:A",
      "target_sheet_id": "11cEF1jzciWFMvB5dIlHyifiBttPQqkSEzzyjE8fev3Q"
    },
    {
      "from": "setup!B2",
      "purpose": "GICS sector/group/industry/sub",
      "target_range": "universe!F3:I",
      "target_sheet_id": "11cEF1jzciWFMvB5dIlHyifiBttPQqkSEzzyjE8fev3Q"
    },
    {
      "from": "setup!H2",
      "purpose": "SIC value",
      "target_range": "universe!W3:W",
      "target_sheet_id": "11cEF1jzciWFMvB5dIlHyifiBttPQqkSEzzyjE8fev3Q"
    },
    {
      "from": "setup!I2",
      "purpose": "Morningstar sector/industry",
      "target_range": "universe!Y3:Z",
      "target_sheet_id": "11cEF1jzciWFMvB5dIlHyifiBttPQqkSEzzyjE8fev3Q"
    },
    {
      "from": "setup!L2",
      "purpose": "industry-page metrics block 1",
      "target_range": "setup!DG4:DL",
      "target_sheet_id": "1umHQqjQ8BWJ8RdqF6TROXRIof1o7Yx5uqzch5Xlo4_c"
    },
    {
      "from": "setup!R2",
      "purpose": "industry-page metrics block 2",
      "target_range": "setup!DM4:DO",
      "target_sheet_id": "1umHQqjQ8BWJ8RdqF6TROXRIof1o7Yx5uqzch5Xlo4_c"
    },
    {
      "from": "setup!U2",
      "purpose": "industry-page metrics block 3",
      "target_range": "setup!DP4:DQ",
      "target_sheet_id": "1umHQqjQ8BWJ8RdqF6TROXRIof1o7Yx5uqzch5Xlo4_c"
    },
    {
      "from": "admin!E2",
      "purpose": "ticker header row transposed",
      "target_range": "setup!B4:DJ4",
      "target_sheet_id": "1umHQqjQ8BWJ8RdqF6TROXRIof1o7Yx5uqzch5Xlo4_c"
    }
  ],
  "infrastructure_shape": "Three layers. (1) Ingest: 8 IMPORTRANGEs in setup tab pull from user\u0027s primary tracker + universe sheets \u2014 URLs in admin!I2 (tracker) and admin!I3 (universe). setup!A1/L1 = TRUE/FALSE master switches. (2) Taxonomy: GICS (1001 rows), Morningstar, SIC, wikiscraper (Wikipedia GICS table), SICscraper (SEC SIC list). (3) Output: industries tab \u2014 E1=Class picker, M3=level picker, with cascade of IFERROR(IF(...)) selectors and 75-way unrolled FLATTEN+QUERY joins.",
  "name": "Flowery\u0027s Industry Screener",
  "named_ranges_assessment": "11 named ranges all intact: gicssector/gicsindustrygroup/gicsindustry/gicssubindustry (GICS taxonomy), gics/morningstar/sic (industries Class options), chosen (industries checkbox column), Classes (admin!C3:C6), sortColumn1 (admin!E2:E1000), sortColumn2 (admin!A2:A11). All bind cleanly, no #REF! drift.",
  "purpose": "Secondary GICS/Morningstar/SIC industry slicer that consumes a separate tracker+universe sheet via IMPORTRANGE and pivots the ticker universe into a sector/industry-group/industry/sub-industry drill-down with per-slice averages and a chosen-slice ranked sub-screen. Pure downstream consumer.",
  "questions_for_brad": [
    "Drop Morningstar Class option (no clean free substitute) or backfill from FMP/Finnhub?",
    "Refresh wikiscraper for March 2023 GICS sub-industry revisions?"
  ],
  "rehab_strategy": "Pure repoint job. Sheet is architecturally healthy. Update admin!I2 (tracker URL) and admin!I3 (universe URL) to new DFV-Terminal master sheet IDs. If new master layout differs, edit setup!A2/B2/H2/I2 (universe) and setup!L2/R2/U2 (tracker) IMPORTRANGE strings. Click \u0027Allow access\u0027 on each IMPORTRANGE prompt. Confirm setup!A1=TRUE and setup!L1=TRUE. No formula rewrites required. Optional: refresh wikiscraper for March 2023 GICS sub-industry revisions.",
  "slug": "flowery_industry_screener",
  "tabs": [
    {
      "name": "link your sheets here",
      "notes": "Static instructions",
      "purpose": "Onboarding instructions documenting IMPORTRANGE contract",
      "status": "working"
    },
    {
      "name": "industries",
      "notes": "47 formulas, 0 errors. 75-way unrolled FLATTEN/QUERY joins. AVERAGE rollups in C2:K2. col A checkbox = chosen named range.",
      "purpose": "User-facing screen with sector/industry slicing + ranked sub-screen",
      "status": "working"
    },
    {
      "name": "GICS",
      "notes": "1001 rows \u00d7 127 cols. All Fidelity URLs probe alive 200.",
      "purpose": "Static GICS taxonomy matrix + 11 Fidelity sector landing-page hyperlinks",
      "status": "working"
    },
    {
      "name": "wikiscraper",
      "notes": "166 rows static reference",
      "purpose": "Wikipedia GICS table snapshot",
      "status": "working"
    },
    {
      "name": "Morningstar",
      "notes": "O2 #N/A from empty setup!I:J upstream (universe IMPORTRANGE not authorised)",
      "purpose": "Morningstar taxonomy mirror",
      "status": "partial"
    },
    {
      "name": "SIC",
      "notes": "1023 formulas. H1/J1/K1 #N/A from empty industries!AZ/BA upstream",
      "purpose": "SIC taxonomy matrix",
      "status": "partial"
    },
    {
      "name": "SICscraper",
      "notes": "446 rows static reference",
      "purpose": "SEC SIC code list snapshot",
      "status": "working"
    },
    {
      "name": "setup",
      "notes": "8 IMPORTRANGE cells (A2/B2/H2/I2 universe; L2/R2/U2 tracker industry-page metrics). Status depends on whether user authorised.",
      "purpose": "IMPORTRANGE ingest hub",
      "status": "partial"
    },
    {
      "name": "admin",
      "notes": "E2 = TRANSPOSE(IMPORTRANGE(I2, setup!B4:DJ4))",
      "purpose": "Config: tracker URL (I2), universe URL (I3), Classes/sortColumn1/sortColumn2 named ranges",
      "status": "working"
    }
  ]
}