def scrap_index_histories():
scrap_list = db.create_active_index_url_list(cst.URL_INDEX_HISTORY)
driver = scrap.init_driver(third_party_cookies=True)
for url in scrap_list:
index_uri = url.split("/")[-1]
end_date = date.get_current_date()
start_date = date.subtract_one_year(date.get_current_date())
max_db_date = db.get_latest_date_from_index_history(index_uri)
file_name = cst.PATH_INDEX_HISTORY + index_uri + cst.HTML_EXTENSION
if max_db_date is None:
logger.warning("Scrap Index Histories: MaxDate is None for %s" % url)
pass
elif date.add_one_day(max_db_date) == end_date:
logger.info("Scrap Index Histories: EndDate = MaxDate")
scrap.save_soup_to_file("", file_name)
continue
elif max_db_date > start_date:
start_date = date.add_one_day(max_db_date)
date_interval = (
"/" + date.date_to_string(start_date) + "_" + date.date_to_string(end_date)
)
dated_url = url + date_interval
soup = scrap.get_soup_code_from_url(driver, dated_url)
scrap.save_soup_to_file(soup, file_name)
scrap.close_driver(driver)
def scrap_stock_histories():
scrap_list = db.create_stock_url_list(cst.URL_STOCK_HISTORY)
driver = scrap.init_driver(third_party_cookies=True)
for url in scrap_list:
stock_uri = url.split("/")[-2]
end_date = date.get_current_date()
start_date = date.subtract_one_year(date.get_current_date())
max_db_date = db.get_latest_date_from_stock_history(stock_uri + "-Aktie")
file_name = cst.PATH_STOCK_HISTORY + stock_uri + cst.HTML_EXTENSION
if os.path.isfile(file_name):
logger.info(
"Scrap Stock Histories: Skip existing File for stock: %s" % stock_uri
)
continue
if max_db_date is None:
pass
elif date.add_one_day(max_db_date) == end_date:
scrap.save_soup_to_file("", file_name)
continue
elif max_db_date > start_date:
start_date = date.add_one_day(max_db_date)
date_interval = (
"/" + date.date_to_string(start_date) + "_" + date.date_to_string(end_date)
)
dated_url = url + date_interval
soup = scrap.get_soup_from_history_url(driver, dated_url)
scrap.save_soup_to_file(soup, file_name)
scrap.close_driver(driver)
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 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 save_quarterly_reaction_to_db(stock_uri,
quarterly_diff,
lev_score,
database=cst.DATABASE):
current_date = date.get_current_date()
with dataset.connect(database) as db:
try:
db[cst.TABLE_LEVERMANN].insert(
dict(
AktienURI=stock_uri,
Datum=current_date,
Lev07_Wert=quarterly_diff,
Lev07_Score=lev_score,
))
except IntegrityError:
db.query("UPDATE %s SET "
"Lev07_Wert = %s, "
"Lev07_Score = %s "
'WHERE AktienURI = "%s" AND Datum = "%s"' % (
cst.TABLE_LEVERMANN,
quarterly_diff,
lev_score,
stock_uri,
current_date,
))
pass
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 get_last_quarterly_figures_date(soup, current_date=date.get_current_date()):
"""
Calculates the newest, but in the past lying date, where a company event has
been held. Returns a date-object
:param soup:
:return: date
"""
result_td = soup.find(cst.HTML_H2, text=re.compile(cst.TEXT_BYGONE_DATES))
parent_div = result_td.parent
dates = parent_div.find_all(cst.HTML_TD, {cst.HTML_CLASS: cst.TEXT_TEXT_RIGHT})
date_list = [date.string_to_date(date_.text.strip()) for date_ in dates]
latest_date = get_latest_date_of_list(date_list, current_date=current_date)
return latest_date
def get_latest_date_of_list(date_list, current_date=date.get_current_date()):
quarter_year_ago = date.edit_date(current_date, cst.DT_MINUS, 3, cst.DT_MONTH)
date_list_past = [
date_ for date_ in date_list if quarter_year_ago < date_ < current_date
]
try:
return max(date_list_past)
except ValueError:
logger.error(
"Get Latest Date of List: AttributeError. "
"Last Date probably not in last quarter"
)
return None
def write_index_content_list_to_db(index_content,
index_name,
database=cst.DATABASE):
current_date = date.get_current_date()
stocks = [dict(Name=stock[0], URI=stock[1]) for stock in index_content]
write_index_content_to_stock_table(stocks, database=database)
index_contents = [
dict(IndexURI=index_name, AktienURI=stock[1], Abrufdatum=current_date)
for stock in index_content
]
write_stock_to_index_contents_table(index_contents, database=database)
return True
def save_momentum_to_db(stock_uri, lev_score, database=cst.DATABASE):
current_date = date.get_current_date()
with dataset.connect(database) as db:
try:
db[cst.TABLE_LEVERMANN].insert(
dict(AktienURI=stock_uri,
Datum=current_date,
Lev11_Score=lev_score))
except IntegrityError:
db.query("UPDATE %s SET "
"Lev11_Score = %s "
'WHERE AktienURI = "%s" AND Datum = "%s"' %
(cst.TABLE_LEVERMANN, lev_score, stock_uri, current_date))
pass
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 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_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 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 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_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_get_todays_date(self):
calc_date = date.get_current_date().strftime("%d.%m.%Y")
self.assertEqual(len(calc_date), 10)
