用 DuckDB 搭建股票组合分析仪表盘——把 Excel 里的 KPI 表变成自动赚钱引擎

手把手教你用 DuckDB + Python 搭建股票组合分析仪表盘：交易记录导入、持仓盈亏计算、月度收益曲线、HTML 自动报表生成。把 Excel 手工表变成定时更新的 SaaS 产品。

用 DuckDB 搭建股票组合分析仪表盘——把 Excel 里的 KPI 表变成自动赚钱引擎

难度：⭐⭐⭐ ｜ 预计耗时：2 小时搭建，之后每天 30 秒自动生成

一、为什么这个技能能赚钱？

很多数据分析师、自由职业者和理财顾问手里都有一份"个人投资组合"——股票、基金、ETF 混在一起，用 Excel 手动更新每日涨跌。这不仅效率低，而且无法规模化。

今天我们要做的，是把这套手工流程变成一个自动化的股票组合分析仪表盘：

读取交易记录 CSV
自动计算收益率、持仓分布、月度收益曲线
导出成可分享的 HTML 仪表盘
部署成定时任务，每天自动更新

这个系统可以变成三种赚钱产品：

个人投资顾问服务：帮客户搭建，收取 500-2000 元定制费 + 每月 50-100 元维护费
SaaS 化：封装成 Streamlit 前端，按订阅制收费
数据产品：积累交易数据后，输出"散户持仓趋势报告"卖给投资机构

二、准备交易数据

假设你有一个交易记录文件 trades.csv，格式如下：

date,Symbol,Side,Price,Quantity
2026-01-02,AAPL,BUY,185.50,100
2026-01-05,MSFT,BUY,310.20,50
2026-01-08,AAPL,BUY,188.00,50
2026-02-10,TSLA,SELL,200.00,30
2026-02-15,GOOGL,BUY,140.00,80
2026-03-01,AAPL,SELL,195.00,60
2026-03-15,MSFT,BUY,320.00,40
2026-04-01,TSLA,BUY,180.00,60
2026-04-20,AMZN,BUY,175.00,45
2026-05-05,GOOGL,SELL,155.00,30

如果没有真实数据，可以用 DuckDB 直接生成模拟数据练手：

CREATE OR REPLACE TABLE trades AS
SELECT
    d::DATE AS date,
    t.symbol,
    t.side,
    ROUND(t.price * (1 + (RANDOM() - 0.5) * 0.1), 2) AS price,
    FLOOR(RANDOM() * 100 + 10)::INT AS quantity
FROM
    generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-07-03', INTERVAL '1 day') AS s(d),
    UNNEST(ARRAY['AAPL','MSFT','TSLA','GOOGL','AMZN','NVDA']) AS t(symbol),
    UNNEST(ARRAY['BUY','SELL']) AS t(side),
    UNNEST(ARRAY[150.0, 310.0, 180.0, 140.0, 175.0, 140.0]) AS t(price)
WHERE RANDOM() < 0.15;

三、核心分析查询——一键算出真实收益

这是整个项目的灵魂。一行 DuckDB SQL，算出所有关键指标：

WITH position_summary AS (
    SELECT
        symbol,
        SUM(CASE WHEN side = 'BUY' THEN quantity ELSE 0 END) AS total_bought,
        SUM(CASE WHEN side = 'BUY' THEN price * quantity ELSE 0 END) AS total_cost,
        SUM(CASE WHEN side = 'SELL' THEN quantity ELSE 0 END) AS total_sold,
        SUM(CASE WHEN side = 'SELL' THEN price * quantity ELSE 0 END) AS total_revenue,
        SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) AS current_holding
    FROM trades
    GROUP BY symbol
),
current_prices AS (
    SELECT * FROM (VALUES
        ('AAPL', 230.50), ('MSFT', 450.00), ('TSLA', 260.00),
        ('GOOGL', 185.00), ('AMZN', 195.00), ('NVDA', 135.00)
    ) AS t(symbol, last_price)
)
SELECT
    p.symbol,
    p.current_holding,
    ROUND(p.total_cost, 2) AS total_invested,
    ROUND(p.current_holding * c.last_price, 2) AS current_value,
    ROUND((p.current_holding * c.last_price - p.total_cost) / NULLIF(p.total_cost, 0) * 100, 2) AS unrealized_pnl_pct,
    ROUND(c.last_price, 2) AS current_price
FROM position_summary p
JOIN current_prices c ON p.symbol = c.symbol
WHERE p.current_holding > 0
ORDER BY unrealized_pnl_pct DESC;

关键技巧解析：

NULLIF(total_cost, 0) 防止除零错误——当某只股票全部卖出后，total_cost 为零，不做收益率计算
CASE WHEN 条件聚合区分买入和卖出，分别统计持仓量和成本
CTE 分层设计让逻辑清晰，即使交易记录超过百万行，DuckDB 也能在毫秒级返回结果

