Rolling returns¶

{admonition}
:class: note

When you want rolling returns over some calendar unit (hours, days, weeks, months, year), in realistic data with many firms and returns missing for parts of the calendar (e.g. no weekend trading, no after-hours trading):

1. `df['newVarName'] = df.set_index('date').groupby('asset')['ret'].rolling(window=<dateoffset>,1).apply(lambda x: np.prod(1+x)-1).values`
1. Do not use `window=#` unless you are sure your data is perfectly formed. `window=5` fails in real data because stock markets close for the weekend.
1. The `<dateoffset>` could be '7D', '24H', '1M' or many other things - [see this link](https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases).
1. The docs for the [`rolling` function](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rolling.html#pandas.DataFrame.rolling) provide extra functionality. 

Suppose we have an asset-timeperiod dataset, in long/tidy form. For example, this is a firm-day trading dataset:

The problem¶

Let's create a variable containing the cumulative return over the last week. Let's stipulate that if we have less than five days of returns for a firm at any point, we just use what we have.

{exercise}
Before continuing, eyeball the dataset above and guess what the rolling week-long returns should be for each observation.

Working the problem¶

This one-liner is very close to correct:

df.groupby('Firm')['ret'].rolling(5,1).apply(lambda x: np.prod(1+x)-1)

It's not correct, but let's start here because it's easier to understand. Let me separate that code up and add comments for each part:

(df.groupby('Firm')['ret']         # for each firm, grab the ret var
 .rolling(window=5,min_periods=1)  # in each row, use the prior 1-5 obs (including the current one)
 .apply(lambda x: np.prod(1+x)-1)  # R[0,T] = prod{ R(0), ..., R(T) } - 1
)

To add that variable to our dataset, df['varname'] = <the above> doesn't quite work. A small workaround does it: add .values to the end of the line. (It returns just the column of numbers and ignores the weird index the code above makes.)

So let's look at the result:

{warning}
The result above is wrong!
- 1/17 was a holiday, so the return for 1/18 should start with 1/12 (and only use 4 rows).
- 1/30 should only include itself since we are missing dates. 

Let's fix that. The documentation mentions that the window length can use a time period!

So instead of window=5, we can use window='7D'!

Except there is a final complication: After df.groupby('Firm')['ret'], the date variable is gone. And rolling() needs that, So we need to keep the date variable around somehow.

The last fix: Add .set_index('Date') before groupby, so that the date variable will stick around as the index.

The solution¶

Put all that together:

1. Put the date in the index
2. "For each asset"
3. Grab the returns for the prior week (using the dates to allow for holidays)
4. Cumulate the returns
5. Add that variable to the dataset

We can write this in one line:

The answer¶

{note}
Look at the last three dates for each firm, and you'll see how incorrect the `window=#` approach is.