def deact_mapping_ticker(lkbk=-14,
mxcap=500000000,
debugTF=False,
saveDB=True,
**optx):
'''
De-activate tickers with invalid quotes within last 14 days or marketCap < 500MM
'''
# de-active stale tickers
sys.stderr.write("===STARTING de-active stale tickers\n")
pgDB = conn2pgdb(dbname='ara')
tkCIK = sqlQuery(
"SELECT * FROM mapping_ticker_cik WHERE act_code=1 and ticker not similar to '%%(^|=)%%'"
)
tLst = tkCIK['ticker'].values
dt7d = int(next_date(days=lkbk, dformat='%Y%m%d', dtTF=False))
df = yqc(tLst, tablename=None, screenerTF=False, dfTF=True)
if len(df) < 1:
sys.stderr.write("No deactivate list\n")
return (0)
yqr = 'pbdate<{}|marketCap<{}'.format(dt7d, mxcap)
dg = df.query(yqr)
newLst = dg['ticker'].values
if len(newLst) < 1:
sys.stderr.write("No deactivate list\n")
return (0)
else:
sys.stderr.write("New deactivate List:{}\n".format(newLst))
if saveDB:
xqTmp = "UPDATE mapping_ticker_cik set act_code=0 where ticker in {}"
xqr = xqTmp.format(tuple(newLst))
pgDB.execute(xqr)
return newLst
def run_top10(jobj, engine=None):
xobj = {}
if jobj['category'] == 'backtest':
xql = "SELECT * FROM ara_ranking_backtest_cn"
df = sqlQuery(xql, engine=engine)
xobj = df.to_dict(orient='records')
return xobj
def get_stock_hist(tkLst,ndays=2,xqTmp='',diffTF=False,pgDB=None,end=None,debugTF=False,liveTF=False):
ticker=tkLst[0]
xqx = "select pbdate from prc_hist where name='{}' {} order by pbdate DESC limit {}"
addiClause = '' if end is None else ' AND pbdate<={}'.format(end)
dtmp = sqlQuery(xqx.format(ticker,addiClause,ndays),pgDB)
currdb_date = dtmp['pbdate'].iloc[0]
xdb_date = dtmp['pbdate'].iloc[1]
if end is not None and int(end)>currdb_date and liveTF is True:
from yh_hist_batch import yh_hist
from _alan_date import next_date
s = next_date(end,dformat='%Y%m%d',days=-5,dtTF=False)
ranged = "{},{}".format(s,end)
dtmp = yh_hist(ticker,gap='1d',ranged=ranged,debugTF=debugTF)
sys.stderr.write("===LIVE dtmp:\n{}\n".format(dtmp.tail()))
curryh_date = dtmp['pbdate'].iloc[-1]
else:
curryh_date = currdb_date
if curryh_date>currdb_date:
dx=pd.DataFrame()
for ticker in tkLst:
ticker = ticker.replace('.','-')
dtmp = yh_hist(ticker,gap='1d',ranged=ranged,debugTF=debugTF)[-ndays:]
dtmp['chgPct'] = dtmp['close'].pct_change().values*100.
dtmp['chgLevel'] = dtmp['close'].diff(periods=1).values
dx= pd.concat([dx,dtmp])
dx.rename(columns={'name':'ticker'},inplace=True)
dx.reset_index(drop=True,inplace=True)
sys.stderr.write("===LIVE dx:\n{}\n".format(dx.tail()))
return dx
else:
start = xdb_date
if len(xqTmp)<1:
xqTmp = "select * from prc_hist WHERE name in {} and pbdate>={} {} ORDER BY name,pbdate"
nmLst = [x.replace('.','-') for x in tkLst]
nmPns="('{}')".format("','".join(nmLst))
xqr = xqTmp.format(nmPns,start,addiClause)
dx=sqlQuery(xqr,pgDB)
dx['ticker']= [x.replace('-','.') for x in dx['name'] ]
dx['chgPct']= np.empty(dx['ticker'].shape)
if diffTF is True:
dx['chgLevel']= np.empty(dx['ticker'].shape)
for ticker in tkLst:
dx.loc[dx['ticker']==ticker,'chgPct'] = dx.loc[dx['ticker']==ticker,'close'].pct_change().values*100.
if diffTF is True:
dx.loc[dx['ticker']==ticker,'chgLevel'] = dx.loc[dx['ticker']==ticker,'close'].diff(periods=1).values
return dx
def get_dh(ticker, nfcs=4, nss=4, pgDb=None, sector=None):
""" get eps and stock data and then create dg,dh,dfcs
where
dg: earnings data from temp_eps table
dh: corresponding eps, differnet in PE reverse ratio, and quarterly returns
dfcs: [hwX] forecasts of [ma] period
"""
#- get EPS data
sqx = "SELECT *,\"epsDif\" as ma,rptdate::int/100 as yyyymm FROM temp_eps WHERE ticker={!r} ORDER BY rptdate".format(
ticker)
dg = sqlQuery(sqx, pgDb)
#dg = dg.rename({"pbdate":"asof"},axis='columns')
#- get closing price data
sDu = pull_stock_data(ticker, days=1000, src='iex')[['close', 'pbdate']]
sDu = sDu.rename({"close": "price"}, axis='columns')
cur_prc = sDu['price'][-1]
dx = freq_d2m(sDu, method='last', fq='M')
dx['pbdate'] = dx['pbdate'].astype('int')
dx.loc[:, 'yyyymm'] = (dx['pbdate'][:] / 100).astype('int')
#dx['rtm'] = dx['price'].diff()
dx['rtm'] = dx['price'].pct_change(
) * 100.0 # monthly return and rescale to %
dx['pr1'] = dx['price'].shift()
"""
dx['rt1'] = dx['rt'].shift(-1)
dx['rt2'] = dx['rt'].shift(-2)
dx['pb1'] = dx['pbdate'].shift(-1)
dx['pb2'] = dx['pbdate'].shift(-2)
"""
#- merge data
#dh = pd.merge(dx[['rt1','pb1','rt2','pb2','rt','yyyymm','pbdate','price']],dg[['ma','yyyymm','asof','ticker']],on=['yyyymm'])
dh = pd.merge(dx[['rtm', 'yyyymm', 'pbdate', 'price', 'pr1']],
dg[['ma', 'yyyymm', 'asof', 'ticker']],
on=['yyyymm'])
dh.loc[:, 'ma'] = dh['ma'] / dh['pr1'] * 100.0 # use PE-ratio difference
dh['rt'] = dh['price'].pct_change() * 100.0 # quarterly return
ntr = 5 # nth powter of taylor series
vv = dh['ma'].dropna().astype(float)
if len(vv) < 1:
return [], [], []
for j in range(nfcs):
vv.append(taylor_appx(vv, ntr))
hwX = vv[-nfcs:]
hwN = len(hwX)
hwS = json.dumps(list(hwX))
dfcs = pd.DataFrame(
{
'hwX': [hwS],
'ticker': [ticker],
'prc_cur': [cur_prc],
'sector': [sector]
},
columns=['sector', 'ticker', 'hwX', 'prc_cur'])
return dfcs, dh, dg
def pull_act_tickers(sql=None, engine=None):
if not sql:
sql = "select ticker from mapping_ticker_cik where act_code=1 ORDER BY ticker"
try:
from _alan_calc import sqlQuery
df = sqlQuery(sql, engine=engine)
except Exception as e:
df = {}
return df
def ticker2sectorRanking(ticker,sector='Information Technology',xqTmp='',debugTF=True):
'''
return ranking of 'ticker' in the 'sector'
'''
if not xqTmp:
xqTmp="""Select ticker,"marketCap" from yh_quote_curr where "marketCap">=(SELECT "marketCap" FROM yh_quote_curr WHERE ticker='{ticker}') and ticker in (SELECT ticker from mapping_ticker_cik where sector='{sector}') ORDER BY "marketCap" DESC"""
xqr = xqTmp.format(ticker=ticker,sector=sector)
ret = sqlQuery(xqr)
return ret
def create_theme_media(dbname="ara",
tablename="topic_theme_media",
debugTF=False,
**optx):
''' create MDB::'topic_theme_media' table based on 'madmoney_hist' info
arguments:
'end' default to 2-weeks of today in YYYYMMDD
'callLst' default to ["4","5"] types
'''
zpk = getKeyVal(optx, 'zpk', {'ticker', 'pbdate', 'start', 'end'})
start = getKeyVal(optx, 'start', 0)
end = getKeyVal(optx, 'end', None)
callLst = getKeyVal(optx, 'callLst', ["4", "5"])
if end is None:
end = int(dt2ymd(next_date(weeks=-2)))
xqTmp = "SELECT pbdate,adjusted as price FROM prc_hist where name='{}' and pbdate>{} and pbdate <={} ORDER BY pbdate"
jobj = {"Call": {"$in": callLst}, "pbdate": {"$lte": end, "$gte": start}}
df, mdb, emsg = find_mdb(jobj,
sortLst=["pbdate"],
dbname="ara",
tablename="madmoney_hist",
dfTF=True)
dd = []
for j in df.index:
dx = df.iloc[j].to_dict()
dx['pbdate'] = int(dx['pbdate'])
ticker = dx['ticker']
pbdate = period1 = dx['pbdate']
period2 = int(dt2ymd(next_date(period1, months=1)))
xqr = xqTmp.format(ticker, period1, period2)
dp = sqlQuery(xqr)
vprc = dp['price'].values
if len(vprc) < 1:
continue
vdate = [int(x) for x in dp['pbdate'].values]
sPrice, ePrice = vprc[0], vprc[-1]
period1, period2 = vdate[0], vdate[-1]
rrt = vprc[-1] / vprc[0] * 100. - 100
day_diff = ymd_diff(period1, period2)
dx.update(rrt=rrt, day_diff=day_diff, start=period1, end=period2)
dx.update(sPrice=sPrice, ePrice=ePrice)
sys.stderr.write(
"{j:03d}|{ticker:5s}|{Call}|{rrt:8.2f}|{day_diff:3d}|{start:8d}|{end:8d}|{pbdate:8d}|{Company}|\n"
.format(j=j, **dx))
dd.append(dx)
dy, _, emsg = insert_mdb([dx],
tablename=tablename,
dbname=dbname,
zpk=zpk)
if start > 12345678:
jobj = {"day_diff": {"$lt": 20}, "pbdate": {"$lte": start}}
mdb[dbname][tablename].delete_many(jobj)
df = pd.DataFrame(dd)
return df
def peers_performance(tkLst=[],xqTmp='',debugTF=True):
'''
return peRatio's for comparison based on tickers: 'tkLst'
'''
if not tkLst:
return []
if not xqTmp:
xqTmp="""select ticker,"changePercent"/100.0 as pchg, "trailingPE" as "peRatio" from yh_quote_curr where ticker in ('{}') ORDER BY pchg DESC"""
xqr = xqTmp.format("','".join(tkLst) )
ret = sqlQuery(xqr)
#ret = ret.dropna()
return ret
def get_eqtLst(minMarketCap=50000000000):
'''
return equity list for import highlite based on dow_component,sp500_component, yh_quote_curr
and minimum size (100B) of marketCalp
'''
xqTmp = '''SELECT a.ticker FROM sp500_component a, yh_quote_curr b
WHERE a.ticker=b.ticker AND
(b."marketCap">{} OR a.ticker in (SELECT ticker FROM dow_component))'''
xqr = xqTmp.format(minMarketCap)
try:
eqtLst = list(sqlQuery(xqr)['ticker'])
except Exception as e:
eqtLst=['AAPL','ABT','ACN','ADBE','AMGN','AMZN','AVGO','AXP','BA','BAC','BRK-B','C','CAT','CMCSA','COST','CRM','CSCO','CVX','DHR','DIS','DOW','FB','GOOG','GOOGL','GS','HD','HON','IBM','INTC','JNJ','JPM','KO','LIN','LLY','LMT','MA','MCD','MDT','MMM','MRK','MSFT','NEE','NFLX','NKE','NVDA','ORCL','PEP','PFE','PG','PM','PYPL','SBUX','T','TMO','TRV','TXN','UNH','UNP','UPS','UTX','V','VZ','WBA','WFC','WMT','XOM']
return eqtLst
def select_eps_histIEX(ticker='UNP', types='eps', nrow=1):
""" select eps/revenue/profitMaring from PSQL tables
"""
if types == 'eps':
xqTmp = "SELECT * from iex_earnings_hist where ticker='{}' order by pbdate DESC limit {}"
elif types == 'stats':
xqTmp = "SELECT * from iex_stats_hist where ticker='{}' order by pbdate DESC limit {}"
else:
xqTmp = "SELECT * from iex_financials_hist where ticker='{}' and freq='Q' order by pbdate DESC limit {}"
xqr = xqTmp.format(ticker, nrow)
df = sqlQuery(xqr)
if len(df) < 1:
return {}
return df
def tk2info(ticker='',
tablename='mapping_ticker_cik',
colx='ticker',
pgDB=None):
if ticker is None or len(ticker) < 1:
return {}
elif isinstance(ticker, list):
ticker = ticker[0]
xqTmp = """SELECT * FROM "{tablename}" WHERE {colx}='{ticker}'"""
xqr = xqTmp.format(tablename=tablename, ticker=ticker, colx=colx)
sys.stderr.write(" --find {} from SQL:\n{}\n".format(ticker, xqr))
df = sqlQuery(xqr, engine=pgDB)
if len(df) > 0 and isinstance(df, pd.DataFrame):
return df
else:
return []
def loop_hourly_mkt(tkLst=[],
lang='cn',
dbname='ara',
cutoff_hm='1600',
rpt_date='20190731',
region='us',
start=20190731,
archiveTest=False,
dirname='templates/',
outdir='US/mp3_hourly',
**optx):
''' run a list of tkLst
'''
region = region.lower()
dbname = '{}{}'.format(dbname, '.' + region if region == 'tw' else '')
pgDB = conn2pgdb(dbname=dbname)
if not tkLst and region == 'tw':
tkLst = get_stocks_tw(pgDB)
elif not tkLst:
tkLst = get_stocks_us(pgDB)
xqTmp = "select ticker,company{} as label from mapping_ticker_cik where ticker in ('{}')"
xqr = xqTmp.format('_cn' if lang == 'cn' else '', "','".join(tkLst))
tkLbLst = sqlQuery(xqr, pgDB).to_dict(orient='records')
for v in tkLbLst:
try:
ticker, title = v['ticker'], v['label']
run_hourly_mkt(ticker,
title,
start=start,
pgDB=pgDB,
target_hm=cutoff_hm,
region=region,
dirname=dirname,
outdir=outdir,
archiveTest=archiveTest,
rpt_date=rpt_date)
except Exception as e:
sys.stderr.write("**ERROR:{ticker}:{label}:{err}\n".format(
err=str(e), **v))
continue
#from mongo2psql import mongo2psql
#mongo2psql('hourly_report',dbname,engine=pgDB)
fp = open(outdir + '/hourly_mp3_uptodate.txt', 'w')
fp.write("{}_{}\n".format(start, cutoff_hm))
fp.close()
return 0
def ticker_peers(ticker,xqTmp='',debugTF=True,j=0):
'''
USE
SELECT a.*,b."marketCap" as marketCap, b."trailingPE" as peRatio FROM (select ticker,sector,industry from "yh_summaryProfile" where industry in (select industry from "yh_summaryProfile" where ticker='{}')) as a, yh_quote_curr b WHERE a.ticker=b.ticker ORDER BY marketCap DESC
'''
if len(xqTmp)<1:
xqLst=(
"""SELECT a.*,b."marketCap" as marketCap, b."trailingPE" as peRatio FROM (select ticker,sector,industry from "yh_summaryProfile" where industry in (select industry from "yh_summaryProfile" where ticker='{}')) as a, yh_quote_curr b WHERE a.ticker=b.ticker ORDER BY marketCap DESC""",
"""SELECT a.*,b."marketCap" as marketCap, b."trailingPE" as peRatio FROM (select ticker,sector,industry from "yh_summaryProfile" where sector in (select sector from "yh_summaryProfile" where ticker='{}')) as a, yh_quote_curr b WHERE a.ticker=b.ticker ORDER BY marketCap DESC""",
"""SELECT a.*,b.\"marketCap\" as marketCap, b.\"trailingPE\" as peRatio FROM (SELECT etfname as ticker,sector_alias as sector,NULL as industry FROM spdr_sector WHERE sector_alias in (select sector from "yh_summaryProfile" where ticker='{}' )) as a, yh_quote_curr b WHERE a.ticker=b.ticker ORDER BY marketCap DESC""")
xqTmp = xqLst[j]
xqr=xqTmp.format(ticker)
if debugTF is True:
sys.stderr.write(xqr+"\n")
ret = sqlQuery(xqr)
if len(ret)<5 and j<len(xqLst)-1:
ret=ticker_peers(ticker,debugTF=debugTF,j=j+1)
return ret
def eten_hist(ticker=None,
gap='1m',
ranged='1d',
hdrTF=True,
tsTF=True,
debugTF=False):
if gap == '1d':
from _alan_calc import sqlQuery
xqr = "SELECT * FROM prc_hist WHERE name='{}' AND pbdate>20171101".format(
ticker)
return sqlQuery(xqr, dbname='ara.tw')
twtime = datetime.now(pytz.timezone('Asia/Taipei'))
if twtime.hour < 9:
pbdate = (twtime - timedelta(days=1)).strftime('%Y%m%d')
else:
pbdate = (twtime).strftime('%Y%m%d')
return eten_minute(ticker=ticker,
pbdate=pbdate,
hdrTF=hdrTF,
tsTF=tsTF,
debugTF=debugTF)
def rerun_dss(ticker=''):
'''
rerun daily_single_stock via forking subprocess
'''
data=''
if len(ticker)<1:
return json.dumps(dict(err="no ticker assigned",err_code=400))
tkLst=sqlQuery("SELECT ticker from mapping_ticker_cik where ticker='{}'".format(ticker))
if len(tkLst)<1:
return json.dumps(dict(err="ticker not available",err_code=402))
try:
xcmd="cd /apps/fafa/pyx/alan/; python3 _alan_mp4.py daily_single_stock {} 1".format(ticker)
sys.stderr.write("==RERUN: {}\n".format(xcmd))
myproc = subprocess.Popen(xcmd,shell=True)
if myproc.poll() != None:
sys.stderr.write("**WARNING: {} not running\n".format(xcmd))
else:
sys.stderr.write("=={}: {} still running\n".format(ticker,xcmd))
return json.dumps(dict(err="Analysis will be available shortly.",err_code=304))
except Exception as e:
sys.stderr.write("**WARNING: {} on {}\n".format(str(e),xcmd))
return json.dumps(dict(err=str(e),err_code=403))
def yh_quote_curr(tkLst=None,
screenerTF=False,
dbname='ara',
tablename='yh_quote_curr',
zpk={'ticker'},
m2pTF=False,
webTF=None,
**optx):
'''
create yh_quote_curr table
'''
if tkLst is None or len(tkLst) < 1:
from _alan_calc import sqlQuery
tkDF = sqlQuery(
'select ticker from mapping_ticker_cik where act_code=1')
tkLst = list(tkDF['ticker'])
try:
jobj = dict(ticker=tkLst[0])
if webTF is None:
webTF = useWeb(jobj=jobj,
dbname=dbname,
tablename=tablename,
**optx)
if not webTF:
jobj = {'ticker': {'$in': tkLst}}
d, _, _ = find_mdb(jobj,
dbname=dbname,
tablename=tablename,
dfTF=True)
sys.stderr.write("===Using data from {}::{}\n".format(
dbname, tablename))
return d
d = yh_quote_comparison(tkLst,
screenerTF=screenerTF,
dbname=dbname,
tablename=tablename,
zpk=zpk,
**optx)
except Exception as e:
sys.stderr.write("**ERROR: {}:{}:{}\n".format("yh_quote_curr()",
"@ yh_quote_comparison",
str(e)))
d = []
saveDB = optx.pop('saveDB', False)
if not saveDB:
return d
try:
if m2pTF:
from mongo2psql import mongo2psql
mongo2psql(tablename, dbname)
sys.stderr.write(
"===Running mongo2psql()...{dbname}::{tablename}".format(
**locals()))
except Exception as e:
sys.stderr.write("**ERROR: {}:{}:{}\n".format("yh_quote_curr()",
"mongo2psql ...",
str(e)))
# Save yh_quote_curr To yh_quote_hist
try:
dbM = conn2mgdb(dbname=dbname)
tablename = tablename.replace('curr', 'hist')
ret = dbM[tablename].insert_many(d.to_dict(orient='records'),
ordered=False)
sys.stderr.write(
"===Saving history: {dbname}::{tablename}".format(**locals()))
except Exception as e:
sys.stderr.write("**ERROR: {}:{}:{}\n".format("yh_quote_curr()",
"MDB saving...", str(e)))
return d
def titlehead_backtest(opts={}, **optx):
import pandas as pd
from _alan_calc import sqlQuery, pull_stock_data as psd
from _alan_str import jj_fmt
dirname, lang, mp3YN = getKeyVal(optx, ['dirname', 'lang', 'mp3YN'],
['templates', 'cn', False])
nlookback = getKeyVal(optx, 'nlookback', 1)
days = getKeyVal(optx, 'days', 3700)
searchDB = getKeyVal(optx, 'searchDB', True)
debugTF = getKeyVal(optx, 'debugTF', False)
dbname = optx.pop('dbname', 'ara')
tablename = optx.pop('tablename', None)
dLst = optx.pop('args', None)
if dLst is None or len(dLst) < 1:
dLst = sqlQuery(
"SELECT * FROM mapping_series_label WHERE freq='D' and category_label_seq>0"
)
else:
dLst = sqlQuery(
"SELECT * FROM mapping_series_label WHERE freq='D' and category_label_seq>0 and series in ('{}')"
.format("','".join(dLst)))
ds = []
for lkbx in range(nlookback):
dm = []
for jx in range(len(dLst)):
ticker, freq, src, label_cn, category_cn = dLst[[
'series', 'freq', 'source', 'label_cn', 'category_cn'
]].iloc[jx]
if freq != 'D':
continue
df = psd(ticker,
days=days,
src=src,
debugTF=debugTF,
pchgTF=True,
searchDB=searchDB)
try:
dx = df.iloc[:-lkbx] if lkbx > 0 else df
ret = recordHiLo2(dx, ticker=ticker)
if len(ret['YTD']) > 0:
dd = dLst.iloc[jx].to_dict()
dd.update(ret['YTD'])
cmt = jj_fmt('headtitle3_cn.j2',
lang=lang,
mp3YN=False,
dirname=dirname,
ctrlSTRUCT='include',
**dd)
mp3cmt = jj_fmt('headtitle3_cn.j2',
lang=lang,
mp3YN=True,
dirname=dirname,
ctrlSTRUCT='include',
**dd)
dd.pop('_id', None)
dd['ticker'] = ticker
dd['pbdate'] = dd['endDT']
dd['comment'] = cmt
dd['mp3comment'] = mp3cmt
emsg = "{}\n".format((jx, category_cn, freq, ticker, src,
label_cn, dd['fqWd'], dd['endDT']))
sys.stderr.write(emsg)
dm.append(dd)
if debugTF:
sys.stderr.write("RUNNING {}:{}:{}\n{}\n".format(
lkbx, jx, ticker, ret))
except Exception as e:
sys.stderr.write("**ERROR:{}:{}:{}\n".format(
jx, ticker, str(e)))
continue
if len(dm) < 1:
continue
dm = pd.DataFrame(dm)
ds = dm.sort_values(by=['days', 'category_seq', 'category_label_seq'],
ascending=[False, True, True])
if tablename is not None:
zpk = {"ticker", "pbdate"}
sys.stderr.write("Save to {}:\n{}\n".format(tablename, dm))
mobj, clientM, msg = upsert_mdb(dm,
tablename=tablename,
dbname=dbname,
zpk=zpk)
return ds
def get_tkLst(tkLst=[], xqr="select etfname from spdr_sector", pgDB=None):
xLst = [str(x) for x in sqlQuery(xqr, pgDB).iloc[:, 0].values]
if len(tkLst) < 1:
return xLst
else:
return np.append(tkLst, xLst)
def run_api(jobj, engine=None):
pd.options.display.float_format = '{:,.2f}'.format
sty = "<style>.dataframe {text-align:right;}</style>\n"
ret = """Usage of:
<H3>
?topic='TOPIC'
</H3>
Where
<PRE>
TOPIC = [ipo|theme|priceRange|top10|utdEarningsList|mongo_search|file|write2file|test] """
topic = getKeyVal(jobj, 'topic', '').lower()
if topic is None:
return ret
if topic == 'theme':
subtopic = getKeyVal(jobj, 'subtopic', 'majorplayer')
if topic == 'theme' and subtopic in ['majorplayer', 'media', 'ipo']:
jobj.update(subtopic=subtopic)
return run_topic_theme(dfTF=False, **jobj)
elif topic == 'pricerange':
xqTmp = '''select * from (select ticker,close as price,"trailingPE" as "peRatio","marketCap"::float/1000000 as "marketCapMM","changePercent" as "change%%",change from yh_quote_curr where close>={} and close<={}) as a, (select ticker,company_cn,company,sector_cn from mapping_ticker_cik where act_code>0) as b where a.ticker=b.ticker order by price'''
try:
vr = jobj['range'].split(",") if 'range' in jobj else [60, 70]
if len(vr) >= 2:
vr = np.array(vr, dtype=float)
lb, ub = (vr.min(), vr.max())
xqr = xqTmp.format(lb, ub)
df = sqlQuery(xqr)
pd.options.display.float_format = '{:,.2f}'.format
cfm = {'marketCapMM': "{:,.0f}".format}
ret = df.to_html(formatters=cfm)
return sty + ret
except Exception as e:
pqint(str(e))
return str(e)
elif topic == 'top10':
return run_top10(jobj, engine=None)
elif topic in ['utd_earnings_list', 'utdearningslist']:
from utd_earnings_list import utd_earnings_list
dd = dict(sector='Technology', pbdate='20190101')
dd.update(subDict(jobj, ['sector', 'pbdate']))
df = utd_earnings_list(**dd).sort_values(by=['pbdate', 'marketCap'],
ascending=[False, False])
df.rename(columns={'quarter': 'epochs'}, inplace=True)
cfm = {
'marketCap': "{:,.0f}".format,
'recdate': "{:.0f}".format,
'epochs': "{:.0f}".format
}
ret = df.to_html(formatters=cfm)
return sty + ret
elif topic in ['daily_med', 'dailymed']:
from dailyMed_api import drug2labelInfo as dli
try:
dd = dli(jobj['drug_name'])
except Exception as e:
sys.stderr.write("**ERROR:{}\n".format(str(e)))
return (str(e))
#return dd
ts = """{{drug_name}}
<P>
{{ sec_cn|join('</P><P>\n') }}
</P>
"""
return jj_fmt(ts, **dd)
elif topic == 'mongo_search':
d = dict(dbname='ara',
tablename='lsi2nlg',
username='******',
field='tmplname',
ticker='AAPL')
if len(jobj) > 0:
d.update(jobj)
findDct = {'username': d.get('username')}
fieldLst = d.get('field').split(',')
fieldDct = {x: 1 for x in fieldLst}
xg, _, _ = find_mdb(dbname=d['dbname'],
tablename=d['tablename'],
jobj=findDct,
field=fieldDct)
return xg
elif topic == 'file':
try:
dirname = jobj[
'dirname'] if 'dirname' in jobj else "/apps/fafa/pyx/tst"
if 'j2name' in jobj:
fname = "{}/{}".format(dirname, jobj['j2name'])
ret = open(fname).read()
elif 'image' in jobj:
fname = "{}/{}".format(dirname, jobj['image'])
ret = open(fname).read()
except Exception as e:
sys.stderr.write("**ERROR:{}".format(str(e)))
elif topic == 'write2file':
# save 'j2name' to 'dirName'
pqint(jobj)
try:
dirName = "/apps/fafa/pyx/flask/rmc/templates"
if 'j2name' in jobj and 'j2ts' in jobj:
if 'dirname' in jobj:
dirName = jobj['dirname']
fname = "{}/{}".format(dirName, jobj['j2name'])
fp = open(fname, 'w+')
fp.write(jobj['j2ts'])
fp.close()
pqint("===Save {} To {}".format(jobj['j2ts'], fname))
ret = "Successfully save to {}".format(fname)
except Exception as e:
sys.stderr.write("**ERROR:{}".format(str(e)))
ret = str(e)
elif topic == 'test':
if 'tmplrpt' in jobj:
ret = jobj['tmplrpt']
return ret
def daily_briefing(start=None,
region='US',
dirname='templates/',
outdir="US/mp3_hourly/",
dbname='ara',
saveDB=True,
**optx):
from headline_writer import generate_headline
if 'cdt' not in optx:
cdt = datetime.datetime.now()
else:
cdt = optx['cdt']
if isinstance(cdt, str):
cdt = pd.Timestamp(cdt)
if start is None:
start = cdt.strftime('%Y%m%d')
sys.stderr(" --cdt:{}, start:{}\n".format(cdt, start))
opts = {
'lang': 'cn',
'dirname': 'templates',
'end': None,
'nlookback': 1,
'args': [],
'sep': '|',
'debugTF': False,
'hostname': 'localhost',
'tablename': None,
'days': 730,
'saveDB': True,
'extraJS': None,
'j2ts': '{% include "daily_briefing_cn.j2" %}',
'onTheFly': True,
'output': None,
'narg': 0,
'filename': None,
'extraQS': None,
'dbname': 'ara',
'mp3YN': False
}
del optx['args']
hm = int(cdt.strftime("%H00")) # NOTE: sensative to crontab timing issue
hm = get_cutoff_hm(hm=int(hm), region=region)
if hm < 1600:
start = sqlQuery(
"select pbdate from prc_hist where name='AAPL' ORDER BY pbdate DESC limit 1"
).iloc[0].values[0]
start = int(start)
category = 'SoD'
else:
category = 'EoD'
if not os.path.exists(outdir):
outdir = './'
#ret=generate_headline(opts,start=start,outdir=outdir,category=category,rpt_time=cdt,**optx)
ret = jj_fmt(opts['j2ts'],
dirname=dirname,
start=start,
outdir=outdir,
rpt_time=cdt,
category=category)
if 'tablename' not in locals() or tablename is None:
tablename = 'mkt_briefing'
title = '{}_briefing'.format(category)
dd = dict(comment=ret,
pbdt=cdt,
title=title,
hhmm=hm,
category=category,
rpt_time=cdt)
if saveDB:
clientM = None
mobj, clientM, _ = write2mdb(dd,
clientM,
dbname=dbname,
tablename=tablename,
zpk=['hhmm', 'category'])
tablename = tablename + '_hist'
mobj, clientM, _ = write2mdb(dd,
clientM,
dbname=dbname,
tablename=tablename,
zpk=['pbdt', 'category'])
return ret
def merge_ticker_cik(tkLst=[],colStr='ticker,company_cn',dbname='ara',tablename='mapping_ticker_cik'):
xqTmp="""Select {colStr} from "{tablename}" where ticker in ('{tkQStr}')"""
tkQStr="','".join(tkLst)
xqr = xqTmp.format(**locals())
df = sqlQuery(xqr,dbname=dbname)
return df
def upd_mapping_ticker(lkbk=-7,
mxcap=10**9,
debugTF=False,
saveDB=True,
**optx):
sys.stderr.write("===STARTING to update ticker list\n")
#1. UPDATE sp500 / Dow30 list
from theme_list import sp500_component
sp500 = sp500_component(saveDB=False)
if len(sp500) < 1:
sp500 = sqlQuery("SELECT * FROM sp500_component")
sLst = sp500['ticker'].values
#2. PULL MDB::ara:madmoney_hist
dt6mo = int(next_date(months=-6, dformat='%Y%m01', dtTF=False))
mdb = conn2mgdb(dbname='ara')
mLst = mdb['madmoney_hist'].find({
"pbdate": {
"$gt": dt6mo
}
}).distinct("ticker")
mLst = [x.replace('.', '-') for x in mLst]
#3. COMPILE a new list based on madmoney and SP500 new tickers
tkCIK = sqlQuery(
"SELECT * FROM mapping_ticker_cik WHERE act_code=1 and ticker not similar to '%%(^|=)%%'"
)
tLst = tkCIK['ticker'].values
n1Lst = set(sLst) - set(tLst)
sys.stderr.write("New SP500 List:{}\n".format(n1Lst))
n2Lst = set(mLst) - set(tLst)
sys.stderr.write("New madMoney List:{}\n".format(n2Lst))
nLst = list(n1Lst.union(n2Lst))
#4. PULL only valid quotes within last 7 days and marketCap > 1B
dt7d = int(next_date(days=lkbk, dformat='%Y%m%d', dtTF=False))
df = yqc(nLst, tablename=None, screenerTF=False, dfTF=True)
xqr = 'not(pbdate<{}|marketCap<{})'.format(dt7d, mxcap)
dg = df.query(xqr)
newLst = dg['ticker'].values
if len(newLst) < 1:
sys.stderr.write("No Additions\n")
return (0)
else:
sys.stderr.write("New Additional List:{}\n".format(newLst))
#5. PULL new list 'newLst' with cik/sic/sector info
if saveDB:
newDf = tkmp(newLst)
pgDB = conn2pgdb(dbname='ara')
newDf.to_sql('ticker_mapping_temp',
pgDB,
schema='public',
index=False,
if_exists='replace')
xqr = """
DELETE FROM mapping_ticker_cik B USING ticker_mapping_temp C WHERE B.ticker = C.ticker;
INSERT INTO mapping_ticker_cik
SELECT cik,ticker,company,sic,sector,company_cn,sector_cn,1::int as act_code FROM (SELECT a.*,b.sector,b.sector_cn from ticker_mapping_temp a LEFT JOIN spdr_sector b ON a.sector_alias=b.sector_alias) as s
"""
pgDB.execute(xqr, pgDB)
return newLst
return xLst
else:
return np.append(tkLst, xLst)
start = int(sys.argv[1]) if len(sys.argv) > 1 else int(
datetime.datetime.now().strftime('%Y%m%d'))
lang = sys.argv[2] if len(sys.argv) > 2 else 'cn'
pgDB = conn2pgdb(dbname='ara.tw')
#tkLst = ['1101','1102','1216','1301','1303','1326','1402','2002','2105','2301','2303','2308','2317','2327','2330','2354','2357','2382','2395','2408','2409','2412','2454','2474','2492','2633','2801','2823','2880','2881','2882','2883','2884','2885','2886','2887','2890','2891','2892','2912','3008','3045','3481','3711','4904','4938','5871','5880','6505','9904']
tkLst = [
'000', '001', '050', '1101', '1102', '1216', '1301', '1303', '1326',
'1402', '2002', '2105', '2301', '2303', '2308', '2317', '2327', '2330',
'2354', '2357', '2382', '2395', '2408', '2409', '2412', '2454', '2474',
'2492', '2633', '2801', '2823', '2880', '2881', '2882', '2883', '2884',
'2885', '2886', '2887', '2890', '2891', '2892', '2912', '3008', '3045',
'3481', '3711', '4904', '4938', '5871', '5880', '6505', '9904'
]
xqr = "select ticker,company_cn as label from mapping_ticker_cik where ticker in ('{}')".format(
"','".join(tkLst))
tkLbLst = sqlQuery(xqr, pgDB).to_dict(orient='records')
print xqr, tkLbLst
for v in tkLbLst:
ticker, title = v['ticker'], v['label']
d = dict(start=start, title=title, ticker=ticker, lang=lang)
xcmd = "python hourly_mkt.py {ticker} --start={start} --extra_xs='archiveTest=True;dirname=\"TW/mp3\"' --title='{title}' --lang={lang} --src=tw".format(
**d)
print >> sys.stderr, xcmd
sysCall(xcmd)
import sys
import pandas as pd
from pprint import pprint
from _alan_calc import sqlQuery, conn2pgdb, upd_temp2hist
def printerr(s, file=sys.stderr, end='\n'):
file.write(s + end)
dbname = 'ara'
hostname = 'localhost'
pgDB = conn2pgdb(dbname=dbname, hostname=hostname)
xqr = """select * from (select a.pbdate,a.name,a.close,b.close,(a.close/b.close-1)*100. as pchg from prc_hist_iex a,prc_temp_iex b, (select name,min(pbdate) mndate from prc_temp_iex group by name) as c where a.pbdate=c.mndate and b.pbdate=c.mndate and a.pbdate=b.pbdate and a.name=b.name and a.name=c.name ) as x where abs(pchg)>0.5 ORDER BY abs(pchg)
"""
scLst = sqlQuery(xqr, engine=pgDB)
if len(scLst) > 0:
tb_temp = 'temp_list'
scLst.to_sql(tb_temp,
pgDB,
index=False,
schema='public',
if_exists='replace')
xlst = "('{}')".format("','".join(scLst['name']))
#fp=open('stock_list_upd.tmp','w')
#fp.write(scLst.to_csv(index=False,sep='|'))
#fp.close()
printerr(xlst)
# delete entire hist if temp in the earliest is not consistent
xqr = """delete from prc_hist_iex where name in {}""".format(xlst)
def bb_predefined(scrIds='most_actives',dbname='ara',saveDB=True,mappingTF=True,mmGap=30,addiFilter=1,debugTF=False,**optx):
'''
return a ticker list based on the combo of
yahoo 'scrIds' list and BB internal tracking tickers
where
mappingTF: apply list on in the [mapping_ticker_cik] table
addiFilter: adds additional query
1 for 'abs(changePercent)>2.5&price>7.99'
2 for 'abs(changePercent)>5&price>7.99'
3 for 'abs(changePercent)>2.5&price>4.99'
OR a string such as 'abs(changePercent)>2.5&price>4.99'
default for None
also see
from yh_chart import yh_quote_comparison; help(yh_quote_comparison)
Note,
Info are pulled from DB 1st if last update was with in 'mmGap' minutes
If live data are not available, existed DB info will be used
'''
#from yh_predefined import yh_predefined_query
tablename = "yh_{}".format(scrIds)
cdt=s2dt()
try:
df,_,_ = find_mdb(dbname=dbname,tablename=tablename,dfTF=True)
if len(df)>0 and 'pbdt' in df:
t1=df['pbdt'].iloc[0]
mmPassed=pd.Timedelta(cdt - t1).total_seconds() / 60.0
if mmPassed>mmGap: # check if DB info is winthin last 'mmGap' (30-minute)
df=[]
else:
df=[]
if len(df)>0:
if debugTF:
sys.stderr.write("===Use {} from MDB:{}\n".format(scrIds,tablename))
return df
df=yh_predefined_query(scrIds,dfTF=True)
if len(df)<1: # using whatever in the DB if live pulling failed
df,_,_ = find_mdb(dbname=dbname,tablename=tablename,dfTF=True)
return df
df['pbdt'] = cdt
if mappingTF:
secInfo=sqlQuery('select * from mapping_ticker_cik where act_code=1')
df = df.loc[df['ticker'].isin(secInfo['ticker'])]
if addiFilter:
if addiFilter==1:
addiFilter='abs(changePercent)>0.5&price>7.99'
elif addiFilter==2:
addiFilter='abs(changePercent)>5&price>7.99'
elif addiFilter==3:
addiFilter='abs(changePercent)>2.5&price>4.99'
if debugTF:
sys.stderr.write("==addiFilter:{}\n".format(addiFilter))
df = df.query(addiFilter)
#df['pbdt'] = cdt
df = df.merge(secInfo[['ticker','company_cn','sector','sector_cn']],on='ticker')
df.reset_index(drop=True,inplace=True)
if debugTF:
sys.stderr.write("{}\n".format(df))
if saveDB:
clientM=None
mobj, clientM, _ = write2mdb(df,clientM,dbname=dbname,tablename=tablename,zpk={'*'})
sys.stderr.write("Data saved to {}::{}".format(clientM,tablename))
except Exception as e:
sys.stderr.write("**ERROR: bb_predefined(): {}\n".format(str(e)))
df=[]
return df.iloc[:6]
def ytdRtn_calc(start=20200219,
group='sector',
loDate=20200323,
dbname='ara',
**optx):
sys.stderr.write(" --INPUTS:{}\n".format(locals()))
groupLst = [group]
sqx = "SELECT name as ticker,close as ytdclose from prc_hist where pbdate={} ORDER BY ticker".format(
start)
dhi = sqlQuery(sqx)
sqx = "SELECT name as ticker,close as loclose from prc_hist where pbdate={} ORDER BY ticker".format(
loDate)
dlo = sqlQuery(sqx)
sys.stderr.write(" --sqx:{}".format(sqx))
#display(print("<style>div.output_scroll { height: 30em; }</style>"))
dytd = dhi.merge(dlo, on='ticker')
dytd['loRtn'] = dytd['loclose'] / dytd['ytdclose'] * 100 - 100
#print(dytd.tail().to_string(index=False),file=sys.stderr)
tkLst = list(dytd['ticker'].values)
jobj = {"ticker": {'$in': tkLst}}
dq, clientM, _ = find_mdb(jobj,
clientM=None,
dbname='ara',
tablename='yh_quote_curr',
dfTF=True)
#print(dq.head().to_string(index=False),file=sys.stderr)
pbdt = dq['pbdt'].max()
colX = ['ticker', 'close', 'marketCap', 'trailingPE', 'shortName', 'pbdt']
dh = dq[colX].merge(dytd, on='ticker')
#print(dh.tail().to_string(index=False),file=sys.stderr)
dh['ytdRtn'] = dh['close'] / dh['ytdclose'] * 100 - 100
#print(dh.head().to_string(index=False))
field = {'ticker', 'sector', 'industry'}
ds, clientM, _ = find_mdb(jobj,
clientM=None,
dbname='ara',
tablename='yh_summaryProfile',
field=field,
dfTF=True)
dh = dh.merge(ds, on='ticker')
#print(dh.head().to_string(index=False))
d500, clientM, _ = find_mdb(
jobj,
clientM=None,
dbname='ara',
field={"ticker", "GICS Sector", "GICS Sub Industry"},
tablename='sp500_component',
dfTF=True)
#print(d500.head().to_string(index=True))
find_mdb({"ticker": {
'$nin': tkLst
}},
clientM=None,
dbname='ara',
tablename='sp500_component',
dfTF=True)[0]
dh1 = dh.query('marketCap>3000000000')
#print(dh1.head().to_string(index=True))
dh1 = dh1[dh1[group] != '']
dss = dh1.groupby(groupLst).apply(
lambda x: pd.Series([
np.average(x.ytdRtn, weights=x.marketCap),
x.ytdRtn.mean(),
np.average(x.loRtn, weights=x.marketCap),
x.loRtn.mean(),
x.marketCap.sum(),
x.ytdRtn.count()
],
index=[
'ytdRtnWAvg', 'ytdRtnAvg', 'loRtnWAvg',
'loRtnAvg', 'marketCap', 'count'
]))
dss['recoverRtnWA'] = dss['ytdRtnWAvg'] - dss['loRtnWAvg']
# use index as first column
dss.reset_index(inplace=True)
# add additional columns
if group == 'industry':
colx = ['sector', 'industry']
dss = dss.merge(dh1[colx].drop_duplicates(colx),
on=['industry'],
how='left')
elif group == 'ticker':
colx = ['ticker', 'sector', 'industry', 'pbdt']
dss = dss.merge(dh1[colx], on=['ticker'], how='left')
if 'pbdt' not in dss:
dss['pbdt'] = pbdt
colD = dict(ytdRtnWAvg="WA Return% since_2/19",
ytdRtnAvg="Avg Return% since_2/19",
loRtnWAvg="WA Return% 3/23-2/19",
loRtnAvg="Avg Return% 3/23-2/19",
recoverRtnWA="Recovered Return% since_3/23")
dss = renameDict(dss, colD)
cfm = {'marketCap': "{:,.0f}".format, 'count': "{:.0f}".format}
#print(dss.to_string(index=True,formatters=cfm) ,file=sys.stderr)
#print(dss.to_string(index=True,formatters=cfm) ,file=sys.stderr)
return dss
