Annual filings. 30+ years of alpha.

from valuein_sdk import ValueinClient, ValueinError sql = """WITH members AS (    SELECT r.cik, r.symbol, r.name, r.sector    FROM "references" r    JOIN index_membership im ON im.cik = r.cik    WHERE im.index_name = 'SP500'      AND im.removal_date IS NULL      AND r.is_active      AND r.is_primary_ticker),cagr AS (    -- Precomputed in the ratio table; latest annual value per company.    SELECT entity_id, period_end, value AS cagr_5yr    FROM ratio    WHERE ratio_name = 'revenue_cagr_5y'      AND fiscal_period = 'FY'    QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY period_end DESC) = 1)SELECT    m.symbol,    m.name,    m.sector,    c.period_end,    ROUND(c.cagr_5yr, 4) AS cagr_5yrFROM cagr cJOIN members m ON m.cik = c.entity_idWHERE c.cagr_5yr > 0.15ORDER BY cagr_5yr DESCLIMIT 25""" try:    with ValueinClient() as client:        df = client.run_query(sql)        print(df)except ValueinError as e:    print(f"Valuein error: {e}")

from valuein_sdk import ValueinClient, ValueinError sql = """WITH members AS (    SELECT r.cik, r.symbol, r.name, r.sector    FROM "references" r    JOIN index_membership im ON im.cik = r.cik    WHERE im.index_name = 'SP500'      AND im.removal_date IS NULL      AND r.is_active      AND r.is_primary_ticker),margins AS (    SELECT        entity_id,        period_end,        value AS op_margin,        LAG(value, 3) OVER (PARTITION BY entity_id ORDER BY period_end) AS op_margin_3y_ago    FROM ratio    WHERE ratio_name = 'operating_margin'      AND fiscal_period = 'FY'    QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY period_end DESC) = 1)SELECT    m.symbol,    m.name,    g.period_end,    ROUND(g.op_margin_3y_ago * 100, 1)                AS margin_3y_ago_pct,    ROUND(g.op_margin * 100, 1)                       AS margin_latest_pct,    ROUND((g.op_margin - g.op_margin_3y_ago) * 100, 1) AS margin_expansion_ppFROM margins gJOIN members m ON m.cik = g.entity_idWHERE ABS(g.op_margin) <= 1 AND ABS(g.op_margin_3y_ago) <= 1   -- skip implausible values  AND g.op_margin - g.op_margin_3y_ago > 0.03ORDER BY margin_expansion_pp DESCLIMIT 20""" try:    with ValueinClient() as client:        df = client.run_query(sql)        print(df)except ValueinError as e:    print(f"Valuein error: {e}")

from valuein_sdk import ValueinClient, ValueinError sql = """WITH members AS (    SELECT r.cik, r.symbol, r.name, r.sector    FROM "references" r    JOIN index_membership im ON im.cik = r.cik    WHERE im.index_name = 'SP500'      AND im.removal_date IS NULL      AND r.is_active      AND r.is_primary_ticker),annual AS (    -- One value per company, concept and fiscal year: the latest-filed vintage.    SELECT entity_id, period_end, standard_concept, numeric_value    FROM fact    WHERE fiscal_period = 'FY'      AND standard_concept IN ('OperatingCashFlow', 'CAPEX', 'NetIncome')    QUALIFY ROW_NUMBER() OVER (        PARTITION BY entity_id, standard_concept, period_end        ORDER BY accepted_at DESC    ) = 1),latest AS (    SELECT        entity_id,        period_end,        MAX(CASE WHEN standard_concept = 'OperatingCashFlow' THEN numeric_value END) AS ocf,        MAX(CASE WHEN standard_concept = 'CAPEX'             THEN numeric_value END) AS capex,        MAX(CASE WHEN standard_concept = 'NetIncome'         THEN numeric_value END) AS net_income    FROM annual    GROUP BY entity_id, period_end    HAVING ocf IS NOT NULL AND capex IS NOT NULL AND net_income IS NOT NULL    QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY period_end DESC) = 1)SELECT    m.symbol, m.name, m.sector,    l.period_end,    ROUND(l.ocf - ABS(l.capex), 0)                                AS fcf,    ROUND(l.net_income, 0)                                        AS net_income,    ROUND((l.ocf - ABS(l.capex)) / NULLIF(l.net_income, 0), 2)    AS fcf_conversion_ratioFROM latest lJOIN members m ON m.cik = l.entity_idWHERE l.ocf - ABS(l.capex) > 0  AND (l.ocf - ABS(l.capex)) / NULLIF(l.net_income, 0) > 1.1ORDER BY fcf_conversion_ratio DESCLIMIT 20""" try:    with ValueinClient() as client:        df = client.run_query(sql)        print(df)except ValueinError as e:    print(f"Valuein error: {e}")

from valuein_sdk import ValueinClient, ValueinError sql = """WITH members AS (    SELECT r.cik, r.symbol, r.name, r.sector    FROM "references" r    JOIN index_membership im ON im.cik = r.cik    WHERE im.index_name = 'SP500'      AND im.removal_date IS NULL      AND r.is_active      AND r.is_primary_ticker)SELECT    m.symbol,    m.name,    year(r.period_end)      AS year,    ROUND(r.value * 100, 1) AS roic_pctFROM ratio rJOIN members m ON m.cik = r.entity_idWHERE r.ratio_name = 'roic'  AND r.fiscal_period = 'FY'QUALIFY AVG(r.value) OVER (PARTITION BY r.entity_id) > 0.15ORDER BY m.symbol, r.period_end""" try:    with ValueinClient() as client:        df = client.run_query(sql)    print(df.pivot_table(index=["symbol", "name"], columns="year", values="roic_pct"))except ValueinError as e:    print(f"Valuein error: {e}")