def cal_return_all(self, beg_date, end_date, risk_model_name, period='D'):
""" 计算每天的因子收益率、因子暴露、股票的残差收益率 """
self.set_model_name(risk_model_name)
self.get_data_all(risk_model_name)
date_series = Date().get_trade_date_series(beg_date, end_date, period)
date_series = list(
set(date_series) & set(self.pct_chg.columns)
& set(self.free_mv_data.columns) & set(self.trade_status.columns)
& set(self.industry.columns))
date_series.sort()
factor_return = pd.DataFrame()
res_return = pd.DataFrame()
# 因子暴露文件
for i_date in range(len(date_series)):
date = date_series[i_date]
before_date = Date().get_trade_date_offset(date, -1)
factor_return_date, exposure_before_date, res_return_date = self.cal_return_date(
date)
exposure_file = os.path.join(self.exposure_path,
"exposure_%s.csv" % before_date)
exposure_before_date.to_csv(exposure_file)
factor_return = pd.concat([factor_return, factor_return_date],
axis=1)
res_return = pd.concat([res_return, res_return_date], axis=1)
# 因子收益率文件
factor_return = factor_return.T
factor_return_file = os.path.join(self.factor_return_path,
"factor_return.csv")
if os.path.exists(factor_return_file):
old_data = self.get_factor_return()
factor_return = Stock().pandas_add_row(old_data, factor_return)
factor_return.to_csv(factor_return_file)
factor_return_cum = factor_return.cumsum()
factor_return_file = os.path.join(self.factor_return_path,
"factor_return_cum.csv")
factor_return_cum.to_csv(factor_return_file)
# 股票残差率文件
res_return = res_return.T
res_return_file = os.path.join(self.res_return_path,
"stock_residual_return.csv")
if os.path.exists(res_return_file):
old_data = self.get_stock_residual_return()
res_return = Stock().pandas_add_row(old_data, res_return)
res_return.to_csv(res_return_file)
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'])
