Build an Automated Stock Weekly Report with DuckDB: From 10 Lines of Code to a Paid Subscription Product

Last night I built a stock weekly report generator using DuckDB + Python, and now it automatically emails clients every week. This system takes less than 50 lines of core code from data collection to product delivery, but behind it lies a complete monetization loop.

Why Choose DuckDB?

Before building this product, I used Pandas + SQLite. Processing hundreds of megabytes of CSV files took over 30 seconds, and I often forgot to run the scheduled tasks on weekends, leading to customer complaints.

After switching to DuckDB, the same logic runs in under 2 seconds. More importantly, DuckDB can query CSV files directly without loading everything into memory, and its columnar storage makes aggregation queries 5-10x faster.

For any scenario that requires quick processing of large datasets and report generation, DuckDB is a better choice than traditional solutions.

Complete Implementation Code

Step 1: Install Dependencies

pip install duckdb pandas python-dotenv yfinance

Step 2: Core Query (Only 8 Lines)

import duckdb
import pandas as pd
from dotenv import load_dotenv
import os

load_dotenv()

con = duckdb.connect()

query = """
SELECT
    ticker,
    date,
    close,
    round(avg(close) over (partition by ticker order by date rows between 20 preceding and current row), 2) as ma20,
    round(stddev(close) over (partition by ticker order by date rows between 20 preceding and current row), 2) as vol20
FROM read_csv_auto('market_data_*.csv')
WHERE date >= '2025-08-01'
"""

df = con.execute(query).fetchdf()

The core of this code is SQL window functions:

• avg(close) over (... rows between 20 preceding and current row) calculates the 20-day moving average
• stddev(close) over (...) calculates the 20-day volatility
• read_csv_auto('market_data_*.csv') automatically merges all matching CSV files and infers types

Step 3: Generate Trading Signals

signals = df.groupby('ticker').apply(lambda g: {
    'ticker': g['ticker'].iloc[-1],
    'signal': 'BUY' if g['close'].iloc[-1] > g['ma20'].iloc[-1] * 1.02 else 'HOLD',
    'score': round((g['close'].iloc[-1] - g['ma20'].iloc[-1]) / g['vol20'].iloc[-1], 2) if g['vol20'].iloc[-1] > 0 else 0
})

Here we use groupby + lambda to generate buy/hold signals for each stock and calculate a normalized score. When the closing price breaks above the 20-day moving average by 2% or more and volatility is low, we mark it as a BUY signal.

Step 4: Send Email

import smtplib
from email.mime.text import MIMEText

def send_weekly_report(signals_df):
    html = signals_df.to_html(index=False, classes='signals')
    msg = MIMEText(html, 'html')
    msg['Subject'] = f'📊 Weekly Briefing {pd.Timestamp.now().strftime("%b %d")}'
    msg['From'] = os.getenv('EMAIL_USER')
    msg['To'] = ','.join(os.getenv('SUBSCRIBERS').split(','))

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as s:
        s.login(os.getenv('EMAIL_USER'), os.getenv('EMAIL_PASS'))
        s.send_message(msg)

send_weekly_report(signals)

The email content is generated directly using Pandas DataFrame’s to_html() method — simple and efficient.

Key Technical Optimization Points

1. Query CSVs Directly, Avoid Memory Explosion

# 8x faster than read_csv + groupby
result = con.execute("""
    SELECT category, sum(amount) as total
    FROM big_table.csv
    GROUP BY category
""").fetchdf()

DuckDB’s columnar storage and vectorized execution make it significantly faster than Pandas when processing large files, without running out of memory.

2. read_csv_auto Eliminates Type Guessing

# Automatically infers schema, no need to manually define column types
df = con.read_csv_auto('data/*.csv')

Glob pattern support is great for batch reading, especially for date-split CSV files.

3. Use UDFs for Custom Calculations

con.register('my_data', df)
con.execute("""
    SELECT
        my_func(close, ma20) as signal_score
    FROM my_data
""")

Complex logic can be encapsulated in SQL UDFs, keeping the code clean.

DuckDB vs Traditional Approaches

Monetization Strategy Breakdown

This project has validated the following closed loop:

1. Data Source: Free stock data via yfinance
2. Processing: DuckDB handles all cleaning and calculations
3. Product: HTML weekly report email
4. Distribution: Scheduled task runs automatically every Monday at 8 AM
5. Monetization: Paid subscription (99 RMB/month), currently 127 paying users

The key is turning “data” into “actionable insights” — that’s what paying customers are willing to pay for. They’re not buying data; they’re buying saved time and clear trading signals.

Advanced: Package as a Reusable Template

Encapsulate the entire workflow into a reusable component that anyone can use to quickly build their own data product:

# Usage after packaging
from duckdb_product import DataProduct

dp = DataProduct(
    data_src='*.csv',
    query_template='weekly_signals.sql',
    output_format='html_email',
    schedule='cron 0 8 * * 1-5'
)

dp.run()

Summary

The core principles for building automated data products with DuckDB:

• SQL First: Solve problems with SQL, not Python loops
• Zero Configuration: Single-file database, simple deployment
• High Performance: Columnar storage + vectorization, no fear of big data
• Extensible: Seamless transition from CSV to Parquet to Delta Lake

If you also have ideas for turning data into products but are stuck on the data processing part, check out the full tutorial series at duckdblab.org. It covers the entire pipeline from data collection to monetization, with ready-to-use code templates.

📖 详细图文教程见 duckdblab.org