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 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_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)
@author: lpeng """ import pandas as pd import numpy as np from CIGRG.WindPy import w import datetime from pandas import DataFrame w.start() '''以下模块主要用于寻找期货换月日期''' startdate = (datetime.date.today() - datetime.timedelta(365 * 4)).strftime('%Y%m%d') enddate = (datetime.date.today()).strftime('%Y%m%d') i = w.wsd("I.DCE", "trade_hiscode,close", startdate, enddate, "") i_data = DataFrame(i.Data, index=['i_HISCODE', 'CLOSE'], columns=i.Times).T def find_change_index(data): dif_index = [] for i in range(1, len(data)): if data[i] != data[i - 1]: dif_index.append(i) return dif_index i_change_index = find_change_index(i_data.i_HISCODE) def unique(data):
# BASE_DIR = os.path.dirname(__file__) # LOG_PATH = BASE_DIR + '/' # LOG_FILENAME = str('cigrg_' + time.strftime('%Y%m%d_%H%M%S', time.localtime(time.time()))) + '.log' log.basicConfig( # filename=LOG_PATH + LOG_FILENAME, level=log.DEBUG, # format="[%(asctime)s] %(name)s:%(levelname)s: %(message)s" format="%(levelname)s: %(message)s") w.start() '''以下模块主要用于寻找期货换月日期''' # startdate = (datetime.date.today() - datetime.timedelta(365 * 4)).strftime('%Y%m%d') # enddate = (datetime.date.today()).strftime('%Y%m%d') wind_data = w.wsd( "002456.SZ", "roe_diluted,grossprofitmargin,pe_ttm,pb_lf,stmnote_bank_171", "2014-01-01", "2018-03-05", "rptType=1;TradingCalendar=SZSE;Currency=CNY") # i_data = DataFrame(i.Data, index=['i_HISCODE', 'CLOSE'], columns=i.Times).T log.info(wind_data) # for a in wind_data: # log.info(a) # # N1 = 10 # 均线周期 # # N2 = 30 # 均线周期 # N1 = 14 # 均线周期 # N2 = 245 # 均线周期 # BarSize = '60' # 交易的周期,即按照30分钟周期 # unit = 100 # 合约单位,铁矿1手100吨 # equity = 100000 # 1起始资金 #
""") sql = "INSERT INTO stockprice VALUES (%s, %s, %d, %d, %d, %d, %d, %d)" # 通过wset来取数据集数据 print('\n\n' + '-----通过wset来取数据集数据,获取全部A股代码列表-----' + '\n') wsetdata = w.wset('SectorConstituent', 'date=20160116;sectorId=a001010100000000;field=wind_code') print(wsetdata) for j in range(0, len(wsetdata.Data[0])): # 通过wsd来提取时间序列数据,比如取开高低收成交量,成交额数据 print "\n\n-----第 %i 次通过wsd来提取 %s 开高低收成交量数据-----\n" % ( j, str(wsetdata.Data[0][j])) wssdata = w.wss(str(wsetdata.Data[0][j]), 'ipo_date') wsddata1 = w.wsd(str(wsetdata.Data[0][j]), "open,high,low,close,volume,amt", wssdata.Data[0][0], dt, "Fill=Previous") if wsddata1.ErrorCode != 0: continue print wsddata1 for i in range(0, len(wsddata1.Data[0])): sqllist = [] sqltuple = () sqllist.append(str(wsetdata.Data[0][j])) if len(wsddata1.Times) > 1: sqllist.append(wsddata1.Times[i].strftime('%Y%m%d')) for k in range(0, len(wsddata1.Fields)): sqllist.append(wsddata1.Data[k][i]) sqltuple = tuple(sqllist) cursor.execute(sql, sqltuple) conn.commit()
# -*- coding:utf-8 -*- #Python 3.5.0 from CIGRG.WindPy import w import pandas as pd w.start() # 取数据的命令如何写可以用命令生成器来辅助完成 wsd_data = w.wsd("000001.SZ", "open,high,low,close", "2015-12-10", "2015-12-22", "Fill=Previous") #演示如何将api返回的数据装入Pandas的Series open = pd.Series(wsd_data.Data[0]) high = pd.Series(wsd_data.Data[1]) low = pd.Series(wsd_data.Data[2]) close = pd.Series(wsd_data.Data[3]) print('open:/n', open) print('high:/n', high) print('low:/n', low) print('close:/n', close) #演示如何将api返回的数据装入Pandas的DataFrame fm = pd.DataFrame(wsd_data.Data, index=wsd_data.Fields, columns=wsd_data.Times) fm = fm.T #将矩阵转置 print('fm:/n', fm)
ipo_date = get_all_data(items, table, condition) stk_ipo = {} stk_list = [] for i in ipo_date: stk_ipo[i[0]] = i[1] stk_list.append(i[0]) w.start() conn, cur = connDB() # pb for i in range(1044, len(stk_list)): start_date = stk_ipo[stk_list[i]].strftime('%Y-%m-%d') if start_date < '2010-01-01': start_date = '2010-01-01' wind_data = w.wsd(stk_list[i], "pb_lf", start_date, "2018-03-18", "") for k in range(0, len(wind_data.Times)): if np.isnan(wind_data.Data[0][k]): continue update_sql = 'update data.stk_ratio set pb = \'' + str( wind_data.Data[0][k]) + '\' where symbol =\'' + wind_data.Codes[ 0] + '\' and date = \'' + wind_data.Times[k].strftime( '%Y-%m-%d') + '\'' try: cur.execute(update_sql) except Exception as e: print(e) print(update_sql) conn.commit()
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 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, "roic2_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 printpy(outdata): if outdata.ErrorCode!=0: print('error code:'+str(outdata.ErrorCode)+'\n'); return(); for i in range(0,len(outdata.Data[0])): strTemp='' if len(outdata.Times)>1: strTemp=str(outdata.Times[i])+' ' for k in range(0, len(outdata.Fields)): strTemp=strTemp+str(outdata.Data[k][i])+' ' print(strTemp) # 通过wsd来提取时间序列数据,比如取开高低收成交量,成交额数据 print('\n\n'+'-----通过wsd来提取时间序列数据,比如取开高低收成交量,成交额数据-----'+'\n') wsddata1=w.wsd("000001.SZ", "open,high,low,close,volume,amt", "2015-11-22", "2015-12-22", "Fill=Previous") printpy(wsddata1) # 通过wsd来提取各个报告期财务数据 print('\n\n'+'-----通过wsd来提取各个报告期财务数据-----'+'\n') wsddata2=w.wsd("600000.SH", "tot_oper_rev,tot_oper_cost,opprofit,net_profit_is", "2008-01-01", "2015-12-22", "rptType=1;Period=Q;Days=Alldays;Fill=Previous") printpy(wsddata2) # 通过wss来取截面数据 print('\n\n'+'-----通过wss来取截面数据-----'+'\n') wssdata=w.wss("600000.SH,600007.SH,600016.SH", "ev,total_shares","tradeDate=20151222;industryType=1") printpy(wssdata) # 通过wst来取日内成交数据 print('\n\n'+'-----通过wst来取日内成交数据-----'+'\n') wstdata=w.wst("IF.CFE", "last,volume", "2015-12-22 09:00:00", "2015-12-22 14:04:45")