Copy of Metrics

{
  "author": "Kleven",
  "cross_sheet_dependencies": [
    {
      "from": "admin!E3",
      "kind": "internal_ref",
      "to": "start!B6"
    },
    {
      "from": "admin!E4",
      "kind": "IMPORTRANGE",
      "to": "Universe.start!B7:F"
    },
    {
      "from": "yahoo!A1",
      "kind": "IMPORTRANGE",
      "to": "Universe.setup!AR1"
    },
    {
      "from": "yahoo!A2",
      "kind": "IMPORTRANGE",
      "to": "Universe.setup!B5:B"
    },
    {
      "from": "receive!A2",
      "kind": "IMPORTRANGE",
      "to": "Tracker.send!A3:A"
    },
    {
      "from": "testing123!A1",
      "kind": "internal_arrayformula",
      "to": "yahoo!A1:A"
    },
    {
      "from": "yahoo!B2",
      "kind": "internal_arrayformula",
      "to": "testing123!B2:BI"
    }
  ],
  "effort_notes": "Math is correct and self-contained from testing123!B2 onward; rebuild is a data-loading exercise, not a logic rewrite. ~25 SEC tags to map, one EDGAR-to-sheet writer, plus thin DASH replacement. Hardest piece: TTM rollup correctness across fiscal-year boundaries.",
  "effort_rating": "medium",
  "external_dependencies": [
    {
      "alive": false,
      "cells": 1,
      "family": "yahoo_v6"
    },
    {
      "alive_http": true,
      "cells": 6,
      "family": "treasury.gov_textview",
      "usable": false
    },
    {
      "alive_http": true,
      "cells": 2,
      "family": "treasury.gov_xml",
      "usable": false
    },
    {
      "alive_http": "mixed",
      "family": "investing.com",
      "pages": 13,
      "usable": false
    },
    {
      "alive_http": "mixed",
      "family": "marketwatch.com",
      "pages": 3,
      "usable": false
    },
    {
      "alive_http": false,
      "cells": 2,
      "family": "cboe.com"
    },
    {
      "alive_http": true,
      "cells": 2,
      "family": "finviz.com",
      "usable": "partial"
    },
    {
      "alive_http": true,
      "cells": 1,
      "family": "fred.stlouisfed.org",
      "note": "FRED API available"
    },
    {
      "alive": true,
      "cells": 338,
      "family": "googlefinance"
    }
  ],
  "infrastructure_shape": "transformation_layer",
  "key_cells": {
    "altman_z_composite": "yahoo!BJ2",
    "ticker_input": "receive!A2",
    "tracker_link": "trackerSheet named range",
    "universe_link": "admin!E3 (= start!B6)",
    "yahoo_v6_probe": "Sheet7!A2"
  },
  "name": "Copy of Metrics",
  "purpose": "TTM ratio + Altman-Z engine for the Kleven Universe/Tracker stack, plus a self-contained macro dashboard (DASH) of treasury yields, commodities, and sector ETFs. testing123 is a 68-column per-ticker fundamentals table fed by an upstream Universe sheet via IMPORTRANGE; downstream columns compute fcf variants, Altman-Z components Z1-Z5, 16 price-ratio multiples, composite Z score, and leverage rank.",
  "rehab_strategy": {
    "approach": "Replace Yahoo TTM ratio inputs with secfsdstools-derived equivalents written directly to testing123 columns B-AD, bypassing the yahoo relay tab and the Universe upstream. Downstream columns AE-BK are pure formula and need no changes.",
    "column_mapping": [
      {
        "col": "B",
        "label": "revs",
        "secfsdstools": "IncomeStatementStandardizer.Revenues",
        "ttm": true,
        "yahoo": "incomeStatementHistory.totalRevenue"
      },
      {
        "col": "C",
        "label": "roa (ttm)",
        "secfsdstools": "derived: NetIncomeLoss / avg Assets",
        "yahoo": "financialData.returnOnAssets"
      },
      {
        "col": "D",
        "label": "roe (ttm)",
        "secfsdstools": "derived: NetIncomeLoss / avg StockholdersEquity",
        "yahoo": "financialData.returnOnEquity"
      },
      {
        "col": "E",
        "label": "ebitda",
        "secfsdstools": "derived: OperatingIncomeLoss + DepreciationAndAmortization",
        "yahoo": "defaultKeyStatistics.ebitda"
      },
      {
        "col": "H",
        "label": "cash, equiv \u0026 sti",
        "secfsdstools": "BalanceSheetStandardizer: CashAndCashEquivalentsAtCarryingValue + ShortTermInvestments",
        "yahoo": "financialData.totalCash"
      },
      {
        "col": "I",
        "label": "debt",
        "secfsdstools": "BalanceSheet: LongTermDebt + ShortTermBorrowings|DebtCurrent",
        "yahoo": "financialData.totalDebt"
      },
      {
        "col": "J",
        "label": "eps",
        "secfsdstools": "IncomeStatement: EarningsPerShareDiluted (TTM sum)",
        "yahoo": "defaultKeyStatistics.trailingEps"
      },
      {
        "col": "L",
        "label": "lfcf (ttm)",
        "secfsdstools": "CashFlow: NetCashProvidedByUsedInOperatingActivities - PaymentsToAcquirePropertyPlantAndEquipment",
        "yahoo": "financialData.freeCashflow"
      },
      {
        "col": "M",
        "label": "cfo (ttm)",
        "secfsdstools": "CashFlowStandardizer.NetCashProvidedByUsedInOperatingActivities",
        "yahoo": "cashflowStatementHistory.totalCashFromOperatingActivities"
      },
      {
        "col": "N",
        "label": "cfi (ttm)",
        "secfsdstools": "CashFlow.NetCashProvidedByUsedInInvestingActivities",
        "yahoo": "totalCashflowsFromInvestingActivities"
      },
      {
        "col": "O",
        "label": "cff (ttm)",
        "secfsdstools": "CashFlow.NetCashProvidedByUsedInFinancingActivities",
        "yahoo": "totalCashFromFinancingActivities"
      },
      {
        "col": "P",
        "label": "tb (tangible book)",
        "secfsdstools": "BalanceSheet: StockholdersEquity - Goodwill - IntangibleAssetsNetExcludingGoodwill",
        "yahoo": "derived"
      },
      {
        "col": "Q",
        "label": "pre-tx inc",
        "secfsdstools": "IncomeStatement: IncomeLossFromContinuingOperationsBeforeIncomeTaxes...",
        "yahoo": "incomeBeforeTax"
      },
      {
        "col": "R",
        "label": "inc tax exp",
        "secfsdstools": "IncomeStatement.IncomeTaxExpenseBenefit",
        "yahoo": "incomeTaxExpense"
      },
      {
        "col": "S",
        "label": "intrst exp",
        "secfsdstools": "IncomeStatement.InterestExpense",
        "yahoo": "interestExpense"
      },
      {
        "col": "T",
        "label": "ebit",
        "secfsdstools": "IncomeStatement.OperatingIncomeLoss",
        "yahoo": "derived"
      },
      {
        "col": "U",
        "label": "capex",
        "secfsdstools": "CashFlow.PaymentsToAcquirePropertyPlantAndEquipment",
        "yahoo": "capitalExpenditures"
      },
      {
        "col": "V-AD",
        "label": "balance-sheet line items",
        "secfsdstools": "BalanceSheet: AssetsCurrent, CashAndCashEquivalentsAtCarryingValue, ShortTermInvestments, InventoryNet, LiabilitiesCurrent, AccountsReceivableNetCurrent, Assets, Liabilities, RetainedEarningsAccumulatedDeficit",
        "yahoo": "balanceSheetHistory"
      }
    ],
    "dash_replacement": "Treasury fiscaldata API for yields, FRED API for GDP/CPI, GOOGLEFINANCE for commodity ETFs (USO/GLD/SLV/URA/LIT/REMX) - drop investing.com/marketwatch/cboe scrapes",
    "preserved_formula_columns": "AE-BK (delta NWC, eff tax rate, fcf/ufcf/sfcf/ncf, Altman Z1-Z5, composite Z, 16 price multiples, leverage rank) - no rewrite needed",
    "price_block": "GOOGLEFINANCE for p and mc still works; alternatively Stooq/EODHD",
    "schema_contract": "testing123 row 1 header (68 cols)"
  },
  "self_contained": false,
  "sheet_id": "1SwLjneSsEfIgMY6awjF3z_yIqzJBzLlV3fTQLTl2zw8",
  "slug": "kleven_metrics",
  "stats": {
    "error_cells": 69,
    "external_urls_unique": 23,
    "importrange_edges": 0,
    "named_ranges": 12,
    "top_functions": {
      "GOOGLEFINANCE": 338,
      "IMPORTHTML": 44,
      "IMPORTRANGE": 8,
      "IMPORTXML": 20,
      "INDEX": 44,
      "SUBSTITUTE": 31,
      "VALUE": 31
    },
    "total_formulas": 428,
    "unique_formulas": 417
  },
  "tabs": {
    "DASH": "57-row macro dashboard (yields, commodities, sector ETFs, individual funds)",
    "Notes": "user notes",
    "Sheet7": "single direct Yahoo v6 quoteSummary probe via IMPORTJSON (broken)",
    "admin": "12 named ranges (config inputs)",
    "receive": "ticker watchlist receiver from Tracker.send",
    "start": "user-facing wizard, IMPORTRANGE handshake to Universe and Tracker sheets",
    "test": "treasury yield XML probe (broken)",
    "testing123": "main 68-col TTM fundamentals + derived metrics table",
    "yahoo": "thin IMPORTRANGE relay between Universe.setup and testing123"
  },
  "upstream_dependencies": [
    "Kleven Universe sheet (admin!E7) - holds the actual Yahoo HTTP scraping in setup tab",
    "Kleven Tracker sheet (trackerSheet named range) - holds watchlist in send!A3:A"
  ],
  "vintage": "2024-06",
  "what_broken": {
    "cboe_daily_stats": {
      "affected_cells": [
        "DASH!AA10",
        "DASH!AB10"
      ],
      "endpoint": "https://www.cboe.com/us/options/market_statistics/daily/",
      "probe_status": 404
    },
    "fmp_unauth": {
      "endpoint": "https://financialmodelingprep.com/api/v3/ratios-ttm/",
      "note": "Same endpoint with embedded apikey returns 200 - this is the FMP fallback Discord history mentions",
      "probe_status": 401
    },
    "googlefinance_dead_symbols": {
      "affected_cells": [
        "DASH!K7",
        "DASH!L7",
        "DASH!AA3",
        "DASH!AB3",
        "DASH!W5",
        "DASH!X5",
        "DASH!S16",
        "DASH!T16"
      ],
      "symbols": [
        "FTW5000",
        "SLY",
        "IRBO"
      ]
    },
    "investing_root": {
      "endpoint": "https://www.investing.com/commodities/",
      "probe_status": 404
    },
    "investing_subpages_unscrapable": {
      "affected_cells_pattern": "DASH!O4:P34 (35+ cells)",
      "note": "Pages return 200 but IMPORTHTML can no longer parse them",
      "probe_status": 200
    },
    "marketwatch_anti_bot": {
      "affected_cells": [
        "DASH!W16",
        "DASH!X16",
        "DASH!AA21",
        "DASH!AB21",
        "DASH!W17",
        "DASH!X17",
        "DASH!AA55",
        "DASH!AB55",
        "DASH!AA56",
        "DASH!AB56"
      ],
      "probe_status": 401
    },
    "treasury_textview_layout_drift": {
      "affected_cells": [
        "DASH!C3",
        "DASH!D3",
        "DASH!C4",
        "DASH!D4"
      ],
      "note": "Page layout shifted; B100 anchor cell empty",
      "probe_status": 200
    },
    "treasury_yield_xml": {
      "affected_cells": [
        "test!A3",
        "test!B3"
      ],
      "endpoint": "https://home.treasury.gov/sites/default/files/interest-rates/yield.xml",
      "note": "IMPORTXML cannot parse",
      "probe_status": 200
    },
    "yahoo_quote_page": {
      "endpoint": "https://finance.yahoo.com/quote/",
      "probe_status": 500
    },
    "yahoo_v6_quoteSummary": {
      "affected_cells": [
        "Sheet7!A2"
      ],
      "endpoint": "https://query2.finance.yahoo.com/v6/finance/quoteSummary/AAPL?modules=financialData,defaultKeyStatistics",
      "note": "v6 retired post-2023; v10 path requires crumb cookie",
      "probe_status": 404
    }
  },
  "what_works": [
    "338 GOOGLEFINANCE calls for ETF/index/fund prices and changepct (QQQ, TAIL, MTUM, IBB, XBI, VTI, ITOT, ESPO, SPMD, VIX, etc.)",
    "Treasury textview pages return 200 (layout shifted, indexed lookups #REF)",
    "IMPORTRANGE wiring in start/admin works given a live Universe URL",
    "Math from testing123!B2 onward is correct and pure-formula"
  ]
}