def deact_mapping_ticker(lkbk=-14, mxcap=500000000, debugTF=False, saveDB=True, **optx): ''' De-activate tickers with invalid quotes within last 14 days or marketCap < 500MM ''' # de-active stale tickers sys.stderr.write("===STARTING de-active stale tickers\n") pgDB = conn2pgdb(dbname='ara') tkCIK = sqlQuery( "SELECT * FROM mapping_ticker_cik WHERE act_code=1 and ticker not similar to '%%(^|=)%%'" ) tLst = tkCIK['ticker'].values dt7d = int(next_date(days=lkbk, dformat='%Y%m%d', dtTF=False)) df = yqc(tLst, tablename=None, screenerTF=False, dfTF=True) if len(df) < 1: sys.stderr.write("No deactivate list\n") return (0) yqr = 'pbdate<{}|marketCap<{}'.format(dt7d, mxcap) dg = df.query(yqr) newLst = dg['ticker'].values if len(newLst) < 1: sys.stderr.write("No deactivate list\n") return (0) else: sys.stderr.write("New deactivate List:{}\n".format(newLst)) if saveDB: xqTmp = "UPDATE mapping_ticker_cik set act_code=0 where ticker in {}" xqr = xqTmp.format(tuple(newLst)) pgDB.execute(xqr) return newLst
def get_ts_cmt(sqx, ts_alt, pgDB=None, dbname=None): try: if pgDB is None: pgDB = conn2pgdb(dbname=dbname) ts_cmt = pd.read_sql(sqx, pgDB).iloc[0][0] except Exception, e: print >> sys.stderr, "** ERROR: get_ts_cmt(),{}", format(str(e)) ts_cmt = ts_alt
def mainTst(): nss = 4 # seasonality period nfcs = 4 # quarterly forecast pngname = None saveDB = True pgDb = conn2pgdb(dbname='ara') sqx = "SELECT sector AS sector FROM spdr_sector WHERE sector NOT LIKE '%%ETF' ORDER BY sector" #sqx = "SELECT sector_alias AS sector FROM spdr_sector WHERE sector NOT LIKE '%%ETF'" secLst = [str(x) for x in pd.read_sql(sqx, pgDb).iloc[:, 0]] secLst += ['ALL'] #secLst = secLst [6:7] # for special testing dH = pd.DataFrame() dFcs = pd.DataFrame() dPrm = pd.DataFrame() rmode = 'replace' for j, sector in enumerate(secLst): try: dprm, dh, dfcs = sector_eps_price(sector, nss=nss, nfcs=nfcs, pgDb=pgDb, pngname=None) """ get [dh] from database sqx = "select * from fa_eps_price where ticker in (select ticker from iex_company_hist where sector={!r})".format(sector) dH = pd.read_sql(sqx,pgDb) dprm = wrap_regression(dH,npar=2,sector=sector,pngname="hello") """ print(j, sector, file=sys.stderr) print(dprm, file=sys.stderr) print(dfcs, file=sys.stderr) print(dh, file=sys.stderr) if saveDB is True: dh.to_sql('fa_eps_price', pgDb, schema='public', index=False, if_exists=rmode) dfcs.to_sql('fa_eps_fcs', pgDb, schema='public', index=False, if_exists=rmode) dprm.to_sql('fa_eps_param', pgDb, schema='public', index=False, if_exists=rmode) rmode = 'append' #dH = pd.concat([dH,dh]) #dFcs = pd.concat([dFcs,dfcs]) #dPrm = pd.concat([dPrm,dprm]) except: dprm, dh, dfcs = ([], [], []) continue #return dPrm, dH, dFcs return dprm, dh, dfcs
def get_alert_list(dbname='ara.tw', numLimit=10): fname = 'ohlc_alert_list.sql.tmp' pgDB = conn2pgdb(dbname=dbname) xqTmp = open(fname).read() cdate = pd.read_sql("SELECT * FROM ara_uptodate", pgDB).pbdate[0] x1dYmd = ymd_delta(cdate, 1) x1wYmd = ymd_delta(cdate, 7) xqr = xqTmp.format(**locals()) df = pd.read_sql(xqr, pgDB) return (df, pgDB)
def get_ts_cmt(sqx, ts_alt, pgDB=None, dbname=None, debugTF=False): try: if pgDB is None: pgDB = conn2pgdb(dbname=dbname) ts_cmt = pd.read_sql(sqx, pgDB).iloc[0][0] except Exception as e: print("** ERROR: get_ts_cmt(),{}", format(str(e)), file=sys.stderr) ts_cmt = ts_alt if debugTF is True: print("TS SQL:\n{}\nTS TEMPLATE:\n{}".format(sqx, str(ts_cmt)), file=sys.stderr) return ts_cmt
def get_alert_list(dbname='ara.tw', numLimit=10): fname = 'ohlc_alert_list.sql.tmp' pgDB = conn2pgdb(dbname=dbname) xqTmp = open(fname).read() cdate = pd.read_sql("SELECT * FROM ara_uptodate", pgDB).pbdate[0] x1dYmd = pd.read_sql( "SELECT pbdate FROM ohlc_daily_comment_cn GROUP BY pbdate ORDER BY pbdate DESC limit 2", pgDB).iloc[1][0] #x1dYmd=ymd_delta(cdate,1) x1wYmd = ymd_delta(cdate, 7) xqr = xqTmp.format(**locals()) df = pd.read_sql(xqr, pgDB) print >> sys.stderr, df return (df, pgDB)
def get_macro_fcs(tkLst=[], pgDB=None, dbname='ara', lang="en"): ext = "" if lang == "en" else "_" + lang pgDB = conn2pgdb(dbname=dbname) tkStr = "('{}')".format("','".join(tkLst)) sqr = """SELECT p.*,m.freq as mfreq,m.label{} as label, 'Market Index' as sector, m.category,m.category_seq FROM (SELECT * FROM macro_fcs where ticker in {} ) as p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq""".format( ext, tkStr) tmpx = pd.read_sql(sqr, con=pgDB) datax = tmpx.query( "(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy() datax['zx'] = (datax.prc_chg / datax.rrate_sigma) ds = datax.query("freq=='W'") dm = [ds.query("ticker=={!r}".format(x)).iloc[0].to_dict() for x in tkLst] return (dm, datax, pgDB)
def get_ohlc_fcs(tkLst=[], pgDB=None, dbname='ara', lang="en"): if pgDB is None: pgDB = conn2pgdb(dbname=dbname) xqr = "SELECT ticker FROM ara_ranking_list WHERE category='AI' AND subtype='Index' ORDER BY ranking limit 1" tkLst = pd.read_sql(xqr, pgDB).iloc[0].values ext = "" if lang == "en" else "_" + lang tkStr = "('{}')".format("','".join(tkLst)) sqr = """SELECT p.*,'D' as mfreq, m.company{} as label ,m.sector ,'stock' as category, 5::int as category_seq FROM (SELECT * FROM ohlc_fcs where ticker in {} ) as p LEFT JOIN mapping_ticker_cik m ON p.ticker=m.ticker""".format(ext, tkStr) datax = pd.read_sql(sqr, con=pgDB) print >> sys.stderr, sqr, dbname datax['zx'] = (datax.prc_chg / datax.rrate_sigma)[:] ds = datax.query("freq=='W'") dm = ds.to_dict(orient='record') return (dm, datax, pgDB)
def loop_hourly_mkt(tkLst=[], lang='cn', dbname='ara', cutoff_hm='1600', rpt_date='20190731', region='us', start=20190731, archiveTest=False, dirname='templates/', outdir='US/mp3_hourly', **optx): ''' run a list of tkLst ''' region = region.lower() dbname = '{}{}'.format(dbname, '.' + region if region == 'tw' else '') pgDB = conn2pgdb(dbname=dbname) if not tkLst and region == 'tw': tkLst = get_stocks_tw(pgDB) elif not tkLst: tkLst = get_stocks_us(pgDB) xqTmp = "select ticker,company{} as label from mapping_ticker_cik where ticker in ('{}')" xqr = xqTmp.format('_cn' if lang == 'cn' else '', "','".join(tkLst)) tkLbLst = sqlQuery(xqr, pgDB).to_dict(orient='records') for v in tkLbLst: try: ticker, title = v['ticker'], v['label'] run_hourly_mkt(ticker, title, start=start, pgDB=pgDB, target_hm=cutoff_hm, region=region, dirname=dirname, outdir=outdir, archiveTest=archiveTest, rpt_date=rpt_date) except Exception as e: sys.stderr.write("**ERROR:{ticker}:{label}:{err}\n".format( err=str(e), **v)) continue #from mongo2psql import mongo2psql #mongo2psql('hourly_report',dbname,engine=pgDB) fp = open(outdir + '/hourly_mp3_uptodate.txt', 'w') fp.write("{}_{}\n".format(start, cutoff_hm)) fp.close() return 0
def get_macro_list(currdate=None, pgDB=None, dbname='ara', lang="en", debugTF=False): """ 2. get globalmacro index return dx as a list of largest movement of each category """ if pgDB is None: pgDB = conn2pgdb(dbname=dbname) if currdate is None: try: currdate = int( pd.read_sql( "SELECT pbdate FROM macro_fcs where ticker='^GSPC'", pgDB).iloc[0, 0]) except Exception as e: print("**ERROR: {}{}".format( str(e), "@ get_macro_list(): [currdate] not defined!"), file=sys.stderr) return ([], [], pgDB) ext = "" if lang == "en" else "_" + lang sqr = "SELECT p.*,m.freq as mfreq,m.label{0} as label, m.category,m.category_seq FROM macro_fcs p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq".format( ext) tmpx = pd.read_sql(sqr, con=pgDB) datax = tmpx.query( "(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy() datax['zx'] = (datax.prc_chg / datax.rrate_sigma) if debugTF is True: print("macro_list SQL:\n{}\n".format(sqr), file=sys.stderr) print("macro_list DATA:\n{}".format(datax.to_csv(index=False, sep='|')), file=sys.stderr) dx = [] for j in (datax.category_seq.unique()): dy = macro_list_x(j, datax, currdate=currdate, pgDB=pgDB, debugTF=debugTF) if dy is None or len(dy) < 1: continue dx.append(dy) return (dx, datax, pgDB)
def get_mkt_list(dbname='ara', lang="en", currdate=None): ext = "" if lang == "en" else "_" + lang pgDB = conn2pgdb(dbname=dbname) sqr = "SELECT p.*,m.freq as mfreq,m.label{0} as label, m.category,m.category_seq FROM macro_fcs p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq".format( ext) tmpx = pd.read_sql(sqr, con=pgDB) datax = tmpx.query( "(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy() datax['zx'] = (datax.prc_chg / datax.rrate_sigma) print >> sys.stderr, datax.head(2) print >> sys.stderr, datax.tail(3) dx = [] for j in (datax.category_seq.unique()): #if j>3: continue dy = mkt_list_x(j, datax, currdate=currdate, pgDB=pgDB) if dy is None or len(dy) < 1: continue dx.append(dy) #print >> sys.stderr, dx return (dx, datax, pgDB)
def get_macro_fcs(tkLst=None, pgDB=None, dbname='ara', lang="en", debugTF=False): """ 1a, get selected market indices from [macro_hist_fred] table index section """ if pgDB is None: pgDB = conn2pgdb(dbname=dbname) if tkLst is None: tkLst = ["^DJI", "^SOX", "^IXIC", "^GSPC"] ext = "" if lang == "en" else "_" + lang tkStr = "('{}')".format("','".join(tkLst)) xqTmp = """SELECT p.*,m.freq as mfreq,m.label{} as label, 'Market Index' as sector, m.category,m.category_seq FROM (SELECT * FROM macro_fcs where ticker in {} ) as p LEFT JOIN mapping_series_label m ON p.ticker=m.series ORDER BY m.category_seq""" sqr = xqTmp.format(ext, tkStr) tmpx = pd.read_sql(sqr, con=pgDB) datax = tmpx.query( "(freq=='W' & mfreq=='D') | (freq=='D' & mfreq!='D')").copy() datax['zx'] = (datax.prc_chg / datax.rrate_sigma) ds = datax.query("freq=='W'") dm = [ds.query("ticker=={!r}".format(x)).iloc[0].to_dict() for x in tkLst] return (dm, datax, pgDB)
reload(sys) sys.setdefaultencoding('utf8') def get_tkLst(tkLst=[], xqr="select etfname from spdr_sector", pgDB=None): xLst = [str(x) for x in sqlQuery(xqr, pgDB).iloc[:, 0].values] if len(tkLst) < 1: return xLst else: return np.append(tkLst, xLst) start = int(sys.argv[1]) if len(sys.argv) > 1 else int( datetime.datetime.now().strftime('%Y%m%d')) lang = sys.argv[2] if len(sys.argv) > 2 else 'cn' pgDB = conn2pgdb(dbname='ara.tw') #tkLst = ['1101','1102','1216','1301','1303','1326','1402','2002','2105','2301','2303','2308','2317','2327','2330','2354','2357','2382','2395','2408','2409','2412','2454','2474','2492','2633','2801','2823','2880','2881','2882','2883','2884','2885','2886','2887','2890','2891','2892','2912','3008','3045','3481','3711','4904','4938','5871','5880','6505','9904'] tkLst = [ '000', '001', '050', '1101', '1102', '1216', '1301', '1303', '1326', '1402', '2002', '2105', '2301', '2303', '2308', '2317', '2327', '2330', '2354', '2357', '2382', '2395', '2408', '2409', '2412', '2454', '2474', '2492', '2633', '2801', '2823', '2880', '2881', '2882', '2883', '2884', '2885', '2886', '2887', '2890', '2891', '2892', '2912', '3008', '3045', '3481', '3711', '4904', '4938', '5871', '5880', '6505', '9904' ] xqr = "select ticker,company_cn as label from mapping_ticker_cik where ticker in ('{}')".format( "','".join(tkLst)) tkLbLst = sqlQuery(xqr, pgDB).to_dict(orient='records') print xqr, tkLbLst for v in tkLbLst:
def batch_daily_comment(tkLst=None, **kwargs): for ky, va in kwargs.items(): exec("{}=va".format(ky)) pgDB = conn2pgdb(dbname=dbname, hostname=hostname) mgDB = conn2mgdb(dbname=dbname.replace('.', '_'), hostname=hostname) #====OHLC Comment=========================== pqint("====OHLC Comment===========================", file=sys.stderr) if tablename is None: tablename = "{}_{}".format("ohlc_daily_comment", lang) if mp3YN is True: tablename = "{}_{}".format(tablename, "mp3") else: tablename = tablename # Initial assignments (tempS, daily_hdr) = assign_ts_ohlc(lang=lang) if filename is None: fp = None elif filename == "stdout" or tablename == "-": fp = sys.stdout elif filename == "stderr": fp = sys.stderr else: fp = open(filename, "w") # Get batch list sqr = "SELECT s.*,m.sector,m.company{} as company FROM mapping_ticker_cik m right join (select ticker,min(pbdate) as mndt,max(pbdate) as mxdt from ohlc_pnl group by ticker) as s ON m.ticker=s.ticker order by s.ticker".format( "" if lang == "en" else "_" + lang) dataM = pd.read_sql(sqr, pgDB) #dataM=pd.read_sql(sqr,pgDB)[9:10] if tkLst is not None: dx = dataM[dataM['ticker'].isin(tkLst)] if dx.empty is True: pqint("{} not found!".format(tkLst), file=sys.stderr) return None else: dataM = dx.reset_index(drop=True) pqint( "START batch_daily_comment-------------------------------------------", file=sys.stderr) for j, tRw in dataM.iterrows(): (tkX, mndt, mxdt, sector, label) = tRw.values iteritem_daily_comment(j, tempS, tkX, mndt, mxdt, sector, label, daily_hdr, tablename, pgDB, mgDB, fp, wmode=wmode, lang=lang, saveDB=saveDB, mp3YN=mp3YN, dirname=dirname, debugTF=debugTF) wmode = 'append' #fp.close() pqint("END batch_daily_comment-------------------------------------------", file=sys.stderr) print(kwargs.keys()) print(kwargs.values())
def headline_hist(ndays=2,saveDB=False,end=None,outTF=False,**optx): eqtLst = get_eqtLst() pgDB=conn2pgdb(dbname='ara') # get indices chgPct: idxRtn idxLst = ['^GSPC','^DJI','^IXIC','^SOX'] idxRtnHist = get_stock_hist(idxLst,ndays=ndays,diffTF=True,pgDB=pgDB,end=end) if len(idxRtnHist)<1: return [] # get equity chgPct eqtRtnHist = get_stock_hist(eqtLst,ndays=ndays,pgDB=pgDB,end=end) if len(eqtRtnHist)<1: return [] # get equity chgPct pbLst = idxRtnHist.loc[idxRtnHist['ticker']==idxLst[0],'pbdate'].sort_values(ascending=False)[:] # redirect stdout old_stdout = sys.stdout mystdout = StringIO() sys.stdout = mystdout fwrite=sys.stdout.write fwrite("pbdate|Index1|chgPct1|chgLevel1|UpDn1|close1") fwrite("|Index2|chgPct2|chgLevel2|UpDn2|close2") fwrite("|Index3|chgPct3|chgLevel3|UpDn3|close3") fwrite("|Index4|chgPct4|chgLevel4|UpDn4|close4") print("|topDate1|topUD1|topLst1|topDate2|topUD2|topLst2|conflict") for pbdate in pbLst: idxRtn,idxUp,idxDn = find_top_changes(pbdate=pbdate,dx=idxRtnHist) eqtRtn,eqtUp,eqtDn = find_top_changes(pbdate=pbdate,dx=eqtRtnHist) #print(idxRtn,"\n",eqtUp,"\n",eqtDn) if len(idxRtn)<1: sys.stderr.write("skip date: {}\n".format(pbdate)) continue sp500Ret = idxRtn.loc[idxRtn['ticker']=='^GSPC'].iloc[0].to_dict() dowRet = idxRtn.loc[idxRtn['ticker']=='^DJI'].iloc[0].to_dict() nasdaqRet = idxRtn.loc[idxRtn['ticker']=='^IXIC'].iloc[0].to_dict() phlRet = idxRtn.loc[idxRtn['ticker']=='^SOX'].iloc[0].to_dict() if np.isnan(sp500Ret['chgPct']): sys.stderr.write("skip date: {}\n".format(pbdate)) continue conflict = 0 if dowRet['sign'] == sp500Ret['sign'] else 1 eqtLx1 = eqtUp if sp500Ret['sign']==1 else eqtDn if len(eqtLx1)<1: topLst1=[] eqtLead1 = {} else: dtmp=eqtLx1[['ticker','chgPct']].head(3).to_dict(orient='records') topLst1=[{x['ticker']:round(x['chgPct'],2)} for x in dtmp] #topLst1=["{!r}:{:.2f}".format(*x) for x in (eqtLx1[['ticker','chgPct']].head(3).values)] eqtLead1 = eqtLx1.iloc[0].to_dict() eqtLead1['topLst1']=topLst1 eqtLx2 = eqtUp if dowRet['sign']==1 else eqtDn if len(eqtLx2)<1: topLst2=[] eqtLead2 = {} else: dtmp=eqtLx2[['ticker','chgPct']].head(3).to_dict(orient='records') topLst2=[{x['ticker']:round(x['chgPct'],2)} for x in dtmp] #topLst2=["{!r}:{:.2f}".format(*x) for x in (eqtLx2[['ticker','chgPct']].head(3).values)] eqtLead2 = eqtLx2.iloc[0].to_dict() eqtLead2['topLst2']=topLst2 fwrite("{pbdate}|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**sp500Ret)) fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**dowRet)) fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**nasdaqRet)) fwrite("|{ticker}|{chgPct:.2f}|{chgLevel:.0f}|{UpDn}|{close:5g}".format(**phlRet)) if len(eqtLx1)>0: fwrite("|{pbdate}|{UpDn}|{topLst1}".format(**eqtLead1)) if len(eqtLx2)>0: fwrite("|{pbdate}|{UpDn}|{topLst2}".format(**eqtLead2)) fwrite("|{conflict}\n".format(conflict=conflict)) # redirect the stdout to string and convert it to dataframe xstr = mystdout.getvalue() sys.stdout = old_stdout fwrite=sys.stdout.write if outTF: fwrite(xstr) df = pd.read_csv(StringIO(xstr),sep='|') from _alan_calc import save2pgdb from _alan_str import write2mdb if saveDB==True: tablename="headline_hist" sys.stderr.write("Save to {}\n".format(tablename)) #save2pgdb(df,db=pgDB,tablename=tablename) clientM=None zpk={"ticker","pbdate"} mobj,clientM,msg = write2mdb(df,clientM,tablename=tablename,zpk=zpk) if 'topDict' in optx and optx['topDict']==True: if 'topLst1' in df: df['topLst1']=[ ast.literal_eval(x) if hasattr(df['topLst1'], "__len__") else {} for x in df['topLst1'] ] if 'topLst2' in df: df['topLst2']=[ ast.literal_eval(x) if hasattr(df['topLst2'], "__len__") else {} for x in df['topLst2'] ] df['chg1']=df['chgLevel1'] df['chg2']=df['chgLevel2'] df['allUpDn']=0 for j in range(df.shape[0]): if all([ x>0 for x in df[['chgPct1','chgPct2','chgPct3']].iloc[j] ]) : allUpDn = 1 elif all([ x<0 for x in df[['chgPct1','chgPct2','chgPct3']].iloc[j] ]) : allUpDn = -1 else: allUpDn = 0 df.loc[df.index[j],'allUpDn']=allUpDn return df
md2['label']=optx['label'] print >> sys.stderr, "Additionals in run_narrative():{}".format(optx) dirname = optx['dirname'] if 'dirname' in optx else '.' fname = '{}/daily_macdEnhance_{}.j2'.format(dirname,lang) ts_enhance = open(fname).read() enhanceClause = generate_comment_ohlc(md2,ts_enhance,lang=lang) if 'fcsChg' in optx: md1.update(fcsChg=optx['fcsChg']) if 'label' in optx: md1['label']=optx['label'] md1['enhanceClause'] = enhanceClause fname = '{}/daily_ohlc_{}.j2'.format(dirname,lang) ts_ohlc = open(fname).read() ret = generate_comment_ohlc(md1,ts_ohlc,lang=lang) if debugTF is True: print >> sys.stderr, ret return ret #----- MAIN ------# if __name__ == '__main__': pgDB=conn2pgdb(dbname='ara',hostname='localhost') args=sys.argv[1:] ticker = 'SPY' if len(args)==0 else args[0] date = 20181203 if len(args)<=1 else int(args[1]) minute_hist = get_stgyData(ticker,date) minute_pnl = find_stgy(minute_hist,pgDB) #run_narratives_tst(ticker,minute_hist,minute_pnl,pgDB) ret = run_narratives(ticker,minute_hist,minute_pnl,pgDB) print >> sys.stderr, ret
def get_select_list(dbname='ara.tw', tkLst=None): pgDB = conn2pgdb(dbname=dbname) xqTmp = "SELECT b.company_cn as label,a.* FROM (SELECT * FROM ohlc_daily_comment_cn_mp3 WHERE ticker in ({})) as a LEFT JOIN mapping_ticker_cik b ON a.ticker=b.ticker ORDER BY a.trr" xqr = xqTmp.format("'{}'".format("','".join(tkLst))) df = pd.read_sql(xqr, pgDB) return (df, pgDB)
def t2l(ticker='', output='dict', quoteTF=True, dbname='ara'): ''' get basic info from ticker ''' if isinstance(ticker, list): return batch_t2l(tkLst=ticker, output=output, quoteTF=quoteTF, dbname=dbname) #- GET summaryProfile from mDB:yh_summaryProfile or onTheFly dg = tk2infoM(ticker, tablename="yh_summaryProfile", funcArg='yh_financials', modules="summaryProfile", zpk={'ticker'}, deltaTolerance=864000) dg = renameDict(dg, {"sector": "sector_alias"}) #- GET basic ticker info from pgDB:mapping_ticker_cik pgDB = conn2pgdb(dbname=dbname) df = tk2info(ticker, tablename='mapping_ticker_cik', pgDB=pgDB) if all([len(df), len(dg)]): df = df.merge(dg, on='ticker') elif len(dg) > 0: df = dg #- CHECK available data ---------------------------# if len(df) < 1: return {} #- GET ticker sector info from pgDB:spdr_sector if 'sector_alias' in df: sa = 'sector_alias' dg = tk2info(df[sa].values[0], tablename='spdr_sector', colx=sa, pgDB=pgDB) elif 'sector' in df: sa = 'sector' dg = tk2info(df[sa].values[0], tablename='spdr_sector', colx=sa, pgDB=pgDB) else: dg = [] if all([len(df), len(dg)]): df = df.merge(dg[['etfname', 'sector', 'sector_alias', 'sector_cn']], on=sa) if "sector_cn" not in df: df = renameDict(df, { "sector_cn_x": "sector_cn", "sector_x": "sector" }) elif len(dg) > 0: df = dg if 'industry' in df: dg = tk2info(df['industry'].values[0], tablename='sector_alias_industry', colx='industry', pgDB=pgDB) if len(dg): df = df.merge(dg[['industry', 'industry_cn']], on='industry') else: df['industry_cn'] = df['industry'].values #- GET summaryProfile from mDB:yh_quote_curr or onTheFly dg = tk2infoM(ticker, tablename='yh_quote_curr', funcArg='yh_quote_comparison', zpk={'ticker'}, deltaTolerance=900) if all([len(df), len(dg)]): df = df.merge(dg, on='ticker') if 'trailingPE' in df: df['peRatio'] = df['trailingPE'] elif len(dg) > 0: df = dg if 'sector' not in df and 'quoteType' in df: df['sector_cn'] = df['sector'] = df['quoteType'] try: if 'shortName' in df: df['company'] = df['shortName'] if not df['company'].values[0]: df['company'] = df['ticker'] if 'company_cn' not in df: df['company_cn'] = en2cn(df['company'].values[0]) except Exception as e: sys.stderr.write("**ERROR:{} via {} in {}\n".format( str(e), 'en2cn', 't2l()')) if output == 'dict': return df.to_dict(orient='records')[0] return df
def upd_mapping_ticker(lkbk=-7, mxcap=10**9, debugTF=False, saveDB=True, **optx): sys.stderr.write("===STARTING to update ticker list\n") #1. UPDATE sp500 / Dow30 list from theme_list import sp500_component sp500 = sp500_component(saveDB=False) if len(sp500) < 1: sp500 = sqlQuery("SELECT * FROM sp500_component") sLst = sp500['ticker'].values #2. PULL MDB::ara:madmoney_hist dt6mo = int(next_date(months=-6, dformat='%Y%m01', dtTF=False)) mdb = conn2mgdb(dbname='ara') mLst = mdb['madmoney_hist'].find({ "pbdate": { "$gt": dt6mo } }).distinct("ticker") mLst = [x.replace('.', '-') for x in mLst] #3. COMPILE a new list based on madmoney and SP500 new tickers tkCIK = sqlQuery( "SELECT * FROM mapping_ticker_cik WHERE act_code=1 and ticker not similar to '%%(^|=)%%'" ) tLst = tkCIK['ticker'].values n1Lst = set(sLst) - set(tLst) sys.stderr.write("New SP500 List:{}\n".format(n1Lst)) n2Lst = set(mLst) - set(tLst) sys.stderr.write("New madMoney List:{}\n".format(n2Lst)) nLst = list(n1Lst.union(n2Lst)) #4. PULL only valid quotes within last 7 days and marketCap > 1B dt7d = int(next_date(days=lkbk, dformat='%Y%m%d', dtTF=False)) df = yqc(nLst, tablename=None, screenerTF=False, dfTF=True) xqr = 'not(pbdate<{}|marketCap<{})'.format(dt7d, mxcap) dg = df.query(xqr) newLst = dg['ticker'].values if len(newLst) < 1: sys.stderr.write("No Additions\n") return (0) else: sys.stderr.write("New Additional List:{}\n".format(newLst)) #5. PULL new list 'newLst' with cik/sic/sector info if saveDB: newDf = tkmp(newLst) pgDB = conn2pgdb(dbname='ara') newDf.to_sql('ticker_mapping_temp', pgDB, schema='public', index=False, if_exists='replace') xqr = """ DELETE FROM mapping_ticker_cik B USING ticker_mapping_temp C WHERE B.ticker = C.ticker; INSERT INTO mapping_ticker_cik SELECT cik,ticker,company,sic,sector,company_cn,sector_cn,1::int as act_code FROM (SELECT a.*,b.sector,b.sector_cn from ticker_mapping_temp a LEFT JOIN spdr_sector b ON a.sector_alias=b.sector_alias) as s """ pgDB.execute(xqr, pgDB) return newLst
7. check splits, M&A activies daily basis ''' import sys import pandas as pd from pprint import pprint from _alan_calc import sqlQuery, conn2pgdb, upd_temp2hist def printerr(s, file=sys.stderr, end='\n'): file.write(s + end) dbname = 'ara' hostname = 'localhost' pgDB = conn2pgdb(dbname=dbname, hostname=hostname) xqr = """select * from (select a.pbdate,a.name,a.close,b.close,(a.close/b.close-1)*100. as pchg from prc_hist_iex a,prc_temp_iex b, (select name,min(pbdate) mndate from prc_temp_iex group by name) as c where a.pbdate=c.mndate and b.pbdate=c.mndate and a.pbdate=b.pbdate and a.name=b.name and a.name=c.name ) as x where abs(pchg)>0.5 ORDER BY abs(pchg) """ scLst = sqlQuery(xqr, engine=pgDB) if len(scLst) > 0: tb_temp = 'temp_list' scLst.to_sql(tb_temp, pgDB, index=False, schema='public', if_exists='replace') xlst = "('{}')".format("','".join(scLst['name'])) #fp=open('stock_list_upd.tmp','w') #fp.write(scLst.to_csv(index=False,sep='|')) #fp.close() printerr(xlst)
def batch_ohlc_fcs(tkLst, opts=None, optx=None, fpTF=False): if opts is None: opts, _ = opt_ohlc_fcs([]) if optx is not None: opts.update(optx) if opts['category'] != 'stock': opts['src'] = 'fred' for ky, va in opts.items(): exec("{}=va".format(ky)) debugTF = getKeyVal(opts, 'debugTF', False) dbname = getKeyVal(opts, 'dbname', False) hostname = getKeyVal(opts, 'hostname', 'localhost') days = getKeyVal(opts, 'days', 730) freq = getKeyVal(opts, 'freq', 'D') j2ts = getKeyVal(opts, 'j2ts', None) j2name = getKeyVal(opts, 'j2name', None) category = getKeyVal(opts, 'category', 'stock') lang = getKeyVal(opts, 'lang', 'cn') saveDB = getKeyVal(opts, 'saveDB', False) mp3YN = getKeyVal(opts, 'mp3YN', False) if debugTF is True: pqint(opts, file=sys.stderr) pgDB = conn2pgdb(dbname=dbname, hostname=hostname) dcmt = '' for ticker in tkLst: if '|' in ticker: alst = ticker.split('|') dlst = [alst[0], freq, sector, alst[0]] (ticker, freq, sector, label) = alst + dlst[len(alst) - len(dlst):] else: label = ticker if freq != 'D': nday = adjust_fq_days(days, fq=freq) opts['freq'] = freq opts['days'] = nday else: nday = days try: (dd, df, datax) = run_ohlc_fcs(ticker, opts, pgDB=pgDB) if df is None: continue pqint(df.tail(3), file=sys.stderr) j2ts = j2ts if j2ts is not None else open( j2name).read() if j2name is not None else None dcmt, fp = convert_data_comment_fcst(ticker, category, df, pgDB, lang=lang, mp3YN=mp3YN, ts=j2ts, fpTF=True) if saveDB is True: df.to_sql(tablename, pgDB, schema='public', index=False, if_exists=wmode) wmode = 'append' pqint(df, file=sys.stderr) else: pqint(df.to_csv(sep="\t"), file=sys.stderr) pqint(dcmt, file=sys.stdout) except Exception as e: pqint(str(e), file=sys.stderr) pqint('**ERROR {}: {}'.format("batch", ticker), file=sys.stderr) continue if pgDB is not None: pgDB.dispose() global gData from lsi_daily import gData return dcmt if fpTF is False else (dcmt, fp)
import pprint #%matplotlib inline #import matplotlib.pyplot as plt from hashlib import md5 if sys.version_info.major == 2: reload(sys) sys.setdefaultencoding('utf8') dtCvt = lambda x: str(x) if isinstance(x, (ObjectId,datetime.datetime)) else x # set pandas global display formula pd.set_option('display.max_colwidth', -1) pd.options.display.float_format='{:,.2f}'.format # set mongoDB database connection as globals() mgDB=conn2mgdb(dbname='ara') pgDB=conn2pgdb(dbname='ara') currDate=datetime.datetime.now() def verify_apikey(ak): if ak=='beyondbond_internal_testing_only': return 1 ht=pd.read_csv("/apps/fafa/webDir/loginDir/.htpasswd.dat",sep="\t") aLst=list(ht['api_key']) return 1 if ak.lower() in aLst else 0 class myPrettyPrinter(pprint.PrettyPrinter): def format(self, object, context, maxlevels, level): if isinstance(object, unicode): #return (object.encode('utf8'), True, False) return (object, True, False) return pprint.PrettyPrinter.format(self, object, context, maxlevels, level)