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 trade_cal(force=False):
'''
http://218.244.146.57/static/calAll.csv
交易日历
isOpen=1是交易日，isOpen=0为休市
'''
dst = os.path.join(CURDIR, 'trade.cal.csv')
if os.path.exists(dst) and not force:
df = pd.read_csv(dst, index_col=0)
df = df.set_index('calendarDate')
return df
def date2int(date):
y, m, d = date.split("/")
return int(y) * 10000 + int(m) * 100 + int(d)
for _ in range(5):
try:
df = pd.read_csv('http://218.244.146.57/static/calAll.csv')
df['calendarDate'] = df['calendarDate'].map(date2int)
df.to_csv(dst)
df = df.set_index('calendarDate')
ytrack.info('update trade_cal from network..')
return df
except Exception as e:
ytrack.error(traceback.format_exc())
def get_sina_goods():
from codes import add_params, GOODS_URL, goods, get_page, parse_df
for good in goods:
dst = "%s/sina_goods/%s.csv" % (CURDIR, good['breed'])
if os.path.exists(dst):
ytrack.error("%s exists.." % dst)
continue
url = add_params(GOODS_URL, good)
page = get_page(url)
if not page:
ytrack.fail('%s page is None' % good['name'])
continue
data = pd.DataFrame()
for i in range(1, page + 1):
good.update({"page": i})
url = add_params(GOODS_URL, good)
df = parse_df(url)
if df is None or len(df) == 0:
continue
else:
data = data.append(df, ignore_index=True)
if len(data) > 0:
data.columns = ['date', 'close', 'open', 'high', 'low', 'volume']
data.to_csv(dst)
ytrack.info("%s finished.." % good['name'])
else:
ytrack.error("%s len is 0")
ytrack.show()
def is_open_day(day):
'''
isOpen=1是交易日，isOpen=0为休市
'''
if isinstance(day, datetime.datetime):
day = day.year * 10000 + day.month * 100 + day.day
try:
isOpen = tradecal_df.loc[day]['isOpen']
return isOpen == 0
except Exception as e:
ytrack.error(traceback.format_exc())
return False
def get_code(symbol):
try:
a = engine.execute("select id from us_symbol_int where symbol='%s'" %
symbol)
aa = a.fetchall()
if not aa:
b = engine.execute(
"insert into us_symbol_int(symbol) values ('%s')" % symbol)
a = engine.execute(
"select id from us_symbol_int where symbol='%s'" % symbol)
aa = a.fetchall()
return aa[0][0]
except Exception as e:
ytrack.error(traceback.format_exc())
return 0
def _get_basics(force=False):
fle = os.path.join(CURDIR, 'stock.basics.csv')
if os.path.exists(fle) and not force:
df = pd.read_csv(fle, dtype=str)
df = df.set_index('code')
return df
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_stock_basics()
df.to_csv(fle, encoding="utf-8")
ytrack.info('update 股票列表 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
ytrack.info('成功 股票基本信息 from network..')
return df
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))
if __name__ == "__main__":
try:
run_daily()
except Exception as e:
ytrack.error(traceback.format_exc())
ynotice.send(ytrack.get_logs(),
style='stock',
title=u'us run daily error.')
def update_stock_info():
trade_cal(True)
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_stock_basics()
fle = os.path.join(CURDIR, 'stock.basics.csv')
df.to_csv(fle, encoding="utf-8")
df = df.set_index(df.index.astype(int))
df.to_sql('stock_list',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 股票列表 save to sql')
time.sleep(0.1)
df = ts.get_today_all()
fle = os.path.join(CURDIR, 'stock.today.csv')
df.to_csv(fle, encoding="utf-8")
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_today',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update stock_today')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_industry_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_industry',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 股票行业 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
# ytrack.info('成功 股票基本信息 from network..')
# ynotice.send(ytrack.get_logs(), style='stock', title='更新股票基础数据和交易日期')
# return
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_concept_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_concept',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 股票概念 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_area_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_area',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 股票地域 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_sme_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_sme',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 中小板股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_gem_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_gem',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update 创业板股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_st_classified()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_st',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update st股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_hs300s()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_hs300',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update hs300股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_sz50s()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_sz50s',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update sz50s股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
for _ in range(5):
try:
time.sleep(0.1)
df = ts.get_zz500s()
df = df.set_index('code')
df = df.set_index(df.index.astype(int))
df.to_sql('stock_zz500s',
engine,
if_exists='replace',
index=True,
index_label='code')
ytrack.info('update zz500s股票 save to sql')
break
except Exception as e:
ytrack.error(traceback.format_exc())
ytrack.info('成功 股票基本信息 from network..')
ynotice.send(ytrack.get_logs(), style='stock', title='更新股票基础数据和交易日期')