import pandas as pd
import finnhub
import requests
import numpy as np
import time
from datetime import datetime
###three index###########
def get_ticker():
    api_key1 = '86dd63f6b8ae774b061232685b78eb52'
    SP = requests.get(f'https://financialmodelingprep.com/api/v3/sp500_constituent?&apikey={api_key1}').json()    
    SP = pd.DataFrame(SP)
    sp = SP['symbol'].values.tolist()
    
    api_key1 = '86dd63f6b8ae774b061232685b78eb52'
    NASDAQ = requests.get(f'https://financialmodelingprep.com/api/v3/nasdaq_constituent?&apikey={api_key1}').json()
    NASDAQ = pd.DataFrame(NASDAQ)
    nasdaq = NASDAQ['symbol'].values.tolist()
    
    DJI= requests.get('https://finnhub.io/api/v1/index/constituents?symbol=^DJI&token=bt3efpf48v6tfcs816eg').json()
    dji = DJI['constituents']
    
    tickers = sp + nasdaq + dji
    tickers = np.array(tickers) 
    tickers = np.unique(tickers)
    
    ticker_3 = pd.DataFrame(tickers).rename(columns = {0:'ticker'})
    all_ticker = ticker_3['ticker'].values.tolist()
    ###########################################market cap > 5Billion#####################
    
   # filter = requests.get(f'https://financialmodelingprep.com/api/v3/stock-screener?marketCapMoreThan=200000000&priceMoreThan=25&volumeMoreThan=1000000&country=US&apikey={api_key1}').json()
    filter1= requests.get(f'https://financialmodelingprep.com/api/v3/stock-screener?marketCapMoreThan=500000000&volumeMoreThan=300000&priceMoreThan=60&country=US&apikey={api_key1}').json()
    #filter1 = requests.get(f'https://financialmodelingprep.com/api/v3/stock/list?apikey={api_key1}').json()

    filter1= pd.DataFrame(filter1)
    mid_lar_cap= filter1['symbol'].values.tolist()
    
    
    final_tickers = all_ticker+mid_lar_cap
    final_tickers1 = np.unique(final_tickers).tolist()
    final_tickers1.remove('BF-B')
    final_tickers1.remove('BRK-B')
    #############################etf ###############################
    #etf = requests.get(f'https://financialmodelingprep.com/api/v3/etf/list?apikey={api_key1}').json()
    #t = ['SPBO', 'IGIB', 'SCHI', 'RING','PICK','SLVP','XHE','FHLC','GHYG','GHYB','IHY','VONG','JMOM','IUSG','VONV','IWD','IUSV','ILTB','IGLB','SPLB','MDYG','VOT','VXF','USRT', 'REET','RWR',
        # 'SPSB','SCHJ','ISTB','VDE','XLB','xli','VCR','VDC','XLV','XLF','VGT','XLC','XLU','VNQ','QQQ','SPY','Dji','IWM']
    #t = ['QQQ','SPY', 'IWM','DIA','XLK','XLV','XLF','XLRE','KRE','KBE','XLB','XLE','XLY','XLI','XLU','XLP','XLC']
    
   # f =[]
    #for tick in t:
   #     if tick not in final_tickers1:
   #         f.append(tick)
   # stock_lists1 = f+final_tickers1
    #stock_lists1.remove('BF-B')
  #  stock_lists1.remove('BRK-B')
 #   tickers3 = np.unique(stock_lists1)


    stock_lists =pd.DataFrame(final_tickers1).rename(columns = {0:'symbol'})
    return stock_lists
  #  tickers3 = np.unique(stock_lists)

stock_lists= get_ticker()
s =stock_lists.symbol.tolist()

