Tutorials Python

DuckDB in Action: Building Automated Financial Report Systems with Python

Build an automated financial report generator with DuckDB + Python that compresses hours of manual work into seconds, and turns it into a billable SaaS product.

DuckDB in Action: Building Automated Financial Report Systems with Python

Tonight’s project: build a money-making tool — an automated financial report generator.

Many small and medium enterprises have their financial data scattered across CSV and Excel files, spending hours every month manually assembling reports. With DuckDB, you can compress this process to seconds — and even turn it into a SaaS service with subscription billing.

1. Project Background & Business Value

Imagine this scenario: you serve 10 small business clients, generating financial summary reports for each one monthly. The traditional approach involves manually exporting data from various sources, cleaning, pivoting, and producing reports — taking 2-3 hours per client.

With the DuckDB automation solution:

Metric	Traditional Approach	DuckDB Solution
Generation time	2-3 hours	10 seconds
Scalable clients	10	Unlimited
Data accuracy	Manual errors likely	SQL auto-validation
Marginal cost	Linear growth	Near zero
Billing model	Hourly rate	Subscription

This is a textbook example of using technical leverage to scale service capacity.

Commercialization Paths

Path	Investment	Monthly Revenue Estimate
Personal service	Minimal	$50-100/client
SaaS product	Medium	$199-999/month/enterprise
Template sales	Low	One-time revenue
Consulting + implementation	Medium	$5000+ per project

2. Project Structure Design

A production-grade financial report system should include:

finance_report/
├── data/
│   ├── income.csv          # Income data
│   ├── expense.csv         # Expense data
│   └── budget.csv          # Budget data
├── generate_report.py      # Core script
├── report_config.json      # Configuration
└── output/
    └── report_q3_2024.md   # Generated report

3. Data Preparation & Loading

Start with sample data. In real projects, your data comes from various sources — CSV, databases, APIs. Here we demonstrate using DuckDB’s native capabilities directly.

import duckdb
import json
from pathlib import Path
from datetime import datetime

# Create sample data (in practice, these come from different sources)
con = duckdb.connect(':memory:')

con.execute("""
CREATE TABLE income AS SELECT * FROM read_csv_auto('data/income.csv');
CREATE TABLE expense AS SELECT * FROM read_csv_auto('data/expense.csv');
CREATE TABLE budget AS SELECT * FROM read_csv_auto('data/budget.csv');
""")

System Architecture

Figure: DuckDB Financial Report System Architecture — Unified queries across multiple data sources

Sample Data Generation

You can use this Python script to generate test data:

import pandas as pd
from datetime import datetime, timedelta
import random

# Generate income data
income_data = []
categories = ['Product Sales', 'Service Revenue', 'Investment Income', 'Other Income']
for i in range(100):
    income_data.append({
        'date': (datetime(2024, 1, 1) + timedelta(days=random.randint(0, 364))).strftime('%Y-%m-%d'),
        'category': random.choice(categories),
        'amount': round(random.uniform(100, 50000), 2),
        'description': f'Income record #{i+1}'
    })

# Generate expense data
expense_data = []
expense_categories = ['Personnel Costs', 'Server Expenses', 'Marketing', 'Office Supplies', 'Travel']
for i in range(150):
    expense_data.append({
        'date': (datetime(2024, 1, 1) + timedelta(days=random.randint(0, 364))).strftime('%Y-%m-%d'),
        'category': random.choice(expense_categories),
        'amount': round(random.uniform(50, 20000), 2),
        'description': f'Expense record #{i+1}'
    })

# Generate budget data
budget_data = []
for cat in expense_categories:
    budget_data.append({
        'year': 2024,
        'category': cat,
        'budget_amount': round(random.uniform(50000, 500000), 2)
    })

# Save as CSV
pd.DataFrame(income_data).to_csv('data/income.csv', index=False)
pd.DataFrame(expense_data).to_csv('data/expense.csv', index=False)
pd.DataFrame(budget_data).to_csv('data/budget.csv', index=False)
print("Test data generation complete")

4. Core Query Logic

This is the soul of the entire project — performing multi-dimensional analysis with DuckDB SQL:

4.1 Income Summary Query

SELECT 
    category,
    SUM(amount) AS total_income,
    AVG(amount) AS avg_income,
    COUNT(*) AS transaction_count
FROM income
WHERE strftime(date, '%Y-%m') = '2024-06'
GROUP BY category
ORDER BY total_income DESC

4.2 Expense Summary Query

SELECT 
    category,
    SUM(amount) AS total_expense,
    AVG(amount) AS avg_expense,
    COUNT(*) AS transaction_count
FROM expense
WHERE strftime(date, '%Y-%m') = '2024-06'
GROUP BY category
ORDER BY total_expense DESC

4.3 Key Financial Metrics

SELECT 
    SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) AS total_income,
    SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) AS total_expense,
    SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) -
    SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) AS net_profit,
    ROUND(
        (SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) -
         SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END)) * 100.0 /
        NULLIF(SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END), 0),
        2
    ) AS profit_margin
