Search code examples
pythonpandasquantitative-finance

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!!

Solution

  • 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