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()