import pandas as pd
import os
import eikon as ek
ek.set_app_key('3f9662a042714f22afb4e9e960c8ceb738e3f8f2')
data = ek.get_timeseries('AAPL.O',  # the RIC for Apple, Inc.

                         fields='*',  # all fields

                         start_date='2017-01-01',  # start

                         end_date='2017-12-31')  # end datedate



data.tail()          



import 
csv

ricframe1 
= "missing_rics_filtered.csv"

rics 
= []

with 
open (ricframe1, 
'r+') as 
csvfile:

    testreader 
= csv.reader(csvfile)

    for 
row in 
testreader:

        rics.append(row[0])



print(rics)



rics 
= [i 
for i 
in rics 
if i 
!= '']

sortedrics 
= sorted(set(rics),
key 
= rics.index)

print(sortedrics)



fields 
= ['TR.InvestorFullName', 
'TR.InvestorFullName.investorid','TR.InvestorFullName.investorpermid','TR.CategoryOwnershipPct','TR.InvestorType','TR.InvParentType',
'TR.PctOfSharesOutHeld', 
'TR.PctofSharesOutHeld.Date', 'TR.InvAddrCountry',
'TR.OwnTrnverRating', 
'TR.OwnTurnover','TR.OwnTurnover.date','TR.InvInvestmentStyleCode']



suffixes_list= [('_1999','_2000'),('_2001','_2001'),('_2001','_2002'),('_2003','_2003'),('_2003','_2004'),('_2005','_2005'),('_2005','_2006'),('_2007','_2007'),

                ('_2007','_2008'), ('_2009','_2009'),('_2009','_2010'),('_2011',"_2011"),('_2011',"_2012"),('_2013',"_2013"),('_2013',"_2014"),('_2015',"_2015"),

                ('_2015',"_2016"),('_2017',"_2017"),('_2017',"_2018"),('_2019',"_2019"),('_2019',"_2020"),('_2021',"_2021"),('_2021',"_2022")]

# Chunk for RIC at index 2 = corresponds to
BROG.OQ

instruments 
= sortedrics[2]  # Adjust the index for each RIC

data_frames 
= []

for 
i in 
range(-24,
-1):

    s_date 
= str(i)

    e_date 
= str(i)

    df, 
err = 
ek.get_data(instruments,
fields, {'SDate':
s_date, 
'Edate': e_date, 
'Frq':"Y"})

    keylist 
= df.keys()

    for 
item in 
keylist:

        df[item]
= 
df[item].astype(str)

    print(s_date,
e_date)

    data_frames.append(df)

merged_df 
= data_frames[0]

for 
i in 
range(1, 
len(data_frames)):

    merged_df 
= pd.merge(merged_df,
data_frames[i],
on='Investor Full Name',
how='outer',
suffixes=suffixes_list[i-1])

merged_df.to_csv(f'{instruments}.csv')