用 DuckDB 搭建自动财报分析器：从原始 CSV 到投资报告，全程一键生成

import duckdb
from datetime import datetime

# 连接 DuckDB（内存模式，也可换成持久化数据库）
con = duckdb.connect(":memory:")

# 1. 直接读取 CSV（支持 glob，一次读多文件）
con.execute("""
    CREATE TABLE income AS
    SELECT * FROM read_csv_auto('data/income_statement/*.csv');

    CREATE TABLE balance AS
    SELECT * FROM read_csv_auto('data/balance_sheet/*.csv');

    CREATE TABLE quotes AS
    SELECT * FROM read_csv_auto('data/stock_quotes/*.csv');
""")

# 2. 一行 SQL 输出季度财报分析
report_sql = """
WITH quarterly AS (
    SELECT
        i.code,
        STRFTIME(i.date, '%Y-%q') AS quarter,
        AVG(i.revenue)   AS revenue,
        AVG(i.net_profit) AS net_profit,
        AVG(i.gross_margin) AS gross_margin,
        b.total_assets,
        b.total_liabilities,
        b.equity
    FROM income i
    LEFT JOIN balance b
        ON i.code = b.code
        AND STRFTIME(i.date, '%Y-%m') = STRFTIME(b.date, '%Y-%m')
    GROUP BY i.code, quarter, b.total_assets, b.total_liabilities, b.equity
),
with_growth AS (
    SELECT
        *,
        LAG(revenue)        OVER (PARTITION BY code ORDER BY quarter) AS rev_lag1,
        LAG(net_profit)     OVER (PARTITION BY code ORDER BY quarter) AS profit_lag1,
        LAG(gross_margin)   OVER (PARTITION BY code ORDER BY quarter) AS margin_lag1,
        LAG(revenue)        OVER (PARTITION BY code ORDER BY quarter) AS rev_lag4,
        LAG(net_profit)     OVER (PARTITION BY code ORDER BY quarter) AS profit_lag4
    FROM quarterly
)
SELECT
    code,
    quarter,
    ROUND(revenue / 1e8, 2)                AS revenue_亿,
    ROUND(net_profit / 1e8, 2)             AS net_profit_亿,
    ROUND(gross_margin * 100, 1)           AS gross_margin_pct,
    ROUND((revenue  - rev_lag1)  / NULLIF(ABS(rev_lag1), 0)  * 100, 1) AS qoq_rev,
    ROUND((net_profit - profit_lag1) / NULLIF(ABS(profit_lag1), 0) * 100, 1) AS qoq_profit,
    ROUND((revenue - rev_lag4)  / NULLIF(ABS(rev_lag4), 0)  * 100, 1) AS yoy_rev,
    ROUND((net_profit - profit_lag4) / NULLIF(ABS(profit_lag4), 0) * 100, 1) AS yoy_profit,
    ROUND(equity / NULLIF(total_assets, 0) * 100, 1) AS equity_ratio,
    ROUND(net_profit / NULLIF(equity, 0) * 100, 1) AS roe_pct
FROM with_growth
ORDER BY code, quarter DESC;
"""

result = con.execute(report_sql).fetchdf()
print(result.to_string(index=False))

# 3. 一键导出为 Excel 报告
result.to_excel("财报分析_自动生成.xlsx", index=False, engine='openpyxl')
print("✅ 报告已保存")

# pandas 需要手动循环、推断、合并
import pandas as pd
import glob

files = glob.glob('data/income_statement/*.csv')
dfs = [pd.read_csv(f) for f in files]
df = pd.concat(dfs, ignore_index=True)

SELECT * FROM read_csv_auto('data/income_statement/*.csv');

LAG(revenue) OVER (PARTITION BY code ORDER BY quarter) AS rev_lag1

enrich_sql = """
WITH base AS (
    -- 复用上面的 quarterly + with_growth 逻辑
    SELECT
        code, quarter, revenue_亿, net_profit_亿,
        qoq_rev, qoq_profit, yoy_rev, yoy_profit,
        roe_pct
    FROM (/* 上面的完整 report_sql */ sub)
),
latest_quote AS (
    SELECT code, close_price
    FROM quotes
    WHERE date = (SELECT MAX(date) FROM quotes)
)
SELECT
    b.*,
    q.close_price,
    CASE
        WHEN b.roe_pct > 15 AND b.yoy_profit > 10 THEN '🟢 推荐关注'
        WHEN b.roe_pct > 10 AND b.yoy_profit > 0  THEN '🟡 观察'
        ELSE '🔴 谨慎'
    END AS signal
FROM base b
JOIN latest_quote q ON b.code = q.code
ORDER BY b.code, b.quarter DESC;
"""