def get_closing_price(date, ticker):
connection, cursor = get_connection_cursor()
sql = f"select t.closing_price from shares.tickers_prices t where t.date = '{date}' and t.ticker = '{ticker}'"
# print(f"sql is: {sql}")
cursor.execute(sql)
(closing_price, ) = cursor.fetchone()
return closing_price
def get_max_date_for_metric_id(id):
connection, cursor = get_connection_cursor()
sql = 'select max(g.date) from shares.global_data g where g.global_metric_id =%s'
values = [id]
cursor.execute(sql, values)
(date, ) = cursor.fetchone()
return date
def print_feature_dict():
connection, cursor = get_connection_cursor()
sql = "select f.id, f.name from shares.features f"
cursor.execute(sql)
print("features_dict = {", end='')
for (id, name) in cursor:
print('"', name, '": ', '"', id, '", ', sep='', end='')
print("}")
def get_missing_dates(date_str_list):
connection, cursor = get_connection_cursor()
business_dates_sql_table = create_dates_table(date_str_list)
sql = f'select dates.date from ({business_dates_sql_table}) as dates left join shares.business_days b ' \
'on (dates.date = b.date) ' \
'where b.business_day is NULL'
cursor.execute(sql)
return [item[0] for item in cursor.fetchall()]
def get_missing_tickers(date, ticker_list):
connection, cursor = get_connection_cursor()
ticker_list_table_sql = create_ticker_list_table_sql(ticker_list)
sql = f"select tickers.ticker from ({ticker_list_table_sql}) as tickers where tickers.ticker not in (select " \
f"t.ticker from shares.tickers_prices t where t.date = '{date}')"
# print(f"sql is: {sql}")
cursor.execute(sql)
tuples = cursor.fetchall()
return [i[0] for i in tuples]
def get_ticker_last_date(ticker, connection=None, cursor=None):
if not connection:
connection, cursor = get_connection_cursor()
sql = f"select t.date from shares.tickers_prices t where t.ticker ='{ticker}' ORDER BY date DESC LIMIT 1"
# print(f"sql is: {sql}")
cursor.execute(sql)
(last_date, ) = cursor.fetchone()
return last_date
def get_business_day(date):
connection, cursor = get_connection_cursor()
sql = f"select b.business_day from shares.business_days b where b.date = '{date}')"
print(f"sql is: {sql}")
cursor.execute(sql)
result = cursor.fetchone()
business_day = None
if result:
(business_day, ) = result
return business_day
def get_market_cap_list(date_str, business_day, tickers):
connection, cursor = get_connection_cursor()
tickers_list = StrUtils.create_comma_sperated__quoated_list(tickers)
sql = f"select t.date, t.ticker, t.market_cap from shares.tickers_prices t where t.ticker in ({tickers_list}) and t.date = '{business_day}';"
print(f"sql is: {sql}")
cursor.execute(sql)
dateticker_to_capprice_map = {}
for row in cursor:
(date, ticker, market_cap) = row
dateticker_to_capprice_map[(date_str, ticker)] = market_cap
return dateticker_to_capprice_map
def fillDBFeatures():
data = getJsonFromFile('../../../resources/MSFT.json')
connection, cursor = get_connection_cursor()
for dic in data:
link = dic['field_name']
featureName = extractFeautreName(link)
sql = "INSERT INTO shares.features (name) VALUES (%s)"
val = [featureName]
cursor.execute(sql, val)
print(featureName)
connection.commit()
def get_global_metric_id(chart_id, key):
connection, cursor = get_connection_cursor()
sql = 'select g.id from shares.global_metrics g where g.chart_id=%s AND g.sub_name=%s'
values = [chart_id, key]
cursor.execute(sql, values)
result = cursor.fetchone()
if result:
(id, ) = result
else:
id = None
return id
def populate_federal_funds_rate_TRY():
text = get_federal_funds_rate_html_from_macrotrends_TRY()
lines = iter(text.splitlines())
read_till_data(lines)
latest_date = get_max_date_for_metric_id(1)
first_new_line = read_thru_date(lines, latest_date)
(connection, cursor) = get_connection_cursor()
insert_row_to_global_data(cursor, 1, first_new_line)
for line in lines:
splitted_line = get_date_value_line(line)
if (splitted_line):
insert_row_to_global_data(cursor, 1, splitted_line)
def fill_business_days(missing_dates):
connection, cursor = get_connection_cursor()
my_data = []
for date in missing_dates:
business_day = DateUtils.get_business_date(date)
t = (date, business_day)
my_data.append(t)
sql = f"insert into shares.business_days (date, business_day) VALUES (%s, %s)"
cursor.executemany(sql, my_data)
connection.commit()
def get_companies_ids(tickers):
connection, cursor = get_connection_cursor()
tickers_no_brakets = ", ".join(map(add_quaotes, tickers))
sql = f"select c.ticker, c.id from shares.companies c where c.ticker in ({tickers_no_brakets})"
# print(f"sql is: {sql}")
cursor.execute(sql)
db_map = {}
for key, value in cursor:
db_map[key] = value
companies_id = []
for id in tickers:
companies_id.append(db_map[id])
return companies_id
def get_market_cap(date, ticker):
connection, cursor = get_connection_cursor()
business_day = DateUtils.get_business_date(date)
sql = f"select t.market_cap from shares.tickers_prices t where t.ticker = '{ticker}' and t.date = '{business_day}';"
# print(f"sql is: {sql}")
cursor.execute(sql)
single_result = cursor.fetchone()
if single_result:
(market_cap, ) = single_result
return market_cap
else:
return None
def get_tickers(companies_ids):
connection, cursor = get_connection_cursor()
comp_name_no_brakets = ", ".join(map(str, companies_ids))
sql = f"select c.id, c.ticker from shares.companies c where c.id in ({comp_name_no_brakets})"
# print(f"sql is: {sql}")
cursor.execute(sql)
db_map = {}
for key, value in cursor:
db_map[key] = value
tickers = []
for id in companies_ids:
tickers.append(db_map[id])
return tickers
def get_last_updated(data_name, ticker, connection=None, cursor=None):
if not connection:
connection, cursor = get_connection_cursor()
sql = f"select t.date from shares.last_updated t where t.data_name ='{data_name}' AND t.ticker = '{ticker}'"
# print(f"sql is: {sql}")
cursor.execute(sql)
result = cursor.fetchone()
if result:
(last_updated, ) = result
else:
last_updated = None
return last_updated
def get_global_metric_name(id):
connection, cursor = get_connection_cursor()
sql = 'select g.name, g.sub_name from shares.global_metrics g where g.id=%s'
values = [id]
cursor.execute(sql, values)
result = cursor.fetchone()
if result:
(
name,
sub_name,
) = result
else:
id = None
return f"{name}.{sub_name}"
def get_market_cap_for_list_of_dates(ticker,
list_of_dates,
connection=None,
cursor=None):
if not connection:
connection, cursor = get_connection_cursor()
# (select 1 id, '2019-3-31' date union all select 2 id, '2019-6-30' date)
original_dates_sql = create_dates_sql(list_of_dates)
list_of_business_dates = DateUtils.get_business_dates(list_of_dates)
business_dates_sql = create_dates_sql(list_of_business_dates)
sql = f"select dates.date original_date, t.date business_day, t.market_cap from {original_dates_sql} as dates, {business_dates_sql} as business_dates, shares.tickers_prices t where t.ticker = '{ticker}' AND dates.id=business_dates.id AND t.date = business_dates.date;"
cursor.execute(sql)
return cursor.fetchall()
def populate_general_metric_data_from_json(resourceName, global_metric_id):
(connection, cursor) = get_connection_cursor()
with open('../resources/' + resourceName, 'r') as file:
lines = iter(file.read().splitlines())
read_till_data(lines)
latest_date = get_max_date_for_metric_id(global_metric_id)
first_new_line = read_thru_date(lines, latest_date)
if first_new_line:
insert_row_to_global_data(connection, cursor, first_new_line,
global_metric_id)
for line in lines:
splitted_line = get_date_value_line(line)
if (splitted_line):
insert_row_to_global_data(connection, cursor,
splitted_line, global_metric_id)
connection.commit()
def fill_db_companies_from_json_file():
data = getJsonFromFile('../../../resources/stock-screener.json')
connection, cursor = get_connection_cursor()
ticker_max = comp_name_max = comp_name_2Max = exchangeMax = zacks_x_ind_descMax = zacks_x_sector_descMax = zacks_m_ind_descMax = emp_cntMax = 0
for dic in data:
ticker = dic['ticker']
comp_name = dic['comp_name']
comp_name_2 = dic['comp_name_2']
exchange = dic['exchange']
zacks_x_ind_desc = dic['zacks_x_ind_desc']
zacks_x_sector_desc = dic['zacks_x_sector_desc']
zacks_m_ind_desc = dic['zacks_m_ind_desc']
emp_cnt = dic['emp_cnt']
ticker_max = max(len(ticker), ticker_max)
comp_name_max = max(len(comp_name), comp_name_max)
comp_name_2Max = max(len(comp_name_2), comp_name_2Max)
exchangeMax = max(len(exchange), exchangeMax)
zacks_x_ind_descMax = max(len(zacks_x_ind_desc), zacks_x_ind_descMax)
zacks_x_sector_descMax = max(len(zacks_x_sector_desc),
zacks_x_sector_descMax)
zacks_m_ind_descMax = max(len(zacks_m_ind_desc), zacks_m_ind_descMax)
emp_cntMax = max(len(emp_cnt), emp_cntMax)
sql = "INSERT INTO shares.companies (ticker, comp_name, comp_name_2, exchange, zacks_x_ind_desc, " \
"zacks_x_sector_desc, zacks_m_ind_desc, emp_cnt) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) "
val = [
ticker, comp_name, comp_name_2, exchange, zacks_x_ind_desc,
zacks_x_sector_desc, zacks_m_ind_desc, emp_cnt
]
print(*val, sep=",")
cursor.execute(sql, val)
val = [
ticker_max, comp_name_max, comp_name_2Max, exchangeMax,
zacks_x_ind_descMax, zacks_x_sector_descMax, zacks_m_ind_descMax,
emp_cntMax
]
print(*val, sep=",")
connection.commit()
def populate_db_financial_statements(url_pattern, companies=None):
companies = companies or CompaniesDAO.get_all_companies()
connection, cursor = get_connection_cursor()
for (company_id, ticker, company_name) in tqdm(companies, colour="CYAN"):
json: Optional[Any] = get_json_from_macrotrends(url_pattern, ticker, company_name)
if (json):
for dic in json:
link = dic['field_name']
feature_name = extractFeautreName(link)
for key in dic:
if (is_date(key)):
date = key
value = dic[date]
if (value):
feature_id = get_metric_id(feature_name)
sql = "INSERT INTO shares.feature_data (company_id, feature_id, date, value) VALUES (%s, " \
"%s, %s, %s) ON DUPLICATE KEY UPDATE value=%s "
val = [company_id, feature_id, date, value, value]
cursor.execute(sql, val)
connection.commit()
def populate_single_ticker(connection=None, cursor=None, ticker=None):
if not connection:
connection, cursor = get_connection_cursor()
json: Optional[Any] = get_json_from_macrotrends(ticker)
if json:
my_data = []
for dict in json:
date = dict["date"]
market_cap = dict["v1"]
t = (date, ticker, market_cap)
my_data.append(t)
print(f"there are {len(json)} dates for {ticker}")
sql = f"INSERT INTO shares.tickers_prices (date, ticker, market_cap) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE market_cap=VALUES(market_cap)"
cursor.executemany(sql, my_data)
sql = f"INSERT INTO shares.last_updated (data_name, ticker, date) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE date=VALUES(date)"
values = ['market_cap', ticker, datetime.today()]
cursor.execute(sql, values)
connection.commit()
print("committed")
def populate_general_metric_data_from_macrotrends(chart_id):
connection, cursor = get_connection_cursor()
# TODO load only new data
json_obj = get_json_from_macrotrends(chart_id)
if (json_obj):
my_data = []
for dic in json_obj:
for key in dic:
if key == 'date':
date = dic[key]
else:
metric_id = GlobalMetricDAO.get_global_metric_id(
chart_id, key)
if not metric_id:
continue
value = dic[key]
tuple = (date, metric_id, value)
my_data.append(tuple)
sql = f"INSERT INTO shares.global_data (date, global_metric_id, global_metric_value) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE global_metric_value=VALUES(global_metric_value)"
cursor.executemany(sql, my_data)
connection.commit()
else:
print(f"No info found for macrotrends general_metric id: {chart_id}")
def insert_closing_price(ticker, date_obj, closing_price):
connection, cursor = get_connection_cursor()
sql = f"INSERT INTO shares.tickers_prices (date, ticker, closing_price) VALUES ('{date_obj}', '{ticker}', {closing_price})"
# print(f"sql is: {sql}")
cursor.execute(sql)
connection.commit()
def get_ticker_range(ticker):
connection, cursor = get_connection_cursor()
start_date = get_ticker_first_date(ticker, connection, cursor)
end_date = get_ticker_last_date(ticker, connection, cursor)
return start_date, end_date
def get_companies_not_in_market_cap_db():
connection, cursor = get_connection_cursor()
sql = "select c.ticker from shares.companies c where c.ticker not in (select distinct t.ticker from shares.tickers_prices t where t.market_cap is not NULL)"
cursor.execute(sql)
return cursor.fetchall()
def populate_db_market_cap(companies=None):
companies = companies or CompaniesDAO.get_all_companies()
connection, cursor = get_connection_cursor()
for (ticker, ) in tqdm(companies):
populate_single_ticker(connection, cursor, ticker)
def get_companies_not_in_db(feature_id):
connection, cursor = get_connection_cursor()
sql = f"select c.id, c.ticker, c.comp_name from shares.companies c where c.id NOT IN (SELECT distinct " \
f"d.company_id FROM shares.feature_data d WHERE d.feature_id = {feature_id}) "
cursor.execute(sql)
return cursor.fetchall()
def tickerCompanyNameIteratorTRY():
connection, cursor = get_connection_cursor()
sql = "select c.ticker, c.comp_name from shares.companies c"
cursor.execute(sql)
return cursor.fetchall()
def get_all_companies():
connection, cursor = get_connection_cursor()
sql = "select c.id, c.ticker, c.comp_name from shares.companies c where c.ticker = 'AMZN'"
sql = "select c.id, c.ticker, c.comp_name from shares.companies c"
cursor.execute(sql)
return cursor.fetchall()