stock_lists =pd.DataFrame(s)
#stock_lists.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool_12_10.csv' )  
stock_lists.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool_09_20.csv' ) 
s = stock_lists['symbol'].tolist()
#remove recent two month ipo datat
api_key1= '86dd63f6b8ae774b061232685b78eb52'
filter_ipo = pd.DataFrame(columns = ['symbol','ipoDate'])
for ticker in s:
    filter_ipo1 = requests.get(f'https://financialmodelingprep.com/api/v3/profile/{ticker}?apikey={api_key1}').json()[0]
    filter_ipo = filter_ipo.append({'symbol':filter_ipo1['symbol'], 'ipoDate':filter_ipo1['ipoDate']}, ignore_index = True)
    date ='2022-06-15'
    filter_ipo2 = filter_ipo[filter_ipo['ipoDate'] < date]

filter_ipo3 =list(filter_ipo2['symbol'])    
# filter_ipo2 =filter_ipo1.reset_index()
# filter_ipo3 =list(filter_ipo2['symbol'])
# c =filter_ipo1.copy()
# for i in range(len(filter_ipo1)):
#   #  print(i)
#     if filter_ipo1['ipoDate'][i] == '':
#         filter_ipo2.remove(c['symbol'][i]) 

# s =[]
# for tick in tickers3:
#     if tick not in symbols:
#         s.append(tick)
#filter_ipo3.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool_11_21.csv' )  

        

symbols3 = stocks_tickers['0'].tolist()

        
def get_companyinfo(symbols):
        import requests
        from datetime import datetime
        api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
        stocks = pd.DataFrame(columns = [ 'symbol', 'name','volAvg','price'])
        for symb  in symbols:
            comp = requests.get(f'https://financialmodelingprep.com/api/v3/profile/{symb}?apikey={api_key1}').json()[0]
            stocks = stocks.append({ 'symbol': comp['symbol'], 'name': comp['companyName'],'volAvg':comp['volAvg'], 'price':comp['price']}, ignore_index = True)
            
        stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
        stocks =stocks.set_index('symbol')
        stocks =stocks.sort_index()
    
       # stocks = stocks.style.format({'mktCap': "{:.2f}",'volAvg': "{:.2f}",'var3': "{:.2%}"})
        return stocks 
dd = get_companyinfo(filter_ipo3)

cc =dd.copy()

cc = cc[cc['price']>50]
#symbols1= cc[~cc['name'].str.contains('ETF')] 
symbols2=cc[~cc['name'].str.contains('Bond')] 
symbols3=symbols2[~symbols2['name'].str.contains('Trust')] 
symbols4=symbols3[~symbols3['name'].str.contains('S&P')] 
symbols5=symbols4[~symbols4['name'].str.contains('QQQ')] 


symbols6=symbols5[~symbols5['name'].str.contains('-')] 
stock_lists1 = stock_lists[~stock_lists['0'].str.contains('-')] 
stock_lists3 = stock_lists1[stock_lists1['0'].str.contains('.')] 
s = symbols5.index.tolist()
symbols = s
symbols.remove('BROS')
symbols.remove('AMC')
symbols.remove('GME')
symbols.remove('ALXN')
from datetime import datetime as dt
import datetime
today = dt.today()
a=str(today.year)
b=str(today.month)
c=str(today.day)
#f.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool_11_9.csv' )
f = pd.DataFrame(symbols)
stocks_tickers = pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stocks_list_7-29\stock_lists_2021_8_2.csv' )   
f.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool_09_20_2022.csv' )  

   

symbols.remove('BCE')
# #symbols.remove('GEHCV')
# symbols.remove('RXDX')
# symbols.remove('ALTM')
# symbols.remove('YUM')
# symbols.remove('ZEUS')
  
     
 
    
filter_ipo1 =filter_ipo1.reset_index()
filter_ipo2 =list(filter_ipo1['symbol'])
c =filter_ipo1.copy()
for i in range(len(filter_ipo1)):
  #  print(i)
    if filter_ipo1['ipoDate'][i] == '':
        filter_ipo2.remove(c['symbol'][i]) 
        
ticker1 = np.unique(filter_ipo2)
f =pd.DataFrame(filter_ipo2)

from datetime import datetime as dt
import datetime
today = dt.today()
a=str(today.year)
b=str(today.month)
c=str(today.day)
f.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists_pool.csv' )  
 
