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'])