def wss(tickers, fields, date):
if isinstance(tickers,str):
tickers = tickers.replace(',','').split()
if isinstance(fields,str):
fields = fields.replace(',','').split()
tmp = w.wss(tickers, fields, 'tradedate = %s'%(date))
return pd.DataFrame(dict(zip(tmp.Fields, tmp.Data)),index = tmp.Codes, columns= tmp.Fields)
def value(self, asOfDate):
result = w.wss(
self.instCode, ["dirty_cnbd"], "tradeDate={0}".format(format(asOfDate, "%Y%m%d")), "credibility=1"
)
if result.ErrorCode == 0:
v = result.Data[0][0]
return v
else:
return None
def getInstData(self):
fields = self.windFieldRequests()
if fields:
windFields = [f[0] for f in fields]
result = w.wss(unicode(self.code), windFields, 'tradeDate={0}'.format(format(datetime.datetime.today(), '%Y%m%d')), 'industryType=1')
if result:
if result.ErrorCode == 0:
for i in range(len(fields)):
if fields[i][2]:
self.__setattr__(fields[i][1], fields[i][2](result.Data[i][0]))
else:
self.__setattr__(fields[i][1], result.Data[i][0])
self.initOK = True
else:
self.initOK = True
def getFundInfo(self, productList=[]):
if not productList:
self.PrintInfoDemo.PrintLog('未传入指数参数，请检查！')
return
try:
fundInfoDf = pd.read_excel(
r"C:\\Users\\lenovo\\PycharmProjects\\fundPortfolio\\GetHistoryData\\fundInfoDf.xlsx"
)
self.PrintInfoDemo.PrintLog(infostr='本地读取基金历史信息数据 fundInfoDf')
return fundInfoDf
except:
w.start()
self.PrintInfoDemo.PrintLog(infostr='wind读取基金历史信息数据 fundInfoDf')
codeList = [code + '.OF' for code in productList]
filedList = [
'fund_setupdate', 'fund_fundscale', 'fund_scaleranking',
'fund_mgrcomp', 'fund_type', 'fund_fundmanager',
'fund_structuredfundornot', 'fund_firstinvesttype',
'fund_investtype', 'fund_risklevel', 'fund_similarfundno',
'fund_manager_geometricavgannualyieldoverbench', 'risk_sharpe',
'fund_managementfeeratio', 'fund_fullname',
'fund_custodianfeeratio', 'NAV_periodicannualizedreturn',
'fund_manager_managerworkingyears', 'fund_benchmark',
'fund_benchindexcode', 'fund_initial'
]
options = "fundType=3;order=1;returnType=1;startDate=20180813;endDate=20180913;period=2;riskFreeRate=1"
fundInfo = w.wss(codes=codeList, fields=filedList, options=options)
if fundInfo.ErrorCode != 0:
self.PrintInfoDemo.PrintLog(infostr='wind读取基金历史信息数据失败，错误代码：',
otherInfo=fundInfo.ErrorCode)
return pd.DataFrame()
fundInfoDf = pd.DataFrame(fundInfo.Data,
index=fundInfo.Fields,
columns=codeList).T
writer = pd.ExcelWriter(
r"C:\\Users\\lenovo\\PycharmProjects\\fundPortfolio\\GetHistoryData\\fundInfoDf.xlsx"
)
fundInfoDf.to_excel(writer)
writer.save()
self.PrintInfoDemo.PrintLog(
infostr='wind读取基金历史信息数据成功，写入本地文件fundInfoDf.xlsx')
return fundInfoDf
def getInstData(self):
fields = self.windFieldRequests()
if fields:
windFields = [f[0] for f in fields]
result = w.wss(
unicode(self.code), windFields, 'tradeDate={0}'.format(
format(datetime.datetime.today(), '%Y%m%d')),
'industryType=1')
if result:
if result.ErrorCode == 0:
for i in range(len(fields)):
if fields[i][2]:
self.__setattr__(fields[i][1],
fields[i][2](result.Data[i][0]))
else:
self.__setattr__(fields[i][1], result.Data[i][0])
self.initOK = True
else:
self.initOK = True
def getMonthData(self,
codeList=[],
startDate='2019-03-01',
endDate='2019-05-30'):
totalTradeList = [startDate, endDate]
sqlstr = "select * from stock_month_value where stock_code in %s and update_time in %s" % (
tuple(codeList), tuple(totalTradeList))
tempDf = pd.read_sql(sql=sqlstr, con=self.engine)
lackCode = self.checkLackMonthData(tempDf, codeList)
if lackCode:
self.logger.debug("getMonthData从wind获取,缺失code: %s" %
','.join(lackCode))
dfList = []
for tradeDate in [startDate, endDate]:
tradeDateStr = tradeDate[:4] + tradeDate[5:7] + tradeDate[8:]
wssData = w.wss(codes=lackCode,
fields=['close', 'sec_name'],
options="tradeDate=%s;priceAdj=F;cycle=M" %
tradeDateStr)
if wssData.ErrorCode != 0:
self.logger.error("获取股票截面行情价格有误，错误代码" +
str(wssData.ErrorCode))
return pd.DataFrame()
df = pd.DataFrame(wssData.Data,
columns=wssData.Codes,
index=wssData.Fields).T
df.rename(columns={
"CLOSE": "close_price",
"SEC_NAME": "stock_name"
},
inplace=True)
df['update_time'] = [tradeDate] * len(df)
df['stock_code'] = df.index.tolist()
dfList.append(df)
tempLackDf = pd.concat(dfList, axis=0, sort=True)
self.GetDataToMysqlDemo.GetMain(tempLackDf, 'stock_month_value')
tempDf = pd.concat([tempDf, tempLackDf], axis=0, sort=True)
tempDf = tempDf.drop_duplicates(
subset=['stock_code', 'update_time'])
else:
self.logger.debug("getMonthData从本地数据库获取！")
return tempDf[['stock_code', 'close_price', 'update_time']]
def get_secs_liqshare(sec_ids=[], date=""):
"""
获取流通股本
@parameters:
sec_ids (list of str): 证券代码列表
date (str): 查询日期
return (dict of dict): 键是证券代码，值是总流动股本
"""
fields = ["windcode", "float_a_shares"]
options = {"tradDate": date, "unit": "1"}
response = WDServer.wss(codes=",".join(sec_ids),
fields=",".join(fields),
options=options2str(options))
test_error(response)
output = dict(zip(*response.Data))
return output
def get(codes, fields, options, name, note1=None, note2=None, flag=True):
global date
print('{} {}'.format(name, options))
d = w.wss(codes, fields, options)
if d.ErrorCode != 0:
print(d)
os._exit(-1)
for code, v in zip(d.Codes, d.Data[0]):
# 对于特殊返回类型的特殊处理
if isinstance(v, datetime.datetime):
v = v.date().strftime('%Y%m%d')
if note2:
client['STOCK'][code].insert_one({'DATE': str(date), 'NAME': str(name), 'VALUE': str(v), 'NOTE1': note1, 'NOTE2': note2})
elif note1:
client['STOCK'][code].insert_one({'DATE': str(date), 'NAME': str(name), 'VALUE': str(v), 'NOTE1': note1})
elif flag:
client['STOCK'][code].insert_one({'DATE': str(date), 'NAME': str(name), 'VALUE': str(v)})
else:
client['STOCK'][code].insert_one({'NAME': str(name), 'VALUE': str(v)})
def loadData_daily(begin_date=constant.BEGIN_DATE,
stockname='600519.SH',
end_date=constant.END_DATE):
if not w.isconnected():
w.start()
res = w.wsd(stockname,
"high, low, close, trade_status",
begin_date,
end_date,
'priceadj=F',
showblank=0)
is_index = w.wss(stockname, 'windtype').Data[0][0] == "股票指数"
K_list = []
if res.ErrorCode != 0:
#print(stockname + " load daily K info Error: wsd - " +
# str(res.ErrorCode))
# 这里抛出定义的异常，能够在调动的上层捕捉，以防程序异常停止
raise loaddataError(stockname + 'load data from Wind error: ' +
res.ErrorCode)
# TODO:优化对非停牌日导致的价格数据缺失的前向填充方法，借用pd.DataFrame的方法
valid_idx = 0
for jj in range(len(res.Data[0])):
if not is_index and res.Data[3][jj] == "停牌一天":
continue
if jj >= 1:
res.Data[0][jj] = (res.Data[0][jj] or res.Data[0][jj - 1])
res.Data[1][jj] = (res.Data[1][jj] or res.Data[1][jj - 1])
res.Data[2][jj] = (res.Data[2][jj] or res.Data[2][jj - 1])
if not res.Data[0][jj] or not \
res.Data[1][jj] or not res.Data[2][jj]:
continue
temp_time = res.Times[jj].strftime("%Y-%m-%d")
# DEBUG: Kti标记需要剔除掉停牌期
k = K(time=temp_time,
high=round(res.Data[0][jj], 2),
low=round(res.Data[1][jj], 2),
close=round(res.Data[2][jj], 2),
i=Kti(8, valid_idx, 7, 5),
lev=1)
K_list.append(k)
valid_idx += 1
return K_list
def bond_gain(bond, duizhangdan, code):
sum_gain = 0
sum_pay = 0
hold_num = 0
jiaquanchengben = 0
buy_times = 0
sum_payback = 0
for index, row in duizhangdan.iterrows():
if bond == row["Unnamed: 3"]:
tradedate = "tradeDate=" + str(row["对账单合集"])
if row["Unnamed: 1"] == "证券买入":
buy_times += 1
inf = (w.wss(code, "couponrate3,ptmyear,termifexercise",
tradedate)).Data
# print(inf)
lilv = float(''.join([str(x) for x in inf[0]])) / 100
rest_date = float(''.join([str(x) for x in inf[1]]))
xingquan = float(''.join([str(x) for x in inf[2]]))
#判断行权期限是不是空
if xingquan != xingquan:
times = math.ceil(rest_date)
else:
times = math.ceil(xingquan)
payback = 100 * (1 + lilv * times)
absgain_per = payback / float(
abs(float(row["Unnamed: 6"])) / int(row["Unnamed: 4"])) - 1
absgain = absgain_per * abs(float(row["Unnamed: 6"]))
sum_gain += absgain
sum_pay += abs(float(row["Unnamed: 6"]))
hold_num += int(row["Unnamed: 4"])
sum_payback += payback
if jiaquanchengben == 0:
jiaquanchengben = float(sum_pay / hold_num)
else:
jiaquanchengben = (
abs(float(row["Unnamed: 6"])) + jiaquanchengben *
(hold_num - int(row["Unnamed: 4"]))) / hold_num
elif row["Unnamed: 1"] == "证券卖出":
sum_gain -= ((sum_payback / buy_times - jiaquanchengben) *
int(row["Unnamed: 4"]))
hold_num -= int(row["Unnamed: 4"])
return sum_gain
def load_audit_category_date(self, date):
""" 下载 最近年报审计意见 """
year_date = Date().get_last_stock_year_report_date(date)
file = os.path.join(self.data_path_static, "stmnote_audit_category.csv")
code_list = self.get_all_stock_code_now()
code_list_str = ','.join(code_list)
new_data = w.wss(code_list_str, "stmnote_audit_category", "rptDate=%s;zoneType=1" % year_date)
new_data = pd.DataFrame(new_data.Data, index=new_data.Fields, columns=new_data.Codes).T
new_data.columns = [year_date]
if os.path.exists(file):
old_data = pd.read_csv(file, encoding='gbk', index_col=[0])
data = pd.concat([old_data, new_data], axis=1)
data = data.T.sort_index().T
else:
data = new_data
data.to_csv(file)
def get_ind(stocks, date):
date_i = date.strftime("%Y%m%d")
rawdata = w.wss(stocks, "indexcode_citic",
"tradeDate=" + date_i + ";industryType=1")
data = pd.DataFrame(rawdata.Data).T
ind_ = []
for x in data[0].tolist():
if x is not None:
ind_.append(int(x[6:-3]))
else:
ind_.append(np.nan)
data[0] = ind_
data.index = stocks
data.columns = ['ind']
return data
def getHigh5(self, objectList):
retDict = {}
endDay = (datetime.datetime.now() - datetime.timedelta(1)).strftime("%Y%m%d")
startDay = 'ED-4TD'
if objectList:
req = ''
for object in objectList:
req += object
req += ','
# data = self.w.wsd(req, 'high', startDay, endDay, 'PriceAdj=F')
para = 'startDate=' + startDay + ';endDate=' + endDay + ';priceAdj=F'
data = w.wss(req, 'high_per', para)
if data.ErrorCode == 0:
for i in range(0, len(data.Codes)):
maxd = data.Data[0][i]
if maxd == maxd:
retDict[data.Codes[i]] = maxd
else:
retDict[data.Codes[i]] = 0
return retDict
def get_fund_top10_stock(self):
""" 前十大重仓股 """
fund_top10_stock = self.fund_top10_stock
print(fund_top10_stock)
fund_top10_stock.columns = ['占基金净值比']
fund_top10_stock['股票名称'] = fund_top10_stock.index.map(
Stock().get_stock_name_date)
data = w.wss(self.fund_code, "prt_stocktonav",
"rptDate=%s" % self.quarter_date)
stock_sum = data.Data[0][0] / 100.0
fund_top10_stock['占股票市值比'] = fund_top10_stock['占基金净值比'] / stock_sum
fund_top10_stock['重仓股票(%s)' % self.quarter_date] = range(
1,
len(fund_top10_stock) + 1)
fund_top10_stock = fund_top10_stock[[
'重仓股票(%s)' % self.quarter_date, '股票名称', '占股票市值比', '占基金净值比'
]]
return fund_top10_stock
def download(symbol, start_date="2005-01-01", end_date="2016-12-31"):
current_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
dic = {}
dates = []
while current_date <= end_date:
print current_date.strftime("%Y%m%d")
raw_data = w.wss(symbol, factors,
"tradeDate=%s" % (current_date.strftime("%Y%m%d")))
for data, field in zip(raw_data.Data, raw_data.Fields):
if not dic.has_key(str(field.lower())):
dic[str(field.lower())] = data
else:
dic[str(field.lower())].append(data[0])
dates.append(current_date)
current_date = current_date + datetime.timedelta(1)
df = pd.DataFrame(dic)
df["date"] = dates
df.to_csv("../data/%s.csv" % (symbol), index=False)
def get_secs_name(sec_ids=[]):
"""
获取日线数据
@parameters:
sec_ids (list of str): 证券代码列表
date (str): 查询日期
level (int): 行业层级，1、2、3分别对应申万一级、二级、三级分类
return (dict of str): 键是证券代码，值是行业名称
"""
if not sec_ids:
return {}
fields = ["windcode", "sec_name"]
response = WDServer.wss(codes=",".join(sec_ids), fields=",".join(fields))
test_error(response)
output = dict(zip(*response.Data))
return output
def industry_info(timestr):
w.start()
w.isconnected()
#timestr=input('tradedate')
datac = []
for i in range(49, 58):
windds1 = w.wset(
"sectorconstituent", "date=" + timestr + ";sectorid=b10" + chr(i) +
"000000000000;field=wind_code")
datac = datac + windds1.Data[0]
for i in range(97, 117):
windds1 = w.wset(
"sectorconstituent", "date=" + timestr + ";sectorid=b10" + chr(i) +
"000000000000;field=wind_code")
datac = datac + windds1.Data[0]
windds1 = w.wss(datac, "industry_citiccode",
"tradeDate=" + timestr + ";industryType=3")
dc = {'Ticker': windds1.Codes, 'industry_citiccode': windds1.Data[0]}
df = pd.DataFrame(dc)
return (df)
def load_ipo_data(self, beg_date):
""" 下载IPO数据 上市日期 发行价 中签率 申购上限 等等"""
data = self.get_new_stock_list(beg_date)
code_str = ','.join(data.index.values)
data = w.wss(code_str,
"sec_name,ipo_date,ipo_price,ipo_cashratio,ipo_lotteryrate_abc,ipo_otc_cash_pct,ipo_op_uplimit",
"instituteType=1")
data_pd = pd.DataFrame(data.Data, index=data.Fields, columns=data.Codes).T
data_pd["IPO_DATE"] = data_pd["IPO_DATE"].map(lambda x: x.strftime('%Y-%m-%d'))
data_pd.columns = ['股票名称', '上市日期', '发行价格', '网上中签率(%)',
'网下A类中签率(%)', '网下总计中签率(%)', '申购上限数量(万股)']
data_pd['申购上限金额(万元)'] = data_pd["申购上限数量(万股)"] * data_pd['发行价格']
data_pd = data_pd.dropna()
data_pd = data_pd.sort_values(by=['上市日期'], ascending=True)
for i_code in range(0, len(data_pd)):
code = data_pd.index.values[i_code]
ipo_date = data_pd.ix[i_code, '上市日期']
open_date, open_pct, open_price = self.get_open_date_pct(code, ipo_date)
data_pd.ix[i_code, '开板日期'] = open_date
data_pd.ix[i_code, '开板价格'] = open_price
data_pd.ix[i_code, '开板收益'] = open_pct
print(data_pd)
file = os.path.join(self.data_path, 'ipo_data.xlsx')
data = pd.read_excel(file, index_col=[1])
data = data.T.dropna(how='all').T
concat_data = FactorOperate().pandas_add_row(data, data_pd)
concat_data = concat_data.sort_values(by=['上市日期'], ascending=True)
excel = WriteExcel(file)
worksheet = excel.add_worksheet("新股检测")
excel.write_pandas(concat_data, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=None, color="orange", fillna=True)
excel.close()
def getPetChg(self, codeList, startDate, endDate):
'''
获取股票区间涨跌幅数据
:return:
'''
sqlStr = "select stock_code,pct_chg_value from stock_range_updown_value where stock_code in %s and start_date='%s' and end_date='%s'" % (
tuple(codeList), startDate, endDate)
resultDf = pd.read_sql(sql=sqlStr, con=self.engine)
if not resultDf.empty:
lackCode = [
code for code in codeList
if code not in resultDf['stock_code'].tolist()
]
else:
lackCode = codeList[:]
if lackCode:
self.logger.debug("getPetChg从wind获取！")
startDateParam = startDate[:4] + startDate[5:7] + startDate[8:]
endDateParam = endDate[:4] + endDate[5:7] + endDate[8:]
wssData = w.wss(codes=lackCode,
fields=["pct_chg_per"],
options="startDate=%s;endDate=%s" %
(startDateParam, endDateParam))
if wssData.ErrorCode != 0:
self.logger.error("getPetChg获取pct_chg_per有误，错误代码%s" %
(str(wssData.ErrorCode)))
return pd.DataFrame()
df = pd.DataFrame(wssData.Data,
index=["pct_chg_value"],
columns=wssData.Codes).T
df['stock_code'] = df.index.tolist()
df['start_date'] = startDate
df['end_date'] = endDate
self.GetDataToMysqlDemo.GetMain(df, 'stock_range_updown_value')
resultDf = pd.concat([resultDf, df], axis=0,
sort=True)[['stock_code', 'pct_chg_value']]
else:
self.logger.debug("getPetChg从本地数据库获取！")
resultDf.set_index('stock_code', inplace=True, drop=True)
return resultDf
def wsi(cls, code, fields, trade_date, num_retries=2):
"""单代码多维"""
try:
if type(trade_date) in (datetime, pd._libs.tslib.Timestamp):
trade_date = trade_date.strftime("%Y%m%d")
if type(fields) is list:
fields = ",".join(fields)
if type(code) is list:
code = ",".join(code)
w.start()
result = w.wss(code, fields, "tradeDate=" + trade_date +
";credibility=1").Data[0]
if result[0] == u'CWSSService: invalid indicators.' and len(
result) == 0:
raise Exception("CWSSService: invalid indicators.")
result = [0.0 if isnan(x) else x for x in result]
return tuple(result) if len(result) > 1 else result[0]
except Exception as e:
if num_retries > 0:
num_retries -= 1
cls.wsi(code, fields, trade_date, num_retries=num_retries)
def update_bond():
everything = pd.read_csv("bonds.xlsx", encoding='gbk')
date = "date=" + str(int(time.strftime("%Y-%m-%d", time.localtime()))) + ";sectorid=1000008620000000;field=wind_code,sec_name"
inf = w.wset("sectorconstituent",date)
if inf.ErrorCode==0:
print("errrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrror")
return
new_bonds = inf.Data
for bond in new_bonds:
daoqiri = (w.wss("136670.SH", "maturitydate").Data)[0]
for index,ch in enumerate(daoqiri):
if ch == '/':
daoqiri[index]='-'
everything = everything.append(
pd.DataFrame({'证券代码': [bond[0][0]], '证券简称': [bond[1][0]], '到期日期':[daoqiri]}),
ignore_index=True)
#去重
everything.drop_duplicates(subset='证券代码',keep='last',inplace=True)
#按到期日近到远排序
everything.sort_values('到期日期',inplace=True)
everything.to_excel("bonds.xlsx", index=False, header=True)
def get_weights(self):
code_list = list_jq2wind(self.code_list)
SW1_code_list = [t[0] for t in SW1_INDEX]
weight_value = self._calc_weights(SW1_code_list) # 提取行业权重
industry_list = w.wss(code_list, "indexcode_sw", "tradeDate=" +
self.date + ";industryType=1").Data[0]
weight_value_temp = []
for i in range(len(code_list)):
industry_temp = industry_list[i]
if industry_temp is None: # 个股无行业分类数据的处理
weight_value_temp.append(0.0)
else:
industry_index = SW1_code_list.index(industry_temp)
weight_value_temp.append(weight_value[industry_index])
weight_value_temp = np.array(weight_value_temp)
weight_value_temp = weight_value_temp / np.sum(
weight_value_temp) # 权重归一化
code_weights = dict(
[[list_wind2jq([code_list[i]])[0], weight_value_temp[i]]
for i in range(len(code_list))])
return code_weights
def download_fundamental_data():
codes = utils.get_index_component('881001.WI')
years = range(2008, 2018)
months = [3, 6, 9, 12]
dates = [
'%s-%s-%s' % (y, m, calendar.monthrange(y, m)[1]) for y in years
for m in months
]
fields = 'roic,roe,roa'
for code in codes:
fname = '%s/%s.xlsx' % (const.FUNDAMENTAL_DIR, code)
if os.path.exists(fname):
continue
print('downloading %s...' % (code))
df = pd.DataFrame(columns=fields.split(','),
index=pd.to_datetime(dates, format='%Y-%m-%d'))
for date in df.index:
wdata = w.wss(code, fields,
'rptDate=%s' % (date.strftime('%Y-%m-%d')))
df.loc[date, :] = [x[0] for x in wdata.Data]
df.to_excel(fname)
def get_data(self):
w.start()
df1 = pd.read_excel("行业指数ETF概况.xlsx", sheet_name='Sheet1', index_col=0)
df2 = pd.read_excel("策略指数ETF概况.xlsx", sheet_name='Sheet1', index_col=0)
df3 = pd.read_excel("主题指数ETF概况.xlsx", sheet_name='Sheet1', index_col=0)
df4 = pd.read_excel("规模指数ETF概况.xlsx", sheet_name='Sheet1', index_col=0)
df5 = pd.read_excel("风格指数ETF概况.xlsx", sheet_name='Sheet1', index_col=0)
# index_code_list = df1.index.tolist()+df2.index.tolist()+df3.index.tolist()+df4.index.tolist()+df5.index.tolist()
name_list = [
'行业指数ETF概况', '策略指数ETF概况', '主题指数ETF概况', '规模指数ETF概况', '风格指数ETF概况'
]
df_list = [df1, df2, df3, df4, df5]
for name in name_list:
df = df_list[name_list.index(name)]
aa = w.wss(df.index.tolist(), "sec_name")
tempdf1 = pd.DataFrame(aa.Data, columns=aa.Codes,
index=aa.Fields).T
result = pd.concat([df1, tempdf1], axis=1, sort=True)
result.to_excel("%s.xlsx" % name)
break
def wind_future_daily(dt, contracts):
datestr = dt.strftime("%Y-%m-%d")
try:
res = w.wss(
contracts,
"pre_close,open,high,low,close,volume,amt,oi,pre_settle,settle,windcode",
"tradeDate=" + datestr + ";priceAdj=U;cycle=D")
d = res.Data
f = res.Fields
df = pd.DataFrame(
data=np.transpose(d),
columns=f,
)
df1 = df.dropna(subset=['CLOSE'])
df1['id_instrument'] = df1['WINDCODE'].apply(
lambda x: (x[-len(x):-8] + '_' + x[-8:-4]).lower())
df1['name_code'] = df1['WINDCODE'].apply(
lambda x: x[-len(x):-8].lower())
df1['cd_exchange'] = df1['WINDCODE'].apply(lambda x: x[-3:].lower())
df1.loc[:, 'datasource'] = 'wind'
df1.loc[:, 'timestamp'] = datetime.datetime.today()
df1.loc[:, 'dt_date'] = dt
df1 = df1.rename(
columns={
'PRE_CLOSE': 'amt_last_close',
'OPEN': 'amt_open',
'HIGH': 'amt_high',
'LOW': 'amt_low',
'CLOSE': 'amt_close',
'VOLUME': 'amt_trading_volume',
'AMT': 'amt_trading_value',
'OI': 'amt_holding_volume',
'PRE_SETTLE': 'amt_last_settlement',
'SETTLE': 'amt_settlement',
'WINDCODE': 'code_instrument'
})
return df1
except Exception as e:
print(e)
return pd.DataFrame()
def download_ipo_last_trade_trading(w, wind_codes):
# 黄金从20130625开始将最小变动价位从0.01调整成了0.05，但从万得上查出来还是完全一样，所以没有必要记录mfprice
# 郑商所在修改合约交易单位时都改了合约代码，所以没有必要记录contractmultiplier
w.asDateTime = asDateTime
w_wss_data = w.wss(wind_codes, "sec_name,ipo_date,lasttrade_date,lasttradingdate", "")
grid = w_wss_data.Data
# T1803一类的会被当成时间，需要提前转置
new_grid = [[row[i] for row in grid] for i in range(len(grid[0]))]
df = pd.DataFrame(new_grid)
df.columns = ['sec_name', 'ipo_date', 'lasttrade_date', 'lasttradingdate']
df.index = w_wss_data.Codes
df.index.name = 'wind_code'
df['ipo_date'] = df['ipo_date'].apply(datetime_2_yyyyMMdd)
df['lasttrade_date'] = df['lasttrade_date'].apply(datetime_2_yyyyMMdd)
df['lasttradingdate'] = df['lasttradingdate'].apply(datetime_2_yyyyMMdd)
df.replace(18991230, 0, inplace=True)
return df
def get_wss(universe, factors, if_convert=False, names=None, **options):
'''
获取万德多维数据。
Parameters
----------
universe
list ['600340','000001']
factors
'pe_ttm,pb_mrq'
if_convert
是否将universe转换成wind代码,默认为False,仅支持沪深股票
names
list of str,列别名,默认为None
options
其他参数,如tradeDate = '20171009'
Returns
--------
DataFrame
'''
options = dict_2_str(options)
if names is not None:
assert len(names) == len(factors.split(','))
if if_convert:
universe_wind = code_2_wind_symbol(universe)
universe_wind = ','.join(universe_wind)
else:
universe_wind = ','.join(universe)
data = w.wss(universe_wind, factors, options)
if names is not None:
df = pd.DataFrame(data.Data, index=names, columns=universe).T
else:
df = pd.DataFrame(data.Data, index=data.Fields, columns=universe).T
return df
def getTypeData(code):
q = QtSql.QSqlQuery("""SELECT SEC_NAME, SEC_TYPE, EXCHANGE FROM SECINFO WHERE SEC_CODE='%s'""" % code)
while q.next():
name = q.value(0).toString()
insttype = q.value(1).toString()
exchange = q.value(2).toString()
return name, insttype, exchange
infolist = ['sec_name', 'sec_type', 'exch_city']
result = w.wss(unicode(code), infolist, 'tradeDate={0}'.format(format(datetime.datetime.today(), '%Y%m%d')))
if result:
if result.ErrorCode == 0:
name = result.Data[0][0]
insttype = result.Data[1][0]
exchange = result.Data[2][0]
q = QtSql.QSqlQuery()
try:
q.exec_("""INSERT INTO SECINFO VALUES ('%s','%s','%s','%s')""" % (code, name, insttype, exchange))
QtSql.QSqlDatabase().commit()
return name, insttype, exchange
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def _calculate_factor(self):
date_list_end = self.date
date_list_start = date_list_end.copy()
date_list_start[0] = str(int(date_list_start[0]) - 3)
date_list_end = '-'.join(date_list_end)
date_list_start = '-'.join(date_list_start)
data_temp = w.wset(
"sharepledge",
"startdate=" + date_list_start + ";enddate=" + date_list_end +
";sectorid=a001010100000000;field=wind_code,pledged_shares,pledge_end_date,pledge_termination_date"
).Data
# 将None数据用一个遥远的时间代替
data_temp[2] = self._replace_list(data_temp[2])
data_temp[3] = self._replace_list(data_temp[3])
df = pd.DataFrame(data=np.array(
[data_temp[1], data_temp[2], data_temp[3]]).transpose(),
index=data_temp[0])
df[0] = df[0] * 10000.0 # 把质押的份数换为股数
# 取出所有未到期的股权质押信息
df = df[
(df[1] > datetime.datetime.strptime(date_list_end, '%Y-%m-%d'))
& (df[2] > datetime.datetime.strptime(date_list_end, '%Y-%m-%d'))]
ds = df[0] # 全A个股处于股权质押状态的股票数量
ds = ds.sum(level=0)
all_shares = w.wss(self.code_list, "total_shares",
"unit=1;tradeDate=" +
''.join(self.date)).Data[0] # 获取总股本列表
for i in range(len(self.code_list)): # 计算个股的抵押比例
code_temp = self.code_list[i]
try:
pledge_shares = ds[code_temp]
except:
pledge_shares = 0
all_shares[i] = pledge_shares / all_shares[i]
df_ratio = pd.DataFrame(data=all_shares,
index=self.code_list,
columns=[self.factor_name]) # 对应个股的总股本
return df_ratio
def get_fund_basic_info(self):
""" wind 得到基金的基本信息 类型 成立日期 最新规模等等 """
data = w.wss(self.fund_code,
"fund_setupdate,fund_investtype,netasset_total",
"unit=1;tradeDate=%s" % self.last_trade_date)
data_pd = pd.DataFrame(data.Data,
index=data.Fields,
columns=data.Codes).T
data_pd.columns = ['成立日期', '基金类型', '基金规模（亿）']
data_pd['成立日期'] = data_pd['成立日期'].map(lambda x: x.strftime("%Y%m%d"))
data_pd['基金规模（亿）'] = data_pd['基金规模（亿）'].map(
lambda x: np.round(x / 100000000, 2))
data_pd = data_pd.T
data_info = pd.DataFrame([self.fund_code, self.fund_name],
columns=[self.fund_code],
index=['基金代码', '基金名称'])
data_concat = pd.concat([data_info, data_pd], axis=0)
data_concat.columns = ['内容']
data_concat['基本信息'] = data_concat.index
data_concat = data_concat[['基本信息', '内容']]
return data_concat
def find_start_date(self, db, symbol, table_name):
cursor = db.cursor()
sql1 = """SELECT trade_date from {:s}_DAILY
where id=(select max(id) from {:s}_DAILY)""".format(table_name, table_name)
row_num = cursor.execute(sql1)
if row_num == 0:
ipo_date = w.wss(symbol, "contract_issuedate").Data[0][0]
start_date = np.maximum(datetime.datetime(2013, 12, 31), ipo_date)
else:
last_date = Util.datetime2date(cursor.fetchone()[0])
sql2 = """select TRADE_DATE from trade_date where id=
(select id from trade_date where TRADE_DATE = '{:%Y-%m-%d}')""".format(last_date)
cursor.execute(sql2)
start_date = cursor.fetchone()[0]
return start_date
def set_input_mode(self):
"""
设置确定组合初始股票池或范围的模式类型.
* 追踪某个指数成分股-1.
* 在某个市场或多个板块概念成分组合下采用基本面选股-2.
* 选取用户指定的股票，通过设置文件地址读入-3.
"""
if self.target_index:
self.input_mode = 1
self.calendar = w.wss(self.target_index, "exch_eng").Data[0][0]
elif self.global_spec:
self.input_mode = 2
self.read_ics()
self.read_ics_fv()
self.read_ics_rank()
self.read_indices()
self.read_refresh_freq()
elif self.code_dir:
self.input_mode = 3
else:
self.input_mode = None
raise Exception("InputModeError: Can't decide stock pools.")
def get_secs_industry_sw(sec_ids=[], date="", level=1, market="A"):
"""
获取股票列表申万行业分类
@parameters:
sec_ids (list of str): 证券代码列表
date (str): 查询日期
level (int): 行业层级，1、2、3分别对应申万一级、二级、三级分类
market (str): 证券市场 A:表示A股市场 H:表示港股市场
return (dict of str): 键是证券代码，值是行业名称
"""
WindAPI.login(is_quiet=True)
date = date.replace("-", "")
if not sec_ids:
return {}
levelmap = {1: SWL1_CODE2NAME, 2: SWL2_CODE2NAME, 3: SWL3_CODE2NAME}
lookup = levelmap[level]
fields = SW_FIELDS_MAP[market]
options = {"tradDate": date.replace("-", ""), "industryType": level}
response = WDServer.wss(codes=",".join(sec_ids),
fields=",".join(fields),
options=options2str(options))
test_error(response)
output = {}
for i, sec in enumerate(response.Data[0]):
if response.Data[1][i] in lookup:
output[sec] = lookup[response.Data[1][i]]
elif response.Data[2][i]:
output[sec] = response.Data[2][i]
return output
def _set_ql_option(self):
ql_options = {}
date_end = datetime.datetime.strptime(
self.date_now, '%Y-%m-%d') # 记录期权的最后到期日，此类应该都是一个到期日
strike_list = []
# 将期权的payoff转化为ql中的对象
for option in self.options:
number = self.options[option]
option_data = w.wss(
option, "exe_mode,exe_price,exe_ratio,lasttradingdate",
"tradeDate=" + self.date_now).Data
option_type = option_data[0][0]
option_strike = option_data[1][0]
option_volume = option_data[2][0] * number
option_last_day = option_data[3][0]
option_type = ql.Option.Call if option_type == '认购' else ql.Option.Put
option_payoff = ql.PlainVanillaPayoff(option_type, option_strike)
if option_last_day > date_end:
date_end = option_last_day
ql_options[option] = (option_payoff, option_volume
) # 记录期权的payoff函数和对应的份数函数
strike_list.append(option_strike)
return ql_options, date_end.strftime('%Y-%m-%d'), np.array(strike_list)
# 通过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")
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')
amt real,
PRIMARY KEY (secid,tradedate)
)
""")
sql = "INSERT OR REPLACE INTO stockprice VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
# 通过wset来取数据集数据
print('\n\n'+'-----通过wset来取数据集数据,获取全部A股代码列表-----'+'\n')
wsetdata=w.wset('SectorConstituent','date=20160625;sectorId=a001010100000000;field=wind_code')
print(wsetdata)
for j in range(0,len(wsetdata.Data[0])):
# 通过wsd来提取时间序列数据，比如取开高低收成交量，成交额数据
print( u"\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()
__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()
