spb/firc0001_uqo

1"""N10 - universe artefacts: hashes, reconstructible counts, missing files, 'missing' filings, annual_panel CIKs (cluster H, I). Read-only."""2from __future__ import annotations3import json, sys, subprocess4from pathlib import Path5sys.path.insert(0, str(Path(__file__).resolve().parent))6from common import *78r = Run("universe")9META = RDB / "data/metadata"10U = {}11files = ["universe.json", "russell3000_constituents.json", "sp500_constituents.json", "cik_mapping.json", "shards.json"]12U["sha256"] = {f: {"sha256": sha256_file(META / f), "bytes": (META / f).stat().st_size, "mtime": dt.datetime.fromtimestamp((META / f).stat().st_mtime).isoformat()} for f in files if (META / f).exists()}13with open(r.out / "SHA256SUMS", "w") as fh:14    for f, v in U["sha256"].items():15        fh.write(f"{v['sha256']}  {f}\n")16# not archived?17found = subprocess.run(["find", str(RDB), "-maxdepth", "6", "(", "-iname", "*holding*", "-o", "-iname", "company_tickers*", "-o", "-iname", "*IWV*", ")", "-not", "-path", "*/.venv/*"], capture_output=True, text=True).stdout.strip().splitlines()18U["holdings_or_company_tickers_files_found"] = found19uni = json.load(open(META / "universe.json")); ru = json.load(open(META / "russell3000_constituents.json")); sp = json.load(open(META / "sp500_constituents.json")); cm = json.load(open(META / "cik_mapping.json"))20U["universe_keys"] = sorted(uni.keys()); U["russell_keys"] = sorted(ru.keys()); U["sp500_keys"] = sorted(sp.keys())21U["cik_mapping_type"] = type(cm).__name__; U["cik_mapping_size"] = len(cm)22U["cik_mapping_scalars"] = {k: v for k, v in cm.items() if not isinstance(v, (list, dict))} if isinstance(cm, dict) else None23U["cik_mapping_dict_sizes"] = {k: len(v) for k, v in cm.items() if isinstance(v, (list, dict))} if isinstance(cm, dict) else None24U["universe_scalars"] = {k: v for k, v in uni.items() if not isinstance(v, (list, dict))}25U["russell_scalars"] = {k: v for k, v in ru.items() if not isinstance(v, (list, dict))}26U["sp500_scalars"] = {k: v for k, v in sp.items() if not isinstance(v, (list, dict))}27cons = ru.get("constituents", [])28U["russell_constituent_lines"] = len(cons)29U["russell_distinct_ciks"] = len({c.get("cik") for c in cons})30U["russell_constituent_keys"] = sorted(cons[0].keys()) if cons else []31U["russell_unmatched"] = ru.get("unmatched")32dup = {}33for c in cons: dup.setdefault(c.get("cik"), []).append(c.get("ticker"))34U["russell_ciks_with_multiple_lines"] = {k: v for k, v in dup.items() if len(v) > 1}35# mapper view of the unmatched tickers36T2C = cm.get("ticker_to_cik", {}) if isinstance(cm, dict) else {}37def lookup(tkr):38    out = {"in_ticker_to_cik": T2C.get(tkr) or T2C.get(tkr.upper()) or T2C.get(tkr.replace(".", "-")) or T2C.get(tkr.replace("-", "."))}39    for k, v in (cm.items() if isinstance(cm, dict) else []):40        if isinstance(v, dict) and k != "ticker_to_cik" and (tkr in v or tkr.upper() in v):41            out[k] = v.get(tkr, v.get(tkr.upper()))42        if isinstance(v, list) and any(str(x).upper().startswith(tkr.upper()) for x in v[:100000] if isinstance(x, str)):43            out[k] = [x for x in v if isinstance(x, str) and str(x).upper().startswith(tkr.upper())][:3]44    return out45U["unmatched_mapper_view"] = {str(u): lookup(str(u).split(" ")[0]) for u in (ru.get("unmatched") or [])}46# is the unmatched ticker's company present in the universe under another ticker/CIK (e.g. HOLX)?47U["unmatched_in_universe_by_name"] = {str(u): [ (c.get("ticker"), c.get("cik"), c.get("company_name") or c.get("name")) for c in ucons0 if str(u).split("(")[-1].rstrip(")").split(" ")[0].upper() in str(c.get("company_name") or c.get("name") or "").upper()][:3] for u in (ru.get("unmatched") or [])} if (ucons0 := uni.get("constituents", [])) is not None else None48U["unmatched_in_edgar_companies"] = {}49ucons = uni.get("constituents", [])50U["universe_counts"] = {"n": len(ucons), "in_sp500": sum(1 for c in ucons if c.get("in_sp500")), "in_russell3000": sum(1 for c in ucons if c.get("in_russell3000")),51                        "both": sum(1 for c in ucons if c.get("in_sp500") and c.get("in_russell3000")), "sp_only": sum(1 for c in ucons if c.get("in_sp500") and not c.get("in_russell3000")),52                        "ru_only": sum(1 for c in ucons if c.get("in_russell3000") and not c.get("in_sp500")), "keys": sorted(ucons[0].keys()) if ucons else []}53U["sp500_rows"] = len(sp.get("constituents", [])); U["sp500_distinct_ciks"] = len({c.get("cik") for c in sp.get("constituents", [])})54U["reconciliation"] = {"iwv_equity_lines_reported_in_paper": 2587, "reconstructible": False, "reason": "latest-holdings.csv / company_tickers.json not archived; universe.py drops non-company lines with a bare continue",55                       "lines_mapped": ru.get("count"), "distinct_ciks": U["russell_distinct_ciks"], "unmatched": len(ru.get("unmatched") or []), "universe_n": uni.get("count")}56# 'missing' filings and annual_panel CIKs57con = connect(SPID_DB); attach_edgar(con)58U["download_parse_status"] = con.execute("select form_type, download_status, parse_status, count(*) from edgar.filings group by 1,2,3 order by 1,2,3").fetchall()59r.dump("filings_status_missing.csv", con, "select accession_number, cik, ticker, form_type, filing_date, download_status, parse_status, local_path, file_size_bytes from edgar.filings where parse_status in ('missing','error') or download_status <> 'complete' order by parse_status, 1")60U["filings_missing_on_disk"] = con.execute("select form_type, count(*) from edgar.filings where parse_status='missing' group by 1").fetchall()61U["filings_parse_error"] = con.execute("select form_type, count(*) from edgar.filings where parse_status='error' group by 1").fetchall()62U["sections_for_error_filings"] = con.execute("select count(distinct accession_number) from edgar.filing_sections where accession_number in (select accession_number from edgar.filings where parse_status in ('error','missing'))").fetchone()[0]63U["paper_annual_panel_firms"] = 250164U["annual_panel_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.annual_panel").fetchone()[0]65U["filings_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.filings").fetchone()[0]66U["companies_rows"] = con.execute("select count(*) from edgar.companies").fetchone()[0]67U["annual_panel_ciks_not_in_filings"] = con.execute("select list(distinct cik) from edgar.annual_panel where cik not in (select cik from edgar.filings)").fetchone()[0]68U["filings_ciks_not_in_annual_panel"] = con.execute("select list(distinct cik) from edgar.filings where cik not in (select cik from edgar.annual_panel)").fetchone()[0]69U["annual_panel_ciks_not_in_companies"] = con.execute("select list(distinct cik) from edgar.annual_panel where cik not in (select cik from edgar.companies)").fetchone()[0]70U["annual_panel_rows"] = con.execute("select count(*), min(fiscal_year), max(fiscal_year) from edgar.annual_panel").fetchone()71U["quarterly_panel_distinct_ciks"] = con.execute("select count(distinct cik) from edgar.quarterly_panel").fetchone()[0]72for u in (ru.get("unmatched") or []):73    tk = str(u).split(" ")[0]; nm = str(u).split("(")[-1].rstrip(")")74    U["unmatched_in_edgar_companies"][str(u)] = con.execute("select ticker, cik, company_name, in_sp500, in_russell3000 from edgar.companies where upper(ticker)=? or upper(company_name) like ? limit 3", [tk.upper(), "%" + nm.split(" ")[0].upper() + "%"]).fetchall()75U["companies_in_sp500_in_russell"] = con.execute("select count(*) filter (where in_sp500), count(*) filter (where in_russell3000), count(*) filter (where in_sp500 and in_russell3000), count(*) from edgar.companies").fetchone()76r.write_json("universe_artefacts.json", U)77r.finish()78