pip install pandas
y


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 = '8a1cf9d14d53126b05c8a7636fe3d006'
    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 = '8a1cf9d14d53126b05c8a7636fe3d006'
    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=5000000000&priceMoreThan=15&volumeMoreThan=2000000&country=US&apikey={api_key1}').json()
    
    filter1= pd.DataFrame(filter)
    mid_lar_cap= filter1['symbol'].values.tolist()
    
    
    final_tickers = all_ticker+mid_lar_cap
    final_tickers1 = np.unique(final_tickers).tolist()
    
    #############################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(stock_lists1).rename(columns = {0:'symbol'})
    return stock_lists


stock_lists = get_ticker()
stock_lists.to_csv(r'C:\Users\jizha\Desktop\SEABRIDGEDB\stock_lists.csv' )   
stock_lists = pd.read_csv(r'C:\Users\jizha\Desktop\SEABRIDGEDB\stock_lists.csv' )   
 
stock_lists = pd.DataFrame(stock_lists['symbol'])
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[2]
#update the first sheet with df, starting at cell B2. 
wks.set_dataframe(df,(1,1))    
 
    
 
    
 
    
 
    
 
    
 
    
 
    
 
    
 
    



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)

https://financialmodelingprep.com/api/v3/available-traded/list?apikey=demo

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



filter = requests.get(f'https://financialmodelingprep.com/api/v3/stock-screener?marketCapMoreThan=100000000&betaMoreThan=1&volumeMoreThan=2000000&apikey={api_key1}').json()