对比 Excel 方案：Excel 需要手动维护 VLOOKUP 公式、逐个更新价格，而 DuckDB 一条 SQL 搞定所有计算，数据量增大时性能反而更稳定。

四、月度收益曲线分析

想知道你的组合哪个月表现最好？用 DuckDB 的时间序列窗口函数：

WITH monthly_trades AS (
    SELECT
        DATE_TRUNC('month', date) AS month,
        symbol, side, price, quantity,
        price * quantity AS trade_value
    FROM trades
),
monthly_pnl AS (
    SELECT
        month,
        SUM(CASE WHEN side = 'BUY' THEN -trade_value ELSE trade_value END) AS net_cashflow,
        COUNT(DISTINCT symbol) AS active_symbols,
        SUM(trade_value) AS total_volume
    FROM monthly_trades
    GROUP BY month
)
SELECT
    month,
    ROUND(net_cashflow, 2) AS net_cashflow,
    active_symbols,
    ROUND(total_volume, 2) AS total_volume,
    ROUND(SUM(net_cashflow) OVER (ORDER BY month), 2) AS cumulative_invested,
    ROUND(
        (net_cashflow - LAG(net_cashflow) OVER (ORDER BY month))
        / NULLIF(ABS(LAG(net_cashflow) OVER (ORDER BY month)), 0) * 100, 2
    ) AS mom_change_pct
FROM monthly_pnl
ORDER BY month;

核心亮点：

SUM() OVER (ORDER BY month) 累积求和，自动生成累计投入曲线
LAG() 窗口函数计算环比变化百分比，识别最佳和最差月份
COUNT(DISTINCT symbol) 追踪活跃标的数量变化

这个查询比 Pandas 快 10 倍以上，且不需要把数据加载到内存。对于超过百万行的交易记录，DuckDB 的向量化执行引擎能充分发挥多核 CPU 优势。

五、生成自动更新的 HTML 仪表盘

把分析结果导出成 HTML 报表，可以发给客户或挂在服务器上作为 SaaS 界面：

import duckdb
import pandas as pd

con = duckdb.connect("portfolio.db")

# 读取交易数据
con.execute("""
    CREATE OR REPLACE TABLE trades AS
    SELECT * FROM read_csv_auto('trades.csv')
""")

# 执行核心分析查询
positions = con.execute("""
    WITH position_summary AS (
        SELECT
            symbol,
            SUM(CASE WHEN side = 'BUY' THEN quantity ELSE 0 END) AS total_bought,
            SUM(CASE WHEN side = 'BUY' THEN price * quantity ELSE 0 END) AS total_cost,
            SUM(CASE WHEN side = 'SELL' THEN quantity ELSE 0 END) AS total_sold,
            SUM(CASE WHEN side = 'SELL' THEN price * quantity ELSE 0 END) AS total_revenue,
            SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) AS current_holding
        FROM trades
        GROUP BY symbol
    ),
    current_prices AS (
        SELECT * FROM (VALUES
            ('AAPL', 230.50), ('MSFT', 450.00), ('TSLA', 260.00),
            ('GOOGL', 185.00), ('AMZN', 195.00), ('NVDA', 135.00)
        ) AS t(symbol, last_price)
    )
    SELECT
        p.symbol,
        p.current_holding,
        ROUND(p.total_cost, 2) AS total_invested,
        ROUND(p.current_holding * c.last_price, 2) AS current_value,
        ROUND((p.current_holding * c.last_price - p.total_cost) / NULLIF(p.total_cost, 0) * 100, 2) AS pnl_pct,
        ROUND(c.last_price, 2) AS current_price
    FROM position_summary p
    JOIN current_prices c ON p.symbol = c.symbol
    WHERE p.current_holding > 0
    ORDER BY pnl_pct DESC
""").fetchdf()

# 生成 HTML 仪表盘
total_invested = positions['total_invested'].sum()
current_value = positions['current_value'].sum()
avg_pnl = positions['pnl_pct'].mean()

