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 raw2spark_output(jobj): from _alan_calc import renameDict colx = [ 'ticker', 'close', 'changePercent', 'regularMarketPreviousClose', 'epochs', 'pbdt', 'change' ] ds = {x: y for x, y in jobj.items() if x in colx} renameDict(ds, { 'regularMarketPreviousClose': 'xclose', 'changePercent': 'pchg' }) ds['hhmm'] = ds['pbdt'].strftime('%H%M') ds['pchg'] /= 100.0 return ds
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 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 quote_dx2dd(jdTmp, dicX, colX): if "regularMarketTime" not in jdTmp: sys.stderr.write("**WARNING:{} {}\n".format(ticker, "is invalid.")) return {} renameDict(jdTmp, dicX) #keep "regularMarketPreviousClose" for backward compatibility if "regularMarketPreviousClose" in jdTmp: jdTmp['xclose'] = jdTmp["regularMarketPreviousClose"] if "changePercent" in jdTmp: jdTmp['pchg'] = jdTmp["changePercent"] / 100.0 epoch = int(jdTmp["regularMarketTime"]) jdTmp['epochs'] = epoch * 1000 jdTmp['pbdt'] = pbdt = datetime.datetime.fromtimestamp(epoch) jdTmp['hhmm'] = pbdt.strftime('%H%M') jdTmp['pbdate'] = int(pbdt.strftime('%Y%m%d')) dd = subDict(jdTmp, colX) if len(colX) > 0 else jdTmp return dd
def geteach_quote(ticker,fdLst,tablename=None,lang=None,dbname="ara",hostname='localhost',colx='ticker'): if tablename is None: return None xtmp="select {} from {} WHERE {}='{}' order by pbdate DESC limit 2" xqr=xtmp.format(fdLst,tablename,colx,ticker) sys.stderr.write(" --geteach_quote from pgDB::{}\n".format(tablename)) sys.stderr.write(" --SQL:\n{}\n".format(xqr)) try: data=pd.read_sql(xqr,con=pgDB) renameDict(data,{"series":"ticker","value":"close"}) colv = 'close' if data.shape[0]>1 and 'change' not in data.columns: chg = data[colv].iloc[0]-data[colv].iloc[1] pchg = data[colv].iloc[0]/data[colv].iloc[1]-1 data = data.iloc[0] data['change']=chg data['changePercent']=pchg sys.stderr.write(" --DATA[{}]:\n{}\n".format(len(data),data)) except Exception as e: sys.stderr.write("**ERROR:{} @ {}\n".format(str(e),'geteach_quote')) return None return data
def search_quote(tkLst,fdLst,**opts): tkLst=get_sector_etfname(tkLst,**opts) sys.stderr.write("---tkLst: {} @ search_quote\n".format(tkLst)) instrument = getKeyVal(opts,'instrument','stock') outTF = getKeyVal(opts,'outTF',True) hostname,dbname,tablename,lang = getKeyVal(opts,['hostname','dbname','tablename','lang'],['localhost','ara',None,None]) colx='ticker' if instrument=='stock' else 'series' data=[] opts.pop('ticker',None) for ticker in tkLst: try: # get quotes from MDB::"yh_quote_curr" for yahoo source indices setup in the PGDB::'mapping_series_label' if instrument=='stock' or re.search(r'[=^.]',ticker): mktLst =['^GSPC','^DJI','^IXIC','^SOX'] if ticker.upper() in mktLst: tablename="market_indicator_quote" elif re.search(r'[=^.]',ticker): tablename="yh_spark_hist" else: tablename="iex_spark_hist" jobj={"ticker":ticker} ret = list(mgDB[tablename].find(jobj,{"_id":0},sort=[("epochs",-1)]).limit(1)) #ret,_,_=find_mdb(jobj,tablename=tablename,dbname="ara") ret = subDict(ret,['ticker','close','change','pchg','xclose','epochs','pbdate','pbdt']) ret = renameDict(ret,{'pchg':'changePercent','xclose':'prevClose'}) else: # get quotes for all fields from pgDB ret=geteach_quote(ticker,fdLst='*',tablename=tablename,lang=lang,dbname=dbname,hostname=hostname,colx=colx) if ret is not None and len(ret)>0: #data=data.append(ret,ignore_index=True) data.extend(ret) else: continue except Exception as e: pqint( "**ERROR:{} @ {}".format(str(e),search_quote) ,file=sys.stderr) continue if len(data)<1: return None if not outTF: return data data=pd.DataFrame(data) if fdLst is None: pass elif len(fdLst)>2 and fdLst.lower()=='all': pass else: colx=['ticker','epochs','open','high','low','close','volume','xclose','change','pchg','pbdt','hhmm','pbdate','changePercent','prevClose','marketCap'] data=subDF(data,colx) # data = data_output(data,output) return data
def search_hist(tkLst,fdLst,**opts): output=getKeyVal(opts,'output','json') topic=getKeyVal(opts,'topic','daily') opts.pop('ticker',None) data=pd.DataFrame() dd=[] for ticker in tkLst: df=geteach_history(ticker,fdLst,**opts) sys.stderr.write(" --DF: {}\n{}\n".format(ticker,type(df))) if isinstance(df,pd.DataFrame) and len(df)>0: data=data.append(df,ignore_index=True) elif isinstance(df,list) and len(df)>0: dd=dd.extend(df) else: continue if len(dd)>0: data=pd.DataFrame(dd) sys.stderr.write(" --DATA[{}] tail:\n{}\n".format(len(data),data.tail())) if topic not in ['daily','minute']: return data # data_output(data,output) renameDict(data,{"name":"ticker"}) colx=['ticker','epochs','open','high','low','close','volume','xclose','change','pchg','pbdt','hhmm','pbdate'] data=subDF(data,colx) return data # data_output(data,output)
def yh_hist_query(tkLst=[], filter='*', types='quote', nchunk=50, rawTF=False, screenerTF=False, dfTF=False, debugTF=False, dbname=None, tablename=None, **optx): ''' Pull minute ohlc pricing data from Yahoo but use marketVolume as volume since market data has 15-minute delay, latest 15 marketVolumes become 0 ''' if len(tkLst) < 1: tkLst = list(pull_act_tickers()['ticker']) jdLst = yh_batchTypes(tkLst, filter=filter, types=types, nchunk=nchunk, debugTF=debugTF, **optx) colX = [ "ticker", "open", "high", "low", "close", "volume", "xclose", "change", "pchg", "epochs", 'hhmm', "pbdate", "pbdt" ] dLst = [] df = pd.DataFrame() clientM = None tablename = 'yh_{}_temp'.format(types) if tablename is None else tablename for j, jdTmp in enumerate(jdLst): try: jdX = {} if 'response' in jdTmp: ticker = jdTmp['symbol'] jdd = jdTmp['response'][0] elif 'meta' in jdTmp: ticker = jdTmp['meta']['symbol'] jdd = jdTmp else: #- for 'quote' parsing if "regularMarketPrice" not in jdTmp: continue if "regularMarketTime" not in jdTmp: continue jdTmp['epochs'] = jdTmp['regularMarketTime'] * 1000 jdTmp['pbdt'] = datetime.datetime.fromtimestamp( jdTmp['regularMarketTime']) jdTmp['pbdate'] = int(jdTmp['pbdt'].strftime('%Y%m%d')) newNames = { "symbol": "ticker", "regularMarketPrice": "close", "regularMarketChange": "change", "regularMarketChangePercent": "changePercent", "regularMarketOpen": "open", "regularMarketDayHigh": "high", "regularMarketDayLow": "low", "regularMarketVolume": "volume", "regularMarketPreviousClose": "xclose", "regularMarketTime": "epoch" } if rawTF: renameDict(jdTmp, mapper=newNames) if debugTF: sys.stderr.write("{}\n".format(jdTmp)) if screenerTF == True: colx = [ "change", "changePercent", "company", "marketCap", "close", "ticker", "volume", "epochs", "pbdt" ] #ds=raw2screener_output_1(jdTmp) ds = subDict(jdTmp, colx) renameDict(ds, {"close": "price"}) elif screenerTF > 0: # original False case colx = list( set(newNames.values()).union([ 'epochs', 'pbdt', 'hhmm', 'pbdate', 'marketCap' ])) ds = subDict(jdTmp, colx) else: ds = jdTmp if all([dbname, tablename]): zpk = getKeyVal(optx, 'zpk', ['ticker', 'epochs']) #mobj,clientM,err_msg = write2mdb(ds,clientM,dbname=dbname,tablename=tablename,zpk=zpk) mobj, clientM, err_msg = insert_mdb( ds, clientM, dbname=dbname, tablename=tablename, zpk=zpk) if debugTF: sys.stderr.write("{}\nSave to {}::{}\n".format( ds, dbname, tablename)) dLst.append(ds) continue #- for 'spark' and 'chart' parsing dx = pd.DataFrame([jdTmp]) dx.rename(newNames, axis='columns', inplace=True) if 'volume' not in dx: continue dx.dropna(subset=['volume'], inplace=True) if len(dx) < 1: continue colX = [x for x in colX if x in dx.columns] dm = dx[colX] if debugTF: sys.stderr.write("quote:\n{}".format(dm.tail())) df = pd.concat([df, dm]) continue xClose = None if 'meta' in jdd and 'previousClose' in jdd['meta']: xClose = jdd['meta']['previousClose'] epoch = jdd['timestamp'] for x, y in jdd['indicators']['quote'][0].items(): jdX[x] = y jdX['epochs'] = np.array(epoch) * 1000 dx = pd.DataFrame(jdX) dx['ticker'] = ticker if 'pchgTF' in optx and optx['pchgTF'] is False: df = pd.concat([df, dx]) continue elif 'pchgTF' in optx and optx[ 'pchgTF'] and jdd['meta']['dataGranularity'][:1] != 'm': dx['pchg'] = dx['close'].pct_change() dx['change'] = dx['close'].diff() xChartClose = jdd['meta']['chartPreviousClose'] dx.loc[dx.index[0], 'pchg'] = dx.loc[dx.index[0], 'close'] / xChartClose - 1 dx.loc[dx.index[0], 'change'] = dx.loc[dx.index[0], 'close'] - xChartClose pbdt = [datetime.datetime.fromtimestamp(int(x)) for x in epoch] dx['hhmm'] = [x.strftime('%H%M') for x in pbdt] dx['pbdate'] = [x.strftime('%Y%m%d') for x in pbdt] dx['pbdate'] = dx['pbdate'].astype(int) dx['pbdt'] = pbdt dx = dx.dropna() if xClose is not None and xClose > 0: dx['pchg'] = dx['close'] / xClose - 1 dx['change'] = dx['close'] - xClose dx['xclose'] = xClose colX = [x for x in colX if x in dx.columns] dm = dx[colX] if debugTF: sys.stderr.write("{}".format(dm.tail())) if dfTF: df = pd.concat([df, dm]) else: dLst.extend(dm.to_dict(orient='records')) except Exception as e: sys.stderr.write("**ERROR: {}:{}:{}\n".format(j, jdTmp, str(e))) continue if len(df) > 0: df.reset_index(drop=True, inplace=True) if len(dLst) > 0: if dfTF: dLst = pd.DataFrame(dLst) return dLst return df
def nasdaq_ipos(hostname='localhost', dbname='ara', tablename='nasdaq_ipos', tab='filing', month='', debugTF=False, **kwargs): ''' Update MDB Table: nasdaq_ipos get recent IPOs ref table: nasdaq_ipos ref site: https://api.nasdaq.com/api/ipo/calendar?date=2019-10 ''' if len(month) < 1: month = datetime.datetime.now().strftime('%Y-%m') urx = 'https://api.nasdaq.com/api/ipo/calendar?date={month}' url = urx.format(month=month) sys.stderr.write(' --URL:{}\n'.format(url)) headers = { 'Content-Type': 'application/json', 'Accept': 'application/json', 'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36' } try: #ret =requests.get(url,timeout=5) ret = requests.Session().get(url, headers=headers) jsx = ret.json() actions = getKeyVal(kwargs, key='actions', val='priced') data = jsx['data'][actions]['rows'] if not data: actions = 'filed' data = jsx['data'][actions]['rows'] if not data: return {} colX = jsx['data'][actions]['headers'] df = pd.DataFrame(data) df = renameDict(df, colX) if debugTF: sys.stderr.write(' --DATA:\n{}\n'.format(data)) sys.stderr.write(' --df:\n{}\n'.format(df)) if "Exchange/ Market" in df: df = df.rename(columns={"Exchange/ Market": "Market"}) if debugTF: sys.stderr.write('COLUMNS:{}\n'.format(df.columns)) df.dropna(subset=['Symbol'], inplace=True) if len(df) < 1: return {} df['ticker'] = [s.replace('.', '-') for s in df['Symbol']] if 'Offer Amount' in df: df['offer'] = [ float(s.replace('$', '').replace(',', '')) for s in df['Offer Amount'] ] df = df.drop(['Offer Amount'], axis=1) if 'Date' in df: df['pbdate'] = [ int( datetime.datetime.strptime(s, '%m/%d/%Y').strftime('%Y%m%d')) for s in df['Date'] ] elif 'Date Filed' in df: df['pbdate'] = [ int( datetime.datetime.strptime(s, '%m/%d/%Y').strftime('%Y%m%d')) for s in df['Date Filed'] ] elif 'Date Priced' in df: df['pbdate'] = [ int( datetime.datetime.strptime(s, '%m/%d/%Y').strftime('%Y%m%d')) for s in df['Date Priced'] ] except Exception as e: sys.stderr.write('**ERROR: nasdaq_ipos:{}\n'.format(str(e))) return {} df.columns = [x.replace(' ', '').lower() for x in df.columns] df = add_sector_industry(df) mobj, _, _ = upsert_mdb(df, clientM=None, dbname=dbname, tablename=tablename, zpk={'ticker', 'pbdate', 'actions'}) return df
def plot_csvdata(df, nbins=6,rsiYN=False,title=None,pivot_value=None,pivot_group=None,pngname=None,x_fmt="%b-%d-%y",interpolateYN=True,backend="tkAgg",npar=15,tsTF=True,xaxis=None,trendTF=False,debugTF=False,ohlcTF=False,ohlcComboTF=False,lang='en',**kwargs): if debugTF: sys.stderr.write("===plot_csvdata VARS:\t{}\n".format(locals())) import matplotlib.dates as mdates import matplotlib.image as mimage import matplotlib.ticker as mticker #pltStyle=getKeyVal(kwargs,'pltStyle','dark_background') pltStyle=getKeyVal(kwargs,'pltStyle','classic') figsize=getKeyVal(kwargs,'figsize',(11,6)) ylabel=getKeyVal(kwargs,'ylabel',None) fontpath=getKeyVal(kwargs,'fontpath',None) if fontpath is not None: prop.set_file(fontpath) if pltStyle in plt.style.available: plt.style.use(pltStyle) #- Use backend to 'tkAgg' for cronjob if pngname is None or len(pngname)<=4: plt.switch_backend(backend) #- Rename columns renColumns=getKeyVal(kwargs,'renColumns',{}) if len(renColumns)>0: df = renameDict(df,mapper=renColumns) #- Create datetime index idxname='date' pbname=xaxis if isinstance(df.index,pd.DatetimeIndex): pass elif pbname in df.columns: sdate = str(df[pbname].iloc[0]) if sdate.isdigit() == True: if int(sdate)>123456789: idxpt=[epoch_parser(x) for x in df[pbname]] else: idxpt=[ymd_parser(x,fmt="%Y%m%d") for x in df[pbname]] else: idxpt=[ymd_parser(x,fmt=x_fmt) for x in df[pbname]] df.set_index(pd.DatetimeIndex(idxpt),inplace=True) df.index.rename(idxname,inplace=True) df = df.drop(pbname,1) elif idxname in df.columns: df[idxname] = pd.to_datetime(df[idxname]) df.set_index(idxname,inplace=True) else: df = df.reset_index(drop=True) #- Create 2nd dataframe 'df2' for 2-Yaxis plot from _alan_calc import subDF dux=getKeyVal(kwargs,'columns2',None) if dux is not None: colLst2=dux.split(',') df2=subDF(df,colLst2) df=subDF(df,colLst2,reverseTF=True) else: df2={} #- Create a pivot table trendName = None if pivot_group in df.columns and pivot_value in df.columns: trendName = df[pivot_group][0] df=df.pivot_table(index='date',columns=pivot_group,values=pivot_value) #- Rename columns renColumns=getKeyVal(kwargs,'renColumns',{}) if len(renColumns)>0: df = renameDict(df,mapper=renColumns) #- Create linear-interpolation for missing data if interpolateYN is True: df=df.apply(extrapolate_series,axis=0) #- Create return since inception if rsiYN is True: de=[] for j in range(df.shape[1]): inix = df.iloc[0,j] if df.iloc[0,j]>1 else 1 de.append(df.iloc[:,j]/inix*100.-100) #de = [df.iloc[:,j]/df.iloc[0,j]*100.-100 for j in range(df.shape[1])] df = pd.concat(de,axis=1) #- NO PLOTTING, just return data if 'plotTF' in kwargs and kwargs['plotTF'] is False: return df,{},{} #- Create trend curve if trendTF is True: try: from _alan_pppscf import vertex_locator if trendName is None: trendName = df._get_numeric_data().columns[0] dg, dh = vertex_locator(df[trendName],npar=npar,debugTF=debugTF) #df['trend'] = dg['trend'].values if debugTF is True: sys.stderr.write("{}\n{}\n".format("Trendline dh:",dh)) except Exception as e: sys.stderr.write("**ERROR: {} @ {}\n".format(str(e),'trendline')) if title is None: title="/".join(df.columns).upper() if rsiYN is True: title += " Return Since Inception" #- plot simple line plot if tsTF is False: df = df.reset_index(drop=True) if debugTF is True: sys.stderr.write("{}\n".format(df.head())) sys.stderr.write("{}\n".format(df.tail())) nobs=len(df.index) nsp = (nobs/nbins) if nobs>nbins*2 else nobs #ds=[y for j,y in enumerate(df.index) if j%nsp==0] #ax=df.plot(xticks=ds,title=title) colorUD = ['red','green'] if lang=='cn' else ['green','red'] if ohlcComboTF is True: from alan_plot import plot_candlestickCombo from _alan_calc import run_tech chartType = 'minute' if pbname == 'epochs' else 'chart' #ma1=5;ma2=30 ma1,ma2=sma=getKeyVal(kwargs,'sma',[5,30]) datax = run_tech(df, pcol='close',winLst=sma,debugTF=debugTF,nanTF=True) if 'open' not in datax: return datax, None, None fig, axes = plot_candlestickCombo(datax,title,ma1,ma2,block=False,chartType=chartType,trendTF=trendTF,npar=npar,debugTF=debugTF,colorUD=colorUD,title=title) #plt.suptitle(title,fontsize=18,fontproperties=prop) if pngname is not None and len(pngname)>4 and '.' in pngname: ghLst = plt.gcf().canvas.get_supported_filetypes().keys() ghx = pngname.split('.')[-1] format = ghx.lower() if ghx.lower() in ghLst: format = ghx.lower() else: format = 'svg' pngname = pngname.replace(ghx,'svg') plt.savefig(pngname, format=format) #, bbox_inches='tight',dpi=1000) # skip the plot if pngname='noshow' elif pngname is None: plt.show(axes) return datax, fig, axes fig, ax=plt.subplots(figsize=figsize) if ohlcTF is True: if 'marketVolume' in df: df.rename(columns={'marketVolume': 'volume'},inplace=True) if 'open' not in df and 'close' in df: df['open']=df['high']=df['low']=df['close'] elif 'open' not in df: return df, None, None from alan_plot import plot_candlestick chartType = 'minute' if pbname == 'epochs' else 'chart' ax = plot_candlestick(df,tsidx=df.index,chartType=chartType,title=title,block=False,debugTF=debugTF,ax=ax,trendTF=trendTF,npar=npar,colorUD=colorUD) x_fmt = "%H:%M" if chartType == 'minute' else x_fmt else: colorLst=['blue','red','green','salmon','lightgray','cyan'] df.plot(ax=ax,grid=True,color=colorLst) #ax=df.plot(figsize=(11,6)) ax.set_ylabel(df.columns[0]) if trendTF is True: dg.plot(ax=ax) if len(df2)>0: if debugTF: sys.stderr.write("DF2:\n{}\n{}\n".format(df2.tail(),df2.shape)) axv = ax.twinx() df2.plot(ax=axv,kind='area',alpha=0.4,legend=False) axv.yaxis.set_major_formatter(FuncFormatter(lambda x,pos: '{:,.0f}'.format(x))) if rsiYN is True: # calc Returns Since Incept ax.set_ylabel("Returns Since Inception (%)") if ylabel is not None and len(ylabel)>0: ax.set_ylabel(ylabel,fontproperties=fontProp(size=12)) ax.grid(linestyle='dotted',linewidth=0.5) if df.index._typ == "datetimeindex": mddfmt=mdates.DateFormatter(x_fmt) ax.xaxis.set_major_formatter(mddfmt) xtinterval=(df.index[1]-df.index[0]) if xtinterval.days < 7 and xtinterval.days>=1 : # daily data ax.set_xlim(df.index[0], df.index[-1]) #ax.xaxis.set_major_locator(mdates.MonthLocator(interval=int(nsp/30.+0.97))) bymd = [1,5,10,15,20,25] if nobs<50 else [1,15] if nobs<120 else [1] itv = 1 if nobs<160 else int(nsp/30.+0.97) xlocator = mdates.MonthLocator(bymonthday=bymd,interval=itv) ax.xaxis.set_major_locator(xlocator) # check if min/max of xaxis should be included major ticks if debugTF is True: sys.stderr.write("{} {}\n".format( ax.get_xticks(),ax.get_xlim())) xtcks = list(ax.get_xticks()) x1,x2 = xtcks[:2] xmin,xmax = ax.get_xlim() if (x1-xmin)>(x2-x1)*0.6: xtcks = [xmin] + xtcks if (xmax-xtcks[-1])>(x2-x1)*0.6: xtcks = xtcks + [xmax] ax.set_xticks(xtcks) ax.xaxis.set_minor_locator(mdates.MonthLocator(interval=1)) if debugTF is True: sys.stderr.write("{}\n".format(ax.get_xticks())) sys.stderr.write("{}\n".format( "Daily data use MonthLocator")) elif xtinterval.seconds < 30: # second data locator = mdates.AutoDateLocator() locator.intervald[5] = [0,5,10,15,20,25,30,35,40,45,55] mddfmt = mdates.AutoDateFormatter(locator) mddfmt.scaled[1/(24.*60.)] = '%M:%S' ax.xaxis.set_major_locator(locator) ax.xaxis.set_major_formatter(mddfmt) sys.stderr.write("{} {}\n".format( "Second data use AutoDateLocator",xtinterval.seconds)) elif xtinterval.seconds < 100 : # minute data bym = [0,15,30,45] if nobs<=120 else [0,30] if nobs<=360 else [0] xlocator = mdates.MinuteLocator(byminute=bym, interval = 1) ax.xaxis.set_major_locator(xlocator) sys.stderr.write("{} {}\n".format( "Minute data use MinuteLocator",xtinterval.days)) else: # periodic data sys.stderr.write("{}\n".format( "Periodic data use DayLocator" )) ax.xaxis.set_major_locator(mdates.DayLocator(interval=nsp)) ax.xaxis.label.set_visible(False) plt.title(title,fontsize=30,fontproperties=prop) plt.xticks(rotation='20',fontsize=12)#,fontproperties=prop) if len(df.columns)>1 and ohlcTF is False: ax.legend(loc="upper left",prop=prop) #ax.legend().set_visible(False) #logo = mimage.imread("aicaas_icon.png") #plt.figimage(logo, xo=20,yo=420) plt.subplots_adjust(left=0.1,bottom=0.30) if pngname is not None and len(pngname)>4: plt.savefig(pngname)#, bbox_inches='tight',dpi=1000) sys.stderr.write("Save chart {} to {}\n".format(title,pngname)) else: plt.show(ax) return df, fig, [ax]
def iex_quote_short(tkLst=[], filter='', types='quote', dbname='ara', tablename='iex_quote_short', saveDB=True, debugTF=False, **optx): ''' Pull in-the-fly IEX quotes and save to MDB::ara:iex_quote_short mapping { "symbol":"ticker","changePercent":"pchg","latestPrice":"close", "latestUpdate":"epochs","previousClose":"xclose","avgTotalVolume":"volume", "previousVolume":"xvolume" } ''' if not filter: filter = 'iexMarketPercent,previousClose,avgTotalVolume,previousVolume,week52High,peRatio,iexLastUpdated,companyName,calculationPrice,latestPrice,isUSMarketOpen,week52Low,lastTradeTime,primaryExchange,ytdChange,symbol,latestTime,change,marketCap,changePercent,latestSource,latestUpdate' isNew = getKeyVal(optx, 'isNew', False) tkLst, mxdate = getTkLst(tkLst) jdTmp = iex_batchTypes(tkLst, filter=filter, types=types, **optx) dicX = { "symbol": "ticker", "changePercent": "pchg", "latestPrice": "close", "latestUpdate": "epochs", "previousClose": "xclose", "avgTotalVolume": "volume", "previousVolume": "xvolume" } dm = [] for ticker in tkLst: try: if ticker not in jdTmp: continue elif types not in jdTmp[ticker]: continue elif len(jdTmp[ticker][types]) < 1: coninue dx = jdTmp[ticker][types] renameDict(dx, dicX) pbdt = epoch_parser(dx['epochs']) pbdate = int(pbdt.strftime('%Y%m%d')) dx.update(ticker=ticker, pbdt=pbdt, pbdate=pbdate) dm.append(dx) except Exception as e: sys.stderr.write("**ERROR: {}:{}\n".format(ticker, str(e()))) continue if len(dm) < 1: return {} if saveDB is True: zpk = getKeyVal(optx, 'zpk', ['ticker', 'epochs']) ordered = getKeyVal(optx, 'ordered', False) optx.pop('zpk', None) optx.pop('ordered', None) mdb = conn2mgdb(dbname=dbname) if isNew is True: mdb[tablename].drop() insert_mdb(dm, clientM=mdb, zpk=zpk, tablename=tablename, ordered=ordered, **optx) return dm
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 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