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 macro_list_x(j, datax, currdate=None, pgDB=None):
xqr = "category_seq=={}".format(j)
df = datax.query(xqr)
if j == 1: # indicator
df = df[~df['ticker'].isin(["^DJI", "^SOX", "^IXIC", "^GSPC"])]
elif j == 2: # currency
df = df.query("ticker!='DEXUSAL'")
elif j == 3: # rate
df = df[df['ticker'].str.contains("DGS")]
elif j == 4: # macro
try:
vntLst = pd.read_sql(
"select freq,series,vntdate::int from macro_vintage_date where series in ('UNRATE','SPCS20RSA','A939RX0Q048SBEA','CPIAUCNS','PPIACO','HPIPONM226S','UMCSENT') ORDER BY vntdate DESC",
con=pgDB)
except Exception, e:
print >> sys.stderr, "**ERROR @ {}():{},DB:{}".format(
"macro_list_x", str(e), pgDB)
return None
x7d = int(ymd_delta(currdate, days=10))
vntx = vntLst.query("vntdate>={}".format(x7d))
if vntx.shape[0] > 0:
df = df[df['ticker'].str.contains("|".join(vntx['series'].values))]
df['series'] = df['ticker'].apply(
lambda x: x.replace('_PCTCHG', ''))
df = df.merge(vntx[['series', 'vntdate']], on='series')
else:
return None
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 macro_list_x(j, datax, currdate=None, pgDB=None, debugTF=False):
xqr = "category_seq=={}".format(j)
df = datax.query(xqr)
if j == 1: # indicator
df = df[~df['ticker'].isin(["^DJI", "^SOX", "^IXIC", "^GSPC"])].copy()
elif j == 2: # currency
df = df.query("ticker!='DEXUSAL'").copy()
elif j == 3: # rate
df = df[df['ticker'].str.contains("DGS")].copy()
elif j == 4: # macro
try:
vntLst = pd.read_sql(
"select freq,series,vntdate::int from macro_vintage_date where series in ('UNRATE','SPCS20RSA','A939RX0Q048SBEA','CPIAUCNS','PPIACO','HPIPONM226S','UMCSENT') ORDER BY vntdate DESC",
con=pgDB)
except Exception as e:
print("**ERROR @ {}():{},DB:{}".format("macro_list_x", str(e),
pgDB),
file=sys.stderr)
return None
x7d = int(ymd_delta(currdate, days=10))
vntx = vntLst.query("vntdate>={}".format(x7d))
if vntx.shape[0] > 0:
df = df[df['ticker'].str.contains("|".join(
vntx['series'].values))].copy()
df.loc[:, 'series'] = df['ticker'].apply(
lambda x: x.replace('_PCTCHG', ''))
df = df.merge(vntx[['series', 'vntdate']], on='series')
else:
return None
if (len(df) < 1):
return None
dy = df.loc[df.zx.abs().sort_values(ascending=False).index[:2]]
dydc = dy.to_dict(orient='records')
if j == 3: # rate
dydc[1] = df.query("ticker=='DGS10'").iloc[0].to_dict()
return dydc
def mkt_list_x(j, datax, currdate=None, pgDB=None):
xqr = "category_seq=={}".format(j)
df = datax.query(xqr)
if j == 1:
df = df[~df['ticker'].isin(["^DJI", "^SOX", "^IXIC", "^GSPC"])]
if j == 2:
df = df.query("ticker!='DEXUSAL'")
elif j == 3:
df = df[df['ticker'].str.contains("DGS")]
elif j == 4:
try:
vntLst = pd.read_sql(
"select * from macro_vintage_date where series in ('UNRATE','SPCS20RSA','A939RX0Q048SBEA','CPIAUCNS','PPIACO','HPIPONM226S','UMCSENT') ORDER BY vntdate DESC",
con=pgDB)
except Exception, e:
print >> sys.stderr, "**ERROR @ {}():{},DB:{}".format(
"mkt_list_x", str(e), pgDB)
return None
vntx = vntLst.iloc[0]
x7d = int(ymd_delta(currdate, days=7))
if int(vntx['vntdate']) >= x7d:
df = df[df['ticker'].str.contains(vntx['series'])]
else:
return None