How to calculate the cumulative product of a rolling window in Pandas?

Question

I have a DataFrame, df with daily stock returns as such:

Date         Stock A    Stock B     Stock C
2018-12-26  -0.018207   0.083554   -0.006546
2018-12-27   0.004223   0.000698    0.003806
2018-12-28   0.024847  -0.008717    0.028399
2018-12-31   0.000000   0.010904    0.000000
2019-01-02   0.036554   0.002436    0.035557
2019-01-03   0.043541  -0.028462    0.006065
2019-01-04  -0.036207   0.070025    0.003025
2019-01-07  -0.005367   0.046411   -0.001546
2019-01-08   0.002878   0.014678    0.003631
2019-01-09   0.004663   0.014151    0.017179
2019-01-10   0.009282   0.026047    0.002062
2019-01-11   0.021224  -0.006649   -0.001578
2019-01-14   0.022168  -0.015211    0.008713
2019-01-15  -0.009827   0.020080   -0.004424
2019-01-16   0.021561  -0.016657    0.003583
2019-01-17   0.005025   0.011703    0.010149
2019-01-18   0.013333   0.012785    0.007824
2019-01-21   0.003289   0.000000   -0.000905
2019-01-22  -0.023934  -0.030658   -0.009447
2019-01-23   0.031911  -0.039690    0.015299
2019-01-24   0.030273   0.020665    0.011589
2019-01-25   0.000000   0.040810    0.000000
2019-01-28   0.018325   0.006991   -0.022861
2019-01-29  -0.021098  -0.044974    0.002043
2019-01-30  -0.002536   0.019595    0.014189
2019-01-31   0.000000   0.040298    0.004103
2019-02-01   0.014935  -0.011025    0.004795
2019-02-04   0.010332   0.022597    0.007439
2019-02-05   0.022002   0.012669   -0.002820
2019-02-06  -0.023651  -0.006110   -0.037381

How do I compute the cumulative returns in a rolling window on each stock?

For example, if the rolling window is of 5 days:

• The first element in the cumulative returns Series for Stock A should be (1 + df.loc["2018-12-26":"2019-01-02", "Stock A"]).cumprod() - 1 which computes to (1 + -0.018207)*(1 + 0.004223)*(1 + 0.024847)*(1 + 0.000000)*(1 + 0.036554) - 1 or 0.047372.
• The second element should be (1 + df.loc["2018-12-27":"2019-01-03", "Stock A"]).cumprod() - 1 which computes to (1 + 0.004223)*(1 + 0.024847)*(1 + 0.000000)*(1 + 0.036554)*(1 + 0.043541) - 1 or 0.113245.
• And so on...

Gaps in the Date index (like for weekends, for example) don't matter, the rolling window should only take into consideration the dates included in the index.

Regent

How do you define 'cumulative return'? How do you want to handle the gaps? (Presumably caused by weekends.) Are they included in the window size? – Tiphani

Possible dupe. – Tiphani

Thanks @DYZ, I added an example to illustrate what I'm trying to accomplish. – Regent

Answer 1

For some reason pandas rolling objects don't have a prod method, but you can apply NumPy prod to them. Also, you need to add 1 to your DataFrame and later subtract it, so the most straightforward one-liner approach would be

import numpy as np
...
cumulative_returns_df = (df+1).rolling(5).apply(np.prod)-1

Arguably, it's more computationally efficient and numerically stable to log-transform, calculate rolling sums and then reverse the transformation:

cumulative_returns_df = np.exp(np.log(df+1).rolling(5).sum())-1

Absence

How to apply the log transform on returns less than -1? – Subtenant

Stock price returns can't be less than -1: a return of -1 means that the price is exactly 0, and a return less than -1 results a negative price. Generally, you can't log transform negative numbers and a common technique to ensure all the values are positive is adding a constant to them. – Absence

is there a way to use cumprod() and divide the last element in the window by the first element in the window? that is more understandable to me than the exp/log method – Pasadena

Answer 2

Your question is not well defined but assuming the reference date is the first row index 2019-01-30, you can use df.pct_change(30).

Magnetics

Thanks, this will work if the DataFrame contains stock prices. My DataFrame, df, however, contains stock returns. How could I do it in this case? – Regent