Why This Project?
Many data analysts struggle with the same repetitive task every month: pulling financial data from multiple sources, generating reports manually, and hoping nothing breaks. The pain is real — and it’s also a business opportunity.
With DuckDB, you can build a reusable automated financial analysis system in 10 minutes that becomes your standard deliverable for every client. Deploy it as a SaaS, and you’ve got a product that earns while you sleep.
Step 1: Set Up Multi-Source Data
We’ll assume a company’s financial data is scattered across CSV files. DuckDB’s read_csv_auto infers the schema automatically — no manual column definitions needed:
import duckdb
from duckdb import connect
# In-memory database for blazing-fast queries
con = connect(":memory:")
# Three tables: revenue, costs, budget
con.execute("""
CREATE TABLE revenue AS
SELECT date, product, amount, region
FROM read_csv_auto('revenue_2024.csv')
WHERE date >= '2024-01-01';
CREATE TABLE costs AS
SELECT date, category, amount, dept
FROM read_csv_auto('costs_2024.csv')
WHERE date >= '2024-01-01';
CREATE TABLE budget AS
SELECT dept, year, quarter, allocated
FROM read_csv_auto('budget_2024.csv');
""")
💡 In production, data usually lives in PostgreSQL or MySQL. Use
postgres_scan/mysql_scanto query directly — no ETL pipeline needed:con.execute(""" CREATE VIEW financial_data AS SELECT * FROM postgres_scan( 'dbname=finance host=localhost user=xxx password=xxx' ) """)
Step 2: Core Analysis Queries
2.1 Monthly Revenue Trend (with MoM Growth)
Month-over-Month (MoM) growth is the metric every boss cares about — how much did we grow compared to last month?
monthly_revenue = con.execute("""
SELECT
date_trunc('month', date) AS month,
product,
SUM(amount) AS total_revenue,
LAG(SUM(amount)) OVER (
PARTITION BY product
ORDER BY date_trunc('month', date)
) AS prev_month_revenue,
ROUND(
100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (
PARTITION BY product ORDER BY date_trunc('month', date)
)) / NULLIF(LAG(SUM(amount)) OVER (
PARTITION BY product ORDER BY date_trunc('month', date)
), 0),
2
) AS mom_pct
FROM revenue
GROUP BY month, product
ORDER BY month, product
""").df()
Key points:
date_trunc('month', date)is a DuckDB core function — 10x faster than pandas’dt.to_period, and it runs entirely in SQL without pulling data into PythonLAG()window function gets the previous month’s value, withPARTITION BY productensuring independent MoM calculation per productNULLIF(..., 0)prevents division-by-zero errors
2.2 Budget Execution Rate (with Alert Logic)
budget_actual = con.execute("""
SELECT
b.dept,
b.year,
b.quarter,
b.allocated AS budget,
COALESCE(c.spent, 0) AS actual_spent,
ROUND(
100.0 * COALESCE(c.spent, 0) / b.allocated, 1
) AS spend_pct,
CASE
WHEN COALESCE(c.spent, 0) / b.allocated > 0.9 THEN '🔴 Over Budget'
WHEN COALESCE(c.spent, 0) / b.allocated > 0.8 THEN '🟡 Near Limit'
ELSE '🟢 Normal'
END AS status
FROM budget b
LEFT JOIN (
SELECT dept, year, quarter, SUM(amount) AS spent
FROM costs
GROUP BY dept, year, quarter
) c USING (dept, year, quarter)
ORDER BY spend_pct DESC
""").df()
This query directly produces the “budget execution alert table” that bosses love — zero Python loops, all SQL. The three-tier CASE WHEN alert logic is reusable for any monitoring scenario.
Step 3: Export Formatted Reports
3.1 Export to Excel (with Styling)
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
# Export both tables
monthly_revenue.to_excel('monthly_report.xlsx', sheet_name='Revenue Trend', index=False)
budget_actual.to_excel('monthly_report.xlsx', sheet_name='Budget Execution', index=False)
# Format headers
wb = load_workbook('monthly_report.xlsx')
for sheet_name in ['Revenue Trend', 'Budget Execution']:
ws = wb[sheet_name]
ws.freeze_panes = 'A2' # Freeze header row
for cell in ws[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill('solid', fgColor='4472C4') # Dark blue
cell.alignment = Alignment(horizontal='center')
wb.save('monthly_report.xlsx')
3.2 One-Click Package for Delivery
import zipfile
from datetime import datetime
filename = f"financial_report_{datetime.now().strftime('%Y%m%d')}.zip"
with zipfile.ZipFile(filename, 'w') as z:
z.write('monthly_report.xlsx')
z.writestr('README.txt', f'''
Financial Report Generated: {datetime.now()}
Data Range: 2024 Q1-Q3
Tool: DuckDB + Python
Note: Re-run gen_report.py to regenerate
''')
print(f"✅ Report generated: {filename}")
Step 4: Automation & API
4.1 Cron Job (Monthly Auto-Generation)
# Auto-generate report on the 1st of every month at 2 AM
0 2 1 * * cd /path/to/project && python3 gen_report.py
4.2 FastAPI Service (On-Demand Access)
from fastapi import FastAPI
from fastapi.responses import FileResponse
import subprocess
from datetime import datetime
app = FastAPI()
@app.get("/report")
def get_report():
subprocess.run(["python3", "gen_report.py"], check=True)
return FileResponse(
f"financial_report_{datetime.now().strftime('%Y%m%d')}.zip",
media_type='application/zip'
)
@app.get("/revenue/trend")
def get_revenue_trend():
con = connect(":memory:")
con.execute("""
CREATE TABLE revenue AS
SELECT * FROM read_csv_auto('revenue_2024.csv');
""")
result = con.execute("""
SELECT date_trunc('month', date) AS month,
product, SUM(amount) AS total
FROM revenue GROUP BY month, product
ORDER BY month
""").df().to_dict('records')
return result
Comparison: Traditional vs DuckDB
| Dimension | Traditional (Excel + Python Loops) | DuckDB |
|---|---|---|
| Monthly report time | 2-4 hours | 5-10 minutes |
| Adding a data source | Modify code every time | read_csv_auto('*.csv') auto-merges |
| MoM calculation | Nested loops, error-prone | One SQL LAG() window function |
| Budget alerts | Python loop with conditionals | SQL CASE WHEN in one line |
| Reusability | Rewrite per client | Swap data files, logic stays |
| Accuracy | Manual errors possible | SQL is idempotent, always consistent |
💰 Monetization: What Is This Worth?
| Scenario | Price |
|---|---|
| Monthly financial reports for one SME | ¥2,000-5,000/month |
| SaaS with 10 clients | ¥20,000-50,000/month |
| Custom development (incl. system design) | ¥15,000-30,000/project |
The core logic: You’re not selling “SQL queries” — you’re selling “monthly reports that make your boss happy.” Clients pay for results, not tools.
Advanced: Production Optimizations
Persistent Storage (No Need to Re-read CSVs)
# First run: build tables from CSV and persist to .duckdb file
con = duckdb.connect("finance.db")
con.execute("CREATE TABLE revenue AS SELECT * FROM read_csv_auto('revenue_2024.csv')")
con.execute("CREATE TABLE costs AS SELECT * FROM read_csv_auto('costs_2024.csv')")
con.execute("CREATE TABLE budget AS SELECT * FROM read_csv_auto('budget_2024.csv')")
con.close()
# Subsequent runs: open the database file directly, instant response
con = duckdb.connect("finance.db")
# Query logic is identical — data is already on disk
Incremental Updates (Only Process New Data)
con.execute("""
-- Only import new revenue records for this month
INSERT INTO revenue
SELECT * FROM read_csv_auto('revenue_2024_08.csv')
WHERE date >= '2024-08-01'
AND date NOT IN (SELECT date FROM revenue);
""")
Monetization Paths Summary
- Freelance gigs: List “financial automation reports” service on platforms like Zhubajie or Upwork, priced at ¥5,000-15,000/project
- SaaS subscription: Deploy to cloud, charge ¥500-2,000/month per client
- Template sales: Package the code as a configurable template, sell on Gumroad for ¥99-299
- Corporate training: Teach SMEs DuckDB automation reporting, ¥3,000-8,000/session
- Knowledge product: Compile your experience into paid courses on platforms like Xiaobiaotou or Knowledge Planet
💡 More DuckDB practical tutorials → duckdblab.org