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()