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 load_data_to_db(): format_files() path = 'C:/temp/stk_price/' conn, cur = connDB() 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: conn.cursor().execute(istsql) except Exception as e: print(e) print(istsql) conn.commit() print(filename + ' is loaded into database.') stock.close() try: os.remove(path + filename) print(filename + ' is deleted.') except Exception as e: print(e) connClose(conn, cur) print('''########################################''' + '\n' + ' data.stk_price is updated to latest status.' + '\n' + '''########################################''')
def update_cb_tdx(): path = 'C:\\tdx\\T0002\\export\\' conn, cur = connDB() files = os.listdir(path) for filename in files: stockprice = open(path + filename, mode='r', encoding=None, errors=None, newline=None, closefd=True, opener=None) content = stockprice.readlines() if len(content) <= 3: continue symbol = content[0][0:6] type = content[0].split(' ')[2][0] content.pop() for i in range(2,len(content)): m_data = content[i].strip().split(',') insert_sql = 'insert into data.cb_price (datetime,open,high,low,close,volume,symbol,type) VALUES(\''\ + m_data[0].replace('/','-') + ' ' + m_data[1][0:2] + ':' + m_data[1][2:] +':00\',' \ + str(m_data[2:7]).replace('[','').replace(']','') + ',\'' + symbol + '\',\'' + type +'\');' try: conn.cursor().execute(insert_sql) except Exception as e: print(insert_sql) print(e) conn.commit() print(symbol + '(' + str(files.index(filename)+1) + '/' + str(len(files)) + ') is inserted into data.cb_price') connClose(conn, cur)
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_cb(symbol_list, scale): conn, cur = connDB() for symbol in symbol_list: if symbol.startswith('12'): sym = 'sz' + symbol else: sym = 'sh' + symbol for s in scale: url = 'http://money.finance.sina.com.cn/quotes_service/api/json_v2.php/CN_MarketData.getKLineData?symbol=' + sym + '&scale=' + s +'&ma=no&datalen=50' response = rq.urlopen(url, timeout=20) r_data = response.read().decode('utf-8', 'ignore') if r_data == 'null' or len(r_data) <= 4: continue r_data = r_data.replace('[','').replace (']','').replace('{','{"').replace(':"','":"').replace('",','","').replace('},','},,').split(',,') for rec in r_data: insert_data = json.loads(rec) insert_sql = 'insert into data.cb_price (datetime,open,high,low,close,volume,symbol,type) VALUES(' + str(list(insert_data.values())).replace(']','').replace('[','') + ',\'' + symbol + '\',\'' + s + '\');' # print(insert_sql) try: conn.cursor().execute(insert_sql) except Exception as e: print(e) conn.commit() print(symbol + '(' + str(symbol_list.index(symbol)+1) + '/' + str(len(symbol_list)) +') is inserted into data.cb_price') time.sleep(3) 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 download_financial_data(): conn, cur = connDB() 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) connClose(conn, cur)
def get_hs300():# 获取沪深300交易日期和涨跌幅作为基准参考 conn, cur = connDB() query_sql = 'select date, chgrate from data.idx_price where symbol = \'399300.SZ\' and date between \'' + startDate + '\' and \'' + endDate + '\' order by date' try: cur.execute(query_sql) hs300_data = cur.fetchall() except Exception as e: print(e) trade_date = [] hs300_chgrate = [] for g in range(0, len(hs300_data)): trade_date.append(hs300_data[g][0]) hs300_chgrate.append(hs300_data[g][1]) connClose(conn, cur) return(trade_date, hs300_chgrate)
def Update_Symbol_List(): symbols_url = 'http://finance.sina.com.cn/iframe/futures_info_cff.js' url_page = urllib.request.urlopen(symbols_url).read().decode('gb2312') raw_list = [i for i in url_page.split() if i.startswith('Array(')] raw_list.pop(0) conn, cur = connDB() for j in raw_list: insert_sql = 'insert into data.fur_info values' + j.replace( 'Array', '') try: conn.cursor().execute(insert_sql) except Exception as e: print(e) print(insert_sql) conn.commit() connClose(conn, cur)
def load_financial_data(): for filename in os.listdir(path): with open(path + filename, newline='') as csvfile: reader_csv = csv.reader(csvfile) csv_content = {} for i in reader_csv: if i[0] != '\t\t': csv_key = i[0] csv_value = i[1:] csv_value.pop() csv_content[csv_key] = csv_value data_content = pd.DataFrame( csv_content, columns=[ '报告日期', '基本每股收益(元)', '每股净资产(元)', '每股经营活动产生的现金流量净额(元)', '净资产收益率加权(%)', '主营业务收入(万元)', '主营业务利润(万元)', '营业利润(万元)', '投资收益(万元)', '营业外收支净额(万元)', '利润总额(万元)', '净利润(万元)', '净利润(扣除非经常性损益后)(万元)', '经营活动产生的现金流量净额(万元)', '现金及现金等价物净增加额(万元)', '总资产(万元)', '流动资产(万元)', '总负债(万元)', '流动负债(万元)', '股东权益不含少数股东权益(万元)' ]) for j in range(0, data_content.index.size): # finance_value = [] finance_value = list(data_content.loc[j]) finance_value = [ 'NULL' if x == '--' else x for x in finance_value ] finance_value.insert(0, filename.replace('.csv', '')) insert_sql = 'insert into data.stk_finance values (' + str( finance_value).replace('[', '').replace(']', '').replace( '\'NULL\'', 'default') + ');' conn, cur = connDB() try: cur.execute(insert_sql) except Exception as e: print(e) connClose(conn, cur) log.info(filename.replace('.csv', '') + 'is loaded.')
def update_etf_price_backward(): conn, cur = connDB() items = 'symbol, max(date) as date' tables = 'etf_price_backward' condition = ' group by symbol order by symbol' data_info = get_all_data(items, tables, condition) etf_info = dict(data_info) end_date = datetime.now().strftime('%Y-%m-%d') w.start() for i in etf_info: start_date = (etf_info[i] + timedelta(1)).strftime('%Y-%m-%d') if start_date > end_date: continue etf_price = w.wsd(i, "close,pct_chg", start_date, end_date, "PriceAdj=B") for r in range(0, len(etf_price.Times)): etf_value = tuple([ etf_price.Codes[0], etf_price.Times[r].strftime('%Y-%m-%d'), etf_price.Data[0][r], etf_price.Data[1][r] ]) insert_sql = 'insert into data.etf_price_backward values ' + str( etf_value) try: conn.cursor().execute(insert_sql) except Exception as e: print(e) update_sql = 'update data.etf_price_backward set close =\'' + str( etf_price.Data[0][r]) + '\', chg_rate = \'' + str( etf_price.Data[1][r] ) + '\' where symbol = \'' + etf_price.Codes[ 0] + '\' and date = \'' + str(etf_price.Times[r]) + '\'' try: conn.cursor().execute(update_sql) except Exception as e: print(e) print(i + ' is inserted in etf_price_backward. ') conn.commit() connClose(conn, cur)
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]) conn, cur = connDB() 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: cur.execute(insert_sql) except Exception as e: print(e) conn.commit() print(symbol_list3 + 'is filled.') conn, cur = connDB() 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: conn.cursor().execute(insql) except Exception as e: print(e) conn.commit() log.info(str(data_info[k][0]) + ' is inserted') fill_adj_stk(table) connClose(conn, cur)
def calc_gm(g, m, start_date, end_date): conn, cur = connDB() price_sql = 'select a.*, b.m_close from (select date, close as g_close from stk_price_forward where symbol =\'000651.SZ\' ) a inner join (select date, close as m_close from stk_price_forward where symbol =\'000333.SZ\' ) b on a.date =b.date where a.date > \'' + start_date + '\' and a.date <= \'' + end_date + '\'order by a.date asc' try: cur.execute(price_sql) db_data = cur.fetchall() except Exception as e: print(e) df_price = pd.DataFrame(list(db_data), columns=['date', 'g_close', 'm_close']) hold_signal = [] hold_return = [0] hold_cl_ret = [1] g_cl_ret = [1] m_cl_ret = [1] gm_cl_ret = [1] max_dd = [0] ## 持仓换仓信号,g为格力电器,m为美的集团 for i in range(len(df_price)): if float(df_price['g_close'].iloc[i]) > (1 - m) * float( df_price['m_close'].iloc[i]): hold_signal.append(m) elif float(df_price['g_close'].iloc[i]) < (1 - g) * float( df_price['m_close'].iloc[i]): hold_signal.append(g) else: hold_signal.append(0) ## 补充持仓信号,确保换仓后持仓信号的一致性 for j in range(1, len(hold_signal)): if hold_signal[j] == 0: hold_signal[j] = hold_signal[j - 1] ## 计算策略组合的每日收益 for k in range(1, len(hold_signal)): if hold_signal[k - 1] == g: hold_return.append((float(df_price['g_close'].iloc[k]) - float(df_price['g_close'].iloc[k - 1])) / float(df_price['g_close'].iloc[k - 1]) * 100) elif hold_signal[k - 1] == m: hold_return.append((float(df_price['m_close'].iloc[k]) - float(df_price['m_close'].iloc[k - 1])) / float(df_price['m_close'].iloc[k - 1]) * 100) else: hold_return.append(0) ## 计算其他情况的累计收益 for h in range(1, len(hold_return)): hold_cl_ret.append(hold_cl_ret[h - 1] * (1 + hold_return[h] / 100)) g_cl_ret.append( round(df_price['g_close'].iloc[h] / df_price['g_close'].iloc[0], 6)) m_cl_ret.append( round(df_price['m_close'].iloc[h] / df_price['m_close'].iloc[0], 6)) gm_cl_ret.append( 0.5 * float(df_price['g_close'].iloc[h] / df_price['g_close'].iloc[0]) + 0.5 * float(df_price['m_close'].iloc[h] / df_price['m_close'].iloc[0])) ## 计算每日最大回撤 for x in range(1, len(hold_cl_ret)): temp = (hold_cl_ret[x] - max(hold_cl_ret[0:x])) / max(hold_cl_ret[0:x]) if temp <= 0: max_dd.append(temp) else: max_dd.append(0) max_draw_down = str(round(min(max_dd) * 100, 2)) + '%' # 获得最大回测 df_price['hold_signal'] = pd.DataFrame(hold_signal) # 持有信号,g为格力电器,m为美的集团 df_price['hold_return'] = pd.DataFrame(hold_return) # 持有的日收益 df_price['hold_cl_ret'] = pd.DataFrame(hold_cl_ret) # 持仓的累计收益 df_price['g_cl_ret'] = pd.DataFrame(g_cl_ret) # 单持有格力电器的累计收益 df_price['m_cl_ret'] = pd.DataFrame(m_cl_ret) # 单持有美的集团的累计收益 df_price['gm_cl_ret'] = pd.DataFrame(gm_cl_ret) # 持有格力电器和美的集团各50%仓位的组合累计收益 df_price['max_dd'] = pd.DataFrame(max_dd) # 每日的最大回测数据 # for s in range(0,len(df_price)): # print(df_price.iloc[s]) # 计算总收益率和年化收益率 total_return = str(round((hold_cl_ret[-1] - 1) * 100, 2)) + '%' annualized_return = str( round( (hold_cl_ret[-1]**(365.25 / (datetime.today() - datetime.strptime( start_date, '%Y-%m-%d')).days) - 1) * 100, 2)) + '%' connClose(conn, cur) print( str(g) + ' & ' + str(m) + ': ' + total_return + ', ' + annualized_return + ', ' + max_draw_down) # 做图 x = np.array(df_price['date']) y1 = np.array(df_price['hold_cl_ret']) y2 = np.array(df_price['g_cl_ret']) y3 = np.array(df_price['m_cl_ret']) y4 = np.array(df_price['gm_cl_ret']) y5 = np.array(df_price['max_dd']) plt.figure(figsize=(9, 6)) ## 收益子图 plt.subplot(211) plt.plot(x, y1, 'r', label='Cigrg_006') plt.plot(x, y2, 'm', label='000651', linewidth=0.7) plt.plot(x, y3, 'g', label='000333', linewidth=0.7) plt.plot(x, y4, 'y', label='0.5*[000651,000333]', linewidth=0.7) plt.grid(True) plt.axis('tight') plt.ylabel('Return') pic_txt = 'Cumulative Return for Cigrg_006: ' + start_date + ' ~ ' + end_date plt.legend(loc='upper left', frameon=False) font_set = FontProperties(fname=r"c:\windows\fonts\simsun.ttc", size=11.5, weight='bold') plt.title(pic_txt, loc='left', fontproperties=font_set) ## 最大回撤子图 plt.subplot(212) plt.plot(x, y5, 'b') # plt.xticks(rotation=30) plt.grid(True) plt.axis('tight') plt.xlabel('Date') plt.ylabel('MaxDrawDown') plt.ylim(min(max_dd), 0) PNG_FILENAME = 'CIGRG_002_' + datetime.now().strftime( '%Y%m%d_%H%M%S') + '.png' # plt.savefig(PNG_FILENAME) # plt.show() return (total_return, annualized_return, max_draw_down, df_price)
# -=-=-=-=-=-=-=-=-=-=-= # coding=UTF-8 # __author__='Guo Jun' # Version 1..0.0 # -=-=-=-=-=-=-=-=-=-=-= from ConnectDB import connDB, connClose, get_data, get_all_data from datetime import datetime as dt, timedelta conn, cur = connDB() # symbol = 'IC1806.CFE' def fill_fur_price(symbol): ## 获取缺失的datetime列表 req_sql = 'select std_time from (select datetime as std_time from data.fur_price where symbol =\'000300.SH\') a left join (select datetime as symbol_time FROM data.fur_price where symbol =\'' + symbol + '\') b on a.std_time =b.symbol_time where b.symbol_time is null and a.std_time > (SELECT min(datetime) FROM data.fur_price where symbol =\'' + symbol + '\')' try: cur.execute(req_sql) std_date = cur.fetchall() except Exception as e: print(e) std_time = [] for i in range(0, len(std_date)): std_time.append((std_date[i][0] - timedelta(minutes=1)).strftime('%Y-%m-%d %H:%M:%S')) ## 获取前一分钟收盘价,准备注入 close_sql = 'select datetime, close from data.fur_price where datetime in (' + str( std_time).replace('[', '').replace( ']', '') + ') and symbol = \'' + symbol + '\'' try:
def update_stk_fina_calc(): conn, cur = connDB() request_date = datetime.now().date().strftime('%Y%m%d') items = 'symbol, max(date) as date' tables = 'stk_fina_calc' condition = ' where (rpt_date is not null and eps_ttm is not null and roe_ttm is not null and div_yield is not null) group by symbol order by symbol' data_info = get_all_data(items, tables, condition) update_info = {} for i in data_info: update_info[i[0]] = i[1].strftime('%Y-%m-%d') symbol_list = list(update_info.keys()) # symbol_list2 = ['002456.SZ','000001.SZ','601318.SH'] w.start() w_data = w.wsd(symbol_list, "stm_predict_issuingdate", "ED-5M", request_date, "rptYear=2016;Period=Q;Days=Alldays") report_info = [] for s in range(0, len(w_data.Codes)): for r in range(0, len(w_data.Times)): report_info_temp = [] if w_data.Data[s][r] == None or w_data.Data[s][r].strftime( '%Y-%m-%d') > request_date: continue report_info_temp.append(w_data.Codes[s]) report_info_temp.append(w_data.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data.Data[s][r].strftime('%Y-%m-%d')) report_info.append(report_info_temp) for k in report_info: insert_sql = 'insert into data.stk_fina_calc(symbol, date,rpt_date) values(' + str( k).replace('[', '').replace(']', '') + ');' insert_sql = insert_sql.replace('nan', 'default') update_sql = 'update data.stk_fina_calc set rpt_date = \'' + k[ 2] + '\' where symbol = \'' + k[0] + '\' and date = \'' + k[ 1] + '\'' update_sql = update_sql.replace('\'nan\'', 'default') try: cur.execute(insert_sql) except Exception as e: print(e) try: cur.execute(update_sql) except Exception as e: print(e) conn.commit() request_list = [] for t in range(0, len(report_info)): request_list.append(report_info[t][0]) request_list = list(set(request_list)) # request_list2 = ['000001.SZ','601318.SH'] w_data2 = w.wsd(request_list, "eps_ttm", "ED-5M", request_date, "rptYear=2016;Period=Q;Days=Alldays") eps_info = [] for s in range(0, len(w_data2.Codes)): for r in range(0, 2): report_info_temp = [] if w_data2.Data[s][r] == None or ( w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '12-31' and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '03-31' and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '06-31' and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '09-30' ) or np.isnan(w_data2.Data[s][r]): continue report_info_temp.append(w_data2.Codes[s]) report_info_temp.append(w_data2.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data2.Data[s][r]) eps_info.append(report_info_temp) for j in eps_info: update_sql = 'update data.stk_fina_calc set eps_ttm = \'' + str( j[2] ) + '\' where symbol = \'' + j[0] + '\' and date = \'' + j[1] + '\'' update_sql = update_sql.replace('\'nan\'', 'default') try: cur.execute(update_sql) except Exception as e: print(e) conn.commit() w_data3 = w.wsd(request_list, "roe_ttm2", "ED-5M", request_date, "rptYear=2016;Period=Q;Days=Alldays") roe_info = [] for s in range(0, len(w_data3.Codes)): for r in range(0, 2): report_info_temp = [] if w_data3.Data[s][r] == None or ( w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '12-31' and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '03-31' and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '06-31' and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '09-30' ) or np.isnan(w_data3.Data[s][r]): continue report_info_temp.append(w_data3.Codes[s]) report_info_temp.append(w_data3.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data3.Data[s][r]) roe_info.append(report_info_temp) for g in roe_info: update_sql = 'update data.stk_fina_calc set roe_ttm = \'' + str( g[2] ) + '\' where symbol = \'' + g[0] + '\' and date = \'' + g[1] + '\'' update_sql = update_sql.replace('\'nan\'', 'default') try: cur.execute(update_sql) except Exception as e: print(e) conn.commit() w_data4 = w.wsd(request_list, "dividendyield2", "ED-5M", request_date, "rptYear=2016;Period=Q;Days=Alldays") div_info = [] for s in range(0, len(w_data4.Codes)): for r in range(0, 2): report_info_temp = [] if w_data4.Data[s][r] == None or ( w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '12-31' and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '03-31' and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '06-31' and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '09-30' ) or np.isnan(w_data4.Data[s][r]): continue report_info_temp.append(w_data4.Codes[s]) report_info_temp.append(w_data4.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data4.Data[s][r]) div_info.append(report_info_temp) for f in div_info: update_sql = 'update data.stk_fina_calc set div_yield = \'' + str( f[2] ) + '\' where symbol = \'' + f[0] + '\' and date = \'' + f[1] + '\'' update_sql = update_sql.replace('\'nan\'', 'default') try: cur.execute(update_sql) except Exception as e: print(e) conn.commit() w_data5 = w.wsd(request_list, "roic_ttm", "ED-5M", request_date, "Period=Q;Days=Alldays") for s in range(0, len(w_data5.Codes)): for r in range(0, len(w_data5.Times)): if np.isnan(w_data5.Data[s][r]): continue update_sql = 'update data.stk_fina_calc set roic_ttm =\'' + str(w_data5.Data[s][r]) + '\' where symbol =\'' + \ w_data5.Codes[s] + '\' and date = \'' + w_data5.Times[r].strftime('%Y-%m-%d') + '\';' try: conn.cursor().execute(update_sql) except Exception as e: print(e) conn.commit() log.info('stk_fina_calc is updated.') connClose(conn, cur)
def update_stk_ratio(): conn, cur = connDB() request_date = datetime.now().date().strftime('%Y-%m-%d') items = 'symbol' tables = 'stk_ratio' condition = ' group by symbol order by symbol' data_info = get_all_data(items, tables, condition) symbol_list = [] for i in data_info: symbol_list.append(i[0]) w.start() w_data = w.wsd(symbol_list, "pe_ttm", "ED-1TD", request_date, "") # "ED-1TD" # w_data = w.wsd(symbol_list, "pe_ttm", '2018-04-02', '2018-04-02', "") pe_info = [] for s in range(0, len(w_data.Codes)): for r in range(0, len(w_data.Times)): report_info_temp = [] if np.isnan(w_data.Data[s][r]): continue report_info_temp.append(w_data.Codes[s]) report_info_temp.append(w_data.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data.Data[s][r]) pe_info.append(report_info_temp) w_data2 = w.wsd(symbol_list, "pb_lf", "ED-1TD", request_date, "") # "ED-1TD" pb_info = [] for s in range(0, len(w_data2.Codes)): for r in range(0, len(w_data2.Times)): report_info_temp = [] if np.isnan(w_data2.Data[s][r]): continue report_info_temp.append(w_data2.Codes[s]) report_info_temp.append(w_data2.Times[r].strftime('%Y-%m-%d')) report_info_temp.append(w_data2.Data[s][r]) pb_info.append(report_info_temp) for k in pb_info: insert_sql = 'insert into data.stk_ratio (symbol, date, pb) values (' + str( k).replace('[', '').replace(']', '') + ');' try: cur.execute(insert_sql) except Exception as e: print(e) conn.commit() for j in pe_info: update_sql = 'update data.stk_ratio set pe_ttm = \'' + str( j[2] ) + '\' where symbol = \'' + j[0] + '\' and date = \'' + j[1] + '\';' try: cur.execute(update_sql) except Exception as e: print(e) conn.commit() connClose(conn, cur) log.info('stk_ratio is updated.') def fill_ratio_data(): conn, cur = connDB() items = 'symbol, date' endDate = (datetime.now().date() + timedelta(days=-1)).strftime('%Y%m%d') startDate = (datetime.now().date() + timedelta(days=-15)).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]) conn, cur = connDB() items2 = 'symbol, min(date), max(date)' table2 = 'stk_ratio' 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, pe_ttm, pb ' 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 = [] pe_stk = {} pb_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) pe_stk[stk_data[b][0]] = stk_data[b][1] pb_stk[stk_data[b][0]] = stk_data[b][2] fill_pe_stk = {} fill_pb_stk = {} fill_pe_stk[date_list_idx[0]] = pe_stk[date_list_idx[0]] fill_pb_stk[date_list_idx[0]] = pb_stk[date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_pe_stk[date_list_idx[c]] = pe_stk[date_list_idx[c]] fill_pb_stk[date_list_idx[c]] = pb_stk[date_list_idx[c]] else: fill_pe_stk[date_list_idx[c]] = fill_pe_stk[date_list_idx[ c - 1]] fill_pb_stk[date_list_idx[c]] = fill_pb_stk[date_list_idx[ c - 1]] for d in date_list_idx: if d in date_stk: date_stk.remove(d) fill_pe_stk.pop(d) fill_pb_stk.pop(d) if len(date_stk) == 0: continue for e in date_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_pe_stk[e])) + '\',\'' + str( float(fill_pb_stk[e])) + '\');' print(insert_sql) try: cur.execute(insert_sql) except Exception as e: print(e) conn.commit() log.info(symbol_list3 + 'is filled.') connClose(conn, cur) fill_ratio_data()
def fill_ratio_data(): conn, cur = connDB() items = 'symbol, date' endDate = (datetime.now().date() + timedelta(days=-1)).strftime('%Y%m%d') startDate = (datetime.now().date() + timedelta(days=-15)).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]) conn, cur = connDB() items2 = 'symbol, min(date), max(date)' table2 = 'stk_ratio' 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, pe_ttm, pb ' 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 = [] pe_stk = {} pb_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) pe_stk[stk_data[b][0]] = stk_data[b][1] pb_stk[stk_data[b][0]] = stk_data[b][2] fill_pe_stk = {} fill_pb_stk = {} fill_pe_stk[date_list_idx[0]] = pe_stk[date_list_idx[0]] fill_pb_stk[date_list_idx[0]] = pb_stk[date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_pe_stk[date_list_idx[c]] = pe_stk[date_list_idx[c]] fill_pb_stk[date_list_idx[c]] = pb_stk[date_list_idx[c]] else: fill_pe_stk[date_list_idx[c]] = fill_pe_stk[date_list_idx[ c - 1]] fill_pb_stk[date_list_idx[c]] = fill_pb_stk[date_list_idx[ c - 1]] for d in date_list_idx: if d in date_stk: date_stk.remove(d) fill_pe_stk.pop(d) fill_pb_stk.pop(d) if len(date_stk) == 0: continue for e in date_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_pe_stk[e])) + '\',\'' + str( float(fill_pb_stk[e])) + '\');' print(insert_sql) try: cur.execute(insert_sql) except Exception as e: print(e) conn.commit() log.info(symbol_list3 + 'is filled.') connClose(conn, cur)