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 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 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 func2mdb(tkLst,
tablename='yh_spark_hist',
dbname='ara',
funcN='yh_hist_query',
zpk={'ticker', 'hhmm'},
debugTF=False,
**optx):
'''
Run 'funcN'() and save the result to mongoDB
'''
from _alan_str import write2mdb, insert_mdb
if funcN in globals():
funcArg = globals()[funcN]
else:
return {}
df = funcArg(tkLst, debugTF=debugTF, dbname=dbname, **optx)
try:
#dbM = conn2mgdb(dbname=dbname)
#ordered=False # to insert whatever is available
#if not tablename in dbM.collection_names():
# zsrt=[1]*len(zpk)
# dbM[tablename].create_index([(k,v) for k,v in zip(zpk,zsrt)],unique=True)
#ret=dbM[tablename].insert_many(df,ordered=ordered)
dbM = None
zpk = zpk
mobj, dbM, err_msg = upsert_mdb(df,
dbM,
dbname=dbname,
tablename=tablename,
zpk=zpk,
insertOnly=True)
except Exception as e:
sys.stderr.write("**ERROR: {}:{}\n".format("func2mdb()", str(e)))
return df
def run_fintel(fund='',
dats='2020-06-30',
sn=0,
tn=1,
zpk=['fund', 'ticker', 'funddate'],
saveDB=False,
debugTF=False,
dbname='ara',
tablename='topic_fintel_hist'):
if len(fund) < 1:
return {}
if debugTF:
sys.stderr.write("===fund:{},dats:{},sn:{},tn:{}\n".format(
fund, dats, sn, tn))
#dx = get_fintel(fund=fund,dats=dats,sn=sn,tn=tn,debugTF=debugTF)
funddate = int(dats.replace('-', ''))
dx = read_fintel(fund=fund, funddate=funddate, debugTF=debugTF)
if len(dx) < 1:
return {}
if debugTF:
sys.stderr.write("===dx:\n{}\n".format(dx))
df = process_fintel(fund, dx, debugTF=debugTF)
df['funddate'] = int(dats.replace('-', ''))
df2pickle(df, prefix=fund)
if len(df) < 1:
return {}
if debugTF:
sys.stderr.write("===df:\n{}\n".format(df.head()))
if saveDB:
ret = upsert_mdb(df, zpk=zpk, dbname=dbname, tablename=tablename)
return df
def earnings_morningstar(xdate=None,
xtype='earnings',
uri=None,
dbname='ara',
tablename='earnings_morningstar',
debugTF=False,
**optx):
""" Usage of:
python -c "from macro_event_yh import earnings_morningstar as em;em(xdate=20190430)"
"""
xdate = get_datestr(xdate)
#http://www.morningstar.com/earnings/Handler/GetEarningsCalendar.ashx?rptDate=2019-05-01&r=68265
if uri is None:
urx = "http://www.morningstar.com/earnings/Handler/GetEarningsCalendar.ashx?rptDate={}"
uri = urx.format(xdate)
ret = pd.read_html(uri)
df = ret[1]
v = [
str(x.split()[-1].encode('ascii', 'ignore'))
for x in df[u'Company Name']
]
df['ticker'] = v
df['pbdate'] = int(xdate.replace('-', ''))
zpk = {'ticker', 'pbdate'} if xtype == 'earnings' else {'event', 'pbdate'}
clientM = None
print(v)
mobj, clientM, err_msg = upsert_mdb(df,
clientM,
dbname=dbname,
tablename=tablename,
zpk=zpk)
return df
def qS_keyStatistics(tkLst=[],
tablename='qS_keyStatistics',
saveDB=False,
debugTF=True,
**optx):
''' pull defaultKeyStatistics from yahoo and save to qS_keyStatistics
e.g.,
python3 -c "from yh_chart import qS_keyStatistics as yks;yks(['AAPL']);"
'''
if tkLst is None or len(tkLst) < 1:
tkLst = list(pull_act_tickers()['ticker'])
dbM = None
pbdt = datetime.datetime.now()
jdM = yh_batchTypes(tkLst,
types='quoteSummary',
modules='defaultKeyStatistics',
debugTF=debugTF)
for jx in jdM:
jx['pbdt'] = pbdt
if saveDB is True:
zpk = optx.pop('zpk', {"ticker"})
m, dbM, err = upsert_mdb(jdM,
clientM=dbM,
tablename=tablename,
zpk=zpk,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkLst, jdM[-1], tablename))
return jdM
def run_macro_event_yh(uri=None,
xdate=None,
saveDB=True,
tablename=None,
wmode='replace',
xtype='earnings',
clientM=None,
hostname='localhost',
dbname=None,
ticker=None,
debugTF=False,
**optx):
""" find macro event from yahoo
"""
pgDB = None
if ticker is not None:
urx = 'https://finance.yahoo.com/calendar/earnings?symbol={}'
uri = urx.format(ticker)
funcName = 'search_earnings_by_symbol'
tablename = "{}_hist_yh".format(xtype)
else:
funcName = "search_{}".format(xtype)
tablename = "{}_yh".format(xtype) if not tablename else tablename
if funcName in globals():
searchFunc = globals()[funcName]
dfyc = searchFunc(xdate, uri=uri, xtype=xtype)
if len(dfyc) < 1:
return {}
else:
return {}
if any([saveDB is False]):
sys.stderr.write('{}'.format(dfyc.to_csv(index=False, sep='|')) + "\n")
else:
zpk = {'ticker', 'pbdate'
} if xtype == 'earnings' else {'event', 'pbdate'}
mobj, clientM, err_msg = upsert_mdb(dfyc,
clientM,
dbname=dbname,
tablename=tablename,
zpk=zpk)
sys.stderr.write("{}\n...\n{}\n saved to {}:{}".format(
dfyc.head(1), dfyc.tail(1), clientM, tablename) + "\n")
if pgDB is not None:
dfyc.to_sql(tablename,
pgDB,
index=False,
schema='public',
if_exists=wmode)
return dfyc
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 saveBriefing(_pn_,tablename="mkt_briefing_details",zpk=['category','templateName','rpt_time','lang']):
fx = lambda m,y: [m[x][y] for x in m['tmplLst'] if y in m[x] and m[x][y] is not None]
try:
if 'headTitle' not in _pn_:
_pn_ = set_ht(_pn_)
if 'comment' not in _pn_:
ret = "\n".join(fx(_pn_,'comment'))
_pn_.update(comment=ret)
if 'mp3comment' not in _pn_:
m3ret = "\n".join(fx(_pn_,'mp3comment'))
_pn_.update(m3comment=m3ret)
ret,_,emsg = upsert_mdb([_pn_],zpk=zpk,dbname='ara',tablename=tablename,ordered=False)
sys.stderr.write("==errmsg:SAVE TO {}:{} {}\n".format(tablename,_pn_['pbdt'],str(emsg)))
except Exception as e:
sys.stderr.write("**ERROR:INSERT INTO {}: {} of _pn_:\n{}\n".format(tablename,str(e),_pn_))
return _pn_
def batch_fintel_majorplayer(fundLst=[],
dbname='ara',
tablename='topic_fintel_hist',
themename='topic_theme_majorplayer',
saveDB=False,
debugTF=False):
''' Process ticker list + daily quote and ytd performance
'''
if len(fundLst) < 1:
fundLst = [
'berkshire-hathaway', 'blackrock', 'bridgewater-associates-lp',
'goldman-sachs-group', 'renaissance-technologies-llc'
]
zpk = ["fund", "ticker", "funddate"]
colX = [
'fund', 'funddate', 'pbdate', 'pbdt', 'ticker', 'shortName', 'change',
'changePercent', 'sector_cn', 'company_cn', 'marketCap', 'close',
'peRatio', 'CurrentShares', 'percentPos', 'SharesChangePercent',
'fiftyTwoWeekRange'
]
clientM = None
for fn in fundLst:
sys.stderr.write("==GET fund:{}\n".format(fn))
jobj = {"fund": fn}
try:
df, clientM, err, funddate = get_fund_quote(jobj,
clientM=clientM,
dbname=dbname,
tablename=tablename)
df = subDF(df, colX)
if saveDB:
sys.stderr.write("==SAVING {} of\n{}\n".format(
fn, df.iloc[:10]))
dg = df.to_dict(orient='records')
ret = upsert_mdb(dg,
clientM=clientM,
dbname=dbname,
tablename=themename,
zpk=zpk)
except Exception as e:
sys.stderr.write("**ERROR:{}:{}\n".format(fn, str(e)))
df = {}
return df
def load_rssCompany(ticker='', **optx):
if len(ticker) < 1:
return []
urx = 'https://feeds.finance.yahoo.com/rss/2.0/headline?s={ticker}®ion=US&lang=en-US'
url = urx.format(ticker=ticker)
sys.stderr.write("==URL:{}\n".format(url))
feed = feedparser.parse(url)
rssLst = feed.entries
if not isinstance(rssLst, list):
return []
for j in range(len(rssLst)):
rssLst[j]['pubDate'] = pd.Timestamp(rssLst[j]['published'])
rssLst[j]['ticker'] = ticker
kyLst = list(rssLst[j])
for ky in kyLst:
if isinstance(rssLst[j][ky], dict):
rssLst[j].pop(ky, None)
sys.stderr.write(" --Save To DB:{}\n".format(optx))
ret, clientM, err = upsert_mdb(rssLst, **optx)
return ret
def pn2mp4(_pn_={},zpk=[],debugTF=False,**optx):
'''
convert _pn_ that contail svg, comment, mp3comment into relevant mp3 and mp4 files
and save file locations and save to MDB table: 'mkt_briefing_media' or 'daily_single_stock_media'
'''
if 'tmplLst' not in _pn_:
return {}
tmplLst = _pn_['tmplLst']
title = _pn_['title']
dpn=subDict(_pn_,zpk+['title','pbdt','headTitle','intraday_headline','daily_headline','comment','mp3comment'])
videoLst=[]
j=0
for tname in tmplLst:
if tname not in _pn_:
sys.stderr.write("===NotFound: @ {}\n".format(tname))
continue
sys.stderr.write("===Running: {}\n{}\n".format(tname,_pn_[tname]))
try:
dx = _pn_[tname]
if 'chartpath' not in dx or dx['chartpath'] is None:
continue
chartpath= dx['chartpath']
xtmp=re.sub("(.svg)?","",chartpath)
outdir,mpname=os.path.dirname(xtmp),os.path.basename(xtmp)
if len(mpname)<1:
sys.stderr.write("**WARNING: @ {}\t{}\n".format(tname,"Filename not exists"))
continue
dx.pop('title',None)
vk = [k for k in dx]
for k in vk:
v = dx[k]
if isinstance(v,(dict,tuple,list,pd.DataFrame)):
dx.pop(k,None)
#dx.update(k=None)
txtChart2audioSrtVideo(tmplname=tname,mpname=mpname,tmplobj=dx,debugTF=debugTF,**optx)
dpn.update({tname:dx})
videoLst.append(dx['videoPath'])
sys.stderr.write("\n==={}:{} successfully created!\n".format(tname,dx['videoPath']))
except Exception as e:
sys.stderr.write("**ERROR: @ {}\n\t{}\n".format(tname,str(e)))
continue
j=j+1
if j<1:
return {}
sys.stderr.write("===Total:{}: {}\n".format(j,videoLst))
try:
xpoch = mpname.split("_")[-1]
if title=='daily_single_stock':
ticker = getKeyVal(_pn_,'ticker',None)
videoPath='{}_{}_{}_ALL.mp4'.format(title,ticker,xpoch)
else:
videoPath='{}_{}_ALL.mp4'.format(title,xpoch)
run_concat(videoLst=videoLst,outdir=outdir,videoPath=videoPath)
#dpn.update(comment=rptTxt)
dpn.update(videoPath=videoPath)
sys.stderr.write("===Combine: {} To {}\n".format(videoLst,videoPath))
if len(glob(outdir+"/"+videoPath))>0:
sys.stderr.write("===videoLst: {}\n".format(videoLst))
sys.stderr.write("===videopath: {} successfully created!\n".format(videoPath))
else:
sys.stderr.write("**ERROR: {} not created!\n".format(videoPath))
# save to MDB 'dbname'::'tablename'
dbname=optx.pop('dbname','ara')
tablename=optx.pop('tablename','')
if title[-9:]=='_briefing':
tablename='mkt_briefing_media'
elif title=='daily_single_stock':
tablename=title+'_media'
if len(tablename)>5:
ret,_,emsg = upsert_mdb([dpn],zpk=zpk,dbname=dbname,tablename=tablename,ordered=False)
sys.stderr.write("==errmsg:SAVE TO {} @ {}, STATUS:{}\n".format(tablename,dpn['pbdt'],str(emsg)))
sys.stderr.write("==SUCCESS videoLst:{}, mp4:{}\n".format(videoLst,mpname))
except Exception as e:
sys.stderr.write("**ERROR: @ {}:{}\n\t{}\n".format(videoLst,mpname,str(e)))
return dpn
def yh_batchSpark(tkLst=[],
filter='*',
types='spark',
nchunk=100,
saveDB=True,
dbname='ara',
tablename='yh_spark_hist',
zpk={'ticker', 'epochs'},
t2='yh_quote_curr',
t2zpk={'ticker'},
isEoD=False,
**optx):
'''
pull data from types=[spark|quote] then save to mgDB 'dbname':: 'tablename' and 't2' respectively
Note,
if isEoD=True: quote data save to both mgDB 'dbname':: 'tablename' and 't2'
if tablename or t2 ='' , nothing will be saved in 'tablename' or 't2'
quote data will also be saved to pgDB 'dbname'::'t2' if isEoD=True and types='quote'
'''
debugTF = getKeyVal(optx, 'debugTF', False)
if debugTF:
sys.stderr.write("===LOCALS: {}\noptx: {}\n".format(locals(), optx))
dbM = conn2mgdb(dbname=dbname)
if tkLst is None or len(tkLst) < 1:
tkLst = list(pull_act_tickers()['ticker'])
elif isinstance(tkLst, str):
tkLst = [tkLst]
chunkLst = list2chunk(tkLst, nchunk)
jdN = []
colX = [
'ticker', 'open', 'high', 'low', 'close', 'xclose', 'volume', 'pchg',
'change', 'pbdate', 'epochs', 'pbdt', 'hhmm'
]
for j, tkM in enumerate(chunkLst):
jdTmp = yh_batchRaw(tkM, types=types, **optx)
jdQC = {}
if types in ['spark', 'chart']:
jdLst = jdTmp[types]['result']
if types == 'chart':
jdM = chart2df(jdLst, **optx)
else:
jdM = batchSpark_sparkConv(jdLst, **optx)
if len(jdM) < 1:
continue
if saveDB is True:
m, dbM, err = insert_mdb(jdM,
clientM=dbM,
tablename=tablename,
**optx)
if debugTF:
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdM[-1], tablename))
jdN.extend(jdM)
else: # for case of types.lower()=='quote'
jdLst = jdTmp['quoteResponse']['result']
jdQC = batchSpark_quoteConv(jdLst, **optx)
if len(jdQC) < 1:
continue
jdM = subDict(jdQC, colX)
if saveDB is True and len(jdQC) > 0:
if len(t2) > 0:
qc, dbM, erq = upsert_mdb(jdQC,
clientM=dbM,
tablename=t2,
zpk=t2zpk,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdQC[-1], t2))
if isEoD is True:
m, dbM, err = insert_mdb(jdM,
clientM=dbM,
tablename=tablename,
zpk=zpk,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdM[-1], tablename))
jdN.extend(jdQC)
if debugTF:
sys.stderr.write("=== tkM:{}[{}/{}], last:\n{}\n".format(
tkM, len(jdM), len(jdN), jdN[-1]))
if saveDB is True and len(t2) > 0 and len(jdN) > 0 and types.lower(
) == 'quote' and isEoD is True:
try:
df = pd.DataFrame(jdN)
df = df.drop(['_id'], axis=1)
if debugTF:
sys.stderr.write(
"=== Save to pgDB::{} of {} rows(1st-last)\n{}\n{}\n".
format(t2, len(df), df.iloc[:1], df.iloc[-1:]))
from _alan_calc import save2pgdb
save2pgdb(df, dbname, tablename=t2)
except Exception as e:
sys.stderr.write("**ERROR: {}:{}\n".format("save2pgdb", str(e)))
return jdN
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 runOTF(funcArg,
ticker='',
tableChk='',
zpkChk=["ticker"],
deltaTolerance=43200,
**optx):
'''
To ticker='AAPL'
where real-time data is only grab based on 'deltaTolerance' in seconds
current setup is half-days
'''
if isinstance(ticker, list):
tkLst = ticker
dLst = []
for tkX in tkLst:
sys.stderr.write("==BATCH Running:{} on {}\n".format(
ticker, funcArg))
dd = runOTF(funcArg,
tkX,
tableChk,
zpkChk=zpkChk,
deltaTolerance=deltaTolerance,
**optx)
if isinstance(dd, list):
dLst.extend(dd)
else:
dLst.extend([dd])
return dLst
if isinstance(funcArg, str):
if funcArg in globals() and hasattr(globals()[funcArg], '__call__'):
funcArg = globals()[funcArg]
else:
return []
sys.stderr.write("==START Running:{} on {}\n".format(ticker, funcArg))
dbname = getKeyVal(optx, 'dbname', 'ara')
optx.update({'dbname': dbname})
tablename = getKeyVal(optx, 'tablename', '')
if not all([tablename, ticker]):
return []
if 'modules' in optx:
objChk = dict(ticker=ticker, module=optx['modules'])
else:
objChk = dict(ticker=ticker)
if not tableChk:
tableChk = tablename + '_chk'
renewTF, objChk, clientM = lastRunChk(objChk=objChk,
tableChk=tableChk,
deltaTolerance=deltaTolerance,
**optx)
if renewTF:
sys.stderr.write(
"==Data outdated or never run, Running:{}\n".format(funcArg))
retObj = funcArg(ticker=ticker, **optx)
if len(retObj) < 1:
return []
xChk = {ky: val for ky, val in retObj[0].items() if ky in zpkChk}
objChk.update(xChk)
retObj, clientM, errChk = upsert_mdb(retObj, **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)
retObj, clientM, errMsg = find_mdb(objChk, clientM=clientM, **optx)
return retObj
def madmoney_screener(hostname='localhost',
dbname='ara',
tablename='madmoney_hist',
airdate='180',
pricehigh=1000,
pricelow=0,
sortby='airdate',
showrows=2000,
rawTF=False,
saveDB=True,
debugTF=False,
**kwargs):
'''
Update MDB Table: madmoney_hist
get madmoney list in daily basis
ref table: madmoney_hist
ref site: https://madmoney.thestreet.com/screener/index.cfm?showview=stocks&showrows=500
'''
sys.stderr.write('{}\n'.format(locals()))
urx = 'https://madmoney.thestreet.com/screener/index.cfm?showview=stocks&showrows={showrows}&airdate={airdate}'
CallD = {
'5': 'buy',
'4': 'positive',
'3': 'hold',
'2': 'negative',
'1': 'sell'
}
SegmentD = dict(F='Featured Stock',
D='Discussed Stock ',
C='Callers Stock',
I='Guest Interview',
L='Lighting Round',
M='Mail Bag',
G='Game Plan',
S='Sudden Death')
url = urx.format(showrows=showrows, airdate=airdate)
jd = dict(symbol='',
airdate=airdate,
called='%',
industry='%',
sector='%',
segment='%',
pricelow=pricelow,
pricehigh=pricehigh,
sortby=sortby)
sys.stderr.write('URL:\n{}\nPOST:{}\n'.format(url, jd))
try:
#res = requests.get(url,timeout=10)
res = requests.post(url, data=json.dumps(jd), timeout=10)
if res.status_code != 200:
sys.stderr.write('**HTTP: {} @ {}\n'.format(res.status_code, url))
return {}
xstr = res.content
df = pd.read_html(xstr,
attrs={'id': 'stockTable'},
index_col=False,
header=0)[0]
if len(df) < 1 or pd.isnull(df.loc[0, 'Company']):
sys.stderr.write('**{} @ {}\n'.format("No sotcks were found...",
url))
return {}
s = BeautifulSoup(xstr, 'lxml')
trLst = s.find_all('table')[0].find_all('tr')
dd = []
for j, rwx in enumerate(trLst):
if j < 1:
tagX = 'th'
tdLst = rwx.find_all(tagX)
vHdr = [x.text for x in tdLst]
continue
else:
tagX = 'td'
tdLst = rwx.find_all(tagX)
vLst = []
if len(tdLst) < len(vHdr):
continue
for k, xTd in enumerate(tdLst):
if isinstance(xTd.next,
bs4Element.Tag) and xTd.next.has_attr('alt'):
xv = xTd.next['alt']
elif isinstance(xTd.next,
bs4Element.Tag) and xTd.next.has_attr('href'):
xv = parse_qs(xTd.next['href'])['symbol'][0]
else:
xv = xTd.text
vLst.append(xv)
dx = dict(zip(vHdr, vLst))
dd.append(dx)
df = pd.DataFrame(dd)
except Exception as e:
sys.stderr.write('**ERROR: {} @{}\n'.format(str(e),
'madmoney_screener()'))
return {}
if rawTF:
return df
try:
df['CallDscript'] = [CallD[x] for x in df['Call']]
df['SegmentDscript'] = [SegmentD[x] for x in df['Segment']]
df['Price'] = df['Price'].apply(lambda x: float(x.strip('$')))
mmdd = [int("".join(x.split('/'))) for x in df['Date']]
tdate = datetime.datetime.today()
cmmdd = int(tdate.strftime("%m%d"))
cYr = tdate.year
xYr = tdate.year - 1
pbdate = [(cYr if cmmdd > x else xYr) * 10000 + x for x in mmdd]
df['pbdate'] = pbdate
df['Portfolio'] = df['Portfolio'].apply(lambda x: x.strip())
df['ticker'] = [s.replace('.', '-') for s in df['Portfolio']]
df = add_sector_industry(df)
except Exception as e:
sys.stderr.write('**ERROR: {} @ PULLING {}\n'.format(str(e), url))
return {}
if saveDB:
mobj, _, msg = upsert_mdb(df,
clientM=None,
dbname=dbname,
tablename=tablename,
zpk={'ticker', 'pbdate'})
sys.stderr.write('==Save to: MDB:{}: {}\n'.format(tablename, msg))
return df
def nasdaq_ipos(hostname='localhost',
dbname='ara',
tablename='nasdaq_ipos',
tab='filing',
month='',
debugTF=False,
**kwargs):
'''
Update MDB Table: nasdaq_ipos
get recent IPOs
ref table: nasdaq_ipos
ref site: https://api.nasdaq.com/api/ipo/calendar?date=2019-10
'''
if len(month) < 1:
month = datetime.datetime.now().strftime('%Y-%m')
urx = 'https://api.nasdaq.com/api/ipo/calendar?date={month}'
url = urx.format(month=month)
sys.stderr.write(' --URL:{}\n'.format(url))
headers = {
'Content-Type':
'application/json',
'Accept':
'application/json',
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36'
}
try:
#ret =requests.get(url,timeout=5)
ret = requests.Session().get(url, headers=headers)
jsx = ret.json()
actions = getKeyVal(kwargs, key='actions', val='priced')
data = jsx['data'][actions]['rows']
if not data:
actions = 'filed'
data = jsx['data'][actions]['rows']
if not data:
return {}
colX = jsx['data'][actions]['headers']
df = pd.DataFrame(data)
df = renameDict(df, colX)
if debugTF:
sys.stderr.write(' --DATA:\n{}\n'.format(data))
sys.stderr.write(' --df:\n{}\n'.format(df))
if "Exchange/ Market" in df:
df = df.rename(columns={"Exchange/ Market": "Market"})
if debugTF:
sys.stderr.write('COLUMNS:{}\n'.format(df.columns))
df.dropna(subset=['Symbol'], inplace=True)
if len(df) < 1:
return {}
df['ticker'] = [s.replace('.', '-') for s in df['Symbol']]
if 'Offer Amount' in df:
df['offer'] = [
float(s.replace('$', '').replace(',', ''))
for s in df['Offer Amount']
]
df = df.drop(['Offer Amount'], axis=1)
if 'Date' in df:
df['pbdate'] = [
int(
datetime.datetime.strptime(s,
'%m/%d/%Y').strftime('%Y%m%d'))
for s in df['Date']
]
elif 'Date Filed' in df:
df['pbdate'] = [
int(
datetime.datetime.strptime(s,
'%m/%d/%Y').strftime('%Y%m%d'))
for s in df['Date Filed']
]
elif 'Date Priced' in df:
df['pbdate'] = [
int(
datetime.datetime.strptime(s,
'%m/%d/%Y').strftime('%Y%m%d'))
for s in df['Date Priced']
]
except Exception as e:
sys.stderr.write('**ERROR: nasdaq_ipos:{}\n'.format(str(e)))
return {}
df.columns = [x.replace(' ', '').lower() for x in df.columns]
df = add_sector_industry(df)
mobj, _, _ = upsert_mdb(df,
clientM=None,
dbname=dbname,
tablename=tablename,
zpk={'ticker', 'pbdate', 'actions'})
return df