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)