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.')