FROM (
    SELECT date, 'income' AS type, amount FROM income
    UNION ALL
    SELECT date, 'expense' AS type, amount FROM expense
) t
WHERE strftime(t.date, '%Y-%m') = '2024-06'

4.4 Budget Execution Comparison

SELECT 
    b.category,
    b.budget_amount,
    COALESCE(e.total_expense, 0) AS actual_expense,
    b.budget_amount - COALESCE(e.total_expense, 0) AS remaining,
    CASE 
        WHEN b.budget_amount > 0 
        THEN ROUND(COALESCE(e.total_expense, 0) * 100.0 / b.budget_amount, 1)
        ELSE 0 
    END AS usage_rate
FROM budget b
LEFT JOIN (
    SELECT category, SUM(amount) AS total_expense
    FROM expense
    WHERE strftime(date, '%Y-%m') = '2024-06'
    GROUP BY category
) e ON b.category = e.category
WHERE b.year = 2024
ORDER BY usage_rate DESC

5. Python Integration & Report Generation

5.1 Financial Summary Function

def generate_financial_summary(con, year: int, month: int) -> dict:
    """Generate monthly financial summary"""
    
    period = f"{year}-{month:02d}"
    
    # 1. Income summary
    income_query = f"""
        SELECT 
            category,
            SUM(amount) AS total_income,
            AVG(amount) AS avg_income,
            COUNT(*) AS transaction_count
        FROM income
        WHERE strftime(date, '%Y-%m') = '{period}'
        GROUP BY category
        ORDER BY total_income DESC
    """
    
    # 2. Expense summary
    expense_query = f"""
        SELECT 
            category,
            SUM(amount) AS total_expense,
            AVG(amount) AS avg_expense,
            COUNT(*) AS transaction_count
        FROM expense
        WHERE strftime(date, '%Y-%m') = '{period}'
        GROUP BY category
        ORDER BY total_expense DESC
    """
    
    # 3. Key metrics
    metrics_query = f"""
        SELECT 
            SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) AS total_income,
            SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) AS total_expense,
            SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) -
            SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) AS net_profit,
            ROUND(
                (SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END) -
                 SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END)) * 100.0 /
                NULLIF(SUM(CASE WHEN t.type='income' THEN t.amount ELSE 0 END), 0),
                2
            ) AS profit_margin
        FROM (
            SELECT date, 'income' AS type, amount FROM income
            UNION ALL
            SELECT date, 'expense' AS type, amount FROM expense
        ) t
        WHERE strftime(t.date, '%Y-%m') = '{period}'
    """
    
    # Execute queries
    income_df = con.execute(income_query).df()
    expense_df = con.execute(expense_query).df()
    metrics_df = con.execute(metrics_query).df()
    
    return {
        'income': income_df.to_dict('records'),
        'expense': expense_df.to_dict('records'),
        'metrics': metrics_df.iloc[0].to_dict() if len(metrics_df) > 0 else {},
    }

5.2 Markdown Report Rendering

def render_report(summary: dict, year: int, month: int) -> str:
    """Render Markdown report"""
    
    lines = [
        f"# Financial Report — {year}-{month:02d}",
        "",
        f"**Generated**: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}",
        "",
    ]
    
    # Key metrics
    m = summary['metrics']
    lines += [
        "## 📊 Key Metrics",
        "",
        "| Metric | Value |",
        "|--------|-------|",
        f"| Total Income | ${m.get('total_income', 0):,.2f} |",
        f"| Total Expenses | ${m.get('total_expense', 0):,.2f} |",
        f"| Net Profit | ${m.get('net_profit', 0):,.2f} |",
        f"| Profit Margin | {m.get('profit_margin', 0)}% |",
        "",
    ]
    
    # Income breakdown
    lines += ["## 💰 Income Breakdown", ""]
    for row in summary['income']:
        lines.append(f"- **{row['category']}**: ${row['total_income']:,.2f} "
                     f"(avg ${row['avg_income']:,.2f}, {row['transaction_count']} transactions)")
    lines.append("")
    
    # Expense breakdown
    lines += ["## 💸 Expense Breakdown", ""]
    for row in summary['expense']:
        lines.append(f"- **{row['category']}**: ${row['total_expense']:,.2f} "
                     f"(avg ${row['avg_expense']:,.2f}, {row['transaction_count']} transactions)")
    lines.append("")
    
    # Risk alerts
    lines += ["## ⚠️ Risk Alerts", ""]
    if m.get('profit_margin', 0) < 10:
        lines.append("- Profit margin below 10%, consider cost control")
    total_expense = m.get('total_expense', 0)
    total_income = m.get('total_income', 0)
    if total_expense > total_income * 1.1:
        lines.append("- Expenses exceed income by 10%, urgent review needed")
    if not lines[-1].startswith("-"):
        lines.append("- Financial health is good, keep it up")
    
    return "\n".join(lines)

6. Scheduling & Deployment

Use Python’s schedule library for automated scheduling:

import schedule
import time
from pathlib import Path

