{
  "author": "Kleven",
  "cross_sheet_dependencies": [
    {
      "consumed_at": [
        "DASH!B1 (DASH!B1:AB)",
        "setup!AR5 (yahoo!B2:BP5050)"
      ],
      "direction": "downstream of Metrics \u2014 DASH is gated on Metrics rehab",
      "named_range": "metricsSheet (admin!E5)",
      "target": "kleven_metrics",
      "target_id": "1lzVjg_2Kuhj21_Mmqj2pn-GfUXeBy7ieg-K7-v42dlQ"
    },
    {
      "consumed_at": [
        "setup!B3 (universe!A4:AE select 19 cols)",
        "setup!B5 (universe!A4:AE select 16 cols)",
        "setup!R5 (universe!AF4:AT)",
        "admin!W28 (tagsList)"
      ],
      "named_range": "universe (admin!E3)",
      "target": "universe_sheet",
      "target_id": "1tNrpUxxx12Cxe-NG4W0C14VoJ3ujo5SAHCC7cqYD3K4"
    },
    {
      "consumed_at": [
        "setup!AG5 (universeBuys!C4:M)",
        "$+!BC4 (largeBuys!A4:AE)"
      ],
      "named_range": "insiderBuying (admin!E4)",
      "target": "insider_buying_sheet",
      "target_id": "1PcMpdUwgJOQ7S-hAjthzkalF7YkFLu2bcVy85Cj3VLU"
    }
  ],
  "effort_breakdown": {
    "blocking_dependency": "Yahoo replacement writer must exist before this sheet is usable; without it the sheet stays dead regardless of any in-sheet work.",
    "in-sheet_surgery": "20% (mechanical once writer is producing fund_cache)",
    "python_writer_edgar_work": "80% (shared across all Tracker-family sheets)"
  },
  "effort_rating": "HIGH",
  "external_dependencies": [
    {
      "criticality": "blocking",
      "occurrences_in_formulas": 62195,
      "status": "dead (auth/crawler-locked)",
      "url": "https://query1.finance.yahoo.com/v10/finance/quoteSummary/"
    },
    {
      "criticality": "cosmetic (HYPERLINK display only)",
      "occurrences": 1220,
      "status": "500 per url_probe",
      "url": "https://finance.yahoo.com/quote/"
    },
    {
      "criticality": "cosmetic",
      "occurrences": 32204,
      "status": "alive 200",
      "url": "https://stockcharts.com/h-sc/ui?s="
    },
    {
      "criticality": "cosmetic",
      "occurrences": 14528,
      "status": "alive 200",
      "url": "http://openinsider.com/search?q="
    },
    {
      "criticality": "cosmetic",
      "occurrences": 1242,
      "status": "alive 200",
      "url": "https://seekingalpha.com/symbol/"
    },
    {
      "criticality": "cosmetic",
      "occurrences": 1240,
      "status": "alive 200",
      "url": "https://whalewisdom.com/stock/"
    },
    {
      "criticality": "cosmetic",
      "occurrences": 1220,
      "status": "alive 200",
      "url": "https://www.google.com/search?q="
    },
    {
      "criticality": "cosmetic",
      "occurrences": 1220,
      "status": "alive 200",
      "url": "https://www.sec.gov/edgar/search/?r=el#/entityName="
    }
  ],
  "function_inventory": {
    "ADDRESS": 15505,
    "ARRAYFORMULA": 162,
    "FILTER": 321,
    "HYPERLINK": 26491,
    "IFERROR": 63,
    "IMPORTJSON": 62195,
    "IMPORTRANGE": 12,
    "INDEX": 13088,
    "INDIRECT": 15496,
    "ISBLANK": 88869,
    "MATCH": 15571,
    "QUERY": 17,
    "REGEXREPLACE": 15,
    "ROW": 15500,
    "SORT": 171,
    "SUM": 43453
  },
  "infrastructure_notes": [
    "IMPORTJSON is a custom Apps Script (bound script project, not in inventory) \u2014 standard Tracker v0.7 pattern.",
    "Defensive wrapping is essentially absent: 63 IFERROR vs 62,195 IMPORTJSON. When Yahoo dies, cells go red.",
    "Yahoo URL is built from admin!AQ3 (base) + ticker + admin!AR3 (modules query string). Secondary template at admin!AF6/AG6.",
    "Cross-sheet handshake at start!B6/B7/B8 with token-echo confirmation via admin!M3/AA3/AL3."
  ],
  "lineage": "Power-user fork of Tracker v0.7d (same 16-tab skeleton, 29 named ranges, identical static link layer)",
  "name": "Copy of Market Tracker",
  "purpose": "Multi-timeframe market tracker (d/w/$) plus heavyweight $+ screener and configurable custom column screener; consumes a Universe sheet, an Insider Buying sheet, and Kleven\u0027s Metrics sheet via IMPORTRANGE; pulls fundamentals per ticker via IMPORTJSON against Yahoo Finance quoteSummary endpoint.",
  "rehab_strategy": {
    "approach": "Replace IMPORTJSON layer with VPS Python writer feeding hidden fund_cache tab via Sheets API; VLOOKUP from existing formulas.",
    "data_source_mapping": {
      "assetProfile": "static metadata from EDGAR submissions API",
      "balanceSheetHistoryQuarterly": "secfsdstools EDGAR quarterly",
      "cashflowStatementHistoryQuarterly": "secfsdstools EDGAR quarterly",
      "defaultKeyStatistics": "derived from EDGAR + price feed",
      "earningsTrend": "FMP free tier or drop column (no SEC equivalent)",
      "financialData": "derived from EDGAR + price feed",
      "incomeStatementHistoryQuarterly": "secfsdstools EDGAR quarterly",
      "majorHoldersBreakdown": "13F via SEC + whalewisdom backstop",
      "netSharePurchaseActivity": "SEC Form 4 (already covered by Insider Buying sister sheet)",
      "price": "FRED/IEX/Polygon/yfinance-replacement",
      "quoteType": "static metadata from EDGAR submissions API",
      "summaryDetail": "derived from EDGAR + price feed",
      "summaryProfile": "static metadata from EDGAR submissions API"
    },
    "ordered_steps": [
      "Rehab Kleven Metrics first (or in parallel) \u2014 DASH consumes its output.",
      "Build VPS writer producing per-ticker fund_cache row keyed on ticker.",
      "Replace ~62k IMPORTJSON formulas with VLOOKUP into fund_cache (collapses to ~200 ARRAYFORMULA-driven lookups).",
      "Wrap remaining IMPORTJSON-touch cells in IFERROR.",
      "Repoint 5 industry!AE column #N/A HYPERLINKs to current industry!A8:A row offsets.",
      "Fix admin!BE3/BF3 TRANSPOSE/SPLIT against same shifted industry list.",
      "Audit and delete the three (old) tabs.",
      "Replace cosmetic finance.yahoo.com/quote/ HYPERLINKs (TradingView or drop)."
    ]
  },
  "scale": {
    "error_cells": 159,
    "named_ranges": 29,
    "tabs": 16,
    "tabs_hidden": 3,
    "total_formulas": 89160,
    "total_rows": 35232,
    "unique_formulas": 65496
  },
  "sheet_id": "1P8j369Wxy_Bzf8AMfrVFdAzAnxzPBfXi1iSQGmGFLms",
  "slug": "kleven_market_tracker",
  "tab_inventory": {
    "$": {
      "errors": 0,
      "formulas": 4977,
      "role": "dollar-volume timeframe view"
    },
    "$ (old)": {
      "errors": 0,
      "formulas": 1008,
      "role": "legacy snapshot, hidden"
    },
    "$+": {
      "errors": 152,
      "formulas": 20608,
      "primary_failure_site": true,
      "role": "heavyweight screener with deep Yahoo quoteSummary pulls"
    },
    "DASH": {
      "downstream_of": "kleven_metrics",
      "errors": 0,
      "formulas": 26,
      "role": "multi-portfolio dashboard; reads from Metrics via IMPORTRANGE"
    },
    "admin": {
      "errors": 2,
      "formulas": 11,
      "role": "config tab \u2014 Yahoo URL templates, sheet IDs, tag list"
    },
    "custom": {
      "errors": 0,
      "formulas": 47644,
      "role": "user-configurable mega-screener; over half the sheet\u0027s formulas live here"
    },
    "d": {
      "errors": 0,
      "formulas": 4977,
      "role": "daily timeframe view"
    },
    "d (old)": {
      "errors": 0,
      "formulas": 306,
      "role": "legacy snapshot, hidden"
    },
    "industry": {
      "errors": 5,
      "formulas": 370,
      "role": "GICS-style industry taxonomy + jump-link index"
    },
    "notes": {
      "errors": 0,
      "formulas": 0,
      "role": "documentation"
    },
    "send": {
      "errors": 0,
      "formulas": 3,
      "role": "concat aggregator pushing tickers back to Metrics"
    },
    "setup": {
      "errors": 0,
      "formulas": 29,
      "role": "QUERY-pivot of Universe sheet rows; sub-industry named range source"
    },
    "start": {
      "errors": 0,
      "formulas": 8,
      "role": "user-facing setup wizard for IMPORTRANGE handshakes"
    },
    "toDo": {
      "errors": 0,
      "formulas": 0,
      "role": "user notes"
    },
    "w": {
      "errors": 0,
      "formulas": 4977,
      "role": "weekly timeframe view"
    },
    "w (old)": {
      "errors": 0,
      "formulas": 4216,
      "role": "legacy snapshot, hidden"
    }
  },
  "vintage": "June 2024",
  "what_is_broken": [
    {
      "cells": "CH4 onward through column GG (152 errors)",
      "error_type": "#ERROR!",
      "root_cause": "IMPORTJSON(admin!$AQ$3 \u0026 ticker \u0026 admin!$AR$3, ...) against query1.finance.yahoo.com/v10/finance/quoteSummary \u2014 endpoint crawler-locked since mid-2023; URL probe confirms finance.yahoo.com/quote/ returns 500",
      "tab": "$+"
    },
    {
      "cells": "AE2 onward (5 errors)",
      "error_type": "#N/A",
      "root_cause": "HYPERLINK+MATCH against industry list with shifted row anchors; cosmetic",
      "tab": "industry"
    },
    {
      "cells": "BE3, BF3 (2 errors)",
      "error_type": "#N/A",
      "root_cause": "TRANSPOSE/SPLIT over industry!B2:AL6 \u2014 same shifted-list issue",
      "tab": "admin"
    }
  ],
  "what_works": [
    "Static link layer: stockcharts/openinsider/whalewisdom/seekingalpha/EDGAR/Google all return 200.",
    "IMPORTRANGE handshake to Universe, Insider Buying, Metrics is structurally intact.",
    "setup-tab QUERY pivots from Universe sheet work.",
    "industry taxonomy (2229 rows) and sub-industry named range (5711 rows) are sound.",
    "DASH layout, custom-tab QUERY engine, and dynamic header-concat column selection are pure-Sheets logic."
  ],
  "yahoo_modules_used": [
    "assetProfile",
    "balanceSheetHistoryQuarterly",
    "cashflowStatementHistoryQuarterly",
    "defaultKeyStatistics",
    "earningsTrend",
    "financialData",
    "incomeStatementHistoryQuarterly",
    "majorHoldersBreakdown",
    "netSharePurchaseActivity",
    "price",
    "quoteType",
    "summaryDetail",
    "summaryProfile"
  ]
}