Building an Automated Investment Research Report System with DuckDB
Many data analysts spend hours every day on repetitive research reports. But the real money is in building “productizable” analysis. Today, I’ll show you how to build an automated investment research report system with DuckDB — from data collection to report generation, all automated. You can use this architecture to offer paid research services.
Why DuckDB?
The traditional research report workflow looks like this: Python scrapes data → pandas cleans it → Excel formats it → manual distribution. It’s extremely inefficient and error-prone.
DuckDB’s core advantages shine here:
- Embedded database, zero deployment cost, runs from a single file
- SQL directly analyzes CSV/Parquet/JSON, no ETL pipeline needed
- Multi-language bindings (Python/R/Node.js), seamless integration into existing workflows
- Vectorized execution, million-row queries in seconds
The entire system we’re building today requires no database server — just one Python script.
System Architecture
The system has three layers:
- Data Layer: Read market data (CSV/Parquet) using DuckDB
- Analysis Layer: Calculate key metrics (PE, ROE, momentum) with SQL
- Output Layer: Generate structured reports and push to subscribers
Let’s start with data preparation. Assume you have a stock history file stocks.csv with fields including date, ticker, close price, volume, and fundamental data.
Step 1: Build the Data Pipeline
import duckdb
import pandas as pd
from datetime import datetime, timedelta
# Connect to in-memory database, zero configuration
con = duckdb.connect(':memory:')
# Register CSV as virtual table, no need to load into memory
con.execute("""
CREATE TABLE stocks AS
SELECT * FROM read_csv_auto('stocks.csv')
""")
# Read and merge multiple files
con.execute("""
CREATE TABLE fundamentals AS
SELECT * FROM read_csv_auto('fundamentals/*.csv')
""")
The key point here is that read_csv_auto automatically infers column types. And it only reads data when actually needed — DuckDB uses predicate pushdown optimization to read only the columns and rows you need.
Step 2: Write Research Analysis SQL
The real core value lies in the analysis logic. We’ll calculate all metrics in one go with SQL:
-- Create analysis view: calculate PE percentile, momentum, ROE trends
CREATE OR REPLACE VIEW daily_analysis AS
WITH price_changes AS (
SELECT
date,
code,
close,
close / LAG(close, 20) OVER (PARTITION BY code ORDER BY date) - 1 AS momentum_20d,
close / LAG(close, 60) OVER (PARTITION BY code ORDER BY date) - 1 AS momentum_60d,
close / LAG(close, 120) OVER (PARTITION BY code ORDER BY date) - 1 AS momentum_120d
FROM stocks
),
ranked_pe AS (
SELECT
code,
date,
pe_ratio,
PERCENT_RANK() OVER (PARTITION BY code ORDER BY pe_ratio) AS pe_percentile,
AVG(pe_ratio) OVER (
PARTITION BY code
ORDER BY date
ROWS BETWEEN 250 PRECEDING AND CURRENT ROW
) AS pe_ma5y
FROM fundamentals
),
combined AS (
SELECT
p.date, p.code, p.close,
p.momentum_20d, p.momentum_60d,
r.pe_ratio, r.pe_percentile, r.pe_ma5y,
f.roe, f.revenue_growth
FROM price_changes p
JOIN ranked_pe r ON p.code = r.code AND p.date = r.date
JOIN fundamentals f ON p.code = f.code AND p.date = f.date
)
SELECT * FROM combined;
Notice which DuckDB native capabilities are used here:
- Window functions
LAG/PERCENT_RANK/AVG OVER: Directly calculate momentum and PE percentiles, no need for complex groupby + join patterns - Time-range aggregation:
ROWS BETWEEN 250 PRECEDING AND CURRENT ROWnaturally expresses “last 5 years” - View materialization:
CREATE OR REPLACE VIEWlets subsequent queries reference cleanly, logical layering is clear
Step 3: Strategy Screening Logic
With the analysis view in place, here’s the core strategy logic:
-- Generate daily picks list
CREATE OR REPLACE VIEW daily_picks AS
SELECT
date, code, name, close,
ROUND(pe_ratio, 2) AS pe,
ROUND(pe_percentile * 100, 1) AS pe_percentile,
ROUND(momentum_60d * 100, 2) AS momentum_60d_pct,
ROUND(roe * 100, 2) AS roe_pct,
ROUND(revenue_growth * 100, 2) AS rev_growth_pct,
CASE
WHEN pe_percentile < 0.3 AND roe > 0.15
AND momentum_60d > 0 AND revenue_growth > 0.1
THEN 'BUY'
WHEN pe_percentile > 0.7 AND momentum_60d < -0.1
THEN 'SELL'
ELSE 'HOLD'
END AS signal
FROM daily_analysis
WHERE date = (SELECT MAX(date) FROM daily_analysis)
ORDER BY pe_percentile ASC;
This query’s output is the core table of your daily research report — sorted by PE percentile, stocks with low PE + high ROE + positive momentum + high growth are marked as BUY.
Step 4: Generate Reports and Push
The final step is turning SQL results into readable reports:
# Get today's picks
today = datetime.now().strftime('%Y-%m-%d')
picks = con.execute("""
SELECT code, name, close, pe, pe_percentile,
momentum_60d_pct, roe_pct, signal
FROM daily_picks
""").fetchdf()
# Generate report text
report = f"""
📊 Investment Research Report {today}
{'='*20}
🔥 Top Picks Today
{chr(10).join([
f"• {row['name']}({row['code']}) | Price:${row['close']:.2f} | PE:{row['pe']}({row['pe_percentile']}% percentile) | ROE:{row['roe_pct']}% | 60d Momentum:{row['momentum_60d_pct']}%"
for _, row in picks[picks['signal'] == 'BUY'].iterrows()
])}
📈 Market Overview
Total Symbols: {len(picks)}
Buy Signals: {len(picks[picks['signal']=='BUY'])}
Sell Signals: {len(picks[picks['signal']=='SELL'])}
⚠️ Disclaimer: Analysis is for reference only, not investment advice.
"""
# Send to Telegram
import requests
bot_token = "YOUR_BOT_TOKEN"
chat_id = "YOUR_CHAT_ID"
requests.post(
f"https://api.telegram.org/bot{bot_token}/sendMessage",
json={"chat_id": chat_id, "text": report, "parse_mode": "HTML"}
)
Step 5: Scheduled Automation
Use cron or Python’s schedule library for nightly execution:
import schedule
import time
def daily_report():
con = duckdb.connect(':memory:')
# ... full logic above ...
print(f"[{datetime.now()}] Report sent")
schedule.every().day.at("22:00").do(daily_report)
while True:
schedule.run_pending()
time.sleep(60)
Comparison with Traditional Approaches
| Dimension | Traditional Approach | DuckDB Approach |
|---|---|---|
| Deployment Cost | Requires MySQL/PostgreSQL server | Zero deployment, in-memory DB |
| Data Prep | ETL pipeline + scheduled extraction | Read CSV/Parquet directly |
| Query Performance | Slow, depends on indexes and partitioning | Vectorized execution, predicate pushdown |
| Development Speed | Multi-language stitching (Python + SQL) | Pure SQL for all logic |
| Maintenance Cost | High, need database monitoring | Low, single-file execution |
Key Optimization Tips
If you’re handling millions of rows, these techniques can boost performance 10x+:
1. Use Parquet Instead of CSV
con.execute("COPY stocks TO 'stocks.parquet' (FORMAT PARQUET)")
# 5-10x faster queries after
con.execute("SELECT * FROM 'stocks.parquet' WHERE date > '2024-01-01'")
2. Set Memory Limits to Prevent OOM
con.execute("SET memory_limit='4GB'")
con.execute("SET threads TO 4")
3. Leverage Partition Pruning
# Only read needed date ranges, DuckDB auto-pushes predicates
con.execute("SELECT * FROM 'parquet_files/*.parquet' WHERE date > '2024-06-01'")
4. Materialize Intermediate Results
con.execute("CREATE TABLE analysis_cache AS SELECT ...")
# Next time, read cache directly, avoid recomputation
con.execute("SELECT * FROM analysis_cache WHERE date = '2024-09-19'")