Build an Automated Financial Report Analyzer with DuckDB: From Raw CSV to Investment Reports in One Click
Many finance professionals and quantitative enthusiasts repeat the same routine every day: open 10+ Excel files, manually cross-check numbers, write a handful of VLOOKUP formulas, and finally piece together a financial report. This process takes 2-3 hours and is prone to errors.
Today, we’ll automate the entire workflow using DuckDB — from raw financial data to a shareable analysis report — all in a single SQL query. Time drops from 2 hours to 30 seconds.
1. Use Case Definition: What Do You Need to Analyze?
Assume you have the following raw data files (in CSV format):
income_statement/*.csv: Income statements (date, stock code, revenue, net profit, gross margin, etc.)balance_sheet/*.csv: Balance sheets (date, stock code, total assets, total liabilities, equity, etc.)stock_quotes/*.csv: Daily market data (date, stock code, closing price, volume, etc.)
The goal output: key quarterly metrics per stock + YoY/QoQ changes + simple valuation signals.
The traditional approach requires writing a dozen lines of Excel or Python pandas code. With DuckDB, you can do “where the data is, that’s where the SQL runs” — no need to load all files into memory first; you can execute analysis directly against files on disk.
2. Core Code: One SQL Query to Handle the Full Pipeline
import duckdb
from datetime import datetime
# Connect to DuckDB (in-memory mode, or switch to a persistent database)
con = duckdb.connect(":memory:")
# 1. Read CSV files directly (supports glob, reads multiple files at once)
con.execute("""
CREATE TABLE income AS
SELECT * FROM read_csv_auto('data/income_statement/*.csv');
CREATE TABLE balance AS
SELECT * FROM read_csv_auto('data/balance_sheet/*.csv');
CREATE TABLE quotes AS
SELECT * FROM read_csv_auto('data/stock_quotes/*.csv');
""")
# 2. One SQL query outputs the quarterly financial report
report_sql = """
WITH quarterly AS (
SELECT
i.code,
STRFTIME(i.date, '%Y-%q') AS quarter,
AVG(i.revenue) AS revenue,
AVG(i.net_profit) AS net_profit,
AVG(i.gross_margin) AS gross_margin,
b.total_assets,
b.total_liabilities,
b.equity
FROM income i
LEFT JOIN balance b
ON i.code = b.code
AND STRFTIME(i.date, '%Y-%m') = STRFTIME(b.date, '%Y-%m')
GROUP BY i.code, quarter, b.total_assets, b.total_liabilities, b.equity
),
with_growth AS (
SELECT
*,
LAG(revenue) OVER (PARTITION BY code ORDER BY quarter) AS rev_lag1,
LAG(net_profit) OVER (PARTITION BY code ORDER BY quarter) AS profit_lag1,
LAG(gross_margin) OVER (PARTITION BY code ORDER BY quarter) AS margin_lag1,
LAG(revenue) OVER (PARTITION BY code ORDER BY quarter) AS rev_lag4,
LAG(net_profit) OVER (PARTITION BY code ORDER BY quarter) AS profit_lag4
FROM quarterly
)
SELECT
code,
quarter,
ROUND(revenue / 1e8, 2) AS revenue_100M,
ROUND(net_profit / 1e8, 2) AS net_profit_100M,
ROUND(gross_margin * 100, 1) AS gross_margin_pct,
ROUND((revenue - rev_lag1) / NULLIF(ABS(rev_lag1), 0) * 100, 1) AS qoq_rev,
ROUND((net_profit - profit_lag1) / NULLIF(ABS(profit_lag1), 0) * 100, 1) AS qoq_profit,
ROUND((revenue - rev_lag4) / NULLIF(ABS(rev_lag4), 0) * 100, 1) AS yoy_rev,
ROUND((net_profit - profit_lag4) / NULLIF(ABS(profit_lag4), 0) * 100, 1) AS yoy_profit,
ROUND(equity / NULLIF(total_assets, 0) * 100, 1) AS equity_ratio,
ROUND(net_profit / NULLIF(equity, 0) * 100, 1) AS roe_pct
FROM with_growth
ORDER BY code, quarter DESC;
"""
result = con.execute(report_sql).fetchdf()
print(result.to_string(index=False))
# 3. Export to Excel report with one click
result.to_excel("financial_report_auto.xlsx", index=False, engine='openpyxl')
print("✅ Report saved")
3. Deep Dive into Key Technical Points
3.1 read_csv_auto — Zero-Config Reading, Leaving pandas Behind
DuckDB’s read_csv_auto is truly “works out of the box.” It automatically infers the data type of every column (integer, float, date, string) and supports glob patterns to read all matching files in a directory at once.
Compared to the traditional pandas approach:
# pandas requires manual looping, type inference, and merging
import pandas as pd
import glob
files = glob.glob('data/income_statement/*.csv')
dfs = [pd.read_csv(f) for f in files]
df = pd.concat(dfs, ignore_index=True)
DuckDB needs just one line:
SELECT * FROM read_csv_auto('data/income_statement/*.csv');
When files reach hundreds of MB or even several GB, DuckDB’s columnar storage and vectorized execution shine — pandas may OOM, while DuckDB runs smoothly.
3.2 CTE + Window Functions — Replacing Every VLOOKUP
In a traditional Excel workflow, calculating quarter-over-quarter growth requires:
- Using VLOOKUP to find last quarter’s data
- Writing manual
(current - prior) / priorformulas - Copying formulas to every row
DuckDB uses CTEs (Common Table Expressions) + LAG() window functions in one step:
LAG(revenue) OVER (PARTITION BY code ORDER BY quarter) AS rev_lag1
LAG(revenue, 1) fetches the revenue value from the row before the current one. Paired with PARTITION BY code ORDER BY quarter, it precisely retrieves “last quarter’s” data per stock.
Similarly, LAG(revenue, 4) gets data from the same quarter last year (year-over-year).
NULLIF(ABS(...), 0) prevents division-by-zero errors — when the base period is 0, it returns NULL, preventing the entire calculation from breaking.
3.3 Chained Pipeline, Zero-Copy Memory
All intermediate CTEs in DuckDB are virtual views that don’t consume extra memory. Only when you call fetchdf() are the results materialized into a pandas DataFrame. This means you can write 10 levels of CTEs in a single SQL query without intermediate data being copied around in memory.
4. Advanced: Integrate Market Data for Investment Signals
Building on the basic financial analysis, add real-time quotes for simple valuation and signal generation:
enrich_sql = """
WITH base AS (
-- Reuse the quarterly + with_growth logic above
SELECT
code, quarter, revenue_100M, net_profit_100M,
qoq_rev, qoq_profit, yoy_rev, yoy_profit,
roe_pct
FROM (/* the full report_sql above */ sub)
),
latest_quote AS (
SELECT code, close_price
FROM quotes
WHERE date = (SELECT MAX(date) FROM quotes)
)
SELECT
b.*,
q.close_price,
CASE
WHEN b.roe_pct > 15 AND b.yoy_profit > 10 THEN '🟢 Recommended'
WHEN b.roe_pct > 10 AND b.yoy_profit > 0 THEN '🟡 Watch'
ELSE '🔴 Caution'
END AS signal
FROM base b
JOIN latest_quote q ON b.code = q.code
ORDER BY b.code, b.quarter DESC;
"""
This SQL takes the latest closing price, JOINs it with the financial data, and assigns a simple signal label based on ROE and net profit YoY growth.
5. Comparison with Traditional Tools
Excel approach:
- Manual formula per stock, 10 stocks = 10 sheets
- Data updates require reopening and refreshing
- Error-prone, version management is chaotic
pandas approach:
- 3-5x more lines of code
- High memory pressure with large files
- Manual glob handling, type inference, and null management required
DuckDB approach:
- One SQL query handles the entire pipeline
- Columnar storage, smooth handling of GB-scale data
- Results go directly into pandas via
fetchdf() - Seamlessly integrates with Streamlit / FastAPI applications
6. Real-World Monetization Value
This script can:
- Run weekly on schedule — Replace your manual report compilation, compressing 2 hours of weekly reporting into 30 seconds
- Embed as a data product in your investment platform — Become a differentiated feature for your team, increasing your irreplaceability
- Side-hustle starting point — Package this logic as a standard financial report SaaS and charge small institutions or independent investors (¥99-299/month)
- Quant strategy data source — Use as your factor library, providing clean financial data for quantitative backtesting
Start by preparing your CSV data and running it locally in Jupyter, then consider deploying to a scheduled task.
Learn more DuckDB practical experience → duckdblab.org