Calculate monthly returns from daily returns in pandas(cumpound)

Question

I was trying to calculate monthly returns for a particular stock, but I can't figure out a good method which doesn't use a big quantity of for cycles. The df has the following form

           MSFT-US  AAPL-US    GE    RF
 20150501    1.01   -0.33   -0.60   0.000
 20150504    0.32    0.06    0.16   0.000
 20150505   -1.19   -0.10    0.34   0.000
 20150506   -0.31    0.62   -0.20   0.000
 20150507    0.39    0.03   -0.43   0.000
 20150508    1.21   -0.54   -0.21   0.000
 20150511   -0.39    0.67   -0.11   0.000
 20150512   -0.27    0.00    0.11   0.000
 20150513    0.01    0.02   -0.06   0.000
 20150514    1.01   -0.10   -0.36   0.000
 20150515    0.05   -0.26   -0.01   0.000
 20150518    0.44    0.72   -0.09   0.000
 20150519   -0.09   -0.08    0.03   0.000
 20150520   -0.05    0.21   -0.09   0.000
 20150521    0.23   -0.31    0.09   0.000
 20150522   -0.22   -0.11   -0.14   0.000
 20150526   -1.01   -0.04   -0.02   0.000
 20150527    0.93    0.33   -0.39   0.000
 20150528   -0.11    0.11    0.07   0.000
 20150529   -0.58    0.02    0.05   0.000

So I want to have something like this (but coumpound not sum):

         MSFT-US   AAPL-US     GE      RF
 201505    1.36     0.92     -1.89    0.00

Possible duplicate of [Pandas group-by and sum](https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum) — Ankur Sinha, Sep 07 '18 at 08:05

score 8 · Answer 1 · answered May 16 '19 at 13:32

I assume that your Dataframe has a DatetimeIndex. If so, I prefer this approach from daily to monthly returns:

df.resample('M').agg(lambda x: (x + 1).prod() - 1)

But you can also apply for another frequency. For example, for weekly returns last Friday:

df.resample('W-FRI').agg(lambda x: (x + 1).prod() - 1)

asongtoruin · Answer 2 · 2018-09-07T09:46:34.290

7

Assuming your date column is called 'date':

df['month'] = df['date'].astype(str).str[:6]

monthly_total = df.groupby('month').sum().drop('date', axis='columns')

gives you

        MSFT-US  AAPL-US    GE   RF
month                              
201505     1.38     0.92 -1.86  0.0

To get compound returns, we need to add 1 to each value and then use .prod():

df[['MSFT-US', 'AAPL-US', 'GE', 'RF']] += 1
monthly_total = df.groupby('month').prod().drop('date', axis='columns')

Giving us:

         MSFT-US   AAPL-US        GE   RF
month                                    
201505  0.008739  0.946043  0.070769  1.0

edited Sep 07 '18 at 09:46

answered Sep 07 '18 at 08:03

asongtoruin

9,794
3
36
47

but if i want compound returns? – ok_dataentry Sep 07 '18 at 09:25
@ok_dataentry what do you mean by that? – asongtoruin Sep 07 '18 at 09:26

Naga kiran · Answer 3 · 2018-09-07T08:22:35.463

3

df.index = df.index.map(lambda x:pd.to_datetime(str(x)))
df.groupby([df.index.year,df.index.month]).sum()

output:

        MSFT-US AAPL-US GE  RF
2015    5   1.38    0.92    -1.86   0.0

edited Sep 07 '18 at 08:22

answered Sep 07 '18 at 08:06

Naga kiran

4,528
1
17
31

what happens if the data spans more than one year? – asongtoruin Sep 07 '18 at 08:17
we can group it out to consider the index years. i just included . thanks – Naga kiran Sep 07 '18 at 08:24

score 1 · Answer 4 · answered Jul 26 '20 at 07:08

Assuming your data is daily returns, below snippet can be used to resample the data for monthly returns:

df.index = pd.to_datetime(df.index)
df = df.resample('1M').mean()
df.index = pd.to_datetime(df.index, format="%Y%m").to_period('M')

Date index will be in the format:

PeriodIndex(['2015-07', '2015-08', '2015-09'...dtype='period[M]', name='date', freq='M')

The value will be mean of returns for that month.

Calculate monthly returns from daily returns in pandas(cumpound)

4 Answers4

Linked