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