import pandas as pd
import os
import eikon as ek
import time
from datetime import date
start_time=time.time()
ek.set_app_key('0c2e9ba6799840f1aecd044100ab14f042f3a0dc')

def get_eikon_data(ric_list,factor_list):
    try:
        df=ek.get_data(ric_list,factor_list)[0]
    except:
        time.sleep(5)
        print("error, sleep and retry")
        df=ek.get_data(ric_list,factor_list)[0]
    return df

def get_eikon_data_iteratively(ric_list,factor_list,threshold=3000):
    # if the number of ric_list is larger than 1000, then we need to get data iteratively, and then combine them together
    # the maximum number of ric_list is 1000
 #   threshold=3000
    df=pd.DataFrame()
    if len(ric_list)>threshold:
        n=len(ric_list)//threshold
        for i in range(n):
            print("stock group: "+str(i)+" from: "+str(i*threshold)+' to: '+str((i+1)*threshold))
            df=pd.concat([df,get_eikon_data(ric_list[i*threshold:(i+1)*threshold],factor_list)])
  #          df=df.append(get_eikon_data(ric_list[i*threshold:(i+1)*threshold],factor_list))
#        df=df.append(get_eikon_data(ric_list[n*threshold:],factor_list))
        df=pd.concat([df,get_eikon_data(ric_list[n*threshold:],factor_list)])
    else:
        df=get_eikon_data(ric_list,factor_list)
    return df

#input_dir='E:/ad hoc data/'
today = str(date.today())
# input_dir='D:/persues/python_codes/data/'
# output_dir='D:/persues/relative_valuation_factors/'+today+"/us/"
input_dir='./data/'
output_dir='./relative_valuation_factors/'+today+"/us/"
if not os.path.exists(output_dir):
    os.makedirs(output_dir)
    
stock_list=pd.read_excel(input_dir+'stocks_gics_multiple_market.xlsx',sheet_name='all')
stock_list = stock_list[stock_list['country of exchange']=='United States of America']
ric_list=stock_list['RIC'].unique().tolist()

#ric_list=['AAPL.O','IBM']
data_file=input_dir+'stock_peers_and_rv.xlsx'
fd_data=pd.read_excel(data_file)
factor_codes=list(fd_data['codes'])
factor_names=list(fd_data['names'])
print(factor_codes)
print(factor_names)
#output_dir=input_dir

all_data=pd.DataFrame(columns=['ric']+factor_names)
all_data['ric']=ric_list
all_entries=factor_codes


    # all_entries=[fd_data['Est Code'].iloc[ir],fd_data['Hist Code'].iloc[ir]]
for ie in range(len(all_entries)):
    entry=all_entries[ie].strip()
    print(entry)
    try:
        entry_data=get_eikon_data_iteratively(ric_list,entry,3000)
        aa=entry_data.iloc[:,1].reset_index()
        aa=aa.loc[:,aa.columns[1]]
        all_data[factor_names[ie]]=aa
    except:
        time.sleep(60)
        entry_data=get_eikon_data_iteratively(ric_list,entry,3000)
        aa=entry_data.iloc[:,1].reset_index()
        aa=aa.loc[:,aa.columns[1]]
        all_data[factor_names[ie]]=aa


all_data.to_excel(output_dir+'all_data.xlsx',header=True,index=False)


end_time=time.time()

print(f"Execution time: {end_time - start_time} seconds")