symbols =  filter_ipo2       
# date ='2020-1-1'    
# filter_ipo1 = filter_ipo1[filter_ipo1['ipoDate'] < date]
# #filter_ipo = pd.DataFrame(filter_ipo['symbol'])
# filter_ipo2 = pd.DataFrame(filter_ipo2)

dividen_tick=pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_watchlist\high_dividend_list20.csv' ) 
dividend_ticker  =dividen_tick['0'].tolist()
f =pd.read_excel(r'C:/Users/jizha/Desktop/seabridge_ibd_watchlists\IBD50.xls' ) 
long_term_ticker = list(f['Symbol']) 
#etf_index = ['QQQ','SPY', 'IWM','DIA','XLK','XLV','XLF','XLRE','KRE','KBE','XLB','XLE','XLY','XLI','XLU','XLP','XLC']
under_value_ticker =['ADM',
 'AON',
 'DHI',
 'HII',
 'HUM',
 'IVZ',
 'K',
 'LEN',
 'MAS',
 'MS',
 'NOC',
 'ORCL',
 'PGR',
 'PHM',
 'PNC',
 'TROW']
d =pd.read_excel(r'C:\Users\jizha\Desktop\seabridge_ibd_watchlists\BIGCAP20.xls' ) 
big_cap20 = list(d['Symbol']) 
#dividend_ticker =list(d['symbol'])
b= big_cap20 + under_value_ticker +long_term_ticker +dividend_ticker
c = np.unique(b).tolist()
symbols1 = c+filter_ipo3
symbols2 = np.unique(symbols1).tolist()
resis_not_good = ['ALLY','AJG','AMAT','BKR','BSX','BX','XLV','SIRI','TTD','VER','GOOG','HOG','KMB','NOV','NXPI','O','ON','XEL']
resis_not_good1 =['AJG','AVB','AVY','BEN','biib','BKR','CE''BSX','CHTR',,'DPZ','TXT','KPD','KMB','KLAC','IVZ','KR','VIZC','LEG','LNT','MRK','MRNA','NE','NEM','SWK','SRE','TTD','MU','SWK','VIAC','MS','NVAN','NXPI','SIRI','USB','CARR','VISER','EBAY']
list_backtest = ['BKR','ASO','DASH','CARR','DISCK','OTIS','PPD','U','ABNB','AMC','ZION','DISCA','DISCK','AIZ','U','ZION','OTIS','AMC','LNT','MRK','MRNA','NEM','SWK','SRE','TTD','MU','PDD','PENN']##not good

b =resis_not_good +resis_not_good1+list_backtest
c = np.unique(b).tolist()
d =[]
for ticker in list_backtest:
    if ticker in filter_ipo2:
        filter_ipo2.remove(ticker)
 
filter_ipo2 = pd.DataFrame(filter_ipo2)       
filter_ipo2 .to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stocks_list_7-29\stock_lists_7_29.csv' )  
 
stock_lists = pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_lists.csv' )  
for ticker in c:
    if ticker in filter_ipo2:
        d.append(ticker)

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import json
import pygsheets
import pandas as pd
#authorization
import pandas as pd
import pygsheets
import pandas as pdSeabridger
#authorization
df = stock_lists.copy()
#df = df.reset_index()
gc = pygsheets.authorize(service_file=r'C:/Users/jizha/App-googlesheet/seabridgeai_data2.json')
#df =pd.read_csv(r'C:\Users\jizha\Desktop\seabridge fintech\strategy_sum2' )
#df = ema_strategy_ret_df.merge(benchmark, how = 'inner')
#df=df.rename({"index":"symbol"}, axis = 1)
#open the google spreadsheet (where 'PY to Gsheet Test' is the name of my sheet)
sh =  gc.open_by_key('1HUKkg6IIY8uVvc24Z9SnU69ONVUlevPuQ4tG8suD7cY')
#select the first sheet 
wks = sh[1]
#update the first sheet with df, starting at cell B2. 
wks.set_dataframe(df,(1,1))    
 


