def write_stock_estimates_data_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_ESTIMATES + stock[:-6] + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_uri = file.split("/")[-1][:-5] + "-Aktie"
stock_estimate_soup = scrap.get_soup_code_from_file(file)
if stock_estimate_soup is None:
logger.warning(
"Write Stock Estimate Data to DB: Soup is None for %s" %
str(file))
continue
try:
eps_0, eps_p1 = parse.get_result_per_share_current_and_next_year(
stock_estimate_soup)
db.upsert_item(
table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock_uri,
eps_0=eps_0,
eps_p1=eps_p1)
except:
logger.exception(
"Write Stock Estimate Data to DB: Exception for stock: %s" %
stock_uri)
def write_stock_last_quarterly_figures_date_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_DATES + stock[:-6] + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_uri = file.split("/")[-1][:-5] + "-Aktie"
stock_targets_soup = scrap.get_soup_code_from_file(file)
if stock_targets_soup is None:
logger.warning(
"Write Stock Quaterlyfigures Data to DB: Soup is None for %s" %
str(file))
continue
try:
last_figures_date = parse.get_last_quarterly_figures_date(
stock_targets_soup)
if last_figures_date is None:
continue
else:
# db.write_single_stock_dates_data_to_db(stock_uri, last_figures_date)
db.upsert_item(
table=cst.TABLE_STOCK_DATES,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=last_figures_date,
quarterly="Quarterly/Yearly",
)
except:
logger.exception(
"Write Stock Quaterly Data to DB: Exception for stock: %s" %
stock_uri)
def write_stock_targets_data_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_TARGETS + stock[:-6] + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_uri = file.split("/")[-1][:-5] + "-Aktie"
stock_targets_soup = scrap.get_soup_code_from_file(file)
if stock_targets_soup is None:
logger.warning(
"Write Stock Targets Data to DB: Soup is None for %s" %
str(file))
continue
try:
buy, hold, sell = parse.get_analyst_ratings(stock_targets_soup)
db.upsert_item(
table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock_uri,
analyst_buy=buy,
analyst_hold=hold,
analyst_sell=sell,
)
except:
logger.exception(
"Write Stock Targets Data to DB: Exception for stock: %s" %
stock_uri)
def test_get_list_from_db(self):
index_list = db.get_list(table=cst.TABLE_INDIZES,
columns=cst.COLUMN_URI,
database=cst.TEST_DATABASE)
asserted_list = [
"CAC_40", "dax", "dow_jones", "FTSE_100", "s&p_500", "tecdax"
]
self.assertEqual(asserted_list.sort(), index_list.sort())
def test_write_index_content_to_stock_table(self):
stock_list = [
["3i", "3i-Aktie"],
["Admiral Group", "admiral_group-Aktie"],
["Anglo American", "anglo_american-Aktie"],
]
index_uri = "FTSE_100"
asserted_stock_list_before = [
"3i-Aktie",
"3m-Aktie",
"ab_inbev-Aktie",
"adidas-Aktie",
"africa-israel_investments-Aktie",
"afyon_cimento_sanayii_tas-Aktie",
"bechtle-Aktie",
"cellcom_israel-Aktie",
"coca-cola-Aktie",
]
self.assertEqual(
asserted_stock_list_before,
db.get_list(
table=cst.TABLE_STOCKS,
columns=cst.COLUMN_URI,
database=cst.TEST_DATABASE,
),
)
db.write_index_content_list_to_db(stock_list, index_uri,
cst.TEST_DATABASE)
asserted_stock_list_after = [
"3i-Aktie",
"3i-Aktie",
"admiral_group-Aktie",
"anglo_american-Aktie",
]
self.assertEqual(
asserted_stock_list_after,
db.get_list(
table=cst.TABLE_INDEX_CONTENTS,
columns=cst.COLUMN_STOCK_URI,
condition=[cst.COLUMN_INDEX_URI, "FTSE_100"],
database=cst.TEST_DATABASE,
),
)
def test_get_list_from_db_with_condition(self):
index_list = db.get_list(
table=cst.TABLE_INDIZES,
columns=cst.COLUMN_URI,
condition=[cst.COLUMN_LARGECAP, 1],
database=cst.TEST_DATABASE,
)
asserted_list = ["CAC_40", "dax", "dow_jones", "FTSE_100"]
self.assertEqual(asserted_list.sort(), index_list.sort())
def write_stock_balance_data_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_BALANCE + stock[:-6] + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_uri = file.split("/")[-1][:-5] + "-Aktie"
stock_balance_soup = scrap.get_soup_code_from_file(file)
if stock_balance_soup is None:
logger.warning(
"Write Stock Balance Data to DB: Soup is None for %s" %
str(file))
continue
if not parse.is_data_available(stock_balance_soup):
logger.warning(
"Write Stock Balance Data to DB: No Data Available for %s" %
str(file))
continue
try:
earnings_after_tax = parse.get_current_value_of_attribute(
stock_balance_soup, cst.TEXT_RESULT_AFTER_TAX)
operative_result = parse.get_current_value_of_attribute(
stock_balance_soup, cst.TEXT_OPERATIVE_RESULT)
sales_revenue = parse.get_current_value_of_attribute(
stock_balance_soup, cst.TEXT_SALES_REVENUE)
balance = parse.get_current_value_of_attribute(
stock_balance_soup, cst.TEXT_BALANCE)
equity_capital = parse.get_current_value_of_attribute(
stock_balance_soup, cst.TEXT_EQUITY_CAPITAL)
eps_m3, eps_m2, eps_m1 = parse.get_result_per_share_last_three_years(
stock_balance_soup)
db.upsert_item(
table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock_uri,
equity_capital=equity_capital,
earnings_after_tax=earnings_after_tax,
operative_result=operative_result,
sales_revenue=sales_revenue,
balance=balance,
eps_m3=eps_m3,
eps_m2=eps_m2,
eps_m1=eps_m1)
except:
logger.exception(
"Write Stock Balance Data to DB: Exception for stock: %s" %
stock_uri)
continue
def levermann_03():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
# equity_capital = db.get_equity_capital(stock)
equity_capital = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_EQUITY_CAPITAL,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
# balance = db.get_balance(stock)
balance = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_BALANCE,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
if equity_capital is None or balance is None or balance == 0:
logger.info(
"Calculate Lev03: Equity Capital or Balance is None or 0 for stock: %s"
% stock)
continue
equity_ratio = round(equity_capital / balance, 2)
if db.check_is_financial_company(stock):
if equity_ratio > 0.10:
lev_03_score = 1
elif equity_ratio < 0.05:
lev_03_score = -1
else:
lev_03_score = 0
else:
if equity_ratio > 0.25:
lev_03_score = 1
elif equity_ratio < 0.15:
lev_03_score = -1
else:
lev_03_score = 0
# db.save_equity_ratio_to_db(stock, equity_ratio, lev_03_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_03_val=equity_ratio,
lev_03_sco=lev_03_score,
)
def levermann_02():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
# operative_result = db.get_operative_result(stock)
operative_result = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_OPERATIVE_RESULT,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
# sales_revenue = db.get_sales_revenue(stock)
sales_revenue = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_SALES_REVENUE,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
if operative_result is None or sales_revenue is None or sales_revenue == 0:
logger.info(
"Calculate Lev02: Operative Result or Sales Revenue is None for stock: %s"
% stock)
continue
ebit = round(operative_result / sales_revenue, 2)
if db.check_is_financial_company(stock):
logger.info("Calculate Lev02: %s is financial Stock" % stock)
ebit = 0
lev_02_score = 0
else:
if ebit > 0.12:
lev_02_score = 1
elif ebit < 0.06:
lev_02_score = -1
else:
lev_02_score = 0
# db.save_ebit_to_db(stock, ebit, lev_02_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_02_val=ebit,
lev_02_sco=lev_02_score,
)
def test_get_two_column_list_from_db(self):
stock_list = db.get_list(
table=cst.TABLE_STOCKS,
columns=[cst.COLUMN_URI, cst.COLUMN_MARKET_PLACE],
database=cst.TEST_DATABASE,
)
asserted_list = [
["3i-Aktie", "FSE"],
["3m-Aktie", "FSE"],
["ab_inbev-Aktie", "FSE"],
["adidas-Aktie", "FSE"],
["bechtle-Aktie", "FSE"],
["cellcom_israel-Aktie", "FSE"],
["coca-cola-Aktie", "FSE"],
]
self.assertEqual(asserted_list.sort(), stock_list.sort())
def levermann_12():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
if db.is_small_cap(stock):
db.save_reversal_to_db(stock, 0, 0)
continue
try:
last_days_of_month = date.get_last_days_of_last_four_months(
date.get_current_date())
last_stock_prices_of_month = [
db.get_closing_stock_price(r_date, stock)[0]
for r_date in last_days_of_month
]
index = db.get_main_index_of_stock(stock)
if index is None:
logger.info("Calculate Lev12: No Index found")
continue
last_index_prices_of_month = [
db.get_closing_index_price(r_date, index)[0]
for r_date in last_days_of_month
]
stock_changes = db.calculate_list_changes(
last_stock_prices_of_month)
index_changes = db.calculate_list_changes(
last_index_prices_of_month)
differences = []
for i in range(len(stock_changes)):
differences.append(stock_changes[i] - index_changes[i])
avg_diff = round(sum(differences) / 3, 2)
if all(i > 0 for i in differences):
lev_score_12 = -1
elif all(i < 0 for i in differences):
lev_score_12 = 1
else:
lev_score_12 = 0
db.save_reversal_to_db(stock, avg_diff, lev_score_12)
except TypeError:
logger.exception("Calculate Lev12 TypeError at stock: %s" % stock)
continue
def levermann_08():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
eps_current = db.get_latest_eps(stock)
eps_last = db.get_second_latest_eps(stock)
if eps_current is None:
logger.info("Calculate Lev08: Current EPS is None for stock: %s" %
stock)
continue
if eps_last is None:
logger.info("Calculate Lev08: Last EPS is None for stock: %s" %
stock)
db.save_eps_revision_to_db(stock, 0, 0)
continue
eps_0_c = eps_current[3]
eps_1_c = eps_current[4]
eps_0_l = eps_last[3]
eps_1_l = eps_last[4]
if eps_0_l == 0 or eps_1_l == 0 or eps_0_l is None or eps_1_l is None:
logger.info("Calculate Lev08: eps_0_l or eps_1_l EPS is None or 0 "
"for stock: %s" % stock)
continue
if eps_0_c == 0 or eps_1_c == 0 or eps_0_c is None or eps_1_c is None:
logger.info("Calculate Lev08: eps_0_c or eps_1_c EPS is None or 0 "
"for stock: %s" % stock)
continue
eps_0_ratio = (eps_0_c / eps_0_l) - 1
eps_1_ratio = (eps_1_c / eps_1_l) - 1
if eps_0_ratio > 0.05 or eps_1_ratio > 0.05:
lev_score = 1
elif eps_0_ratio < -0.05 or eps_1_ratio < -0.05:
lev_score = -1
else:
lev_score = 0
db.save_eps_revision_to_db(stock,
round((eps_0_ratio + eps_1_ratio) / 2, 2),
lev_score)
def levermann_06():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
ratings = db.get_analyst_ratings(stock)
if ratings is None:
logger.info("Calculate Lev06: Rating is None for stock: %s" %
stock)
continue
rating_count = sum(ratings)
if rating_count == 0:
logger.info("Calculate Lev06: Zero Rating for stock: %s" % stock)
rating = 0
lev_06_score = 0
else:
rating = (1 * ratings[0] + 2 * ratings[1] +
3 * ratings[2]) / rating_count
is_small_cap = db.is_small_cap(stock)
if is_small_cap and 0 < rating_count <= 5 and 1.0 <= rating <= 1.5:
lev_06_score = 1
elif is_small_cap and 0 < rating_count <= 5 and 1.5 < rating < 2.5:
lev_06_score = 0
elif is_small_cap and 0 < rating_count <= 5 and 2.5 <= rating <= 3.0:
lev_06_score = -1
elif 1.0 <= rating <= 1.5:
lev_06_score = -1
elif 1.5 < rating < 2.5:
lev_06_score = 0
elif 2.5 <= rating <= 3.0:
lev_06_score = 1
else:
lev_06_score = 5
# db.save_rating_to_db(stock, round(rating, 2), lev_06_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_06_val=round(rating, 2),
lev_06_sco=lev_06_score,
)
def write_index_contents_from_html_to_db():
index_list = db.get_list(
table=cst.TABLE_INDIZES,
columns=cst.COLUMN_URI,
condition=[cst.COLUMN_ACTIVE, 1],
)
file_list = [
cst.PATH_INDEX_CONTENT + index + cst.HTML_EXTENSION
for index in index_list
]
for file in file_list:
index_content_soup = scrap.get_soup_code_from_file(file)
stock_list = parse.get_stock_list_of_single_index(index_content_soup)
index_uri = file.split("/")[-1][:-5]
db.write_index_content_list_to_db(stock_list, index_uri)
return True
def write_stock_histories_from_html_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_HISTORY + stock[:-6] + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_history_soup = scrap.get_soup_code_from_file(file)
if stock_history_soup is None:
logger.warning(
"Write Stock History Data to DB: Stock History Soup is None for %s"
% str(file))
continue
stock_history_list = parse.get_historic_prices_from_history(
stock_history_soup)
stock_uri = file.split("/")[-1][:-5] + "-Aktie"
db.write_stock_history_to_db(stock_history_list, stock_uri)
def levermann_01():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
# earnings_before_tax = db.get_earnings_after_tax(stock)
earnings_before_tax = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_EARNINGS_AT,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
# equity_capital = db.get_equity_capital(stock)
equity_capital = db.get_item(
table=cst.TABLE_COMPANY_DATA,
column=cst.COLUMN_EQUITY_CAPITAL,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
if earnings_before_tax is None or equity_capital is None or equity_capital == 0:
logger.info(
"Calculate Lev01: Earnings before Tax or Equity Capital is None for stock: %s"
% stock)
continue
return_on_equity = round(earnings_before_tax / equity_capital, 2)
if return_on_equity > 0.2:
lev_01_score = 1
elif return_on_equity < 0.1:
lev_01_score = -1
else:
lev_01_score = 0
# db.save_roe_to_db(stock, return_on_equity, lev_01_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_01_val=return_on_equity,
lev_01_sco=lev_01_score,
)
def write_stock_overview_data_to_db():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
file_list = [
cst.PATH_STOCK_OVERVIEW + stock + cst.HTML_EXTENSION
for stock in stock_list
]
for file in file_list:
stock_uri = file.split("/")[-1][:-5]
stock_overview_soup = scrap.get_soup_code_from_file(file)
if stock_overview_soup is None:
logger.warning(
"Write Stock Overview Data to DB: Stock Overview Soup is None for %s"
% str(file))
continue
market_cap = parse.get_market_cap(stock_overview_soup)
if market_cap is None:
logger.warning(
"Write Stock Overview Data to DB: Market Cap is None for %s" %
str(file))
market_cap = -1.00
stock_indices = str(parse.get_listed_indizes(stock_overview_soup))
stock_sectors = str(parse.get_sectors(stock_overview_soup))
market_place = parse.get_market_place(stock_overview_soup)
db.upsert_item(table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
stock_uri=stock_uri,
market_cap=market_cap,
current_date=date.get_current_date(),
stock_indices=stock_indices,
stock_sectors=stock_sectors)
db.update_item(table=cst.TABLE_STOCKS,
primary_keys=cst.COLUMN_URI,
uri=stock_uri,
market_place=market_place)
stock_history_list = parse.get_historic_stock_prices_from_overview(
stock_overview_soup)
if stock_history_list is not None:
db.write_stock_overview_history_to_db(stock_history_list,
stock_uri)
def write_index_histories_from_html_to_db():
index_list = db.get_list(
table=cst.TABLE_INDIZES,
columns=cst.COLUMN_URI,
condition=[cst.COLUMN_ACTIVE, 1],
)
file_list = [
cst.PATH_INDEX_HISTORY + index + cst.HTML_EXTENSION
for index in index_list
]
for file in file_list:
index_history_soup = scrap.get_soup_code_from_file(file)
index_history_list = parse.get_historic_prices_from_history(
index_history_soup)
index_uri = file.split("/")[-1][:-5]
try:
db.write_index_history_to_db(index_history_list, index_uri)
except:
logger.exception(
"Unhandled Exceptions at parse.write_index_histories_from_html_to_db"
)
def test_update_existing_stocks_market_place(self):
stock_uri = "coca-cola-Aktie"
market_place = "XETRA"
db.update_item(
table=cst.TABLE_STOCKS,
primary_keys=cst.COLUMN_URI,
database=cst.TEST_DATABASE,
uri=stock_uri,
market_place=market_place,
)
asserted_data = ["XETRA"]
self.assertEqual(
asserted_data,
db.get_list(
table=cst.TABLE_STOCKS,
columns=cst.COLUMN_MARKET_PLACE,
condition=[cst.COLUMN_URI, "coca-cola-Aktie"],
database=cst.TEST_DATABASE,
),
)
def levermann_13():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
eps = db.get_latest_eps(stock)
if eps is None:
logger.info("Calculate Lev13: EPS is None for stock: %s" % stock)
continue
if None in eps:
logger.info("Calculate Lev13: Any EPS is None for stock: %s" %
stock)
continue
if eps[3] != 0:
eps_ratio = round((eps[4] / eps[3]) - 1, 2)
else:
eps_ratio = 0
if eps_ratio > 0.05:
lev_score_12 = 1
elif eps_ratio < -0.05:
lev_score_12 = -1
else:
lev_score_12 = 0
db.save_profit_growth_to_db(stock, eps_ratio, lev_score_12)
def test_upsert_existing_single_overview_data_to_db(self):
stock_uri = "bechtle-Aktie"
market_cap = "3230.00"
current_date = "2019-03-16"
stock_indices = ["TecDAX", "MDAX", "Prime All Share"]
stock_sectors = [
"IT-Dienstleister",
"IT-Beratung Hardware",
"Dienstleistungen",
"Internethandel (B2B, B2C)",
"Informationstechnologie",
]
db.upsert_item(
table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
database=cst.TEST_DATABASE,
stock_uri=stock_uri,
market_cap=market_cap,
current_date=current_date,
stock_indices=str(stock_indices),
stock_sectors=str(stock_sectors),
)
asserted_data = ["['TecDAX', 'MDAX', 'Prime All Share']"]
self.assertEqual(
asserted_data,
db.get_list(
table=cst.TABLE_COMPANY_DATA,
columns=cst.COLUMN_INDICES,
condition=[cst.COLUMN_STOCK_URI, "bechtle-Aktie"],
database=cst.TEST_DATABASE,
),
)
def levermann_09_10_11():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
current_date = date.get_current_date()
date_minus_6_month = date.edit_date(current_date, cst.DT_MINUS, 6,
cst.DT_MONTH)
date_minus_12_month = date.edit_date(current_date, cst.DT_MINUS, 12,
cst.DT_MONTH)
current_price_pack = db.get_closing_stock_price(current_date, stock)
price_6_month_ago_pack = db.get_closing_stock_price(
date_minus_6_month, stock)
price_12_month_ago_pack = db.get_closing_stock_price(
date_minus_12_month, stock)
if (current_price_pack is None or price_6_month_ago_pack is None
or price_12_month_ago_pack is None):
logger.info(
"Calculate Lev09-11: Current Price or Price before 6M or 12M is None for stock: %s"
% stock)
continue
current_price = current_price_pack[0]
price_6_month_ago = price_6_month_ago_pack[0]
price_12_month_ago = price_12_month_ago_pack[0]
if current_price is None:
logger.info(
"Calculate Lev09-11: Current Price is None for stock: %s" %
stock)
continue
if (price_6_month_ago == 0 or price_12_month_ago == 0
or price_6_month_ago is None or price_12_month_ago is None):
logger.info(
"Calculate Lev09-11: Price before 6M or 12M is 0 for stock: %s"
% stock)
continue
ratio_6_month = round((current_price / price_6_month_ago) - 1, 2)
ratio_12_month = round((current_price / price_12_month_ago) - 1, 2)
if ratio_6_month > 0.05:
lev_score_09 = 1
elif ratio_6_month < -0.05:
lev_score_09 = -1
else:
lev_score_09 = 0
db.save_6_months_ratio_to_db(stock, ratio_6_month, lev_score_09)
if ratio_12_month > 0.05:
lev_score_10 = 1
elif ratio_12_month < -0.05:
lev_score_10 = -1
else:
lev_score_10 = 0
db.save_12_months_ratio_to_db(stock, ratio_12_month, lev_score_10)
if lev_score_09 == 1 and lev_score_10 <= 0:
lev_score_11 = 1
elif lev_score_09 == -1 and lev_score_10 >= 0:
lev_score_11 = -1
else:
lev_score_11 = 0
db.save_momentum_to_db(stock, lev_score_11)
def levermann_07():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
logger.info("Calculating Lev07 for %s" % stock)
# quarterly_date = db.get_quarterly_date(stock)
quarterly_date = db.get_item(
table=cst.TABLE_STOCK_DATES,
column=cst.COLUMN_DATE,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
if quarterly_date is None:
logger.info(
"Calculate Lev07: Quaterly Date is None for stock: %s" % stock)
db.save_quarterly_reaction_to_db(stock, 0, 0)
continue
index = db.get_main_index_of_stock(stock)
if index is None:
logger.info("Calculate Lev07: No Index found")
continue
quarterly_stock_pack = db.get_closing_stock_price(
quarterly_date, stock)
quarterly_index_pack = db.get_closing_index_price(
quarterly_date, index)
if quarterly_stock_pack is None or quarterly_index_pack is None:
logger.info(
"Calculate Lev07: Quaterly Date Pack or Quaterly Index Pack is None for stock: %s"
% stock)
continue
quarterly_stock_closing_price, quarterly_stock_actual_date = (
quarterly_stock_pack)
quarterly_index_closing_price, quarterly_index_actual_date = (
quarterly_index_pack)
day_before_actual_date_stock = date.edit_date(
quarterly_stock_actual_date, cst.DT_MINUS, 1, cst.DT_DAY)
day_before_actual_date_index = date.edit_date(
quarterly_index_actual_date, cst.DT_MINUS, 1, cst.DT_DAY)
compare_price_stock = db.get_closing_stock_price(
day_before_actual_date_stock, stock)
compare_price_index = db.get_closing_index_price(
day_before_actual_date_index, index)
if compare_price_stock is None:
logger.info(
"Calc Lev07: Compare Price Stock is None for stock: %s" %
stock)
continue
if compare_price_index is None:
logger.info(
"Calc Lev07: Compare Price Index is None for stock: %s" %
stock)
continue
if compare_price_stock[0] is None or compare_price_stock[0] == 0:
logger.info(
"Calculate Lev07: First Item in Compare Price Stock is None or 0: %s"
% stock)
continue
if compare_price_index[0] is None or compare_price_index[0] == 0:
logger.info(
"Calculate Lev07: First Item in Compare Price Index is None or 0: %s"
% stock)
continue
stock_diff = (quarterly_stock_closing_price /
compare_price_stock[0]) - 1
index_diff = (quarterly_index_closing_price /
compare_price_index[0]) - 1
if index_diff == 0:
continue
combined_diff = round(stock_diff - index_diff, 2)
if combined_diff >= 0.01:
lev_score = 1
elif -0.01 < combined_diff < 0.01:
lev_score = 0
elif combined_diff <= -0.01:
lev_score = -1
db.save_quarterly_reaction_to_db(stock, combined_diff, lev_score)
def levermann_04_05():
stock_list = db.get_list(table=cst.TABLE_STOCKS, columns=cst.COLUMN_URI)
for stock in stock_list:
# current_stock_price = db.get_latest_stock_price(stock)
current_stock_price = db.get_item(
table=cst.TABLE_STOCKS_HISTORIES,
column=cst.COLUMN_CLOSING_VALUE,
condition=[cst.COLUMN_STOCK_URI, stock],
order=[cst.COLUMN_DATE, cst.DESC],
)
eps = db.get_latest_eps(stock)
if current_stock_price is None or eps is None:
logger.info("Calculate Lev04/05: Current Stockprice is None: %s" %
stock)
continue
if eps is None:
logger.info("Calculate Lev04/05: EPS is None for stock: %s" %
stock)
continue
if None in eps:
logger.info("Calculate Lev04/05: Any Nones in EPS for stock: %s" %
stock)
continue
eps_0 = eps[3]
kgv5 = float(current_stock_price) / (sum(eps) /
5) if sum(eps) != 0 else 9999.99
kgv5 = 9999.99 if kgv5 > 9999.99 else kgv5
kgv0 = float(current_stock_price) / eps_0 if eps_0 != 0 else 14
kgv0 = 9999.99 if kgv0 > 9999.99 else kgv0
if 0 < kgv5 < 12:
lev_04_score = 1
elif 12 < kgv5 < 16:
lev_04_score = 0
elif kgv5 <= 0 or kgv5 >= 16:
lev_04_score = -1
else:
lev_04_score = 0
# db.save_kgv5_to_db(stock, round(kgv5, 2), lev_04_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_04_val=round(kgv5, 2),
lev_04_sco=lev_04_score,
)
if 0 < kgv0 < 12:
lev_05_score = 1
elif 12 < kgv0 < 16:
lev_05_score = 0
elif kgv0 <= 0 or kgv0 >= 16:
lev_05_score = -1
else:
lev_05_score = 0
# db.save_kgv0_to_db(stock, round(kgv0, 2), lev_05_score)
db.upsert_item(
table=cst.TABLE_LEVERMANN,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
current_date=date.get_current_date(),
stock_uri=stock,
lev_05_val=round(kgv0, 2),
lev_05_sco=lev_05_score,
)
def test_write_stock_overview_history_to_db(self):
overview_stock_history = [
["08.02.2019", "197,90"],
["07.02.2019", "197,90"],
["06.02.2019", "201,60"],
["05.02.2019", "202,70"],
["04.02.2019", "196,45"],
]
stock_uri = "adidas-Aktie"
self.assertTrue(
db.write_stock_overview_history_to_db(overview_stock_history,
stock_uri,
cst.TEST_DATABASE))
asserted_database_content = [
[
"adidas-Aktie",
date.string_to_date("28.01.2019"), 203.10, 202.80
],
[
"adidas-Aktie",
date.string_to_date("29.01.2019"), 203.20, 204.90
],
[
"adidas-Aktie",
date.string_to_date("30.01.2019"), 204.10, 205.90
],
[
"adidas-Aktie",
date.string_to_date("31.01.2019"), 206.90, 207.40
],
[
"adidas-Aktie",
date.string_to_date("01.02.2019"), 202.30, 199.40
],
["adidas-Aktie",
date.string_to_date("04.02.2019"), None, 196.45],
["adidas-Aktie",
date.string_to_date("05.02.2019"), None, 202.70],
["adidas-Aktie",
date.string_to_date("06.02.2019"), None, 201.60],
["adidas-Aktie",
date.string_to_date("07.02.2019"), None, 197.90],
["adidas-Aktie",
date.string_to_date("08.02.2019"), None, 197.90],
]
validating_list = db.get_list(
table=cst.TABLE_STOCKS_HISTORIES,
columns=[
cst.COLUMN_STOCK_URI,
cst.COLUMN_DATE,
cst.COLUMN_START_VALUE,
cst.COLUMN_CLOSING_VALUE,
],
condition=[cst.COLUMN_STOCK_URI, "adidas-Aktie"],
database=cst.TEST_DATABASE,
)
converted_validating_list = [[
item[0],
item[1],
float(item[2]) if item[2] is not None else None,
float(item[3]) if item[3] is not None else None,
] for item in validating_list]
self.assertEqual(asserted_database_content.sort(),
converted_validating_list.sort())
def test_upsert_new_single_overview_data_to_db(self):
current_date = "2019-03-23"
stock_uri = "coca-cola-Aktie"
market_cap = "171390.00"
stock_indices = [
"Dow Jones",
"S&P 500",
"S&P 100",
"NYSE US 100",
"IGPA",
"BX Swiss - USA",
]
stock_sectors = '["Getränke / Tabak"]'
db.upsert_item(
table=cst.TABLE_COMPANY_DATA,
primary_keys=[cst.COLUMN_STOCK_URI, cst.COLUMN_DATE],
database=cst.TEST_DATABASE,
stock_uri=stock_uri,
market_cap=market_cap,
current_date=current_date,
stock_indices=str(stock_indices),
stock_sectors=str(stock_sectors),
)
asserted_company_data_content_after = [
[
"3i-Aktie", '["FTSE 100", "FTSE Allshare"]',
'["Finanzdienstleister"]'
],
[
"ab_inbev-Aktie",
'["EURO STOXX 50", "STOXX 50", "BEL 20", "EURONEXT 100", "EURO STOXX", "Next CAC 70", "BX Swiss - EMEA"]',
'["Getränke / Tabak"]',
],
[
"ab_inbev-Aktie",
'["STOXX 50", "EURO STOXX 50", "BEL 20", "EURONEXT 100", "EURO STOXX", "Next CAC 70", "BX Swiss - EMEA"]',
'["Getränke / Tabak"]',
],
[
"bechtle-Aktie",
'["TecDAX", "MDAX", "Prime All Share", "LMDAX", "Technology All Share", "HDAX", "LTecDAX", "CDAX", "MDAX Kursindex", "TecDAX Kursindex", "BX Swiss - EMEA", "DAXglobal Sarasin Sustainability Germany Index EUR", "QIX Deutschland", "DAXglobal Sarasin Sustainability Germany", "Schatten-Index-SDAX", "Schatten-Index-TecDAX"]',
'["IT-Dienstleister", "IT-Beratung Hardware", "Dienstleistungen", "Internethandel (B2B, B2C)", "Informationstechnologie"]',
],
[
"cellcom_israel-Aktie",
'["TA-100"]',
'["Telekommunikation", "Mobilkommunikation", "Netzbetreiber (Carrier)", "IT-Dienstleister"]',
],
[
"coca-cola-Aktie",
"['Dow Jones', 'S&P 500', 'S&P 100', 'NYSE US 100', 'IGPA', 'BX Swiss - USA']",
'["Getränke / Tabak"]',
],
]
self.assertEqual(
asserted_company_data_content_after,
db.get_list(
table=cst.TABLE_COMPANY_DATA,
columns=[
cst.COLUMN_STOCK_URI, cst.COLUMN_INDICES,
cst.COLUMN_SECTORS
],
database=cst.TEST_DATABASE,
),
)