how can i use dataframe and datetimeindex to return rolling 12-month?

Question

Imagine a pandas dataframe with 2 columns (“Manager Returns” and “Benchmark Returns”) and a DatetimeIndex of monthly frequency. Please write a function to calculate the rolling 12-month manager alpha and rolling-12 month tracking error (both annualized).

so far I have this but confused about the rolling-12 month:

import pandas as pd
import numpy as np

#define dummy dataframe with monthly returns
df = pd.DataFrame(1 + np.random.rand(20), columns=['returns'])

#compute 12-month rolling returns
df_roll = df.rolling(window=12).apply(np.prod) - 1

score 0 · Answer 1 · 2022-11-16T10:03:01.660

So, you want to calculate the excess return on the 'Manager Returns' compared to the 'Benchmark Returns. First, we create some random data for these two values.

import pandas as pd
import numpy as np
n=20
df = pd.DataFrame(dict(
    Manager=np.random.randint(2, 9, size=n),
    Benchmark=np.random.randint(1, 7, size=n),
    index=pd.date_range("20180101", freq='MS', periods=20)))

df.set_index('index', inplace = True)

To calculates the excess return (Alpha), the rolling mean of Alpha and the rolling mean of Tracking Error we create new columns for each value.

# Create Alpha
df['Alpha'] = df['Manager'] - df['Benchmark']
# Rolling mean of Alpha
df['Alpha_rolling'] = df['Alpha'].rolling(12).mean()
# Rolling mean of Tracking error
df['TrackingError_rolling'] = df['Alpha'].rolling(12).std()

Edit: I see that the values should be annualized, so you would have to transform the monthly returns I guess, my finance lingo is not currently up to date.

how can i use dataframe and datetimeindex to return rolling 12-month?

1 Answers1