Building an Automated Stock Analysis Report with DuckDB: From Data Fetch to Report Generation
Many data analysts and quantitative enthusiasts share a common pain point: you want to relax on the weekend, but Monday morning’s data reports are mandatory. Repetitive manual work not only wastes time but also introduces errors due to fatigue.
Today, I’ll walk you through building a fully automated stock analysis report system using DuckDB + Python. The entire process runs autonomously: data fetch on Friday after market close, DuckDB local analysis, Markdown report generation, all triggered by cron.
The only infrastructure you need is a local DuckDB database and a few Python scripts.
1. Overall Architecture Design
The system consists of four layers:
- Data Fetch Layer: Use yfinance to pull stock data from Yahoo Finance and save as CSV
- Analysis Layer: DuckDB reads CSV files and executes SQL queries (ranking, volatility, moving average signals)
- Report Layer: Assemble analysis results into a readable Markdown report
- Scheduling Layer: Use cron for timed automatic execution
The core idea: DuckDB excels at processing CSV files, and yfinance can directly export CSV. Combined, no database service deployment is needed — everything runs locally with minimal cost.
2. Data Fetch Layer
Environment Setup
pip install yfinance duckdb pandas matplotlib
Core Script: data_fetcher.py
import yfinance as yf
import duckdb
from datetime import datetime, timedelta
import os
TICKERS = ["AAPL", "MSFT", "GOOGL", "AMZN", "NVDA", "TSLA", "META"]
DATA_DIR = "stock_data"
os.makedirs(DATA_DIR, exist_ok=True)
def fetch_and_save():
end_date = datetime.now()
start_date = end_date - timedelta(days=400)
for ticker in TICKERS:
df = yf.download(ticker, start=start_date, end=end_date, progress=False)
if df.empty:
continue
csv_path = os.path.join(DATA_DIR, f"{ticker}.csv")
df.to_csv(csv_path)
print(f"✅ {ticker} data saved: {csv_path}")
# Load CSVs into DuckDB
conn = duckdb.connect("stocks.db")
conn.execute("DROP TABLE IF EXISTS metadata")
tables = []
for ticker in TICKERS:
csv_path = os.path.join(DATA_DIR, f"{ticker}.csv")
if os.path.exists(csv_path):
conn.execute(f"CREATE TABLE {ticker} AS SELECT * FROM read_csv_auto('{csv_path}')")
tables.append(ticker)
conn.execute("CREATE TABLE metadata AS SELECT 'stocks' as source, current_date() as last_updated")
conn.commit()
conn.close()
print(f"🗄️ Database updated, {len(tables)} stocks loaded")
if __name__ == "__main__":
fetch_and_save()
Key Technical Points:
- yfinance DataFrames have MultiIndex column names (like
('Close', '')), butread_csv_autohandles this automatically — no manual flattening needed - Use
CREATE TABLE ... AS SELECT * FROM read_csv_auto()to load CSVs into an in-memory DuckDB database for fast subsequent queries - The
metadatatable records the data source and last update time, making it easy to check data freshness later
3. Analysis Layer: DuckDB SQL Core
This is the heart of the system. All analysis logic is expressed in SQL — clear, reusable, and auditable.
1. Top Gainers (Last 30 Days)
import duckdb
conn = duckdb.connect("stocks.db")
top_gainers = conn.execute("""
SELECT ticker,
first(close) as close_30d_ago,
last(close) as close_current,
round((last(close) - first(close)) / first(close) * 100, 2) as pct_change
FROM (
SELECT ticker, date, close,
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY date DESC) as rn_desc,
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY date ASC) as rn_asc
FROM (
UNPIVOT stocks ON columns INCLUDE (date) INTO name, value
)
WHERE name = 'close'
) sub
WHERE rn_desc <= 1 OR rn_asc <= 1
GROUP BY ticker
ORDER BY pct_change DESC
LIMIT 5
""").fetchdf()
print("📈 Top 5 Gainers (30-day):")
for _, row in top_gainers.iterrows():
print(f" {row.ticker}: {row.pct_change}%")
Technique Explanation: This uses DuckDB’s UNPIVOT operation. yfinance CSVs have a wide format (Open/High/Low/Close/Volume columns). UNPIVOT reshapes them into a long format (ticker, date, name, value), enabling cross-column analysis. This is a classic pattern for financial time series in DuckDB.
2. Volatility Analysis (Risk Dimension)
volatility = conn.execute("""
SELECT ticker,
round(stddev(close), 2) as volatility,
round(avg(volume) / 1e6, 1) as avg_volume_m,
round(avg(close), 2) as avg_price
FROM (
SELECT ticker, date, close, volume
FROM (
UNPIVOT stocks ON columns INCLUDE (date) INTO name, value
)
WHERE name IN ('close', 'volume')
PIVOT_agg(avg) ON name
)
GROUP BY ticker
ORDER BY volatility DESC
LIMIT 5
""").fetchdf()
print("\n📊 Top 5 by Volatility (High Risk, High Reward):")
for _, row in volatility.iterrows():
print(f" {row.ticker}: Volatility={row.volatility}, Avg Price=${row.avg_price}")
3. Moving Average Signal Detection (Golden Cross / Death Cross)
ma_signals = conn.execute("""
WITH daily AS (
SELECT ticker, date, close,
AVG(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) as ma20,
AVG(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as ma50
FROM (
SELECT ticker, date, close
FROM (
UNPIVOT stocks ON columns INCLUDE (date) INTO name, value
)
WHERE name = 'close'
)
),
signals AS (
SELECT ticker, date, close, ma20, ma50,
LAG(ma20, 1) OVER (PARTITION BY ticker ORDER BY date) as ma20_prev,
LAG(ma50, 1) OVER (PARTITION BY ticker ORDER BY date) as ma50_prev
FROM daily
)
SELECT ticker, date,
CASE
WHEN ma20 > ma50 AND ma20_prev <= ma50_prev THEN '🟢 Golden Cross'
WHEN ma20 < ma50 AND ma20_prev >= ma50_prev THEN '🔴 Death Cross'
ELSE '⚪ No Signal'
END as signal,
round(close, 2) as price
FROM signals
WHERE date >= CURRENT_DATE - INTERVAL '5' DAY
ORDER BY date DESC, ticker
""").fetchdf()
print("\n📉 Latest MA Signals:")
for _, row in ma_signals.iterrows():
print(f" {row.ticker} ({row.date}): {row.signal} @ ${row.price}")
conn.close()
Advanced Technique: This combines DuckDB’s window functions (AVG OVER + LAG) with CTEs. The LAG function retrieves the previous day’s moving average value, enabling golden cross / death cross detection. The entire logic is expressed in SQL without Python loops — extremely efficient.
4. Report Generation Layer
After analysis, assemble results into a readable Markdown report.
report_generator.py
import duckdb
from datetime import datetime
conn = duckdb.connect("stocks.db")
report_date = datetime.now().strftime("%Y-%m-%d")
week_number = datetime.now().isocalendar()[1]
# 30-day gainers
gainers = conn.execute("""
SELECT ticker,
round(last(close) - first(close), 2) as change,
round((last(close) / first(close) - 1) * 100, 2) as pct
FROM (
SELECT ticker, date, close
FROM (UNPIVOT stocks ON columns INCLUDE (date) INTO name, value)
WHERE name = 'close'
)
WHERE date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY ticker
ORDER BY pct DESC
LIMIT 5
""").fetchall()
conn.close()
# Assemble report
lines = [
f"# 📊 Stock Weekly Report Week {week_number} ({report_date})",
"",
"## 🏆 Top 5 Gainers (30-day)",
"",
]
for i, row in enumerate(gainers, 1):
lines.append(f"{i}. **{row[0]}** — Change: {row[2]}% (Absolute: ${row[1]})")
lines += [
"",
"## 💡 Weekly Strategy Note",
"",
"> This content is for reference only and does not constitute investment advice. Investments involve risk.",
"",
f"*Report generated by DuckDB + yfinance | {report_date}*",
]
report = "\n".join(lines)
output_path = f"report_week{week_number}.md"
with open(output_path, "w") as f:
f.write(report)
print(f"✅ Report generated: {output_path}")
5. Automated Scheduling
Use cron to run automatically every Friday after market close:
# Edit cron tasks
crontab -e
# Add this line (5:00 PM UTC on Fridays = 1:00 AM Saturday Beijing time)
0 17 * * 5 cd /path/to/your/project && /usr/bin/python3 run.py
For a more flexible approach, wrap everything in a unified run.py:
#!/usr/bin/env python3
"""Unified entry: fetch data → analyze → generate report"""
import subprocess
import sys
steps = [
("Fetching data", "data_fetcher.py"),
("Running analysis", "analyzer.py"),
("Generating report", "report_generator.py"),
]
for name, script in steps:
print(f"\n{'='*40}")
print(f"▶ {name}: {script}")
print('='*40)
result = subprocess.run([sys.executable, script], capture_output=True, text=True)
print(result.stdout)
if result.returncode != 0:
print(f"❌ {name} failed: {result.stderr}")
sys.exit(1)
print("\n🎉 All steps completed successfully!")
Then simplify your crontab to a single line:
0 17 * * 5 cd /path/to/your/project && /usr/bin/python3 run.py
6. Advanced: Email / Telegram Delivery
After report generation, auto-deliver to your email or Telegram:
Email Delivery
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
def send_report(email_to, report_path, smtp_server, smtp_port, smtp_user, smtp_pass):
with open(report_path, "r") as f:
body = f.read()
msg = MIMEMultipart()
msg['From'] = smtp_user
msg['To'] = email_to
msg['Subject'] = f"📊 Stock Weekly Report - {datetime.now().strftime('%Y-%m-%d')}"
msg.attach(MIMEText(body, 'plain', 'utf-8'))
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(smtp_user, smtp_pass)
server.send_message(msg)
server.quit()
print("📧 Email sent")
Telegram Delivery
import requests
def send_to_telegram(chat_id, bot_token, message):
url = f"https://api.telegram.org/bot{bot_token}/sendMessage"
requests.post(url, json={
"chat_id": chat_id,
"text": message,
"parse_mode": "Markdown"
})
print("📱 Telegram message sent")
7. Comparison with Traditional Approaches
| Dimension | Traditional (Manual) | DuckDB Automated |
|---|---|---|
| Data Fetch | Manual login to platforms | yfinance auto-fetch |
| Data Processing | Manual Excel calculations | DuckDB SQL in one line |
| Analysis Logic | Python loops + Pandas | Pure SQL, reusable & auditable |
| Report Generation | Manual copy-paste | Auto-generated Markdown |
| Scheduling | Manual trigger | Cron fully automated |
| Error Rate | High (human fatigue) | Low (consistent script execution) |
| Maintenance Cost | Redone every time | Set once, benefits long-term |
8. Monetization Suggestions
The value of this system extends beyond time savings — it can be productized:
Paid Subscription Service: Package the weekly report as a paid Telegram channel or Newsletter, charging $5-20/month. DuckDB’s local analysis means zero server costs — extremely high margins.
Data Product Backend: Many small SaaS products need data processing but don’t want to maintain databases. Use DuckDB as the backend engine and offer “analytics as a service,” charging per query.
Automated Report Service: Provide automated weekly/monthly reports for SMEs. Use DuckDB to process their CSV/Excel data and auto-generate reports. $100-500/month per client with near-zero marginal cost.
Quantitative Strategy Backtesting Tool: Extend this system into a strategy backtesting platform. Users submit strategy parameters, DuckDB auto-calculates returns, max drawdown, and other metrics — charge per run.
Core Insight: DuckDB lets you deliver what used to require expensive infrastructure — at near-zero cost (local execution, zero server fees). That’s your competitive moat.
📖 The complete runnable code (including email/Telegram delivery modules) is available at duckdblab.org, with detailed deployment steps and troubleshooting guides.