def Cal_AllIndex_HalfYear_Holding_Exposure(path, report_date):
"""
计算重点指数最近一个半年报 的持仓风格暴露
"""
# 参数举例
#####################################################################
# path = 'E:\\3_Data\\4_fund_data\\8_fund_index_exposure_weekly\\'
# report_date = '20171231'
# today = datetime.today().strftime("%Y%m%d")
# 基金池
#####################################################################
index_pool_file = os.path.join(path, "fund_pool", '重点指数代码.csv')
index_pool = pd.read_csv(index_pool_file, index_col=[0], encoding='gbk')
date = Date().get_trade_date_offset(report_date, 0)
beg_date = Date().get_trade_date_offset(report_date, -2)
end_date = Date().get_trade_date_offset(report_date, 2)
# 计算重点所有重点 基金最近一个半年报 的持仓风格暴露
#####################################################################
# 计算万德全A的基准 因为每早下载 这里就不重复下载
# Index().load_weight_china_index_date(date)
for i_index in range(len(index_pool)):
index_code = index_pool.index[i_index]
# 下载指数权重 因为每早下载 这里就不重复下载
# if index_code not in ["000300.SH", '000905.SH', '881001.WI']:
# Index().load_weight_from_wind_date(index_code=index_code, date=date)
Index().cal_index_exposure(index_code, beg_date, end_date)
if i_index == 0:
exposure = Index().get_index_exposure_date(index_code,
date,
type_list=["STYLE"])
exposure["CTY"] = 1.0
else:
exposure_add = Index().get_index_exposure_date(index_code,
date,
type_list=["STYLE"])
exposure_add["CTY"] = 1.0
exposure = pd.concat([exposure, exposure_add], axis=0)
#####################################################################
exposure.index = index_pool.index_name
exposure.loc["1/3*中小板综+1/3*创业板综+1/3*中证500", :] = exposure.loc[
["中小板综", "创业板综", "中证500"], :].mean()
cols = list(exposure.columns)
cols.insert(0, 'Type')
exposure['Type'] = '市场指数'
exposure = exposure[cols]
exposure_file = os.path.join(
path, "halfyear_holding_exposure",
'IndexHalfYearExposure_' + report_date + '.csv')
exposure.to_csv(exposure_file)
def cal_factor_exposure(self, beg_date, end_date, index_code):
""" 计算指标数值 """
short_term = 5
long_term = 90
data = Index().get_index_factor(index_code, attr=['CLOSE'])
print("Calculate Timing Factor %s At From %s To %s" %
(self.factor_name, beg_date, end_date))
data['AverageShort'] = data['CLOSE'].rolling(window=short_term).mean()
data['AverageLong'] = data['CLOSE'].rolling(window=long_term).mean()
data['Diff'] = data['AverageShort'] - data['AverageLong']
data = data.dropna()
data['DiffRatio'] = data['Diff'] / data['CLOSE']
data['RawTimer'] = data['DiffRatio']
data['Timer'] = data['RawTimer'].map(self.score_average_diff)
file = os.path.join(self.data_path, 'exposure',
'%s_%s.csv' % (self.factor_name, index_code))
data = data.dropna(how="all")
data.to_csv(file)
def holding_data_liuxin(today, project_path, out_path):
# 输入参数
##################################################################################
# project_path = 'E:\\4_代码\\pycharmprojects\\timer\\input_data\\'
# out_path = 'E:\\3_数据\\7_other_data\\0_mail_holding_all\\'
person_name = 'liuxin'
# today = datetime(2018, 6, 27).strftime("%Y%m%d")
before_trade_data = Date().get_trade_date_offset(today, -1)
today = Date().change_to_str(today)
# 基金列表
##################################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
# fund_list.append("泰达宏利品质生活")
# 基金持仓证券
##################################################################################
fund_asset = MfcData().get_fund_security(before_trade_data)
fund_asset = fund_asset[[
'持仓日期', '序号', '基金名称', '证券代码', '证券名称', '证券类别', '市值', '市值比净值(%)', '持仓',
'净买量', '净买金额', '费用合计', '当日涨跌幅(%)', '持仓多空标志', '估值价格', '最新价'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_asset_fund = fund_asset[fund_asset['基金名称'] == fund_name]
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '持仓.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 股票库
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = [
"改革动力股票库.xls", "改革动力禁止库.xls", "改革动力限制库.xls", "公司超五库.xls", "公司股票库.xls",
"公司关联库.xls", "公司禁止库.xls", "公司限制库.xls", "逆向股票库.xls", "逆向禁止库.xls",
"逆向限制库.xls", "同顺禁止库.xls", "量化限制库.xls"
]
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for i_file in range(len(pool_list)):
file = pool_list[i_file]
src_file = os.path.join(pool_path, 'raw_file', today, file)
out_file = os.path.join(out_sub_path, file)
try:
shutil.copyfile(src_file, out_file)
except:
pd.DataFrame([]).to_excel(out_file)
# 指数权重 Axioma
##################################################################################
index_code_list = [
"000300.SH", "000905.SH", "000016.SH", "881001.WI",
'China_Index_Benchmark'
]
out_sub_path = os.path.join(out_path, person_name, today, "index_weight")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for index_code in index_code_list:
data = Index().get_weight(index_code, before_trade_data)
out_file = os.path.join(out_sub_path, index_code + '.csv')
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data.to_csv(out_file, header=None)
def holding_data_for_manager(self, person_name='liuyang'):
"""
holding_data 中包含 基金资产、单个基金证券、股票池
index_weight 为指数权重
"""
# 输入参数
print(" Prepare Data For ", person_name)
before_trade_data = Date().get_trade_date_offset(self.today, -1)
# 基金列表
fund = MfcData().get_mfc_manage_info()
fund_val = fund[person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
# 基金资产
fund_asset = MfcData().get_fund_asset(before_trade_data)
fund_asset = fund_asset[[
'序号', '统计日期', '基金编号', '基金名称', '股票资产', '净值', '基金份额', '单位净值',
'单位净值涨跌幅(%)', '累计单位净值', '昨日单位净值', '当日股票收益率(%)', '当日股票净买入金额',
'股票资产/净值(%)', '当前现金余额', '累计应收金额', '累计应付金额', '期货保证金账户余额', '期货保证金',
'可用期货保证金', '保证金', 'T+0交易可用', 'T+1交易可用', 'T+0交易可用/净值(%)',
'T+1交易可用/净值(%)'
]]
fund_asset_fund = fund_asset[fund_asset['基金名称'].map(
lambda x: x in fund_list)]
out_sub_path = os.path.join(self.data_path, person_name, self.today,
"holding_data")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, '基金资产.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 基金持仓证券
fund_asset = MfcData().get_fund_security(before_trade_data)
fund_asset = fund_asset[[
'持仓日期', '序号', '基金名称', '证券代码', '证券名称', '证券类别', '市值', '市值比净值(%)',
'持仓', '净买量', '净买金额', '费用合计', '当日涨跌幅(%)', '持仓多空标志', '估值价格', '最新价'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_asset_fund = fund_asset[fund_asset['基金名称'] == fund_name]
out_file = os.path.join(out_sub_path, fund_name + '持仓.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 股票库
pool_path = MfcData().data_path
pool_path = os.path.join(pool_path, r"mfc_holding_raw\201806_now")
if person_name == 'liuyang':
pool_list = [
"改革动力股票库.xls", "改革动力禁止库.xls", "改革动力限制库.xls", "公司超五库.xls",
"公司股票库.xls", "公司关联库.xls", "公司禁止库.xls", "公司限制库.xls",
"逆向股票库.xls", "逆向禁止库.xls", "逆向限制库.xls", "同顺禁止库.xls",
"量化限制库.xls", '沪深300关联禁止库.xls', '沪深300投资库.xls', '沪深300限制库.xls',
'中证500关联禁止库.xls', '中证500投资库.xls', '中证500限制库.xls'
]
elif person_name == 'liuxin':
pool_list = [
"改革动力股票库.xls", "改革动力禁止库.xls", "改革动力限制库.xls", "公司超五库.xls",
"公司股票库.xls", "公司关联库.xls", "公司禁止库.xls", "公司限制库.xls",
"逆向股票库.xls", "逆向禁止库.xls", "逆向限制库.xls", "同顺禁止库.xls",
"量化限制库.xls", '红利禁止库.xls'
]
elif person_name == 'caolongjie':
pool_list = [
"公司超五库.xls", "公司股票库.xls", "公司关联库.xls", "公司禁止库.xls",
"公司限制库.xls", "专户禁止库.xls", "量化11号禁止库.xls", "人寿固收限制库.xls",
"人寿固收禁止库(委托人发送).xls", "量化限制库.xls"
]
for i_file in range(len(pool_list)):
file = pool_list[i_file]
src_file = os.path.join(pool_path, 'raw_file', self.today, file)
out_file = os.path.join(out_sub_path, file)
try:
shutil.copyfile(src_file, out_file)
except Exception as e:
print(e)
print(src_file)
print(out_file)
pd.DataFrame([]).to_excel(out_file)
# 指数权重 Axioma
out_sub_path = os.path.join(self.data_path, person_name, self.today,
"index_weight")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for index_code in self.index_code_list:
data = Index().get_weight_date(index_code, before_trade_data)
out_file = os.path.join(out_sub_path, index_code + '.csv')
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data = data.sort_values(by=['WEIGHT'], ascending=False)
data.to_csv(out_file, header=None)
def holding_data_clj(today, project_path, out_path):
# 输入参数
##################################################################################
# project_path = 'E:\\4_代码\\pycharmprojects\\timer\\input_data\\'
# out_path = 'E:\\3_数据\\7_other_data\\0_mail_holding_all\\'
person_name = 'caolongjie'
before_trade_data = Date().get_trade_date_offset(today, -1)
today = Date().change_to_str(today)
# 基金列表
##################################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
# 基金持仓证券
##################################################################################
fund_asset = MfcData().get_fund_security(before_trade_data)
fund_asset = fund_asset[[
'持仓日期', '序号', '基金名称', '证券代码', '证券名称', '证券类别', '市值', '市值比净值(%)', '持仓',
'净买量', '净买金额', '费用合计', '当日涨跌幅(%)', '持仓多空标志', '估值价格', '最新价'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_asset_fund = fund_asset[fund_asset['基金名称'] == fund_name]
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '持仓.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 基金资产
##################################################################################
fund_asset = MfcData().get_fund_asset(before_trade_data)
fund_asset = fund_asset[[
'序号', '统计日期', '基金编号', '基金名称', '股票资产', '净值', '基金份额', '单位净值',
'单位净值涨跌幅(%)', '累计单位净值', '昨日单位净值', '当日股票收益率(%)', '当日股票净买入金额',
'股票资产/净值(%)', '当前现金余额', '累计应收金额', '累计应付金额', '期货保证金账户余额', '期货保证金',
'可用期货保证金', '保证金'
]]
fund_asset_fund = fund_asset[fund_asset['基金名称'].map(
lambda x: x in fund_list)]
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
out_file = os.path.join(out_sub_path, '基金资产.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 股票库
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = [
"公司超五库.xls", "公司股票库.xls", "公司关联库.xls", "公司禁止库.xls", "公司限制库.xls",
"专户禁止库.xls", "量化11号禁止库.xls", "人寿固收限制库.xls", "人寿固收禁止库(委托人发送).xls",
"量化限制库.xls"
]
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
for i_file in range(len(pool_list)):
file = pool_list[i_file]
src_file = os.path.join(pool_path, 'raw_file', today, file)
out_file = os.path.join(out_sub_path, file)
try:
shutil.copyfile(src_file, out_file)
except:
pd.DataFrame([]).to_excel(out_file)
# 股票库 英文
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = {
"公司禁止库.xls": "Company Forbidden Pool.csv",
"公司关联库.xls": "Company Related Pool.csv",
"公司限制库.xls": "Company Limited Pool.csv"
}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
for scr_file, out_file in pool_list.items():
src_file = os.path.join(pool_path, 'raw_file', before_trade_data,
scr_file)
out_file = os.path.join(out_sub_path, out_file)
data = pd.read_excel(src_file, index_col=[0])
data.index = data['证券代码'].map(stock_code_add_postfix)
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data['Status'] = 1.0
data.to_csv(out_file, header=None, columns=['Status'])
# Recent IPO Stock.csv
##################################################################################
ipo_date_pd = Stock().get_ipo_date()
beg_date = (datetime.strptime(today, '%Y%m%d') -
timedelta(days=365)).strftime("%Y%m%d")
ipo_date_pd = ipo_date_pd[ipo_date_pd['IPO_DATE'] > beg_date]
ipo_date_pd.loc[:, 'IPO_DATE'] = 1.0
ipo_date_pd.index = ipo_date_pd.index.map(lambda x: x[0:6] + '-CN')
filename = 'Recent IPO Stock.csv'
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
print('loading ', filename, ' ......')
ipo_date_pd.to_csv(os.path.join(out_sub_path, filename),
header=None,
columns=['IPO_DATE'])
# Suspended List.csv
##################################################################################
status_data = Stock().get_trade_status_date(today)
ipo_date_pd = Stock().get_ipo_date()
data = pd.concat([status_data, ipo_date_pd], axis=1)
data = data.dropna()
data = data[data['DELIST_DATE'] >= today]
data['Trade_Status'] = 1.0
data.index = data.index.map(lambda x: x[0:6] + '-CN')
filename = 'Suspended List.csv'
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
print('loading ', filename, ' ......')
data.to_csv(os.path.join(out_sub_path, filename),
header=None,
columns=['Trade_Status'])
# CSI500 Benchmark.csv 5.5 现金
##################################################################################
data = Index().get_weight("000905.SH", before_trade_data)
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data['WEIGHT'] *= 94.5
result = pd.DataFrame([5.5], index=["CSH_CNY"], columns=['WEIGHT'])
result = pd.concat([result, data], axis=0)
filename = 'CSI500 Benchmark.csv'
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
print('loading ', filename, ' ......')
result.to_csv(os.path.join(out_sub_path, filename),
header=None,
columns=['WEIGHT'])
# 指数权重 Axioma
##################################################################################
index_code_list = ["000300.SH", "000905.SH", "000016.SH", "881001.WI"]
out_sub_path = os.path.join(out_path, person_name, today, "index_weight")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for index_code in index_code_list:
data = Index().get_weight(index_code, before_trade_data)
out_file = os.path.join(out_sub_path, index_code + '.csv')
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data.to_csv(out_file, header=None)
# 英文持仓情况
##################################################################################
en_holding_dict = {"建行中国人寿中证500管理计划": "China Life Insurance Portfolio.csv"}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
fund_sec = MfcData().get_fund_security(before_trade_data)
for name, out_file in en_holding_dict.items():
fund_sec_one = fund_sec[fund_sec['基金名称'] == name]
fund_sec_one = fund_sec_one[fund_sec_one['证券类别'] == '股票']
fund_sec_one = fund_sec_one[['证券代码', '持仓']]
fund_sec_one.index = fund_sec_one['证券代码'].map(stock_code_add_postfix)
fund_sec_one.index = fund_sec_one.index.map(lambda x: x[0:6] + '-CN')
out_file = os.path.join(out_sub_path, out_file)
fund_sec_one.to_csv(out_file, header=None, columns=['持仓'])
def holding_data_yangchao(today, project_path, out_path):
# 输入参数
##################################################################################
person_name = 'yangchao'
before_trade_data = Date().get_trade_date_offset(today, -1)
today = Date().change_to_str(today)
# 基金列表
##################################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
# 基金持仓证券
##################################################################################
fund_asset = MfcData().get_group_security(before_trade_data)
fund_asset = fund_asset[[
'日期', '组合名称', '基金名称', '证券代码', '证券名称', '持仓', '市值比净值(%)', '盈亏率(%)',
'证券类别', '当日买金额', '当日卖金额', '资产单元名称', '持仓多空标志'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_asset_fund = fund_asset[fund_asset['基金名称'] == fund_name]
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '.csv')
fund_asset_fund.to_csv(out_file, index=None)
# 绝对收益组合资产
##################################################################################
group_name = 'yangchao_group'
fund_val = fund.ix[:, group_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
fund_asset = MfcData().get_group_security(before_trade_data)
fund_asset = fund_asset[[
'日期', '组合名称', '基金名称', '证券代码', '证券名称', '持仓', '市值比净值(%)', '盈亏率(%)',
'证券类别', '当日买金额', '当日卖金额', '资产单元名称', '持仓多空标志'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
one_fund = fund_asset[fund_asset['组合名称'] == fund_name]
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
if fund_name == '绝对收益期货组合':
fund_name = "绝对收益股指期货组合"
out_file = os.path.join(out_sub_path, fund_name + '.csv')
one_fund.to_csv(out_file)
# 股票库
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = [
"公司超五库.xls", "公司股票库.xls", "公司关联库.xls", "公司禁止库.xls", "公司限制库.xls",
"绝对收益禁止库.xls", "绝对收益投资库.xls", "量化限制库.xls"
]
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
for i_file in range(len(pool_list)):
file = pool_list[i_file]
src_file = os.path.join(pool_path, 'raw_file', today, file)
out_file = os.path.join(out_sub_path, file)
try:
shutil.copyfile(src_file, out_file)
except:
pd.DataFrame().to_excel(out_file)
# 股票库 英文
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = {
"公司禁止库.xls": "Company Forbidden Pool.csv",
"公司关联库.xls": "Company Related Pool.csv",
"公司限制库.xls": "Company Limited Pool.csv",
"公司股票库.xls": "Company Investment Pool.csv",
"绝对收益禁止库.xls": "ABS Fund Forbidden Pool.csv",
"绝对收益投资库.xls": "ABS Fund Investment Pool.csv",
"量化限制库.xls": "Quantitative Limited Pool.csv"
}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
for scr_file, out_file in pool_list.items():
src_file = os.path.join(pool_path, 'raw_file', before_trade_data,
scr_file)
out_file = os.path.join(out_sub_path, out_file)
data = pd.read_excel(src_file, index_col=[0])
data.index = data['证券代码'].map(stock_code_add_postfix)
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data['Status'] = 1.0
data.to_csv(out_file, header=None, columns=['Status'])
# 股票库 Company Investment Pool.csv 包括公司股票库和公司超5库
##################################################################################
stock_pool_file = os.path.join(pool_path, 'raw_file', before_trade_data,
"公司股票库.xls")
stock_pool = pd.read_excel(stock_pool_file, index_col=[0])
stock_pool.index = stock_pool['证券代码'].map(stock_code_add_postfix)
stock_pool.index = stock_pool.index.map(lambda x: x[0:6] + '-CN')
stock_pool['Status'] = 1.0
stock_5_pool_file = os.path.join(pool_path, 'raw_file', before_trade_data,
"公司超五库.xls")
stock_5_pool = pd.read_excel(stock_5_pool_file, index_col=[0])
stock_5_pool.index = stock_5_pool['证券代码'].map(stock_code_add_postfix)
stock_5_pool.index = stock_5_pool.index.map(lambda x: x[0:6] + '-CN')
stock_5_pool['Status'] = 1.0
out_file = os.path.join(out_sub_path, "Company Investment Pool.csv")
res = pd.concat([stock_5_pool['Status'], stock_pool['Status']], axis=0)
res.to_csv(out_file, header=None)
# Recent IPO Stock.csv
##################################################################################
ipo_date_pd = Stock().get_ipo_date()
beg_date = (datetime.strptime(today, '%Y%m%d') -
timedelta(days=365)).strftime("%Y%m%d")
ipo_date_pd = ipo_date_pd[ipo_date_pd['IPO_DATE'] > beg_date]
ipo_date_pd.loc[:, 'IPO_DATE'] = 1.0
ipo_date_pd.index = ipo_date_pd.index.map(lambda x: x[0:6] + '-CN')
filename = 'Recent IPO Stock.csv'
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
print('loading ', filename, ' ......')
ipo_date_pd.to_csv(os.path.join(out_sub_path, filename),
header=None,
columns=['IPO_DATE'])
# Suspended List.csv
##################################################################################
status_data = Stock().get_trade_status_date(today)
ipo_date_pd = Stock().get_ipo_date()
data = pd.concat([status_data, ipo_date_pd], axis=1)
data = data.dropna()
data = data[data['DELIST_DATE'] >= today]
data['Trade_Status'] = 1.0
data.index = data.index.map(lambda x: x[0:6] + '-CN')
filename = 'Suspended List.csv'
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
print('loading ', filename, ' ......')
data.to_csv(os.path.join(out_sub_path, filename),
header=None,
columns=['Trade_Status'])
# Benchmark.csv 5.5 现金
##################################################################################
benchmark_dict = {
"000905.SH": "CSI500 Benchmark.csv",
"000300.SH": "CSI300 Benchmark.csv",
"000016.SH": "CSI50 Benchmark.csv"
}
for index_code, out_file in benchmark_dict.items():
data = Index().get_weight(index_code, before_trade_data)
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data['WEIGHT'] *= 94.5
result = pd.DataFrame([5.5], index=["CSH_CNY"], columns=['WEIGHT'])
result = pd.concat([result, data], axis=0)
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
result.to_csv(os.path.join(out_sub_path, out_file),
header=None,
columns=['WEIGHT'])
# 英文持仓情况
##################################################################################
en_holding_dict = {
"泰达宏利量化增强": "Quantitative Enhencement portfolio.csv",
"泰达宏利业绩驱动量化": "Quantitative Earning Drive.csv",
"泰达新思路": "New Thinking Portfolio.csv",
"泰达宏利集利债券": "High Dividend Bond Equity.csv",
"泰达宏利沪深300": "CSI300 Portfolio.csv",
"泰达中证500指数分级": "CSI500 Portfolio.csv"
}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
fund_sec = MfcData().get_fund_security(before_trade_data)
for name, out_file in en_holding_dict.items():
fund_sec_one = fund_sec[fund_sec['基金名称'] == name]
fund_sec_one = fund_sec_one[fund_sec_one['证券类别'] == '股票']
fund_sec_one = fund_sec_one[['证券代码', '持仓']]
fund_sec_one.index = fund_sec_one['证券代码'].map(stock_code_add_postfix)
fund_sec_one.index = fund_sec_one.index.map(lambda x: x[0:6] + '-CN')
print(fund_sec_one)
if out_file != "High Dividend Bond Equity.csv":
asset = MfcData().get_fund_asset(before_trade_data)
asset.index = asset['基金名称']
asset = asset[~asset.index.duplicated()]
fund_sec_one = fund_sec_one[~fund_sec_one.index.duplicated()]
fund_sec_one.ix['CSH_CNY', "持仓"] = asset.ix[name, "当前现金余额"]
out_file = os.path.join(out_sub_path, out_file)
fund_sec_one['持仓'] = fund_sec_one['持仓'].round(0)
fund_sec_one.to_csv(out_file, header=None, columns=['持仓'])
# 英文绝对收益持仓情况
##################################################################################
en_holding_dict = {
"绝对收益50对冲股票组合": "Absolute Return Strategy CSI50 Portfolio.csv",
"绝对收益300对冲股票组合": "Absolute Return Strategy CSI300 Portfolio.csv",
"绝对收益500对冲股票组合": "Absolute Return Strategy CSI500 Portfolio.csv",
}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
fund_sec = MfcData().get_group_security(before_trade_data)
for name, out_file in en_holding_dict.items():
fund_sec_one = fund_sec[fund_sec['组合名称'] == name]
fund_sec_one = fund_sec_one[fund_sec_one['证券类别'] == '股票']
fund_sec_one = fund_sec_one[['证券代码', '持仓']]
fund_sec_one.index = fund_sec_one['证券代码'].map(stock_code_add_postfix)
fund_sec_one.index = fund_sec_one.index.map(lambda x: x[0:6] + '-CN')
out_file = os.path.join(out_sub_path, out_file)
fund_sec_one.to_csv(out_file, header=None, columns=['持仓'])
# China Market Index.csv
##################################################################################
data = Index().get_weight("China_Index_Benchmark", before_trade_data)
out_file = "China Market Index.csv"
data.index = data.index.map(lambda x: x[0:6] + '-CN')
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
data.to_csv(os.path.join(out_sub_path, out_file),
header=None,
columns=['WEIGHT'])
# Monitor 基金证券
##################################################################################
en_holding_dict = {"泰达中证500指数分级": "CSI500 Monitor.csv"}
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
fund_sec = MfcData().get_fund_security(before_trade_data)
for name, out_file in en_holding_dict.items():
fund_sec_one = fund_sec[fund_sec['基金名称'] == name]
fund_sec_one = fund_sec_one[fund_sec_one['证券类别'] == '股票']
fund_sec_one = fund_sec_one[['证券代码', '持仓']]
fund_sec_one.columns = ['STOCK_CODE', 'HOLDING']
fund_sec_one.index = fund_sec_one['STOCK_CODE'].map(
stock_code_add_postfix)
out_file = os.path.join(out_sub_path, out_file)
fund_sec_one.to_csv(out_file, columns=['HOLDING'])
# Monitor 组合证券 绝对收益
##################################################################################
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
en_holding_dict = {
"绝对收益50对冲股票组合": "Absolute Trading Monitor CSI50.csv",
"绝对收益300对冲股票组合": "Absolute Trading Monitor CSI300.csv",
"绝对收益500对冲股票组合": "Absolute Trading Monitor CSI500.csv",
"绝对收益期货组合": "Absolute Monitor Option.csv",
}
fund_sec = MfcData().get_group_security(before_trade_data)
for name, out_file in en_holding_dict.items():
fund_sec_one = fund_sec[fund_sec['组合名称'] == name]
# fund_sec_one = fund_sec_one[fund_sec_one['资产类别'] == '股票资产']
fund_sec_one = fund_sec_one[['证券代码', '持仓']]
fund_sec_one.columns = ['STOCK_CODE', 'HOLDING']
if name != "绝对收益期货组合":
fund_sec_one.index = fund_sec_one['STOCK_CODE'].map(
stock_code_add_postfix)
else:
fund_sec_one.index = fund_sec_one['STOCK_CODE']
out_file = os.path.join(out_sub_path, out_file)
fund_sec_one.to_csv(out_file, columns=['HOLDING'])
def Index_Group_Statistics(out_path, index_code, value_factor, group_number):
# 0、输入参数
##############################################################################
# out_path = 'C:\\Users\\doufucheng\\OneDrive\\Desktop\\data\\'
# index_code = "000300.SH"
# value_factor = 'PE_TTM'
# group_number = 8
# 1、原始数据整理
##############################################################################
# 1、1: 读入指数收益率数据和pe
###################################
data = Index().get_index_factor(index_code, None, None,
['PCT', value_factor])
data = data.dropna(subset=[value_factor])
data.columns = ['pct', 'pe']
data['pe'] = data['pe'].round(2)
year_number = 242
# 1、2: 指数 日收益 累计收益
###################################
data['ln_pct'] = np.log(data['pct'] + 1)
data['cum_sum_pct'] = data['ln_pct'].cumsum().map(lambda x: np.exp(x) - 1)
# 1、3: 之后1、3、5年的收益
###################################
data['return_1y'] = data['ln_pct'].rolling(
window=year_number).sum().shift(-year_number)
data['return_3y'] = data['ln_pct'].rolling(window=year_number *
3).sum().shift(-year_number * 3)
data['return_5y'] = data['ln_pct'].rolling(window=year_number *
5).sum().shift(-year_number * 5)
# 1、4: 之后1、3、5年的收益是否大于0
###################################
data['if_zero_1y'] = data['return_1y'] > 0.0
data['if_zero_3y'] = data['return_3y'] > 0.0
data['if_zero_5y'] = data['return_5y'] > 0.0
# 1、5: 在全局的pe百分比
###################################
data['rank'] = data['pe'].rank() / len(data)
data['rank'] *= 100
data['rank'] = data['rank'].round(0)
# 1、7: 之后1年超过初始PE的时间
###################################
for i in range(0, len(data) - year_number):
init_pe = data.ix[i, "pe"]
data_bigger = data.ix[i:i + year_number, 'pe'] > init_pe
data_bigger = data_bigger[data_bigger]
ratio = len(data_bigger) / year_number
data.ix[i, 'if_1y_ratio'] = ratio
# 1、8: 之后3年超过初始PE的时间
###################################
for i in range(0, len(data) - year_number * 3):
init_pe = data.ix[i, "pe"]
data_bigger = data.ix[i:i + year_number * 3, 'pe'] > init_pe
data_bigger = data_bigger[data_bigger]
ratio = len(data_bigger) / (year_number * 3)
data.ix[i, 'if_3y_ratio'] = ratio
# 1、9: 之后5年超过初始PE的时间
###################################
for i in range(0, len(data) - year_number * 5):
init_pe = data.ix[i, "pe"]
data_bigger = data.ix[i:i + year_number * 5, 'pe'] > init_pe
data_bigger = data_bigger[data_bigger]
ratio = len(data_bigger) / (year_number * 5)
data.ix[i, 'if_5y_ratio'] = ratio
data['pe_cut'] = pd.qcut(data['pe'], group_number)
data.to_csv(out_path + index_code + '_原始数据.csv')
# 2、分组统计
##############################################################################
# 2、1: 1年后 收益中位数、pe超过初始PE的百分比、有效数字的个数
########################################################
my_data = data.dropna(subset=['return_1y'])
my_data['pe_cut'] = pd.qcut(data['pe'], group_number)
if_pe_1y_ratio = my_data.groupby(by=['pe_cut'])['if_1y_ratio'].mean()
return_1y_median = my_data.groupby(by=['pe_cut'])['return_1y'].median()
return_1y_count = my_data.groupby(by=['pe_cut'])['return_1y'].count()
my_data['pe_rank_cut'] = pd.qcut(data['rank'], group_number)
return_pe_rank = my_data.groupby(by=['pe_rank_cut'])['return_1y'].median()
return_pe_rank = pd.DataFrame(
return_pe_rank.index.values.to_dense(),
index=return_1y_median.index.values.to_dense(),
columns=['历史百分位数'])
return_pe_rank['开始时间'] = data.index[0]
return_pe_rank['结束时间'] = data.index[-1]
return_pe_rank['当前PE'] = data.ix[-1, 'pe']
# 1年后 收益大于0的百分比
if_zero_number = my_data.groupby(by=['pe_cut'])['if_zero_1y'].sum()
sum_number = my_data.groupby(by=['pe_cut'])['if_zero_1y'].count()
zero_ratio_1y = pd.DataFrame(if_zero_number / sum_number)
# 2、1: 3年后 收益中位数、pe超过初始PE的百分比、有效数字的个数
#######################################################
my_data = data.dropna(subset=['return_3y'])
my_data['pe_cut'] = pd.qcut(data['pe'], group_number)
if_pe_3y_ratio = my_data.groupby(by=['pe_cut'])['if_3y_ratio'].median()
return_3y_median = my_data.groupby(by=['pe_cut'])['return_3y'].median()
return_3y_count = my_data.groupby(by=['pe_cut'])['return_3y'].count()
if_zero_number = my_data.groupby(by=['pe_cut'])['if_zero_3y'].sum()
sum_number = my_data.groupby(by=['pe_cut'])['if_zero_3y'].count()
zero_ratio_3y = pd.DataFrame(if_zero_number / sum_number)
# 2、3: 5年后 收益中位数、pe超过初始PE的百分比、有效数字的个数
#######################################################
my_data = data.dropna(subset=['return_5y'])
my_data['pe_cut'] = pd.qcut(data['pe'], group_number)
if_pe_5y_ratio = my_data.groupby(by=['pe_cut'])['if_5y_ratio'].median()
return_5y_median = my_data.groupby(by=['pe_cut'])['return_5y'].median()
return_5y_count = my_data.groupby(by=['pe_cut'])['return_5y'].count()
if_zero_number = my_data.groupby(by=['pe_cut'])['if_zero_5y'].sum()
sum_number = my_data.groupby(by=['pe_cut'])['if_zero_5y'].count()
zero_ratio_5y = pd.DataFrame(if_zero_number / sum_number)
# 数据输出
##############################################################################
res = pd.concat([
return_pe_rank, return_1y_count, return_3y_count, return_5y_count,
return_1y_median, return_3y_median, return_5y_median, zero_ratio_1y,
zero_ratio_3y, zero_ratio_5y, if_pe_1y_ratio, if_pe_3y_ratio,
if_pe_5y_ratio
],
axis=1)
res.index.name = "PE绝对值范围"
res.columns = [
"PE百分位范围", '开始时间', '结束时间', '当前PE', '有效数据个数_1y', '有效数据个数_3y',
'有效数据个数_5y', '收益中位数_1y', '收益中位数_3y', '收益中位数_5y', '收益大于0的比例_1y',
'收益大于0的比例_3y', '收益大于0的比例_5y', '超过初始PE天数的比例的中位数_1y',
'超过初始PE天数的比例的中位数_3y', '超过初始PE天数的比例的中位数_5y'
]
res.index = res.index.values.to_dense()
res.index.name = "PE绝对值范围"
num_format_pd = pd.DataFrame([], columns=res.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix[
'format',
['开始时间', '结束时间', '当前PE', '有效数据个数_1y', '有效数据个数_3y', '有效数据个数_5y'
]] = '0.0'
begin_row_number = 0
begin_col_number = 1
color = "red"
file_name = out_path + index_code + '_收益中位数.xlsx'
sheet_name = "收益中位数"
write_pandas(file_name, sheet_name, begin_row_number, begin_col_number,
res, num_format_pd, color)
