Copy of flowerys metrics

{
  "author": "flowery",
  "cross_sheet_dependencies": {
    "downstream": [
      "Tracker (1fIGJcy...) consumes the yahoo!B2:AD schema as its fundamentals input"
    ],
    "shim_design": "yahoo tab is a 28-column FILTER passthrough that mimics old Yahoo Finance schema so downstream sheets need no changes when the data source moves",
    "upstream": [
      "Universe template (1hwnvKYv...) supplies tickers",
      "Tracker (1fIGJcy...) supplies extra tickers"
    ]
  },
  "effort": {
    "blocking_dependencies": [
      "secfsdstools wrapper for is/bs/cfs standardizers must exist and be tested against ~5000 filers",
      "VPS publish target (CSV-per-ticker or master sheet) must be decided",
      "ratio compute library (57 ratios) needs unit tests vs FMP outputs as ground truth before deprecating FMP"
    ],
    "rating_sheet_side": "4/10",
    "rating_writer_side": "7/10",
    "summary": "Medium. Sheet structure is clean \u2014 only 5 endpoint families, 5 receiving tabs, well-documented column schemas. No formula logic to rewrite. Heavy lift is on SEC writer: implement ~57 ratio computations + TTM rollup with field names byte-matching FMP\u0027s. Once writer publishes, sheet swap is half-day."
  },
  "external_dependencies": {
    "fmp_paid_api_trap": {
      "calls_per_ticker_per_recalc": 5,
      "estimated_breakage": "50-ticker universe burns free quota in minutes given Sheets recalc-on-edit",
      "free_tier_limit": "250 calls/day",
      "leak_risk": "leaked Discord key 76194d00cde20e858167dfb370efc6eb still works but FMP can revoke any time; also visible in ratiosTTM!B1 cell content of THIS spreadsheet copy",
      "paid_floor_usd_month": 19,
      "verdict": "shipping this sheet free-to-community requires either signup, payment, or a revocable leaked key \u2014 exactly what SEC-direct path eliminates"
    },
    "importrange_targets": [
      {
        "from_cell": "keymetricsTTM!B2",
        "purpose": "Universe ticker list",
        "range": "setup!AE5:AE",
        "target": "1hwnvKYv2nxkXqfSmPsgcYd4ezxaOfK1fHldgUn8zwXs"
      },
      {
        "from_cell": "receive!C1",
        "purpose": "Tracker (downstream)",
        "range": "send!A:A",
        "target": "1fIGJcyKWO86xYwUaxR057Sg_dxPH_K8fL5T92dioeqQ"
      },
      {
        "from_cell": "receive!D1",
        "purpose": "third sheet",
        "range": "admin!A17:A",
        "target": "17qpbneXwh4O57Y--qSUWH07YlJdSSVA6f5d7gBVPQWU"
      }
    ]
  },
  "infrastructure": {
    "custom_apps_script": "IMPORTJSON (not built-in); apikey!A3 hints script body has hard-coded key in Row 17 of is/bs/cfs script files separate from user-entered key in apikey!B1",
    "empty_value_formulas": 22763,
    "errors": 43,
    "row_pattern": "=IF(B\u003crow\u003e=\"\",, IF(REGEXMATCH(receive!$A$2, \";\"\u0026A\u003crow\u003e\u0026\";\"), INDEX(IMPORTJSON(\"\u003cFMP_URL\u003e\"\u0026A\u003crow\u003e\u0026\"?apikey=\"\u0026apikey!$B$1), 2, ),))",
    "tab_list": [
      "start",
      "apikey",
      "yahoo",
      "isTTM",
      "bsTTM",
      "cfsTTM",
      "keymetricsTTM",
      "ratiosTTM",
      "receive",
      "admin"
    ],
    "tabs": 10,
    "top_functions": {
      "ARRAYFORMULA": 35,
      "FILTER": 33,
      "IMPORTJSON": 22765,
      "IMPORTRANGE": 10,
      "INDEX": 22762,
      "REGEXMATCH": 22759
    },
    "total_formulas": 22809,
    "unique_formulas": 22805
  },
  "name": "Copy of flowerys metrics",
  "purpose": "Emergency post-Yahoo-break replacement plumbing for the Roaring Kitty / DFV community fundamentals stack. Pulls TTM income/balance/cash-flow/key-metrics/ratios from FMP per ticker, exposes them under a `yahoo`-named tab so existing downstream Tracker/Universe sheets work unchanged. Support sheet, not a viewer.",
  "rehab_strategy": {
    "approach": "Replace every FMP endpoint with VPS Python writer reading SEC EDGAR via secfsdstools; preserve isTTM/bsTTM/cfsTTM/keymetricsTTM/ratiosTTM tab schemas exactly so yahoo shim and downstream Tracker keep working untouched.",
    "delivery_mechanism": "VPS writer publishes per-ticker static CSVs (or master Google Sheet kept current) consumed via IMPORTDATA or IMPORTRANGE; replace IMPORTJSON Apps Script entirely; strip apikey tab",
    "endpoint_replacements": [
      {
        "downstream_fields_used": [
          "revenue",
          "Ebitda",
          "Eps",
          "incomebeforetax",
          "incometaxexpense",
          "interestexpense",
          "Depreciationandamortization"
        ],
        "drives_tab": "isTTM",
        "fmp_endpoint": "/api/v3/income-statement/{T}?period=quarter\u0026limit=4",
        "fmp_occurrences": 2,
        "sec_replacement": "secfsdstools IncomeStatementStandardizer over last 4 10-Q filings, sum to TTM",
        "yahoo_columns": [
          "B (revenue)",
          "E (Ebitda)",
          "J (Eps)",
          "Q (incomebeforetax)",
          "R (incometaxexpense)",
          "S (interestexpense)",
          "T (ebitda - D\u0026A)"
        ]
      },
      {
        "downstream_fields_used": [
          "Cashandshortterminvestments",
          "Totaldebt",
          "totalstockholdersequity",
          "Goodwillandintangibleassets",
          "totalcurrentassets",
          "Cashandcashequivalents",
          "Shortterminvestments",
          "inventory",
          "Totalcurrentliabilities",
          "Netreceivables",
          "totalassets",
          "Totalliabilities",
          "retainedearnings"
        ],
        "drives_tab": "bsTTM",
        "fmp_endpoint": "/api/v3/balance-sheet-statement/{T}?period=quarter\u0026limit=4",
        "fmp_occurrences": 2,
        "sec_replacement": "secfsdstools BalanceSheetStandardizer, latest 10-Q snapshot",
        "yahoo_columns": [
          "H,I,K,P,V,W,X,Y,Z,AA,AB,AC,AD"
        ]
      },
      {
        "downstream_fields_used": [
          "Freecashflow",
          "Operatingcashflow",
          "Netcashusedforinvestingactivites",
          "Netcashusedprovidedbyfinancingactivities",
          "capitalexpenditure"
        ],
        "drives_tab": "cfsTTM",
        "fmp_endpoint": "/api/v3/cash-flow-statement/{T}?period=quarter\u0026limit=4",
        "fmp_occurrences": 2,
        "sec_replacement": "secfsdstools CashFlowStandardizer, sum 4 quarters to TTM",
        "yahoo_columns": [
          "L,M,N,O,U"
        ]
      },
      {
        "drives_tab": "keymetricsTTM",
        "fmp_endpoint": "/api/v3/key-metrics-ttm/{T}",
        "fmp_occurrences": 11409,
        "key_derivations": {
          "dividendyieldttm": "cash_dividends_paid_TTM / market_cap",
          "enterprisevaluettm": "market_cap + Totaldebt - Cashandshortterminvestments",
          "sharesout": "dei:EntityCommonStockSharesOutstanding (cover-page XBRL fact)"
        },
        "sec_replacement": "Compute server-side from is/bs/cfs standardizers + price (FRED for yields, equity-price feed for market cap) + cover-page XBRL dei:EntityCommonStockSharesOutstanding for sharesout"
      },
      {
        "all_57_headers": [
          "Dividendyielttm",
          "Dividendyielpercentagettm",
          "Peratiottm",
          "Pegratiottm",
          "Payoutratiottm",
          "Currentratiottm",
          "Quickratiottm",
          "Cashratiottm",
          "Daysofsalesoutstandingttm",
          "Daysofinventoryoutstandingttm",
          "Operatingcyclettm",
          "Daysofpayablesoutstandingttm",
          "Cashconversioncyclettm",
          "Grossprofitmarginttm",
          "Operatingprofitmarginttm",
          "Pretaxprofitmarginttm",
          "Netprofitmarginttm",
          "Effectivetaxratettm",
          "Returnonassetsttm",
          "Returnonequityttm",
          "Returnoncapitalemployedttm",
          "Netincomeperebtttm",
          "Ebtperebitttm",
          "Ebitperrevenuettm",
          "Debtratiottm",
          "Debtequityratiottm",
          "Longtermdebttocapitalizationttm",
          "Totaldebttocapitalizationttm",
          "Interestcoveragettm",
          "Cashflowtodebtratiottm",
          "Companyequitymultiplierttm",
          "Receivablesturnoverttm",
          "Payablesturnoverttm",
          "Inventoryturnoverttm",
          "Fixedassetturnoverttm",
          "Assetturnoverttm",
          "Operatingcashflowpersharettm",
          "Freecashflowpersharettm",
          "Cashpersharettm",
          "Operatingcashflowsalesratiottm",
          "Freecashflowoperatingcashflowratiottm",
          "Cashflowcoverageratiosttm",
          "Shorttermcoverageratiosttm",
          "Capitalexpenditurecoverageratiottm",
          "Dividendpaidandcapexcoverageratiottm",
          "Pricebookvalueratiottm",
          "Pricetobookratiottm",
          "Pricetosalesratiottm",
          "Priceearningsratiottm",
          "Pricetofreecashflowsratiottm",
          "Pricetooperatingcashflowsratiottm",
          "Pricecashflowratiottm",
          "Priceearningstogrowthratiottm",
          "Pricesalesratiottm",
          "Enterprisevaluemultiplettm",
          "Pricefairvaluettm",
          "Dividendpersharettm"
        ],
        "drives_tab": "ratiosTTM",
        "fmp_endpoint": "/api/v3/ratios-ttm/{T}",
        "fmp_occurrences": 11348,
        "key_insight": "FMP\u0027s ratios-ttm is pure arithmetic on the three SEC statements + price; nothing requires a third-party data source",
        "sec_replacement": "Compute server-side, all 57 ratios derivable from is/bs/cfs + price"
      }
    ],
    "schema_preservation": "Critical: keep tab names (isTTM/bsTTM/cfsTTM/keymetricsTTM/ratiosTTM) and column header strings (e.g. \u0027Returnonassetsttm\u0027, \u0027Currentratiottm\u0027) byte-identical so existing FILTER predicates in yahoo tab keep matching"
  },
  "sheet_id": "1IQzunGw82rDageg_R1iD6k6hlc7NCCkgjW1WImzlIDo",
  "slug": "flowery_metrics_fmp",
  "vintage": "Feb 2024",
  "what_broken": {
    "broken_cells_concrete": [
      {
        "cause": "apikey!B1 blank \u2192 IMPORTJSON fails",
        "cell": "isTTM!B1",
        "value": "#ERROR!"
      },
      {
        "cause": "apikey!B1 blank",
        "cell": "bsTTM!B1",
        "value": "#ERROR!"
      },
      {
        "cause": "apikey!B1 blank",
        "cell": "cfsTTM!B1",
        "value": "#ERROR!"
      },
      {
        "cause": "apikey!B1 blank",
        "cell": "keymetricsTTM!C1",
        "value": "#ERROR!"
      },
      {
        "cause": "IMPORTRANGE to 1hwnvKYv2nxkXqfSmPsgcYd4ezxaOfK1fHldgUn8zwXs not granted",
        "cell": "keymetricsTTM!B2",
        "value": "#REF!"
      },
      {
        "cause": "depends on B2 chain",
        "cell": "keymetricsTTM!C2",
        "value": "#REF!"
      },
      {
        "cause": "FILTER predicates match against header rows that are #ERROR!",
        "cell": "yahoo!B2..AD2 (28 cells)",
        "value": "#N/A"
      },
      {
        "cause": "yahoo!A2:A is #N/A \u2192 cascades",
        "cell": "ratiosTTM!A2,B2",
        "value": "#N/A"
      },
      {
        "cause": "trackerSheet IMPORTRANGE not granted",
        "cell": "receive!B1,A2",
        "value": "#N/A"
      },
      {
        "cause": "admin!universe named range empty",
        "cell": "admin!E4",
        "value": "#N/A"
      }
    ],
    "endpoints_dead_without_key": [
      "https://financialmodelingprep.com/api/v3/key-metrics-ttm/ (401)",
      "https://financialmodelingprep.com/api/v3/ratios-ttm/ (401)",
      "https://financialmodelingprep.com/api/v3/income-statement/ (401)",
      "https://financialmodelingprep.com/api/v3/balance-sheet-statement/ (401)",
      "https://financialmodelingprep.com/api/v3/cash-flow-statement/ (401)"
    ]
  },
  "what_works": {
    "endpoints_alive_with_key": {
      "/api/v3/balance-sheet-statement/{T}?period=quarter\u0026limit=4": 200,
      "/api/v3/cash-flow-statement/{T}?period=quarter\u0026limit=4": 200,
      "/api/v3/income-statement/{T}?period=quarter\u0026limit=4": 200,
      "/api/v3/key-metrics-ttm/{T}": 200,
      "/api/v3/ratios-ttm/{T}": 200
    },
    "fmp_key_alive": true,
    "leaked_key": "76194d00cde20e858167dfb370efc6eb",
    "note": "All 5 FMP endpoint families return HTTP 200 with the leaked Discord key. Sheet is mechanically functional today IF the key is supplied AND IMPORTRANGE links to Universe/Tracker are granted."
  }
}