4

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.

mpjan
  • 1,790
  • 5
  • 18
  • 21
  • 1
    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? – DYZ Feb 06 '19 at 23:51
  • [Possible dupe](https://stackoverflow.com/questions/15295434/how-to-calculate-rolling-cumulative-product-on-pandas-dataframe). – DYZ Feb 06 '19 at 23:54
  • Thanks @DYZ, I added an example to illustrate what I'm trying to accomplish. – mpjan Feb 07 '19 at 15:01

2 Answers2

10

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
Edward Khachatryan
  • 459
  • 4
  • 15
  • How to apply the log transform on returns less than -1? – nocibambi May 27 '19 at 19:49
  • 2
    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. – Edward Khachatryan May 31 '19 at 16:27
  • 1
    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 – Jayen Nov 22 '21 at 07:34
1

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).

mbass
  • 95
  • 2
  • 10
  • 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? – mpjan Feb 07 '19 at 15:06