def get_period(self): sql_statement = f"SELECT ticker FROM finance_quant.{self.stock_table_list_name};" period_dict = { "1d" : [], "5d" : [], "1mo" : [], "3mo" : [], "6mo" : [], "1y" : [] } try: cursor = self.conn.cursor() cursor.execute(sql_statement) temp_data = cursor.fetchall() except connect.errors as err: database().insert_error_log(f"ERROR FETCHING ALL SYMBOLS FOR PERIOD LENGTH SEARCH") print("Finding Ideal Period Length for each Symbol...") # temp_data = [('GOOG',),('AAPL',),('AACG',),('BGCP',)] l = len(temp_data) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for z, ticker in enumerate(temp_data): if ticker[0] == "ZXYZ.A": continue sql_statement = f"SELECT dt FROM {ticker[0]}_STK ORDER BY dt DESC LIMIT 1;" recent_date = None try: cursor = self.conn.cursor() cursor.execute(sql_statement) recent_date = cursor.fetchone() except connect.errors as error: database().insert_error_log(f"ERROR FINDING IDEAL PERIOD FOR TECHNICAL DATA INTO DATABASE FOR {ticker[0]}") current_date = datetime.datetime.now() if recent_date == None or recent_date == "": period_dict["1y"].append(ticker[0]) else: difference = current_date - recent_date[0] if difference.days < 1: continue elif difference.days < 2: period_dict["1d"].append(ticker[0]) elif difference.days < 6: period_dict["5d"].append(ticker[0]) elif difference.days <32: period_dict["1mo"].append(ticker[0]) elif difference.days < 94: period_dict["3mo"].append(ticker[0]) elif difference.days < 190: period_dict["6mo"].append(ticker[0]) elif difference.days < 366: period_dict["1y"].append(ticker[0]) utility.printProgressBar(z + 1, l, prefix='Progress:', suffix='Complete', length=50) return period_dict
def gather_headlines(self): l = len(self.tickers) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for z, ticker in enumerate(self.tickers): response = None data = None url = self.finwiz_url + "=" + ticker[0] try: req = Request(url=url, headers={'user-agent': 'my-app/0.0.1'}) response = urlopen(req) # Read the contents of the file into 'html' html = BeautifulSoup(response, features='lxml') # Find 'news-table' in the Soup and load it into 'news_table' news_table = html.find(id='news-table') parsed_news = [] if news_table == None: continue for x in news_table.findAll('tr'): # read the text from each tr tag into text # get text from a only text = x.a.get_text() link = x.a['href'] # splite text in the td tag into a list date_scrape = x.td.text.split() # if the length of 'date_scrape' is 1, load 'time' as the only element if len(date_scrape) == 1: time = date_scrape[0] # else load 'date' as the 1st element and 'time' as the second else: date = date_scrape[0] time = date_scrape[1] # Append ticker, date, time and headline as a list to the 'parsed_news' list parsed_news.append([ticker, date, time, text, link]) except: database().insert_error_log( f"ERROR GATHERING HEADLINES FOR {ticker} AT {date}") self.time.sleep(1) data = self.sentiment_analysis(parsed_news) self.insert_data(data) utility.printProgressBar(z + 1, l, prefix='Progress:', suffix='Complete', length=50)
def list_to_db(self): self.tables.create_stock_list_table() file = open(f"./{self.directory}/nasdaq.txt") l = len(file.readlines()[1:]) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for i, line in enumerate(file.readlines()[1:]): data = line.split("|") if len(data) < 2: continue if CRUD.crud().check_stock_entry_in_list(data[0]): continue symbol = yf.Ticker(data[0]) sector, industry = None, None try: sector = symbol.info["sector"] industry = symbol.info["industry"] except: pass data_dict = { "ticker": data[0], "description": data[1], "sector": sector, "industry": industry } sql_statement = f"INSERT INTO {self.stock_table_list_name} (ticker, description, sector, industry, market) " \ "VALUES (%s, %s, %s, %s, %s)" values = (data_dict["ticker"], data_dict["description"], data_dict["sector"], data_dict["industry"], "nasdaq") try: cursor = self.conn.cursor() cursor.execute(sql_statement, values) self.conn.commit() utility.printProgressBar(i + 1, l, prefix='Progress:', suffix='Complete', length=50) except connect.errors as err: return False print(err) return True
def create_all_stock_tables(self): result = True stock_list = self.crud.get_list_of_stocks() l = len(stock_list) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for i, stock in enumerate(stock_list): # Look into data types for indicator values and certain metrics that could be added in sql_statement = f"CREATE TABLE IF NOT EXISTS {stock[0]}_STK (id INT AUTO_INCREMENT PRIMARY KEY, " \ f"dt DATETIME, open FLOAT(8,4), close FLOAT(8,4), high FLOAT(8,4), low FLOAT(8,4), adj_close FLOAT(8,4), " \ f"volume INT, split VARCHAR(25), dividend FLOAT(8,4), ma FLOAT(8,4), ema FLOAT(8,4), stoch FLOAT(8,4), " \ f"macd FLOAT(8,4), boll_bands FLOAT(8,4), rsi FLOAT(8,4), fibo_retrac FLOAT(8,4), ichimoku FLOAT(8,4), " \ f"std_dev FLOAT(8,4), avg_dir_idx FLOAT(8,4));" try: conn = connect.connect(host=self.host, user=self.root_user, password=self.root_pass, database=self.stock_db_name) cursor = self.conn.cursor() cursor.execute(sql_statement) conn.close() utility.printProgressBar(i + 1, l, prefix='Progress:', suffix='Complete', length=50) except: db = database() db.insert_error_log( f"ERROR CREATING TABLE PRICES {stock}: {self.conn.get_warnings}" ) print( f"ERROR CREATING TABLE {stock}: {self.conn.get_warnings}") result = False return result
def create_all_sentiment_tables(self): result = True stock_list = self.crud.get_list_of_stocks() l = len(stock_list) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for i, stock in enumerate(stock_list): sql_statement_sentiment = f"CREATE TABLE IF NOT EXISTS {stock[0]}_SENT (id INT AUTO_INCREMENT PRIMARY KEY, " \ f"dt DATETIME, headline VARCHAR(500), sent_neg FLOAT(8,4), sent_neutral FLOAT(8,4), " \ f"sent_pos FLOAT(8,4), sent_compound FLOAT(8,4), url VARCHAR(350));" try: conn = connect.connect(host=self.host, user=self.root_user, password=self.root_pass, database=self.sentiment_db_name) cursor = conn.cursor() cursor.execute(sql_statement_sentiment) conn.close() utility.printProgressBar(i + 1, l, prefix='Progress:', suffix='Complete', length=50) except errors: db = database() db.insert_error_log( f"ERROR CREATING TABLE PRICES {stock}: {self.conn.get_warnings}" ) print( f"ERROR CREATING TABLE SENTIMENT {stock[0]}: {conn.get_warnings}" ) result = False return result
def update_data(self): period_dict = self.get_period() # period_dict = self.sample_data l = len(period_dict) utility.printProgressBar(0, l, prefix='Progress:', suffix='Complete', length=50) for x, period in enumerate(period_dict): ticker_list = "" if len(period_dict[period]) == 0: continue print(f"Beginning download for {period}") for ticker in period_dict[period]: ticker_list += ticker + " " try: data = yf.download(tickers=ticker_list, threads=False, group_by="ticker", period=period) except: print("ERROR") database().insert_error_log("YF001 - YFINANCE IS DOWN OR NOT WORKING") utility.restart_yfinance() break for symbol in period_dict[period]: stock_obj = data.get(symbol, None) if stock_obj is None: continue for header in stock_obj.columns.values: for i in range(0, len(stock_obj[header].index)): if self.check_values(stock_obj, i): dt = self.frmt_dt(stock_obj[header].index[i]) date = dt.strftime("%Y-%m-%d %H:%M:%S") open = self.flt_num(stock_obj['Open'][i]) close = self.flt_num(stock_obj['Close'][i]) high = self.flt_num(stock_obj['High'][i]) low = self.flt_num(stock_obj['Low'][i]) adj_close = self.flt_num(stock_obj['Adj Close'][i]) volume = self.int_num(stock_obj['Volume'][i]) values = self.check_insert_values((date, open, close, high, low, adj_close, volume)) if values == False: continue try: cursor = self.conn.cursor() result = self.check_if_exists(symbol, date, adj_close, volume, open, close) if result: sql_statement = f"UPDATE {symbol}_STK " \ f"SET dt='{date}', open='{open}', close='{close}', high='{high}', low='{low}', " \ f"adj_close='{adj_close}', volume='{volume}' " \ f"WHERE ID='{result}';" cursor.execute(sql_statement) self.conn.commit() elif result == False: sql_statement = f"INSERT INTO {symbol}_STK (dt, open, close, high, low, adj_close, volume) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql_statement, values) self.conn.commit() except connect.errors as err: database().insert_error_log(f"ERROR INSERTING/UPDATING TECHNICAL DATA INTO DATABASE FOR {symbol} AT {date}") utility.printProgressBar(x + 1, l, prefix='Progress:', suffix='Complete', length=50)