def report_comment(tkLst,fdLst,lang='cn',dbname='ara',hostname='localhost',limit=1,**optx):
outTF=optx.pop('outTF',True)
tablename='daily_single_stock'
if tkLst is not None and len(tkLst)>0:
ticker=tkLst[0].upper()
jobj={"ticker":ticker}
else:
ticker=''
jobj={}
sortLst={'pbdt'}
data=[]
try:
xtmp,_,_=find_mdb(jobj,tablename=tablename,dbname=dbname,sortLst=sortLst,limit=1,dfTF=False)
if len(xtmp)>0:
field = {x for x in xtmp[0] if not isinstance(x,(dict,list))}
data,_,err=find_mdb(jobj,tablename=tablename,dbname=dbname,field=field,sortLst=sortLst,limit=limit,dfTF=outTF)
except Exception as e:
sys.stderr.write("**ERROR: {},{} @{}\n".format(ticker,str(e),'report_comment'))
data=[]
if len(data)<1:
ret=rerun_dss(ticker)
sys.stderr.write("**WARNING: {} @{}\n".format(ret,'report_comment'))
return ret
pqint( " --from tablename: {}".format(data) ,file=sys.stderr)
# data = data_output(data,output)
pqint( "===report_comment():\nFind:{},Field:{},Sort:{}\n".format(jobj,field,sortLst) ,file=sys.stderr)
pqint( " --from tablename: {}".format(tablename) ,file=sys.stderr)
pqint( " --DF[top]:\n{}".format(data) ,file=sys.stderr)
return data
def topic_theme_media_OLD(**opts):
from _alan_str import find_mdb
subtab = getKeyVal(opts, 'subtab', 'blacklist')
if subtab.lower() == 'whitelist':
jobj = {"rrt": {"$gt": 10}}
dtmp, mDB, errmsg = find_mdb(jobj,
tablename='topic_theme_media',
dbname='ara',
dfTF=True)
df = dtmp.sort_values(by='rrt', ascending=False).iloc[:100]
elif subtab.lower() == 'conflict':
dtmp, mDB, errmsg = find_mdb({},
tablename='topic_theme_media',
dbname='ara',
dfTF=True)
a1 = list(dtmp.query("rrt>=1.0")['ticker'].unique())
a2 = list(dtmp.query("rrt<=-1.0")['ticker'].unique())
aLst = set(a1).intersection(set(a2))
df = dtmp.loc[dtmp['ticker'].isin(aLst)].sort_values(by='ticker')
else:
jobj = {"rrt": {"$lt": -10}}
dtmp, mDB, errmsg = find_mdb(jobj,
tablename='topic_theme_media',
dbname='ara',
dfTF=True)
df = dtmp.sort_values(by='pbdate', ascending=False).iloc[:100]
colX = [
"ticker", "pbdate", "start", "end", "sPrice", "ePrice", "rrt",
"Price", "SegmentDscript", "CallDscript", "Company"
]
df = subDF(df, colX)
return df
def select_eps_hist(ticker='AAPL',
pbdate=None,
types='earnings',
dbname='ara',
**optx):
debugTF = optx.pop('debugTF', False)
if types.lower() in ['earnings', 'eps']:
tablename = 'earnings_yh'
elif types.lower() == 'stats':
tablename = 'qS_keyStatistics'
jobj = {"ticker": ticker}
ret, _, errmsg = find_mdb(jobj, dbname=dbname, tablename=tablename)
if len(ret) < 1:
return {}
df = pd.DataFrame(ret)
return df
else:
return {}
pbdate = int(next_date(pbdate, dformat='%Y%m%d', dtTF=False, days=-2))
jobj = {"pbdate": {'$lt': pbdate}, "ticker": ticker}
ret, _, errmsg = find_mdb(jobj,
dbname=dbname,
tablename=tablename,
sortLst=['pbdate'],
ascendingTF=False)
df = pd.DataFrame(ret)
if debugTF:
sys.stderr.write("EPS {} of {}:\n".format(ticker, tablename))
sys.stderr.write("{}\n".format(df))
if 'actualEPS' not in df:
return df
df = df.dropna(subset=['actualEPS'])
df = df[~df['actualEPS'].isin(['-'])]
df['actualEPS'] = df['actualEPS'].astype(float)
return df
def get_fund_quote(jobj={}, clientM=None, **optx):
'''
Get first set data: data1 based on jobj={"fund":'fundName'}
then based on ticker list: 'tkLst' and combine 'data1' with quote data
base on mergerOn=['ticker'] and mergeHow='left' to merge
'''
if len(jobj) < 1:
return {}, clientM, 'jobj is {}', 0
limit = optx.pop('limit', 100)
dbname = optx.pop('dbname', None)
tablename = optx.pop('tablename', '')
#
# get first set data: data1
data0, clientM, err = find_mdb(jobj,
clientM,
dbname=dbname,
tablename=tablename,
sortLst={"funddate"},
limit=1,
dfTF=False)
if len(data0) < 1:
return {}, clientM, "wrong fundName", 0
mxdate = data0[0]['funddate']
jobj.update({"funddate": mxdate})
sortLst = {'CurrentValueUSDx1000'}
data1, clientM, err = find_mdb(jobj,
clientM,
dbname=dbname,
tablename=tablename,
sortLst=sortLst,
limit=100,
dfTF=True)
if len(data1) < 1:
return {}, clientM, "no data found", mxdate
tkLst = list(data1['ticker'])
#
# Get ticker list: 'tkLst' and combine 'data1' with quote data 'data2' on 'left' merge
mergeOn = optx.pop('mergeOn', ['ticker'])
mergeHow = optx.pop('mergeHow', 'left')
kobj = {"ticker": {"$in": tkLst}}
data2, clientM, err = find_mdb(kobj,
clientM,
tablename='yh_quote_curr',
dfTF=True)
data2['pbdt'] = data2['pbdt'].astype(object).where(data2['pbdt'].notnull(),
None)
data1 = data1.merge(data2, on=mergeOn, how=mergeHow)
return data1, clientM, err, mxdate
def find_ipoReport(dbname='ara',
tablename='ipoReport',
debugTF=False,
sort='marketCapMM',
**optx):
output = getKeyVal(optx, 'output', 'html')
if sort not in [
'Range52Week', 'change', 'changePercent', 'currDate', 'currPrice',
'daysSinceIPO', 'fiftyDayAverage', 'ipoDate', 'ipoPrice',
'marketCapMM', 'shortName', 'ticker', 'trailingPE'
]:
sort = 'marketCapMM'
df, clientM, _ = find_mdb(dbname=dbname,
tablename=tablename,
dfTF=True,
sortLst=[sort])
pd.options.display.float_format = '{:,.2f}'.format
cfm = {'marketCapMM': "{:,.0f}".format, 'changePercent': "{:.2%}".format}
if output.lower() in ['csv']:
return (df.to_csv(sep="|", index=False))
elif output.lower() in ['tsv']:
return (df.to_csv(sep="\t", index=False))
elif output.lower() in ['html']:
return (df.to_html(formatters=cfm, index=False))
else:
return (df)
def find_latest_eps(ticker='UNP', cdt=None, dbname='ara', debugTF=False):
"""
Find eps since monday of the last week
"""
if not cdt:
cdt = next_date()
elif not isinstance(cdt, datetime.date):
cdt = next_date(cdt)
gap = -1 if cdt.weekday() == 0 else -2
xdt = next_date(cdt, weeks=gap, weekday=0) # monday of the last week
sundt = next_date(xdt, days=6) # coming sunday from xdt(monday)
xdate = int(xdt.strftime('%Y%m%d'))
findDct = {"pbdate": {'$gte': xdate}, "ticker": ticker}
# use zacks for up-to-date EPS
ret, _, errmsg = find_mdb(
jobj=findDct,
dbname=dbname,
tablename='earnings_zacks',
field=['ticker', 'Estimate', 'Reported', 'pbdate', 'Time'],
sortLst=['pbdate'],
ascendingTF=False)
if len(ret) > 0:
dd = ret[0]
dd['eps'] = float(dd['Reported'])
pdt = s2dt(dd['pbdate'])
if pdt <= xdt: # discard any data less Monday of the week
sys.stderr.write("Stale news:{} < {}, disregard!\n".format(
pdt, xdt))
return {}
currWeekTF = (pdt > sundt)
todayTF = (pdt.day == cdt.day)
dd.update(weekday=pdt.weekday() + 1,
currWeekTF=currWeekTF,
todayTF=todayTF)
pbdate = dd['pbdate']
else:
return {}
dh = select_eps_hist(ticker=ticker, pbdate=pbdate, debugTF=debugTF)
if len(dh) > 0:
ceps = dd['eps']
if dh.shape[0] > 3:
xeps = dh['actualEPS'].iloc[3]
dd.update(eps_chgFreq='Y')
else:
xeps = dh['actualEPS'].iloc[0]
dd.update(eps_Freq='Q')
pchg, chgTF = calc_pchg(ceps, xeps)
dd.update(eps_pctChange=pchg, prev_eps=xeps, eps_isPct=chgTF)
dd.update(latest2weekTF=True)
d1 = dh[['actualEPS', 'pbdate']].set_index('pbdate')
d1.loc[pbdate] = {'actualEPS': ceps}
dd.update(past_eps=d1.sort_index())
dh = select_eps_hist(ticker=ticker, types='financials', debugTF=debugTF)
if len(dh) and 'revenue' in dd:
revenueStr = dd['revenue']
revenue = num_en2cn(revenueStr, numTF=True)
pchg = revenue / dh['totalRevenue'].iloc[0] - 1
dd.update(revenue_pctChange=pchg, prev_revenue=xeps)
dd.update(revenue=revenue, revenueStr=revenueStr)
return (dd)
def tmpl2mp4(ts='',category='ItD',title='intraday_briefing',datax={},debugTF=False,_pn_={},**optx):
'''
Create comment, mp3comment based on 'title' and svg and
save them into into MDB table: 'mkt_briefing_details' (for 'intraday_briefing' and 'daily_briefing')
or 'daily_single_stock' (for title='daily_single_stock')
'''
if title[-9:]=='_briefing':
tablename="mkt_briefing_details"
rptTxt = dat2rptTxt(ts=ts,_pn_=_pn_,debugTF=debugTF,**optx)
zpk = ['category','templateName','rpt_time','lang']
sys.stderr.write("==SAVE {} to MDB table:{}\n".format(title,tablename))
elif title=='daily_single_stock':
dbname='ara'
tablename=title
deltaTolerance= optx.pop('deltaTolerance',43200)
ticker=getKeyVal(optx,'ticker','')
o,m,e=find_mdb(dict(ticker=ticker),dbname=dbname,tablename=tablename,sortLst=['pbdt'])
if len(o)>0 and 'pbdt' in o[0]:
pbdtMod=o[0]['pbdt']
deltaPassed=int(pd.Timedelta(pd.datetime.now()-pbdtMod).total_seconds())
if deltaPassed<=deltaTolerance:
sys.stderr.write(" --pbdtMod:{},deltaPassed:{},deltaTolerance:{}\n".format(pbdtMod,deltaPassed,deltaTolerance))
sys.stderr.write(" --Directly use comment from table: {}\n".format(tablename))
return o[0]
rptTxt = dat2rptTxt(ts=ts,_pn_=_pn_,debugTF=debugTF,**optx)
_pn_['headTitle']=_pn_['stock_performance']['comment'];
ticker=_pn_['ticker']=_pn_['stock_performance']['ticker'];
_pn_['category']=category
_pn_['templateName']=title
_pn_['pbdt']=_pn_['stock_performance']['pbdt'];
_pn_['lang']=getKeyVal(optx,'lang','cn')
zpk=['ticker']
sys.stderr.write("==SAVE {}:{} to MDB table:{}\n".format(title,ticker,tablename))
else:
return {}
_pn_.update(title=title)
saveBriefing(_pn_,tablename=tablename,zpk=zpk)
if debugTF:
sys.stderr.write("=====ts:\n{}\n".format(ts))
sys.stderr.write("=====optx:\n{}\n".format(optx))
sys.stderr.write("=====rptTxt:\n{}\n".format(rptTxt))
sys.stderr.write("=====_pn_:\n{}\n".format(_pn_))
sys.stderr.write("=====keys:\n{}\n".format(_pn_.keys()))
if 'tmplLst' in _pn_:
tmplLst = _pn_['tmplLst']
sys.stderr.write("=====tmplLst: {}\n".format(tmplLst))
for tname in tmplLst:
dx = _pn_[tname]
sys.stderr.write("===Chartpath: {}:{}\n".format(tname,dx['chartpath']))
else:
sys.stderr.write("*ERROR:tmplLst not found in {}\n".format(_pn_.keys()))
return {}
txtOnly=optx.pop('txtOnly',False)
if txtOnly is True:
sys.stderr.write("=====RUN Text Only: {}\n".format(txtOnly))
return _pn_
sys.stderr.write("=====RUN pn2mp4: {}\n".format(tmplLst))
dpn = pn2mp4(_pn_=_pn_,zpk=zpk,**optx)
return dpn
def topic_theme_majorplayer(**opts):
'''
use 'themename' as tablename
'''
from _alan_str import find_mdb
fund = getKeyVal(opts, 'fund', 'renaissance-technologies-llc')
clientM = getKeyVal(opts, 'clientM', None)
dbname = getKeyVal(opts, 'dbname', 'ara')
themename = getKeyVal(opts, 'themename', 'topic_theme_majorplayer')
dfTF = getKeyVal(opts, 'dfTF', True)
jobj = dict(fund=fund)
df, mDB, errmsg = find_mdb(jobj,
tablename=themename,
dbname=dbname,
dfTF=dfTF)
colX = [
'ticker', 'close', 'marketCap', 'changePercent', 'CurrentShares',
'percentPos', 'SharesChangePercent', 'fiftyTwoWeekRange', 'pbdate',
'shortName', 'fund', 'funddate', 'pbdt'
]
colDc = {
'close': 'closePrice',
'pbdate': 'closeDate',
'shortName': 'company',
'SharesChangePercent': 'SharesChg%',
'changePercent': 'Chg%',
'percentPos': 'Position%'
}
df = subDict(df, colX)
df = renameDict(df, colDc)
return df
def topic_theme_ipo(**opts):
from _alan_str import find_mdb
subtab = getKeyVal(opts, 'subtab', '')
opts.pop('output', None) # not output type assigned
updTF = opts.pop('updTF', False)
jobj = json.loads(opts.pop('jobj', '{}'))
sector = opts.pop('sector', '')
if len(sector) > 5:
sLst = sector.split(',')
jobj.update(sector={"$in": sLst})
#df = get_ipoReport(updTF=updTF,**opts) # DEPRECATED
#df = create_topic_theme_ipo(updTF=updTF,**opts) # SETUP in crontab
tablename = opts.pop('tablename', None)
if tablename is None:
tablename = 'topic_theme_ipo'
df, _, _ = find_mdb(jobj, dbname='ara', tablename=tablename, dfTF=True)
colX = [
'ticker', 'ipoDate', 'marketCap', 'ipoPrice', 'Price', 'closePrice',
'ipoChg%', 'dayChg%', 'EPS', 'Range52Week', 'Company', 'sector',
'industry', 'pubDate'
]
df = subDict(df, colX)
#-onTheFly run, not used
#-run daily crontab to create 'topic_theme_ipo' table via
#-python3 -c "from ipoReport import create_topic_theme_ipo as ctt;ctt()"
if subtab.lower() == 'conflict':
df = df.query('"ipoChg%">10')
return df
def create_topic_theme_media(start=20200101,
dbname="ara",
tablename="topic_theme_media",
tablesrc="madmoney_hist",
**optx):
from _alan_str import find_mdb
from yh_chart import yh_quote_comparison as yqc
dtmp, mDB, errmsg = find_mdb(
{
'pbdate': {
'$gt': start
},
'Call': {
'$in': ['4', '5']
}
},
sortLst={'ticker', 'pbdate'},
dbname=dbname,
tablename=tablesrc,
dfTF=True)
dg = dtmp.groupby(['ticker']).apply(lambda x: pd.Series(
[x.Call.count(), x.pbdate.max()], index=['buyCount', 'buyDate']))
renameDict(dtmp, {'pbdate': 'buyDate', 'Price': 'buyPrice'})
mediaD = dtmp.merge(dg, on=['ticker', 'buyDate'])
colX = ['ticker', 'buyCount', 'buyDate', 'buyPrice', 'sector', 'industry']
mediaD = subDict(mediaD, colX)
quoLst = yqc(mediaD['ticker'].values)
quoD = pd.DataFrame(quoLst)
colX = [
'ticker', 'close', 'fiftyTwoWeekRange', 'marketCap', 'pbdate',
'shortName', 'changePercent', 'epsTrailingTwelveMonths', 'pbdt'
]
quoD = subDict(quoD, colX)
quoD = renameDict(
quoD,
dict(epsTrailingTwelveMonths='EPS',
close='closePrice',
shortName='Company',
fiftyTwoWeekRange='Range52Week',
changePercent='dayChg%',
change='Chg',
pbdt='pubDate'))
df = mediaD.merge(quoD,
on='ticker') #- remove no-quote rows # ,how='left')
df.dropna(subset=['marketCap'], inplace=True)
df['buyChg%'] = (df['closePrice'] / df['buyPrice'].astype(float) - 1) * 100
colX = [
'ticker', 'buyCount', 'buyDate', 'marketCap', 'buyPrice', 'closePrice',
'buyChg%', 'dayChg%', 'EPS', 'Company', 'Range52Week', 'pbdate',
'pubDate', 'sector', 'industry'
]
#df=subDict(df,colX)
print(" --media DF:\n{}".format(df), file=sys.stderr)
zpk = optx.pop('zpk', {'ticker'})
upsert_mdb(df, dbname=dbname, tablename=tablename, zpk=zpk)
sys.stderr.write(" --DF:\n{}\n".format(df.head().to_string(index=False)))
return df
def get_hiloRecord(ticker,pbdate):
from _alan_str import find_mdb
ret,_,_=find_mdb({"ticker":ticker,"pbdate":pbdate},tablename="record_hilo",dbname="ara")
if len(ret)<1:
hiloRecord={}
else:
hiloRecord=ret[0]['YTD'] if len(ret[0]['YTD'])>0 else {}
return hiloRecord
def mongo_upd_tst(jobj={},limit=0,sortLst={},field={},dfTF=False,debugTF=False,**optx):
hostname,port,dbname,tablename = getKeyVal(optx,
['hostname','port','dbname','tablename'], ['localhost',27017,'ara','rssNews'])
data,_,_=find_mdb(jobj,tablename=tablename,dbname=dbname,field=field,sortLst=sortLst,limit=limit,dfTF=dfTF)
if len(data)<1:
return None
if debugTF is True:
prn_dict(field=field,sortLst=sortLst,limit=limit,**optx)
return data
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 find_lsi2nlg_info(jobj={},
fieldLst=['username', 'category', 'tmplname', 'lang'],
dbname='ara',
tablename='lsi2nlg'):
findDct = subDict(jobj, fieldLst)
mobj, clientM, err_msg = find_mdb(jobj=findDct,
dbname=dbname,
tablename=tablename)
if len(mobj) > 0:
return (mobj[0])
else:
return []
def ytdOTF(funcArg, **optx):
'''
real-time data is only grab based on 'deltaTolerance' in seconds
current setup is half-days
'''
if isinstance(funcArg, str):
if funcArg in globals() and hasattr(globals()[funcArg], '__call__'):
funcArg = globals()[funcArg]
else:
return []
sys.stderr.write("==START Running {}\n".format(funcArg))
deltaTolerance = optx.pop('deltaTolerance', 43200)
dbname = optx.pop('dbname', 'ara')
tablename = optx.pop('tablename', '')
tableChk = optx.pop('tableChk', tablename + '_chk')
objChk = optx.pop('objChk', {})
zpkChk = optx.pop('zpkChk', list(objChk.keys()))
if not all([tablename]):
return []
renewTF, objChk, clientM = lastRunChk(objChk=objChk,
dbname=dbname,
tableChk=tableChk,
deltaTolerance=deltaTolerance,
**optx)
if renewTF:
sys.stderr.write(
"==Data outdated or never run, Running:{}\n".format(funcArg))
retObj = funcArg(dbname=dbname, **optx)
if len(retObj) < 1:
return []
retObj, clientM, errChk = upsert_mdb(retObj,
dbname=dbname,
tablename=tablename,
**optx)
sys.stderr.write(" --Update {} to {}\n".format(objChk, tableChk))
objChk, clientM, errChk = upsert_mdb(objChk,
clientM=clientM,
tablename=tableChk,
zpk=zpkChk)
else:
sys.stderr.write("==Data exist, LoadFromTable:{}\n".format(tablename))
objChk.pop('pbdt', None)
optx.pop('zpk', None)
optx.pop('field', {})
retObj, clientM, errMsg = find_mdb(objChk,
clientM=clientM,
dbname=dbname,
tablename=tablename,
field={},
**optx)
return retObj
def getlist_quotes(tkLst,fdLst,tablename="iex_sector_quote",lang=None,dbname="ara",hostname='localhost',colx='symbol'):
from _alan_str import find_mdb
dd = []
pqint( tkLst ,file=sys.stderr)
for ticker in tkLst:
ret,_,_=find_mdb({colx:ticker},tablename=tablename,dbname=dbname)
pqint( ret,ticker,tablename,dbname ,file=sys.stderr)
dd = dd + ret
df = pd.DataFrame(dd)
if fdLst != '*':
field = set(fdLst.split(','))
newcol = list(field & set(df.columns))
df = df[newcol]
return df
def create_topic_theme_ipo(updTF=False, **opts):
''' create 'topic_theme_ipo' based on
'nasdaq_ipos' and yh live-quote info
'''
from _alan_calc import renameDict, subDict
from _alan_str import find_mdb, upsert_mdb
from yh_chart import yh_quote_comparison as yqc
# Note: 500 limit may cause close prices of certain tickers not get updated, need further debugging
limit = opts.pop('limit', 500)
ipoLst, _, _ = find_mdb(tablename='nasdaq_ipos',
dbname='ara',
sortLst=['pbdate'],
limit=limit,
dfTF=True)
ipoLst = renameDict(ipoLst, dict(pbdate='ipoDate', price='ipoPrice'))
ipoD = subDict(ipoLst,
['ticker', 'ipoDate', 'ipoPrice', 'sector', 'industry'])
quoLst = yqc(ipoD['ticker'].values)
quoD = pd.DataFrame(quoLst)
colX = [
'ticker', 'close', 'fiftyTwoWeekRange', 'marketCap', 'pbdate',
'shortName', 'changePercent', 'epsTrailingTwelveMonths', 'pbdt'
]
quoD = subDict(quoD, colX)
quoD = renameDict(
quoD,
dict(epsTrailingTwelveMonths='EPS',
close='closePrice',
shortName='Company',
fiftyTwoWeekRange='Range52Week',
changePercent='dayChg%',
change='Chg',
pbdt='pubDate'))
df = ipoD.merge(quoD, on='ticker') #- remove no-quote rows # ,how='left')
df.dropna(subset=['marketCap'], inplace=True)
df['ipoChg%'] = (df['closePrice'] / df['ipoPrice'].astype(float) - 1) * 100
colX = [
'ticker', 'ipoDate', 'marketCap', 'ipoPrice', 'closePrice', 'ipoChg%',
'dayChg%', 'EPS', 'Company', 'Range52Week', 'pbdate', 'pubDate',
'sector', 'industry'
]
df = subDict(df, colX)
pqint(" --ipo DF:\n{}".format(df), file=sys.stderr)
dbname = opts.pop('dbname', 'ara')
tablename = opts.pop('tablename', 'topic_theme_ipo')
zpk = opts.pop('zpk', {'ticker'})
upsert_mdb(df, dbname=dbname, tablename=tablename, zpk=zpk)
sys.stderr.write(" --DF:\n{}\n".format(df.head().to_string(index=False)))
return df
def run_rssCompany(ticker='',
deltaTolerance=43200,
clientM=None,
dbname='ara',
tablename='rssCompany',
sortLst=['pubDate']):
'''
To get company news based on ticker='AAPL'
where real-time data is only grab based on 'deltaTolerance' in seconds
current setup is half-days
'''
clientM = None
currDt = pd.datetime.now()
jobj = {"ticker": ticker}
tableChk = tablename + '_chk'
zsrt = [(k, -1) for k in sortLst] if len(sortLst) > 0 else [('_id', 1)]
lastObj, clientM, _ = find_mdb(jobj,
clientM=clientM,
dbname=dbname,
tablename=tableChk,
sortLst=sortLst,
limit=1)
if not lastObj:
renewTF = True
else:
lastModDt = lastObj[0]['lastModDt']
renewTF = renewChk(currDt, lastModDt, deltaTolerance)
if renewTF:
sys.stderr.write("==Data outdated, loading now\n")
ret = load_rssCompany(ticker,
clientM=clientM,
dbname=dbname,
tablename=tablename,
zpk={"ticker", "pubDate", "link"})
jobj.update(lastModDt=currDt)
sys.stderr.write(" --{}\n".format(jobj))
retChk, clientM, errChk = upsert_mdb(jobj,
clientM=clientM,
dbname=dbname,
tablename=tableChk,
zpk={"ticker"})
else:
sys.stderr.write("==Data exist, no loading\n")
ret = clientM[dbname][tablename].find(jobj, sort=zsrt)
ret = list(ret)
#ret,clientM,err = clientM[dbname][tablename]find_mdb(jobj,clientM=clientM,dbname=dbname,tablename=tablename)
return adjObjectId(ret)
def hourly_comment(tkLst,fdLst,tablename=None,lang=None,dbname="ara",hostname='localhost',output=None,start=None,end=None,topic=None,subtopic=None,**optx):
from _alan_str import find_mdb
if fdLst=='*':
field = {}
else:
field = set(fdLst.split(','))
limit=0
instrument = getKeyVal(optx,'instrument','stock')
rpt_hm = getKeyVal(optx,'rpt_hm',None)
hhmm = getKeyVal(optx,'hhmm',None)
pqint(tablename,optx,file=sys.stderr)
#if subtopic.lower() == 'sector':
tablename="mkt_briefing_media"
if subtopic == 'sector':
tablename="hourly_report"
if len(tkLst)<1:
jobj={"ticker":{"$in":["^GSPC","^DJI","^IXIC"]}}
sortLst=['rpt_time']
field={'ticker','comment','rpt_time','rpt_hm','rpt_status','title','label'}
limit=3
elif rpt_hm is not None or len(tkLst)>0:
tablename="mkt_briefing_details"
jobj={}
if rpt_hm is not None:
hmLst = [int(x) for x in rpt_hm.split(',')]
jobj.update(rpt_hm={"$in":hmLst})
if len(tkLst)>0 and tkLst[0]!='$' :
jobj.update(ticker={"$in":tkLst})
if len(field)<1:
field = {"ticker","label","rpt_hm","rpt_time","cprice","xprice","comment","rpt_date","pngname"}
sortLst=['rpt_time']
elif hhmm is not None:
hmLst = [int(x) for x in hhmm.split(',')]
jobj={"hhmm":{"$in":hmLst}}
sortLst=['pbdt']
else:
jobj={}
sortLst=['pbdt']
limit=1
outTF = getKeyVal(optx,'outTF',True)
df,_,_=find_mdb(jobj,tablename=tablename,dbname=dbname,field=field,sortLst=sortLst,limit=limit,dfTF=outTF)
data = df # data = data_output(df,output)
pqint( "===hourly_comment():\nFind:{},Field:{},Sort:{}\n".format(jobj,field,sortLst) ,file=sys.stderr)
pqint( " --tkLst: {},fdLst: {}".format(tkLst,fdLst) ,file=sys.stderr)
pqint( " --from tablename: {}".format(tablename) ,file=sys.stderr)
pqint( " --DF:\n{}".format(df)[:200]+"\n" ,file=sys.stderr)
return data
def topic_theme_media(**opts):
from _alan_str import find_mdb
jobj = {"buyCount": {"$gt": 5}}
dtmp, mDB, errmsg = find_mdb(jobj,
tablename='topic_theme_media',
dbname='ara',
dfTF=True)
df = dtmp.sort_values(by='buyCount', ascending=False)
colX = [
'ticker', 'buyDate', 'marketCap', 'buyPrice', 'closePrice', 'buyChg%',
'buyCount', 'dayChg%', 'EPS', 'pbdate', 'Range52Week', 'Company',
'sector', 'industry', 'pubDate'
]
if 'marketCap' in df:
df['marketCap'] = df['marketCap'].astype(float)
df = subDF(df, colX)
return df
def find_lsi2nlg_list(findDct={'username': '******'},
fieldLst=['tmplname'],
dbname='ara',
tablename='lsi2nlg'):
if not isinstance(fieldLst, list):
fieldLst = list(fieldLst)
fieldDct = {x: 1 for x in fieldLst}
xg, _, _ = find_mdb(dbname=dbname,
tablename=tablename,
jobj=findDct,
field=fieldDct)
if len(xg) < 1:
return []
else:
df = pd.DataFrame(xg)
ky = fieldLst[0]
return df[ky].sort_values().unique()
def news_comment(tkLst,fdLst,lang='cn',dbname='ara',hostname='localhost',output='html',limit=200,**optx):
from remote2mgdb import remote2mgdb
from _alan_str import popenCall
if popenCall('hostname')[0].decode().strip() not in ['bbapi','api1']:
serverM,clientM=remote2mgdb(sshPass='******',host='api1.beyondbond.com')
else:
serverM,clientM=None,None
from mongo_en2cn import mongo_en2cn as me
outTF=optx.pop('outTF',True)
tablename='rssNews'
jobj={}
subtopic=optx.pop('subtopic','')
sortLst=['pubDate']
data=[]
if subtopic=='company' and len(tkLst)>0:
ticker=tkLst[0]
from rssCompany import run_rssCompany as rrc
data = rrc(ticker)
elif subtopic=='translate':
link=optx.pop('link','')
_id=optx.pop('_id','')
src=optx.pop('fromLang','en')
dest=optx.pop('toLang','zh-tw')
tablename=optx.pop('tablenews','rssCompany')
if link:
jobj={'link':link}
elif not _id:
return []
else:
jobj={'_id':ObjectId(_id)}
field={'title','title_cn','summary','summary_cn'}
print("===Run Translation:",jobj,tablename,field,src,dest,file=sys.stderr)
data = me(jobj,clientM=clientM,tablename=tablename,field=field,src=src,dest=dest)
else:
jobj.update({"source.title":{"$nin":["Insider Monkey","TipRanks"]}})
field={'_id','ticker','title','title_cn','description','summary','summary_cn','pubDate','link','source'}
data,_,_=find_mdb(jobj,clientM=clientM,tablename=tablename,dbname=dbname,field=field,sortLst=sortLst,limit=limit,dfTF=outTF)
if isinstance(data,pd.DataFrame):
data.drop_duplicates(subset =["pubDate","link"], keep = 'first', inplace = True)
if outTF and not isinstance(data,pd.DataFrame):
data = pd.DataFrame(data)
if serverM:
clientM.close()
serverM.stop()
return data
def yh_financials(ticker,
modules='incomeStatementHistory',
saveDB=False,
clientM=None,
useDB=False,
dbname='ara',
tablename='',
**optx):
''' pull financials from yahoo and save to 'tablename'
e.g.,
python3 -c "from yh_chart import yh_financials as yf;yf('AAPL');"
'''
debugTF = getKeyVal(optx, 'debugTF', False)
types = getKeyVal(optx, 'types', 'quoteSummary')
xmod = modules
jdM = []
if useDB is True:
jdTmp, dbM, err = find_mdb({"ticker": ticker},
clientM,
dbname=dbname,
tablename='yh_' + xmod)
if len(jdTmp) < 1:
return []
jdMod = jdTmp[0]
else:
try:
jdTmp = yh_quoteSummary(ticker, modules=xmod, **optx)
jdMod = jdTmp['quoteSummary']['result'][0][xmod]
jD = proc_summaryQuote(jdMod, ticker=ticker, xmod=xmod)
if jD:
jdM = [jD] if not isinstance(jD, list) else jD
except:
sys.stderr.write("**ERROR:{} not found via {}\n".format(
ticker, modules))
if saveDB is True and len(tablename) > 0 and len(jdM) > 0:
m, dbM, err = insert_mdb(jdM,
clientM=clientM,
tablename=tablename,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
ticker, jdM[-1], tablename))
sys.stderr.write("{}\n".format(
pd.DataFrame(jdM)[['ticker', 'module']].to_string(index=False)))
return jdM
def tk2infoM(ticker='',
tablename='yh_curr_quote',
colx='ticker',
dbname='ara',
clientM=None,
**optx):
from yh_chart import runOTF
from _alan_str import find_mdb
funcArg, zpk, deltaTolerance = getKeyVal(
optx, ["funcArg", "zpk", "deltaTolerance"],
['yh_financials', {'ticker'}, 1800])
modules = optx.pop('modules', '')
jobj = {colx: ticker}
df = []
try:
df = find_mdb(jobj,
clientM=clientM,
dbname=dbname,
tablename=tablename,
dfTF=True)[0]
if len(df) > 0 and isinstance(df, pd.DataFrame):
sys.stderr.write(" --{} found in {}:\n{}\n".format(
ticker, tablename, df)[:100] + "\n")
return df
else:
datax = runOTF(funcArg,
ticker,
deltaTolerance=deltaTolerance,
modules=modules,
dbname=dbname,
tablename=tablename,
zpk=zpk)
if len(datax) < 1:
sys.stderr.write(" --{} not found via {} @{}\n".format(
ticker, modules, 'tk2infoM'))
return []
df = pd.DataFrame(datax)
sys.stderr.write(
" --{} found in {}:\n{}\n".format(ticker, modules, df)[:100] +
"\n")
return df
except:
sys.stderr.write("**ERROR:{}: {}\n".format(ticker, str(e)))
return df
def create_ipoData(dbname='ara', tablesrc='nasdaq_ipos', tablename='ipoData'):
'''Create YTD 'ipoData' table based on 'nasdaq_ipos'
'''
from yh_chart import yh_quote_comparison
from ticker2label import ticker2label as t2l
df, clientM, _ = find_mdb(dbname=dbname, tablename=tablesrc, dfTF=True)
sys.stderr.write(" --DF:\n{}\n".format(df))
try:
df['price'] = [
float(x.replace('$', '').replace(',', '')) for x in df['price']
]
except Exception as e:
sys.stderr.write("**ERROR:{}\n".format(str(e)))
tkLst = [x for x in df['ticker'].values if "'" not in x]
df = merge_t2l(tkLst, df, quoteTF=False)
df = merge_yqc(tkLst, df)
df = df.astype(object).where(df.notnull(), None)
clientM[dbname][tablename].delete_many({})
clientM[dbname][tablename].insert_many(df.to_dict(orient='records'))
return df
def lastRunChk(objChk={},
tableChk='',
deltaTolerance=43200,
clientM=None,
dbname='ara',
**optx):
pbdtCurr = pd.datetime.now()
lastObj, clientM, _ = find_mdb(objChk,
clientM=clientM,
dbname=dbname,
tablename=tableChk,
limit=1)
if not lastObj:
pbdtMod = pbdtCurr
renewTF = True
else:
pbdtMod = lastObj[0]['pbdt']
renewTF = renewChk(pbdtCurr, pbdtMod, deltaTolerance)
if renewTF:
pbdtMod = pbdtCurr
objChk.update(pbdt=pbdtMod)
return renewTF, objChk, clientM
def create_ipoReport(dbname='ara',
tablesrc='ipoData',
topRow=50,
tablename='ipoReport',
saveDB=True):
'''Create YTD 'ipoReport' table based on 'nasdaq_ipos'
'''
from _alan_date import ymd_diff
dds, clientM, _ = find_mdb(dbname=dbname, tablename=tablesrc, dfTF=True)
dds = dds.astype(object).where(dds.notna(), None)
pbdate = int(dds['pbdate_y'].max())
dds['daysSinceIPO'] = [
ymd_diff(x, int(y)) if y is not None else ymd_diff(x, pbdate)
for x, y in dds[['pbdate_x', 'pbdate_y']].values
]
if 'fiftyTwoWeekLow' in dds:
dds['Range52Week'] = [
"{:.2f} - {:.2f}".format(x, y)
if y is not None and y < 9999 else ""
for x, y in dds[['fiftyTwoWeekLow', 'fiftyTwoWeekHigh']].values
]
dds['marketCapMM'] = dds['marketCap'] / 10**6
dds['currDate'] = pbdate
renCol = {'pbdate_x': 'ipoDate', 'price': 'ipoPrice', 'close': 'currPrice'}
dds.rename(columns=renCol, inplace=True)
dds['changeSinceIPO'] = dds['currPrice'] - dds['ipoPrice']
dds['changePercent'] = dds['currPrice'] / dds['ipoPrice'] - 1
colX = [
'ticker', 'ipoDate', 'ipoPrice', 'currPrice', 'currDate',
'changeSinceIPO', 'changePercent', 'Range52Week', 'fiftyDayAverage',
'daysSinceIPO', 'marketCapMM', 'trailingPE', 'shortName'
]
df = subDF(
dds.sort_values(by=['marketCap'], ascending=False).iloc[:topRow], colX)
df.reset_index(drop=True, inplace=True)
if saveDB is True:
clientM[dbname][tablename].delete_many({})
clientM[dbname][tablename].insert_many(df.to_dict(orient='records'))
return df
def headline_writer(opts={},**optx):
if not opts:
opts, args = parse_opt(sys.argv)
if optx:
opts.update(optx)
lang = opts['lang']
nlookback = int(opts['nlookback'])
#xqr="SELECT * FROM headline_hist ORDER BY pbdate DESC limit {}".format(nlookback)
#dj=sqlQuery(xqr).to_dict(orient='records')
dj,_,_=find_mdb({},tablename="headline_hist",dbname="ara",sortLst=['pbdate'],limit=nlookback)
ts="""
{%- set xcase = 2 if chgLevel2>200 or chgLevel2<-200 else 1 -%}
{%- set headline_file='daily_headline_{}_{}.j2'.format(xcase,lang) -%}
{%- include headline_file -%}
"""
for dd in dj:
try:
ret = create_headline(dd,ts=ts,**opts)
sys.stderr.write("{}\n".format(ret))
except Exception as e:
sys.stderr.write("{}:{}".format(dd['pbdate'],str(e)))
continue
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 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
