Build a Stock Portfolio Analysis Dashboard with DuckDB — Turn Your Excel KPI Sheet into an Automated Money-Making Engine
Difficulty: ⭐⭐⭐ ｜ Estimated Setup Time: 2 hours, then 30 seconds daily
Why This Skill Makes Money
Many data analysts, freelancers, and financial advisors have a “personal investment portfolio” — stocks, funds, and ETFs mixed together, manually updated daily in Excel. It’s inefficient and doesn’t scale.
Today we’ll transform this manual workflow into an automated stock portfolio analysis dashboard:
- Read trade records from CSV
- Automatically calculate returns, holdings distribution, and monthly return curves
- Export as shareable HTML dashboards
- Deploy as scheduled tasks that update daily
This system can become three money-making products:
- Personal Investment Advisory Service: Build custom dashboards for clients at ¥500-2000 setup + ¥50-100/month maintenance
- SaaS Product: Wrap with a Streamlit frontend and charge subscription fees
- Data Product: Aggregate trade data to produce “retail investor positioning reports” for institutions
Step 1: Prepare Your Trade Data
Assume you have a trade record file 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
If you don’t have real data, generate mock data directly in 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;
Step 2: Core Analysis Query — Calculate Real Returns in One Shot
This is the heart of the project. A single DuckDB SQL query calculates all key metrics:
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;
Key Techniques Explained:
NULLIF(total_cost, 0)prevents division by zero — when a stock is fully sold, skip return calculationCASE WHENconditional aggregation distinguishes buys from sells, separately tracking holding quantities and costs- CTE layered design keeps logic clear; even with millions of trade records, DuckDB returns results in milliseconds
Compared to Excel: Excel requires manual VLOOKUP formulas and individual price updates. DuckDB handles all calculations with one SQL query, and performance stays stable as data volume grows.
Step 3: Monthly Return Curve Analysis
Want to know which months your portfolio performed best? Use DuckDB’s time-series window functions:
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;
Highlights:
SUM() OVER (ORDER BY month)cumulative sum generates the cumulative investment curve automaticallyLAG()window function calculates month-over-month percentage change, identifying best and worst monthsCOUNT(DISTINCT symbol)tracks changes in active security count over time
This query runs 10x+ faster than Pandas and doesn’t load data into memory. For trade records exceeding one million rows, DuckDB’s vectorized execution engine fully leverages multi-core CPUs.
Step 4: Generate an Auto-Updating HTML Dashboard
Export analysis results as HTML reports you can share with clients or host as a SaaS interface:
import duckdb
import pandas as pd
con = duckdb.connect("portfolio.db")
# Read trade data
con.execute("""
CREATE OR REPLACE TABLE trades AS
SELECT * FROM read_csv_auto('trades.csv')
""")
# Execute core analysis
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()
# Generate HTML dashboard
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>📊 Stock Portfolio Analysis Dashboard</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>📊 Stock Portfolio Analysis Dashboard</h1>
<p>Last Updated: {pd.Timestamp.now().strftime('%Y-%m-%d %H:%M')}</p>
<div class="card">
<h2>Key Metrics</h2>
<div class="kpi">
<div class="kpi-item">
<div>Holdings</div>
<div style="font-size:24px;font-weight:bold">{len(positions)}</div>
</div>
<div class="kpi-item">
<div>Total Invested</div>
<div style="font-size:24px;font-weight:bold">${total_invested:,.0f}</div>
</div>
<div class="kpi-item">
<div>Current Value</div>
<div style="font-size:24px;font-weight:bold">${current_value:,.0f}</div>
</div>
<div class="kpi-item">
<div>Avg Return</div>
<div style="font-size:24px;font-weight:bold" class="{'positive' if avg_pnl > 0 else 'negative'}">{avg_pnl:.1f}%</div>
</div>
</div>
</div>
</body>
</html>"""
with open("portfolio_dashboard.html", "w") as f:
f.write(html_report)
print("✅ Dashboard generated: portfolio_dashboard.html")
con.close()
The result is a standalone HTML file that opens in any browser — no server, no dependencies required.
Step 5: Automation — Daily Auto-Updates
# Add to crontab
crontab -e
# Auto-update dashboard daily at 8 AM
0 8 * * * cd ~/portfolio && python3 update.py >> logs/update.log 2>&1
Advanced: Use DuckDB’s httpfs extension to pull real-time data directly from 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'
);
Comparison with Traditional Approaches
| Approach | Data Processing Speed | Learning Curve | Deployment Complexity | Best For |
|---|---|---|---|---|
| Excel + VBA | Slow (>100K rows lag) | Low | None | Small personal records |
| Python + Pandas | Medium | Medium-High | Requires env config | Data analysis pipelines |
| Tableau/Power BI | Fast | High | Needs licenses | Enterprise BI |
| DuckDB + HTML | Blazing Fast | Low | Minimal | Individual/Micro Products |
DuckDB’s core advantage: it’s both a high-performance database engine and a one-line Python integration. No server installation needed, no connection pool configuration — just import duckdb and query gigabytes of data directly.
本文信息
| 项目 | 内容 |
|---|---|
| DuckDB 版本 | v1.5.x（部分功能基于 v2.0 Preview） |
| 最后验证 | 2026-09-12 |
| 测试环境 | Linux / x86_64 / 16GB RAM |
| 官方文档 | DuckDB Documentation |
| GitHub | pengzz9527/duckdb-blog |
如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。