CODEWITHSUNDEEP
pythonpandas
daveskis
daveskis

Reputation: 153

Python rolling period returns

I need to develop a rolling 6-month return on the following dataframe

date         Portfolio Performance
2001-11-30    1.048134
2001-12-31    1.040809
2002-01-31    1.054187
2002-02-28    1.039920
2002-03-29    1.073882
2002-04-30    1.100327
2002-05-31    1.094338
2002-06-28    1.019593
2002-07-31    1.094096
2002-08-30    1.054130
2002-09-30    1.024051
2002-10-31    0.992017

A lot of the answers from previous questions describe rolling average returns, which I can do. However, i am not looking for the average. What I need is the following example formula for a rolling 6-month return:

(1.100327 - 1.048134)/1.100327

The formula would then consider the next 6-month block between 2001-12-31 and 2002-05-31 and continue through to the end of the dataframe.

I've tried the following, but doesn't provide the right answer.

portfolio['rolling'] = portfolio['Portfolio Performance'].rolling(window=6).apply(np.prod) - 1

Expected output would be:

date         Portfolio Performance  Rolling
2001-11-30    1.048134              NaN
2001-12-31    1.040809              NaN
2002-01-31    1.054187              NaN
2002-02-28    1.039920              NaN
2002-03-29    1.073882              NaN
2002-04-30    1.100327              0.0520
2002-05-31    1.094338              0.0422    
2002-06-28    1.019593             -0.0280

The current output is:

            Portfolio Performance   rolling
date                                       
2001-11-30               1.048134       NaN
2001-12-31               1.040809       NaN
2002-01-31               1.054187       NaN
2002-02-28               1.039920       NaN
2002-03-29               1.073882       NaN
2002-04-30               1.100327  0.413135
2002-05-31               1.094338  0.475429
2002-06-28               1.019593  0.445354
2002-07-31               1.094096  0.500072
2002-08-30               1.054130  0.520569
2002-09-30               1.024051  0.450011
2002-10-31               0.992017  0.307280

Upvotes: 3

Views: 1972

Answers (1)

r-beginners
r-beginners

Reputation: 35135

I simply added the columns shifted 6 months and ran the formula presented. Does this meet the intent of the question?

df['before_6m'] = df['Portfolio Performance'].shift(6)
df['rolling'] = (df['Portfolio Performance'] - df['before_6m'])/df['Portfolio Performance']
df
|    | date                |   Portfolio Performance |   before_6m |     rolling |
|---:|:--------------------|------------------------:|------------:|------------:|
|  0 | 2001-11-30 00:00:00 |                1.04813  |   nan       | nan         |
|  1 | 2001-12-31 00:00:00 |                1.04081  |   nan       | nan         |
|  2 | 2002-01-31 00:00:00 |                1.05419  |   nan       | nan         |
|  3 | 2002-02-28 00:00:00 |                1.03992  |   nan       | nan         |
|  4 | 2002-03-29 00:00:00 |                1.07388  |   nan       | nan         |
|  5 | 2002-04-30 00:00:00 |                1.10033  |   nan       | nan         |
|  6 | 2002-05-31 00:00:00 |                1.09434  |     1.04813 |   0.042221  |
|  7 | 2002-06-28 00:00:00 |                1.01959  |     1.04081 |  -0.0208083 |
|  8 | 2002-07-31 00:00:00 |                1.0941   |     1.05419 |   0.0364767 |
|  9 | 2002-08-30 00:00:00 |                1.05413  |     1.03992 |   0.0134803 |
| 10 | 2002-09-30 00:00:00 |                1.02405  |     1.07388 |  -0.0486607 |
| 11 | 2002-10-31 00:00:00 |                0.992017 |     1.10033 |  -0.109182  |

Upvotes: 3

Related Questions