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:

DateDaily Return (%)
2024-09-052.00
2024-09-06-1.00
2024-09-071.50
2024-09-08-3.00

You want to calculate:

DateDaily Return (%)Stock Price
2024-09-052.00102.00
2024-09-06-1.00100.98
2024-09-071.50102.49
2024-09-08-3.0099.42

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:

Calculate cumulative product via logarithms
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;
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.

Related Documentation