Cumulative product for random walk

Calculate the cumulative product of daily returns to simulate a stock's price path (random walk). This is useful for financial modeling, backtesting trading strategies, and portfolio analysis where you need to compound returns over time.

Problem: Compound daily returns

You have a table with daily returns for a stock and want to calculate the cumulative price starting from an initial value (e.g., $100). Each day's price is calculated by multiplying the previous price by (1 + return).

For example, with these daily returns:

You want to calculate:

Solution: Use logarithms to convert multiplication to addition

Use the mathematical identity: exp(sum(ln(x))) = product(x)

This converts the cumulative product into a cumulative sum, which window functions handle naturally:

WITH ln_values AS (
    SELECT
        date,
        return,
        SUM(ln(1 + return)) OVER (ORDER BY date) AS ln_value
    FROM daily_returns
)
SELECT
    date,
    return,
    100 * exp(ln_value) AS stock_price
FROM ln_values;

This query:

1. Calculates ln(1 + return) for each day
2. Uses a cumulative SUM window function to add up the logarithms
3. Applies exp() to convert back to the product

How it works

The mathematical identity used here is:

product(1 + r₁, 1 + r₂, ..., 1 + rₙ) = exp(sum(ln(1 + r₁), ln(1 + r₂), ..., ln(1 + rₙ)))

Breaking it down:

• ln(1 + return) converts each multiplicative factor to an additive one
• SUM(...) OVER (ORDER BY date) creates a cumulative sum
• exp(ln_value) converts the cumulative sum back to a cumulative product
• Multiply by 100 to apply the starting price of $100

Adapting to your data

You can easily modify this pattern:

Different starting price:

SELECT date, return, 1000 * exp(ln_value) AS stock_price  -- Start at $1000
FROM ln_values;

Different time granularity:

-- For hourly returns
WITH ln_values AS (
    SELECT
        timestamp,
        return,
        SUM(ln(1 + return)) OVER (ORDER BY timestamp) AS ln_value
    FROM hourly_returns
)
SELECT timestamp, 100 * exp(ln_value) AS price FROM ln_values;

Multiple assets:

WITH ln_values AS (
    SELECT
        date,
        symbol,
        return,
        SUM(ln(1 + return)) OVER (PARTITION BY symbol ORDER BY date) AS ln_value
    FROM daily_returns
)
SELECT
    date,
    symbol,
    100 * exp(ln_value) AS stock_price
FROM ln_values;

:::tip Use Case: Monte Carlo Simulation This pattern is essential for Monte Carlo simulations in finance. Generate random returns, apply this cumulative product calculation, and run thousands of iterations to model possible future price paths. :::

:::info Related Documentation

• Window functions
• Mathematical functions
• SUM aggregate :::