CODEWITHSUNDEEP
pandasfinancequantitative-finance
Nicholas Abrams
Nicholas Abrams

Reputation: 11

Cumulative running returns using pandas

I am doing a Lagged cumulative return based on 6 months of monthly returns. So this is a running total. I know how to do it in Excel. Image below. How do I do this in Pandas?

enter image description here

Upvotes: 1

Views: 1613

Answers (2)

mxhet
mxhet

Reputation: 113

Assuming that the data is stored in a pandas DataFrame called 'df', your column '% Returns' would be generated with:

df['perc_Returns'] = df['VIXCLS'].pct_change()

the fourth column in your excel file is produced with:

df['Cumulative_Returns'] = 1000 * (1 + df['perc_Returns']).cumprod()

and the column 'Cumulative Returns Lagged' is calculated using a rolling cumulative product as follows:

To be very specific, you can do it in two steps.

First, calculate the 6-month rolling cumulative returns:

df['rolling_rets_6'] = (1 + df['perc_Returns']).rolling(window = 6).apply(np.prod, raw = True) - 1

and then calculate the cumulative value with an initial investment of $1000:

df['cumulative_value'] = 1000 * (1 + df['rolling_rets_6'])

Upvotes: 2

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4263

calculate the percent change then create a list of percent change + 1 then multiple the accumulative product (multiplication of the previous rows 1+pct_change aggregation) then multiple it by the amount for the running total

data=[17.24,18.19,19.22,20.11,20.26,22.2,22.44,20.05]
df=pd.DataFrame({'VIXCLS':data})
df['Percent_Change']=df['VIXCLS'].pct_change().fillna(0)

amount=1000

returns_plus_one=df['Percent_Change']+1
cumulative_return = returns_plus_one.cumprod()

df['Cumulative']=cumulative_return.mul(amount)

print(df)

df['2item']=df['Cumulative'].rolling(window=2).mean().plot()

output:

VIXCLS  Percent_Change   Cumulative
0   17.24        0.000000  1000.000000
1   18.19        0.055104  1055.104408
2   19.22        0.056625  1114.849188
3   20.11        0.046306  1166.473318
4   20.26        0.007459  1175.174014
5   22.20        0.095755  1287.703016
6   22.44        0.010811  1301.624130
7   20.05       -0.106506  1162.993039

Upvotes: 0

Related Questions