def update_idx_price_tec():
items = 'symbol, max(date)'
table = 'idx_price_tec'
condition = ' group by symbol order by symbol'
idx_data = get_all_data(items, table, condition)
idx_info = dict(idx_data)
symbols = list(idx_info.keys())
for j in symbols:
items = 'date, close'
table = 'idx_price'
condition = ' where symbol = \'' + j + '\' order by date desc limit 120'
idx_data = get_all_data(items, table, condition)
idx_price = dict(idx_data)
df_price = pd.DataFrame(list(idx_price.values()),
columns=['close'],
index=idx_price.keys())
df_price.sort_index(inplace=True)
ma_list = [5, 10, 20, 30, 60, 120]
for ma in ma_list:
# df_price['ema' + str(ma)] = pd.ewma(df_price['close'], span=ma)
df_price['ma' + str(ma)] = df_price['close'].rolling(
window=ma, center=False).mean()
df_price = df_price.drop(columns=['close'])
for h in range(0, len(df_price)):
if df_price.index[h] <= idx_info[j]:
continue
insert_sql = 'insert into data.idx_price_tec values(\'' + j + '\',\'' + df_price.index[
h].strftime('%Y-%m-%d') + '\', ' + str(list(
df_price.iloc[h])).replace('[', '').replace(']', ');')
fill_data(insert_sql)
print(j + ' is inserted in idx_price_tec')
def get_stock_list(current_date):
## step 1, 过滤次新股、黑名单股和东三省的股票
pre_year_date = (datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d')
items = 'symbol'
table = 'stk_info'
condition = 'where ipo < \'' + pre_year_date + '\' and symbol not in (select symbol from data.b_list) and area not in (\'黑龙江\',\'吉林\',\'辽宁\') order by symbol'
symbol_data = get_all_data(items, table, condition)
stock_list = []
for i in symbol_data:
stock_list.append(i[0])
## step 2, 过滤小市值股票，市值不能低于150亿(小公司，知名度低，公开性和公平度相对较差，商业护城河较差，成长性也不高，交易流动性一般也较低）,以及交易量为0的股票
mkt_value = 150
items = 'symbol'
table = 'stk_price'
condition = 'where date = \'' + current_date + '\' and symbol in (' + str(stock_list).replace('[','').replace(']','') + ') and mktcap > \'' + str(mkt_value*100000000) + '\' and volume <> 0 order by symbol'
symbol_data_mkt = get_all_data(items, table, condition)
stock_list_mkt = []
for i in symbol_data_mkt:
stock_list_mkt.append(i[0])
## step3, 过滤巨大值(不超过100）或者负值的PE，PB
items = 'symbol'
table = 'stk_ratio'
condition = 'where date = \'' + current_date + '\' and symbol in (' + str(stock_list_mkt).replace('[', '').replace(']','') + ') and pe_ttm>0 and pe_ttm<100 and pb>0 and pb<100 order by symbol'
symbol_data_be = get_all_data(items, table, condition)
stock_list_be = []
for i in symbol_data_be:
stock_list_be.append(i[0])
return(stock_list_be)
def get_etf_list(PERIOD):
items = 'date, close'
tables = 'idx_price'
condition = ' where symbol = \'399300.SZ\' and date between \'' + start_date + '\' and \'' + end_date + '\' order by date'
price_info = get_all_data(items, tables, condition)
idx_price = dict(price_info)
df_price = pd.DataFrame(list(idx_price.values()),
columns=['399300.SZ'],
index=idx_price.keys())
idx_down = df_price[:-1]
idx_up = df_price[1:]
idx_down.index = idx_up.index
idx_chg = (idx_up - idx_down) * 100 / idx_down
idx_chg = idx_chg[PERIOD - 1:]
for symbol in universe:
items = 'date, close'
tables = 'etf_price'
condition = ' where symbol = \'' + symbol + '\' and date between \'' + start_date + '\' and \'' + end_date + '\' order by date'
price_info = get_all_data(items, tables, condition)
temp_price = dict(price_info)
temp_df = pd.DataFrame(list(temp_price.values()),
columns=[symbol],
index=temp_price.keys())
df_price = df_price.merge(temp_df,
how='left',
left_index=True,
right_index=True)
df_down = df_price[:-PERIOD]
df_up = df_price[PERIOD:]
df_down.index = df_up.index
date_list = list(df_up.index)
df_chg = (df_up - df_down) * 100 / df_down
# idx_chg = df_chg['399300.SZ']
df_chg = df_chg.drop(columns=['399300.SZ'])
# df_chg.iloc[0].sort_values(ascending=False)[0:3]
raw_list = {}
for date_k in df_chg.index:
dict_temp = dict(
df_chg.loc[date_k].sort_values(ascending=False)[0:TOP])
raw_list[date_k.strftime('%Y-%m-%d')] = dict_temp
etf_list = {}
for i in raw_list:
remove_list = []
for j in raw_list[i]:
if np.isnan(float(raw_list[i][j])) or raw_list[i][j] < PERCENT:
remove_list.append(j)
for k in remove_list:
raw_list[i].pop(k)
etf_list[i] = raw_list[i]
return (etf_list, idx_chg, date_list)
def UpdateList(start_time, end_time):
n_days = int((datetime.strptime(end_time,'%Y-%m-%d %H:%M:%S') - datetime.strptime(start_time,'%Y-%m-%d %H:%M:%S')).days/15 + 1)
items = 'symbol'
table = 'fur_info'
condition = ' where symbol not like \'SP%\' having length(symbol) > 3 order by symbol'
symbol_info = get_all_data(items, table, condition)
symbol_list = str(symbol_info).replace('(','').replace('),','').replace(',))','').replace('\'','').split(', ')
items = 'code, market'
table = 'fur_td_info'
condition = ' order by code'
code_info = get_all_data(items, table, condition)
code_list = dict(code_info)
for symbol in symbol_list:
remove_digits = str.maketrans('', '', digits)
symbol_type = symbol.translate(remove_digits)
symbol_mkt = code_list[symbol_type]
if symbol_mkt == 'CFE':
symbol_m = 'CFFEX.' + symbol.lower()
elif symbol_mkt == 'SHF':
symbol_m = 'SHFE.' + symbol.lower()
elif symbol_mkt == 'CZC':
symbol_m = 'CZCE.' + symbol.lower()
else:
symbol_m = symbol_mkt + '.' + symbol.lower()
stime = start_time
for n in range(1, n_days+1):
etime = (datetime.strptime(start_time,'%Y-%m-%d %H:%M:%S') + timedelta(days=15*n)).strftime('%Y-%m-%d %H:%M:%S')
if etime > end_time:
etime = end_time
print(symbol + ': ' + stime + ' ~ ' + etime)
UpdateFutureData(symbol_m,symbol,stime,etime)
stime = etime
symbols_cm = ['CFFEX.IC','CFFEX.IF','CFFEX.IH','CFFEX.T','CFFEX.TF','CZCE.CF','CZCE.FG','CZCE.JR','CZCE.LR','CZCE.MA','CZCE.OI','CZCE.PM','CZCE.RI','CZCE.RM','CZCE.RS','CZCE.SF','CZCE.SM','CZCE.SR','CZCE.TA','CZCE.WH','CZCE.ZC','DCE.A','DCE.B','DCE.BB','DCE.C','DCE.CS','DCE.FB','DCE.I','DCE.J','DCE.JD','DCE.JM','DCE.L','DCE.M','DCE.P','DCE.PP','DCE.V','DCE.Y','SHFE.AG','SHFE.AL','SHFE.AU','SHFE.BU','SHFE.CU','SHFE.FU','SHFE.HC','SHFE.NI','SHFE.PB','SHFE.RB','SHFE.RU','SHFE.SN','SHFE.WR','SHFE.ZN']
symbols_c = ['IC','IF','IH','T','TF','CF','FG','JR','LR','MA','OI','PM','RI','RM','RS','SF','SM','SR','TA','WH','ZC','A','B','BB','C','CS','FB','I','J','JD','JM','L','M','P','PP','V','Y','AG','AL','AU','BU','CU','FU','HC','NI','PB','RB','RU','SN','WR','ZN']
for i in range(0,len(symbols_cm)):
stime = start_time
for n in range(1, n_days+1):
etime = (datetime.strptime(start_time,'%Y-%m-%d %H:%M:%S') + timedelta(days=15*n)).strftime('%Y-%m-%d %H:%M:%S')
if etime > end_time:
etime = end_time
print(symbols_cm[i] + ': ' + stime + ' ~ ' + etime)
UpdateFutureData(symbols_cm[i],symbols_c[i],stime,etime)
stime = etime
def get_stk(symbol, start_time, end_time):
if symbol.startswith('SHSE'):
sym = symbol.replace('SHSE.', '') + '.SH'
else:
sym = symbol.replace('SZSE.', '') + '.SZ'
items = 'date, open, high, low, close' #, volume'
table = ' stk_price_forward'
condition = 'where symbol = \'' + sym + '\' and date >= \'' + start_time + '\' and date <= \'' + end_time + '\' order by date asc'
symbol_data = get_all_data(items, table, condition)
k_data = pd.DataFrame(list(symbol_data),
columns=['datetime', 'open', 'high', 'low',
'close']) #, 'volume'])
k_data.set_index(["datetime"], inplace=True)
k_data = k_data.astype('float64')
k_data = k_data.reset_index(["datetime"])
return (k_data)
# n = 60
# k = 0
# symbol = 'SHSE.601318'
# start_time = '2018-01-01'
# end_time = '2018-12-31'
# df_k = get_stk(symbol, start_time, end_time)
# print(df_k)
#
# df = get_k_stk(symbol, 60,0 , start_time, end_time)
# print(df)
def get_data(symbol, table):
items = 'max(date) as date'
table = table
condition = ' where symbol = \'' + symbol + '\''
db_data = get_all_data(items, table, condition)
db_info = [symbol, table, db_data[0][0].strftime('%Y-%m-%d')]
return (db_info)
def update_stk_PEPB():
end_date = datetime.now().date().strftime('%Y-%m-%d')
items = 'symbol, max(date)'
tables = 'stk_ratio'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
ratio_data = dict(data_info)
for key in ratio_data:
if key.startswith('6'):
sym = 'SHSE.' + key.replace('.SH','')
else:
sym = 'SZSE.' + key.replace('.SZ', '')
start_date = (ratio_data[key] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
df_r = get_fundamentals(table='trading_derivative_indicator', symbols=sym, start_date=start_date, end_date=end_date,fields='PETTM, PB', df = 'True')
if len(df_r) > 0:
df_r = df_r.drop(['pub_date'], axis=1, inplace=False)
df_r= df_r.fillna('NULL')
for i in range(0, len(df_r)):
data_string = '\'' + key + '\',\'' + df_r.end_date.iloc[i].strftime('%Y-%m-%d') + '\',' + str(round(df_r.PETTM.iloc[i],3)) + ',' + str(round(df_r.PB.iloc[i],3)) + ');'
insert_sql = 'insert into data.stk_ratio values(' + data_string
try:
fill_data(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
print(sym + ' is updated')
def update_w_idx_price():
w.start()
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = 'idx_price'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
w_idx_data = dict(data_info)
end_date = datetime.now().date().strftime('%Y-%m-%d')
for i in w_idx_data:
start_date = (w_idx_data[i] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
w_data = w.wsd(i, "open,high,low,close,pct_chg,amt", start_date,
end_date, "")
# if len(w_data.Data) <= 1 or w_data.Data[0][1] == None:
# continue
for s in range(0, len(w_data.Times)):
temp = []
temp.append(i)
temp.append(w_data.Times[s].strftime('%Y-%m-%d'))
for r in range(0, len(w_data.Fields)):
temp.append(w_data.Data[r][s])
insert_sql = 'insert into data.idx_price values(' + str(
temp).replace('[', '').replace(']', '') + ');'
# print(insert_sql)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(i + ' is inserted. ')
conn.commit()
connClose(conn, cur)
def main():
items = 'max(date)'
table = 'idx_price'
condition = ' where symbol =\'000001.SH\' group by symbol'
idx_data = get_all_data(items, table, condition)
currentDate = idx_data[0][0].strftime('%Y-%m-%d')
pf_test = []
# 设定换仓阈值
g = 0.145
m = 0.045
# for m in range(5, 100, 5):
# for g in range(m, 200, 5):
start_date = '2018-04-20'
end_date = currentDate
# end_date = '2016-01-31'
# total_return, annualized_return, max_draw_down = calc_gm(g/1000, m/1000, start_date, end_date)
# pf_test.append([start_date, end_date, g/1000, m/1000, total_return, annualized_return, max_draw_down])
total_return, annualized_return, max_draw_down, df = calc_gm(
g, m, start_date, end_date)
pf_test.append(
[start_date, end_date, total_return, annualized_return, max_draw_down])
print(df.iloc[-1])
return (df.iloc[-1])
def Get_Future_Price():
conn, cur = connDB()
items = 'a.symbol,b.datetime'
table = 'fur_info a left join (select symbol, max(datetime) as datetime from data.fur_price_5m group by symbol) b on a.symbol=b.symbol'
condition = 'order by a.symbol asc'
list_data = get_all_data(items, table, condition)
symbol_datetime = dict(list_data)
for symbol in symbol_datetime.keys():
if symbol.startswith('IF') or symbol.startswith(
'IC') or symbol.startswith('IH') or symbol.startswith(
'TF') or symbol.startswith('TS') or symbol.startswith(
'T1'):
symbol_url = 'http://stock2.finance.sina.com.cn/futures/api/json.php/CffexFuturesService.getCffexFuturesMiniKLine5m?symbol=' + symbol
else:
symbol_url = 'http://stock2.finance.sina.com.cn/futures/api/json.php/IndexService.getInnerFuturesMiniKLine5m?symbol=' + symbol
symbol_page = urllib.request.urlopen(symbol_url).read().decode('utf-8')
symbol_data = symbol_page.replace('[[', '').replace(']]',
'').split('],[')
for k in symbol_data:
if symbol_datetime[symbol] is not None:
if k[1:20] <= symbol_datetime[symbol].strftime(
'%Y-%m-%d %H:%M:%S'):
continue
insert_sql = 'insert into data.fur_price_5m values(\'' + symbol + '\',' + k + ')'
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
conn.commit()
connClose(conn, cur)
def update_stk_adj_factor():
w.start()
conn, cur = connDB()
items = 'symbol, max(date)'
tables = 'stk_adj_factor'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
stk_data = dict(data_info)
end_date = datetime.now().date().strftime('%Y-%m-%d')
for i in stk_data:
start_date = (stk_data[i] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
w_data = w.wsd(i, "adjfactor", start_date, end_date, "")
for s in range(0, len(w_data.Times)):
insert_sql = 'insert into data.stk_adj_factor values(\'' + i + '\', \'' + w_data.Times[
s].strftime('%Y-%m-%d') + '\',\'' + str(
w_data.Data[0][s]) + '\');'
# print(insert_sql)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(i + ' is inserted in stk_adj_factor. ')
conn.commit()
connClose(conn, cur)
def update_etf_price():
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = 'etf_price'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
end = datetime.now().date().strftime('%Y-%m-%d')
for k in range(0, len(data_info)):
start = (data_info[k][1] + timedelta(days=1)).strftime('%Y-%m-%d')
if start > end:
continue
pricedata = ts.get_k_data(data_info[k][0].replace('.OF', ''),
ktype='D',
start=start,
end=end)
for h in range(0, len(pricedata)):
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.OF\'')
insql = 'insert into data.etf_price (date,open,close,high,low,volume,symbol) values (' + values + ');'
# print(insql)
try:
conn.cursor().execute(insql)
except Exception as e:
print(e)
conn.commit()
log.info(str(data_info[k][0]) + ' is inserted in etf_price')
connClose(conn, cur)
def get_symbols(table):
items = 'symbol'
condition = ' group by symbol order by symbol'
symbol_list = []
symbol_data = get_all_data(items, table, condition)
for item in symbol_data:
symbol_list.append(item[0])
return(symbol_list)
def get_time():
items = 'max(dtime)'
tables = 'data.td_price_1m'
condition = ' '
date_info = get_all_data(items, tables, condition)
start_time = date_info[0][0].strftime('%Y-%m-%d %H:%M:%S')
end_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
return (start_time, end_time)
def get_info(divSet7):
items = 'symbol, name, industry'
table = 'stk_info'
condition = 'where symbol in ' + str(divSet7).replace('[', '(').replace(
']', ')')
stk_data = get_all_data(items, table, condition)
df = pd.DataFrame(list(stk_data), columns=['symbol', 'name', 'industry'])
df = df.set_index('symbol')
return (df)
def get_b_list():
items = 'symbol'
table = 'b_list'
condition = 'order by symbol'
symbol_data = get_all_data(items, table, condition)
b_list = []
for i in symbol_data:
b_list.append(i[0])
return (b_list)
def fill_ratio(table):
item = 'symbol, min(date), max(date)'
table = table
condition = ' where symbol > \'600757.SH\' and date >= \'2010-01-01\' and date < \'2018-03-15\' group by symbol order by symbol'
db_data = get_all_data(item, table, condition)
for a in range(0, len(db_data)):
index_start = date_list.index(db_data[a][1])
index_end = date_list.index(db_data[a][2]) + 1
date_list_idx = date_list[index_start:index_end]
item2 = 'date, pe_ttm, pb'
table2 = table
symbol_list2 = '\'' + db_data[a][0] + '\''
startDate2 = db_data[a][1].strftime('%Y-%m-%d')
endDate2 = db_data[a][2].strftime('%Y-%m-%d')
stk_data = get_data(item2, table2, symbol_list2, startDate2, endDate2)
date_stk = []
pe_ttm_stk = {}
pb_stk = {}
for b in range(0, len(stk_data)):
date_stk.append(stk_data[b][0])
pe_ttm_stk[stk_data[b][0]] = stk_data[b][1]
pb_stk[stk_data[b][0]] = stk_data[b][2]
fill_stk = {}
fill_stk[date_list_idx[0]] = [
float(pe_ttm_stk[date_list_idx[0]]),
float(pb_stk[date_list_idx[0]])
]
for c in range(1, len(date_list_idx)):
if date_list_idx[c] in date_stk:
fill_stk[date_list_idx[c]] = [
float(pe_ttm_stk[date_list_idx[c]]),
float(pb_stk[date_list_idx[c]])
]
else:
fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]]
for d in date_stk:
if d in date_list_idx:
fill_stk.pop(d)
for e in fill_stk:
insert_sql = 'insert into data.' + table2 + ' values (' + symbol_list2 + ',\'' + str(
e) + '\',' + str(fill_stk[e]).replace('[', '').replace(
']', '') + ');'
try:
cur.execute(insert_sql)
except Exception as e:
print(e)
log.info(symbol_list2 + 'is filled.')
def download_financial_data():
items = 'symbol'
table = 'stk_info'
condition = ' order by symbol'
symbol_list = []
symbol_data = get_all_data(items, table, condition)
for i in symbol_data:
symbol_list.append(i[0])
file_list = []
while len(symbol_list) != len(file_list):
file_list = file_name()
download(symbol_list, file_list)
def get_data(symbol, start_time, end_time):
items = 'datetime, open, high, low, close, volume'
table = ' fur_price_1m'
condition = 'where symbol = \'' + symbol + '\' and datetime >= \'' + start_time + '\' and datetime <= \'' + end_time + '\' order by datetime asc'
symbol_data = get_all_data(items, table, condition)
k_data = pd.DataFrame(list(symbol_data), columns=['datetime', 'open', 'high', 'low', 'close', 'volume'])
k_data.set_index(["datetime"], inplace=True)
k_days = list(k_data.index.strftime('%Y-%m-%d'))
# k_days = list(k_data.index[:10])
k_days = sorted(set(k_days), key=k_days.index)
return (k_data.astype('float64'), k_days)
def calc_atr(j):
# for j in symbols:
items = 'date, high, low, close'
table = 'stk_price_forward'
condition = ' where symbol=\'' + j + '\' order by date desc limit 200'
stk_data = get_all_data(items, table, condition)
date_list = []
high = []
low = []
close = []
for i in stk_data:
date_list.append(i[0].strftime('%Y-%m-%d'))
high.append(i[1])
low.append(i[2])
close.append(i[3])
df = pd.DataFrame({
'high': high,
'low': low,
'close': close
},
index=date_list)
df.sort_index(inplace=True)
# Average True Range
n = 13
i = 0
TR_l = [0]
while i < len(df.index) - 1:
TR = max(df['high'].iloc[i + 1], df['close'].iloc[i]) - min(
df['low'].iloc[i + 1], df['close'].iloc[i])
TR_l.append(TR)
i = i + 1
TR_s = pd.DataFrame(TR_l, columns=['TR'], index=df.index)
# ATR = pd.Series(pd.ewma(TR_s, span=n, min_periods=n), name='ATR_' + str(n))
df['atr' + str(n)] = TR_s['TR'].ewm(span=n,
min_periods=0,
adjust=True,
ignore_na=False).mean()
df['atr' + str(n + 8)] = TR_s['TR'].ewm(span=n + 7,
min_periods=0,
adjust=True,
ignore_na=False).mean()
# df = df.join(ATR)
for h in range(len(df) - 5, len(df)):
insert_sql = 'update data.stk_price_tec set atr13 =\'' + str(
df['atr13'].iloc[h]) + '\' , atr21 = \'' + str(
df['atr21'].iloc[h]
) + '\' where symbol =\'' + j + '\' and date = \'' + df.index[
h] + '\';'
fill_data(insert_sql)
print(j + '\'s ATR data are updated.')
def get_units(future_list):
items_unit = 'code, unit * min_chg as units'
items_deposit = 'code, unit * deposit as deposit'
items_endtime = 'code, td_time'
table = 'fur_td_info'
condition = 'order by code asc'
code_unit = dict(get_all_data(items_unit, table, condition))
code_deposit = dict(get_all_data(items_deposit, table, condition))
code_endtime = dict(get_all_data(items_endtime, table, condition))
future_units = {}
future_deposit = {}
future_endtime = {}
for symbol in future_list:
if len(symbol) == 6:
future_units[symbol] = float(code_unit[symbol[0:2].upper()])
future_deposit[symbol] = float(code_deposit[symbol[0:2].upper()])
future_endtime[symbol] = code_endtime[symbol[0:2].upper()]
else:
future_units[symbol] = float(code_unit[symbol.upper()])
future_deposit[symbol] = float(code_deposit[symbol.upper()])
future_endtime[symbol] = code_endtime[symbol.upper()]
return (future_units, future_deposit, future_endtime)
def get_current_stocks(currentDate):
stocks= get_stock_list(currentDate)
items = 'a.symbol'
table = 'stk_price_forward a inner join stk_price_tec b on a.symbol =b.symbol and a.date=b.date'
condition = ' where a.symbol in (' + str(stocks).replace('[','').replace(']','') + ') and a.date = \'' + currentDate + '\' and (0.5 * a.close + 0.3 * a.open + 0.1* a.high + 0.1 * a.low) > b.ema21 and (0.5 * a.close + 0.3 * a.open + 0.1* a.high + 0.1 * a.low) > b.ema55 and (0.5 * a.close + 0.3 * a.open + 0.1* a.high + 0.1 * a.low) > b.ema5'
symbol_data = get_all_data(items, table, condition)
symbol_list = []
if len(symbol_data) > 1:
for i in symbol_data:
symbol_list.append(i[0])
return(symbol_list)
def update_stk_price_gm():
items = 'symbol, max(date) as date'
table = 'stk_price'
condition = ' group by symbol order by symbol'
date_info = dict(get_all_data(items, table, condition))
edate = datetime.now().date().strftime('%Y-%m-%d')
for symbol, v in date_info.items():
if symbol.startswith('6'):
sym = 'SHSE.' + symbol.replace('.SH', '')
else:
sym = 'SZSE.' + symbol.replace('.SZ', '')
sdate = v.strftime('%Y-%m-%d')
stk_data1 = history(symbol=sym,
frequency='1d',
start_time=sdate,
end_time=edate,
fields='open,high,low,close,volume,bob',
fill_missing='Last',
adjust=0,
df='True')
if len(stk_data1) == 0:
continue
stk_data2 = get_fundamentals(table='trading_derivative_indicator',
symbols=sym,
start_date=sdate,
end_date=edate,
fields='TOTMKTCAP, NEGOTIABLEMV',
df='True')
stk_data = pd.concat([stk_data1, stk_data2], axis=1)
stk_data['chg_rate'] = (
stk_data.close - stk_data.close.shift(1)) / stk_data.close.shift(1)
stk_data = stk_data.dropna()
try:
stk_data = stk_data.drop(['pub_date', 'end_date', 'symbol'],
axis=1)
except Exception as e:
pass
stk_data = stk_data.round(4)
for i in range(0, len(stk_data)):
insert_str = 'insert into data.stk_price(symbol,date,close,high,low,open,volume,mktcap,trade_mktcap,chgrate) values(\'' + symbol + '\',' + str(
list(stk_data.iloc[i])).replace('[Timestamp(', '').replace(
')', '').replace(']', ')')
try:
fill_data(insert_str)
except Exception as e:
print(e)
print(insert_str)
print(symbol + ' : price is updated from GM')
def fill_adj_stk(table):
items2 = 'symbol, min(date), max(date)'
table2 = table
condition = ' group by symbol order by symbol'
db_data2 = get_all_data(items2, table2, condition)
for a in range(0, len(db_data2)):
index_start = date_list.index(db_data2[a][1])
index_end = date_list.index(db_data2[a][2]) + 1
date_list_idx = date_list[index_start:index_end]
item3 = 'date, close'
table3 = table2
symbol_list3 = '\'' + db_data2[a][0] + '\''
startDate3 = db_data2[a][1].strftime('%Y-%m-%d')
endDate3 = db_data2[a][2].strftime('%Y-%m-%d')
stk_data = get_data(item3, table3, symbol_list3, startDate3, endDate3)
date_stk = []
close_stk = {}
for b in range(0, len(stk_data)):
date_stk.append(stk_data[b][0])
close_stk[stk_data[b][0]] = stk_data[b][1]
fill_stk = {}
fill_stk[date_list_idx[0]] = close_stk[date_list_idx[0]]
for c in range(1, len(date_list_idx)):
if date_list_idx[c] in date_stk:
fill_stk[date_list_idx[c]] = close_stk[date_list_idx[c]]
else:
fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]]
for d in date_stk:
if d in date_list_idx:
fill_stk.pop(d)
for e in fill_stk:
insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str(
e) + '\',\'' + str(float(fill_stk[e])) + '\',\'' + str(
float(fill_stk[e])) + '\',\'' + str(float(
fill_stk[e])) + '\',\'' + str(float(
fill_stk[e])) + '\');'
try:
cur.execute(insert_sql)
except Exception as e:
print(e)
log.info(symbol_list3 + 'is filled.')
def main():
# currentDate = (datetime.now() - timedelta(1)).strftime('%Y-%m-%d')
items = 'max(date)'
table = 'idx_price'
condition = ' where symbol =\'000001.SH\' group by symbol'
idx_data = get_all_data(items, table, condition)
currentDate = idx_data[0][0].strftime('%Y-%m-%d')
# currentDate='2018-06-15'
stk_list, all_list = stock_list(currentDate)
b_list = get_b_list()
stk_list = list(set(stk_list).difference(set(b_list)))
stk_data = get_data(stk_list)
tb = show_table(stk_data)
print(tb)
print(all_list)
return (str(tb) + '\n' + str(all_list))
def update_stk_adj_factor():
current_data = get_instruments(exchanges=['SHSE', 'SZSE'], sec_types=[1], fields='symbol,trade_date,adj_factor',df=False)
for i in current_data:
symbol = i['symbol']
if symbol.startswith('SHSE'):
symbol = symbol.replace('SHSE.', '') + '.SH'
else:
symbol = symbol.replace('SZSE.', '') + '.SZ'
insert_cd = 'insert into data.stk_adj_factor values(\'' + symbol + '\',\'' + i['trade_date'].strftime('%Y-%m-%d') + '\',' + str(round(i['adj_factor'],6)) + ');'
try:
fill_data(insert_cd)
except Exception as e:
print(e)
items = 'symbol, max(date)'
tables = 'stk_adj_factor'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
stk_data = dict(data_info)
end_date = datetime.now().date().strftime('%Y%m%d')
for j in stk_data:
start_date = (stk_data[j] + timedelta(1)).strftime('%Y%m%d')
if start_date > end_date:
continue
if j.startswith('6'):
sym = 'SHSE.' + j.replace('.SH', '')
else:
sym = 'SZSE.' + j.replace('.SZ', '')
gm_data = get_history_instruments(symbols=[sym], fields='symbol,trade_date,adj_factor', start_date=start_date,end_date=end_date, df=True)
if len(gm_data) == 0:
continue
for k in range(0, len(gm_data)):
insert_gm = 'insert into data.stk_adj_factor values(\'' + j + '\',\'' + gm_data['trade_date'][k].strftime('%Y-%m-%d') + '\',' + str(round(gm_data['adj_factor'][k], 6)) + ');'
try:
fill_data(insert_gm)
except Exception as e:
print(e)
# df = pro.adj_factor(ts_code=i, start_date=start_date, end_date=end_date) # trade_date='2018-08-10')
# for s in range(0, len(df)):
# insert_sql = 'insert into data.stk_adj_factor values(' + str(list(df.iloc[s])).replace('[','').replace(']','') + ');'
# # print(insert_sql)
# try:
# fill_data(insert_sql)
# except Exception as e:
# print(e)
print(j + ' is inserted in stk_adj_factor. ')
def get_data(k_list):
items = 'symbol, name, industry'
table = 'stk_info'
condition = ' where symbol in (' + str(k_list).replace('[', '').replace(
']', '') + ') order by symbol'
stk_data = get_all_data(items, table, condition)
k_str = str(k_list).replace('[', '').replace(']', '').replace(
'.SZ\'', '').replace('.SH\'', '').replace('\'00', 's_sz00').replace(
'\'30', 's_sz30').replace('\'60', 's_sh60').replace(' ', '')
request_url = 'http://qt.gtimg.cn/q=' + k_str
headers = {
'User-Agent': 'Mozilla/10 (compatible; MSIE 1.0; Windows NT 4.0)'
}
try:
request = urllib.request.Request(request_url, headers=headers)
response = urllib.request.urlopen(request).read().decode('gbk')
reobj = re.compile('v_s_s.*?~.*?~')
realtemp = reobj.sub('', response).replace('~~";', '').replace(
'\n', '~').strip().split('~')
# log.info(realtemp)
stk_price = {}
for i in range(0, len(realtemp) - 1, 8):
if realtemp[i].startswith('6'):
stk_price[realtemp[i] + '.SH'] = float(realtemp[i + 1])
else:
stk_price[realtemp[i] + '.SZ'] = float(realtemp[i + 1])
except Exception as e:
print(e)
stk_volume = {}
for j in stk_price:
stk_volume[j] = int(MONEY / len(stk_price) / stk_price[j] / 100) * 100
stk_info = []
for k in range(0, len(stk_data)):
temp = list(stk_data[k])
temp.insert(0, str(k + 1))
stk_info.append(temp)
stk_info[k].append(stk_price[stk_data[k][0]])
stk_info[k].append(stk_volume[stk_data[k][0]])
print('status, content = client.SendOrder(0, 4, sInvestorID_' +
stk_data[k][0][-2:] + ', "' + stk_data[k][0][0:6] + '", ' +
str(stk_price[stk_data[k][0]]) + ', ' +
str(stk_volume[stk_data[k][0]]) + ')')
return (stk_info)
def calc_ema(j):
items = 'datetime, close'
table = 'fur_price'
condition = ' where symbol = \'' + j + '\' order by datetime desc limit 240'
idx_data = get_all_data(items, table, condition)
idx_price = dict(idx_data)
df_price = pd.DataFrame(list(idx_price.values()), columns=['close'], index=idx_price.keys())
df_price.sort_index(inplace=True)
ma_list = [5, 21, 55]
for ma in ma_list:
df_price['ema' + str(ma)] = df_price['close'].ewm(span=ma, min_periods=0, adjust=True,
ignore_na=False).mean()
df_price = df_price.drop(columns=['close'])
for h in range(0, len(df_price)):
insert_sql = 'insert into data.fur_price_tec values(\'' + j + '\',\'' + df_price.index[h].strftime(
'%Y-%m-%d %H:%M:%S') + '\', ' + str(list(df_price.iloc[h])).replace('[', '').replace(']','') + ');'
fill_data(insert_sql)
def update_div_yield(symbol_list, start_date, end_date):
for symbol in symbol_list:
if symbol.startswith('6'):
sym = 'SHSE.' + symbol.replace('.SH', '')
else:
sym = 'SZSE.' + symbol.replace('.SZ', '')
items = 'rpt_date as date'
table = 'stk_fina_calc'
condition = ' where symbol = \'' + symbol + '\' and rpt_date between \'' + start_date + '\' and \'' + end_date + '\' and rpt_date is not null order by date asc'
r_data = get_all_data(items, table, condition)
rpt_date = pd.DataFrame(list(r_data), columns=['rpt'])
dy_data = get_all_gm(sym, start_date)
if len(dy_data) == 0 or 'DY' not in list(dy_data.columns):
continue
dy_data.drop(['end_date', 'symbol'], axis=1, inplace=True)
df = dy_data.set_index('pub_date')
begin_date = start_date
dy = 'NULL'
dy_list = []
for i in range(0, len(rpt_date)):
df_dy = df[begin_date:rpt_date.rpt[i]]
for j in range(0, len(df_dy)):
if pd.isna(df_dy.DY[j]):
# dy = 'NULL'
continue
else:
dy = round(df_dy.DY[j], 3)
dy_list.append([rpt_date.rpt[i], dy])
begin_date = rpt_date.rpt[i]
for k in dy_list:
update_dy = 'UPDATE data.stk_fina_calc SET div_yield = ' + str(
k[1]
) + ' where symbol = \'' + symbol + '\' and rpt_date = \'' + k[
0].strftime('%Y-%m-%d') + '\';'
try:
fill_data(update_dy)
except Exception as e:
print(e)
print(update_dy)
print(symbol + ': div_yield is updated.')
def fill_suspend_data_bf(tables):
for i in symbols:
if i.startswith('0') or i.startswith('3') or i.startswith('6'):
items = 'date, close'
table = tables
condition = ' where symbol=\'' + i + '\' order by date desc limit 1'
stk_data = get_all_data(items, table, condition)
if len(stk_data
) == 0 or stk_data[0][0] >= datetime.now().date():
continue
stk_date_list = date_list[date_list.index(stk_data[0][0]) + 1:]
for k in stk_date_list:
insert_sql = 'insert into data.' + tables + ' values(\'' + i + '\',\'' + k.strftime(
'%Y-%m-%d') + '\',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ');'
# print(insert_sql)
fill_data(insert_sql)
print(i + '\'s data are updated.')
