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:
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.(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.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.
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