html_report = f"""<!DOCTYPE html>
<html>
<head>
    <title>📊 股票组合分析仪表盘</title>
    <style>
        body {{ font-family: -apple-system, sans-serif; padding: 20px; background: #f5f5f5; }}
        .card {{ background: white; border-radius: 12px; padding: 20px; margin: 10px 0; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }}
        .kpi {{ display: flex; gap: 20px; flex-wrap: wrap; }}
        .kpi-item {{ background: linear-gradient(135deg, #667eea, #764ba2); color: white; padding: 15px 25px; border-radius: 8px; min-width: 150px; }}
        .positive {{ color: #22c55e; font-weight: bold; }}
        .negative {{ color: #ef4444; font-weight: bold; }}
    </style>
</head>
<body>
    <h1>📊 股票组合分析仪表盘</h1>
    <p>更新时间：{pd.Timestamp.now().strftime('%Y-%m-%d %H:%M')}</p>
    <div class="card">
        <h2>核心指标</h2>
        <div class="kpi">
            <div class="kpi-item">
                <div>持仓数量</div>
                <div style="font-size:24px;font-weight:bold">{len(positions)}</div>
            </div>
            <div class="kpi-item">
                <div>总投入</div>
                <div style="font-size:24px;font-weight:bold">${total_invested:,.0f}</div>
            </div>
            <div class="kpi-item">
                <div>当前市值</div>
                <div style="font-size:24px;font-weight:bold">${current_value:,.0f}</div>
            </div>
            <div class="kpi-item">
                <div>综合收益率</div>
                <div style="font-size:24px;font-weight:bold" class="{'positive' if avg_pnl > 0 else 'negative'}">{avg_pnl:.1f}%</div>
            </div>
        </div>
    </div>
    <div class="card">
        <h2>持仓明细</h2>
        <table style="width:100%;border-collapse:collapse">
            <tr style="background:#f0f0f0">
                <th style="padding:8px;text-align:left">股票代码</th>
                <th style="padding:8px;text-align:right">持仓量</th>
                <th style="padding:8px;text-align:right">总投入</th>
                <th style="padding:8px;text-align:right">当前市值</th>
                <th style="padding:8px;text-align:right">收益率</th>
            </tr>
            {''.join(f'''<tr>
                <td style="padding:8px;border-bottom:1px solid #eee">{row['symbol']}</td>
                <td style="padding:8px;border-bottom:1px solid #eee;text-align:right">{int(row['current_holding'])}</td>
                <td style="padding:8px;border-bottom:1px solid #eee;text-align:right">${row['total_invested']:,.0f}</td>
                <td style="padding:8px;border-bottom:1px solid #eee;text-align:right">${row['current_value']:,.0f}</td>
                <td style="padding:8px;border-bottom:1px solid #eee;text-align:right" class="{'positive' if row['pnl_pct'] > 0 else 'negative'}">{row['pnl_pct']:.1f}%</td>
            </tr>''' for _, row in positions.iterrows())}
        </table>
    </div>
</body>
</html>"""

with open("portfolio_dashboard.html", "w") as f:
    f.write(html_report)
print("✅ 仪表盘已生成: portfolio_dashboard.html")
con.close()

运行后得到一个独立的 HTML 文件，双击即可在浏览器中打开——无需任何服务器、无需任何依赖。

六、自动化——让数据每天自动更新

# 添加到 crontab
crontab -e
# 每天早上 8 点自动更新仪表盘
0 8 * * * cd ~/portfolio && python3 update.py >> logs/update.log 2>&1

进阶玩法：用 DuckDB 的 httpfs 扩展直接从 Yahoo Finance 拉取实时数据：

INSTALL httpfs;
LOAD httpfs;
SELECT * FROM read_csv_auto(
    'https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1640995200&period2=1751251200&interval=1d'
);

七、与传统方案的对比

方案	数据处理速度	学习成本	部署复杂度	适合场景
Excel + VBA	慢（>10万行卡顿）	低	无	个人小规模记录
Python + Pandas	中等	中高	需环境配置	数据分析流程
Tableau/Power BI	快	高	需许可证	企业级 BI
DuckDB + HTML	极快	低	极低	个人/小微产品

DuckDB 的核心优势在于：它既是高性能的数据库引擎，又是 Python 的一行代码集成库。你不需要安装任何服务器，不需要配置连接池，import duckdb 之后就可以直接查询 GB 级别的数据。

八、变现建议

搭建好这个系统后，你有三条变现路径：

路径一：个人投资顾问服务（最快起步）

帮朋友、客户或社群成员搭建个性化投资组合仪表盘
定价：一次性定制费 500-2000 元 + 每月 50-100 元数据维护费
获客渠道：雪球、知乎、小红书分享分析截图引流

路径二：SaaS 化（中期目标）

把 HTML 导出改成 Streamlit 或 FastAPI 前端
接入实时行情 API（如 Alpha Vantage、Yahoo Finance）
按订阅制收费：免费版基础功能，Pro 版 $9.9/月获取实时数据和高级分析

路径三：数据产品（长期布局）

积累足够多的匿名交易数据后，用 DuckDB 做市场行为分析
输出"散户持仓趋势报告"“热门标的资金流向"等数据产品
卖给投资机构、财经媒体或作为付费 Newsletter 内容

这个系统的妙处在于：你不需要成为量化专家才能赚钱。只要你能把数据整理得比对手更清晰、更及时，就已经超越了 90% 的手工 Excel 用户。

📖 本文的完整版已发布在 duckdblab.org，包含更详细的步骤和更多案例。想系统学习 DuckDB 在金融领域的应用？duckdblab.org 上有完整的教程系列，从入门到变现一站式搞定。

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

⚠️ 本站为独立社区项目，与 DuckDB 基金会及 DuckDB 官方项目无任何从属、背书或赞助关系。

"DuckDB" 是 DuckDB 基金会的注册商标，本站仅以事实描述方式使用该名称。

本站内容仅供教育与社区推广用途，不构成任何商业服务。