Using Pandas shift with multi-index

Question

I have am trying to make a simple stock portfolio tracker. I start with $100k dollars and invest based on weights in two stocks. Each month, I want to sell the stock and then set the new starting dollars (shares bought in last period * current price) and reinvest in the two stocks again based on this months weights.

I figured out how to calculate the Shares bought each period thanks to some previous SO help, but I'm have trouble update the starting balance for each date. I think due to order of operations, it needs to be done as a loop, but having trouble getting the shift to work.

Start of my code:

import pandas as pd
from datetime import datetime

df = pd.DataFrame(data=[[datetime(year=2017, day=01, month=01), 100000, 'AAPL', 0.6, 1000],
                        [datetime(year=2017, day=01, month=01), 100000, 'GOOG', 0.4, 1200],
                        [datetime(year=2017, day=01, month=02), 100000, 'AAPL', 0.7, 1400],
                        [datetime(year=2017, day=01, month=02), 100000, 'GOOG', 0.3, 1300],
                        [datetime(year=2017, day=01, month=03), 100000, 'AAPL', 0.8, 980],
                        [datetime(year=2017, day=01, month=03), 100000, 'GOOG', 0.2, 1400]
                        ],
                  columns=['Date', 'Dollars Available', 'Stock', 'Weight', 'Price'])

df = df.set_index(['Date', 'Dollars Available', 'Stock'])
df['Shares Bought'] = (df.index.get_level_values('Dollars Available') * df['Weight']).values / df['Price'].values

So far, I've gotten to here. As you can see, the starting balance is the same for each date.

                                    Weight  Price  Shares Bought
Date       Dollars Available Stock                              
2017-01-01 100000            AAPL      0.6   1000      60.000000
                             GOOG      0.4   1200      33.333333
2017-02-01 100000            AAPL      0.7   1400      50.000000
                             GOOG      0.3   1300      23.076923
2017-03-01 100000            AAPL      0.8    980      81.632653
                             GOOG      0.2   1400      14.285714

Desired Output:

                                        Weight  Price  Shares Bought
Date       Dollars Available Stock                              
2017-01-01 10000.00          AAPL      0.6   1000          60.00
                             GOOG      0.4   1200          33.33
2017-02-01 127333.33         AAPL      0.7   1400          63.66
                             GOOG      0.3   1300          29.38
2017-03-01 103531.79         AAPL      0.8    980          73.95
                             GOOG      0.2   1400          22.19

John Zwinck · Accepted Answer · 2018-01-13 02:54:22Z

1

Think about organizing your data this way (I've left off 2017-03-01 because you don't show prices for that date and it makes the example more concise):

Date        AAPL Return  GOOG Return  AAPL Weight  GOOG Weight
2017-01-01          1.4        1.083          0.6          0.4
2017-02-01          0.7        1.077          0.7          0.3

Above, Return is (close price / open price).

Now multiply for each stock:

Date        AAPL WgtRet  GOOG WgtRet  Sum WgtRet  Cum WgtRet
2017-01-01         0.84       0.4332      1.2732      1.2732
2017-02-01         0.49       0.3231      0.8131      1.0352

Above, Sum WgtRet is like sum(axis=1), and Cum WgtRet is like cumprod(Sum WgtRet).

Now, simply take your starting capital and multiply by Cum WgtRet to get the month-end balances:

Date        End Balance
2017-01-01  127320
2017-02-01  103520

Each of the above operations is easily done in vectorized form, without loops. You can "pivot" the stock column from row labels to column labels at the beginning.

If you want to add the "Shares Bought" columns, just shift(1) the End Balance to make a Begin Balance column, and multiply by the weights.

answered Jan 13, 2018 at 2:54

John Zwinck

252k44 gold badges347 silver badges459 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2242044 Over a year ago

Thank you. This looks promising, but it much above my pandas skill level. What is the best way to get the data even set up like your first dataframe? My thought was to use something like df.groupby(['Stock'])['Price'].shift(-1)/df.groupby(['Stock'])['Price'] but I am getting errors

John Zwinck Over a year ago

After creating the DataFrame, do df.set_index(['Date', 'Stock'], inplace=True) then df.Weight.unstack() to get weight per stock, px = df.Price.unstack() for prices, then px / px.shift() to get the returns. Add each new column to a single DataFrame whose index is just Date.

BENY · Accepted Answer · 2018-01-13 03:11:31Z

0

This is a way to calculate the Dollars

df['New1']=((1+df.groupby('Stock').Price.pct_change())).shift(-2)*df.Weight

df.groupby('Date').New1.sum().cumprod().mul(100000).shift().fillna(100000)
Out[288]: 
Date
2017-01-01    100000.000000
2017-02-01    127333.333333
2017-03-01    103531.794872
Name: New1, dtype: float64

answered Jan 13, 2018 at 3:11

BENY

324k22 gold badges176 silver badges250 bronze badges

4 Comments

user2242044 Over a year ago

Thank you. What am I doing wrong? I only see the first two rows.

BENY Over a year ago

@user2242044 ummm. what you mean first two row ?

user2242044 Over a year ago

sorry nevermind. I shortened my df to two months when I tried your code. Just me being stupid :)

BENY Over a year ago

@user2242044 ahha happy coding

Collectives™ on Stack Overflow

Using Pandas shift with multi-index

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related