#api_key1 = '86dd63f6b8ae774b061232685b78eb52'
#sp = requests.get(f'https://financialmodelingprep.com/api/v3/available-traded/list?apikey={api_key1}').json()    


d =pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_watchlist\high_dividend_list.csv' ) 
dividend_ticker =list(d['symbol'])

a =[]
for ticker in dividend_ticker:
    if  ticker in symbols:
        a.append(ticker)
dividend_ticker  =a

def get_companyinfo(symbols):
        import requests
        from datetime import datetime
        api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
        stocks = pd.DataFrame(columns = [ 'description','symbol', 'logourl', 'name','mktCap','lastDiv','volAvg','price'])
        for symb  in symbols:
            comp = requests.get(f'https://financialmodelingprep.com/api/v3/profile/{symb}?apikey={api_key1}').json()[0]
            stocks = stocks.append({'description': comp['description'], 'symbol': comp['symbol'],
                           'logourl': comp['image'], 'name': comp['companyName'], 'mktCap':comp['mktCap'],'lastDiv':comp['lastDiv'],'volAvg':comp['volAvg'], 'price':comp['price']}, ignore_index = True)
            
        stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
        stocks['div_yiel'] = stocks['lastDiv']/stocks['price']
        stocks[['lastDiv','price']] = stocks[['lastDiv','price']].applymap("{0:,.2f}".format) 
        stocks['div_yiel'] =stocks['div_yiel'].apply(lambda x: "{0:.2f}%".format(x*100))
       # df['var3'] = df['var3'].applymap(lambda x: "{0:.2f}%".format(x*100))
       # stocks = stocks.style.format({'lastDiv': "{:.2f}".format,'price': "{:.2f}".format,'div_yiel': "{:.2%}".format})
        stocks['mktCap'] = ( stocks['mktCap'].astype(float)/1000000).round(2).astype(str) + 'MM'
        stocks =stocks.set_index('symbol')
        stocks =stocks.sort_index()
    
       # stocks = stocks.style.format({'mktCap': "{:.2f}",'volAvg': "{:.2f}",'var3': "{:.2%}"})
        return stocks
#b = list(stock_lists['symbol'])
n =3
groups = np.array_split(symbols,n)
group1 = groups[0].tolist()
group2 = groups[1].tolist()
group3 = groups[2].tolist()

data1 =  get_companyinfo(group1 )
data2 =  get_companyinfo(group2)
data3 =  get_companyinfo(group3)
    
ticker1 =pd.concat([data1,data2,data3], axis =0) 
ticker2 =pd.concat([data1,data2,data3], axis =0) 
ticker1['price']=ticker1['price'].astype(float)
ticker2 = ticker1[ticker1['price']>35]
symbols  =ticker2.index.tolist()
a =[]
for i in ticker1.index:
    if i not in list(stock_lists['symbol']):
        a.append(i)
        
    
def get_companyinfo(symbols):
        import requests
        from datetime import datetime
        api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
        stocks = pd.DataFrame(columns = [ 'description','symbol', 'logourl', 'name','mktCap','lastDiv','volAvg','price'])
        for symb  in symbols:
            comp = requests.get(f'https://financialmodelingprep.com/api/v3/profile/{symb}?apikey={api_key1}').json()[0]
            stocks = stocks.append({'description': comp['description'], 'symbol': comp['symbol'],
                           'logourl': comp['image'], 'name': comp['companyName'], 'mktCap':comp['mktCap'],'lastDiv':comp['lastDiv'],'volAvg':comp['volAvg'], 'price':comp['price']}, ignore_index = True)
            
        stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
        stocks['div_yiel'] = stocks['lastDiv']/stocks['price']
        stocks[['lastDiv','price']] = stocks[['lastDiv','price']].applymap("{0:,.2f}".format) 
        stocks['div_yiel'] =stocks['div_yiel'].apply(lambda x: "{0:.2f}%".format(x*100))
       # df['var3'] = df['var3'].applymap(lambda x: "{0:.2f}%".format(x*100))
       # stocks = stocks.style.format({'lastDiv': "{:.2f}".format,'price': "{:.2f}".format,'div_yiel': "{:.2%}".format})
        stocks['mktCap'] = ( stocks['mktCap'].astype(float)/1000000).round(2).astype(str) + 'MM'
        stocks =stocks.set_index('symbol')
        stocks =stocks.sort_index()
    
       # stocks = stocks.style.format({'mktCap': "{:.2f}",'volAvg': "{:.2f}",'var3': "{:.2%}"})
        return stocks



