SQL for Financial Data Analysis

Why SQL for Finance?

Every financial system runs on a database, and SQL is how you talk to databases. Whether you are querying trade records, building reports, or analysing customer transactions, SQL is non-negotiable. The good news: you only need about 10 commands to handle 95% of financial analysis tasks.

We will use SQLite because it needs zero setup - it is built into Python. The SQL syntax is identical to PostgreSQL, MySQL, and SQL Server for everything we cover here.

Setting Up the Database

First, let us create a realistic financial database with three tables: stocks (the companies), transactions (buy and sell records), and daily_prices (historical market data). This mirrors what you would find at any trading desk or fintech company.

import sqlite3
import random
from datetime import datetime, timedelta

# Create an in-memory database
conn = sqlite3.connect('finance.db')
cursor = conn.cursor()

# Create tables
cursor.executescript('''
CREATE TABLE IF NOT EXISTS stocks (
    id INTEGER PRIMARY KEY,
    symbol TEXT NOT NULL,
    name TEXT NOT NULL,
    sector TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY,
    stock_id INTEGER,
    type TEXT CHECK(type IN ('BUY', 'SELL')),
    quantity INTEGER NOT NULL,
    price REAL NOT NULL,
    date TEXT NOT NULL,
    FOREIGN KEY (stock_id) REFERENCES stocks(id)
);

CREATE TABLE IF NOT EXISTS daily_prices (
    id INTEGER PRIMARY KEY,
    stock_id INTEGER,
    date TEXT NOT NULL,
    open_price REAL,
    close_price REAL,
    high REAL,
    low REAL,
    volume INTEGER,
    FOREIGN KEY (stock_id) REFERENCES stocks(id)
);
'''
)

# Insert sample stocks
stocks = [
    (1, 'AAPL', 'Apple Inc', 'Technology'),
    (2, 'MSFT', 'Microsoft', 'Technology'),
    (3, 'JPM', 'JPMorgan Chase', 'Finance'),
    (4, 'JNJ', 'Johnson and Johnson', 'Healthcare'),
    (5, 'XOM', 'ExxonMobil', 'Energy'),
]
cursor.executemany(
    'INSERT OR IGNORE INTO stocks VALUES (?, ?, ?, ?)', stocks
)
conn.commit()
print('Database ready with sample data')

SELECT and WHERE: Filtering Data

Every SQL query starts with SELECT. You choose which columns you want and use WHERE to filter rows. ORDER BY sorts the results. This is your bread and butter.

-- Basic SELECT: get all stocks in the Technology sector
SELECT symbol, name
FROM stocks
WHERE sector = 'Technology'
ORDER BY name;

-- In Python:
cursor.execute("""
    SELECT symbol, name
    FROM stocks
    WHERE sector = 'Technology'
    ORDER BY name
""")
for row in cursor.fetchall():
    print(f'{row[0]:6s} {row[1]}')

GROUP BY and Aggregates: Portfolio Analysis

GROUP BY combined with aggregate functions (SUM, AVG, COUNT, MIN, MAX) is where SQL becomes incredibly powerful for finance. You can summarise thousands of transactions into meaningful insights with a single query.

-- Total spent per stock (BUY transactions only)
SELECT
    s.symbol,
    s.name,
    SUM(t.quantity) AS total_shares,
    SUM(t.quantity * t.price) AS total_invested,
    ROUND(AVG(t.price), 2) AS avg_buy_price
FROM transactions t
JOIN stocks s ON t.stock_id = s.id
WHERE t.type = 'BUY'
GROUP BY s.symbol, s.name
ORDER BY total_invested DESC;

Monthly Profit and Loss

CASE expressions let you apply conditional logic inside queries. This is essential for P&L calculations where buys are cash outflows and sells are inflows. Combined with strftime for date grouping, you get a clean monthly cashflow report.

-- Monthly profit and loss
SELECT
    strftime('%Y-%m', t.date) AS month,
    SUM(CASE
        WHEN t.type = 'SELL' THEN t.quantity * t.price
        WHEN t.type = 'BUY' THEN -t.quantity * t.price
    END) AS net_cashflow,
    SUM(CASE WHEN t.type = 'BUY' THEN 1 ELSE 0 END) AS buys,
    SUM(CASE WHEN t.type = 'SELL' THEN 1 ELSE 0 END) AS sells
FROM transactions t
GROUP BY month
ORDER BY month;

JOINs: Finding Top Performers

JOINs connect tables together. This query joins stocks, transactions, and daily_prices to calculate the percentage return for each holding - comparing your average buy price against the current market price.

-- Find the best performing stock this month
-- Compare latest close price to average buy price
SELECT
    s.symbol,
    s.sector,
    ROUND(AVG(t.price), 2) AS avg_buy_price,
    dp.close_price AS current_price,
    ROUND(
        ((dp.close_price - AVG(t.price)) / AVG(t.price)) * 100,
        2
    ) AS return_pct
FROM stocks s
JOIN transactions t ON s.id = t.stock_id AND t.type = 'BUY'
JOIN daily_prices dp ON s.id = dp.stock_id
WHERE dp.date = (SELECT MAX(date) FROM daily_prices)
GROUP BY s.symbol
ORDER BY return_pct DESC;

SQL + pandas: The Killer Combo

The real power comes when you combine SQL with pandas. Use SQL to filter, join, and aggregate data in the database (where it is fast), then load the result into a DataFrame for visualisation and further analysis.

import sqlite3
import pandas as pd

# Connect to the database
conn = sqlite3.connect('finance.db')

# Run a query and load directly into pandas
query = '''
SELECT
    s.symbol,
    s.sector,
    COUNT(t.id) AS num_trades,
    SUM(t.quantity) AS total_shares,
    ROUND(SUM(t.quantity * t.price), 2) AS total_value
FROM transactions t
JOIN stocks s ON t.stock_id = s.id
WHERE t.type = 'BUY'
GROUP BY s.symbol, s.sector
ORDER BY total_value DESC
'''

df = pd.read_sql_query(query, conn)
print(df.to_string(index=False))

# Sector breakdown
sector_summary = df.groupby('sector')['total_value'].sum()
print('\nBy sector:')
print(sector_summary.to_string())

conn.close()