Search code examples
pythonpandasnumpyreturnfinance

Pandas: How to accumulate returns - forecasting prices

I have a pandas dataframe of stock prices indexed by date (monthly data). I want to compute the following: starting with 100 stocks at Jan 31, 1983 worth $4100 (41.00 per stock) what is the maximum absolute value of stocks in march 2012, if I could have accurately forecasted next month's ending price.

  • if next months end price higher than this months end price: buy stocks for full amount of cash and fully participate at stock returns
  • if next months end price lower than this months end price: sell all stocks and take the cash at beginning of the month (do not participate in stock losses)

enter image description here

Here is some sample data to work with:

df = pd.DataFrame({
    'Date': ['1983-01-01','1983-02-28','1983-03-31','1983-04-30','1983-05-31'],
    'Month End Price': [41.00,46.75,44.25,50.00,59.25]
}).set_index('Date')

df.index = pd.to_datetime(df.index)

For example in Feb 1983 stock price increased from 41.00 to 46.75, which is a return of 14.02% that month. So my stocks, initially worth 4100$, would rise to 4100$*(1+14.02%)= $4675 for end of Feb 1983.

In Mar 83, there is a negative return (as price declines from 46.75 to 44.25). Having had known that decline, I would have sold all stocks worth $4675 end of Feb (not participating losses) and then reinvest in the beginning of April 1983.

In April 1983, stocks performance is +12.99% (50.00/44.25 -1), so my net worth would increase from $4675 to $4675*(1+12.99%) = $5282.5 until end of April 1983.

Solution

  • You can do this more compactly, but I will set up with a few intermediate columns so the logic is clear. First, I'm going to set up a sample dataset with a few ups and downs.

    import pandas as pd

prices = [50.00,46.75,44.25,50.00,59.25,66.50,
          29.25,44.25,59.25,61.00,64.25,65.25]
dates = pd.date_range('01-31-1983','12-31-1983', freq='m')

df = pd.DataFrame({'Month End Price':prices}, index=dates)

    This yields a dataframe that looks like this:

               Month End Price
1983-01-31            50.00
1983-02-28            46.75
1983-03-31            44.25
1983-04-30            50.00
1983-05-31            59.25
1983-06-30            66.50
1983-07-31            29.25
1983-08-31            44.25
1983-09-30            59.25
1983-10-31            61.00
1983-11-30            64.25
1983-12-31            65.25

    And this: enter image description here

    You can compute the month-to-month price fluctuations as:

    df['Monthly Returns'] = df['Month End Price'].diff()/df['Month End Price']

    We want to realize all gains and avoid all losses, from what I understand. I set up a multiplier column that equals 1 for months when we should have avoided losses and is basically 1 + df['Monthly Returns'] for months with gains. Then I compute a Cash column as the cumulative product of the Multiplier column times $41, which was our principal. There is a temptation to use a for loop here, but with Pandas, anytime you see a for, there's often a quicker, built-in like cumprod:

    df['Multiplier'] = df['Monthly Returns'].apply(lambda x: max(x, 0)) + 1
df['Cash'] = df['Multiplier'].cumprod() * 41

    Once all that is done, we have something that looks like:

               Month End Price  Monthly Returns  Multiplier        Cash
1983-01-31            50.00              NaN         NaN   41.000000
1983-02-28            46.75        -0.069519    1.000000   41.000000
1983-03-31            44.25        -0.056497    1.000000   41.000000
1983-04-30            50.00         0.115000    1.115000   45.715000
1983-05-31            59.25         0.156118    1.156118   52.851941
1983-06-30            66.50         0.109023    1.109023   58.613995
1983-07-31            29.25        -1.273504    1.000000   58.613995
1983-08-31            44.25         0.338983    1.338983   78.483145
1983-09-30            59.25         0.253165    1.253165   98.352296
1983-10-31            61.00         0.028689    1.028689  101.173878
1983-11-30            64.25         0.050584    1.050584  106.291623
1983-12-31            65.25         0.015326    1.015326  107.920614

    And the value of the positions look like this:

    enter image description here