def get_data(exchange):
df = None
for _ in range(3):
try:
time.sleep(0.1)
csv_path = "http://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange=%s&render=download" % exchange
df = pd.read_csv(csv_path)
df['Symbol'] = df['Symbol'].apply(string.strip).apply(string.rstrip)
df['MarketCap'] = df['MarketCap'].apply(marketcap_to_float)
df.to_sql('us_%s' % exchange, engine, if_exists='replace', index=True, index_label='id')
ytrack.success("us_%s 数据更新成功" % exchange)
break
except requests.exceptions.ConnectionError as e:
yyhtools.error(traceback.format_exc())
return
if df is None:
ytrack.error("us_%s 数据更新失败." % exchange)
return
symbols = df['Symbol'].values.tolist()
sql = 'select Symbol, cid from us_%s_cid' % exchange
try:
a = engine.execute(sql)
aa = a.fetchall()
cids = {}
for symbol, cid in aa:
cids[symbol] = cid
for symbol in symbols:
if symbol not in cids:
cid = get_cid(exchange, symbol)
sql = 'insert into us_%s_cid(Symbol, cid) values("%s", "%s")' % (exchange, symbol, cid)
engine.execute(sql)
ytrack.success("cid(%s, %s)=%s" % (exchange, symbol, cid))
except Exception as e:
yyhtools.error(trackback.format_exc())
def get_data():
page_url = 'http://www.jin10.com/'
s = requests.Session()
s.headers.update({
"User-Agent":
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.98 Safari/537.36",
})
r = None
for _ in range(3):
try:
time.sleep(0.5)
r = s.get(page_url)
break
except requests.exceptions.ConnectionError as e:
yyhtools.error("%s" % (page_url))
yyhtools.error(traceback.format_exc())
return
if r is None:
yyhtools.error("requests.get('%s') is None" % page_url)
return
soup = BeautifulSoup(r.text.encode(r.encoding))
allnews = soup.findAll("div", {"class": "newsline"})
cnt = 0
session = Session()
for news in allnews:
try:
id = long(news.attrs.get('id')) / 100
html = str(news)
session.merge(News(id=id, html=html))
cnt += 1
except:
ytrack.fail(traceback.format_exc())
session.commit()
ytrack.success("%s 成功更新 %s 条记录." % ('jin10_news', cnt))
def my_update_someday_data(df, date, save_table):
sql = "delete from %s where date = %s" % (save_table, date)
ytrack.success("execute: %s" % sql)
try:
engine.execute(sql)
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s删除数据成功" % save_table)
try:
df.to_sql(save_table, engine, if_exists='append', index=True, index_label='code')
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s 成功更新 %s 条记录." % (save_table, df.shape[0]))
def run_daily(exchange, date, symbol):
symbol = symbol.upper()
if not date:
# 美股时间差一天
day = datetime.datetime.now() - datetime.timedelta(days=1)
day = day.replace(hour=0, minute=0, second=0, microsecond=0)
date = int(day.strftime("%Y%m%d"))
else:
day = datetime.datetime.strptime(str(date), "%Y%m%d")
if day.weekday() in (5, 6):
ytrack.fail('%s is not open day' % date)
ynotice.send(ytrack.get_logs(),
style='stock',
title=u'%s-%s-K线图更新' % (get_day_date(day), exchange))
return
def my_update_someday_data(df, date, save_table):
sql = "delete from %s where date = %s" % (save_table, date)
ytrack.success("execute: %s" % sql)
try:
engine.execute(sql)
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success(u"%s删除数据成功" % save_table)
try:
df.to_sql(save_table,
engine,
if_exists='append',
index=True,
index_label='code')
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success(u"%s 成功更新 %s 条记录." % (save_table, df.shape[0]))
ytrack.success("start run_daily(date=%s, exchange=%s, symbol=%s)" %
(date, exchange, symbol))
table1 = "%s_ohlc_daily" % exchange
df1 = _update_ohlc_daily(day, symbol, table1, exchange)
if df1 is not None:
my_update_someday_data(df1, get_day_date(day), table1)
else:
ytrack.success(u"%s 需要更新的数据为空" % table1)
table2 = '%s_ohlc_weekly' % exchange
df2 = _update_ohlc_weekly(date, symbol, table2)
if df2 is not None:
my_update_someday_data(df2, get_week_date(day), table2)
else:
ytrack.success(u"%s 需要更新的数据为空" % table2)
table3 = '%s_ohlc_monthly' % exchange
df3 = _update_ohlc_monthly(date, symbol, table3)
if df3 is not None:
my_update_someday_data(df3, get_month_date(day), table3)
else:
ytrack.success(u"%s 需要更新的数据为空" % table3)
macd_cols = ['date'] + ['ma%s' % i for i in range(5, 251, 5)]
df4 = _update_macd_daily(date, symbol, table1)
if df4 is not None:
df4 = df4[macd_cols]
my_update_someday_data(df4, get_day_date(day),
'%s_macd_daily' % exchange)
else:
ytrack.success(u"%s_macd_daily 需要更新的数据为空" % exchange)
df5 = _update_macd_weekly(date, symbol, table2)
if df5 is not None:
df5 = df5[macd_cols]
my_update_someday_data(df5, get_week_date(day),
'%s_macd_weekly' % exchange)
else:
ytrack.success(u"%s_macd_weekly 需要更新的数据为空" % exchange)
df6 = _update_macd_monthly(date, symbol, table3)
if df6 is not None:
df6 = df6[macd_cols]
my_update_someday_data(df6, get_month_date(day),
'%s_macd_monthly' % exchange)
else:
ytrack.success(u"%s_macd_monthly 需要更新的数据为空" % exchange)
ynotice.send(ytrack.get_logs(),
style='stock',
title=u'%s-%s-K线图更新' % (get_day_date(day), exchange))
def run_daily_investing(date, curr_id, n):
if not date:
# 由于数据更新日期不统一，默认取三天前数据
assert n >= 0, '默认参数n=%s必须大于等于0'
day = datetime.datetime.now() - datetime.timedelta(days=n)
day = day.replace(hour=0, minute=0, second=0, microsecond=0)
date = int(day.strftime("%Y%m%d"))
else:
day = datetime.datetime.strptime(str(date), "%Y%m%d")
def my_update_someday_data(df, date, save_table):
sql = "delete from %s where date = %s" % (save_table, date)
ytrack.success("execute: %s" % sql)
try:
engine.execute(sql)
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s删除数据成功" % save_table)
try:
df.to_sql(save_table, engine, if_exists='append', index=True, index_label='code')
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s 成功更新 %s 条记录." % (save_table, df.shape[0]))
ytrack.success("start run_daily_investing(date=%s, curr_id=%s)" % (date, curr_id))
df1 = _update_ohlc_daily(day, curr_id, 'investing_ohlc_daily')
if df1 is not None:
my_update_someday_data(df1, get_day_date(day), "investing_ohlc_daily")
else:
ytrack.success("investing_ohlc_daily 需要更新的数据为空")
df2 = _update_ohlc_weekly(date, curr_id, 'investing_ohlc_weekly')
if df2 is not None:
my_update_someday_data(df2, get_week_date(day), 'investing_ohlc_weekly')
else:
ytrack.success("investing_ohlc_weekly 需要更新的数据为空")
df3 = _update_ohlc_monthly(date, curr_id, 'investing_ohlc_monthly')
if df3 is not None:
my_update_someday_data(df3, get_month_date(day), 'investing_ohlc_monthly')
else:
ytrack.success("investing_ohlc_monthly 需要更新的数据为空")
macd_cols = ['date'] + ['ma%s' % i for i in range(5, 251, 5)]
df4 = _update_macd_daily(date, curr_id, 'investing_ohlc_daily')
if df4 is not None:
df4 = df4[macd_cols]
my_update_someday_data(df4, get_day_date(day), 'investing_macd_daily')
else:
ytrack.success("investing_macd_daily 需要更新的数据为空")
df5 = _update_macd_weekly(date, curr_id, 'investing_ohlc_weekly')
if df5 is not None:
df5 = df5[macd_cols]
my_update_someday_data(df5, get_week_date(day), 'investing_macd_weekly')
else:
ytrack.success("investing_macd_weekly 需要更新的数据为空")
df6 = _update_macd_monthly(date, curr_id, 'investing_ohlc_monthly')
if df6 is not None:
df6 = df6[macd_cols]
my_update_someday_data(df6, get_month_date(day), 'investing_macd_monthly')
else:
ytrack.success("investing_macd_monthly 需要更新的数据为空")
ynotice.send(ytrack.get_logs(), style='stock', title='%s-investing-K线图更新' % get_day_date(day))
def run_daily_hs_indexs(date, code, save):
if not date:
day = datetime.datetime.now()
date = int(day.strftime("%Y%m%d"))
else:
day = datetime.datetime.strptime(str(date), "%Y%m%d")
if is_open_day(day):
ytrack.fail("%s is not Open.." % date)
ynotice.send(ytrack.get_logs(),
style='error',
title='%s-不是交易日' % get_day_date(day))
return
def my_update_someday_data(df, date, save_table):
sql = "delete from %s where date = %s" % (save_table, date)
ytrack.success("execute: %s" % sql)
try:
engine.execute(sql)
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s删除数据成功" % save_table)
try:
df.to_sql(save_table,
engine,
if_exists='append',
index=True,
index_label='code')
except:
ytrack.fail(traceback.format_exc())
else:
ytrack.success("%s 成功更新 %s 条记录." % (save_table, df.shape[0]))
ytrack.success("start run_daily_hs_indexs(date=%s, code=%s, save=%s)" %
(date, code, save))
df1 = _update_ohlc_daily(date, code, 'hs_indexs_ohlc_daily')
if df1 is not None:
my_update_someday_data(df1, get_day_date(day), "hs_indexs_ohlc_daily")
else:
ytrack.success("hs_indexs_ohlc_daily 需要更新的数据为空")
df2 = _update_ohlc_weekly(date, code, 'hs_indexs_ohlc_weekly')
if df2 is not None:
my_update_someday_data(df2, get_week_date(day),
'hs_indexs_ohlc_weekly')
else:
ytrack.success("hs_indexs_ohlc_weekly 需要更新的数据为空")
df3 = _update_ohlc_monthly(date, code, 'hs_indexs_ohlc_monthly')
if df3 is not None:
my_update_someday_data(df3, get_month_date(day),
'hs_indexs_ohlc_monthly')
else:
ytrack.success("hs_indexs_ohlc_monthly 需要更新的数据为空")
macd_cols = ['date'] + ['ma%s' % i for i in range(5, 251, 5)]
df4 = _update_macd_daily(date, code, 'hs_indexs_ohlc_daily')
if df4 is not None:
df4 = df4[macd_cols]
my_update_someday_data(df4, get_day_date(day), 'hs_indexs_macd_daily')
else:
ytrack.success("hs_indexs_macd_daily 需要更新的数据为空")
df5 = _update_macd_weekly(date, code, 'hs_indexs_ohlc_weekly')
if df5 is not None:
df5 = df5[macd_cols]
my_update_someday_data(df5, get_week_date(day),
'hs_indexs_macd_weekly')
else:
ytrack.success("hs_indexs_macd_weekly 需要更新的数据为空")
df6 = _update_macd_monthly(date, code, 'hs_indexs_ohlc_monthly')
if df6 is not None:
df6 = df6[macd_cols]
my_update_someday_data(df6, get_month_date(day),
'hs_indexs_macd_monthly')
else:
ytrack.success("hs_indexs_macd_monthly 需要更新的数据为空")
ynotice.send(ytrack.get_logs(),
style='stock',
title='%s-沪深指数K线图更新' % get_day_date(day))