Social Stock Tracker v1.0

{
  "broken_cells_summary": {
    "by_tab": {
      "100K buys": 1,
      "4chan": 1,
      "B\u0026U Volume": 1,
      "ClusterBuys": 1,
      "Fideltiy Stocks": 1,
      "Master": 5198,
      "Sheet19": 949,
      "Twitch": 1,
      "Twitter": 1,
      "UOA": 1,
      "Unusual Volume": 1,
      "WSB Daily": 50,
      "WSB stocks": 1
    },
    "root_causes": [
      {
        "cause": "googlefinance_mic_suffix",
        "count": 5115,
        "example_addrs": [
          "Master!P2:Y513",
          "Sheet19!B2:B919",
          "WSB Daily!E2:E51"
        ]
      },
      {
        "cause": "ref_ghost_comparator",
        "count": 152,
        "example_addrs": [
          "Master!S",
          "Master!T",
          "Master!V"
        ]
      },
      {
        "cause": "importrange_unauthorized",
        "count": 11,
        "example_addrs": [
          "Finviz data!A1",
          "WSB stocks!A1",
          "Twitter!A1"
        ]
      },
      {
        "cause": "importjson_dead_apps_script",
        "count": 949,
        "example_addrs": [
          "Sheet19!N3",
          "Sheet19 J/K cols"
        ]
      },
      {
        "cause": "ifna_or_ifs_propagated_na",
        "count": 434,
        "example_addrs": [
          "Master!S col IFS"
        ]
      }
    ],
    "total": 6207
  },
  "effort_rating": "high",
  "external_dependencies": [
    {
      "alive": false,
      "purpose": "TDA fundamentals (Sheet19, 56 cells). LEAKS API KEY.",
      "replacement": "SEC EDGAR companyfacts via VPS /feeds/edgar_fundamentals.csv",
      "url": "https://api.tdameritrade.com/v1/instruments?apikey=L1TT29WF3BHSBMS3DOHASQ14ZCYD1HLQ\u0026symbol="
    },
    {
      "alive": false,
      "purpose": "StockTwits trending (Stocktwits!A2). 403 from datacenters.",
      "replacement": "VPS proxy with residential User-Agent, or scrape stocktwits.com web page",
      "url": "https://api.stocktwits.com/api/2/trending/symbols/equities.json"
    },
    {
      "alive": false,
      "purpose": "WSB ticker counts API (WSB Daily!A1). Redirects to tradestie.com.",
      "replacement": "VPS reads tradestie.com/api/v1/apps/reddit directly",
      "url": "https://dashboard.nbshare.io/api/v1/apps/reddit"
    },
    {
      "alive": true,
      "purpose": "WSB hot posts (Hot Posts!A3)",
      "replacement": "Keep; VPS forwards (Sheets cannot consume JSON natively)",
      "url": "https://www.reddit.com/r/wallstreetbets/hot/.json?count=25"
    },
    {
      "alive": true,
      "purpose": "thetagang hot posts (Hot Posts!B3)",
      "replacement": "Keep; VPS forwards",
      "url": "https://www.reddit.com/r/thetagang/hot/.json?count=25"
    },
    {
      "alive": false,
      "purpose": "High short interest list (HighShort!A2). 404.",
      "replacement": "FINRA reg-SHO daily CSV via VPS",
      "url": "https://www.highshortinterest.com/"
    },
    {
      "alive": true,
      "purpose": "Per-ticker hyperlink display (Master!AA2:AA513, 512 cells)",
      "replacement": "Keep as-is",
      "url": "https://finviz.com/quote.ashx?t="
    }
  ],
  "importrange_edges_out": [
    {
      "from": "Finviz data!A1",
      "range": "Master!A:BP",
      "status": "REF",
      "target_sheet": "1l6gJjrkYg-3A33xUgcJJXLPAH1v4ceZF1MEvGqOYZgY"
    },
    {
      "from": "WSB stocks!A1",
      "range": "WSB stocks!A:Q",
      "status": "REF",
      "target_sheet": "1cSMtqkX9M8JgFc5EHHd8vOeiAAfPZhs_G33yAwnWT4k"
    },
    {
      "from": "Fideltiy Stocks!A1",
      "range": "Fideltiy Stocks!A:E",
      "status": "REF",
      "target_sheet": "1t-heT4GnTzcDRY0nLy0lEYdKOzXP7D_F_-6T9TazY1M"
    },
    {
      "from": "Twitch!A1",
      "range": "Twitch!A:D",
      "status": "REF",
      "target_sheet": "1iiPjZ7gRyxxMuF9oDBAtBm1lW6rfqt547Vz3spEhn3s"
    },
    {
      "from": "4chan!A1",
      "range": "4chan!A:D",
      "status": "REF",
      "target_sheet": "1xz7P7e_wVM8lpluNC163mZub7RGGBRMZi0-PXi3psCw"
    },
    {
      "from": "Twitter!A1",
      "range": "Twitter!A:G",
      "status": "REF",
      "target_sheet": "1mYa3nB-t1Xn8obtb6LgurHPBrygB1K00zknnz_STbcA"
    },
    {
      "from": "ClusterBuys!A1",
      "range": "ClusterBuys!A:S",
      "status": "REF",
      "target_sheet": "1NjA0leWr-yBbu4quqpuuCtss_4lUDH9zf0Q86vgIMqI"
    },
    {
      "from": "100K buys!A1",
      "range": "100kBuys!A:S",
      "status": "REF",
      "target_sheet": "1NjA0leWr-yBbu4quqpuuCtss_4lUDH9zf0Q86vgIMqI"
    },
    {
      "from": "B\u0026U Volume!A1",
      "range": "B\u0026U Volume!A:B",
      "status": "REF",
      "target_sheet": "1_QeIpr0iK8AChyS7edcmegFfOZL5dntqPuFDTIuXueM"
    },
    {
      "from": "Unusual Volume!A1",
      "range": "Unusual Volume!A:B",
      "status": "REF",
      "target_sheet": "1_QeIpr0iK8AChyS7edcmegFfOZL5dntqPuFDTIuXueM"
    },
    {
      "from": "UOA!A1",
      "range": "UOAMaster!A:H",
      "status": "REF",
      "target_sheet": "1LL2ncLpn_3scMXw3W0-0-3wiap7bAbfNTunfszoXkyA"
    }
  ],
  "infrastructure_shape": "3-layer: (1) 9 IMPORTRANGE platform-tab proxies pulling from upstream community sheets, (2) Master aggregator (~512 rows x 25 cols) with COUNTIF mention counters and GOOGLEFINANCE price/volume cols, (3) Dashboard SORT/SEQUENCE projection of Master gated by named ranges. Side workspace Sheet19 wraps a TDA-Ameritrade fundamentals pipeline via custom ImportJSON Apps Script.",
  "name": "Social Stock Tracker v1.0",
  "named_ranges_assessment": "2 named ranges, both healthy. sortColumn1 -\u003e Admin!C3:C26 (24 column labels driving Dashboard\u0027s column-picker). sortOrder -\u003e Admin!E3:E4 (Asc/Desc). Both power the single SORT() in Dashboard. Keep as-is.",
  "purpose": "Cross-platform stock-mention dashboard. Aggregates ticker mentions from 9 social/insider sources (WSB, Fidelity, Twitch, 4chan, StockTwits, Twitter, ClusterBuys, 100K buys, HighShort, B\u0026U Volume, Unusual Volume, UOA), sums them in Master, and surfaces a sortable Dashboard ranked by composite mention score with GOOGLEFINANCE price context.",
  "questions_for_brad": [
    "Twitter feed \u2014 pay for X-API/Nitter or drop the \ud83d\udc24 column from Master/Dashboard entirely? Post-paywall this is the most expensive source.",
    "4chan /biz/ ticker scraper \u2014 worth keeping? Noisy signal, ongoing maintenance liability.",
    "RK Pulse hidden tab has 54 hardcoded tickers (AMZN, AAPL, FB, ...) \u2014 SuperSpy artifact to delete, or Brad-curated watchlist worth surfacing in v2?",
    "Fundamentals replacement \u2014 bring SEC-EDGAR fundamentals into this sheet (replacing Sheet19), or punt entirely to the marcus_fundamentals sister sheet and delete Sheet19?",
    "Authorize publishing the rehabbed sheet in read-only public mode, or per-user copy? Affects whether IMPORTDATA endpoints need auth tokens."
  ],
  "rehab_strategy": "Phase 1 (1 day, kills ~5200 errors): wrap ticker refs in Master!A and Sheet19!A,L with LEFT(A,FIND(\u0027.\u0027,A)-1) to strip MIC suffix; patch 152 #REF!\u003eP_ ghosts in Master S/T/V. Phase 2 (2-3 days): VPS publishes /feeds/*.csv for wsb_mentions, wsb_daily, cluster_buys, 100k_buys, high_short, uoa, hot_posts. Reddit + OpenInsider feeds are already alive per url_probe. Replace each platform-tab A1 IMPORTRANGE with =IMPORTDATA(\u003cfeed\u003e). Phase 3 (1-2 days): StockTwits residential-UA proxy; Twitter alternative (Nitter or drop column \ud83d\udc24); Twitch and 4chan /biz/ ticker scrapers. Phase 4 (1 day): replace entire Sheet19 with single SEC-EDGAR-backed CSV; delete leaked TDA key. Preserve FALSE kill-switch toggles in Hot Posts!A1, Stocktwits!C1, HighShort!A1, WSB Daily!F1 as UX pattern.",
  "slug": "social_tracker",
  "tabs": [
    {
      "name": "Dashboard",
      "notes": "2 formulas, 0 errors. SORT(Master!A2:Y) gated by sortColumn1/sortOrder named ranges.",
      "purpose": "Sortable ranked view of Master",
      "status": "working"
    },
    {
      "name": "To Do",
      "notes": "Empty",
      "purpose": "Notes",
      "status": "working"
    },
    {
      "name": "Admin",
      "notes": "24 sort-column labels in C3:C26, Asc/Desc in E3:E4",
      "purpose": "Hidden config; hosts sortColumn1 and sortOrder named ranges",
      "status": "working"
    },
    {
      "name": "Master",
      "notes": "5198 errors. ~5060 GOOGLEFINANCE #N/A in P:Y from MIC-suffixed tickers (.XNAS); ~152 #REF!\u003eP_ ghosts in S/T/V from a deleted column. COUNTIF cols C-N would work the moment platform tabs have data.",
      "purpose": "Core aggregation: ~512 tickers x 25 cols",
      "status": "broken"
    },
    {
      "name": "Sheet19",
      "notes": "949 errors. ImportJSON Apps Script missing in this copy + TDA endpoint DNS-dead post-Schwab. Leaks API key in N3+55 cells.",
      "purpose": "TDA-Ameritrade fundamentals workspace",
      "status": "broken"
    },
    {
      "name": "Finviz data",
      "notes": "A1 references parent sheet 1l6gJjrkYg-3A33xUgcJJXLPAH1v4ceZF1MEvGqOYZgY. #REF! authorization.",
      "purpose": "IMPORTRANGE shim to a parent sheet\u0027s Master!A:BP",
      "status": "broken"
    },
    {
      "name": "Hot Posts",
      "notes": "A3/B3 hold reddit JSON URLs (both alive in url_probe). FALSE toggle in A1. Consumer likely an Apps Script that\u0027s gone.",
      "purpose": "Drives Reddit JSON fetch for r/wallstreetbets and r/thetagang",
      "status": "partial"
    },
    {
      "name": "WSB stocks",
      "notes": "A1 IMPORTRANGE to 1cSMtqkX...WSB stocks!A:Q -\u003e #REF!",
      "purpose": "WSB ticker mention list",
      "status": "broken"
    },
    {
      "name": "WSB Daily",
      "notes": "50 GOOGLEFINANCE #N/A on E2:E51 (MIC suffix). A1 references dashboard.nbshare.io API (now 403/redirect to tradestie).",
      "purpose": "Daily WSB ticker rollup with prices",
      "status": "broken"
    },
    {
      "name": "Fideltiy Stocks",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "Fidelity most-traded",
      "status": "broken"
    },
    {
      "name": "Twitch",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "Twitch chat ticker mentions",
      "status": "broken"
    },
    {
      "name": "4chan",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "4chan /biz/ ticker mentions",
      "status": "broken"
    },
    {
      "name": "Stocktwits",
      "notes": "Has cached data (32 rows incl IREN). C1=TRUE toggle. A2 calls api.stocktwits.com (403 from datacenters).",
      "purpose": "StockTwits trending equities",
      "status": "partial"
    },
    {
      "name": "Twitter",
      "notes": "A1 IMPORTRANGE -\u003e #REF!. Hardest source to revive post X-API paywall.",
      "purpose": "Twitter ticker mentions",
      "status": "broken"
    },
    {
      "name": "ClusterBuys",
      "notes": "A1 IMPORTRANGE -\u003e #REF!. OpenInsider feed alive in url_probe.",
      "purpose": "Insider cluster-buy alerts",
      "status": "broken"
    },
    {
      "name": "100K buys",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "$100k+ insider buys",
      "status": "broken"
    },
    {
      "name": "HighShort",
      "notes": "A2 IMPORTHTML highshortinterest.com (404). 7 empty-value formulas.",
      "purpose": "High short-interest tickers",
      "status": "broken"
    },
    {
      "name": "B\u0026U Volume",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "Bullish/Unusual volume",
      "status": "broken"
    },
    {
      "name": "Unusual Volume",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "Unusual volume scanner",
      "status": "broken"
    },
    {
      "name": "UOA",
      "notes": "A1 IMPORTRANGE -\u003e #REF!",
      "purpose": "Unusual options activity",
      "status": "broken"
    },
    {
      "name": "RK Pulse",
      "notes": "54 hardcoded tickers (AMZN, AAPL, FB...). No formulas. Likely v2 feature stub.",
      "purpose": "Hidden Roaring Kitty watchlist",
      "status": "working"
    }
  ]
}