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 |
I know QuestDB doesn’t have a direct cumulative product function, and I found this 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.
Problem is that QuestDB does not allow to use any functions on top of a Window Function result, so when I do:
SELECT
date,
return,
exp(sum(ln(1 + return)) OVER (ORDER BY date)) AS StockPrice
FROM daily_returns
I get an error “dangling literal”.
Any workarounds here or cannot cumulative product can be calculated using QuestDB?
3
Even if we cannot use exp on top of a window function, it doesn’t mean we cannot still use a cumulative SUM on a CTE and then use exp for every row.
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;
And this is the outcome