def calc_magic_formula_trailing():
LOGGER.info('Calculating Magic Formula Trailing')
strategy = "magic_formula_ttm"
query = d.Company.raw('''SELECT company.symbol,
financialdata.rank_pe_ratio_ttm + financialdata.rank_return_on_assets AS score
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.rank_pe_ratio_ttm > 0
AND financialdata.rank_return_on_assets > 0
AND company.sector IS NOT 'Finance'
AND company.sector IS NOT 'Energy'
AND company.sector IS NOT 'Miscellaneous'
AND company.sector NOT LIKE '%Utilities%'
ORDER BY score ASC''')
rankings = alg.getRank(query, strategy)
count = 0
for x in rankings:
count = count + 1
data.set_financial_data(company=x["company"],
symbol=x["symbol"],
date=get_time(),
magic_formula_trailing=x[strategy])
LOGGER.info(
'Finished calculating Magic F Trailing - %s total calculations' %
count)
def calc_garp_ratio():
LOGGER.info('Calculating garp ratio')
query = d.Company.raw('''SELECT company.symbol,
financialdata.pe_ratio_ttm / financialdata.peg_ratio as garp_ratio
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.pe_ratio_ttm > 0
AND financialdata.peg_ratio > 0
ORDER BY company.symbol ASC''')
financials = []
for row in query:
symbol = row.symbol
garp_ratio = row.garp_ratio
values = {}
values.update({
"company": row.id,
"symbol": symbol,
"garp_ratio": garp_ratio
})
financials.append(values)
count = 0
for x in financials:
count = count + 1
data.set_financial_data(company=x["company"],
symbol=x["symbol"],
date=get_time(),
garp_ratio=x["garp_ratio"])
LOGGER.info('Finished calculating garp_ratio - %s total calculations' %
count)
def calc_magic_formula_trailing():
LOGGER.info('Calculating Magic Formula Trailing')
strategy = "magic_formula_ttm"
query = d.Company.raw(
'''SELECT company.symbol,
financialdata.rank_pe_ratio_ttm + financialdata.rank_return_on_assets AS score
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.rank_pe_ratio_ttm > 0
AND financialdata.rank_return_on_assets > 0
AND company.sector IS NOT 'Finance'
AND company.sector IS NOT 'Energy'
AND company.sector IS NOT 'Miscellaneous'
AND company.sector NOT LIKE '%Utilities%'
ORDER BY score ASC'''
)
rankings = alg.getRank(query, strategy)
count = 0
for x in rankings:
count = count + 1
data.set_financial_data(
company = x["company"],
symbol = x["symbol"],
date=get_time(),
magic_formula_trailing = x[strategy]
)
LOGGER.info('Finished calculating Magic F Trailing - %s total calculations' % count)
def get_yahoo_roa(companies):
url = 'https://finance.yahoo.com/q/ks'
for company in companies:
LOGGER.info('Getting ks: %s' % company.symbol)
map_data = {
'Return on Assets (ttm):': {
'key': 'return_on_assets',
'decode': decode_percent,
},
'Return on Equity (ttm):': {
'key': 'return_on_equity',
'decode': decode_percent,
},
}
response = requests.get(url, params={'s': company.symbol})
soup = BeautifulSoup(response.text, 'html.parser')
for doc in soup.body.find_all('tr'):
try:
md = map_data[doc.td.text]
if doc.td.text in map_data:
md['value'] = doc.contents[1].text.strip()
except:
continue
extra = {}
for md in map_data.values():
if 'value' not in md:
continue
value = md['decode'](md['value'])
if value is not None:
extra[md['key']] = value
if extra:
timestamp = get_time()
LOGGER.info('Setting ks: %s: %s' % (company.symbol, extra))
data.set_financial_data(company=company, symbol=company.symbol, date=timestamp, **extra)
else:
LOGGER.info('Skipping ks: %s' % company.symbol)
def calc_pe_ratio_ftm():
LOGGER.info('Calculating pe ratio')
query = d.Company.raw(
'''SELECT company.symbol,
financialdata.ask / financialdata.EPSEstimateNextYear AS future_pe
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.ask IS NOT Null
AND financialdata.EPSEstimateNextYear > 0
ORDER BY company.symbol ASC'''
)
financials = []
for row in query:
symbol = row.symbol
pe_ratio_ftm = row.future_pe
values = {}
values.update({
"company" : row.id,
"symbol" : symbol,
"pe_ratio_ftm" : pe_ratio_ftm
})
financials.append(values)
count = 0
for x in financials:
count = count + 1
data.set_financial_data(
company=x["company"],
symbol=x["symbol"],
date=get_time(),
pe_ratio_ftm=x["pe_ratio_ftm"]
)
LOGGER.info('Finished calculating pe_ratio - %s total calculations' % count)
return financials
def yahoo_finance(sleep_time):
companies = list(data.get_companies())
companies = [companies[i:i+BATCH] for i in range(0, len(companies), BATCH)]
for i, batch in enumerate(companies):
if i > 0: time.sleep(sleep_time)
batch = dict([(c.symbol, c) for c in batch])
url = 'https://query.yahooapis.com/v1/public/yql'
params = {
'q': 'select * from yahoo.finance.quotes where symbol IN ("%s")' % '", "'.join(batch.keys()),
'format': 'json',
'env': 'http://datatables.org/alltables.env',
}
response = requests.get(url, params=params)
body = response.json()
LOGGER.info('Getting quotes: %s' % ', '.join(batch.keys()))
for item in body['query']['results']['quote']:
company = batch[item['symbol']]
timestamp = get_time()
data.set_financial_data(
company=company,
symbol=company.symbol,
date=timestamp,
ask=decode_money(item.get('Ask')),
market_cap=decode_money(item.get('MarketCapitalization')),
ebitda=decode_money(item.get('EBITDA')),
pe_ratio_ttm=decode_float(item.get('PERatio')),
peg_ratio=decode_float(item.get('PEGRatio')),
DividendYield = decode_float(item.get('DividendYield')),
OneyrTargetPrice = decode_float(item.get('OneyrTargetPrice')),
EPSEstimateCurrentYear = decode_float(item.get('EPSEstimateCurrentYear')),
EPSEstimateNextYear = decode_float(item.get('EPSEstimateNextYear')),
EPSEstimateNextQuarter = decode_float(item.get('EPSEstimateNextQuarter')),
)
def calc_garp_ratio():
LOGGER.info('Calculating garp ratio')
query = d.Company.raw(
'''SELECT company.symbol,
financialdata.pe_ratio_ttm / financialdata.peg_ratio as garp_ratio
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.pe_ratio_ttm > 0
AND financialdata.peg_ratio > 0
ORDER BY company.symbol ASC'''
)
financials = []
for row in query:
symbol = row.symbol
garp_ratio = row.garp_ratio
values = {}
values.update({
"company" : row.id,
"symbol" : symbol,
"garp_ratio" : garp_ratio
})
financials.append(values)
count = 0
for x in financials:
count = count + 1
data.set_financial_data(
company=x["company"],
symbol=x["symbol"],
date=get_time(),
garp_ratio=x["garp_ratio"]
)
LOGGER.info('Finished calculating garp_ratio - %s total calculations' % count)
def get_quandl(companies):
for i, company in enumerate(companies):
q_codes ={
"net_income" : "NET_INCOME_Q",
"total_assets" : "TOTAL_ASSETS_Q",
"shares_outstanding" : "TOTAL_COMMON_SHARES_OUTSTANDING_Q"
}
financials = {}
LOGGER.info('Getting quandl income & assets for: %s' % company.symbol)
for k, v in q_codes.iteritems():
code = "RAYMOND/" + company.symbol + "_" + v
try:
stat = Quandl.get(code, rows="1", authtoken=QUAND_KEY)
stat = decode_quandl(stat)
stat = decode_float(stat)
financials.update({k : stat})
except:
stat = "N/A"
stat = decode_float(stat)
financials.update({k : stat})
LOGGER.info('%s --- %s:' % (company.symbol, financials))
timestamp = get_time()
data.set_financial_data(
company=company,
symbol=company.symbol,
date=timestamp,
**financials
)
def calc_pe_ratio_ftm():
LOGGER.info('Calculating pe ratio')
query = d.Company.raw('''SELECT company.symbol,
financialdata.ask / financialdata.EPSEstimateNextYear AS future_pe
FROM company
INNER JOIN financialdata on company.id = financialdata.company_id
WHERE financialdata.ask IS NOT Null
AND financialdata.EPSEstimateNextYear > 0
ORDER BY company.symbol ASC''')
financials = []
for row in query:
symbol = row.symbol
pe_ratio_ftm = row.future_pe
values = {}
values.update({
"company": row.id,
"symbol": symbol,
"pe_ratio_ftm": pe_ratio_ftm
})
financials.append(values)
count = 0
for x in financials:
count = count + 1
data.set_financial_data(company=x["company"],
symbol=x["symbol"],
date=get_time(),
pe_ratio_ftm=x["pe_ratio_ftm"])
LOGGER.info('Finished calculating pe_ratio - %s total calculations' %
count)
return financials
