Stock #     Date           Monthly Return
A           31/01/2015           1.05%
A           28/02/2015           2.04%
..........
A           31/01/2018           5.06%
A           28/02/2018           8.07%
A           31/03/2018           5.25%

B           31/01/2015           2.53%
B           28/02/2015           2.55%
..........
B           31/01/2018           1.53%
B           28/02/2018           1.55%
B           31/03/2018           1.63%

Stock #     Effective Date   1 Month   3 Month  1 Year  5 Years (annualised)
A           31/03/2018         5.25%    19.50%   etc
B           31/03/2018         1.63%    4.78%    etc

import pandas as pd
import glob
from tabulate import tabulate

path = r'H:/'

filename = glob.glob(path + "Stock_Returns.xls")
print(filename[0])
df = pd.read_excel(filename[0])



df[['Return']] = df[['Return']].apply(pd.to_numeric)
df['Date'] = pd.to_datetime(df['Date'])
df['Return'] = df['Return']/100+ 1
df = df[['Stock','Date', 'Return']]
df['date']=df['As Of Date'].dt.year
df['one_year_return'] = df.groupby(['Stock','date']).cumprod()
df = df.groupby(['Stock','date']).nth(11)
print(tabulate(df, headers='firstrow', tablefmt='psql'))

df.to_excel(filename[0])