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 get_ts_cmt(sqx, ts_alt, pgDB=None, dbname=None):
try:
if pgDB is None:
pgDB = conn2pgdb(dbname=dbname)
ts_cmt = pd.read_sql(sqx, pgDB).iloc[0][0]
except Exception, e:
print >> sys.stderr, "** ERROR: get_ts_cmt(),{}", format(str(e))
ts_cmt = ts_alt
def mainTst():
nss = 4 # seasonality period
nfcs = 4 # quarterly forecast
pngname = None
saveDB = True
pgDb = conn2pgdb(dbname='ara')
sqx = "SELECT sector AS sector FROM spdr_sector WHERE sector NOT LIKE '%%ETF' ORDER BY sector"
#sqx = "SELECT sector_alias AS sector FROM spdr_sector WHERE sector NOT LIKE '%%ETF'"
secLst = [str(x) for x in pd.read_sql(sqx, pgDb).iloc[:, 0]]
secLst += ['ALL']
#secLst = secLst [6:7] # for special testing
dH = pd.DataFrame()
dFcs = pd.DataFrame()
dPrm = pd.DataFrame()
rmode = 'replace'
for j, sector in enumerate(secLst):
try:
dprm, dh, dfcs = sector_eps_price(sector,
nss=nss,
nfcs=nfcs,
pgDb=pgDb,
pngname=None)
""" get [dh] from database
sqx = "select * from fa_eps_price where ticker in (select ticker from iex_company_hist where sector={!r})".format(sector)
dH = pd.read_sql(sqx,pgDb)
dprm = wrap_regression(dH,npar=2,sector=sector,pngname="hello")
"""
print(j, sector, file=sys.stderr)
print(dprm, file=sys.stderr)
print(dfcs, file=sys.stderr)
print(dh, file=sys.stderr)
if saveDB is True:
dh.to_sql('fa_eps_price',
pgDb,
schema='public',
index=False,
if_exists=rmode)
dfcs.to_sql('fa_eps_fcs',
pgDb,
schema='public',
index=False,
if_exists=rmode)
dprm.to_sql('fa_eps_param',
pgDb,
schema='public',
index=False,
if_exists=rmode)
rmode = 'append'
#dH = pd.concat([dH,dh])
#dFcs = pd.concat([dFcs,dfcs])
#dPrm = pd.concat([dPrm,dprm])
except:
dprm, dh, dfcs = ([], [], [])
continue
#return dPrm, dH, dFcs
return dprm, dh, dfcs
def get_alert_list(dbname='ara.tw', numLimit=10):
fname = 'ohlc_alert_list.sql.tmp'
pgDB = conn2pgdb(dbname=dbname)
xqTmp = open(fname).read()
cdate = pd.read_sql("SELECT * FROM ara_uptodate", pgDB).pbdate[0]
x1dYmd = ymd_delta(cdate, 1)
x1wYmd = ymd_delta(cdate, 7)
xqr = xqTmp.format(**locals())
df = pd.read_sql(xqr, pgDB)
return (df, pgDB)
def get_ts_cmt(sqx, ts_alt, pgDB=None, dbname=None, debugTF=False):
try:
if pgDB is None:
pgDB = conn2pgdb(dbname=dbname)
ts_cmt = pd.read_sql(sqx, pgDB).iloc[0][0]
except Exception as e:
print("** ERROR: get_ts_cmt(),{}", format(str(e)), file=sys.stderr)
ts_cmt = ts_alt
if debugTF is True:
print("TS SQL:\n{}\nTS TEMPLATE:\n{}".format(sqx, str(ts_cmt)),
file=sys.stderr)
return ts_cmt
def get_alert_list(dbname='ara.tw', numLimit=10):
fname = 'ohlc_alert_list.sql.tmp'
pgDB = conn2pgdb(dbname=dbname)
xqTmp = open(fname).read()
cdate = pd.read_sql("SELECT * FROM ara_uptodate", pgDB).pbdate[0]
x1dYmd = pd.read_sql(
"SELECT pbdate FROM ohlc_daily_comment_cn GROUP BY pbdate ORDER BY pbdate DESC limit 2",
pgDB).iloc[1][0]
#x1dYmd=ymd_delta(cdate,1)
x1wYmd = ymd_delta(cdate, 7)
xqr = xqTmp.format(**locals())
df = pd.read_sql(xqr, pgDB)
print >> sys.stderr, df
return (df, pgDB)
def get_macro_fcs(tkLst=[], pgDB=None, dbname='ara', lang="en"):
ext = "" if lang == "en" else "_" + lang
pgDB = conn2pgdb(dbname=dbname)
tkStr = "('{}')".format("','".join(tkLst))
sqr = """SELECT p.*,m.freq as mfreq,m.label{} as label, 'Market Index' as sector, m.category,m.category_seq FROM
(SELECT * FROM macro_fcs where ticker in {} ) as p
LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq""".format(
ext, tkStr)
tmpx = pd.read_sql(sqr, con=pgDB)
datax = tmpx.query(
"(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy()
datax['zx'] = (datax.prc_chg / datax.rrate_sigma)
ds = datax.query("freq=='W'")
dm = [ds.query("ticker=={!r}".format(x)).iloc[0].to_dict() for x in tkLst]
return (dm, datax, pgDB)
def get_ohlc_fcs(tkLst=[], pgDB=None, dbname='ara', lang="en"):
if pgDB is None:
pgDB = conn2pgdb(dbname=dbname)
xqr = "SELECT ticker FROM ara_ranking_list WHERE category='AI' AND subtype='Index' ORDER BY ranking limit 1"
tkLst = pd.read_sql(xqr, pgDB).iloc[0].values
ext = "" if lang == "en" else "_" + lang
tkStr = "('{}')".format("','".join(tkLst))
sqr = """SELECT p.*,'D' as mfreq, m.company{} as label ,m.sector ,'stock' as category, 5::int as category_seq FROM
(SELECT * FROM ohlc_fcs where ticker in {} ) as p
LEFT JOIN mapping_ticker_cik m ON p.ticker=m.ticker""".format(ext, tkStr)
datax = pd.read_sql(sqr, con=pgDB)
print >> sys.stderr, sqr, dbname
datax['zx'] = (datax.prc_chg / datax.rrate_sigma)[:]
ds = datax.query("freq=='W'")
dm = ds.to_dict(orient='record')
return (dm, datax, pgDB)
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 get_macro_list(currdate=None,
pgDB=None,
dbname='ara',
lang="en",
debugTF=False):
""" 2. get globalmacro index
return dx as a list of largest movement of each category
"""
if pgDB is None:
pgDB = conn2pgdb(dbname=dbname)
if currdate is None:
try:
currdate = int(
pd.read_sql(
"SELECT pbdate FROM macro_fcs where ticker='^GSPC'",
pgDB).iloc[0, 0])
except Exception as e:
print("**ERROR: {}{}".format(
str(e), "@ get_macro_list(): [currdate] not defined!"),
file=sys.stderr)
return ([], [], pgDB)
ext = "" if lang == "en" else "_" + lang
sqr = "SELECT p.*,m.freq as mfreq,m.label{0} as label, m.category,m.category_seq FROM macro_fcs p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq".format(
ext)
tmpx = pd.read_sql(sqr, con=pgDB)
datax = tmpx.query(
"(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy()
datax['zx'] = (datax.prc_chg / datax.rrate_sigma)
if debugTF is True:
print("macro_list SQL:\n{}\n".format(sqr), file=sys.stderr)
print("macro_list DATA:\n{}".format(datax.to_csv(index=False,
sep='|')),
file=sys.stderr)
dx = []
for j in (datax.category_seq.unique()):
dy = macro_list_x(j,
datax,
currdate=currdate,
pgDB=pgDB,
debugTF=debugTF)
if dy is None or len(dy) < 1:
continue
dx.append(dy)
return (dx, datax, pgDB)
def get_mkt_list(dbname='ara', lang="en", currdate=None):
ext = "" if lang == "en" else "_" + lang
pgDB = conn2pgdb(dbname=dbname)
sqr = "SELECT p.*,m.freq as mfreq,m.label{0} as label, m.category,m.category_seq FROM macro_fcs p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq".format(
ext)
tmpx = pd.read_sql(sqr, con=pgDB)
datax = tmpx.query(
"(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy()
datax['zx'] = (datax.prc_chg / datax.rrate_sigma)
print >> sys.stderr, datax.head(2)
print >> sys.stderr, datax.tail(3)
dx = []
for j in (datax.category_seq.unique()):
#if j>3: continue
dy = mkt_list_x(j, datax, currdate=currdate, pgDB=pgDB)
if dy is None or len(dy) < 1:
continue
dx.append(dy)
#print >> sys.stderr, dx
return (dx, datax, pgDB)
def get_macro_fcs(tkLst=None,
pgDB=None,
dbname='ara',
lang="en",
debugTF=False):
""" 1a, get selected market indices from [macro_hist_fred] table index section
"""
if pgDB is None:
pgDB = conn2pgdb(dbname=dbname)
if tkLst is None:
tkLst = ["^DJI", "^SOX", "^IXIC", "^GSPC"]
ext = "" if lang == "en" else "_" + lang
tkStr = "('{}')".format("','".join(tkLst))
xqTmp = """SELECT p.*,m.freq as mfreq,m.label{} as label, 'Market Index' as sector, m.category,m.category_seq FROM
(SELECT * FROM macro_fcs where ticker in {} ) as p
LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq"""
sqr = xqTmp.format(ext, tkStr)
tmpx = pd.read_sql(sqr, con=pgDB)
datax = tmpx.query(
"(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy()
datax['zx'] = (datax.prc_chg / datax.rrate_sigma)
ds = datax.query("freq=='W'")
dm = [ds.query("ticker=={!r}".format(x)).iloc[0].to_dict() for x in tkLst]
return (dm, datax, pgDB)
reload(sys)
sys.setdefaultencoding('utf8')
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)
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:
def batch_daily_comment(tkLst=None, **kwargs):
for ky, va in kwargs.items():
exec("{}=va".format(ky))
pgDB = conn2pgdb(dbname=dbname, hostname=hostname)
mgDB = conn2mgdb(dbname=dbname.replace('.', '_'), hostname=hostname)
#====OHLC Comment===========================
pqint("====OHLC Comment===========================", file=sys.stderr)
if tablename is None:
tablename = "{}_{}".format("ohlc_daily_comment", lang)
if mp3YN is True:
tablename = "{}_{}".format(tablename, "mp3")
else:
tablename = tablename
# Initial assignments
(tempS, daily_hdr) = assign_ts_ohlc(lang=lang)
if filename is None:
fp = None
elif filename == "stdout" or tablename == "-":
fp = sys.stdout
elif filename == "stderr":
fp = sys.stderr
else:
fp = open(filename, "w")
# Get batch list
sqr = "SELECT s.*,m.sector,m.company{} as company FROM mapping_ticker_cik m right join (select ticker,min(pbdate) as mndt,max(pbdate) as mxdt from ohlc_pnl group by ticker) as s ON m.ticker=s.ticker order by s.ticker".format(
"" if lang == "en" else "_" + lang)
dataM = pd.read_sql(sqr, pgDB)
#dataM=pd.read_sql(sqr,pgDB)[9:10]
if tkLst is not None:
dx = dataM[dataM['ticker'].isin(tkLst)]
if dx.empty is True:
pqint("{} not found!".format(tkLst), file=sys.stderr)
return None
else:
dataM = dx.reset_index(drop=True)
pqint(
"START batch_daily_comment-------------------------------------------",
file=sys.stderr)
for j, tRw in dataM.iterrows():
(tkX, mndt, mxdt, sector, label) = tRw.values
iteritem_daily_comment(j,
tempS,
tkX,
mndt,
mxdt,
sector,
label,
daily_hdr,
tablename,
pgDB,
mgDB,
fp,
wmode=wmode,
lang=lang,
saveDB=saveDB,
mp3YN=mp3YN,
dirname=dirname,
debugTF=debugTF)
wmode = 'append'
#fp.close()
pqint("END batch_daily_comment-------------------------------------------",
file=sys.stderr)
print(kwargs.keys())
print(kwargs.values())
def headline_hist(ndays=2,saveDB=False,end=None,outTF=False,**optx):
eqtLst = get_eqtLst()
pgDB=conn2pgdb(dbname='ara')
# get indices chgPct: idxRtn
idxLst = ['^GSPC','^DJI','^IXIC','^SOX']
idxRtnHist = get_stock_hist(idxLst,ndays=ndays,diffTF=True,pgDB=pgDB,end=end)
if len(idxRtnHist)<1:
return []
# get equity chgPct
eqtRtnHist = get_stock_hist(eqtLst,ndays=ndays,pgDB=pgDB,end=end)
if len(eqtRtnHist)<1:
return []
# get equity chgPct
pbLst = idxRtnHist.loc[idxRtnHist['ticker']==idxLst[0],'pbdate'].sort_values(ascending=False)[:]
# redirect stdout
old_stdout = sys.stdout
mystdout = StringIO()
sys.stdout = mystdout
fwrite=sys.stdout.write
fwrite("pbdate|Index1|chgPct1|chgLevel1|UpDn1|close1")
fwrite("|Index2|chgPct2|chgLevel2|UpDn2|close2")
fwrite("|Index3|chgPct3|chgLevel3|UpDn3|close3")
fwrite("|Index4|chgPct4|chgLevel4|UpDn4|close4")
print("|topDate1|topUD1|topLst1|topDate2|topUD2|topLst2|conflict")
for pbdate in pbLst:
idxRtn,idxUp,idxDn = find_top_changes(pbdate=pbdate,dx=idxRtnHist)
eqtRtn,eqtUp,eqtDn = find_top_changes(pbdate=pbdate,dx=eqtRtnHist)
#print(idxRtn,"\n",eqtUp,"\n",eqtDn)
if len(idxRtn)<1:
sys.stderr.write("skip date: {}\n".format(pbdate))
continue
sp500Ret = idxRtn.loc[idxRtn['ticker']=='^GSPC'].iloc[0].to_dict()
dowRet = idxRtn.loc[idxRtn['ticker']=='^DJI'].iloc[0].to_dict()
nasdaqRet = idxRtn.loc[idxRtn['ticker']=='^IXIC'].iloc[0].to_dict()
phlRet = idxRtn.loc[idxRtn['ticker']=='^SOX'].iloc[0].to_dict()
if np.isnan(sp500Ret['chgPct']):
sys.stderr.write("skip date: {}\n".format(pbdate))
continue
conflict = 0 if dowRet['sign'] == sp500Ret['sign'] else 1
eqtLx1 = eqtUp if sp500Ret['sign']==1 else eqtDn
if len(eqtLx1)<1:
topLst1=[]
eqtLead1 = {}
else:
dtmp=eqtLx1[['ticker','chgPct']].head(3).to_dict(orient='records')
topLst1=[{x['ticker']:round(x['chgPct'],2)} for x in dtmp]
#topLst1=["{!r}:{:.2f}".format(*x) for x in (eqtLx1[['ticker','chgPct']].head(3).values)]
eqtLead1 = eqtLx1.iloc[0].to_dict()
eqtLead1['topLst1']=topLst1
eqtLx2 = eqtUp if dowRet['sign']==1 else eqtDn
if len(eqtLx2)<1:
topLst2=[]
eqtLead2 = {}
else:
dtmp=eqtLx2[['ticker','chgPct']].head(3).to_dict(orient='records')
topLst2=[{x['ticker']:round(x['chgPct'],2)} for x in dtmp]
#topLst2=["{!r}:{:.2f}".format(*x) for x in (eqtLx2[['ticker','chgPct']].head(3).values)]
eqtLead2 = eqtLx2.iloc[0].to_dict()
eqtLead2['topLst2']=topLst2
fwrite("{pbdate}|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**sp500Ret))
fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**dowRet))
fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**nasdaqRet))
fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**phlRet))
if len(eqtLx1)>0:
fwrite("|{pbdate}|{UpDn}|{topLst1}".format(**eqtLead1))
if len(eqtLx2)>0:
fwrite("|{pbdate}|{UpDn}|{topLst2}".format(**eqtLead2))
fwrite("|{conflict}\n".format(conflict=conflict))
# redirect the stdout to string and convert it to dataframe
xstr = mystdout.getvalue()
sys.stdout = old_stdout
fwrite=sys.stdout.write
if outTF:
fwrite(xstr)
df = pd.read_csv(StringIO(xstr),sep='|')
from _alan_calc import save2pgdb
from _alan_str import write2mdb
if saveDB==True:
tablename="headline_hist"
sys.stderr.write("Save to {}\n".format(tablename))
#save2pgdb(df,db=pgDB,tablename=tablename)
clientM=None
zpk={"ticker","pbdate"}
mobj,clientM,msg = write2mdb(df,clientM,tablename=tablename,zpk=zpk)
if 'topDict' in optx and optx['topDict']==True:
if 'topLst1' in df:
df['topLst1']=[ ast.literal_eval(x) if hasattr(df['topLst1'], "__len__") else {} for x in df['topLst1'] ]
if 'topLst2' in df:
df['topLst2']=[ ast.literal_eval(x) if hasattr(df['topLst2'], "__len__") else {} for x in df['topLst2'] ]
df['chg1']=df['chgLevel1']
df['chg2']=df['chgLevel2']
df['allUpDn']=0
for j in range(df.shape[0]):
if all([ x>0 for x in df[['chgPct1','chgPct2','chgPct3']].iloc[j] ]) :
allUpDn = 1
elif all([ x<0 for x in df[['chgPct1','chgPct2','chgPct3']].iloc[j] ]) :
allUpDn = -1
else:
allUpDn = 0
df.loc[df.index[j],'allUpDn']=allUpDn
return df
md2['label']=optx['label']
print >> sys.stderr, "Additionals in run_narrative():{}".format(optx)
dirname = optx['dirname'] if 'dirname' in optx else '.'
fname = '{}/daily_macdEnhance_{}.j2'.format(dirname,lang)
ts_enhance = open(fname).read()
enhanceClause = generate_comment_ohlc(md2,ts_enhance,lang=lang)
if 'fcsChg' in optx:
md1.update(fcsChg=optx['fcsChg'])
if 'label' in optx:
md1['label']=optx['label']
md1['enhanceClause'] = enhanceClause
fname = '{}/daily_ohlc_{}.j2'.format(dirname,lang)
ts_ohlc = open(fname).read()
ret = generate_comment_ohlc(md1,ts_ohlc,lang=lang)
if debugTF is True:
print >> sys.stderr, ret
return ret
#----- MAIN ------#
if __name__ == '__main__':
pgDB=conn2pgdb(dbname='ara',hostname='localhost')
args=sys.argv[1:]
ticker = 'SPY' if len(args)==0 else args[0]
date = 20181203 if len(args)<=1 else int(args[1])
minute_hist = get_stgyData(ticker,date)
minute_pnl = find_stgy(minute_hist,pgDB)
#run_narratives_tst(ticker,minute_hist,minute_pnl,pgDB)
ret = run_narratives(ticker,minute_hist,minute_pnl,pgDB)
print >> sys.stderr, ret
def get_select_list(dbname='ara.tw', tkLst=None):
pgDB = conn2pgdb(dbname=dbname)
xqTmp = "SELECT b.company_cn as label,a.* FROM (SELECT * FROM ohlc_daily_comment_cn_mp3 WHERE ticker in ({})) as a LEFT JOIN mapping_ticker_cik b ON a.ticker=b.ticker ORDER BY a.trr"
xqr = xqTmp.format("'{}'".format("','".join(tkLst)))
df = pd.read_sql(xqr, pgDB)
return (df, pgDB)
def t2l(ticker='', output='dict', quoteTF=True, dbname='ara'):
''' get basic info from ticker
'''
if isinstance(ticker, list):
return batch_t2l(tkLst=ticker,
output=output,
quoteTF=quoteTF,
dbname=dbname)
#- GET summaryProfile from mDB:yh_summaryProfile or onTheFly
dg = tk2infoM(ticker,
tablename="yh_summaryProfile",
funcArg='yh_financials',
modules="summaryProfile",
zpk={'ticker'},
deltaTolerance=864000)
dg = renameDict(dg, {"sector": "sector_alias"})
#- GET basic ticker info from pgDB:mapping_ticker_cik
pgDB = conn2pgdb(dbname=dbname)
df = tk2info(ticker, tablename='mapping_ticker_cik', pgDB=pgDB)
if all([len(df), len(dg)]):
df = df.merge(dg, on='ticker')
elif len(dg) > 0:
df = dg
#- CHECK available data ---------------------------#
if len(df) < 1:
return {}
#- GET ticker sector info from pgDB:spdr_sector
if 'sector_alias' in df:
sa = 'sector_alias'
dg = tk2info(df[sa].values[0],
tablename='spdr_sector',
colx=sa,
pgDB=pgDB)
elif 'sector' in df:
sa = 'sector'
dg = tk2info(df[sa].values[0],
tablename='spdr_sector',
colx=sa,
pgDB=pgDB)
else:
dg = []
if all([len(df), len(dg)]):
df = df.merge(dg[['etfname', 'sector', 'sector_alias', 'sector_cn']],
on=sa)
if "sector_cn" not in df:
df = renameDict(df, {
"sector_cn_x": "sector_cn",
"sector_x": "sector"
})
elif len(dg) > 0:
df = dg
if 'industry' in df:
dg = tk2info(df['industry'].values[0],
tablename='sector_alias_industry',
colx='industry',
pgDB=pgDB)
if len(dg):
df = df.merge(dg[['industry', 'industry_cn']], on='industry')
else:
df['industry_cn'] = df['industry'].values
#- GET summaryProfile from mDB:yh_quote_curr or onTheFly
dg = tk2infoM(ticker,
tablename='yh_quote_curr',
funcArg='yh_quote_comparison',
zpk={'ticker'},
deltaTolerance=900)
if all([len(df), len(dg)]):
df = df.merge(dg, on='ticker')
if 'trailingPE' in df:
df['peRatio'] = df['trailingPE']
elif len(dg) > 0:
df = dg
if 'sector' not in df and 'quoteType' in df:
df['sector_cn'] = df['sector'] = df['quoteType']
try:
if 'shortName' in df:
df['company'] = df['shortName']
if not df['company'].values[0]:
df['company'] = df['ticker']
if 'company_cn' not in df:
df['company_cn'] = en2cn(df['company'].values[0])
except Exception as e:
sys.stderr.write("**ERROR:{} via {} in {}\n".format(
str(e), 'en2cn', 't2l()'))
if output == 'dict':
return df.to_dict(orient='records')[0]
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
7. check splits, M&A activies daily basis
'''
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)
def batch_ohlc_fcs(tkLst, opts=None, optx=None, fpTF=False):
if opts is None:
opts, _ = opt_ohlc_fcs([])
if optx is not None:
opts.update(optx)
if opts['category'] != 'stock':
opts['src'] = 'fred'
for ky, va in opts.items():
exec("{}=va".format(ky))
debugTF = getKeyVal(opts, 'debugTF', False)
dbname = getKeyVal(opts, 'dbname', False)
hostname = getKeyVal(opts, 'hostname', 'localhost')
days = getKeyVal(opts, 'days', 730)
freq = getKeyVal(opts, 'freq', 'D')
j2ts = getKeyVal(opts, 'j2ts', None)
j2name = getKeyVal(opts, 'j2name', None)
category = getKeyVal(opts, 'category', 'stock')
lang = getKeyVal(opts, 'lang', 'cn')
saveDB = getKeyVal(opts, 'saveDB', False)
mp3YN = getKeyVal(opts, 'mp3YN', False)
if debugTF is True:
pqint(opts, file=sys.stderr)
pgDB = conn2pgdb(dbname=dbname, hostname=hostname)
dcmt = ''
for ticker in tkLst:
if '|' in ticker:
alst = ticker.split('|')
dlst = [alst[0], freq, sector, alst[0]]
(ticker, freq, sector, label) = alst + dlst[len(alst) - len(dlst):]
else:
label = ticker
if freq != 'D':
nday = adjust_fq_days(days, fq=freq)
opts['freq'] = freq
opts['days'] = nday
else:
nday = days
try:
(dd, df, datax) = run_ohlc_fcs(ticker, opts, pgDB=pgDB)
if df is None:
continue
pqint(df.tail(3), file=sys.stderr)
j2ts = j2ts if j2ts is not None else open(
j2name).read() if j2name is not None else None
dcmt, fp = convert_data_comment_fcst(ticker,
category,
df,
pgDB,
lang=lang,
mp3YN=mp3YN,
ts=j2ts,
fpTF=True)
if saveDB is True:
df.to_sql(tablename,
pgDB,
schema='public',
index=False,
if_exists=wmode)
wmode = 'append'
pqint(df, file=sys.stderr)
else:
pqint(df.to_csv(sep="\t"), file=sys.stderr)
pqint(dcmt, file=sys.stdout)
except Exception as e:
pqint(str(e), file=sys.stderr)
pqint('**ERROR {}: {}'.format("batch", ticker), file=sys.stderr)
continue
if pgDB is not None:
pgDB.dispose()
global gData
from lsi_daily import gData
return dcmt if fpTF is False else (dcmt, fp)
import pprint
#%matplotlib inline
#import matplotlib.pyplot as plt
from hashlib import md5
if sys.version_info.major == 2:
reload(sys)
sys.setdefaultencoding('utf8')
dtCvt = lambda x: str(x) if isinstance(x, (ObjectId,datetime.datetime)) else x
# set pandas global display formula
pd.set_option('display.max_colwidth', -1)
pd.options.display.float_format='{:,.2f}'.format
# set mongoDB database connection as globals()
mgDB=conn2mgdb(dbname='ara')
pgDB=conn2pgdb(dbname='ara')
currDate=datetime.datetime.now()
def verify_apikey(ak):
if ak=='beyondbond_internal_testing_only':
return 1
ht=pd.read_csv("/apps/fafa/webDir/loginDir/.htpasswd.dat",sep="\t")
aLst=list(ht['api_key'])
return 1 if ak.lower() in aLst else 0
class myPrettyPrinter(pprint.PrettyPrinter):
def format(self, object, context, maxlevels, level):
if isinstance(object, unicode):
#return (object.encode('utf8'), True, False)
return (object, True, False)
return pprint.PrettyPrinter.format(self, object, context, maxlevels, level)
