def load_data_to_db():
format_files()
path = 'C:/temp/stk_price/'
for filename in os.listdir(path):
stock = open(path + filename,
mode='r',
encoding=None,
errors=None,
newline=None,
closefd=True,
opener=None)
hisprice = stock.readlines()
for j in range(0, len(hisprice)):
istsql = 'insert into data.stk_price (date,symbol,close,high,low,open,chgrate,volume,mktcap,trade_mktcap) values ' + hisprice[
j]
# print(istsql)
try:
fill_data(istsql)
except Exception as e:
print(e)
print(istsql)
print(filename + ' is loaded into database.')
stock.close()
try:
os.remove(path + filename)
print(filename + ' is deleted.')
except Exception as e:
print(e)
print('''########################################''' + '\n' +
' data.stk_price is updated to latest status.' + '\n' +
'''########################################''')
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_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 UpdateFutureData(symbol_m, symbol, stime, etime):
fur_data = history(symbol=symbol_m, frequency='60s', start_time = stime, end_time = etime, fields='open,high,low,close,volume,position,bob', df = 'True')
if len(fur_data) > 0:
for i in range(0, len(fur_data)):
data_string = str(list(fur_data.iloc[i])).replace('[Timestamp(','(\'' + symbol + '\',' ).replace(')','').replace(']',');')
insert_sql = 'insert into data.fur_price_1m (symbol, datetime, close, high, low, open, position, volume) values' + data_string
try:
fill_data(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
def update_fur(symbol):
fur_data = w.wsi(symbol, "open,close,high,low", "2018-07-30 09:27:00", "2018-08-27 15:00:00", "periodstart=09:30:00;periodend=15:00:00")
# fur_data = w.wsi(symbol, "open,close,high,low", "2018-07-20 09:30:00", "2018-07-20 15:00:00","periodstart=09:30:00;periodend=15:00:00")
for i in range(len(fur_data.Times)):
temp_price = []
for j in range(len(fur_data.Fields)):
temp_price.append(fur_data.Data[j][i])
temp_price = str(temp_price).replace('[','').replace(']','')
insert_sql = 'insert into data.fur_price values(\'' + fur_data.Codes[0] + '\',\'' + str(fur_data.Times[i]) + '\',' + temp_price + ');'
fill_data(insert_sql)
print(fur_data.Codes[0] + ' is inserted')
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 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 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 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.')
def update_adj_price(table, type):
def fill_adj_stk(table):
items = 'symbol, date'
endDate = (datetime.now().date() +
timedelta(days=-1)).strftime('%Y%m%d')
startDate = (datetime.now().date() +
timedelta(days=-5)).strftime('%Y%m%d')
tables = 'idx_price'
symbol_list = ['000001.SH']
db_data = get_data(items, tables, symbol_list, startDate, endDate)
date_list = []
for i in range(0, len(db_data)):
date_list.append(db_data[i][1])
items2 = 'symbol, min(date), max(date)'
table2 = table
condition = ' where date >= \'' + startDate + '\' and date <=\'' + endDate + '\' 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])
if index_start >= index_end:
continue
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')
if startDate3 > endDate3:
continue
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:
fill_data(insert_sql)
except Exception as e:
print(e)
print(symbol_list3 + 'is filled.')
items = 'symbol, max(date) as date'
tables = table
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
edate = datetime.now().date().strftime('%Y-%m-%d')
for k in range(0, len(data_info)):
sdate = (data_info[k][1] + timedelta(1)).strftime('%Y-%m-%d')
if sdate > edate:
continue
try:
pricedata = ts.get_k_data(data_info[k][0].replace('.SH',
'').replace(
'.SZ', ''),
ktype='D',
autype=type,
start=sdate,
end=edate)
del pricedata['volume']
except Exception as e:
print(e)
for h in range(0, len(pricedata)):
if data_info[k][0].startswith('6'):
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.SH\'')
else:
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.SZ\'')
insql = 'insert into data.' + table + ' (date,open,close,high,low,symbol) values (' + values + ');'
# print(insql)
try:
fill_data(insql)
except Exception as e:
print(e)
print(str(data_info[k][0]) + ' is inserted')
fill_adj_stk(table)
def fill_stk_data():
load_data_to_db()
items = 'symbol, date'
endDate = (datetime.now().date() +
timedelta(days=-1)).strftime('%Y%m%d')
startDate = (datetime.now().date() +
timedelta(days=-5)).strftime('%Y%m%d')
table = 'idx_price'
symbol_list = ['000001.SH']
db_data = get_data(items, table, symbol_list, startDate, endDate)
date_list = []
for i in range(0, len(db_data)):
date_list.append(db_data[i][1])
items2 = 'symbol, min(date), max(date)'
table2 = 'stk_price'
condition = ' where date >= \'' + startDate + '\' and date <=\'' + endDate + '\' 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, mktcap, trade_mktcap '
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')
if startDate3 > endDate3:
continue
stk_data = get_data(item3, table3, symbol_list3, startDate3,
endDate3)
date_stk = []
close_stk = {}
mkt_stk = {}
trade_mkt_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]
mkt_stk[stk_data[b][0]] = stk_data[b][2]
trade_mkt_stk[stk_data[b][0]] = stk_data[b][3]
fill_stk = {}
fill_mkt_stk = {}
fill_trade_mkt_stk = {}
fill_stk[date_list_idx[0]] = close_stk[date_list_idx[0]]
fill_mkt_stk[date_list_idx[0]] = mkt_stk[date_list_idx[0]]
fill_trade_mkt_stk[date_list_idx[0]] = trade_mkt_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]]
fill_mkt_stk[date_list_idx[c]] = mkt_stk[date_list_idx[c]]
fill_trade_mkt_stk[date_list_idx[c]] = trade_mkt_stk[
date_list_idx[c]]
else:
log.info(date_list_idx[c])
fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]]
fill_mkt_stk[date_list_idx[c]] = fill_mkt_stk[
date_list_idx[c - 1]]
fill_trade_mkt_stk[date_list_idx[c]] = fill_trade_mkt_stk[
date_list_idx[c - 1]]
for d in date_list_idx:
if d in date_stk:
fill_stk.pop(d)
fill_mkt_stk.pop(d)
fill_trade_mkt_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])) + '\',\'0\',\'0\',\'' + str(
fill_mkt_stk[e]) + '\',\'' + str(
fill_trade_mkt_stk[e]) + '\');'
# print(insert_sql)
try:
fill_data(insert_sql)
except Exception as e:
print(e)
print(symbol_list3 + 'is filled.')
fill_stk_data()
