CODEWITHSUNDEEP
pythonpandasquantitative-finance
Tom
Tom

Reputation: 87

Multiplying data within columns python

I've been working on this all morning and for the life of me cannot figure it out. I'm sure this is very basic, but I've become so frustrated my mind is being clouded. I'm attempting to calculate the total return of a portfolio of securities at each date (monthly).

The formula is (1 + r1) * (1+r2) * (1+ r(t))..... - 1

Here is what I'm working with:

Adj_Returns = Adj_Close/Adj_Close.shift(1)-1
Adj_Returns['Risk Parity Portfolio'] = (Adj_Returns.loc['2003-01-31':]*Weights.shift(1)).sum(axis = 1) 

Adj_Returns

               SPY         IYR        LQD       Risk Parity Portfolio
Date                
2002-12-31      NaN       NaN         NaN       0.000000
2003-01-31  -0.019802  -0.014723    0.000774   -0.006840
2003-02-28  -0.013479   0.019342    0.015533    0.011701
2003-03-31  -0.001885   0.010015    0.001564    0.003556
2003-04-30  0.088985    0.045647    0.020696    0.036997

For example, with 2002-12-31 being base 100 for risk parity, I want 2003-01-31 to be 99.316 (100 * (1-0.006840)), 2003-02-28 to be 100.478 (99.316 * (1+ 0.011701)) so on and so forth.

Thanks!!

Upvotes: 2

Views: 93

Answers (1)

piRSquared
piRSquared

Reputation: 294258

You want to use pd.DataFrame.cumprod

df.add(1).cumprod().sub(1).sum(1)

Consider the dataframe of returns df

np.random.seed([3,1415])
df = pd.DataFrame(np.random.normal(.025, .03, (10, 5)), columns=list('ABCDE'))

df

          A         B         C         D         E
0 -0.038892 -0.013054 -0.034115 -0.042772  0.014521
1  0.024191  0.034487  0.035463  0.046461  0.048123
2  0.006754  0.035572  0.014424  0.012524 -0.002347
3  0.020724  0.047405 -0.020125  0.043341  0.037007
4 -0.003783  0.069827  0.014605 -0.019147  0.056897
5  0.056890  0.042756  0.033886  0.001758  0.049944
6  0.069609  0.032687 -0.001997  0.036253  0.009415
7  0.026503  0.053499 -0.006013  0.053447  0.047013
8  0.062084  0.029664 -0.015238  0.029886  0.062748
9  0.048341  0.065248 -0.024081  0.019139  0.028955

We can see the cumulative return or total return is

df.add(1).cumprod().sub(1)

          A         B         C         D         E
0 -0.038892 -0.013054 -0.034115 -0.042772  0.014521
1 -0.015641  0.020983  0.000139  0.001702  0.063343
2 -0.008993  0.057301  0.014565  0.014247  0.060847
3  0.011544  0.107423 -0.005853  0.058206  0.100105
4  0.007717  0.184750  0.008666  0.037944  0.162699
5  0.065046  0.235405  0.042847  0.039769  0.220768
6  0.139183  0.275786  0.040764  0.077464  0.232261
7  0.169375  0.344039  0.034505  0.135051  0.290194
8  0.241974  0.383909  0.018742  0.168973  0.371151
9  0.302013  0.474207 -0.005791  0.191346  0.410852

Plot it

df.add(1).cumprod().sub(1).plot()

enter image description here

Add sum of returns to new column

df.assign(Portfolio=df.add(1).cumprod().sub(1).sum(1))

          A         B         C         D         E  Portfolio
0 -0.038892 -0.013054 -0.034115 -0.042772  0.014521  -0.114311
1  0.024191  0.034487  0.035463  0.046461  0.048123   0.070526
2  0.006754  0.035572  0.014424  0.012524 -0.002347   0.137967
3  0.020724  0.047405 -0.020125  0.043341  0.037007   0.271425
4 -0.003783  0.069827  0.014605 -0.019147  0.056897   0.401777
5  0.056890  0.042756  0.033886  0.001758  0.049944   0.603835
6  0.069609  0.032687 -0.001997  0.036253  0.009415   0.765459
7  0.026503  0.053499 -0.006013  0.053447  0.047013   0.973165
8  0.062084  0.029664 -0.015238  0.029886  0.062748   1.184749
9  0.048341  0.065248 -0.024081  0.019139  0.028955   1.372626

Upvotes: 3

Related Questions