n =3
groups = np.array_split(symbols,n)
group1 = groups[0].tolist()
group2 = groups[1].tolist()
group3 = groups[2].tolist()

data1 =  get_companyinfo(group1 )
data2 =  get_companyinfo(group2)
data3 =  get_companyinfo(group3)
    

dg =pd.concat([data1, data2, data3], axis =0)
stocks =  get_companyinfo(dividend_ticker )
dg['price']= pd.to_numeric(dg['price'],errors='coerce')
#dg['price'] = dg['price'].astype(float)
d1 =dg[ dg['price']>25]

stocks_tickers = pd.DataFrame(d1.index.tolist())

stocks_tickers = pd.DataFrame(ticker2.index.tolist())
from datetime import datetime as dt
import datetime
today = dt.today()
a=str(today.year)
b=str(today.month)
c=str(today.day)
stocks_tickers .to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stocks_list_7-29\stock_lists_'+a+'_'+b+'_'+c+'.csv' )  
 
stocks_tickers = pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stocks_list_7-29\stock_lists_2021_7_30.csv' )  
symbols2 = stocks_tickers['0'].tolist()
####################去除earning date###############

def earning_date1(symbols):
        api_key = '86dd63f6b8ae774b061232685b78eb52'
        bs = requests.get(f'https://financialmodelingprep.com/api/v3/earning_calendar?apikey={api_key}').json()
        
        df =pd.DataFrame()
        for i in range(len(bs)):
            d =pd.DataFrame()
            d1=bs[i]['symbol']
            d2 = bs[i]['date']
            d['earning_date'] =[d2]
            d.index = [d1]
            df = pd.concat([d,df],axis =0)
            earning_date =  pd.DataFrame()
        earning_date =  pd.DataFrame()
        for tick in symbols:
            dd= pd.DataFrame()
            if tick not in  df.index:
                d1 ="Not Available"
            else:
                d1=df['earning_date'][tick]
        
            dd['symbol'] = [tick]
            dd['earning_date'] = d1
            dd = dd.set_index('symbol')
            earning_date = pd.concat([earning_date ,dd],axis =0)
        earning_date= earning_date.sort_index()
    
    
        return  earning_date
    
earning_date = earning_date1(symbols)


a= earning_date[earning_date['earning_date'] =='2021-08-10']
b= earning_date[earning_date['earning_date'] =='2021-08-11']



from datetime import datetime as dt
import datetime

# yesterday = today-datetime.timedelta(1)
# start = (2020,3,1)
# end = (yesterday.year, yesterday.month,     yesterday.day)
end = dt.today()
#end = today-datetime.timedelta(1)
#start = '2021-03-01'
start = today-datetime.timedelta(200)

    ticker = 'AAPL'
    import requests
    api_key = '86dd63f6b8ae774b061232685b78eb52'
    from datetime import datetime
    bs = requests.get(f'https://financialmodelingprep.com/api/v3/historical-price-full/{ticker}?from={start}&to={end}&apikey={api_key}').json()['historical']
    stock = pd.DataFrame(bs)
    stock =stock[['date','open','high','low','close','volume']]
    stock = stock.rename(columns = {'date':'Date', 'open':'Open', 'high':'High','low':'Low', 'close':'Close', 'volume':'volume'})
    stock = stock.set_index('Date')
    stock= stock.sort_index()
    data1 = stock.copy()