def sp500_component(hostname='localhost',
dbname='ara',
tablename='sp500_component',
debugTF=False,
**kwargs):
'''
Update pgDB Table: sp500_component
Get Sp500 list from wiki
and save to both mongoDB and postgreSQL
ref site: https://en.wikipedia.org/wiki/List_of_S%26P_500_companies
'''
saveDB = getKeyVal(kwargs, key='saveDB', val=True)
url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'
if debugTF:
sys.stderr.write('URL:{url}\n'.format(url=url))
try:
df = pd.read_html(url,
attrs={'id': 'constituents'},
index_col=False,
header=0)[0]
if len(df) < 500:
return {}
df['ticker'] = [s.replace('.', '-') for s in df['Symbol']]
save2pgdb(df, db=dbname, tablename=tablename)
except Exception as e:
sys.stderr.write('**ERROR: sp500_component:{}\n'.format(str(e)))
return {}
if saveDB:
save2pgdb(df, db=dbname, tablename=tablename)
mobj, _, _ = insert_mdb(df,
clientM=None,
dbname=dbname,
tablename=tablename,
wmode='replace')
return df
def get_exch_time(start=None,end=None,isoCode='XNYS',tz='America/New_York',dbname='ara',tablename='calendar_trading',wmode=None,debugTF=True):
cal = get_calendar(isoCode)
open_time = cal._opens.tz_convert(tz).to_pydatetime()[start:end]
close_time = cal._closes.tz_convert(tz).to_pydatetime()[start:end]
pbdate=[ int(dt2ymd(x)) for x in close_time]
df = pd.DataFrame(data=np.array([open_time,close_time,pbdate]).T,columns=['open_time','close_time','pbdate'])
df['iso_code'] = isoCode
if wmode:
save2pgdb(df,db=dbname,tablename=tablename,wmode=wmode)
pname = "pickle/{tablename}.{isoCode}.pickle".format(**locals())
fp = open(pname,"wb")
pickle.dump(df,fp)
fp.close()
if debugTF:
sys.stderr.write("Calendar Saved to Table:{tablename}: Pickle:{pname}\n".format(**locals()))
return df
def dow_component(hostname='localhost',
dbname='ara',
tablename='dow_component',
debugTF=False,
**kwargs):
'''
Update pgDB Table: dow_component
Get DOW30 list from wiki
and save to both mongoDB and postgreSQL
DB Table: dow_component
ref site: https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average
'''
url = 'https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average'
saveDB = getKeyVal(kwargs, key='saveDB', val=True)
if debugTF:
sys.stderr.write('URL:{url}\n'.format(url=url))
try:
df = pd.read_html(url,
attrs={'id': 'constituents'},
index_col=False,
header=0)[0]
if len(df) < 30:
return {}
df['ticker'] = [s.replace('.', '-') for s in df['Symbol']]
df.columns = [re.sub('[(), ]', '', x) for x in df.columns]
except Exception as e:
sys.stderr.write("**ERROR: dow_component:{}\n".format(str(e)))
return {}
if saveDB:
save2pgdb(df, db=dbname, tablename=tablename)
mobj, _, _ = insert_mdb(df,
clientM=None,
dbname=dbname,
tablename=tablename,
wmode='replace')
return df
def yh_batchSpark(tkLst=[],
filter='*',
types='spark',
nchunk=100,
saveDB=True,
dbname='ara',
tablename='yh_spark_hist',
zpk={'ticker', 'epochs'},
t2='yh_quote_curr',
t2zpk={'ticker'},
isEoD=False,
**optx):
'''
pull data from types=[spark|quote] then save to mgDB 'dbname':: 'tablename' and 't2' respectively
Note,
if isEoD=True: quote data save to both mgDB 'dbname':: 'tablename' and 't2'
if tablename or t2 ='' , nothing will be saved in 'tablename' or 't2'
quote data will also be saved to pgDB 'dbname'::'t2' if isEoD=True and types='quote'
'''
debugTF = getKeyVal(optx, 'debugTF', False)
if debugTF:
sys.stderr.write("===LOCALS: {}\noptx: {}\n".format(locals(), optx))
dbM = conn2mgdb(dbname=dbname)
if tkLst is None or len(tkLst) < 1:
tkLst = list(pull_act_tickers()['ticker'])
elif isinstance(tkLst, str):
tkLst = [tkLst]
chunkLst = list2chunk(tkLst, nchunk)
jdN = []
colX = [
'ticker', 'open', 'high', 'low', 'close', 'xclose', 'volume', 'pchg',
'change', 'pbdate', 'epochs', 'pbdt', 'hhmm'
]
for j, tkM in enumerate(chunkLst):
jdTmp = yh_batchRaw(tkM, types=types, **optx)
jdQC = {}
if types in ['spark', 'chart']:
jdLst = jdTmp[types]['result']
if types == 'chart':
jdM = chart2df(jdLst, **optx)
else:
jdM = batchSpark_sparkConv(jdLst, **optx)
if len(jdM) < 1:
continue
if saveDB is True:
m, dbM, err = insert_mdb(jdM,
clientM=dbM,
tablename=tablename,
**optx)
if debugTF:
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdM[-1], tablename))
jdN.extend(jdM)
else: # for case of types.lower()=='quote'
jdLst = jdTmp['quoteResponse']['result']
jdQC = batchSpark_quoteConv(jdLst, **optx)
if len(jdQC) < 1:
continue
jdM = subDict(jdQC, colX)
if saveDB is True and len(jdQC) > 0:
if len(t2) > 0:
qc, dbM, erq = upsert_mdb(jdQC,
clientM=dbM,
tablename=t2,
zpk=t2zpk,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdQC[-1], t2))
if isEoD is True:
m, dbM, err = insert_mdb(jdM,
clientM=dbM,
tablename=tablename,
zpk=zpk,
**optx)
sys.stderr.write("=== {} of {} saved to {}\n".format(
tkM, jdM[-1], tablename))
jdN.extend(jdQC)
if debugTF:
sys.stderr.write("=== tkM:{}[{}/{}], last:\n{}\n".format(
tkM, len(jdM), len(jdN), jdN[-1]))
if saveDB is True and len(t2) > 0 and len(jdN) > 0 and types.lower(
) == 'quote' and isEoD is True:
try:
df = pd.DataFrame(jdN)
df = df.drop(['_id'], axis=1)
if debugTF:
sys.stderr.write(
"=== Save to pgDB::{} of {} rows(1st-last)\n{}\n{}\n".
format(t2, len(df), df.iloc[:1], df.iloc[-1:]))
from _alan_calc import save2pgdb
save2pgdb(df, dbname, tablename=t2)
except Exception as e:
sys.stderr.write("**ERROR: {}:{}\n".format("save2pgdb", str(e)))
return jdN
def csv_stock_ishare_temp_DNLD():
import pandas as pd
from io import StringIO
from _alan_calc import save2pgdb
etfX = 'ivv'
dbname = 'ara'
ldate = 'remote'
if len(sys.argv) > 1:
etfX = sys.argv[1].lower()
if len(sys.argv) > 2:
dbname = sys.argv[2]
if len(sys.argv) > 3:
ldate = sys.argv[3]
isLocal = False if ldate == 'remote' else True
urlx = 'https://www.ishares.com/us/products/{0}/{1}/1467271812596.ajax?fileType=csv&fileName={2}&dataType=fund'
etfLst = {
'ivv':
['239726', 'ishares-core-sp-500-etf', 'iShares-Core-SP-500-ETF_fund'],
'iwv': [
'239714', 'ishares-russell-3000-etf',
'iShares-Russell-3000-ETF_fund'
],
'iwm': [
'239710', 'ishares-russell-2000-etf',
'iShares-Russell-2000-ETF_fund'
],
'itot': [
'239724', 'ishares-core-sp-total-us-stock-market-etf',
'iShares-Core-SP-Total-US-Stock-Market-ETF_fund'
]
}
try:
urlName = urlx.format(*etfLst[etfX])
sys.stderr.write("URL:{}\n".format(urlName))
except Exception as e:
print >> sys.stderr, "**ERROR: {}, Usage of [ivv|itot|iwm|iwv]".format(
str(e))
exit(-1)
if (isLocal == True):
urlName = "./{}.{}.csv".format(etfX, ldate)
try:
xldata = open(urlName).read().split("\n")
stockLst = pd.read_csv(StringIO(xldata), sep=",", header=9)
except:
print >> sys.stderr, urlName, " Not Found!"
exit(-2)
else:
stockLst = pd.read_csv(urlName, sep=",", header=9)
print >> sys.stderr, "UPDATE [stock_ishare_temp] ", etfX
tbX = "stock_ishare_temp"
colx = [re.sub("[().\s%]", "", xs).lower() for xs in stockLst.columns]
stockLst.columns = colx
a2f = lambda x: float(re.sub(',', '', x) if isinstance(x, str) else x)
for xs in ['price', 'shares', 'marketvalue', 'notionalvalue']:
stockLst[xs] = stockLst[xs].apply(a2f)
stockLst.dropna(subset=['ticker'], inplace=True)
stockLst['etfname'] = etfX
sys.stderr.write("Upload fund:{} to {}::{}\n".format(etfX, dbname, tbX))
save2pgdb(stockLst, dbname, tablename=tbX)
return stockLst