def daily_report():
    con = duckdb.connect(':memory:')
    # Load data
    con.execute("CREATE TABLE income AS SELECT * FROM read_csv_auto('data/income.csv');")
    con.execute("CREATE TABLE expense AS SELECT * FROM read_csv_auto('data/expense.csv');")
    con.execute("CREATE TABLE budget AS SELECT * FROM read_csv_auto('data/budget.csv');")
    
    now = datetime.now()
    summary = generate_financial_summary(con, now.year, now.month)
    report = render_report(summary, now.year, now.month)
    
    # Save report
    output_dir = Path('output')
    output_dir.mkdir(exist_ok=True)
    output_path = output_dir / f"report_{now.year}_{now.month:02d}.md"
    output_path.write_text(report, encoding='utf-8')
    
    print(f"Report generated: {output_path}")
    
    con.close()

# Execute on the 1st of each month at 2:00 AM
schedule.every().month.at("02:00").do(daily_report)

while True:
    schedule.run_pending()
    time.sleep(60)

Deployment options:

Deployment	Use Case	Cost
Cron + Python	Single machine, personal use	Free
Docker container	Multi-environment	Low
Cloud server cron	Production	$10-50/month
Serverless (AWS Lambda)	On-demand execution	Pay-per-use

7. Advanced: Connecting to Real Data Sources

Once the project is working, you can replace data sources:

# Connect to PostgreSQL financial system
con = duckdb.connect()
con.execute("ATTACH 'postgresql://user:***@host:5432/finance' AS pg (TYPE postgres);")
income_df = con.execute("SELECT * FROM pg.income WHERE date >= '2024-01-01'").df()

# Connect to multiple CSV/Excel files
all_data = duckdb.sql("""
    SELECT * FROM read_csv_auto('s3://bucket/income/*.csv', aws_access_key_id='...')
    UNION ALL
    SELECT * FROM read_csv_auto('s3://bucket/expense/*.csv', aws_access_key_id='...')
""").df()

# Query large Parquet datasets directly
big_query = con.sql("""
    SELECT category, SUM(amount) 
    FROM read_parquet('data/transactions/*.parquet')
    GROUP BY category
""").df()

Multi-Source Data Comparison

Data Source	DuckDB Support	Performance
CSV/Excel	`read_csv_auto()`	Auto type inference, second-level loading
Parquet	`read_parquet()`	Columnar storage, ultra-fast queries
PostgreSQL	`ATTACH ... (TYPE postgres)`	No data export needed, direct queries
S3/OSS	`read_csv_auto('s3://...')`	Direct cloud storage access
JSON	`read_json_auto()`	Automatic nested structure handling

8. Comparison with Traditional Solutions

Dimension	Traditional Excel	Traditional ETL	DuckDB Solution
Learning curve	Low	High	Medium (SQL basics)
Processing speed	Slow (lags at 10k rows)	Fast (needs setup)	Fast (billions in seconds)
Memory usage	High	Medium	Low (vectorized execution)
Code volume	No code	Hundreds of lines	Under 50 lines
Multi-source integration	Difficult	Requires development	One SQL line
Automation level	Manual	Needs scheduler	Built-in scheduling
Deployment cost	Low	High	Low
Maintainability	Poor	Medium	Good

9. Monetization Strategies

9.1 Personal Service Path

Target clients: 3-5 small businesses
Services: Monthly automated financial reports with data interpretation
Pricing: Basic $299/month, Premium $999/month
Advantage: No server costs, purely technology-driven

9.2 SaaS Product Path

Product form: Web application + automated reporting
Feature modules:
- Multi-client data isolation
- Custom report templates
- Email/WeChat notifications
- Historical data comparison
Pricing: $199/month per enterprise
Acquisition channels: Finance公众号, business communities

9.3 Template Sales Path

Product form: Pre-configured project templates + data generation scripts
Sales platforms: GitHub, Gumroad, Xiaohongshu
Pricing: One-time $99-299
Additional services: Paid consulting, custom development

9.4 Enterprise Consulting Path

Services: Help enterprises build complete data pipelines
Deliverables:
- Data integration solution
- Automated reporting system
- Training documentation
Pricing: $5000-20000 per project

10. Summary

This project leverages DuckDB’s core capabilities: direct CSV/Parquet querying, SQL aggregation analysis, and in-memory high-performance computing. The entire pipeline from data to report takes under 50 lines of code, yet it transforms work that used to take hours into seconds of automation.

The real barrier isn’t the code itself — it’s your understanding of financial scenarios: which metrics matter? Who needs to see the reports? What frequency is appropriate? These are what you can charge for.

📖 The complete project code, test data generation scripts, and advanced tutorials for connecting to PostgreSQL and S3 are published on duckdblab.org, including ready-to-run project templates.

💡 Want to systematically learn DuckDB’s practical applications in data products? duckdblab.org has a complete tutorial series from beginner to商业化, covering report generation, data APIs, and automated pipelines.

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

⚠️ This site is an independent community project, not affiliated with, endorsed by, or sponsored by the DuckDB Foundation or official DuckDB project.

"DuckDB" is a registered trademark of the DuckDB Foundation. This site uses the name solely for factual description purposes.

All content is for educational and community promotion purposes only and does not constitute any commercial service.