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