import pandas as pd
import finnhub
import requests
import numpy as np
import time
from datetime import datetime
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt
import pandas as pd
import mplfinance as mpf
from mplfinance.original_flavor import candlestick_ohlc
import finnhub
import matplotlib.dates as mpl_dates
import numpy as np
from datetime import * 
import seaborn as sns
from datetime import datetime
import requests

pd.set_option('display.float_format', '{:.2E}'.format)
# 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)

# api_key = 'bt3efpf48v6tfcs816eg' 

# n = 2 # number of groups
# groups = np.array_split(tickers, n)
# stocks = pd.DataFrame(columns = ['description', 'ticker', 'weburl', 'logo', 'name'])
# for group in groups:
#     for symb in group:
#         comp = requests.get(f'https://finnhub.io/api/v1/stock/profile?symbol={symb}&token={api_key}').json()
#         stocks = stocks.append({'description': comp['description'], 'ticker': comp['ticker'],\
#                        'weburl': comp['weburl'], 'logo': comp['logo'], 'name': comp['name']}, ignore_index = True)
    
#     time.sleep(60)
# stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
# stocks.to_csv('stocks.csv', index = False)


    
#     return stocks
import requests

def get_companyinfo(symbols):
    api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
    stocks = pd.DataFrame(columns = [ 'description','symbol', 'logourl', 'name','mktCap','lastDiv'])
    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']}, ignore_index = True)
        
    stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
    
    return stocks

stocks =  get_companyinfo( symbols)
       
stock_lists= pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\backtest_symbols.csv')

symbols = list(stock_lists['0'])  
  
n = 2# number of groups
groups = np.array_split(symbols, n)          
stocks = pd.DataFrame()
for group in groups:
    da1 = get_companyinfo( group)
    stocks= pd.concat([stocks,da1], axis =0)
    time.sleep(60)
        
stocks=stocks.set_index('symbol') 
stocks = stocks.sort_index()
stocks.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\stocks.csv.csv')


#############record_id

stocks['record_id'] = 0
######################earningdate#########################

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)

    return  earning_date

stock_lists= pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\backtest_symbols.csv')
symbols = list(stock_lists['0'])    
    
earning_date = earning_date1(symbols)
earning_date= earning_date.sort_index()
earning_date.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\earning_date.csv.csv')



##################peratio##################
def eps_pe(symbols):
    api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
    da1 = pd.DataFrame(columns = [ 'pe_ratio(TTM)','symbol', 'eps(TTM)'])
    for symb  in symbols:
        comp = requests.get(f'https://financialmodelingprep.com/api/v3/quote/{symb}?apikey={api_key1}').json()[0]
        da1 = da1.append({'pe_ratio(TTM)': comp['pe'], 'symbol': comp['symbol'],
                      'eps(TTM)': comp['eps']}, ignore_index = True)
        
    return da1
#    stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
da2 = eps_pe(symbols)
da2 = da2.set_index('symbol')
da2 = da2.sort_index()


n = 3# number of groups
groups = np.array_split(symbols, n)
da2 =pd.DataFrame()
import time
for group in groups:
    da1 = eps_pe(group)
    da2 =pd.concat([da2,da1], axis =0)
    time.sleep(60)
    

    

stock_info=stocks.merge(earning_date, left_index=True, right_index=True)

stock_info_data = stock_info.merge(da2,  left_index=True, right_index=True)
stock_info_data = stock_info_data.sort_index()
stock_info_data.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stock_info_data.csv')

#stock_info_data = stock_info_data[['description','logourl',	'name','mktCap','volAvg','lastDiv',	'earning_date',	'pe_ratio(TTM)', 	'eps(TTM)']]

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
df = stock_info_data.copy()
df =df.reset_index()
#df =df.sort_index(axis =1)
#authorization
gc = pygsheets.authorize(service_file=r'C:/Users/jizha/App-googlesheet/crenditals.json')
sh =  gc.open_by_key('136vIMVig4r4j0b-gOwg25IlL-evFF_hqLNY3FxIUHWU')
#select the first sheet 
wks = sh[13]
#update the first sheet with df, starting at cell B2. 
wks.set_dataframe(df,(1,1))










##################filter_out to1000############

import requests

def get_companyinfo(symbols):
    api_key1 = '86dd63f6b8ae774b061232685b78eb52'    
    stocks1 = pd.DataFrame(columns = [ 'description','symbol', 'weburl', 'logourl', 'name','mktCap','volAvg', 'industry','lastDiv','price'])
    for symb  in symbols:
       
        comp = requests.get(f'https://financialmodelingprep.com/api/v3/profile/{symb}?apikey={api_key1}').json()[0]
        stocks1 = stocks1.append({'description': comp['description'], 'symbol': comp['symbol'],\
                        'weburl': comp['website'], 'logourl': comp['image'], 'name': comp['companyName'], 'mktCap':comp['mktCap'],'volAvg':comp['volAvg'],'industry':comp['industry'],'lastDiv':comp['lastDiv'],'price':comp['price']}, ignore_index = True)
    return stocks1    
    stocks1['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")



stocks1 =get_companyinfo(s)
stocks2  = stocks1[stocks1['volAvg']>1500000]
#stocks2 =stocks1[stocks1['lastDiv']>0]
stocks2 .to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\revised_one.csv')
q =['QQQ','SPY','IWM','DIA']
symbols1 =stocks2.index.tolist()
symbols+symbol1
# n=7# number of groups
# groups = np.array_split(symbols, n)
# group1 = groups[0].tolist() 
# group2 = groups[1].tolist()
# group3 = groups[2].tolist()
# group4 = groups[3].tolist()
# group5 = groups[4].tolist()
# group6 = groups[5].tolist()


# data1 =   get_companyinfo(group1)   
# data2 = get_companyinfo(group2)     
# data3 =  get_companyinfo(group3)         
# data4 = get_companyinfo(group4)     
# data5 = get_companyinfo(group5)   
# data6= strategy_signal3(group6)    
# data7 = strategy_signal3(group7)       
    

# strategy3 = pd.concat([data1, data2, data3, data4, data5, data6, data7], axis = 0)

# strategy3.to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\startegy3.csv')




stock_markettrend= stock_markettrend.sort_index()

stock_info_data=stock_data.merge(stock_markettrend, left_index=True, right_index=True)

stock_info_data = stock_info_data.sort_index(axis =1)


df = stock_info_data.copy()
df =df.reset_index()
df =df.sort_index(axis =1)
#df=df.rename({"ticker":"symbol"}, axis = 1)
#pip install oauth2client

#pip install gspread
#pip install PyOpenSSL
#pip install pygsheets