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