def get_keyratios(isin_list):
kr = gm.KeyRatiosDownloader()
nr_of_stocks = len(isin_list)
nr_failed = 0
print('Fetching key ratios...')
for index, isin in enumerate(isin_list):
if index % 10 == 0:
print('{:.0f}/{:.0f} remaining, {:.0f} failed.'.format(nr_of_stocks-index, nr_of_stocks, nr_failed))
kr_list = []
try:
kr_list = kr.download(isin, full_year=True)
except ValueError:
nr_failed += 1
continue
for kr_index, frame in enumerate(kr_list):
if kr_index not in [0, 2, 8, 9, 10]:
continue
cols = frame.transpose().columns.values
new_cols = [(lambda x: '_'.join([word.lower() for word in x.split(' ') if word not in currencies]))(col) for col in cols]
temporary_frame = frame.copy().transpose()
temporary_frame.columns = new_cols
temporary_frame.index = temporary_frame.index.year
if kr_index == 0:
isin_frame = pd.DataFrame(index=temporary_frame.index)
isin_frame = pd.merge(isin_frame, temporary_frame, left_index=True, right_index=True, how='outer')
isin_frame.insert(0, 'isin', isin)
if index == 0:
output_frame = pd.DataFrame(columns=isin_frame.columns)
try:
output_frame = pd.concat([output_frame, isin_frame])
except AssertionError:
nr_failed += 1
continue
print('Done!')
return output_frame
def get_roe(stock_ticker):
roe_matcher = '.*Return on Equity.*'
kr = gm.KeyRatiosDownloader()
try:
kr_frames = kr.download(stock_ticker)
roe = re.findall(roe_matcher, str(kr_frames[2]))
print('doc', kr_frames[2]['2017']['Return on Equity %'])
except Exception as novalue:
print(f'got Exception {sys.exc_info()}')
roe = NULL
return roe
def book_value_filter(stock_ticker, bv_filter=0.5):
bv_list = []
kr = gm.KeyRatiosDownloader()
kr_frames = kr.download(stock_ticker)
logging.INFO(f'{kr_frames}')
for yearnum in range(2008, 2018):
year = str(yearnum)
key_ratiostatements = kr_frames[0][year].dropna()
book_value = key_ratiostatements.filter(regex='Book Value Per Share')
if book_value:
bv_list.append(book_value)
return bv_list
def get_roe(stock_ticker):
roe_matcher = '.*Return on Equity.*'
kr = gm.KeyRatiosDownloader()
fr = gm.FinancialsDownloader()
try:
kr_frames = kr.download(stock_ticker)
fr_frames = fr.download(stock_ticker)
roe = re.findall(roe_matcher, str(kr_frames[2]))
print('doc', kr_frames[2]['2017']['Return on Equity %'])
print(stock_ticker, 'orginal kr', kr_frames)
print(stock_ticker, 'oringal fr', fr_frames)
#print('tranpos',kr_frames[2].transpose())
except Exception as novalue:
#print (f'got Exception {sys.exc_info()}')
roe = NULL
return roe
def roe_filter(stock_ticker, roe_threshold=20):
kr = gm.KeyRatiosDownloader()
#fr = gm.FinancialsDownloader()
kr_frames = kr.download(stock_ticker)
#fr_frames = fr.download(stock_ticker)
#print(fr_frames)
#print (kr_frames)
roe_list = []
for yearnum in range(2008, 2018):
year = str(yearnum)
#book_value=kr_frames[0][year]['Book Value Per Share * HKD']
#print(f'Book value{book_value}')
key_ratiostatements = kr_frames[0][year].dropna()
key_profitabilityRatioStatements = kr_frames[2][year].dropna()
#print( key_ratiostatements)
#financial_ratiostatements = fr_frames[0][year]
eps = [
value for key, value in key_ratiostatements.items()
if 'Earnings Per Share' in key
]
#print (f'year {year} eps {eps}')
roe = [
value for key, value in key_profitabilityRatioStatements.items()
if 'Return on Equity' in key
]
#print (f'year {year} roe {roe}')
if roe != []:
roe_list.append(roe[0])
sum = 0
count = 0
for i in roe_list:
sum = sum + i
count = count + 1
average_roe = sum / count
#print (roe_list, average_roe)
if average_roe > roe_threshold:
return average_roe
else:
return False
def get_key_ratios(tickerset = pd.DataFrame(), ignoreset = np.array([]), replace = False, exchange = 'nyse', reg = 'usa', cur = 'USD'):
if len(tickerset) == 0:
sp500, nasdaq = load_tickers()
tickers = sp500
else:
tickers = tickerset
KRD = gm.KeyRatiosDownloader()
for index, row in tickers.iterrows():
ticker = str(row[0])
if ticker in ignoreset:
continue
if not os.path.exists('./data/key_ratios/{ex}/{t}.pkl'.format(ex = exchange, t=ticker)) or replace == True:
print('Downloading KR of {}'.format(ticker))
df = KRD.download(ticker, region=reg, currency=cur)
with open('./data/key_ratios/{ex}/{t}.pkl'.format(ex = exchange, t=ticker), 'wb') as filehandle:
#print(filehandle)
pickle.dump(df, filehandle)
else:
print('Already have KR for {}'.format(ticker))
pass
def _refreshRatiosDB(self, ticker, force_refresh, exchange="XNYS"):
"""
Uses goodmorning to populate ratios
"""
frames = None
ms_ticker = ticker
if exchange != "":
ms_ticker = "{exchange}:{ticker}".format(exchange=exchange,
ticker=ticker)
frames = self.restore_frames(ticker)
if frames and not force_refresh:
logger.debug(
"Found frames in pickle {ticker}".format(ticker=ticker))
else:
logger.debug(
"Refreshing from GoodMorning {ticker} {exchange} {ms_ticker}".
format(ticker=ticker, exchange=exchange, ms_ticker=ms_ticker))
kr = gm.KeyRatiosDownloader()
frames = kr.download(ms_ticker)
if frames:
# Deal with the key ratios and write them to the DB
call = []
# Key Financials
financials = frames[0]
health = frames[9]
temp_objects = {}
last_period = True
for year in sorted(financials, reverse=True):
series = financials[year]
period = self._datefromperiod(year)
# if last_period:
# period = self._datefromperiod("1900")
# last_period = False
revenue = self._sanitise(series[0])
gross_margin = self._sanitise(series[1])
operating_income = self._sanitise(series[2])
operating_margin = self._sanitise(series[3])
net_income = self._sanitise(series[4])
eps = self._sanitise(series[5])
dividends = self._sanitise(series[6])
payout_ratio = self._sanitise(series[7])
shares = self._sanitise(series[8])
bps = self._sanitise(series[9])
operating_cash_flow = self._sanitise(series[10])
cap_spending = self._sanitise(series[11])
fcf = self._sanitise(series[12])
working_capital = self._sanitise(series[13])
# Matching Health Data
h_s = health[year]
current_ratio = self._sanitise(h_s["Current Ratio"])
debt_equity = self._sanitise(h_s["Debt/Equity"])
logger.debug(
"Found and starting to build {ticker} {period} {eps}".
format(ticker=ticker, period=period, eps=eps))
# Check if it exists
query = stockdatamodel.Ratio.query.filter(
stockdatamodel.Ratio.ticker == ticker).filter(
stockdatamodel.Ratio.period == period)
if query.count() < 1:
logger.debug(
"Don't have this yet {ticker} {period} {eps}".format(
ticker=ticker, period=period, eps=eps))
f = stockdatamodel.Ratio(
ticker=ticker,
period=period,
revenue=revenue,
gross_margin=gross_margin,
operating_income=operating_income,
operating_margin=operating_margin,
net_income=net_income,
eps=eps,
dividends=dividends,
payout_ratio=payout_ratio,
shares=shares,
bps=bps,
operating_cash_flow=operating_cash_flow,
cap_spending=cap_spending,
fcf=fcf,
working_capital=working_capital,
current_ratio=current_ratio,
debt_equity=debt_equity,
)
db_session.add(f)
else:
f = query.first()
logger.debug(f)
# If we need to force refresh update it
if force_refresh:
logger.debug(
"We are force refreshing {ticker} {period}".format(
ticker=ticker, period=period))
# f.ticker = ticker
# f.period = period
f.revenue = revenue
f.gross_margin = gross_margin
f.operating_income = operating_income
f.operating_margin = operating_margin
f.net_income = net_income
f.eps = eps
f.dividends = dividends
f.payout_ratio = payout_ratio
f.shares = shares
f.bps = bps
f.operating_cash_flow = operating_cash_flow
f.cap_spending = cap_spending
f.fcf = fcf
f.working_capital = working_capital
f.current_ratio = current_ratio
f.debt_equity = debt_equity
db_session.commit()
"""
import time
import pymysql
import good_morning as gm
DB_HOST = 'db_host'
DB_USER = '******'
DB_PASS = '******'
DB_NAME = 'db_name'
conn = pymysql.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME)
kr = gm.KeyRatiosDownloader()
fd = gm.FinancialsDownloader()
# Taken from: https://en.wikipedia.org/wiki/List_of_S%26P_500_companies
# Notes:
# * Instead of BF-B use BF.B.
# * Instead of BRK-B use BRK.B.
sp500_2015_10 = [
'A', 'AA', 'AAL', 'AAP', 'AAPL', 'ABBV', 'ABC', 'ABT', 'ACE', 'ACN',
'ADBE', 'ADI', 'ADM', 'ADP', 'ADS', 'ADSK', 'ADT', 'AEE', 'AEP', 'AES',
'AET', 'AFL', 'AGN', 'AIG', 'AIV', 'AIZ', 'AKAM', 'ALL', 'ALLE', 'ALTR',
'ALXN', 'AMAT', 'AME', 'AMG', 'AMGN', 'AMP', 'AMT', 'AMZN', 'AN', 'ANTM',
'AON', 'APA', 'APC', 'APD', 'APH', 'ARG', 'ATVI', 'AVB', 'AVGO', 'AVY',
'AXP', 'AZO', 'BA', 'BAC', 'BAX', 'BBBY', 'BBT', 'BBY', 'BCR', 'BDX',
'BEN', 'BF.B', 'BHI', 'BIIB', 'BK', 'BLK', 'BLL', 'BMY', 'BRCM', 'BRK.B',
'BSX', 'BWA', 'BXLT', 'BXP', 'C', 'CA', 'CAG', 'CAH', 'CAM', 'CAT', 'CB',
def basic():
import good_morning as gm
kr = gm.KeyRatiosDownloader()
kr_frames = kr.download('AAPL')
print(kr_frames[0])
# Predictor Scraper for Linear Models Project - S&P 500 Stock Analysis
import os
os.chdir(r'C:\')
import numpy as np
import pandas as pd
import csv
import good_morning as gm
import time
reader = csv.reader(open('SP500.csv', 'r')) # SP500 list in 2018
reader = list(reader)
used = set()
sp500 = [x for sl in reader for x in sl if x not in used and (used.add(x) or True)]
tikcount = len(sp500)
func = gm.KeyRatiosDownloader()
all_result = func.download('MSFT') # for testing only
# all_result[:] # See all variables
result_df = pd.DataFrame()
for i in range(11):
adding = all_result[i]
result_df = result_df.append(adding)
var_menu = list(result_df.index) # A list of all variables for choosing
'''====================================================================='''
file_name = 'sp500_predictors_run500_v2.csv' # <<< Make sure to change it for each run
run_delay = 1 # Seconds
retry_delay = 5 # Seconds