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