# IMPORTING PACKAGES import copy import requests import math import requests from operator import itemgetter from datetime import datetime import time import matplotlib.pyplot as plt import numpy as np from math import floor from termcolor import colored as cl import pandas as pd import finnhub from sklearn.cluster import KMeans import mplfinance as mpf from mplfinance.original_flavor import candlestick_ohlc import matplotlib.dates as mpl_dates from datetime import * import urllib3, socket from urllib3.connection import HTTPConnection import dateutil #pip install gspread import json etf_index = ['QQQ','SPY', 'IWM','DIA','XLK','XLV','XLF','XLRE','XLB','XLE','XLY','XLI','XLU','XLP'] def all_strategy(symbols): def get_strategy1(symbols): def get_signal(ticker, start, end): print(ticker) import requests api_key = '86dd63f6b8ae774b061232685b78eb52' 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() # strategy CALCULATION def get_tsi(Close, long, short, signal): diff = Close - Close.shift(1) abs_diff = abs(diff) diff_smoothed = diff.ewm(span = long, adjust = False).mean() diff_double_smoothed = diff_smoothed.ewm(span = short, adjust = False).mean() abs_diff_smoothed = abs_diff.ewm(span = long, adjust = False).mean() abs_diff_double_smoothed = abs_diff_smoothed.ewm(span = short, adjust = False).mean() tsi = (diff_double_smoothed / abs_diff_double_smoothed) * 100 signal = tsi.ewm(span = signal, adjust = False).mean() # tsi = tsi[tsi.index >= '2020-01-01'].dropna() # signal = signal[signal.index >= '2020-01-01'].dropna() return tsi, signal data1['tsi'], data1['signal_line'] = get_tsi(data1['Close'], 25, 13, 12) # TRUE STRENGTH INDEX STRATEGY def implement_tsi_strategy(prices, tsi, signal_line): buy_price = [] sell_price = [] tsi_signal = [] signal = 0 for i in range(len(prices)): if tsi[i-1] < signal_line[i-1] and tsi[i] > signal_line[i]: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 tsi_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) elif tsi[i-1] > signal_line[i-1] and tsi[i] < signal_line[i]: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 tsi_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) return buy_price, sell_price, tsi_signal buy_price, sell_price, tsi_signal = implement_tsi_strategy(data1['Close'], data1['tsi'], data1['signal_line']) # STOCK POSITION position = [] for i in range(len(tsi_signal)): if tsi_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data1['Close'])): if tsi_signal[i] == 1: position[i] = 1 elif tsi_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data1['Close'] tsi = data1['tsi'] signal_line = data1['signal_line'] tsi_signal = pd.DataFrame(tsi_signal).rename(columns = {0:'tsi_signal'}).set_index(data1.index) position = pd.DataFrame(position).rename(columns = {0:'tsi_position'}).set_index(data1.index) frames_data1 = [Close_price, tsi, signal_line, tsi_signal, position] strategy1 = pd.concat(frames_data1, join = 'inner', axis = 1) strategy1['ret'] =pd.DataFrame(strategy1['Close'].pct_change()) strategy1['tsi_returns'] = strategy1['ret'] *strategy1['tsi_position'] return strategy1 def strategy_signal1(symbols): strategy1 = pd.DataFrame() for tick in symbols: #print(tick) data = get_signal(tick, start , end ) f =pd.DataFrame(columns = ['g_bp','g_sp','g_p_bp','g_p_sp','g_tdate' ,'strategy1_g_signal']) if data['tsi_signal'][-1] == 1 and data['tsi_position'][-1] ==1: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'g_bp':d['Close'][0], 'g_sp':0,'g_p_bp':0,'g_p_sp':0,'g_tdate': d['index'][0],'strategy1_g_signal':1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # date = d.index.values.tolist() elif data['tsi_signal'][-1] == -1 and data['tsi_position'][-1] ==0: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'g_bp':0, 'g_sp':d['Close'][0],'g_p_bp':0,'g_p_sp':0,'g_tdate': d['index'][0],'strategy1_g_signal':-1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['tsi_signal'][-1] == 0 and data['tsi_position'][-1] ==1: data1 = data[(data['tsi_signal'] == 1) & (data['tsi_position'] ==1)] d =pd.DataFrame( data1.iloc[-1,:]).T # buy_p.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'g_bp':0, 'g_sp':0,'g_p_bp':d['Close'][0],'g_p_sp':0,'g_tdate': d['index'][0], 'strategy1_g_signal':0},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['tsi_signal'][-1] == 0 and data['tsi_position'][-1] ==0: data1= data[(data['tsi_signal'] == -1) & (data['tsi_position'] ==0)] d =pd.DataFrame( data1.iloc[-1,:]).T # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'g_bp':0, 'g_sp':0,'g_p_bp':0,'g_p_sp':d['Close'][0],'g_tdate': d['index'][0],'strategy1_g_signal':0},ignore_index = True) f.index = [tick] Close = data.loc[data.index[-1],'Close'] f['g_cur_p'] = [Close] dat = data.index[-1] # dat1 =dat.date() f['g_date'] =[dat] strategy1 = pd.concat([f,strategy1], axis=0) strategy1['g_name'] = 'Green Momentum' return strategy1 strategy1 = strategy_signal1(symbols) strategy1 ['g_b_r_p'] = np.where(strategy1['strategy1_g_signal'] ==1, strategy1['g_bp']+strategy1['g_bp']*0.005,'Not In Buy Range') strategy1['g_b_r_m'] = np.where(strategy1['strategy1_g_signal'] ==1, strategy1['g_bp'] - strategy1['g_bp']*0.005,'Not In Buy Range') strategy1['g_b_zone'] = np.where(strategy1['strategy1_g_signal'] ==1, 'Yes','No') strategy1=strategy1.sort_index() strategy1[['g_bp','g_sp','g_p_bp','g_p_sp','g_cur_p']]= strategy1[['g_bp','g_sp','g_p_bp','g_p_sp','g_cur_p']].applymap("{0:,.2f}".format) return strategy1 from datetime import datetime as dt import datetime today = dt.today() end = today-datetime.timedelta(1) #start = '2021-03-01' start = today-datetime.timedelta(200) # today = dt.today() # yesterday = today-datetime.timedelta(1) # end = (yesterday.year, yesterday.month, yesterday.day) squeeze_ticker strategy1_signal = get_strategy1(symbols) import matplotlib.image as mpimg import matplotlib.pyplot as plt # Read Images img1= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_buy.jpg') img2= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_downtrend.jpg') img3= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_no_signal.jpg') img4= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_sell.jpg') img5= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_sideways.jpg') img6= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_uptrend.jpg') # Output Images def get_pa_signal(symblos1): for ticker in symblos1: fig = plt.figure(figsize=(10,10)) plt.imshow(img1) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\greenline_pa_icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_greenline_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() #dr= 'https://3arbzfbsh-cname-us.ngrok.io' g_pa_signal_url = pd.DataFrame() for ticker in symblos1: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/greenline_pa_icon/"+ticker+"_pa_greenline_signal.jpg" tick = ticker image['g_PA_image_buy_sell']=[ dr] image.index =[tick] g_pa_signal_url = pd.concat([g_pa_signal_url ,image], axis =0) g_pa_signal_url=g_pa_signal_url.sort_index() return g_pa_signal_url symblos1 = list(strategy1_signal.index) g_pa_signal_url = get_pa_signal(symblos1) strategy1 =strategy1_signal.merge(g_pa_signal_url, left_index=True, right_index=True) g_pa_signal_url .to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\greenline_pa_signal_url.csv') strategy1.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\startegy1.csv') ##########################################strategy2##### def get_strategy2(symbols): pd.options.display.float_format = "{:.2f}".format def get_signal2(ticker, start, end): # from datetime import datetime # start =list(start) # end = list(end) # finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") # # Stock candles # start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # res = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) # stock = pd.DataFrame(res) # stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'High', 'l':'Low', 'c':'Close', 's':'status', 'v':'volumn'}) # stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') # stock = stock.set_index('Date') import requests api_key = '86dd63f6b8ae774b061232685b78eb52' 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() data2 =stock.copy() # SUPERTREND CALCULATION def get_supertrend(high, low, Close, lookback, multiplier): # ATR tr1 = pd.DataFrame(high - low) tr2 = pd.DataFrame(abs(high - Close.shift(1))) tr3 = pd.DataFrame(abs(low - Close.shift(1))) frames = [tr1, tr2, tr3] tr = pd.concat(frames, axis = 1, join = 'inner').max(axis = 1) atr = tr.ewm(lookback).mean() # H/L AVG AND BASIC UPPER & LOWER BAND hl_avg = (high + low) / 2 upper_band = (hl_avg + multiplier * atr).dropna() lower_band = (hl_avg - multiplier * atr).dropna() # FINAL UPPER BAND final_bands = pd.DataFrame(columns = ['upper', 'lower']) final_bands.iloc[:,0] = [x for x in upper_band - upper_band] final_bands.iloc[:,1] = final_bands.iloc[:,0] for i in range(len(final_bands)): if i == 0: final_bands.iloc[i,0] = 0 else: if (upper_band[i] < final_bands.iloc[i-1,0]) | (Close[i-1] > final_bands.iloc[i-1,0]): final_bands.iloc[i,0] = upper_band[i] else: final_bands.iloc[i,0] = final_bands.iloc[i-1,0] # FINAL LOWER BAND for i in range(len(final_bands)): if i == 0: final_bands.iloc[i, 1] = 0 else: if (lower_band[i] > final_bands.iloc[i-1,1]) | (Close[i-1] < final_bands.iloc[i-1,1]): final_bands.iloc[i,1] = lower_band[i] else: final_bands.iloc[i,1] = final_bands.iloc[i-1,1] # SUPERTREND supertrend = pd.DataFrame(columns = [f'supertrend_{lookback}']) supertrend.iloc[:,0] = [x for x in final_bands['upper'] - final_bands['upper']] for i in range(len(supertrend)): if i == 0: supertrend.iloc[i, 0] = 0 elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 0] and Close[i] < final_bands.iloc[i, 0]: supertrend.iloc[i, 0] = final_bands.iloc[i, 0] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 0] and Close[i] > final_bands.iloc[i, 0]: supertrend.iloc[i, 0] = final_bands.iloc[i, 1] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 1] and Close[i] > final_bands.iloc[i, 1]: supertrend.iloc[i, 0] = final_bands.iloc[i, 1] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 1] and Close[i] < final_bands.iloc[i, 1]: supertrend.iloc[i, 0] = final_bands.iloc[i, 0] supertrend = supertrend.set_index(upper_band.index) supertrend = supertrend.dropna()[1:] # ST UPTREND/DOWNTREND upt = [] dt = [] Close = Close.iloc[len(Close) - len(supertrend):] for i in range(len(supertrend)): if Close[i] > supertrend.iloc[i, 0]: upt.append(supertrend.iloc[i, 0]) dt.append(np.nan) elif Close[i] < supertrend.iloc[i, 0]: upt.append(np.nan) dt.append(supertrend.iloc[i, 0]) else: upt.append(np.nan) dt.append(np.nan) st, upt, dt = pd.Series(supertrend.iloc[:, 0]), pd.Series(upt), pd.Series(dt) upt.index, dt.index = supertrend.index, supertrend.index return st, upt, dt data2['st'], data2['s_upt'], data2['st_dt'] = get_supertrend(data2['High'], data2['Low'], data2['Close'], 9, 2) #data2 = data2[1:] # SUPERTREND STRATEGY def implement_st_strategy(prices, st): buy_price = [] sell_price = [] st_signal = [] signal = 0 for i in range(len(st)): if st[i-1] > prices[i-1] and st[i] < prices[i]: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 st_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) elif st[i-1] < prices[i-1] and st[i] > prices[i]: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 st_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) return buy_price, sell_price, st_signal buy_price, sell_price, st_signal = implement_st_strategy(data2['Close'], data2['st']) # GENERATING STOCK POSITION position = [] for i in range(len(st_signal)): if st_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data2['Close'])): if st_signal[i] == 1: position[i] = 1 elif st_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data2['Close'] st = data2['st'] st_signal = pd.DataFrame(st_signal).rename(columns = {0:'st_signal'}).set_index(data2.index) position = pd.DataFrame(position).rename(columns = {0:'st_position'}).set_index(data2.index) frames_daat2 = [Close_price, st, st_signal, position] strategy2 = pd.concat(frames_daat2 , join = 'inner', axis = 1) # BACKTESTING strategy2['ret'] =pd.DataFrame(strategy2['Close'].pct_change()) strategy2['st_returns'] = strategy2['ret']*strategy2['st_position'] return strategy2 def strategy_signal2(symbols): strategy2 = pd.DataFrame() for tick in symbols: data = get_signal2(tick, start, end ) f =pd.DataFrame(columns = ['p_bp','p_sp','p_p_bp','p_p_sp','p_tdate' ,'strtategy2_trend_signal']) if data['st_signal'][-1] == 1 and data['st_position'][-1] ==1: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # df.index = pd.to_datetime(df.index).strftime('%d-%m-%Y') # d.index = d.index.strftime('%d/%m/%Y') d = d.reset_index() f= f.append({'p_bp':d['Close'][0], 'p_sp':0,'p_p_bp':0,'p_p_sp':0,'p_tdate': d['index'][0],'strtategy2_trend_signal':1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # date = d.index.values.tolist() elif data['st_signal'][-1] == -1 and data['st_position'][-1] ==0: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # d.index = d.index.strftime('%d/%m/%Y') d = d.reset_index() f= f.append({'p_bp':0, 'p_sp':d['Close'][0],'p_p_bp':0,'p_p_sp':0,'p_tdate': d['index'][0],'strtategy2_trend_signal':-1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['st_signal'][-1] == 0 and data['st_position'][-1] ==1: data1 = data[(data['st_signal'] == 1) & (data['st_position'] ==1)] d =pd.DataFrame( data1.iloc[-1,:]).T # buy_price.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'p_bp':0, 'p_sp':0,'p_p_bp':d['Close'][0],'p_p_sp':0,'p_tdate': d['index'][0], 'strtategy2_trend_signal':0},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['st_signal'][-1] == 0 and data['st_position'][-1] ==0: data1= data[(data['st_signal'] == -1) & (data['st_position'] ==0)] d =pd.DataFrame( data1.iloc[-1,:]).T d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # d.index = d.index.strftime('%d/%m/%Y') d = d.reset_index() f= f.append({'p_bp':0, 'p_sp':0,'p_p_bp':0,'p_p_sp':d['Close'][0],'p_tdate': d['index'][0],'strtategy2_trend_signal':0},ignore_index = True) f.index = [tick] Close = data.loc[data.index[-1],'Close'] f['p_cur_p'] = [Close] dat = data.index[-1] # dat1 =dat.date() f['p_date'] =[dat] strategy2 = pd.concat([f,strategy2], axis=0) strategy2['p_name'] = 'Purple Trend' return strategy2 strategy2 = strategy_signal2(symbols) strategy2 ['p_b_r_p'] = np.where(strategy2['strtategy2_trend_signal'] ==1, strategy2['p_bp']+strategy2['p_bp']*0.005,'Not In Buy Range') strategy2['p_b_r_m'] = np.where(strategy2['strtategy2_trend_signal'] ==1, strategy2['p_bp'] -strategy2['p_bp']*0.005,'Not In Buy Range') strategy2['g_b_zon'] = np.where(strategy2['strtategy2_trend_signal'] ==1, 'Yes','No') strategy2=strategy2.sort_index() strategy2[['p_bp','p_sp','p_p_bp','p_p_sp','p_cur_p']]=strategy2[['p_bp','p_sp','p_p_bp','p_p_sp','p_cur_p']].applymap("{0:,.2f}".format) return strategy2 # import datetime # dt_format="%d%m%Y" # my_date = datetime.date.today() # datetime.datetime.strptime(my_date.strftime(dt_format), dt_format) # from datetime import datetime as dt # import datetime # yesterday = today-datetime.timedelta(1) # start = (2020,3,1) # end = (yesterday.year, yesterday.month, yesterday.day) # today = dt.today() # end = today-datetime.timedelta(1) # #start = '2021-03-01' # start = today-datetime.timedelta(200) strategy2_signal = get_strategy2(symbols) def get_pa_signal2(strategy2_symbol): for ticker in strategy2_symbol: img1= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_buy.jpg') fig = plt.figure(figsize=(10,10)) plt.imshow(img1) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\purpleline_pa_icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_purpleline_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() #dr= 'https://3arbzfbsh-cname-us.ngrok.io' p_pa_signal_url = pd.DataFrame() for ticker in strategy2_symbol: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/purpleline_pa_icon/"+ticker+"_pa_purpleline_signal.jpg" tick = ticker image['p_PA_image_buy_sell']=[ dr] image.index =[tick] p_pa_signal_url = pd.concat([p_pa_signal_url ,image], axis =0) return p_pa_signal_url strategy2_symbol = list(strategy2_signal.index) p_pa_signal_url = get_pa_signal2(strategy2_symbol) p_pa_signal_url= p_pa_signal_url.sort_index() strategy2 =strategy2_signal.merge(p_pa_signal_url, left_index=True, right_index=True) #strategy2 =strategy2.round(2) # import numpy as np # strategy2 = np.round(strategy2, decimals=2) # strategy2[['p_bp','p_sp','p_p_bp','p_p_sp']] =strategy2[['p_bp','p_sp','p_p_bp','p_p_sp']] .astype(float) strategy2.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\startegy2.csv') p_pa_signal_url .to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\purpleline_pa_signal_url.csv') ##############################strategy3######################### def get_strategy3(symbols): def get_signal3(ticker, start, end): # Setup client # Stock candles # ticker = 'SPY' import requests api_key = '86dd63f6b8ae774b061232685b78eb52' 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'}) # from datetime import datetime # finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") # start =list(start) # end = list(end) # # Stock candles # start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # res = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) # stock = pd.DataFrame(res) # stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'High', 'l':'Low', 'c':'Close', 's':'status', 'v':'volumn'}) # stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') stock = stock.set_index('Date') stock= stock.sort_index() data3 = stock.copy() def get_di(data, lookback): ma = data.rolling(lookback).mean() di = ((data - ma) / ma) * 100 return di data3['di_14'] = get_di(data3['Close'], 14) data3 = data3.dropna() def implement_di_strategy(prices, di): buy_price = [] sell_price = [] di_signal = [] signal = 0 for i in range(len(prices)): if di[i-4] < 0 and di[i-3] < 0 and di[i-2] < 0 and di[i-1] < 0 and di[i] > 0: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 di_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) elif di[i-4] > 0 and di[i-3] > 0 and di[i-2] > 0 and di[i-1] > 0 and di[i] < 0: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 di_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) return buy_price, sell_price, di_signal buy_price, sell_price, di_signal = implement_di_strategy(data3['Close'], data3['di_14']) # STOCK POSITION position = [] for i in range(len(di_signal)): if di_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data3['Close'])): if di_signal[i] == 1: position[i] = 1 elif di_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data3['Close'] di = data3['di_14'] di_signal = pd.DataFrame(di_signal).rename(columns = {0:'di_signal'}).set_index(data3.index) position = pd.DataFrame(position).rename(columns = {0:'di_position'}).set_index(data3.index) frames = [Close_price, di, di_signal, position] strategy3 = pd.concat(frames, join = 'inner', axis = 1) # BACKTESTING strategy3['ret'] =pd.DataFrame(strategy3['Close'].pct_change()) strategy3['di_returns'] = strategy3['ret']*strategy3['di_position'] return strategy3 def strategy_signal3(symbols): strategy3 = pd.DataFrame() for tick in symbols: data = get_signal3(tick, start, end ) f =pd.DataFrame(columns = ['b_bp','b_sp','b_p_bp','b_p_sp','b_tdate' ,'strategy3_b_signal']) if data['di_signal'][-1] == 1 and data['di_position'][-1] ==1: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'b_bp':d['Close'][0], 'b_sp':0,'b_p_bp':0,'b_p_sp':0,'b_tdate': d['index'][0],'strategy3_b_signal':1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # date = d.index.values.tolist() elif data['di_signal'][-1] == -1 and data['di_position'][-1] ==0: d =pd.DataFrame( data.iloc[-1,:]).T # buy_price.append(d['Close'].values) #d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'b_bp':0, 'b_sp':d['Close'][0],'b_p_bp':0,'b_p_sp':0,'b_tdate': d['index'][0],'strategy3_b_signal':-1},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['di_signal'][-1] == 0 and data['di_position'][-1] ==1: data1 = data[(data['di_signal'] == 1) & (data['di_position'] ==1)] d =pd.DataFrame( data1.iloc[-1,:]).T # buy_price.append(d['Close'].values) # d.index = d.index.strftime('%d/%m/%Y') d.index = pd.to_datetime(d.index, format='%Y-%m-%d') d = d.reset_index() f= f.append({'b_bp':0, 'b_sp':0,'b_p_bp':d['Close'][0],'b_p_sp':0,'b_tdate': d['index'][0], 'strategy3_b_signal':0},ignore_index = True) # d.index = pd.to_datetime(d.index, format='%Y-%m-%d') elif data['di_signal'][-1] == 0 and data['di_position'][-1] ==0: data1= data[(data['di_signal'] == -1) & (data['di_position'] ==0)] d =pd.DataFrame( data1.iloc[-1,:]).T d.index = pd.to_datetime(d.index, format='%Y-%m-%d') # d.index = d.index.strftime('%d/%m/%Y') d = d.reset_index() f= f.append({'b_bp':0, 'b_sp':0,'b_p_bp':0,'b_p_sp':d['Close'][0],'b_tdate': d['index'][0],'strategy3_b_signal':0},ignore_index = True) f.index = [tick] Close = data.loc[data.index[-1],'Close'] f['b_cur_p'] = [Close] dat = data.index[-1] # dat1 =dat.date() f['b_date'] =[dat] strategy3 = pd.concat([f,strategy3], axis=0) strategy3['b_name'] = 'Blue Oscillator' return strategy3 strategy3 =strategy_signal3(symbols) strategy3 ['b_b_r_p'] = np.where(strategy3['strategy3_b_signal'] ==1, strategy3['b_bp']+strategy3['b_bp']*0.005,'Not In Buy Range') strategy3['b_b_r_m'] = np.where(strategy3['strategy3_b_signal'] ==1, strategy3['b_bp'] -strategy3['b_bp']*0.005,'Not In Buy Range') strategy3['b_b_zon'] = np.where(strategy3['strategy3_b_signal'] ==1, 'Yes','No') strategy3 =strategy3.sort_index() strategy3[['b_bp','b_sp','b_p_bp','b_p_sp','b_cur_p']]= strategy3[['b_bp','b_sp','b_p_bp','b_p_sp','b_cur_p']].applymap("{0:,.2f}".format) return strategy3 # from datetime import datetime as dt # import datetime # today = dt.today() # end = today-datetime.timedelta(1) # #start = '2021-03-01' # start = today-datetime.timedelta(200) strategy3_signal = get_strategy3(symbols) # Output Images def get_pa_singal(strategy3_symbol): for ticker in strategy3_symbol: img1= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_buy.jpg') fig = plt.figure(figsize=(10,10)) plt.imshow(img1) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\green_blueline_pa_icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_blueline_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() #dr= 'https://3arbzfbsh-cname-us.ngrok.io' b_pa_signal_url = pd.DataFrame() for ticker in strategy3_symbol: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/green_blueline_pa_icon/"+ticker+"_pa_blueline_signal.jpg" tick = ticker image['b_PA_image_buy_sell']=[ dr] image.index =[tick] b_pa_signal_url = pd.concat([b_pa_signal_url ,image], axis =0) return b_pa_signal_url strategy3_symbol = list(strategy3_signal.index) b_pa_signal_url = get_pa_singal(strategy3_symbol) strategy3=strategy3_signal.merge(b_pa_signal_url , left_index=True, right_index=True) b_pa_signal_url .to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\blueline_pa_signal_url.csv') strategy3.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\startegy3.csv') return strategy1, strategy2, strategy3 strategy1, strategy2, strategy3 = all_strategy(etf_index) ################################################################################################################################ 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 matplotlib.dates as mpl_dates import numpy as np from datetime import * import seaborn as sns from datetime import datetime def get_rs_other_data(symbols): def get_data(ticker): from datetime import datetime as dt import datetime today = datetime.datetime.today() delta = dateutil.relativedelta.relativedelta(months=6) end = ( today-datetime.timedelta(1)).strftime('%Y-%m-%d') start = (today - delta).strftime('%Y-%m-%d') # 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() stock1 = stock.iloc[:-4,:] def get_optimum_clusters(df, saturation_point = 0.05): wcss = [] k_models = [] labels = [] days = df.shape[0] size = range(1,9) for i in size: # kmeans = KMeans(n_clusters = i, init='k-means++', max_iter=300, n_init=10, random_state=0) kmeans = KMeans(n_clusters = i, init='random', max_iter=300, n_init=10) kmeans.fit(df) wcss.append(kmeans.inertia_) # Sum of squared distances of samples to their Closest cluster center k_models.append(kmeans) labels.append(kmeans.labels_) # Compare differences in inertias until it's no more than saturation_point optimum_k = len(wcss)-1 for i in range(0, len(wcss)-1): diff = abs(wcss[i+1] - wcss[i]) if diff < saturation_point: optimum_k = i break # optimum_clusters = k_models[optimum_k] labels = labels[optimum_k] return labels Lows = pd.DataFrame(data = stock1, index = stock1.index, columns = ['Low']) Highs = pd.DataFrame(data = stock1, index = stock1.index, columns = ['High']) low_labels = get_optimum_clusters(Lows) high_labels = get_optimum_clusters(Highs) Lows['labels'] = pd.Series(low_labels, index = Lows.index) Highs['labels'] = pd.Series(high_labels, index = Highs.index) res = [max(Highs.loc[Highs.labels == i, 'High']) for i in np.unique(high_labels)] sup = [min(Lows.loc[Lows.labels == i, 'Low']) for i in np.unique(low_labels)] # pivotpoints last_day = stock.iloc[-1].to_frame().T not_pivots = last_day.columns last_day['center_gravity'] = (last_day['High'] + last_day['Low'] + last_day['Close'])/3 last_day['vol_up1'] = 2*last_day['center_gravity'] - last_day['Low'] last_day['vol_down1'] = 2*last_day['center_gravity'] - last_day['High'] last_day['vol_up2'] = last_day['center_gravity'] + (last_day['High'] - last_day['Low']) last_day['vol_down2'] = last_day['center_gravity'] - (last_day['High'] - last_day['Low']) last_day['vol_up3'] = last_day['center_gravity'] + 2*(last_day['High'] - last_day['Low']) last_day['vol_down3'] = last_day['center_gravity'] - 2*(last_day['High'] - last_day['Low']) Highs['Date'] = pd.to_datetime(Highs.index) Highs['Date'] = Highs['Date'].apply(mpl_dates.date2num) min_date_high = min(Highs.Date) max_date_high = max(Highs.Date) Lows['Date'] = pd.to_datetime(Lows.index) Lows['Date'] = Lows['Date'].apply(mpl_dates.date2num) min_date_low = min(Lows.Date) max_date_low = max(Lows.Date) Highs['res'] = 0 for i in range(len(Highs)): if Highs['High'][i] in res: Highs['res'][i]= 1 Lows['sup'] = 0 for i in range(len(Lows)): if Lows['Low'][i] in sup: Lows['sup'][i]= 1 resistance= pd.DataFrame() for i in range(len(Highs)): if Highs['res'][i] ==1: current_res = Highs.iloc[i,:] resistance = resistance.append(current_res) support = pd.DataFrame() for i in range(len(Lows)): if Lows['sup'][i] ==1: current_sup = Lows.iloc[i,:] support = support.append(current_sup) rec_res = resistance['High'][-1] rec_sup = support['Low'][-1] last_day['recent_support'] = rec_sup last_day['recent_resistance'] = rec_res tick = ticker last_day.index = [tick] return last_day def strategy_signal4(symbols): strategy_data= pd.DataFrame() for tick in symbols: data = get_data( tick) strategy_data = pd.concat([strategy_data,data], axis=0) return strategy_data # today = dt.today() support_resistance_data= strategy_signal4(symbols) support_resistance_data = support_resistance_data[['center_gravity','vol_up1','vol_up2','vol_up3','vol_down1', "vol_down2" , "vol_down3","recent_support","recent_resistance"]] # strategy_sum1 = pd.read_csv(r'C:\Users\jizha\Desktop\seabridge_datapool\support_resistop_last_peak_data.csv') # strategy_sum1 =strategy_sum1.rename({'Unnamed: 0':'symbol'}, axis =1) # strategy_sum1= strategy_sum1.rename({'pivot':'center_gravity'}, axis =1) #####################stop loss#################################### def get_data2(ticker): from datetime import datetime as dt import datetime today = datetime.datetime.today() delta = dateutil.relativedelta.relativedelta(months=6) end = ( today-datetime.timedelta(1)).strftime('%Y-%m-%d') start = (today - delta).strftime('%Y-%m-%d') 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() def get_stop(stock): # pivotpoints last_day = stock.iloc[-1].to_frame().T not_pivots = last_day.columns last_day['center_gravity'] = (last_day['High'] + last_day['Low'] + last_day['Close'])/3 last_day['vol_up1'] = 2*last_day['center_gravity'] - last_day['Low'] last_day['vol_down1'] = 2*last_day['center_gravity'] - last_day['High'] last_day['vol_up2'] = last_day['center_gravity'] + (last_day['High'] - last_day['Low']) last_day['vol_down2'] = last_day['center_gravity'] - (last_day['High'] - last_day['Low']) last_day['vol_up3'] = last_day['center_gravity'] + 2*(last_day['High'] - last_day['Low']) last_day['vol_down3'] = last_day['center_gravity'] - 2*(last_day['High'] - last_day['Low']) tick = ticker last_day.index = [tick] last_day['stop_loss'] = (last_day['vol_down2'] +last_day['vol_down3'] )/2 last_day['stop_loss_pct'] = (last_day['Close']- last_day['stop_loss'])/last_day['Close']*100 if last_day['stop_loss_pct'][-1] >3.5: last_day['stop_loss'][-1]= (last_day['vol_down1'][-1] +last_day['Close'][-1] )/2 return last_day last_day = get_stop(stock) ema_s = 13 ema_l = 21 stock["EMA_S"] = stock.Close.ewm(span = ema_s, min_periods = ema_s).mean() stock["EMA_L"] = stock.Close.ewm(span = ema_l, min_periods = ema_l).mean() # display(stock.head(30)) def get_ATR(stock, n): hl = stock.High - stock.Low hc = abs(stock.High - stock.Close.shift(1)) lc = abs(stock.Low - stock.Close.shift(1)) stock['TR'] = np.maximum(np.maximum(hl, hc), lc) stock['ATR'] = stock.TR.rolling(n).mean() return stock stock = get_ATR(stock, 20) stock = stock.dropna() mul =2 stock['ATR_stop'] = (stock.Close - mul * stock.ATR).shift(1) # ATR_stop for current day rec_trailingstoploss = stock.loc[stock.index[-1],'ATR_stop'] rec_stoploss = last_day.loc[last_day.index[-1],'stop_loss'] atr = stock.loc[stock.index[-1],'ATR'] strategy_data2=pd.DataFrame() tick = ticker strategy_data2 ['recent_stoploss'] =[rec_stoploss] strategy_data2['recent_trailingstoploss'] = [rec_trailingstoploss] strategy_data2['atr'] = [atr] strategy_data2.index = [tick] return strategy_data2 def strategy_signal5(symbols): strategy_data3= pd.DataFrame() for ticker in symbols: data = get_data2( ticker) strategy_data3= pd.concat([strategy_data3,data], axis=0) return strategy_data3 stop_loss_data = strategy_signal5(symbols) #stop_loss = strategy_signal5(symbols) # n= 4# number of groups # groups = np.array_split(symbols, n) # import time # stop_loss_data = pd.DataFrame() # for symbol in groups: # f= strategy_signal5(symbol) # stop_loss_data = pd.concat([f, stop_loss_data], axis =0) # time.sleep(60) #stop_loss_data .to_csv(r'C:\Users\jizha\Desktop\seabridge_datapool1\stop_loss_data.csv') #####################last peak price################### def get_data3(ticker, start, end): # from datetime import datetime # start =list(start) # end = list(end) # finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") # # Stock candles # start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # res = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) # stock = pd.DataFrame(res) # stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'High', 'l':'Low', 'c':'Close', 's':'status', 'v':'volume'}) # stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') # stock = stock.set_index('Date') from datetime import datetime as dt import datetime import dateutil today = datetime.datetime.today() delta = dateutil.relativedelta.relativedelta(months=6) end = ( today-datetime.timedelta(1)).strftime('%Y-%m-%d') start = (today - delta).strftime('%Y-%m-%d') 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() df =stock.copy() df.drop(df[df["volume"]<1000].index, inplace=True) df.index = pd.to_datetime(df.index) dfday=df.groupby(pd.Grouper(freq="D"))["High"].max() now = datetime.now() glDate=0 lastGLV=0 currentDate="" curentGLV=0 for index, value in dfday.items(): if value > curentGLV: curentGLV=value currentDate=index counter=0 if value < curentGLV: counter=counter+1 if counter==5 and ((index.day != now.day) or (index.month != now.month)): if curentGLV != lastGLV: print(curentGLV) glDate=currentDate lastGLV=curentGLV counter=0 if lastGLV==0: last_peak = ticker+" has not formed a green line yet" last_peak_date = 0 else: last_peak = lastGLV last_peak_date = glDate strategy_data6 =pd.DataFrame() strategy_data6['last_peak'] = [last_peak] strategy_data6['last_peakday'] = [last_peak_date] tick = ticker strategy_data6.index= [tick] return strategy_data6 def strategy_signal6(symbols): from datetime import datetime as dt import datetime today = dt.today() #end = today-datetime.timedelta(1) #start = '2021-03-01' start = (2021,3,1) # today = dt.today() yesterday = today-datetime.timedelta(1) end = (yesterday.year, yesterday.month, yesterday.day) strategy_data7= pd.DataFrame() for tick in symbols: data = get_data3( tick, start, end ) strategy_data7 = pd.concat([strategy_data7,data], axis=0) return strategy_data7 last_peak_data = strategy_signal6(symbols) ########################################## #strategy= strategy.sort_index() support_resistance_data = support_resistance_data.sort_index() stop_loss_data = stop_loss_data.sort_index() last_peak_data = last_peak_data.sort_index() strategy_sum = support_resistance_data .merge(stop_loss_data , left_index=True, right_index=True) strategy_sum1 =strategy_sum.merge(last_peak_data,left_index=True, right_index=True) strategy_sum1 = strategy_sum1 .sort_index() strategy_sum1[['center_gravity', 'vol_up1', 'vol_up2', 'vol_up3', 'vol_down1', 'vol_down2', 'vol_down3', 'recent_support', 'recent_resistance', 'recent_stoploss', 'recent_trailingstoploss', 'atr', 'last_peak' ]] = strategy_sum1[['center_gravity', 'vol_up1', 'vol_up2', 'vol_up3', 'vol_down1', 'vol_down2', 'vol_down3', 'recent_support', 'recent_resistance', 'recent_stoploss', 'recent_trailingstoploss', 'atr', 'last_peak' ]].applymap("{0:.2f}".format) return strategy_sum1 #pd.options.display.float_format = '{:,.2f}'.format strategy_sum1 = get_rs_other_data(etf_index) strategy_sum1.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\support_resistop_last_peak_data.csv') # #####################support_resiistance image########### # 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 # #pip install twine # import trendln # import importlib # import io # from os import path # from setuptools import setup, find_packages # import matplotlib.gridspec as gridspec # import matplotlib.patches as mpatches # import pandas as pd # from pandas_datareader import data # from matplotlib.pylab import rcParams # rcParams['figure.figsize'] = 25,16 # from datetime import datetime # #%matplotlib inline # import yfinance as yf # import quandl as qdl # from scipy.stats import linregress # import time # import datetime # def get_RS(ticker, start, end): # # Setup client # finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") # from datetime import datetime # start = list(start) # end = list(end) # # Stock candles # start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) # res_d = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) # stock = pd.DataFrame(res_d) # stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'High', 'l':'Low', 'c':'Close', 's':'status', 'v':'volumn'}) # stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') # stock = stock.set_index('Date') # stock= stock[['Close', 'High', 'Low','Open','volumn']] # def get_optimum_clusters(df, saturation_point = 0.05): # wcss = [] # k_models = [] # labels = [] # days = df.shape[0] # size = range(1,12) # for i in size: # # kmeans = KMeans(n_clusters = i, init='k-means++', max_iter=300, n_init=10, random_state=0) # kmeans = KMeans(n_clusters = i, init='random', max_iter=300, n_init=10) # kmeans.fit(df) # wcss.append(kmeans.inertia_) # Sum of squared distances of samples to their Closest cluster center # k_models.append(kmeans) # labels.append(kmeans.labels_) # # Compare differences in inertias until it's no more than saturation_point # optimum_k = len(wcss)-1 # for i in range(0, len(wcss)-1): # diff = abs(wcss[i+1] - wcss[i]) # if diff < saturation_point: # optimum_k = i # break # # optimum_clusters = k_models[optimum_k] # labels = labels[optimum_k] # return labels # Lows = pd.DataFrame(data = stock, index = stock.index, columns = ['Low']) # Highs = pd.DataFrame(data = stock, index = stock.index, columns = ['High']) # low_labels = get_optimum_clusters(Lows) # high_labels = get_optimum_clusters(Highs) # Lows['labels'] = pd.Series(low_labels, index = Lows.index) # Highs['labels'] = pd.Series(high_labels, index = Highs.index) # res = [max(Highs.loc[Highs.labels == i, 'High']) for i in np.unique(high_labels)] # sup = [min(Lows.loc[Lows.labels == i, 'Low']) for i in np.unique(low_labels)] # # pivotpoints # last_day = stock.iloc[-1].to_frame().T # not_pivots = list(last_day.columns) # last_day['CG'] = (last_day['High'] + last_day['Low'] + last_day['Close'])/3 # last_day['R1'] = 2*last_day['CG'] - last_day['Low'] # last_day['S1'] = 2*last_day['CG'] - last_day['High'] # last_day['R2'] = last_day['CG'] + (last_day['High'] - last_day['Low']) # last_day['S2'] = last_day['CG'] - (last_day['High'] - last_day['Low']) # last_day['R3'] = last_day['CG'] + 2*(last_day['High'] - last_day['Low']) # last_day['S3'] = last_day['CG'] - 2*(last_day['High'] - last_day['Low']) # pivots = [n for n in last_day.columns if n not in not_pivots] # pcols = ['green', 'blue', 'blue', 'red', 'red', 'black', 'black'] # # plot params setting # n = stock.shape[0] # number of days # xlim_min = 3 * (n/21) # xlim_max = 6 * (n/21) # rs_length = 1 * (n/21) # rs_text_x = 0.8 * (n/21) # rs_text_y_res = 0.2 * ((n/21)**0.5) # rs_text_y_sup = 0.5 * ((n/21)**0.5) # font_size = 7 # pivot_xmin = 1 * (n/21) # pivot_xmax = 2 * (n/21) # pivot_text_x = 2.5 * (n/21) # pivot_text_y = 0.5 * ((n/21)**0.05) # ######################trendline######################## # def get_trend_line(stock): # data0 = stock.copy() # data0['date_id'] = ((data0.index.date - data0.index.date.min())).astype('timedelta64[D]') # data0['date_id'] = data0['date_id'].dt.days + 1 # data1 = data0.copy() # while len(data1)>3: # reg = linregress( # x=data1['date_id'], # y=data1['High'], # ) # data1 = data1.loc[data1['High'] > reg[0] * data1['date_id'] + reg[1]] # reg = linregress( # x=data1['date_id'], # y=data1['High'], # ) # data0['high_trend'] = reg[0] * data0['date_id'] + reg[1] # # low trend line # data1 = data0.copy() # while len(data1)>3: # reg = linregress( # x=data1['date_id'], # y=data1['Low'], # ) # data1 = data1.loc[data1['Low'] < reg[0] * data1['date_id'] + reg[1]] # reg = linregress( # x=data1['date_id'], # y=data1['Low'], # ) # data0['low_trend'] = reg[0] * data0['date_id'] + reg[1] # return data0['high_trend'],data0['low_trend'] # up_trendline , low_trendline = get_trend_line(stock) # plt.rcParams['figure.figsize'] = [11, 7] # plt.rc('font', size =12) # #sns.set_theme() # # plt.rcParams['figure.facecolor'] = ' # # plt.rcParams['figure.figsize'] = [12, 7] # # plt.rc('font', size = 14) # # fig.patch.set_facecolor('white') # # ax.set_axisbelow(True) # # ax.yaxis.grid(color='gray', linestyle='dashed') # # sns.set_style("whitegrid") # # plt.rcParams['figure.figsize'] = [12, 7] # # plt.rc('font', size =14) # # sns.set_theme() # fig, ax = plt.subplots(dpi = 300 ) # ax.set_facecolor('white') # ax.ticklabel_format(style='plain') # # import seaborn as sns # #sns.set_style("whitegrid") # # plt.rcParams['figure.figsize'] = [11, 7] # # plt.rc('font', size =12) # # sns.set_theme() # # plt.rcParams['figure.facecolor'] = ' # # plt.rcParams['figure.figsize'] = [12, 7] # # plt.rc('font', size = 14) # # fig.patch.set_facecolor('white') # # ax.set_axisbelow(True) # # ax.yaxis.grid(color='gray', linestyle='dashed') # # sns.set_style("whitegrid") # stock['Date'] = pd.to_datetime(stock.index) # stock['Date'] = stock['Date'].apply(mpl_dates.date2num) # stock = stock.loc[:,['Date', 'Open', 'High', 'Low', 'Close']] # ax.set_xlim([stock.Date[0] - xlim_min, stock.Date[-1] + xlim_max]) # candlestick_ohlc(ax, stock.values, width = 0.05, # colorup = 'green', colordown = 'red', alpha=0.8) # date_format = mpl_dates.DateFormatter('%d %b %Y') # ax.xaxis.set_major_formatter(date_format) # fig.autofmt_xdate() # fig.tight_layout() # Highs['Date'] = pd.to_datetime(Highs.index) # Highs['Date'] = Highs['Date'].apply(mpl_dates.date2num) # min_date_high = min(Highs.Date) # max_date_high = max(Highs.Date) # for i in range(len(res)): # mu = Highs[Highs.High == res[i]].Date.values[0] # plt.hlines(res[i], xmin = mu - rs_length,\ # xmax = min(mu + rs_length, max_date_high),\ # color = 'red', linewidth =1, linestyle = '--') # plt.text(x = mu - rs_text_x, y = res[i] + rs_text_y_res,\ # s = str(res[i]), size = font_size) # Lows['Date'] = pd.to_datetime(Lows.index) # Lows['Date'] = Lows['Date'].apply(mpl_dates.date2num) # min_date_low = min(Lows.Date) # max_date_low = max(Lows.Date) # for i in range(len(sup)): # mu = Lows[Lows.Low == sup[i]].Date.values[0] # plt.hlines(sup[i], xmin = max(min_date_low, mu - rs_length),\ # xmax = min(mu + rs_length, max_date_low),\ # color = 'green', linewidth = 1, linestyle = '--') # plt.text(x = mu - rs_text_x, y = sup[i] - rs_text_y_sup,\ # s = str(sup[i]), size = font_size) # for i, col in enumerate(pivots): # plt.hlines(last_day[col].iloc[-1], xmin = stock.Date[-1] + pivot_xmin,\ # xmax = stock.Date[-1] + pivot_xmax,\ # color = pcols[i], linewidth = 1, linestyle = '--') # plt.text(x = stock.Date[-1] + pivot_text_x, y = last_day[col].iloc[-1] - pivot_text_y,\ # s = col + ': ' + str(round(last_day[col].iloc[-1], 2)),\ # color = pcols[i], size = font_size) # # up_trendline.plot(color = 'red') # # low_trendline.plot(color = 'blue') # up_trendline.plot(color = 'red',label =ticker+ ' Resistance') # low_trendline.plot(color = 'green',label =ticker+ ' Support') # plt.grid(True, linewidth=0.3, color='grey',linestyle='dashed') # ax.spines["right"].set_visible(False) # ax.spines["left"].set_visible(False) # plt.legend(loc='upper left') # plt.title(ticker+ ' Supports & Resistances',size = 14, y =0.95) # plt.xticks(rotation=30) # # plt.grid(b=None)C:\Users\jizha\Desktop\seabridge fintech\image_app # # img.save('/absolute/path/to/myphoto.jpg', 'JPEG') # # plt.savefig('test.jpg') # #ave_results_to = '/Users/S/Desktop/Results/' # #lt.savefig(save_results_to + 'image.png', dpi = 300) # #import os.path # path ="C:\\users\jizha\Desktop\seabridge fintech\image_app\\" # plt.savefig(path +'{}.jpg'.format(ticker+"_resistance_support"), dpi =300,pad_inches = 0,transparent=True) # plt.show() # from datetime import datetime as dt # import datetime # today = dt.today() # #end = today-datetime.timedelta(1) # #start = '2021-03-01' # #start = (2021,3,1) # yesterday = today-datetime.timedelta(1) # end = (yesterday.year, yesterday.month, yesterday.day) # for ticker in etf_index: # data1 = get_RS(ticker = ticker , start = (2021,1,21), end = end) ################resistance and resiistance ############# def get_image(symbols): image_strategy_return = pd.DataFrame() for ticker in symbols: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/profit_graph_app/" + ticker + "_Return.jpg" tick = ticker image['image_return'] =[ dr] image.index =[tick] image_strategy_return = pd.concat([image_strategy_return ,image], axis =0) ################resistance and resiistance ############# image_rs = pd.DataFrame() for ticker in symbols: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/image_app/" + ticker+"_resistance_support.jpg" tick = ticker image['image_resistance_support']=[ dr] image.index =[tick] image_rs = pd.concat([image_rs,image], axis =0) return image_strategy_return,image_rs image_strategy_return,image_rs = get_image(etf_index) image_rs = image_rs.sort_index() image_strategy_return = image_strategy_return.sort_index() image_data = image_rs.merge(image_strategy_return, left_index=True, right_index=True) image_data.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\image_resistance_support_return.csv') strategy_sum2 =strategy_sum1.merge(image_data,left_index=True, right_index=True) ################################################################## import pandas as pd import requests import matplotlib.pyplot as plt import numpy as np import requests import matplotlib.pyplot as plt from math import floor #import finnhub from termcolor import colored as cl plt.style.use('fivethirtyeight') plt.rcParams['figure.figsize'] = (20, 10) def get_trend_line(symbols): def get_trend (ticker, start, end): # stock = stock.set_index('Date') print(ticker) finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") # Stock candles from datetime import datetime start =list(start) end = list(end) from datetime import datetime start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) res = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) stock = pd.DataFrame(res) stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'high', 'l':'low', 'c':'close', 's':'status', 'v':'volumn'}) stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') stock = stock.set_index('Date') tsla = stock.copy() def get_ci(high, low, close, lookback): tr1 = pd.DataFrame(high - low).rename(columns = {0:'tr1'}) tr2 = pd.DataFrame(abs(high - close.shift(1))).rename(columns = {0:'tr2'}) tr3 = pd.DataFrame(abs(low - close.shift(1))).rename(columns = {0:'tr3'}) frames = [tr1, tr2, tr3] tr = pd.concat(frames, axis = 1, join = 'inner').dropna().max(axis = 1) atr = tr.rolling(1).mean() highh = high.rolling(lookback).max() lowl = low.rolling(lookback).min() ci = 100 * np.log10((atr.rolling(lookback).sum()) / (high - lowl)) / np.log10(lookback) return ci tsla['ci_14'] = get_ci(tsla['high'], tsla['low'], tsla['close'], 14) tsla = tsla.dropna() def get_adx(high, low, close, lookback): plus_dm = high.diff() minus_dm = low.diff() plus_dm[plus_dm < 0] = 0 minus_dm[minus_dm > 0] = 0 tr1 = pd.DataFrame(high - low) tr2 = pd.DataFrame(abs(high - close.shift(1))) tr3 = pd.DataFrame(abs(low - close.shift(1))) frames = [tr1, tr2, tr3] tr = pd.concat(frames, axis = 1, join = 'inner').max(axis = 1) atr = tr.rolling(lookback).mean() plus_di = 100 * (plus_dm.ewm(alpha = 1/lookback).mean() / atr) minus_di = abs(100 * (minus_dm.ewm(alpha = 1/lookback).mean() / atr)) dx = (abs(plus_di - minus_di) / abs(plus_di + minus_di)) * 100 adx = ((dx.shift(1) * (lookback - 1)) + dx) / lookback adx_smooth = adx.ewm(alpha = 1/lookback).mean() return plus_di, minus_di, adx_smooth tsla['plus_di'] = pd.DataFrame(get_adx(tsla['high'], tsla['low'], tsla['close'], 14)[0]).rename(columns = {0:'plus_di'}) tsla['minus_di'] = pd.DataFrame(get_adx(tsla['high'], tsla['low'], tsla['close'], 14)[1]).rename(columns = {0:'minus_di'}) tsla['adx'] = pd.DataFrame(get_adx(tsla['high'], tsla['low'], tsla['close'], 14)[2]).rename(columns = {0:'adx'}) tsla = tsla.dropna() tsla['ci_14_considating'] = np.where(tsla['ci_14'] >61.8,0,1) #tsla['ADX_considating'] = np.where(tsla['adx']<20,0,1) tsla['trend'] = np.where(tsla['plus_di'] >tsla['minus_di'],1,0) tsla['market_trend'] = '' for i in tsla.index: if tsla['ci_14_considating'][i] ==1 and tsla['trend'][i] ==1: tsla['market_trend'][i] = 1 elif tsla['ci_14_considating'][i] ==1 and tsla['trend'][i] ==0: tsla['market_trend'][i] = -1 elif tsla['ci_14_considating'][i] ==0: tsla['market_trend'][i] = 0 signal = tsla.iloc[-1,:] signal =pd.DataFrame(signal).T return signal def get_markett(symbols): from datetime import datetime as dt import datetime today = dt.today() yesterday = today-datetime.timedelta(1) end = (yesterday.year, yesterday.month, yesterday.day) start = (2021,2,15) market_trend = pd.DataFrame() for ticker in symbols: print(ticker) data = get_trend (ticker, start, end) da = pd.DataFrame(data['market_trend']) da.index = [ticker] market_trend = pd.concat([market_trend, da], axis =0) market_trend = market_trend.sort_index() return market_trend stock_priceaction = get_markett(symbols) # Output Images def get_pa_url(symbols): for ticker in symbols: import matplotlib.image as mpimg import matplotlib.pyplot as plt # Read Images img1= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_buy.jpg') img2= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_downtrend.jpg') img3= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_no_signal.jpg') img4= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_sell.jpg') img5= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_sideways.jpg') img6= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_uptrend.jpg') # Output Images if stock_priceaction['market_trend'][ticker] ==1: img6= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_uptrend.jpg') fig = plt.figure(figsize=(10,10)) plt.imshow(img6) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_trend_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() elif stock_priceaction['market_trend'][ticker] ==-1: img2= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_downtrend.jpg') fig = plt.figure(figsize=(10,10)) plt.imshow(img2) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_trend_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() elif stock_priceaction['market_trend'][ticker] ==0: img5= mpimg.imread(r'C:/Users/jizha/App-googlesheet/icon/pa_sideways.jpg') fig = plt.figure(figsize=(10,10)) plt.imshow(img5) plt.axis("off") # plt.grid(b=None) plt.subplots_adjust(top = 1, bottom = 0, right = 1, left = 0, hspace = 0, wspace = 0) path ="C:\\users\jizha\Desktop\seabridge fintech\icon\\" plt.savefig(path +'{}.jpg'.format(ticker+"_pa_trend_signal"), dpi = 72,transparent = True, bbox_inches = 'tight', pad_inches = 0) plt.show() priceaction_url = pd.DataFrame() for ticker in symbols: image = pd.DataFrame() dr = "https://3arbzfbsh-cname-us.ngrok.io/Desktop/seabridge%20fintech/icon/"+ticker+"_pa_trend_signal.jpg" image['priceaction_url']=[ dr] image.index =[ticker] priceaction_url = pd.concat([priceaction_url ,image], axis =0) priceaction_url = priceaction_url .sort_index() return priceaction_url trend_ticker= list(stock_priceaction.index) priceaction_url = get_pa_url(trend_ticker) return priceaction_url priceaction_url = get_trend_line(etf_index) strategy_sum3 = strategy_sum2 .merge(priceaction_url,right_index = True, left_index = True) # strategy_sum2.columns # strategy_sum2 = strategy_sum2.rename({'Unnamed: 0':'symbol'}, axis =1) # strategy_sum2 = strategy_sum2.set_index('symbol') 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 import requests from IPython.display import display #pd.set_option('display.float_format', '{:.2E}'.format) # return stocks def get_profile(symbols): 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 stocks = get_companyinfo(symbols) # df[['var1','var2']] = df[['var1','var2']].applymap("{0:.2f}".format) # df['var3'] = df['var3'].applymap(lambda x: "{0:.2f}%".format(x*100)) ######################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) earning_date= earning_date.sort_index() return earning_date earning_date = earning_date1(symbols) ##################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) da1 = da1.set_index('symbol') # da1[['pe_ratio(TTM)','eps(TTM)']] = da1[['pe_ratio(TTM)','eps(TTM)']] .applymap("{0:,.2f}".format) da1= da1.sort_index() return da1 # stocks['Date_access'] = datetime.now().strftime("%d/%m/%Y %H:%M:%S") da2 = eps_pe(symbols) def get_da3(symbols): api_key1 = '86dd63f6b8ae774b061232685b78eb52' da3 = pd.DataFrame(columns = ['symbol','52wk_high','52wk_low']) for ticker in symbols: #ticker = 'AAPL' bs5 = requests.get(f'https://financialmodelingprep.com/api/v3/quote/{ticker}?apikey={api_key1}').json() da3 =da3.append({'symbol':bs5[0]['symbol'],'52wk_high':bs5[0]['yearHigh'], '52wk_low':bs5[0]['yearLow']}, ignore_index = True) da3 = da3.set_index('symbol') da3[['52wk_high','52wk_low']] =da3 [['52wk_high','52wk_low']] .applymap("{0:,.2f}".format) da3 =da3.sort_index() return da3 da3 = get_da3(symbols) 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_data1 = stock_info_data.merge(da3, left_index=True, right_index=True) stock_info_data1 = stock_info_data1.sort_index() return stock_info_data1 stock_info_data = get_profile(etf_index) stock_info_data1 =stock_info_data.merge(strategy_sum3, right_index = True, left_index = True) ################strategyname ############### stock_info_data1 ['strb_na'] = '' stock_info_data1 ['strp_na'] = '' stock_info_data1 ['strg_na'] = '' for ticker in etf_index: print(ticker) if strategy1['strategy1_g_signal'][ticker] ==1: stock_info_data1 ['strg_na'][ticker] =1 else: stock_info_data1 ['strg_na'][ticker] = 0 if strategy2['strtategy2_trend_signal'][ticker] ==1: print(ticker) stock_info_data1 ['strp_na'][ticker] ='1' else: stock_info_data1 ['strp_na'] [ticker]= '0' if strategy3['strategy3_b_signal'][ticker] ==1: print(ticker) stock_info_data1 ['strb_na'][ticker] =1 else: stock_info_data1 ['strb_na'] [ticker]= 0 stock_info_data1.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\stock_info_data.csv') #stock_info_data = stock_info_data[['description','logourl', 'name','mktCap','volAvg','lastDiv', 'earning_date', 'pe_ratio(TTM)', 'eps(TTM)']] # df = strategy1.copy() # df =df.reset_index() # df=df.rename({"index":"symbol"}, axis = 1) # gc = pygsheets.authorize(service_file=r'C:/Users/jizha/App-googlesheet/watchlist1220.json') # sh = gc.open_by_key('1rqc9KNXnH-HYsrJhwG7kRdDGTDvCTEi5sGOIUMPIf9k') # wks = sh[2] # #update the first sheet with df, starting at cell B2. # wks.set_dataframe(df,(1,1)) ############################################################### # IMPORTING PACKAGES import pandas as pd import requests import matplotlib.pyplot as plt import numpy as np from math import floor from termcolor import colored as cl import pandas as pd import finnhub from sklearn.cluster import KMeans import mplfinance as mpf from mplfinance.original_flavor import candlestick_ohlc import matplotlib.dates as mpl_dates from datetime import * def strategy_performance(ticker, start, end): print(ticker) # EXTRACTING STOCK DATA # Setup client # Stock candles # start =[start] # end =[end] from datetime import datetime start =list(start) end = list(end) finnhub_client = finnhub.Client(api_key = "bt3efpf48v6tfcs816eg") start_time = int(datetime(start[0], start[1], start[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) end_time = int(datetime(end[0], end[1], end[2], 0, 0).replace(tzinfo = timezone.utc).timestamp()) res = finnhub_client.stock_candles(ticker, 'D', start_time, end_time) stock = pd.DataFrame(res) stock = stock.rename(columns = {'t':'Date', 'o':'Open', 'h':'High', 'l':'Low', 'c':'Close', 's':'status', 'v':'volumn'}) stock['Date'] = pd.to_datetime(stock['Date'], unit = 's') stock = stock.set_index('Date') data1 = stock.copy() # strategy CALCULATION def get_tsi(Close, long, short, signal): diff = Close - Close.shift(1) abs_diff = abs(diff) diff_smoothed = diff.ewm(span = long, adjust = False).mean() diff_double_smoothed = diff_smoothed.ewm(span = short, adjust = False).mean() abs_diff_smoothed = abs_diff.ewm(span = long, adjust = False).mean() abs_diff_double_smoothed = abs_diff_smoothed.ewm(span = short, adjust = False).mean() tsi = (diff_double_smoothed / abs_diff_double_smoothed) * 100 signal = tsi.ewm(span = signal, adjust = False).mean() # tsi = tsi[tsi.index >= '2020-01-01'].dropna() # signal = signal[signal.index >= '2020-01-01'].dropna() return tsi, signal data1['tsi'], data1['signal_line'] = get_tsi(data1['Close'], 25, 13, 12) # TRUE STRENGTH INDEX STRATEGY def implement_tsi_strategy(prices, tsi, signal_line): buy_price = [] sell_price = [] tsi_signal = [] signal = 0 for i in range(len(prices)): if tsi[i-1] < signal_line[i-1] and tsi[i] > signal_line[i]: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 tsi_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) elif tsi[i-1] > signal_line[i-1] and tsi[i] < signal_line[i]: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 tsi_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) tsi_signal.append(0) return buy_price, sell_price, tsi_signal buy_price, sell_price, tsi_signal = implement_tsi_strategy(data1['Close'], data1['tsi'], data1['signal_line']) # STOCK POSITION position = [] for i in range(len(tsi_signal)): if tsi_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data1['Close'])): if tsi_signal[i] == 1: position[i] = 1 elif tsi_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data1['Close'] tsi = data1['tsi'] signal_line = data1['signal_line'] tsi_signal = pd.DataFrame(tsi_signal).rename(columns = {0:'tsi_signal'}).set_index(data1.index) position = pd.DataFrame(position).rename(columns = {0:'tsi_position'}).set_index(data1.index) frames_data1 = [Close_price, tsi, signal_line, tsi_signal, position] strategy1 = pd.concat(frames_data1, join = 'inner', axis = 1) strategy1['ret'] =pd.DataFrame(strategy1['Close'].pct_change()) strategy1['tsi_returns'] = strategy1['ret'] *strategy1['tsi_position'] ####################second strategy###################### # SUPERTREND CALCULATION data2 =stock.copy() def get_supertrend(high, low, Close, lookback, multiplier): # ATR tr1 = pd.DataFrame(high - low) tr2 = pd.DataFrame(abs(high - Close.shift(1))) tr3 = pd.DataFrame(abs(low - Close.shift(1))) frames = [tr1, tr2, tr3] tr = pd.concat(frames, axis = 1, join = 'inner').max(axis = 1) atr = tr.ewm(lookback).mean() # H/L AVG AND BASIC UPPER & LOWER BAND hl_avg = (high + low) / 2 upper_band = (hl_avg + multiplier * atr).dropna() lower_band = (hl_avg - multiplier * atr).dropna() # FINAL UPPER BAND final_bands = pd.DataFrame(columns = ['upper', 'lower']) final_bands.iloc[:,0] = [x for x in upper_band - upper_band] final_bands.iloc[:,1] = final_bands.iloc[:,0] for i in range(len(final_bands)): if i == 0: final_bands.iloc[i,0] = 0 else: if (upper_band[i] < final_bands.iloc[i-1,0]) | (Close[i-1] > final_bands.iloc[i-1,0]): final_bands.iloc[i,0] = upper_band[i] else: final_bands.iloc[i,0] = final_bands.iloc[i-1,0] # FINAL LOWER BAND for i in range(len(final_bands)): if i == 0: final_bands.iloc[i, 1] = 0 else: if (lower_band[i] > final_bands.iloc[i-1,1]) | (Close[i-1] < final_bands.iloc[i-1,1]): final_bands.iloc[i,1] = lower_band[i] else: final_bands.iloc[i,1] = final_bands.iloc[i-1,1] # SUPERTREND supertrend = pd.DataFrame(columns = [f'supertrend_{lookback}']) supertrend.iloc[:,0] = [x for x in final_bands['upper'] - final_bands['upper']] for i in range(len(supertrend)): if i == 0: supertrend.iloc[i, 0] = 0 elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 0] and Close[i] < final_bands.iloc[i, 0]: supertrend.iloc[i, 0] = final_bands.iloc[i, 0] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 0] and Close[i] > final_bands.iloc[i, 0]: supertrend.iloc[i, 0] = final_bands.iloc[i, 1] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 1] and Close[i] > final_bands.iloc[i, 1]: supertrend.iloc[i, 0] = final_bands.iloc[i, 1] elif supertrend.iloc[i-1, 0] == final_bands.iloc[i-1, 1] and Close[i] < final_bands.iloc[i, 1]: supertrend.iloc[i, 0] = final_bands.iloc[i, 0] supertrend = supertrend.set_index(upper_band.index) supertrend = supertrend.dropna()[1:] # ST UPTREND/DOWNTREND upt = [] dt = [] Close = Close.iloc[len(Close) - len(supertrend):] for i in range(len(supertrend)): if Close[i] > supertrend.iloc[i, 0]: upt.append(supertrend.iloc[i, 0]) dt.append(np.nan) elif Close[i] < supertrend.iloc[i, 0]: upt.append(np.nan) dt.append(supertrend.iloc[i, 0]) else: upt.append(np.nan) dt.append(np.nan) st, upt, dt = pd.Series(supertrend.iloc[:, 0]), pd.Series(upt), pd.Series(dt) upt.index, dt.index = supertrend.index, supertrend.index return st, upt, dt data2['st'], data2['s_upt'], data2['st_dt'] = get_supertrend(data2['High'], data2['Low'], data2['Close'], 9, 2) #data2 = data2[1:] # SUPERTREND STRATEGY def implement_st_strategy(prices, st): buy_price = [] sell_price = [] st_signal = [] signal = 0 for i in range(len(st)): if st[i-1] > prices[i-1] and st[i] < prices[i]: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 st_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) elif st[i-1] < prices[i-1] and st[i] > prices[i]: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 st_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) st_signal.append(0) return buy_price, sell_price, st_signal buy_price, sell_price, st_signal = implement_st_strategy(data2['Close'], data2['st']) # SUPERTREND SIGNALS position = [] for i in range(len(st_signal)): if st_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data2['Close'])): if st_signal[i] == 1: position[i] = 1 elif st_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data2['Close'] st = data2['st'] st_signal = pd.DataFrame(st_signal).rename(columns = {0:'st_signal'}).set_index(data2.index) position = pd.DataFrame(position).rename(columns = {0:'st_position'}).set_index(data2.index) frames_daat2 = [Close_price, st, st_signal, position] strategy2 = pd.concat(frames_daat2 , join = 'inner', axis = 1) # BACKTESTING strategy2['ret'] =pd.DataFrame(strategy2['Close'].pct_change()) strategy2['st_returns'] = strategy2['ret']*strategy2['st_position'] # fig, ax = plt.subplots() # plt.plot(((1+strategy2['st_returns'][1:] ).cumprod()*10000), color = 'green') # plt.plot(((1+strategy2['ret'][1:]).cumprod()*10000), color = 'red') #ax.yaxis.set_major_formatter(FuncFormatter(lambda y, _: '{:.0%}'.format(y))) ##############strategy3######################## data3 = stock.copy() def get_di(data, lookback): ma = data.rolling(lookback).mean() di = ((data - ma) / ma) * 100 return di data3['di_14'] = get_di(data3['Close'], 14) data3 = data3.dropna() # DISPARITY INDEX PLOT def implement_di_strategy(prices, di): buy_price = [] sell_price = [] di_signal = [] signal = 0 for i in range(len(prices)): if di[i-4] < 0 and di[i-3] < 0 and di[i-2] < 0 and di[i-1] < 0 and di[i] > 0: if signal != 1: buy_price.append(prices[i]) sell_price.append(np.nan) signal = 1 di_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) elif di[i-4] > 0 and di[i-3] > 0 and di[i-2] > 0 and di[i-1] > 0 and di[i] < 0: if signal != -1: buy_price.append(np.nan) sell_price.append(prices[i]) signal = -1 di_signal.append(signal) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) else: buy_price.append(np.nan) sell_price.append(np.nan) di_signal.append(0) return buy_price, sell_price, di_signal buy_price, sell_price, di_signal = implement_di_strategy(data3['Close'], data3['di_14']) # STOCK POSITION position = [] for i in range(len(di_signal)): if di_signal[i] > 1: position.append(0) else: position.append(1) for i in range(len(data3['Close'])): if di_signal[i] == 1: position[i] = 1 elif di_signal[i] == -1: position[i] = 0 else: position[i] = position[i-1] Close_price = data3['Close'] di = data3['di_14'] di_signal = pd.DataFrame(di_signal).rename(columns = {0:'di_signal'}).set_index(data3.index) position = pd.DataFrame(position).rename(columns = {0:'di_position'}).set_index(data3.index) frames = [Close_price, di, di_signal, position] strategy3 = pd.concat(frames, join = 'inner', axis = 1) # BACKTESTING strategy3['ret'] =pd.DataFrame(strategy3['Close'].pct_change()) strategy3['di_returns'] = strategy3['ret']*strategy3['di_position'] def CAGR(DF): "function to calculate the Cumulative Annual Growth Rate of a trading strategy" df = DF.copy() df["cum_return"] = (1 + df["mon_ret"]).cumprod() n = len(df)/360 CAGR = (df["cum_return"].tolist()[-1])**(1/n) - 1 return CAGR def volatility(DF): "function to calculate annualized volatility of a trading strategy" df = DF.copy() vol = df["mon_ret"].std() * np.sqrt(360) return vol def sharpe(DF,rf): "function to calculate sharpe ratio ; rf is the risk free rate" df = DF.copy() sr = (CAGR(df) - rf)/volatility(df) return sr def max_dd(DF): "function to calculate max drawdown" df = DF.copy() df["cum_return"] = (1 + df["mon_ret"]).cumprod() df["cum_roll_max"] = df["cum_return"].cummax() df["drawdown"] = df["cum_roll_max"] - df["cum_return"] df["drawdown_pct"] = df["drawdown"]/df["cum_roll_max"] max_dd = df["drawdown_pct"].max() return max_dd def sortino(DF,rf): "function to calculate sortino ratio ; rf is the risk free rate" df = DF.copy() df["neg_ret"] = np.where(df["mon_ret"]<0,df["mon_ret"],0) neg_vol = df["neg_ret"].std() * np.sqrt(252) sr = (CAGR(df) - rf)/neg_vol return sr # def calmar(DF): # "function to calculate calmar ratio" # df = DF.copy() # clmr = CAGR(df)/max_dd(df) # return clmr strategy1_ret = pd.DataFrame(strategy1.loc[:,'tsi_returns']) strategy1_ret.columns = ['mon_ret'] strategy1_ret.dropna(inplace = True) strategy2_ret = pd.DataFrame(strategy2.loc[:,'st_returns']) strategy2_ret.columns = ['mon_ret'] strategy2_ret.dropna(inplace = True) strategy3_ret = pd.DataFrame(strategy3.loc[:,'di_returns']) strategy3_ret.columns = ['mon_ret'] strategy3_ret.dropna(inplace = True) strategy_b = pd.DataFrame(strategy1.loc[:,'ret']) strategy_b.columns = ['mon_ret'] strategy_b.dropna(inplace = True) #strategy1_ret =strategy1_ret.rename({'tsi_returns':"mon_ret"}) #traetgy1: 'Super Momentum'###########tsi_return green ############strategy3: Super Oscillator di_return green #strateg2:'Super Trend" purple st_return cagr1=CAGR(strategy1_ret) cagr2=CAGR(strategy2_ret) cagr3= CAGR(strategy3_ret) cagr_benchmark = CAGR(strategy_b) data =[{'cagr_green':cagr1, 'cagr_purple':cagr2, 'cagr_blue':cagr3, 'cagr_benchmark': cagr_benchmark}] da = pd.DataFrame(data) da.index= [ticker] vol1 =volatility(strategy1_ret) vol2 =volatility(strategy2_ret) vol3 = volatility(strategy3_ret) vol_benchmark = volatility(strategy_b) data2= [{'vol_green': vol1, 'vol_purple':vol2, 'vol_blue':vol3,'vlo_benchmark': vol_benchmark }] da3= pd.DataFrame(data2) da3.index= [ticker] sharp1 = sharpe(strategy1_ret,0.025) sharp2 = sharpe(strategy2_ret,0.025) sharp3 =sharpe(strategy3_ret,0.025) sharp_benchmark = sharpe(strategy_b,0.025) data3 = [{'sharp_green': sharp1, 'sharp_purple': sharp2, 'sharp_blue': sharp3, 'sharp_benchmark' : sharp_benchmark}] da4 = pd.DataFrame(data3) da4.index= [ticker] max_dd1 = max_dd(strategy1_ret) max_dd2 = max_dd(strategy2_ret) max_dd3 = max_dd(strategy3_ret) max_dd_benchmark = max_dd(strategy_b) data4 = [{'max_dd_green': max_dd1, 'max_dd_purple': max_dd2, 'max_dd_blue': max_dd3, 'max_dd_benchmark': max_dd_benchmark}] da5 = pd.DataFrame(data4) da5.index= [ticker] sortino1 = sortino(strategy1_ret,0.025) sortino2= sortino(strategy2_ret,0.025) sortino3= sortino(strategy3_ret,0.025) sortino_benchmark = sortino(strategy_b,0.025) data5 = [{'sortino_green':sortino1, 'sortino_purple': sortino2, 'sortino_blue' : sortino3,'sortino_benchmark ': sortino_benchmark}] da6 = pd.DataFrame(data5) da6.index= [ticker] # calmar1= calmar(strategy1_ret) # calmar2= calmar(strategy2_ret) # calmar3= calmar(strategy3_ret) # calmar_benchmark = calmar(strategy_b) # data7 = [{'calmar_green':calmar1, ' calmar_purple': calmar2, ' calmar_blue': calmar3,' calmar_benchmark': calmar_benchmark}] # da7 =pd.DataFrame(data6) # da7.index= [ticker] k= da.merge(da3, left_index=True, right_index=True) k1 = k.merge(da4, left_index=True, right_index=True) k2 = k1.merge(da5, left_index=True, right_index=True) kpi = k2.merge(da6, left_index=True, right_index=True) kpi.columns =kpi.columns.to_series().apply(lambda x: x.strip()) kpi[['sharp_green','sharp_purple','sharp_blue','sharp_benchmark','sortino_green','sortino_purple','sortino_blue','sortino_benchmark']] = kpi[['sharp_green','sharp_purple','sharp_blue','sharp_benchmark','sortino_green','sortino_purple','sortino_blue','sortino_benchmark']].applymap("{0:,.2f}".format) #kpi= kpi.astype(float) kpi[['cagr_green','cagr_purple','cagr_blue','cagr_benchmark','vol_green','vol_purple','vol_blue','vlo_benchmark','max_dd_green','max_dd_purple','max_dd_blue','max_dd_benchmark']] =kpi[['cagr_green','cagr_purple','cagr_blue','cagr_benchmark','vol_green','vol_purple','vol_blue','vlo_benchmark','max_dd_green','max_dd_purple','max_dd_blue','max_dd_benchmark']].applymap("{0:,.2%}".format) kpi=kpi.sort_index() return kpi # df[['var1','var2']] = df[['var1','var2']].applymap("{0:.2f}".format) # df['var3'] = df['var3'].applymap(lambda x: "{0:.2f}%".format(x*100)) # df[['var1','var2']].apply(lambda x: map(lambda x:'{:.2f}%'.format(x),x),axis=1) from datetime import datetime as dt import datetime start=(2010,1,21) today = dt.today() yesterday = today-datetime.timedelta(1) end = (yesterday.year, yesterday.month, yesterday.day) kpi = pd.DataFrame() for ticker in etf_index: data = strategy_performance( ticker , start,end) kpi= pd.concat([kpi, data ],axis =0) kpi.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\kpi_performance.csv') #kpi= kpi.astype(float) # kpi[['sharp_green','sharp_purple','sharp_blue','sharp_benchmark','sortino_green','sortino_purple','sortino_blue','sortino_benchmark']] = kpi[['sharp_green','sharp_purple','sharp_blue','sharp_benchmark','sortino_green','sortino_purple','sortino_blue','sortino_benchmark']].applymap("{0:,.2f}".format) # #kpi= kpi.astype(float) # kpi[['cagr_green','cagr_purple','cagr_blue','cagr_benchmark','vol_green','vol_purple','vol_blue','vlo_benchmark','max_dd_green','max_dd_purple','max_dd_blue','max_dd_benchmark']] =kpi[['cagr_green','cagr_purple','cagr_blue','cagr_benchmark','vol_green','vol_purple','vol_blue','vlo_benchmark','max_dd_green','max_dd_purple','max_dd_blue','max_dd_benchmark']].applymap("{0:,.2%}".format) stock_info_data2 =stock_info_data1.merge(kpi,right_index= True, left_index =True ) stock_info_data2['Ticker'] = stock_info_data2.index #stock_info_data2['prev_close'] =stock_info_data2['prev_close'].astype(float) stock_info_data2.to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\all_data.csv') 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\final_strategy_data_temporaly\three_strategy_buy_point_'+a+'_'+b+'_'+c+'.csv' ) stock_info_data2 .to_csv(r'C:\Users\jizha\Desktop\watchlist_datapool\market_index_data\all_data_'+a+'_'+b+'_'+c+'.csv') stock_info_data2[['description', 'logourl', 'name', 'mktCap', 'lastDiv', 'volAvg', 'price', 'Date_access', 'div_yiel', 'earning_date', 'pe_ratio(TTM)', 'eps(TTM)', '52wk_high', '52wk_low', 'center_gravity', 'vol_up1', 'vol_up2', 'vol_up3', 'vol_down1', 'vol_down2', 'vol_down3', 'atr', 'last_peak', 'last_peakday', 'image_resistance_support', 'image_return', 'priceaction_url', 'strb_na', 'strp_na', 'strg_na', 'cagr_green', 'cagr_purple', 'cagr_blue', 'cagr_benchmark', 'vol_green', 'vol_purple', 'vol_blue', 'vlo_benchmark', 'sharp_green', 'sharp_purple', 'sharp_blue', 'sharp_benchmark', 'max_dd_green', 'max_dd_purple', 'max_dd_blue', 'max_dd_benchmark', 'sortino_green', 'sortino_purple', 'sortino_blue', 'sortino_benchmark', 'Ticker']] = stock_info_data2[['description', 'logourl', 'name', 'mktCap', 'lastDiv', 'volAvg', 'price', 'Date_access', 'div_yiel', 'earning_date', 'pe_ratio(TTM)', 'eps(TTM)', '52wk_high', '52wk_low', 'center_gravity', 'vol_up1', 'vol_up2', 'vol_up3', 'vol_down1', 'vol_down2', 'vol_down3', 'atr', 'last_peak', 'last_peakday', 'image_resistance_support', 'image_return', 'priceaction_url', 'strb_na', 'strp_na', 'strg_na', 'cagr_green', 'cagr_purple', 'cagr_blue', 'cagr_benchmark', 'vol_green', 'vol_purple', 'vol_blue', 'vlo_benchmark', 'sharp_green', 'sharp_purple', 'sharp_blue', 'sharp_benchmark', 'max_dd_green', 'max_dd_purple', 'max_dd_blue', 'max_dd_benchmark', 'sortino_green', 'sortino_purple', 'sortino_blue', 'sortino_benchmark', 'Ticker']] .astype(str) stock_info_data2[['recent_support', 'recent_resistance', 'recent_stoploss', 'recent_trailingstoploss']] = stock_info_data2[['recent_support', 'recent_resistance', 'recent_stoploss', 'recent_trailingstoploss']].astype(float) import pandas as pd import numpy as np import requests import time # IMPORTING PACKAGES import pandas as pd import requests import matplotlib.pyplot as plt import numpy as np from math import floor from termcolor import colored as cl import pandas as pd import finnhub from sklearn.cluster import KMeans import mplfinance as mpf from mplfinance.original_flavor import candlestick_ohlc import matplotlib.dates as mpl_dates from datetime import * import pandas as pd import numpy as np import requests import json from pprint import pprint from airtable import Airtable # Function to validate Airtable arguments def validate_airtable_kwargs(kwarg, kwarg_name, prefix, char_length=17, print_messages=True): valid_status = True if len(kwarg) != char_length: if print_messages: print(f"⚠️ Caution: {kwarg_name} not standard length. Make sure it is {char_length} characters long.") valid_status = False if not kwarg.startswith(prefix): if print_messages: print(f"⚠️ Caution: {kwarg_name} doesn't start with '{prefix}'.") valid_status = False return valid_status # Function to identify errors in Airtable response def identify_errors(airtable_response): if 'error' in airtable_response: try: print(f"❌ {airtable_response['error']['type']} error: \"{airtable_response['error']['message']}\"") except Exception: print(f"❌ Error: {airtable_response['error']}") # Function to upload data to Airtable def airtable_upload(table, upload_data, typecast=False, pat=None, base_id=None, record_id=None): if pat is None: print("Enter Airtable Personal Access Token. \n *Find under Airtable Account Overview: https://airtable.com/account") pat = input() headers = { "Authorization": f"Bearer {pat}", 'Content-Type': 'application/json' } if base_id is None: print("Enter Airtable Base ID. \n *Find under Airtable API Documentation: https://airtable.com/api for specific base") base_id = input() url = f'https://api.airtable.com/v0/{base_id}/' path = url + table if record_id is not None: validate_airtable_kwargs(record_id, "Record ID", "rec") if type(upload_data) != dict: print("❌ Error: `upload_data` is not a dictionary.") return upload_dict = {"records": [{"fields": upload_data}], "typecast": typecast} if record_id is None else {"fields": upload_data, "typecast": True} upload_json = json.dumps(upload_dict) method = requests.post if record_id is None else requests.patch full_path = path if record_id is None else f"{path}/{record_id}" response = method(full_path, data=upload_json, headers=headers) airtable_response = response.json() if 'error' in airtable_response: identify_errors(airtable_response) return airtable_response # Function to upload a pandas DataFrame to Airtable def upload_pandas_dataframe(strategy1, table, pat, base_id): pandas_dicts = strategy1.to_dict(orient="records") for record in pandas_dicts: airtable_upload(table, record, pat=pat, base_id=base_id) base_id = 'appcGKXxGyOHf6X7Z' table_name ='tblsrTYMjJMMpCRx3' pat = 'patBpzm9z4qoKaf7U.9e8eab257e0762a2156b826998d3bd5db7b0baf97263ab311a27616e51e25916' # Replace with your actual Personal Access Token # Headers for authentication with PAT headers = { 'Authorization': f'Bearer {pat}', 'Content-Type': 'application/json' } def get_all_records(base_id, table_name, headers): url = f'https://api.airtable.com/v0/{base_id}/{table_name}' all_records = [] offset = None while True: params = {} if offset: params['offset'] = offset response = requests.get(url, headers=headers, params=params) if response.status_code == 200: data = response.json() all_records.extend(data['records']) offset = data.get('offset') if not offset: break else: raise Exception(f"Failed to fetch records: {response.text}") return all_records def delete_records(base_id, table_name, records, headers): for record in records: record_id = record['id'] url = f'https://api.airtable.com/v0/{base_id}/{table_name}/{record_id}' response = requests.delete(url, headers=headers) if response.status_code not in [200, 204]: print(f"Failed to delete record {record_id}: {response.text}") # Fetch all records records = get_all_records(base_id, table_name, headers) # Delete all fetched records delete_records(base_id, table_name, records, headers) # # Function to upload a DataFrame to Airtable (simplified for demonstration) # def upload_dataframe(df, base_id, table_name, headers): # url = f'https://api.airtable.com/v0/{base_id}/{table_name}' # for _, row in df.iterrows(): # data = {"fields": row.to_dict()} # response = requests.post(url, headers=headers, json=data) # if response.status_code != 200: # raise Exception(f"Failed to upload data: {response.text}") # Assuming strategy2 is your DataFrame df1 = stock_info_data2.copy() df1 = df1.reset_index() df1 = df1.rename({"index": "symbol"}, axis=1) s1= upload_pandas_dataframe(df1, table_name, pat, base_id) # curl -v -H "Authorization: Bearer patBpzm9z4qoKaf7U" "https://api.airtable.com/v0/app6TwcWc5AuRHIpg/tbl3my99eu3hhp7Lj"