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