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 insert_data(self, data):
# Organize data, make sure it will fit into table
for i in range(data.shape[0]):
date = data.iloc[i].get('date')
time = data.iloc[i].get('time')
ticker = data.iloc[i].get('ticker')
headline = str(data.iloc[i].get('headline')).replace("'",
"\'")[:500]
link = str(data.iloc[i].get('link'))[:350]
neg = self.flt_num(data.iloc[i].get('neg'))
neu = self.flt_num(data.iloc[i].get('neu'))
pos = self.flt_num(data.iloc[i].get('pos'))
compound = self.flt_num(data.iloc[i].get('compound'))
exists = self.check_if_exists(date, time, ticker, headline, link)
values = (date, headline, link, neg, neu, pos, compound)
if exists:
continue
else:
try:
sql_statement = f"INSERT INTO {ticker[0]}_SENT (dt, headline, url, sent_neg, sent_neutral, sent_pos, sent_compound) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s)"
cursor = self.conn.cursor()
cursor.execute(sql_statement, values)
self.conn.commit()
except connect.errors as err:
database().insert_error_log(
f"ERROR INSERTING VALUES INTO DATABASE FOR {ticker[0]} AT {date}"
)
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 __init__(self):
con = config.config()
self.conn_utility = database().conn_utility
self.conn = database().conn_finance
self.crud = crud()
self.host = con.db_host
self.root_user = con.db_user_root
self.root_pass = con.db_pass_root
self.sentiment_db_name = con.sentiment_db_name
self.stock_db_name = con.stock_db_name
def __init__(self):
config = get_values()
self.conn = database().conn_finance
self.error = database()
self.tables = tables.build_tables()
self.directory = config.file_location
self.stock_table_list_name = "STOCK_LIST_TBL"
self.exchange = {
"nasdaq.txt": config.nasdaq_listed_url,
"nyse.csv": config.nyse_listed_url,
"amex.csv": config.amex_listed_url
}
def build_tables(self):
result = True
if self.create_error_log_table():
print("Error Log table created successfully")
else:
print("FAILED CREATING ERROR LOG TABLE")
result = False
if self.create_status_table():
print("Status table created successfully")
else:
print("FAILED CREATING STATUS TABLE")
result = False
if self.create_stock_list_table():
print("Stock List table created successfully")
else:
print("FAILED CREATING STOCK LIST TABLE")
result = False
if stk_list.stock_list().list_to_db():
print("Stock List loaded into the database\n")
else:
print("FAILED LOADING STOCK LIST INTO DATABASE")
result = False
if result == False:
print(
"ERROR IN TABLE CREATION - DID NOT COMPLETE BUILD TABLE TASKS")
database().insert_error_log(
"ERROR IN TABLE BUILD - DID NOT COMPLETE BUILD TABLE TASKS")
input("PRESS")
return
else:
if self.create_all_stock_tables():
print("All Stock tables created successfully")
else:
print("FAILED CREATING ALL STOCK TABLES")
result = False
if self.create_all_sentiment_tables():
print("All Sentiment tables created successfully")
else:
print("FAILED CREATING ALL SENTIMENT TABLES")
result = False
if result == True:
print("""
------------------
ALL TABLES CREATED
------------------
""")
def check_if_exists(self, symbol, date, adj_close, volume, open, close):
# returns 1 if exists, 0 if not
sql_statement = f"SELECT IF( EXISTS( SELECT * FROM {symbol}_STK WHERE dt = '{date}' AND volume = '{volume}'), 1, 0);";
data = None
try:
cursor = self.conn.cursor(buffered=True)
cursor.execute(sql_statement)
exists = cursor.fetchone()
except connect.errors as error:
database().insert_error_log(f"ERROR CHECKING TECHNICAL DATA FOR DATABASE FOR {symbol} AT {date}")
if exists[0] == 1:
return True
else:
return False
def check_if_exists(self, date, time, ticker, headline, link):
# returns 1 if exists, 0 if not
sql_statement = f"SELECT IF( EXISTS( SELECT * FROM {ticker[0]}_SENT WHERE dt = '{date}' AND url = '{link}'), 1, 0);"
try:
cursor = self.conn.cursor(buffered=True)
cursor.execute(sql_statement)
exists = cursor.fetchone()
except connect.errors as error:
database().insert_error_log(
f"ERROR CHECKING VALUES INTO DATABASE FOR {ticker[0]} AT {date}"
)
print(exists)
if exists[0] == 1:
return True
else:
return False
def __init__(self):
config = get_values()
self.conn = database().conn_sentiment
self.news_tables = {}
self.tickers = [('VRCA', ), ('AAPL', ), ('SG', ), ('SFT', ),
('PBTS', ), ('IMAC', ), ('HOLI', ), ('AACG', )]
# https://chromedriver.chromium.org/downloads
# self.tickers = crud().get_list_of_stocks()
self.finwiz_url = config.finwiz_url
self.time = time
def get_date_range(self):
sql_statement = f"SELECT dt FROM {self.symbol}_TBL ORDER BY dt DESC LIMIT 1;"
recent_date = None
try:
cursor = self.sql.conn_finance.cursor()
cursor.execute(sql_statement)
recent_date = cursor.fetchone()
except:
database().insert_error_log(f"ERROR GETTING DATE RANGE FOR YFINANCE {self.symbol}")
current_date = datetime.datetime.now()
# Grab most recent date on the table and compare with current date
if recent_date == None:
return 'max'
else:
difference = current_date - recent_date[0]
if difference.days > 99:
return 'max'
else:
return (recent_date[0], current_date)
def __init__(self):
self.conn = database().conn_finance
self.market = None
self.date_range = None
self.validate = False
self.stock_table_list_name = "STOCK_LIST_TBL"
self.tickers = crud().get_list_of_stocks()
self.sample_data = {
"1d" : [],
"5d" : ['GOOG', 'AAPL'],
"1mo" : [],
"3mo" : [],
"6mo" : [],
"1y" : []
}
def create_stock_list_table(self):
table_name = "STOCK_LIST_TBL"
sql_statement = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, " \
f"ticker VARCHAR(8), description VARCHAR(255), sector VARCHAR(60), industry VARCHAR(60), market VARCHAR(6));"
try:
cursor = self.conn.cursor()
cursor.execute(sql_statement)
return True
except:
db = database()
db.insert_error_log(
f"ERROR CREATING TABLE [{table_name}]: {self.conn.get_warnings}"
)
return False
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 __init__(self):
config = get_values()
self.conn = database().conn_finance
self.stock_table_list_name = "STOCK_LIST_TBL"
def gather_data(self):
print("Beginning update of all stock data tables...")
yfinance.yfinance().update_data()
database().insert_status_log("UPDATED ALL STOCK DATA TABLES")
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)
