def query_sp500(self, debug=False): """ query over the SP 500 stocks :param debug: whether use the debug mode or not :return: """ results = [] db = MorningStarDB() db.connect() log_info = LogInfo() num_stock_scanned = 0 for stock in constants.SP500_2015_10: fin = MorningStarFinancial(stock) num_stock_scanned += 1 if db.retrieve_fundamentals(fin) and db.retrieve_historical_prices( fin): should_keep = True for tmp_filter in self._criterion: if not tmp_filter.meet(fin, log_info): should_keep = False break if should_keep: results.append(stock) print "selected " + stock + ", score = " + str( log_info.get(IntrinsicValueToMarketPrice, "score")) if num_stock_scanned % 10 == 0: print str(num_stock_scanned) + " stocks were scanned." db.close() return results
def _parse_dividend_data(json_obj, fin): """ Extract the Dividend data from the parsed JSON object into the financial object :param json_obj: the parsed json object :type json_obj: dict :param fin: the morningstar financial object of the stock :type fin: financial.Financial :return: whether success or not """ stock = fin.stock if HistoricalFetcher.DIVIDEND_DATA not in json_obj: raise fetcher_exception.FetcherException( HistoricalFetcher.DIVIDEND_DATA + " is not in the json response for " + stock) dividend_data = json_obj[HistoricalFetcher.DIVIDEND_DATA] dividends = {} num_points = len(dividend_data) if num_points == 0: LogInfo.info(stock + " has no dividend.") else: for idx in range(num_points): record = dividend_data[idx] dividend_date = record["Date"] dividend_type = record["Type"] if dividend_type == "Dividend": desc = record["Desc"] # e.g., "Dividends:0.5200" tokens = desc.split(":") div_str = tokens[1].strip().replace("<br>", "") dividends[dividend_date] = float(div_str) # e.g., 0.5200 fin.stock_dividend_date = dividends return True
def fetch_quote(self, f, num_retries=3): """ Directly call Yahoo Finance website to get the stock quote. See Details: http://wern-ancheta.com/blog/2015/04/05/getting-started-with-the-yahoo-finance-api/ :param f: the financial object :type f: pyvalue.yahoofinance.financial.Financial :param num_retries: the number of retries :type num_retries: int :return: """ stock = f.stock url = ( r'http://finance.yahoo.com/d/quotes.csv?s={0}&f=d1t1l1ghc1vj1b4j4dr1qyep6p5' .format(stock)) for try_idx in range(num_retries): try: response = urllib2.urlopen(url) csv = response.read() if len(csv.strip()) == 0: raise YahooFinanceFetcherException( "Empty response of the http request.") Fetcher._parse_quote_csv(csv, f) return True except Exception as err: traceback.print_exc() LogInfo.info(stock + " : " + err.message + " in the " + str((try_idx + 1)) + " time") if try_idx == num_retries - 1: LogInfo.error('Failed to retrieve information for ' + stock) return False
def update_sp500_morningstars_stock_historical(start_date, end_date, overwrite=True, use_cache=False): db_conn = MorningstarDB() db_conn.connect() num_stock_updated = 0 for stock in constants.get_sp_500_universe(): fin = MorningstarFinancial(stock) success = MorningstarFetcher.fetch_stock_historical( fin, start_date, end_date, use_cache=use_cache) log_msg = "" if (fin is None) or (not success): log_msg += "no result for " + stock + ", " else: ret = db_conn.update_historical_stock_price(fin, overwrite=overwrite) ret |= db_conn.update_historical_dividend_date(fin, overwrite=overwrite) if ret: log_msg += "updated " + stock + ", " else: log_msg += "no update for " + stock + ", " log_msg += "total " + str(num_stock_updated + 1) + " stocks processed." LogInfo.info(log_msg) num_stock_updated += 1 db_conn.close()
def update_sp500_morningstars_fundamental(columns=None, overwrite=True, use_cache=False): db_conn = MorningstarDB() db_conn.connect() num_stock_updated = 0 for stock in constants.get_sp_500_companies(): fin = MorningstarFinancial(stock) success = MorningstarFetcher.fetch_fundamental(fin, use_cache=use_cache) log_msg = "" if (fin is None) or (not success): log_msg += "no result for " + stock + ", " else: ret = db_conn.update_fundamentals(fin, columns=columns, overwrite=overwrite) if ret: log_msg += "updated " + stock + ", " else: log_msg += "no update for " + stock + ", " log_msg += "total " + str(num_stock_updated + 1) + " stocks processed." LogInfo.info(log_msg) num_stock_updated += 1 db_conn.close()
def init(): package_dir = os.path.dirname(constants.__file__) config_file = package_dir + "/../config.ini" global config if config is None: config = ConfigParser.ConfigParser() LogInfo.info("Load config file : " + config_file) config.read(config_file)
def connect(self): if self._conn is not None: LogInfo.info("already connected") return self._conn = pymysql.connect(host=self._db_server, port=self._db_port, user=self._db_username, passwd=self._db_password, db=self.DB_NAME)
def _update_single_column(self, stock, date_values, currency, version, table_name, column_name, overwrite): if currency is not None: currency = currency.upper() # extract the existing records of the stock and version cur = self._conn.cursor() cur.execute("SELECT STOCK, DATE FROM "+table_name+" WHERE STOCK = '%s' AND VERSION = '%s'" % (stock, version)) result = cur.fetchall() existing_dates = [] for row in result: date = row[1] existing_dates.append(date) cur.close() existing_dates = set(existing_dates) # Insert to update the financial values in the database cur = self._conn.cursor() sql_insert = "INSERT INTO " + table_name + "(STOCK, DATE, VERSION, " + column_name + \ ") VALUES('%s','%s','%s','%s')" sql_insert_currency = "INSERT INTO " + table_name + "(STOCK, DATE, VERSION, " + column_name + \ ", CURRENCY) VALUES('%s','%s','%s','%s', '%s')" sql_update = "UPDATE " + table_name \ + " SET TS=CURRENT_TIMESTAMP(), " \ + column_name + " = '%s' " \ + " WHERE STOCK = '%s' AND DATE = '%s' " \ + " AND VERSION = '%s'" sql_update_currency = "UPDATE " + table_name\ + " SET TS=CURRENT_TIMESTAMP(), "\ + column_name + " = '%s', "\ + " CURRENCY = '%s' " \ + " WHERE STOCK = '%s' AND DATE = '%s' "\ + " AND VERSION = '%s'" has_updated = False for date in date_values: value = date_values[date] # check the value is nan or not if math.isnan(value): LogInfo.error(stock + ' : the value of '+column_name+ '/'+table_name+ ' at '+date+' is nan, skip') continue try: if date in existing_dates: if overwrite: if currency is None: cur.execute(sql_update % (value, stock, date, version)) else: cur.execute(sql_update_currency % (value, currency, stock, date, version)) has_updated = True else: if currency is None: cur.execute(sql_insert % (stock, date, version, value)) else: cur.execute(sql_insert_currency % (stock, date, version, value, currency)) has_updated = True except pymysql.err.DataError as err: LogInfo.error(err.message) cur.close() return has_updated
def update_morningstar_fundamental(stock, overwrite=True, use_cache=False): fin = MorningstarFinancial(stock) success = MorningstarFetcher.fetch_fundamental(fin, use_cache=use_cache) if (fin is None) or (not success): LogInfo.info("No result") return print fin.debug_info() db_conn = MorningstarDB() db_conn.connect() db_conn.update_fundamentals(fin, overwrite=overwrite) db_conn.close()
def fetch(self, fin, start_date, end_date, num_retries=3, use_cache=False): """ Fetch the fundamental data of a security :param fin: the morningstar financial object of the stock :type fin: financial.Financial :param start_date: the start date :type start_date: str :param end_date: the end date :type end_date: str :param num_retries: the number of retries :type num_retries: int :param use_cache: whether to use cache if cache exists :type use_cache: bool :return: whether success or not """ stock = fin.stock url = ( r'http://globalquote.morningstar.com/globalcomponent/RealtimeHistoricalStockData.ashx?' r'ticker={0}&showVol=true&dtype=his&f=d&curry=USD' r'&range={1}|{2}&isD=true&isS=true&hasF=true&ProdCode=DIRECT'. format(stock, start_date, end_date)) for try_idx in range(num_retries): try: filename = "/tmp/" + stock + "_prices.json" if use_cache and HistoricalFetcher._has_cache(filename): tmp_file = open(filename, "r") json_text = tmp_file.read() tmp_file.close() else: response = urllib2.urlopen(url) json_text = response.read() if len(json_text.strip()) == 0 or json_text.strip( ) == 'null': raise fetcher_exception.FetcherException( "Empty response of the http request for " + stock) tmp_file = open(filename, "w") tmp_file.write(json_text) tmp_file.close() success = HistoricalFetcher._parse_json(json_text, fin) if success: return True except Exception as err: traceback.print_exc() LogInfo.info(stock + " : " + err.message + " in the " + str((try_idx + 1)) + " time for " + stock) if try_idx == num_retries - 1: LogInfo.error('Failed to retrieve information for ' + stock) return False
def update_yahoofinance_stock_historical(stock, start_date, end_date): fetcher = YahooFinanceFetcher() db_conn = YahooFinancialDB() db_conn.connect() fin = YahooFinanceFinancial(stock) success = fetcher.fetch_historical(fin, start_date, end_date) log_msg = "" if not success: log_msg += "no result for " + stock else: db_conn.update_historical(fin) log_msg += "updated " + stock log_msg += " , 1 stock processed." LogInfo.info(log_msg) db_conn.close()
def update_yahoofinance_stock_quote(stock): fetcher = YahooFinanceFetcher() db_conn = YahooFinancialDB() db_conn.connect() num_stock_updated = 0 fin = YahooFinanceFinancial(stock) success = fetcher.fetch_quote(fin) log_msg = "" if not success: log_msg += "no result for " + stock else: db_conn.update_quote(fin) log_msg += "updated " + stock log_msg += " , " + str(num_stock_updated + 1) + " stocks processed." LogInfo.info(log_msg) num_stock_updated += 1 db_conn.close()
def update_nasdaq_eft_yahoofinance_stock_historical(start_date, end_date): fetcher = YahooFinanceFetcher() db_conn = YahooFinancialDB() db_conn.connect() num_stock_updated = 0 for stock in constants.get_nasaq_efts_symbols(): fin = YahooFinanceFinancial(stock) success = fetcher.fetch_historical(fin, start_date, end_date) log_msg = "" if not success: log_msg += "no result for " + stock else: db_conn.update_historical(fin) log_msg += "updated " + stock log_msg += " , " + str(num_stock_updated + 1) + " stocks processed." LogInfo.info(log_msg) num_stock_updated += 1 db_conn.close()
def update_morningstar_stock_historical(stock, start_date, end_date, overwrite=True, use_cache=False): fin = MorningstarFinancial(stock) success = MorningstarFetcher.fetch_stock_historical(fin, start_date, end_date, use_cache=use_cache) if (fin is None) or (not success): LogInfo.info("No result") return print fin.debug_info() db_conn = MorningstarDB() db_conn.connect() db_conn.update_historical_stock_price(fin, overwrite=overwrite) db_conn.update_historical_dividend_date(fin, overwrite=overwrite) db_conn.close()
def fetch(self, fin, num_retries=3, use_cache=False): """ Fetch the fundamental data of a security :param fin: the morningstar financial object of the stock :type fin: financial.Financial :param num_retries: the number of retries :type num_retries: int :param use_cache: whether to use cache if cache exists :type use_cache: bool :return: whether success or not """ stock = fin.stock url = ( r'http://financials.morningstar.com/ajax/exportKR2CSV.html?' + r'&callback=?&t={0}®ion=usa&culture=en-US&cur=USD'.format(stock) ) for try_idx in range(num_retries): try: filename = "/tmp/" + stock + ".csv" if use_cache and FundamentalFetcher._has_cache(filename): tmp_file = open(filename, "r") html = tmp_file.read() tmp_file.close() else: response = urllib2.urlopen(url) html = response.read() if len(html.strip()) == 0: raise fetcher_exception.FetcherException( "Empty response of the http request.") tmp_file = open(filename, "w") tmp_file.write(html) tmp_file.close() self._parse_html(html, fin) return True except Exception as err: traceback.print_exc() LogInfo.info(stock + " : " + err.message + " in the " + str((try_idx + 1)) + " time") if try_idx == num_retries - 1: LogInfo.error('Failed to retrieve information for ' + stock) return False
def fetch_historical(self, f, start_date, end_date, num_retries=3): """ :param f: the financial object :type f: pyvalue.yahoofinance.financial.Financial :param start_date: the start date :type start_date: str :param end_date: the end data :type end_date: str :param num_retries: the number of retries :type num_retries: int :return: """ stock = f.stock start_year, start_month, start_day = Fetcher._split_date_str( start_date) end_year, end_month, end_day = Fetcher._split_date_str(end_date) url = (r'http://ichart.finance.yahoo.com/table.csv?s={0}' r'&a={1}&b={2}&c={3}&d={4}&e={5}&f={6}&g=d&ignore=.csv'.format( stock, start_month - 1, start_day, start_year, end_month - 1, end_day, end_year)) for try_idx in range(num_retries): try: response = urllib2.urlopen(url) csv = response.read() if len(csv.strip()) == 0: raise YahooFinanceFetcherException( "Empty response of the http request.") Fetcher._parse_historical_csv(csv, f) return True except Exception as err: traceback.print_exc() LogInfo.info(stock + " : " + err.message + " in the " + str((try_idx + 1)) + " time") if try_idx == num_retries - 1: LogInfo.error('Failed to retrieve information for ' + stock) return False
def update_quote(self, fin, version='1'): stock = fin.stock if stock is None: LogInfo.error("the stock is None") return False if fin.trade_datetime is None: LogInfo.error(stock + " : the trade datetime is None") return False cur = self._conn.cursor() cur.execute("SELECT STOCK, TRADE_DATETIME_UTC FROM " + self.STOCK_QUOTE_TABLE + " WHERE STOCK = '%s' AND VERSION = '%s'" % (stock, version)) result = cur.fetchall() existing_datetimes = [] for row in result: date = row[1].strftime('%Y-%m-%d %H:%M:%S') existing_datetimes.append(date) cur.close() existing_datetimes = set(existing_datetimes) # Insert to update the financial values in the database cur = self._conn.cursor() sql_insert = ( "INSERT INTO " + Database.STOCK_QUOTE_TABLE + " (STOCK, TRADE_DATETIME_UTC, VERSION, " " PRICE, DAYS_HIGH, DAYS_LOW, PRICE_CHANGE, " " VOLUME, MARKET_CAP_IN_MILLIONS, BOOK_VALUE, EBITDA_IN_MILLIONS," " DIVIDEND_SHARE, DIVIDEND_PAY_DATE, EX_DIVIDEND_DATE, " " DIVIDEND_YIELD, EARNING_SHARE, PRICE_BOOK, PRICE_SALES) " "VALUES(" # STOCK, TRADE_DATETIME_UTC, VERSION " '%s', '%s', '%s'," # PRICE, DAYS_HIGH, DAYS_LOW, PRICE_CHANGE " %s, %s, %s, %s, " # VOLUME, MARKET_CAP_IN_MILLIONS, BOOK_VALUE, EBITDA_IN_MILLIONS, " %s, %s, %s, %s, " # DIVIDEND_SHARE, DIVIDEND_PAY_DATE, EX_DIVIDEND_DATE, " %s, %s, %s, " # DIVIDEND_YIELD, EARNING_SHARE, PRICE_BOOK, PRICE_SALES " %s, %s, %s, %s )") sql_update = ("UPDATE " + Database.STOCK_QUOTE_TABLE + " SET TS=CURRENT_TIMESTAMP(), " " PRICE = %s," " DAYS_HIGH = %s, " " DAYS_LOW = %s, " " PRICE_CHANGE = %s, " " VOLUME = %s, " " MARKET_CAP_IN_MILLIONS = %s, " " BOOK_VALUE = %s, " " EBITDA_IN_MILLIONS = %s, " " DIVIDEND_SHARE = %s, " " DIVIDEND_PAY_DATE = %s, " " EX_DIVIDEND_DATE = %s, " " DIVIDEND_YIELD = %s, " " EARNING_SHARE = %s, " " PRICE_BOOK = %s, " " PRICE_SALES = %s " "WHERE STOCK = '%s' AND TRADE_DATETIME_UTC = '%s' " " AND VERSION = '%s'") cur_datetime = fin.trade_datetime.strftime( '%Y-%m-%d %H:%M:%S') # Only works for UTC timezone value_tuple = ( fin.price, fin.days_high, fin.days_low, fin.price_change, fin.volume, fin.market_cap_in_millions, fin.book_value, fin.ebitda_in_millions, fin.dividend_share, None if fin.dividend_pay_date is None else fin.dividend_pay_date.strftime('%Y-%m-%d'), None if fin.ex_dividend_date is None else fin.ex_dividend_date.strftime('%Y-%m-%d'), fin.dividend_yield, fin.earning_share, fin.price_book, fin.price_sales, ) # Normalize the value tuple, if the value is None, change it to 'NULL' IN mysql sql value_tuple = Database._process_tuple_value(value_tuple) if cur_datetime in existing_datetimes: value_tuple += (stock, cur_datetime, version) cur.execute(sql_update % value_tuple) else: value_tuple = (stock, cur_datetime, version) + value_tuple cur.execute(sql_insert % value_tuple) cur.close() self._conn.commit()