# step2: 读入持仓文件
tmp = pd.read_excel(u'M:\\分级基金\\创业板50\\综合信息查询_基金证券.xls')
tmp = tmp[:-1]
codes = tmp[u'证券代码']
rawTicker = ['%06.0f' % code for code in codes]
tmp['tickers'] = pd.Series([i + '.SH' if i[0] in {'5','6'} else i + '.SZ' for i in rawTicker])
tmp['position'] = tmp[u'持仓']
tmp['mv'] = tmp[u'市值']
holdings = tmp[['tickers','position', 'mv']]
equity = tmp[u'市值'].sum()
position = tmp[u'市值比净值(%)'].sum()
# step3: 检查是否停牌
status = wu.wss(list(holdings['tickers']), 'trade_status', today)
tmp = pd.merge(holdings, status, left_on = 'tickers', right_index = True)
data = pd.merge(tmp, index)
# 计算
asset = equity / position * 100
asset = asset + asset_dif
equity_dif = asset * target_pos - equity #今日买入
# 可交易列表
float = data[data['TRADE_STATUS'] == u'交易']
float_value = float['mv'].sum()
float_target_value = float_value + equity_dif
# step2: 读入持仓文件
tmp = pd.read_excel(u'M:\\分级基金\\证券\\综合信息查询_基金证券.xls')
tmp = tmp[:-1]
codes = tmp[u'证券代码']
rawTicker = ['%06.0f' % code for code in codes]
tmp['tickers'] = pd.Series([i + '.SH' if i[0] in {'5','6'} else i + '.SZ' for i in rawTicker])
tmp['position'] = tmp[u'持仓']
tmp['mv'] = tmp[u'市值']
holdings = tmp[['tickers','position', 'mv']]
equity = tmp[u'市值'].sum()
position = tmp[u'市值比净值(%)'].sum()
# step3: 检查是否停牌
status = wu.wss(list(holdings['tickers']), 'trade_status', enddate)
tmp = pd.merge(holdings, status, left_on = 'tickers', right_index = True)
data = pd.merge(tmp, index)
# 计算
asset = equity / position * 100
asset = asset + asset_dif
equity_dif = asset * target_pos - equity #今日买入
# 可交易列表
float = data[data['TRADE_STATUS'] == u'交易']
float_value = float['mv'].sum()
float_target_value = float_value + equity_dif
fromDate = raw_input('input fromDate date:')
toDate = raw_input('input toDate date:')
dateStr = raw_input('current Month in YYMM format:')
dateStr = datetime.strptime(dateStr, '%y%m')
interval = [0, 1, 3, 6]
monthList = [dateStr + i * MonthBegin() for i in interval]
strList = [datetime.strftime(i, '%y%m') for i in monthList]
icList = ['IC' + i + '.CFE' for i in strList]
ifList = ['IC' + i + '.CFE' for i in strList]
ihList = ['IC' + i + '.CFE' for i in strList]
indexList = ['000905.SH', '000300.SH', '000016.SH']
indexClose1 = wu.wss(indexList, 'close', fromDate)
indexClose2 = wu.wss(indexList, 'close', toDate)
icClose1 = wu.wss(icList, 'close', fromDate)
icClose2 = wu.wss(icList, 'close', toDate)
ic1 = indexClose1.CLOSE['000905.SH']
ic2 = indexClose2.CLOSE['000905.SH']
icStructure1 = (icClose1.CLOSE - ic1) / ic1
icStructure2 = (icClose2.CLOSE - ic2) / ic2
icStructure = pd.DataFrame([icStructure1, icStructure2],
index=[fromDate, toDate])
tmp = icStructure.transpose()
# step2: 读入持仓文件
tmp = pd.read_excel(u'M:\\分级基金\\50ETF\\综合信息查询_基金证券.xls')
tmp = tmp[:-1]
codes = tmp[u'证券代码']
rawTicker = ['%06.0f' % code for code in codes]
tmp['tickers'] = pd.Series(
[i + '.SH' if i[0] in {'5', '6'} else i + '.SZ' for i in rawTicker])
tmp['position'] = tmp[u'持仓']
tmp['mv'] = tmp[u'市值']
holdings = tmp[['tickers', 'position', 'mv']]
equity = tmp[u'市值'].sum()
position = tmp[u'市值比净值(%)'].sum()
# step3: 检查是否停牌
status = wu.wss(list(holdings['tickers']), 'trade_status', today)
tmp = pd.merge(holdings, status, left_on='tickers', right_index=True)
data = pd.merge(tmp, index)
# 计算
asset = equity / position * 100
asset = asset + asset_dif
equity_dif = asset * target_pos - equity #今日买入
# 可交易列表
float = data[data['TRADE_STATUS'] == u'交易']
float_value = float['mv'].sum()
float_target_value = float_value + equity_dif
float['targetmv'] = float_target_value * float['weight'] / float['weight'].sum(
fromDate = raw_input('input fromDate date:')
toDate = raw_input('input toDate date:')
dateStr = raw_input('current Month in YYMM format:')
dateStr = datetime.strptime(dateStr,'%y%m')
interval = [0, 1, 3, 6]
monthList = [dateStr + i * MonthBegin() for i in interval]
strList = [datetime.strftime(i,'%y%m') for i in monthList]
icList = ['IC'+i+'.CFE' for i in strList]
ifList = ['IC'+i+'.CFE' for i in strList]
ihList = ['IC'+i+'.CFE' for i in strList]
indexList = ['000905.SH', '000300.SH', '000016.SH']
indexClose1 = wu.wss(indexList, 'close', fromDate)
indexClose2 = wu.wss(indexList, 'close', toDate)
icClose1 = wu.wss(icList, 'close', fromDate)
icClose2 = wu.wss(icList, 'close', toDate)
ic1 = indexClose1.CLOSE['000905.SH']
ic2 = indexClose2.CLOSE['000905.SH']
icStructure1 = (icClose1.CLOSE - ic1) / ic1
icStructure2 = (icClose2.CLOSE - ic2) / ic2
icStructure = pd.DataFrame([icStructure1,icStructure2],index = [fromDate, toDate])
tmp = icStructure.transpose()
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'
if __name__ == '__main__':
import utility.windutility as wu
# data = wu.wsi('150018.SZ, 150019.SZ', 'close',"2016-01-25 09:00:00", "2016-01-25 10:37:00" )
wu.wss("000006.SZ,000002.SZ", "trade_status,high",'2016-1-16')
print(data)
