CODEWITHSUNDEEP
pythonpandasdataframe
fjurt
fjurt

Reputation: 793

Calculate cumulative return for a defined window from predefined start day in Python

I try to calculate cumulative returns over for example 3 days and assign the end value to a new DataFrame cumulative_return. To illustrate that I created this example:

returns:
            01K     02K     03K     04K
Dates               
2022-01-01  1.02    1.05    1.02    1.01
2022-01-02  0.97    0.99    1.02    1.06
2022-01-03  1.03    1.07    0.98    1.02
2022-01-04  0.96    1.02    1.03    0.98
2022-01-05  1.02    1.02    1.09    1.03
2022-01-06  1.06    0.95    0.96    0.99

start:
            01K 02K 03K 04K
Dates               
2022-01-01  1   0   0   0
2022-01-02  0   1   0   0
2022-01-03  0   0   0   0
2021-01-04  1   0   1   0
2021-01-05  0   0   1   0
2021-01-06  0   0   0   1

cumulative_returns:
            01K     02K     03K.    04K
Dates               
2022-01-01  1.019   0.00    0.000   0.0
2022-01-02  0.000   1.08    0.000   0.0
2022-01-03  0.000   0.00    0.000   0.0
2022-01-04  1.038   0.00    1.078   0.0
2022-01-05  0.000   0.00    NaN     0.0
2021-01-06  0.000   0.00    0.000   NaN

An example for the cumulative_return calculation over 3 days:

So far I was only able to calculate the cumulative return by individually shifting the returns, which is not efficient for longer time intervals.

returns.shift(-2) * returns.shift(-1) * returns * start

For reproducibility:

import pandas as pd
import numpy as np

returns = pd.DataFrame({
    'Dates':['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04', '2022-01-05', '2022-01-06'],
    '01K':[0.02, -0.03, 0.03, -0.04, 0.02, 0.06], 
    '02K':[0.05, -0.01, 0.07, 0.02, 0.02, -0.05], 
    '03K':[0.02, 0.02, -0.02, 0.03, 0.09, -0.04], 
    '04K':[0.01, 0.06, 0.02, -0.02, 0.03, -0.01]}) 
returns = returns.set_index('Dates')
returns = returns + 1

start = pd.DataFrame({
    'Dates':['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04', '2022-01-05', '2022-01-06'],
    '01K':[1, 0, 0, 1, 0, 0], 
    '02K':[0, 1, 0, 0, 0, 0], 
    '03K':[0, 0, 0, 1, 1, 0], 
    '04K':[0, 0, 0, 0, 0, 1]}) 
start = start.set_index('Dates')

Upvotes: 0

Views: 310

Answers (1)

David
David

Reputation: 891

you can leverage pandas rolling function with numpy product function to get the following:

n=3
returns.rolling(n).apply(np.prod).shift(-n+1)

output:

                 01K       02K       03K       04K
Dates                                             
2022-01-01  1.019082  1.112265  1.019592  1.092012
2022-01-02  0.959136  1.080486  1.029588  1.059576
2022-01-03  1.008576  1.113228  1.100246  1.029588
2022-01-04  1.037952  0.988380  1.077792  0.999306
2022-01-05       NaN       NaN       NaN       NaN
2022-01-06       NaN       NaN       NaN       NaN

Upvotes: 1

Related Questions