Grouping data by company ID, investor id and quarterly date.

Hi Community,

I am trying to calculate institutional investor volatility. I have already downloaded quarterly data of each firm and their quarterly holding as well.

I am stuck in trying to :

Point1. Group data by company ID, investor id and quarterly date and then download that specific Dataframe.

Once I have them grouped as shown in the picture

Point2. I want to calculate standard deviation of the quarterly holdings of each institutional investor of a firm over the 6 quarters.


Any help would be greatly appreciated for either points ! Really stuck on this for a while now. I have the code written but happy to have it tweaked.

df,e = ek.get_data(instruments=['AAPL.O','AAL.L'], fields=[
'TR.CommonName',
'TR.InvestorFullName("TheInvestorType":"113,108,107")',
'TR.SharesHeld.calcdate',
'TR.SharesHeld',
'TR.PctOfSharesOutHeld',
'SUM(TR.PctOfSharesOutHeld)',
'TR.InvestorType',
'TR.InvestorTypeId'],parameters={'SDate':'0','EDate':'-6', 'Period':'FQ0','Frq':'FQ'})
df
#EDate is the number of quaters
#Assume there are two companies
df['Quarter'] = pd.PeriodIndex(df['Calc Date'], freq='Q')
df
gkk = df.groupby(['Investor Full Name', 'Quarter'])
gkk.first()

@Jirapongse Look forward to your help.

Answers