Cumulative product for a random walk

Community
,
1

I have a dataset in QuestDB with daily returns for a stock, starting at $100. I want to calculate the cumulative product of the returns to simulate the stock’s price path (random walk). The daily returns are stored in a table called daily_returns.

Here’s a sample of the relevant columns:

Date Daily Return (%)
2024-09-05 2.00
2024-09-06 -1.00
2024-09-07 1.50
2024-09-08 -3.00
2024-09-09 2.50
2024-09-10 1.00
2024-09-11 -2.00
2024-09-12 1.50

I’m trying to calculate the cumulative product of (1 + return) for each day, starting from $100. This would be the expected output

Date Daily Return (%) Stock Price
2024-09-05 2.00 102.00
2024-09-06 -1.00 100.98
2024-09-07 1.50 102.49
2024-09-08 -3.00 99.42
2024-09-09 2.50 101.91
2024-09-10 1.00 102.93
2024-09-11 -2.00 100.87
2024-09-12 1.50 102.38
2

QuestDB doesn’t have a direct cumulative product function, but as you can see, there is a nice trick (maths to the rescue!) where you can use a window function and get the exponent of the sum of the logarithms for each day, and that would be the same as a cumulative product.

The (it does not work, but keep reading) SQL would be:

 SELECT 
        date,
        return,
        exp(sum(ln(1 + return)) OVER (ORDER BY date)) AS StockPrice
    FROM daily_returns

Problem is that QuestDB does not allow to use any functions on top of a Window Function result, so you need to do that in a query with two steps.

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 "StockPrice"
FROM ln_values;

Which results in the expected outcome

image