def start(self):
super().start()
self._idx = -1
s = self.p.dataname
start_date = str(self.p.fromdate.date())
end_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
frequency = self.p.frequency
if self.p.security == 'stock':
if frequency != 'day':
data_wind = w.wsi(s, "open,high,low,close,volume", start_date, end_date, "BarSize="+frequency)
else:
data_wind = w.wsd(s, "open,high,low,close,volume", start_date, end_date, "PriceAdj=B")
elif self.p.security == 'future':
if frequency != 'day':
data_wind = w.wsi(s, "open,high,low,close,volume", start_date, end_date, "BarSize="+frequency)
else:
data_wind = w.wsd(s, "open,high,low,close,volume", start_date, end_date, "PriceAdj=B")
elif self.p.security == 'index':
if frequency != 'day':
data_wind = w.wsi(s, "open,high,low,close,volume", start_date, end_date, "BarSize="+frequency)
else:
data_wind = w.wsd(s, "open,high,low,close,volume", start_date, end_date, "PriceAdj=B")
self.times = data_wind.Times
self.data = data_wind.Data
self.count = 0
def get_mkt_data_minutes(
d_df: pd_DataFrame,
freq=30
) -> Tuple[pd_DataFrame, pd_DataFrame, pd_DataFrame, pd_DataFrame]:
if op_exists(MKT_DATA_FILE):
d_op_df = pd_read_hdf(MKT_DATA_FILE, key="do")
u_op_df = pd_read_hdf(MKT_DATA_FILE, key="uo")
d_cp_df = pd_read_hdf(MKT_DATA_FILE, key="dc")
u_cp_df = pd_read_hdf(MKT_DATA_FILE, key="uc")
else:
def _data2df(data):
tdf = pd_DataFrame(data=data.Data,
columns=pd_DatetimeIndex(data.Times),
index=["code", "p"]).T
rst_df = pd_concat([df["p"] for _, df in tdf.groupby("code")],
axis=1)
rst_df.columns = [tkr for tkr, _ in tdf.groupby("code")]
return rst_df.astype(float)
w_obj.start()
data = w_obj.wsi(",".join(d_df["d_code"].to_list()), "open", START_DT,
END_DT, f"BarSize={freq}")
d_op_df = _data2df(data)
data = w_obj.wsi(",".join(d_df["u_code"].unique().tolist()), "close",
START_DT, END_DT, f"BarSize={freq}")
u_op_df = pd_DataFrame(data=data.Data,
columns=pd_DatetimeIndex(data.Times),
index=data.Codes).T.astype(float)
data = w_obj.wsi(",".join(d_df["d_code"].to_list()), "close", START_DT,
END_DT, f"BarSize={freq}")
d_cp_df = _data2df(data)
data = w_obj.wsi(",".join(d_df["u_code"].unique().tolist()), "close",
START_DT, END_DT, f"BarSize={freq}")
u_cp_df = pd_DataFrame(data=data.Data,
columns=pd_DatetimeIndex(data.Times),
index=data.Codes).T.astype(float)
w_obj.close()
assert len(d_op_df) == len(u_op_df) == len(d_cp_df) == len(u_cp_df)
d_op_df.sort_index(inplace=True)
d_op_df.to_hdf(MKT_DATA_FILE, key="do")
u_op_df.sort_index(inplace=True)
u_op_df.to_hdf(MKT_DATA_FILE, key="uo")
d_cp_df.sort_index(inplace=True)
d_cp_df.to_hdf(MKT_DATA_FILE, key="dc")
u_cp_df.sort_index(inplace=True)
u_cp_df.to_hdf(MKT_DATA_FILE, key="uc")
return d_op_df, u_op_df, d_cp_df, u_cp_df
def get_data(code,
field,
startDate,
endDate,
add_stockcode=True,
highFreq=False,
highFreqBarSize='barSize=5',
priceAdj=False,
time_std=True):
'''
从wind中获取数据，可以获取高频率（1天及以上）和低频率（1分钟-1天内）的数据，数据以字典的形式返回
@param:
code: 需要获取数据的Wind代码，必须为字符串型
field: 需要获取数据的类型，可以为列表或者元组或者字符串类型，例如['close', 'open', 'high',
'low']等价于('close', 'open', 'high', 'low')等价于'close, open, high, low'，常用的数据类型如
{close: 收盘价，open: 开盘价，high: 最高价，low: 最低价，volume: 成交量}，其他可参考MATLAB中的
w.menu wsi和wsd来查询
startDate: 需要数据的开始时间，可以为datetime.datetime类型，也可以是字符串类型，如果是高频率
的数据，应该是用datetime.datetime类型提供，且提供的时间最好精确到分钟
endDate: 需要数据的结束时间，与startDate的要求相同
add_stockcode: 是否在返回的DataFrame中添加股票代码列，默认为添加(True)
highFreq: 表示是否需要取高频率的数据，bool型
highFreqBarSize: 提取的高频率数据的频率设定，默认为5分钟线，即barSize=5，其他设置可以类似，
但是要求必须为字符串，形式只能为barSize=n，n为需要的数据的频率
priceAdj: 是否需要复权，默认不需要，bool型，只能对有复权选项的证券使用，否则返回的数据会有错误
time_std: 是否将时间由Wind格式转换为正常格式，默认为True
@return:
data: pd.DataFrame格式数据，其中另外会添加time列，记录时间
'''
check_connection()
if highFreq:
if priceAdj:
rawData = w.wsi(code, field, startDate, endDate, highFreqBarSize,
'PriceAdj=F')
else:
rawData = w.wsi(code, field, startDate, endDate, highFreqBarSize)
else:
if priceAdj:
rawData = w.wsd(code, field, startDate, endDate, 'PriceAdj=F')
else:
rawData = w.wsd(code, field, startDate, endDate)
assert rawData.ErrorCode == 0, rawData.Data[0][0]
data = dict(zip(field, rawData.Data))
data['time'] = rawData.Times
data = pd.DataFrame(data)
if add_stockcode:
data['code'] = [code] * len(data)
if time_std:
import dateshandle
data['time'] = dateshandle.wind_time_standardlization(data.time)
return data
def py_wsi(tickers,fields,startDt,endDt, name="", options=""):
# If tickers is an array, only the first field will be returned.
#print type(tickers)
if type(tickers) <> type([]):
temp = w.wsi(tickers,fields,startDt,endDt,options)
dataMat = np.array(temp.Data).T
result = pd.DataFrame(dataMat,index=temp.Times,columns=temp.Fields)
return result
if type(tickers) == type([]):
temp = w.wsi(tickers,fields,startDt,endDt,options)
dataMat = np.array(temp.Data).T
result = pd.DataFrame(dataMat,index=temp.Times,columns=temp.Codes)
return result
def download_data(codes, date):
w.start()
for code in codes:
fname = '%s/%s.xlsx' % (const.MIN_STOCK_DIR, code)
if os.path.exists(fname):
old_df = pd.read_excel(fname)
if (old_df.index[0].day == pd.to_datetime(date).day):
if old_df.index[0] >= pd.to_datetime(date) and pd.to_datetime(
date) <= old_df.index[-1]:
print('skipping %s' % (code))
continue
print('downloading %s' % (code))
data = w.wsi(code, 'close,amt', '%s 09:00:00' % (date),
'%s 15:00:00' % (date))
df = pd.DataFrame(np.array(data.Data).T,
index=data.Times,
columns=['close', 'amt'])
if df.shape[0] == 0:
print('shape error')
continue
if pd.to_datetime(date) < old_df.index[0]:
df = df.append(old_df)
elif pd.to_datetime(date) > old_df.index[-1]:
df = old_df.append(df)
# df = pd.read_excel(fname)
# df = df.loc[df.index.drop_duplicates(keep='first')]
df.to_excel(fname)
def mgWsiUp():
w.start()
d = loadmat('D:\FieldSHSZ')
Field = d['Field'].tolist()
stride = 100
numF = range(len(Field))[::stride]
dt = loadmat('D:\dataTime')
timeD = dt['time']
times = findDate(timeD, '20140925', 30)
client = mg.MongoClient()
db = client['MKD']
col = db['minData']
for t in times:
for f in numF:
data = w.wsi(Field[f:f + stride],
'open,high,low,close,volume',
str(t),
str(t) + '15:01:00',
'showblank=0',
barsize=1).Data[1:]
uniField = set(data[0])
upiter(data, uniField, t, col)
def getMinTimeSeriesDataFrame(code, beginDate, endDate, paraList, bar_size=1):
"""
获取分钟级别数据
get time series from windPy, each code represents one capture
月度合约: trade_hiscode
:param bar_size: (int) The frequency of the data
:param code: string
:param beginDate: date or datetime
:param endDate: date or datetime
:param paraList: list
:return: DataFrame
"""
try:
w.start()
para = ",".join(paraList)
bar_size = "" + str(bar_size) if bar_size is not None else ""
windData = w.wsi(code,
para,
beginDate.strftime("%Y-%m-%d %H:%M:%S"),
endDate.strftime("%Y-%m-%d %H:%M:%S"), "")
if len(windData.Data) == 0:
raise BaseException
if len(windData.Data[0]) == 0:
raise BaseException
dataDict = {}
for i in range(len(windData.Data)):
dataDict[windData.Fields[i].lower()] = windData.Data[i]
df = pd.DataFrame(dataDict, index=windData.Times)
if df.index[0].to_pydatetime().microsecond != 0:
df.index -= timedelta(microseconds=df.index[0].to_pydatetime().microsecond)
df.index.name = "trade_date"
return df
except BaseException as e:
print(format(e))
raise
def import_data_from_wind(d_start, d_end, file_folder='C:\\dev\\src\\ktlib\\data3\\', freq='d', contracts=[]):
df = load_live_cont(d_start, d_end)
contList = [str(inst) for inst in df.index]
startDates = df.start_date
endDates = df.expiry
fields = 'open,close,high,low,volume,oi'
if len(contracts) !=0:
contList = contracts
#w.start()
for cont in contList:
if cont[1].isalpha(): key = cont[:2]
else: key = cont[:1]
ex = 'SH'
for exch in product_code.keys():
if key in product_code[exch]:
ex = exch
ticker = cont + '.' + ex
mth = int(cont[-2:])
if (key not in contMonth):
continue
if mth not in contMonth[key]:
continue
start_d = max(d_start, startDates.ix[cont])
end_d = min(d_end, endDates.ix[cont])
try:
if freq == 'm':
print "loading min data for ticker = %s" % ticker
raw_data = w.wsi(ticker,fields,start_d,end_d)
if len(raw_data.Data)>1:
outfile = file_folder + cont+'_min.csv'
output={'datetime':raw_data.Times,
'open':raw_data.Data[2],
'close':raw_data.Data[3],
'high':raw_data.Data[4],
'low':raw_data.Data[5],
'volume':raw_data.Data[6],
'openInterest':raw_data.Data[7]}
dump2csvfile(output,outfile)
else:
print "no min data obtained for ticker=%s" % ticker
else:
print "loading daily data for ticker = %s" % ticker
raw_data = w.wsd(ticker,fields,start_d,end_d)
if len(raw_data.Data)>1:
outfile = file_folder + cont+'_daily.csv'
output={'datetime':raw_data.Times,
'open':raw_data.Data[0],
'close':raw_data.Data[1],
'high':raw_data.Data[2],
'low':raw_data.Data[3],
'volume':raw_data.Data[4],
'openInterest':raw_data.Data[5]}
dump2csvfile(output,outfile)
else:
print "no daily data obtained for ticker=%s" % ticker
except ValueError:
pass
w.stop()
return True
def mgWsiUp(date=[]):
w.start()
# Wind所需股票、日期
Field=stockSets('shsz')
stride=100
numF=range(len(Field))[::stride]
if date:#若指定日期则下载指定的，否则下载markDate中没被下载的日期
date=[str(d) for d in date]
else:
date=empDates('min')
# dt=loadmat('D:\dataTime')
# timeD=dt['time']
# times=findDate(timeD,'20140925',30)
#Mongo数据库
date=date[:2025]
client=mg.MongoClient()
db=client['MKD']
col=db['minData']
#异常处理矩阵（与日期date）长度相同
unDownStock=[[] for i in range(len(date))]
#下载并插入数据库
for i,t in enumerate(reversed(date)):
for f in numF:
data=w.wsi(Field[f:f+stride],'open,high,low,close,volume',t,t+' 15:01:00','showblank=0',barsize=1).Data[1:]
if data:
uniField=set(data[0])
#Todo: 异常处理如Internet Timeout
unDownStock=deExcept(t,unDownStock)
#数据存入数据库
upiter(data,uniField,t,col)
else:
unDownStock[i]=[-1]
dateMark('min',date,unDownStock)
def GetDatas(a_ifId, a_begin, a_end, a_max):
client = MongoClient('mongodb://localhost:27017/')
col = client.Test[a_ifId]
colRange = client.Test["range_%s" % a_ifId]
if not col.count == 0:
#检测是否给定的时间范围
#否则重置col,colRange
if colRange.count() == 0:
col.drop()
else:
range = [ x[u'_id'] for x in colRange.find()]
if not (range[0] == a_ifId and range[1] == a_begin and range[2] == a_end):
colRange.drop()
col.drop()
if col.count() == 0:
w.start(waitTime=10)
wdatas = w.wsi(a_ifId,"open,close",a_begin,a_end, 'Fill=Previous')
datasOpen = wdatas.Data[0]
datasClose = wdatas.Data[1]
docs =[ {'_id':x[0],'open':x[1],'close':x[2] } for x in zip(wdatas.Times, datasOpen, datasClose) ]
col.insert(docs)
colRange.insert( [ {'_id':a_ifId} , {'_id':a_begin} , {'_id':a_end}] )
else:
datasClose = [ x[u'close'] for x in col.find()]
if a_max == -1:
return datasClose
return datasClose[:a_max]
def _single_bar(cnt,cmt,start,end,field='close'):
#w_data=w.wsi(cnt,field,start,end,"Fill=Previous")
w_data=w.wsi(cnt,field,start,end)
df=wsi2pandas(w_data)
df=_filter(cmt,df)
df.dropna(inplace=True)
return df
def download_data_wind(start_date, end_date, var_list):
if w.isconnected() == False:
w.start()
raw = w.wsi(var_list, "open, high, low, close, volume, amt", start_date,
end_date, "")
raw_df = pd.DataFrame(raw.Data, index=raw.Fields, columns=raw.Times)
raw_df = raw_df.T
return raw_df
def wsi(code, field_list, start_time, end_time):
field_str = ",".join(field_list)
tmp_data = w.wsi(code, field_str, start_time, end_time, "")
WindCheck(tmp_data)
tmp_data = pd.DataFrame(tmp_data.Data,
index=tmp_data.Fields,
columns=tmp_data.Times).T
return tmp_data
def get_wind_data(inst_list, start_date, end_date, save_loc='C:\\dev\\data\\', freq='m'):
exch_map = {v: k for k, v in wind_exch_map.items()}
for instID in inst_list:
exch = misc.inst2exch(instID)
ex = exch_map[exch]
ticker = instID + '.' + ex
product = misc.inst2product(instID)
sdate = start_date
edate = end_date
stime = datetime.time(9, 0, 0)
etime = datetime.time(15, 0, 0)
if product in ['T', 'TF']:
stime = datetime.time(9, 15, 0)
etime = datetime.time(15, 15, 0)
elif product in misc.night_session_markets:
stime = datetime.time(21, 0, 0)
sdate = misc.day_shift(sdate, '-1b')
smin = datetime.datetime.combine(sdate, stime)
emin = datetime.datetime.combine(edate, etime)
fields = 'open,high,low,close,volume,oi'
try:
if freq == 'm':
outfile = save_loc + instID + '_min.csv'
if os.path.isfile(outfile):
continue
raw_data = w.wsi(ticker, fields, smin, emin)
if len(raw_data.Data) > 1:
output = {'datetime': raw_data.Times,
'open': raw_data.Data[0],
'high': raw_data.Data[1],
'low': raw_data.Data[2],
'close': raw_data.Data[3],
'volume': raw_data.Data[4],
'openInterest': raw_data.Data[5]}
dump2csvfile(output, outfile)
else:
print "no min data obtained for ticker=%s" % ticker
elif freq == 'd':
outfile = save_loc + instID + '_daily.csv'
if os.path.isfile(outfile):
continue
raw_data = w.wsd(ticker, fields, start_date, end_date)
if len(raw_data.Data) > 1:
output = {'datetime': raw_data.Times,
'open': raw_data.Data[0],
'high': raw_data.Data[1],
'low': raw_data.Data[2],
'close': raw_data.Data[3],
'volume': raw_data.Data[4],
'openInterest': raw_data.Data[5]}
dump2csvfile(output, outfile)
else:
print "no daily data obtained for ticker=%s" % ticker
except ValueError:
pass
w.stop()
return True
def save_intraday_data(evalDate):
datestr = str(evalDate.year()) + "-" + str(evalDate.month()) + "-" + str(
evalDate.dayOfMonth())
data = w.wsi("510050.SH", "close", "2017-08-11 08:00:00",
"2017-08-11 15:05:00", "BarSize=5;Fill=Previous")
df = pd.DataFrame(data=data.Data[0], index=data.Times)
df.to_json(
os.path.abspath('..') + '\marketdata\intraday_etf_' + datestr +
'.json')
def post(self):
"""
json str:{"codes": "RU1801.SHF", "fields": "open,high,low,close,volume,amt,oi", "begin_time": "2017-12-11 09:00:00", "end_time": "2017-12-11 10:27:41", "options": ""}
:return: 返回万得返回数据dict
"""
data_dic = request.json
# print(request.json)
logger.info('/wsi/ data_dic:%s' % data_dic)
codes = data_dic['codes']
fields = data_dic['fields']
begin_time = data_dic['begin_time']
end_time = data_dic['end_time']
options = data_dic['options']
if not w.isconnected():
w.start()
if options == "":
options = None
ret_data = w.wsi(codes, fields, begin_time, end_time, options)
error_code = ret_data.ErrorCode
if error_code != 0:
msg = ERROR_CODE_MSG_DIC.setdefault(error_code, "")
logger.error('wsi("%s", "%s", "%s", "%s", "%s") ErrorCode=%d %s' %
(codes, fields, begin_time, end_time, options,
error_code, msg))
return {'error_code': ret_data.ErrorCode, 'message': msg}, 404
# if ret_data.ErrorCode != 0:
# logger.error('wsd("%s", "%s", "%s", "%s", "%s") ErrorCode=%d' % (
# codes, fields, begin_time, end_time, options, ret_data.ErrorCode))
# return {'error_code': ret_data.ErrorCode}, 404
# 将 Data数据中所有 datetime date 类型的数据转换为 string
data_len = len(ret_data.Data)
for n_data in range(data_len):
data = ret_data.Data[n_data]
data_len2 = len(data)
if data_len2 > 0:
# 取出第一个部位None的数据
for item_check in data:
if item_check is not None:
break
# 进行类型检查，如果发现是 datetime, date 类型之一，则进行类型转换
if item_check is not None and type(item_check) in (datetime,
date):
ret_data.Data[n_data] = [
format_2_datetime_str(dt) for dt in data
]
logger.info('%d column["%s"] date to str', n_data,
ret_data.Fields[n_data])
# 组成 DataFrame
ret_df = pd.DataFrame(
ret_data.Data,
index=ret_data.Fields,
columns=[format_2_datetime_str(dt) for dt in ret_data.Times])
# print(ret_df)
ret_dic = ret_df.to_dict()
# print('ret_dic:\n', ret_dic)
return ret_dic
def getData(self):
#for calculating index constituents
d = self.date.strftime('%Y%m%d')
s = '000905.SH'
p = w.wset('IndexConstituent','date='+d+';windcode='+s)
self.Constituent= p.Data[1]
self.stocks = pd.DataFrame({'stocks':p.Data[1]})
# for pulling the data frame
start = 0
end = 100
storage= []
for i in range(0,5):
p = w.wsi(self.Constituent[start:end],"close,amt",
self.date.strftime('%Y-%m-%d 09:30:00'),
self.date.strftime('%Y-%m-%d 15:01:00'))
time = pd.to_datetime(p.Times)
tickers = Series(p.Data[1])
close = Series(p.Data[2])
vol = Series(p.Data[3])
a = DataFrame({'time':time,
'ticker': tickers,
'close': close,
'vol':vol})
a = a.set_index(time)
storage.append(a)
start+= 100
end += 100
self.df = pd.concat(storage).pivot(index='time', columns='ticker', values='close')
#pull the index
storage= []
indices = ['000905.SH','000001.SH','000016.SH','000300.SH','399102.SZ']
p = w.wsi(indices,"close,amt",
self.date.strftime('%Y-%m-%d 09:30:00'),
self.date.strftime('%Y-%m-%d 15:01:00'))
time = pd.to_datetime(p.Times)
tickers = Series(p.Data[1])
close = Series(p.Data[2])
vol = Series(p.Data[3])
a = DataFrame({'time':time,'ticker': tickers,
'close': close,
'vol':vol})
a = a.set_index(time)
self.index = a.pivot(index='time', columns='ticker', values='close')
def get_min_data(code, field, period, start, end):
dat = w.wsi(code, ",".join(field), start, end,
"BarSize=%d;PriceAdj=F" % period)
strtime = map(lambda x: x.strftime("%Y-%m-%d %X"), dat.Times)
df = pd.DataFrame(
np.array(dat.Data).T,
index=map(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d %X"),
strtime),
columns=field)
return df
def wind_data_50etf(windcode, date):
db_data = []
id_instrument = 'index_50etf'
datasource = 'wind'
data = w.wsi(windcode, "close,volume,amt", date + " 09:00:00",
date + " 15:01:00", "Fill=Previous")
datetimes = data.Times
prices = data.Data[0]
volumes = data.Data[1]
trading_values = data.Data[2]
for idx, dt in enumerate(datetimes):
price = prices[idx]
volume = volumes[idx]
trading_value = trading_values[idx]
db_row = {
'dt_datetime': dt,
'id_instrument': id_instrument,
'datasource': datasource,
'windcode': windcode,
'amt_price': price,
'amt_trading_volume': volume,
'amt_trading_value': trading_value,
'timestamp': datetime.datetime.today()
}
db_data.append(db_row)
return db_data
# w.start()
# begdate="2017-07-06"
# enddate="2017-07-06"
# windcode = "510050.SH"
# engine = create_engine('mysql+pymysql://root:[email protected]/mktdata_intraday',
# echo=False)
# conn = engine.connect()
# metadata = MetaData(engine)
#
# table = Table('equity_index_50etf_intraday', metadata, autoload=True)
# db_data = wind_data_50etf(windcode,begdate)
# try:
# conn.execute(table.insert(), db_data)
# except Exception as e:
# print(begdate)
# print(e)
# data = w.wsi(windcode, "close,volume,amt", begdate + " 09:00:00", enddate + " 15:01:00", "Fill=Previous")
# # batch_insert_winddata(conn,table,begdate="2017-07-06",enddate="2017-07-06")
# print(data)
#
# dt_datetime = data.Times
# prices = data.Data[0]
# volumes = data.Data[1]
# values = data.Data[2]
def get_basis(cnt, spots, end):
cmt = cnt_to_cmt(cnt)
noontime1 = end + ' 11:29:00'
noontime2 = end + ' 11:30:00'
if cmt in list(spots['Code']):
spot = spots[spots['Code'] == cmt]['spot']
cl = w.wsi(cnt, 'close', noontime1, noontime2,
"Fill=Previous").Data[0][0]
ratio = str(round((cl - spot) * 100.0 / spot, 1)) + '%'
else:
ratio = 'null'
return ratio
def wsi(self, *args, **kwargs):
try:
raw_data = w.wsi(*args, **kwargs)
if raw_data.ErrorCode < 0:
return json.dumps(raw_data.Codes)
df = pd.DataFrame(raw_data.Data)
df = df.transpose()
df.columns = [f.lower() for f in raw_data.Fields]
df['datetime'] = raw_data.Times
df = df.set_index(['datetime'])
return df.to_json()
except:
return json.dumps({'error': 'something wrong in calculation'})
def wsi(self, *args, **kwargs):
try:
raw_data = w.wsi(*args, **kwargs)
if raw_data.ErrorCode < 0:
return json.dumps(raw_data.Codes)
df = pd.DataFrame(raw_data.Data)
df = df.transpose()
df.columns = [f.lower() for f in raw_data.Fields]
df['datetime'] = raw_data.Times
df = df.set_index(['datetime'])
return df.to_json()
except:
return json.dumps({'error': 'something wrong in calculation'})
def wsi(tickers, fields, startdate, enddate):
if isinstance(tickers,str):
tickers = tickers.replace(',','').split()
if isinstance(fields,str):
fields = fields.replace(',','').split()
if len(tickers) == 1:
tmp = w.wsi(tickers, fields, startdate, enddate)
return pd.DataFrame(dict(zip(fields, tmp.Data)),index = tmp.Times)
elif len(fields) == 1:
tmp = w.wsi(tickers[0], fields, startdate, enddate)
df = pd.DataFrame(dict(zip([tickers[0]], tmp.Data)),index = tmp.Times)
for ticker in tickers[1:]:
tmp = w.wsi(ticker, fields, startdate, enddate)
df1 = pd.DataFrame(dict(zip([ticker], tmp.Data)),index = tmp.Times)
df = pd.merge(df, df1, left_index = True, right_index = True)
return df
else:
print 'cannot surrport multiple code with multiple fields'
def wsi(tickers, fields, startdate, enddate, cfg=""):
if isinstance(tickers, str):
tickers = tickers.replace(',', '').split()
if isinstance(fields, str):
fields = fields.replace(',', '').split()
if len(tickers) == 1:
tmp = w.wsi(tickers, fields, startdate, enddate, cfg)
return pd.DataFrame(dict(zip(fields, tmp.Data)), index=tmp.Times)
elif len(fields) == 1:
tmp = w.wsi(tickers[0], fields, startdate, enddate, cfg)
df = pd.DataFrame(dict(zip([tickers[0]], tmp.Data)), index=tmp.Times)
for ticker in tickers[1:]:
tmp = w.wsi(ticker, fields, startdate, enddate, cfg)
df1 = pd.DataFrame(dict(zip([ticker], tmp.Data)), index=tmp.Times)
df = pd.merge(df, df1, left_index=True, right_index=True)
return df
else:
print 'cannot surrport multiple code with multiple fields'
def get_minute_bar(self, symbol, start_date, end_date):
"""
从wind请求合约历史数据
:param symbol: 合约代码
:param start_date: format: %Y-%m-%d %H:%S:%M
:param end_date: format: %Y-%m-%d %H:%S:%M
:return:
"""
# 将合约代码转换为wind合约代码
wind_code = self.get_wind_code_by_symbol(symbol)
# 获取数据
wsi_data = w.wsi(wind_code, wind_data_field, start_date, end_date, "")
# 判断是否正常获取数据
error_code = wsi_data.ErrorCode
if error_code != 0:
# 无数据，可能该合约在该时段还没开始交易
if error_code == -40520007:
print("合约" + wind_code + "在 " + start_date + " -- " +
end_date + " 时段还未开始交易")
return []
print(wind_code + u" wind数据获取错误，错误代码：")
print(error_code)
raise ValueError("get data from wind error")
times = wsi_data.Times # 时间列表
# code = wsi_data.Codes[0] # 合约代码
data = wsi_data.Data # 核心历史数据
# modified_date = get_modified_date() # 更新时间用于更新数据,用不用待定
bar_list = []
for i, dt in enumerate(times):
bar = VtBarData()
bar.vtSymbol = symbol # vt系统代码，需要code转换
bar.symbol = symbol # 代码
# bar.exchange = None # 交易所代码，可以无
bar.open = data[0][i]
bar.high = data[1][i]
bar.low = data[2][i]
bar.close = data[3][i]
bar.volume = data[4][i]
bar.openInterest = data[5][i]
bar.datetime = times[i]
bar.date = bar.datetime.strftime("%Y%m%d") # bar开始的日期
bar.time = bar.datetime.strftime("%H:%M:%S") # 时间
bar_list.append(bar)
return bar_list
def loadData_min(begin_time, stockname, end_time, barsize, init_p):
if not w.isconnected():
w.start()
res = w.wsi(stockname, "high, low, close", begin_time, end_time,
f"barsize={barsize}; Priceadj=F")
K_list = []
if res.ErrorCode != 0:
#print(f"Error:{stockname} load {barsize}min K info error: wsi-{res.ErrorCode}")
raise loaddataError(
f"{stockname} load min data from Wind error: {res.ErrorCode}")
else:
if barsize == 30:
if not isinstance(init_p, tuple):
init_p = (init_p, 0, constant.N_5 - 1)
seq_kti = get_kti_seq(list(range(len(res.Data[0]))), init_p,
constant.N_30, constant.N_5)
n = -1
for jj in range(len(res.Data[0])):
if res.Data[0][jj] == nan or res.Data[1][
jj] == nan or res.Data[2][jj] == nan:
continue
n += 1
temp_t = res.Times[jj].strftime("%Y-%m-%d %H:%M:%S")
k = K30(high=round(res.Data[0][jj], 2),
low=round(res.Data[1][jj], 2),
close=round(res.Data[2][jj], 2),
i=seq_kti[n],
lev=2,
time=temp_t)
K_list.append(k)
else:
init_p = (init_p[0], init_p[1], 0)
seq_kti = get_kti_seq(list(range(len(res.Data[0]))), init_p,
constant.N_30, 1)
n = -1
for jj in range(len(res.Data[0])):
if res.Data[0][jj] == nan or res.Data[1][
jj] == nan or res.Data[2][jj] == nan:
continue
n += 1
temp_t = res.Times[jj].strftime("%Y-%m-%d %H:%M:%S")
k = K5(high=round(res.Data[0][jj], 2),
low=round(res.Data[1][jj], 2),
close=round(res.Data[2][jj], 2),
i=seq_kti[n],
lev=3,
time=temp_t)
K_list.append(k)
return K_list
def download_option_data(start_date, end_date, var_symbol):
"""
下载指定日期的指定期权的分钟收盘价数据
:param start_date:"2018-02-05 09:00:00"
:param end_date:
:param var_symbol:"10001110.SH"
:return:
"""
if w.isconnected() == False:
w.start()
raw = w.wsi(var_symbol, "close", start_date, end_date, "")
raw_df = pd.DataFrame(raw.Data, index=raw.Codes, columns=raw.Times)
raw_df = raw_df.T
return raw_df
def update_fur(symbol):
fur_data = w.wsi(symbol, "open,close,high,low", "2018-08-06 09:00:00",
"2018-08-27 23:01:00",
"periodstart=09:00:00;periodend=23:01: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 get_basis(cnt, spots, end, bs_adjust):
cmt = cnt_to_cmt(cnt)
mcode = cmt + cnt.split('.')[0][-2:]
noontime1 = end + ' 11:29:00'
noontime2 = end + ' 11:30:00'
if cmt in list(spots['Code']):
try:
#adj=bs_adjust.loc[mcode]['adjust']
adj = 0
except:
adj = 0
spot = spots[spots['Code'] == cmt]['spot']
cl = w.wsi(cnt, 'close', noontime1, noontime2,
"Fill=Previous").Data[0][0]
ratio = round((spot - cl - adj) * 100.0 / cl, 0)
else:
ratio = 0
return ratio
def get_wsi(universe,
factors,
start_datetime,
end_datetime,
names=None,
**options):
'''
获取万德分钟序列数据。
Parameters
----------
universe
list ['600340.SH','000001.SZ']
factors
'close,volume'
start_datetime
'2017-10-11 09:00:00'
end_date
'2017-10-21 15:00:00'
names
list of str,列别名,默认为None,仅对单标的多因素
options
其他参数
Returns
--------
DataFrame
Notes
----------
universe与factors最多有一个是多维。
'''
options = dict_2_str(options)
universe_wind = ','.join(universe)
data = w.wsi(universe_wind, factors, start_datetime, end_datetime, options)
if names is not None:
df = pd.DataFrame(data.Data, columns=data.Times, index=names).T
else:
df = pd.DataFrame(data.Data, columns=data.Times, index=data.Fields).T
return df
def data_handle(self, symbol, end_date, create_condition, drop_condition):
db = self.connect2db()
cursor = db.cursor()
name_lis = Util.get_code_split(symbol)
table_name = name_lis[0]
self.drop_existed_table(db, table_name, drop_condition)
self.create_new_table(db, table_name, create_condition)
start_date = self.find_start_date(db, symbol, table_name)
future = w.wsi(symbol, 'open, high, low, close',
"{:%Y-%m-%d} 15:00:00".format(start_date),
"{:%Y-%m-%d} 16:00:00".format(end_date),
"BarSize={:d}".format(self.freq))
df = pd.DataFrame(future.Data[3],
columns=['close'],
index=future.Times)
ret_df = np.log(df).diff(1).iloc[1:]
sql3 = '''INSERT INTO {:s}_{:d}minute (TRADE_TIME, TIME_STAMP, OPEN, HIGH, LOW, CLOSE, LOG_RETURN
) VALUES (%s, %s, %s, %s, %s, %s, %s)'''.format(
table_name, self.freq)
param = [
(future.Times[i + 1].strftime('%Y-%m-%d %H:%M:%S'),
future.Times[i + 1].hour * 60 * 60 +
future.Times[i + 1].minute * 60, future.Data[0][i + 1],
future.Data[1][i + 1], future.Data[2][i + 1],
future.Data[3][i + 1],
None if math.isnan(ret_df.iloc[i, 0]) else float(ret_df.iloc[i,
0]))
for i in xrange(len(future.Data[0][1:]))
]
cursor.executemany(sql3, param)
db.commit()
cursor.close()
def getMinData(self,stockCodeList,fields,startDate,endDate,period):
'''分钟线的具体的获取步骤'''
option = 'BarSize=%s;PriceAdj=F' % period
for stockCode in stockCodeList:
print '添加%s 分钟数据,代碼%s' % (period,stockCode)
wsi_data = w.wsi(stockCode,fields,startDate,endDate,option)
if wsi_data.ErrorCode==0: # 返回成功
stockDate = wsi_data.Times
timeList = [time.strftime("%Y-%m-%d %H-%M-%S") for time in stockDate]
stockDataDict = {'stockCode':stockCode,'time':timeList}
for i in range(len(fields)):
stockDataDict[fields[i]] = wsi_data.Data[i]
stockData = pd.DataFrame(stockDataDict,columns=['stockCode','time']+fields,index=timeList).dropna() # 只要有缺失的数据就删掉这一行，保证数据最为干净
stockData['pct_chg'] = stockData['pct_chg'] / 100 # 让涨幅变为实际涨幅（查询出来的是百分比数据）
# 插入到數據庫中
sql = "insert ignore into stockData_%smin" % period + " values("+"%s,"*(len(fields)+1)+"%s)"
self.cur.executemany(sql,tuple(stockData.values.tolist()))
self.con.commit()
else:
logging.info('ERROR-%s歷史分鐘%smin數據下載失敗' % (stockCode,period))
#获取当前时间
endTime = time.strftime(ISOTIMEFORMAT, time.localtime())
try:
conn = MySQLdb.connect(host='127.0.0.1',user='******',passwd='root',db='MyChartTest',port=3306)
cur = conn.cursor()
conn.set_character_set('utf8')
#获取历史最新时间
cur.execute("select date_add(max(time), interval 1 minute) from bwsi where trade_code = '" + trade_code + "'" )
beginTime = cur.fetchone()[0]
#调用wind接口
w.start();
data=w.wsi(trade_code, "open,high,low,close,volume,amt", beginTime, endTime, "BarSize=1")
w.close();
if data.ErrorCode == 0:
t = 0
j = len(data.Fields)
print data.Codes
print data.Fields
volume_idx = data.Fields.index('volume')
amt_idx = data.Fields.index('amount')
values = []
for time in data.Times:
value = [u'龙净环保',data.Codes[0],time]
print time,
Created on Mon Jul 24 15:13:54 2017
分钟数据网格
国债期货TF1703、TF1706、TF1709
"""
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
#import tushare as ts
from math import isnan
from WindPy import w
from 参数生成 import parameters
###########################数据获取与处理############
w.start()
df1 = w.wsi('tf1703.cfe', 'open,close', '2016-12-21', '2017-01-24')
df1 = pd.DataFrame(df1.Data, index=df1.Fields, columns=df1.Times)
df1 = df1.T
df1 = df1.dropna()
df1.columns = ['open', 'close']
return1 = df1.ix[:, 'close'] / df1.ix[:, 'close'].shift(1)
df2 = w.wsi('tf1706.cfe', 'open,close', '2017-01-23', '2017-05-10')
df2 = pd.DataFrame(df2.Data, index=df2.Fields, columns=df2.Times)
df2 = df2.T
df2 = df2.dropna()
df2.columns = ['open', 'close']
return2 = df2.ix[:, 'close'] / df2.ix[:, 'close'].shift(1)
df3 = w.wsi('tf1709.cfe', 'open,close', '2017-05-09')
df3 = pd.DataFrame(df3.Data, index=df3.Fields, columns=df3.Times)
def my_job():
STOCK_ASSETS = []
# time Init
allstart = time.time()
#now = datetime.datetime.now()
# 测试用数据
now = datetime.datetime(2017, 5, 11, 15, 1, 0)
nowdatetime = now.strftime('%Y-%m-%d %H:%M:%S')
nowdate = now.strftime('%Y-%m-%d')
startcoll = datetime.datetime(2015, 5, 11)
startdate = startcoll.strftime('%Y-%m-%d')
# Wind Python Modules Init
w.start()
print("********************************")
print("Wind数据中心在线API是否可用 %s" % (w.isconnected()))
# MySQL Connection
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db='wind',
port=3306,
charset='utf8')
# 从数据库获取当前所有的股票代码
try:
with connection.cursor() as cursor:
sql = "SELECT ss_code FROM stock_summary"
ss_codes_cnt = cursor.execute(sql)
ss_codes = cursor.fetchmany(ss_codes_cnt)
for ii in ss_codes:
STOCK_ASSETS.append(ii[0])
finally:
print("********************************")
print("获取到数据库中所有的股票信息")
# 获取今天全部的A股信息
sectorconstituent = w.wset(
"sectorconstituent", "date=" + nowdate + ";sectorid=a001010100000000")
if sectorconstituent.ErrorCode != 0:
exit(-1)
# 获取退市的股票代码，并从数据库中删除
print('********************************')
print('检查退市股票')
needdelete = []
for stock in STOCK_ASSETS:
if stock not in sectorconstituent.Data[1]:
needdelete.append(stock)
try:
with connection.cursor as cursor:
pseudo_code = utils.MyUtils.stockconvert(stock)
# 清空数据表，重新获取数据
cursor.execute("DROP TABLE `stock_data_week_" +
pseudo_code + "` ")
cursor.execute("DROP TABLE `stock_data_day_" +
pseudo_code + "` ")
cursor.execute("DROP TABLE `stock_data_hour_" +
pseudo_code + "` ")
cursor.execute(
"DELETE FROM stock_summary where ss_code='%s'" % stock)
connection.commit()
finally:
print('退市股票删除完毕:%s' % needdelete)
for stock in needdelete:
id = STOCK_ASSETS.index(stock)
del STOCK_ASSETS[id]
# 获取数据库中已有股票的复权信息
rec_code = []
if len(STOCK_ASSETS) > 0:
try:
with connection.cursor() as cursor:
DAY_OFFSET = -1
d_today = nowdate
d_before_yesterday = w.tdaysoffset(
DAY_OFFSET,
d_today.replace('-', '')).Data[0][0].strftime('%Y-%m-%d')
print("获取复权因子，日期范围[%s ~ %s]" % (d_before_yesterday, d_today))
ret = w.wsd(STOCK_ASSETS, "adjfactor", d_before_yesterday,
d_today, "")
adjfactor = ret.Data
for i in range(len(ret.Codes)):
if adjfactor[i][0] != adjfactor[i][1]:
rec_code.append(ret.Codes[i])
pseudo_code = utils.MyUtils.stockconvert(ret.Codes[i])
# 清空数据表，重新获取数据
cursor.execute("TRUNCATE `stock_data_week_" +
pseudo_code + "` ")
cursor.execute("TRUNCATE `stock_data_day_" +
pseudo_code + "` ")
cursor.execute("TRUNCATE `stock_data_hour_" +
pseudo_code + "` ")
connection.commit()
finally:
print("复权因子发生变化的股票列表 : %s" % rec_code)
# 从Wind获取所有A股股票的上市日期
all_market_date = w.wss(sectorconstituent.Data[1], 'ipo_date')
stockdict = {}
for i in range(len(all_market_date.Codes)):
stock = all_market_date.Codes[i]
stockdict[stock] = all_market_date.Data[0][i]
# 保存需要插入的股票数据(上市日期满一年才可进入数据库)
print("********************************")
print("检查新增股票")
needinsertion = []
needcreation = []
i = 0
for stock in sectorconstituent.Codes:
ss_market = "SH" # 默认市场为上证
ss_code = sectorconstituent.Data[1][i]
ss_name = sectorconstituent.Data[2][i]
i += 1
if ss_code in STOCK_ASSETS:
continue
else:
if "SZ" in ss_code or "sz" in ss_code: # 设置市场
ss_market = "SZ"
ssmarket_date = stockdict[ss_code]
if (now - ssmarket_date).days >= 365:
needinsertion.append(
[ss_code, ss_name, ss_market, ssmarket_date])
needcreation.append(ss_code)
# 测试用数据
#needcreation = needcreation[:4]
#needinsertion = needinsertion[:4]
if len(needcreation) > 0:
print("新增股票列表 : %s" % needcreation)
else:
print("无新增股票")
# 创建数据库里面没有的表week day hour
if len(needinsertion) > 0:
print("********************************")
print("开始创建新增股票所需数据表")
start = time.time()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO stock_summary (ss_code, ss_name, ss_market ,ss_marketdate) VALUES (%s, %s, %s ,%s)"
cursor.executemany(sql, needinsertion)
for code in needcreation:
code = utils.MyUtils.stockconvert(code)
sql = "CREATE TABLE IF NOT EXISTS `stock_data_week_" + code + "` " \
"( " \
"`_id` int(11) NOT NULL AUTO_INCREMENT, " \
"`open` float(16) DEFAULT NULL COMMENT '开盘价\n.2f元', " \
"`high` float(16) DEFAULT NULL COMMENT '最高价\n.2f元', " \
"`low` float(16) DEFAULT NULL COMMENT '最低价\n.2f元', " \
"`close` float(16) DEFAULT NULL COMMENT '收盘价\n.2f元', " \
"`bs_signal_rule1` varchar(10) DEFAULT NULL COMMENT '规则1信号',"\
"`datetime` datetime DEFAULT NULL COMMENT '时间', " \
"`EMAb_arg3` float(16) DEFAULT 0 COMMENT 'EMA_N为3的买入信号'," \
"`EMAb_arg5` float(16) DEFAULT 0 COMMENT 'EMA_N为5的买入信号'," \
"`EMAb_arg8` float(16) DEFAULT 0 COMMENT 'EMA_N为8的买入信号'," \
"`EMAb_arg13` float(16) DEFAULT 0 COMMENT 'EMA_N为13的买入信号'," \
"`EMAs1` float(16) DEFAULT 0 COMMENT '卖出信号1'," \
"`EMAs2` float(16) DEFAULT 0 COMMENT '卖出信号2'," \
"`guide` float(16) DEFAULT 0 COMMENT '指导线',"\
"`MA` float(16) DEFAULT 0 COMMENT '移动平均',"\
"PRIMARY KEY (`_id`), " \
"UNIQUE KEY `_id_UNIQUE` (`_id`)) " \
"ENGINE=InnoDB " \
"DEFAULT CHARSET=utf8;"
# cursor.executemany(sql.replace("'", ""), needcreation)
cursor.execute(sql)
sql = "CREATE TABLE IF NOT EXISTS `stock_data_day_" + code + "` " \
"( " \
"`_id` int(11) NOT NULL AUTO_INCREMENT, " \
"`open` float(16) DEFAULT NULL COMMENT '开盘价\n.2f元', " \
"`high` float(16) DEFAULT NULL COMMENT '最高价\n.2f元', " \
"`low` float(16) DEFAULT NULL COMMENT '最低价\n.2f元', " \
"`close` float(16) DEFAULT NULL COMMENT '收盘价\n.2f元', " \
"`bs_signal_rule1` varchar(10) DEFAULT NULL COMMENT '规则1信号'," \
"`datetime` datetime DEFAULT NULL COMMENT '时间', " \
"`EMAb_arg3` float(16) DEFAULT 0 COMMENT 'EMA_N为3的买入信号'," \
"`EMAb_arg5` float(16) DEFAULT 0 COMMENT 'EMA_N为5的买入信号'," \
"`EMAb_arg8` float(16) DEFAULT 0 COMMENT 'EMA_N为8的买入信号'," \
"`EMAb_arg13` float(16) DEFAULT 0 COMMENT 'EMA_N为13的买入信号'," \
"`EMAs1` float(16) DEFAULT 0 COMMENT '卖出信号1'," \
"`EMAs2` float(16) DEFAULT 0 COMMENT '卖出信号2'," \
"`guide` float(16) DEFAULT 0 COMMENT '指导线'," \
"`MA` float(16) DEFAULT 0 COMMENT '移动平均'," \
"PRIMARY KEY (`_id`), " \
"UNIQUE KEY `_id_UNIQUE` (`_id`)) " \
"ENGINE=InnoDB " \
"DEFAULT CHARSET=utf8;"
# cursor.executemany(sql, needcreation)
cursor.execute(sql)
sql = "CREATE TABLE IF NOT EXISTS `stock_data_hour_" + code + "` " \
"( " \
"`_id` int(11) NOT NULL AUTO_INCREMENT, " \
"`open` float(16) DEFAULT NULL COMMENT '开盘价\n.2f元', " \
"`high` float(16) DEFAULT NULL COMMENT '最高价\n.2f元', " \
"`low` float(16) DEFAULT NULL COMMENT '最低价\n.2f元', " \
"`close` float(16) DEFAULT NULL COMMENT '收盘价\n.2f元', " \
"`bs_signal_rule1` varchar(10) DEFAULT NULL COMMENT '规则1信号'," \
"`datetime` datetime DEFAULT NULL COMMENT '时间', " \
"`EMAb_arg3` float(16) DEFAULT 0 COMMENT 'EMA_N为3的买入信号'," \
"`EMAb_arg5` float(16) DEFAULT 0 COMMENT 'EMA_N为5的买入信号'," \
"`EMAb_arg8` float(16) DEFAULT 0 COMMENT 'EMA_N为8的买入信号'," \
"`EMAb_arg13` float(16) DEFAULT 0 COMMENT 'EMA_N为13的买入信号'," \
"`EMAs1` float(16) DEFAULT 0 COMMENT '卖出信号1'," \
"`EMAs2` float(16) DEFAULT 0 COMMENT '卖出信号2'," \
"`guide` float(16) DEFAULT 0 COMMENT '指导线'," \
"`MA` float(16) DEFAULT 0 COMMENT '移动平均'," \
"PRIMARY KEY (`_id`), " \
"UNIQUE KEY `_id_UNIQUE` (`_id`))" \
"ENGINE=InnoDB " \
"DEFAULT CHARSET=utf8;"
# cursor.executemany(sql, needcreation)
cursor.execute(sql)
print("新增[%s]结束..." % code)
connection.commit()
finally:
print("所有新增股票数据表创建完毕\n耗时 %.2f s" % (time.time() - start))
# 将复权因子发生变化的股票以及股票池中没有的股票合并，用同样的程序进行更新
needcreation.extend(rec_code)
# 更新股票数据
print("********************************")
print("开始增量同步股票数据")
#needcreation = needcreation[329:]
if len(needcreation) > 0:
try:
with connection.cursor() as cursor:
for code in needcreation:
pseudo_code = utils.MyUtils.stockconvert(code)
ssmarket_date = stockdict[code]
print("开始处理[%s]..." % code)
wus = startcoll
dus = startcoll
hus = startcoll
if ssmarket_date.strftime('%Y-%m-%d') > startcoll.strftime(
'%Y-%m-%d'):
wus = ssmarket_date
dus = ssmarket_date
hus = ssmarket_date
usdate = dus.strftime('%Y-%m-%d')
suspstatus = list(
w.wsd(code, "trade_status", usdate, usdate,
"").Data[0][0])
if '停' in suspstatus:
suspstatus = w.wsd(code, "trade_status", usdate,
nowdate, "")
if '交易' in suspstatus.Data[0]:
id = suspstatus.Data[0].index('交易')
ontrade = suspstatus.Times[id]
wus = ontrade
dus = ontrade
hus = ontrade
else:
cursor.execute("DROP TABLE `stock_data_week_" +
pseudo_code + "` ")
cursor.execute("DROP TABLE `stock_data_day_" +
pseudo_code + "` ")
cursor.execute("DROP TABLE `stock_data_hour_" +
pseudo_code + "` ")
cursor.execute(
"DELETE FROM stock_summary where ss_code='%s'"
% code)
connection.commit()
continue
# 获取该支股票的全部小时数据
husstr = hus.strftime('%Y-%m-%d %H:%M:%S')
print("开始下载[%s]时数据，日期范围[%s ~ %s]" %
(code, husstr, nowdatetime))
ret = w.wsi(code, "open,high,low,close", husstr,
nowdatetime,
"BarSize=60;PriceAdj=F;Fill=Previous")
# 加一个断网判断程序
EMAb_arg3 = np.array(utils.bssignal1.EMA_buy(
ret.Data[3])).tolist()
EMAb_arg5 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=5)).tolist()
EMAb_arg8 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=8)).tolist()
EMAb_arg13 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=13)).tolist()
EMAs1 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 13, 89,
21)).tolist()
EMAs2 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 21, 55,
20)).tolist()
guide1 = (np.array(EMAb_arg3) + np.array(EMAb_arg5) +
np.array(EMAb_arg8) + np.array(EMAb_arg13)) / 4
guide1 = guide1.tolist()
guide = np.array(utils.bssignal1.EMA_buy(guide1)).tolist()
MA = np.array(utils.bssignal1.MA(ret.Data[3])).tolist()
# 确认是否有买卖信号
bs_signal_rule1 = utils.bssignal1.rule1(EMAb_arg3, EMAs1)
params = ret.Data
params.extend([
bs_signal_rule1, EMAb_arg3, EMAb_arg5, EMAb_arg8,
EMAb_arg13, EMAs1, EMAs2, guide, MA
])
params.append(ret.Times)
params = np.array(params[:14]).T.tolist()
sql = 'REPLACE INTO stock_data_hour_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.executemany(sql, params)
sql = 'UPDATE stock_summary set ' \
'ss_hour_update_status = 1, ' \
'ss_hour_update_start = %s, ss_hour_update_end = %s ' \
'where ss_code = %s'
cursor.execute(sql, (hus, now, code))
# 获取该支股票的全部天数据
dusstr = dus.strftime('%Y-%m-%d')
print("开始下载[%s]日数据，日期范围[%s ~ %s]" %
(code, dusstr, nowdate))
ret = w.wsd(code, "open,high,low,close", dusstr, nowdate,
"Period=D;Fill=Previous;PriceAdj=F")
EMAb_arg3 = np.array(utils.bssignal1.EMA_buy(
ret.Data[3])).tolist()
EMAb_arg5 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=5)).tolist()
EMAb_arg8 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=8)).tolist()
EMAb_arg13 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=13)).tolist()
EMAs1 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 13, 89,
21)).tolist()
EMAs2 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 21, 55,
20)).tolist()
guide1 = (np.array(EMAb_arg3) + np.array(EMAb_arg5) +
np.array(EMAb_arg8) + np.array(EMAb_arg13)) / 4
guide1 = guide1.tolist()
guide = np.array(utils.bssignal1.EMA_buy(guide1)).tolist()
MA = np.array(utils.bssignal1.MA(ret.Data[3])).tolist()
# 确认是否有买卖信号
bs_signal_rule1 = utils.bssignal1.rule1(EMAb_arg3, EMAs1)
params = ret.Data # 确认是否有买卖信号
params.extend([
bs_signal_rule1, EMAb_arg3, EMAb_arg5, EMAb_arg8,
EMAb_arg13, EMAs1, EMAs2, guide, MA
])
params.append(ret.Times)
params = np.array(params[:14]).T.tolist()
sql = 'REPLACE INTO stock_data_day_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.executemany(sql, params)
sql = 'UPDATE stock_summary set ' \
'ss_day_update_status = 1, ' \
'ss_day_update_start = %s, ss_day_update_end = %s ' \
'where ss_code = %s'
cursor.execute(sql, (dus, now, code))
# 获取该支股票的全部周数据
wusstr = wus.strftime('%Y-%m-%d')
print("开始下载[%s]周数据，日期范围[%s ~ %s]" %
(code, wusstr, nowdate))
ret = w.wsd(
code, "open,high,low,close", wusstr, nowdate,
"Period=W;Days=Weekdays;Fill=Previous;PriceAdj=F")
EMAb_arg3 = np.array(utils.bssignal1.EMA_buy(
ret.Data[3])).tolist()
EMAb_arg5 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=5)).tolist()
EMAb_arg8 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=8)).tolist()
EMAb_arg13 = np.array(
utils.bssignal1.EMA_buy(ret.Data[3],
EMA_N=13)).tolist()
EMAs1 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 13, 89,
21)).tolist()
EMAs2 = np.array(
utils.bssignal1.EMA_sell(ret.Data[3], 21, 55,
20)).tolist()
guide1 = (np.array(EMAb_arg3) + np.array(EMAb_arg5) +
np.array(EMAb_arg8) + np.array(EMAb_arg13)) / 4
guide1 = guide1.tolist()
guide = np.array(utils.bssignal1.EMA_buy(guide1)).tolist()
MA = np.array(utils.bssignal1.MA(ret.Data[3])).tolist()
# 确认是否有买卖信号
bs_signal_rule1 = utils.bssignal1.rule1(EMAb_arg3, EMAs1)
params = ret.Data
params.extend([
bs_signal_rule1, EMAb_arg3, EMAb_arg5, EMAb_arg8,
EMAb_arg13, EMAs1, EMAs2, guide, MA
])
params.append(ret.Times)
params = np.array(params[:14]).T.tolist()
update_de = ret.Times[-1]
if now.weekday() in [0, 1, 2, 3]:
params = params[:-1]
update_de = ret.Times[-2:-1]
sql = 'REPLACE INTO stock_data_week_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.executemany(sql, params)
sql = 'UPDATE stock_summary set ' \
'ss_week_update_status = 1, ' \
'ss_week_update_start = %s, ss_week_update_end = %s ' \
'where ss_code = %s'
cursor.execute(sql, (wus, update_de, code))
connection.commit()
finally:
print("上市满一年的新股以及需重新复权的股票已更新完毕")
# 更新股票池中已有的股票
STOCK_ASSETS = []
try:
with connection.cursor() as cursor:
data_cnt = cursor.execute("SELECT ss_code FROM stock_summary")
data = cursor.fetchmany(data_cnt)
for ii in data:
STOCK_ASSETS.append(ii[0])
finally:
print('股票池中已有股票查询完毕')
hav_stock = list(set(STOCK_ASSETS).difference(needcreation))
if len(hav_stock) > 0:
hour_start = datetime.datetime(now.year, now.month, now.day, 10, 0,
0).strftime('%Y-%m-%d %H:%M:%S')
number = 100
bracket = int(len(hav_stock) / number)
datadf = pd.DataFrame()
print('需要循环%s次得到今天的所有股票数据' % bracket)
for i in range(bracket):
bracket_slice = hav_stock[i * number:(i + 1) * number]
ret = w.wsi(bracket_slice, "open,high,low,close", hour_start,
nowdatetime, 'Barsize=60;PriceAdj=F;Fill=Previous')
# 判断ret是否正常获得的语句
if ret.ErrorCode != 0:
print(i)
#exit(1)
ssd = ret.Data[2:]
ssd.append(ret.Data[0])
datadf1 = pd.DataFrame(ssd, columns=ret.Data[1])
datadf = pd.concat([datadf, datadf1], axis=1)
print("第%s次循环结束" % i)
bracket_slice = hav_stock[bracket * number:]
ret = w.wsi(bracket_slice, "open,high,low,close", hour_start,
nowdatetime, 'Barsize=60;PriceAdj=F;Fill=Previous')
ssd = ret.Data[2:]
ssd.append(ret.Data[0])
datadf1 = pd.DataFrame(ssd, columns=ret.Data[1])
datadf = pd.concat([datadf, datadf1], axis=1)
ToF = np.isnan(list(np.array(datadf)[0, :]))
nanum = sum([1 if False else 0 for i in ToF])
if nanum == 0:
#print(datadf)
try:
with connection.cursor() as cursor:
for code in hav_stock:
pseudo_code = utils.MyUtils.stockconvert(code)
stockdf = datadf[code].values.tolist()
# 查询该支股票的后(slope_N-1)行数据，得到信号1的bs数据
slope_N = 13
extraray = utils.sql_select_tailn.sql_tail_hour(
'close,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,guide',
cursor, pseudo_code, (slope_N - 1))
close = extraray[:, 0].tolist()
close.extend(stockdf[3])
closeb = [extraray[-1, 0]]
closeb.extend(stockdf[3])
hour_EMAs1 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 5].tolist(), 13, 89,
21)).tolist()[(slope_N - 1):]
hour_EMAb_arg3 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 1]])).tolist()[1:]
hour_EMAb_arg5 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 2]])).tolist()[1:]
hour_EMAb_arg8 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 3]])).tolist()[1:]
hour_EMAb_arg13 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 4]])).tolist()[1:]
guide1 = (np.array(hour_EMAb_arg3) +
np.array(hour_EMAb_arg5) +
np.array(hour_EMAb_arg8) +
np.array(hour_EMAb_arg13)) / 4
guide1 = guide1.tolist()
guide_data = [0]
guide_data.extend(guide1)
hour_guide = np.array(
utils.bssignal.EMA_buy(
guide_data, [extraray[-1, 6]])).tolist()[1:]
#产生买卖信号
hour_EMAb = [extraray[-1, 1]]
hour_EMAb.extend(hour_EMAb_arg3)
hour_EMAs = [extraray[-1, 5]]
hour_EMAs.extend(hour_EMAs1)
hour_bs_signal_rule1 = utils.bssignal.rule1(
EMAb=hour_EMAb, EMAs=hour_EMAs)[1:]
# 查询该支股票的后(slope_N-1)行数据，得到信号2的bs数据
slope_N = 21
extraray = utils.sql_select_tailn.sql_tail_hour(
'close,EMAs2', cursor, pseudo_code, (slope_N - 1))
close = extraray[:, 0].tolist()
close.extend(stockdf[3])
hour_EMAs2 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 1].tolist(), 21, 55,
20)).tolist()[(slope_N - 1):]
# 查询该支股票的后（MA_N-1）行数据，得到MA指标
MA_N = 55
extraray = utils.sql_select_tailn.sql_tail_hour(
'close', cursor, pseudo_code, (MA_N - 1))
close = extraray[:, 0].tolist()
close.extend(stockdf[3])
hour_MA = np.array(utils.bssignal.MA(close)).tolist()
# 得到需要写入sql的数据
params_hour = []
for i in range(len(stockdf[0])):
iopen = stockdf[0][i]
ihigh = stockdf[1][i]
ilow = stockdf[2][i]
iclose = stockdf[3][i]
ihour_bs_signal_rule1 = hour_bs_signal_rule1[i]
iEMAb_arg3 = hour_EMAb_arg3[i]
iEMAb_arg5 = hour_EMAb_arg5[i]
iEMAb_arg8 = hour_EMAb_arg8[i]
iEMAb_arg13 = hour_EMAb_arg13[i]
iEMAs1 = hour_EMAs1[i]
iEMAs2 = hour_EMAs2[i]
iguide = hour_guide[i]
iMA = hour_MA[i]
idatetime = stockdf[4][i]
params1 = [
iopen, ihigh, ilow, iclose,
ihour_bs_signal_rule1, iEMAb_arg3, iEMAb_arg5,
iEMAb_arg8, iEMAb_arg13, iEMAs1, iEMAs2,
iguide, iMA, idatetime
]
params_hour.append(params1)
# 通过小时数据构造天数据
day_close = stockdf[3][-1]
day_open = stockdf[0][0]
day_high = max(stockdf[1])
day_low = min(stockdf[2])
day_datetime = datetime.datetime(
now.year, now.month, now.day, 0, 0, 0)
# 查询该支股票的后(slope_N-1)行数据，得到信号1的bs数据
slope_N = 13
extraray = utils.sql_select_tailn.sql_tail_day(
'close,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,guide',
cursor, pseudo_code, (slope_N - 1))
close = extraray[:, 0].tolist()
close.append(day_close)
closeb = [extraray[-1, 0], day_close]
day_EMAs1 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 5].tolist(), 13, 89,
21)).tolist()[(slope_N - 1):][0]
day_EMAb_arg3 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 1]])).tolist()[1:][0]
day_EMAb_arg5 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 2]])).tolist()[1:][0]
day_EMAb_arg8 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 3]])).tolist()[1:][0]
day_EMAb_arg13 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 4]])).tolist()[1:][0]
guide1 = (day_EMAb_arg3 + day_EMAb_arg5 +
day_EMAb_arg8 + day_EMAb_arg13) / 4
guide_data = [0, guide1]
day_guide = np.array(
utils.bssignal.EMA_buy(
guide_data, [extraray[-1, 6]])).tolist()[1:][0]
# 产生买卖信号
day_EMAb = [extraray[-1, 1]]
day_EMAb.append(day_EMAb_arg3)
day_EMAs = [extraray[-1, 5]]
day_EMAs.append(day_EMAs1)
day_bs_signal_rule1 = utils.bssignal.rule1(
EMAb=day_EMAb, EMAs=day_EMAs)[1]
# 查询该支股票的后(slope_N-1)行数据，得到信号2的bs数据
slope_N = 21
extraray = utils.sql_select_tailn.sql_tail_day(
'close,EMAs2', cursor, pseudo_code, (slope_N - 1))
close = extraray[:, 0].tolist()
close.append(day_close)
day_EMAs2 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 1].tolist(), 21, 55,
20)).tolist()[(slope_N - 1):][0]
# 查询该支股票的后（MA_N-1）行数据，得到MA指标
MA_N = 55
extraray = utils.sql_select_tailn.sql_tail_day(
'close', cursor, pseudo_code, (MA_N - 1))
close = extraray[:, 0].tolist()
close.extend(stockdf[3])
day_MA = np.array(utils.bssignal.MA(close)).tolist()[0]
# 构造需要写入sql的数据
params_day = [
day_open, day_high, day_low, day_close,
day_bs_signal_rule1, day_EMAb_arg3, day_EMAb_arg5,
day_EMAb_arg8, day_EMAb_arg13, day_EMAs1,
day_EMAs2, day_guide, day_MA, day_datetime
]
# 构造周数据
if now.weekday() == 4:
week_close = day_close
week_open = extraray[-4, 3]
high_for4 = list(extraray[-4:, 4])
high_for4.append(day_high)
week_high = max(high_for4)
low_for4 = list(extraray[-4:, 5])
low_for4.append(day_low)
week_low = min(low_for4)
week_datetime = day_datetime
# 查询该支股票的后(slope_N-1)行数据，得到信号1的bs数据
slope_N = 13
extraray = utils.sql_select_tailn.sql_tail_week(
'close,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,guide',
cursor, pseudo_code, (slope_N - 1))
close = extraray[:, 0].tolist()
close.append(week_close)
closeb = [extraray[-1, 0], week_close]
week_EMAs1 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 5].tolist(), 13, 89,
21)).tolist()[(slope_N - 1):][0]
week_EMAb_arg3 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 1]])).tolist()[1:][0]
week_EMAb_arg5 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 2]])).tolist()[1:][0]
week_EMAb_arg8 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 3]])).tolist()[1:][0]
week_EMAb_arg13 = np.array(
utils.bssignal.EMA_buy(
closeb, [extraray[-1, 4]])).tolist()[1:][0]
guide1 = (week_EMAb_arg3 + week_EMAb_arg5 +
week_EMAb_arg8 + week_EMAb_arg13) / 4
guide_data = [0, guide1]
week_guide = np.array(
utils.bssignal.EMA_buy(
guide_data,
[extraray[-1, 6]])).tolist()[1:][0]
# 产生买卖信号
week_EMAb = [extraray[-1, 1]]
week_EMAb.append(week_EMAb_arg3)
week_EMAs = [extraray[-1, 5]]
week_EMAs.append(week_EMAs1)
week_bs_signal_rule1 = utils.bssignal.rule1(
EMAb=week_EMAb, EMAs=week_EMAs)[1]
# 查询该支股票的后(slope_N-1)行数据，得到信号2的bs数据
slope_N = 21
extraray = utils.sql_select_tailn.sql_tail_week(
'close,EMAs2', cursor, pseudo_code,
(slope_N - 1))
close = extraray[:, 0].tolist()
close.append(week_close)
week_EMAs2 = np.array(
utils.bssignal.EMA_sell(
close, extraray[:, 1].tolist(), 21, 55,
20)).tolist()[(slope_N - 1):][0]
# 查询该支股票的后（MA_N-1）行数据，得到MA指标
MA_N = 55
MA_cnt = cursor.execute(
'select MA from stock_data_week_' +
pseudo_code)
if MA_cnt >= (MA_N - 1):
extraray = utils.sql_select_tailn.sql_tail_week(
'close', cursor, pseudo_code, (MA_N - 1))
close = extraray[:, 0].tolist()
close.extend(stockdf[3])
week_MA = np.array(
utils.bssignal.MA(close)).tolist()[0]
else:
week_MA = 0
# 构造需要写入sql的数据
params_week = [
week_open, week_high, week_low, week_close,
week_bs_signal_rule1, week_EMAb_arg3,
week_EMAb_arg5, week_EMAb_arg8,
week_EMAb_arg13, week_EMAs1, week_EMAs2,
week_guide, week_MA, week_datetime
]
sql = 'REPLACE INTO stock_data_week_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.execute(sql, params_week)
update_de = datetime.datetime(now.year, now.month,
now.day, 15, 1, 0)
hus = day_datetime
sql = 'REPLACE INTO stock_data_hour_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.executemany(sql, params_hour)
sql = 'REPLACE INTO stock_data_day_' + pseudo_code + \
' (open, high, low, close,bs_signal_rule1,EMAb_arg3,EMAb_arg5,EMAb_arg8,EMAb_arg13,EMAs1,EMAs2,guide,MA,datetime)' \
' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.execute(sql, params_day)
sql = 'UPDATE stock_summary set ' \
'ss_hour_update_status = 1, ' \
'ss_day_update_status = 1, '\
'ss_week_update_status = 1, ' \
'ss_hour_update_start = %s, ss_hour_update_end = %s, ' \
'ss_day_update_start = %s, ss_day_update_end = %s ,'\
'ss_week_update_start = %s, ss_week_update_end = %s ' \
'where ss_code = %s'
cursor.execute(sql, (hus, update_de, hus, update_de,
hus, update_de, code))
connection.commit()
print("%s今日股票数据更新完毕" % code)
finally:
print("********************************")
print("本次数据更新全部结束\n耗时:%.2f s" % (time.time() - allstart))
connection.close()
print("数据库安全关闭")
print("祝您生活愉快!")
else:
print("存在NAN类型的数据")
connection.close()
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")
printpy(wstdata)
# 通过wsi来取日内分钟数据
print('\n\n' + '-----通过wsi来取日内分钟数据-----' + '\n')
wsidata = w.wsi("IF.CFE", "open,high,low,close,volume,amt", "2015-12-22 09:00:00",
"2015-12-22 14:06:15")
printpy(wsidata)
# 通过wset来取数据集数据
print('\n\n' + '-----通过wset来取数据集数据,获取沪深300指数权重-----' + '\n')
wsetdata = w.wset("IndexConstituent",
"date=20151222;windcode=000300.SH;field=date,wind_code,i_weight")
printpy(wsetdata)
__author__ = 'aming.tao'
from WindPy import w
from datetime import *
w.start()
data=w.wsd("600000.SH","close,amt","2013-04-30", datetime.today()-timedelta(1))#取浦发银行收盘价等信
data=w.wsd("600000.SH","close,amt", datetime.today()-timedelta(100))#
data=w.wsi("600000.SH","close,amt","2015-10-01 9:00:00")#取浦发银行分钟收盘价等信息
data=w.wst("600000.SH","open", datetime.today()-timedelta(0,2*3600), datetime.now())#取浦发银行tick数据信息
data=w.wss("600000.SH,000001.SZ","eps_ttm,orps,surpluscapitalps","rptDate=20121231")#取浦发银行等财务数据信息
data=w.wset("SectorConstituent",u"date=20130608;sector=全部A股")#取全部A 股股票代码、名称信息
w.wset("IndexConstituent","date=20130608;windcode=000300.SH;field=wind_code,i_weight")#取沪深300 指数中股票代码和权重
w.wset("TradeSuspend","startdate=20130508;enddate=20130608;field=wind_code,sec_name,suspend_type,suspend_reason")#取停牌信息
w.wset("SectorConstituent",u"date=20130608;sector=风险警示股票;field=wind_code,sec_name")#取ST 股票等风险警示股票信息
w.tdays("2013-05-01","2013-06-08")#返回5 月1 日到6 月8 日之间的交易日序列
w.tdays("2013-05-01")#返回5 月1 日到当前时间的交易日序列
w.tdaysoffset(-5,"2013-05-01")#返回5 月1 日前推五个交易日的日期，返回2013-4-19
w.tdaysoffset(-5)#返回当前时间前推五个交易日的日期
w.tdayscount("2013-05-01","2013-06-08")#返回5 月1 日到6 月8 日之间的交易日序列长度，为27
w.stop()
long_list = "MA805.CZC,C1805.DCE,ZC805.CZC,A1805.DCE,RU1805.SHF".split(",")
short_list = "J1805.DCE,RB1805.SHF,RM805.CZC,I1805.DCE,JM1805.DCE".split(",")
cnt_size = [6, 19, 11, 10, 1, -1, -15, -11, -6, -3]
cnt_data = w.wss(cnt_list_str, "contractmultiplier")
cnt_data = pd.DataFrame(cnt_data.Data,
columns=cnt_data.Codes,
index=cnt_data.Fields).T
cnt_data["size"] = cnt_size
#设置起止时间
start_time = "2018-02-12 14:59:00"
end_time = "2018-02-13 8:00:00"
#提取分钟数据
minute_data = w.wsi(cnt_list_str, "close", start_time, end_time, "")
minute_data = pd.DataFrame(minute_data.Data, index=minute_data.Fields).T
minute_data["windcode"] = minute_data["windcode"].apply(str)
minute_data.set_index(["time", "windcode"], inplace=True)
minute_data = minute_data.unstack()
minute_data.columns = minute_data.columns.levels[1]
#处理nan:全部无值的时间跳过；有的合约有值时，无值的合约使用前值作为其最新报价
minute_data.dropna(how="all", inplace=True)
minute_data.fillna(method="ffill", inplace=True)
#计算合约价值
minute_value = minute_data * cnt_data["size"] * cnt_data["CONTRACTMULTIPLIER"]
minute_value["long_value"] = minute_value.loc[:, long_list].sum(axis=1)
minute_value["short_value"] = minute_value.loc[:, short_list].sum(axis=1)
minute_value["ratio"] = minute_value["long_value"] / (
-minute_value["short_value"])
minute_value["diff"] = minute_value["long_value"] + minute_value["short_value"]
res_day = w.wsd(stock_code, "adjfactor", start_date, end_date, "Period=D;{0}Fill=Previous".format(priceAdj))
if res_day.ErrorCode != 0:
print res_day
err_msg = "Error while fetching Date K data.ErrorCode: {0}".format(res_day.ErrorCode)
raise StandardError,err_msg
adjfactor=res_day.Data[0][0]
#3.getKData
#·ÖÖÓÐòÁÐKÏß
# print w.wsi("000001.SZ", "open,close,high,low,volume,amt","2014-11-10 09:00:00", "2014-11-10 02:50:46", "")
# print w.wsi("000001.SZ", "open,close,high,low,volume,amt","2014-11-10 09:00:00", "2014-11-10 11:50:46", "BarSize=30")
# print w.wsd("000001.SZ", fields, "2014-11-17", "2014-11-20", "Fill=Previous")
res = w.wsi(stock_code,fields,start_date,end_date,"{0}{1}Fill=Previous;".format(bar_size, priceAdj))
print res
#add adjfactor to kdata
adjfactor_lst = [] #temp adjfactor list.
for item in res.Times:
adjfactor_lst = adjfactor_lst + [adjfactor]
res.Fields = res.Fields + ['adjfactor']
res.Data = res.Data + [adjfactor_lst]
#start to merge MA data
if res.ErrorCode == 0:
resM5 = w.wsi(stock_code, "MA",start_date,end_date,"{0}{1}Fill=Previous;MA_N=5;".format(bar_size, priceAdj))
print resM5
resM10 = w.wsi(stock_code, "MA",start_date,end_date,"{0}{1}Fill=Previous;MA_N=10;".format(bar_size, priceAdj))
def get_min_data(filename):
file_folder = 'C:\\dev\\src\\ktlib\\data3\\'
reader = csv.reader(file(filename, 'rb'))
contList = []
fields = 'open,close,high,low,volume,oi'
contStart = []
contEnd = []
for line in reader:
contList.append(line[0])
contStart.append(line[1])
contEnd.append(line[2])
#w.start()
for cont, startD, endD in zip(contList, contStart, contEnd):
if cont[1].isalpha(): key = cont[:2]
else: key = cont[:1]
ex = 'SH'
for exch in product_code.keys():
if key in product_code[exch]:
ex = exch
ticker = cont + '.' + ex
startmin = ""
endMin = ""
if ex == 'CFE':
startMin = " 09:14:00"
endMin = " 15:15:00"
else:
startMin = " 08:59:00"
endMin = " 15:00:00"
if ex == 'CFE':
startMin = " 09:14:00"
endMin = " 15:15:00"
else:
startMin = " 08:59:00"
endMin = " 15:00:00"
try:
sdate = datetime.datetime.strptime(startD, "%Y/%m/%d")
edate = datetime.datetime.strptime(endD, "%Y/%m/%d")
sMin = datetime.datetime.strptime(startD + startMin, "%Y/%m/%d %H:%M:%S")
eMin = datetime.datetime.strptime(endD + endMin, "%Y/%m/%d %H:%M:%S")
print ticker, sMin, eMin
if edate > datetime.date(2009,1,1):
raw_data = w.wsi(ticker,fields,sMin,eMin)
if len(raw_data.Data)>1:
outfile = file_folder + cont+'_min.csv'
output={'datetime':raw_data.Times,
'open':raw_data.Data[0],
'close':raw_data.Data[1],
'high':raw_data.Data[2],
'low':raw_data.Data[3],
'volume':raw_data.Data[4],
'openInterest':raw_data.Data[5]}
dump2csvfile(output,outfile)
else:
print "no min data obtained for ticker=%s" % ticker
raw_data = w.wsd(ticker,fields,sdate,edate)
if len(raw_data.Data)>1:
outfile = file_folder + cont+'_daily.csv'
output={'datetime':raw_data.Times,
'open':raw_data.Data[0],
'close':raw_data.Data[1],
'high':raw_data.Data[2],
'low':raw_data.Data[3],
'volume':raw_data.Data[4],
'openInterest':raw_data.Data[5]}
dump2csvfile(output,outfile)
else:
print "no daily data obtained for ticker=%s" % ticker
except ValueError:
pass
w.stop()
return True
reload(sys)
sys.setdefaultencoding( "utf-8" )
from WindPy import w
from Utility import str1D
from Utility import str2D
if (w.isconnected() != True):
w.start(); #pwd607387
stock_code = sys.argv[1]
start_date = sys.argv[2]
end_date = sys.argv[3]
bar_size = sys.argv[4]
fields = "open,close,high,low,volume,amt"
print "WindStockReuslt:"
#3.getKData
#·ÖÖÓÐòÁÐKÏß
# print w.wsi("000001.SZ", "open,close,high,low,volume,amt","2014-11-10 09:00:00", "2014-11-10 02:50:46", "")
# print w.wsi("000001.SZ", "open,close,high,low,volume,amt","2014-11-10 09:00:00", "2014-11-10 11:50:46", "BarSize=30")
# print w.wsd("000001.SZ", fields, "2014-11-17", "2014-11-20", "Fill=Previous")
res = w.wsi(stock_code,fields,start_date,end_date,bar_size)
print res
print "\n.Codes="+str1D(res.Codes)
print "\n.Fields="+str1D(res.Fields)
print "\n.Times="+str1D([ format(x,'%Y%m%d %H:%M:%S') for x in res.Times])
print "\n.Data="+str2D(res.Data)
def getamountperminute(codei, bt=tradebegintime, et=tradeendtime):
windcode = w.htocode(codei, "stocka").Data[0][0]
winobj = w.wsi(windcode, 'amt', beginTime=bt, endTime=et)
dfperminute = pd.DataFrame(columns=['Data'], data=winobj.Data[0])
amountperminute = dfperminute.mean()['Data']
return amountperminute
print(x)
if __name__ == '__main__':
now = datetime.now()
one_month_ago = now - timedelta(days=30)
one_day_ago = now - timedelta(days=3)
stock = '000001.SH' # 上证综合指数
w.start()
print('\n' + '获取过去一个月上证综合指数的日线')
wsddata = w.wsd(stock, "open,high,low,close,volume,amt", one_month_ago, now, "Fill=Previous")
printinfo(wsddata)
print('\n' + '获取过去一天上证综合指数的60分钟线')
wsddata = w.wsi(stock, "open,high,low,close,volume,amt", one_day_ago, now, "BarSize=60")
printinfo(wsddata)
print('\n' + '获取过去一天上证综合指数的30分钟线')
wsddata = w.wsi(stock, "open,high,low,close,volume,amt", one_day_ago, now, "BarSize=30")
printinfo(wsddata)
print('\n' + '获取过去一天上证综合指数的15分钟线')
wsddata = w.wsi(stock, "open,high,low,close,volume,amt", one_day_ago, now, "BarSize=15")
printinfo(wsddata)
print('\n' + '获取过去一天上证综合指数的5分钟线')
wsddata = w.wsi(stock, "open,high,low,close,volume,amt", one_day_ago, now, "BarSize=5")
printinfo(wsddata)
