Portfolio Analysis and Correlation

A portfolio is a collection of investments held together. Portfolio analysis goes beyond looking at individual stocks — it examines how stocks interact with each other and whether combining them reduces overall risk. The key insight of modern portfolio theory is that combining stocks with low correlation to each other reduces portfolio volatility without necessarily reducing returns.

This lesson computes portfolio returns, correlations between stocks, and sector-level performance — the foundation of portfolio management.

Why Correlation Matters

If you own 10 stocks that all move together (correlation of 1.0), your portfolio is essentially one bet. When the market drops, all 10 fall simultaneously. But if you own stocks from different sectors with low or negative correlations, some may rise when others fall — smoothing out the portfolio's overall volatility.

Correlation ranges:

• 1.0: Perfect positive correlation — move in lockstep
• 0: No relationship — independent movements
• -1.0: Perfect negative correlation — one rises when other falls (gold vs. growth stocks, sometimes)

SQL's CORR() function computes Pearson correlation — exactly what's needed for portfolio analysis.

Step 1 — Portfolio Total Return

This query computes the return for a hypothetical equal-weight portfolio versus each individual stock. An equal-weight portfolio puts the same dollar amount in each stock, rebalancing daily.

WITH daily_returns AS (
    SELECT company_id, price_date,
        (close_price - LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date))
        / NULLIF(LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date), 0) AS daily_return
    FROM stock_prices
),
portfolio_daily AS (
    SELECT price_date,
        AVG(daily_return) AS portfolio_return,  -- equal-weight average
        COUNT(DISTINCT company_id) AS stocks_in_portfolio
    FROM daily_returns
    WHERE daily_return IS NOT NULL
    GROUP BY price_date
),
cumulative_portfolio AS (
    SELECT price_date,
        ROUND(((EXP(SUM(LN(1 + portfolio_return)) OVER (ORDER BY price_date)) - 1) * 100)::NUMERIC, 2) AS cumulative_return_pct,
        ROUND((STDDEV(portfolio_return) OVER (ORDER BY price_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) * SQRT(252) * 100)::NUMERIC, 2) AS rolling_vol
    FROM portfolio_daily
)
SELECT * FROM cumulative_portfolio ORDER BY price_date;

What This Returns

cumulative_return_pct shows the portfolio's total growth since inception. rolling_vol shows the portfolio's 20-day annualised volatility — typically lower than individual stock volatility because diversification reduces unsystematic risk.