def get_report_industry_change(self, fund_name, report_date,
last_report_date):
""" 报告期之间的权重变动 """
# report_date = "20181231"
# last_report_date = "20180930"
# fund_name = "泰达逆向策略"
report_date = Date().get_trade_date_offset(report_date, 0)
last_report_date = Date().get_trade_date_offset(last_report_date, 0)
print(fund_name, report_date, last_report_date)
data_report = MfcData().get_fund_stock_weight(fund_name=fund_name,
date=report_date)
data_report.columns = ['NowHolder']
data_report = data_report[~data_report.index.duplicated()]
data_report_last = MfcData().get_fund_stock_weight(
fund_name=fund_name, date=last_report_date)
data_report_last.columns = ['LastHolder']
data_report_last = data_report_last[~data_report_last.index.duplicated(
)]
industry = Stock().read_factor_h5("industry_citic1")
industry_date = pd.DataFrame(industry[report_date])
industry_date.columns = ['Industry']
data_report_last_industry = pd.concat(
[data_report_last, industry_date], axis=1)
data_report_last_industry = data_report_last_industry.dropna()
data_last_gb = pd.DataFrame(
data_report_last_industry.groupby(
by=['Industry']).sum()['LastHolder'])
data_report_industry = pd.concat([data_report, industry_date], axis=1)
data_report_industry = data_report_industry.dropna()
data_gb = pd.DataFrame(
data_report_industry.groupby(by=['Industry']).sum()['NowHolder'])
data = pd.concat([data_last_gb, data_gb], axis=1)
data.index = data.index.map(
lambda x: Stock().get_industry_citic1_name_ch(x))
data['Diff'] = data['NowHolder'] - data['LastHolder']
data = data.sort_values(by=['Diff'], ascending=False)
data_bigger = data.iloc[0:3, :]
str1 = "本报告期内，本基金增加了" + "、".join(data_bigger.index) + "的配置"
data = data.sort_values(by=['Diff'], ascending=True)
data_smaller = data.iloc[0:3, :]
str2 = "，减少了" + "、".join(data_smaller.index) + '的配置。'
return str1 + str2
def get_fund_pct(self):
""" 产品某一段时间内的涨跌幅，专户公募分开 """
if self.type == "专户":
fund_pct = MfcData().get_mfc_private_fund_nav(self.fund_name)
fund_pct['基金涨跌幅'] = fund_pct['累计复权净值'].pct_change()
fund_pct = fund_pct[['累计复权净值', '基金涨跌幅']]
fund_pct = fund_pct.dropna()
fund_pct = fund_pct.loc[self.beg_date_pre:self.end_date, :]
else:
fund_pct = MfcData().get_mfc_public_fund_nav(self.fund_code)
fund_pct = fund_pct[['NAV_ADJ', 'NAV_ADJ_RETURN1']]
fund_pct['NAV_ADJ_RETURN1'] = fund_pct['NAV_ADJ'].pct_change()
fund_pct.columns = ['累计复权净值', '基金涨跌幅']
fund_pct = fund_pct.dropna()
fund_pct = fund_pct.loc[self.beg_date_pre:self.end_date, :]
return fund_pct
def cal_ipo_mkt_monitor(today, project_path, out_path):
# 参数
###########################################################################
thread_value = 60000000
day_period = 20
person_list = ['liuxin', 'yangchao', 'liuyang']
before_trade_data = Date().get_trade_date_offset(today, -1)
trade_series = Date().get_trade_date_series(end_date=before_trade_data)
trade_series = pd.DataFrame(trade_series, index=trade_series)
trade_series = list(trade_series.index[-day_period:])
# 基金经理管理的基金
###########################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
# 所需要的持仓文件
#######################################################################################################
holding_data = pd.DataFrame([])
for i_date in range(len(trade_series)):
date = trade_series[i_date]
data = MfcData().get_group_security(date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '持仓', '证券类别', '最新价']]
data.columns = ['FundName', 'StockCode', 'Holding', 'Type', 'Price']
data['Date'] = date
data = data[data.Type == '股票']
data.StockCode = data.StockCode.map(stock_code_add_postfix)
data["Market"] = data.StockCode.map(get_stcok_market)
data['Holding'] = data['Holding'].astype(np.float)
data['Price'] = data['Price'].astype(np.float)
data['StockMarketValue'] = data['Holding'] * data['Price']
holding_data = pd.concat([holding_data, data], axis=0)
holding_data = holding_data.reset_index(drop=True)
# 开始计算每只基金的股票平均市值
#######################################################################################################
for i_col in range(len(person_list)):
person_name = person_list[i_col]
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
columns = ['沪市平均', '沪市最新', '沪市目标', '深市平均', '深市最新', '深市目标']
result = pd.DataFrame([], index=fund_list, columns=columns)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund IPO Stock MarketValue 20 days ", fund_name)
holding_data_fund = holding_data[holding_data.FundName ==
fund_name]
fund_gb = holding_data_fund.groupby(
by=['Date', 'Market'])['StockMarketValue'].sum()
fund_gb = fund_gb.unstack()
result.ix[fund_name, "沪市平均"] = fund_gb.ix[:, "SH"].mean()
result.ix[fund_name, "沪市最新"] = fund_gb.ix[-1, "SH"]
result.ix[fund_name,
"沪市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SH"].sum()
result.ix[fund_name, "深市平均"] = fund_gb.ix[:, "SZ"].mean()
result.ix[fund_name, "深市最新"] = fund_gb.ix[-1, "SZ"]
result.ix[fund_name,
"深市目标"] = thread_value * day_period - fund_gb.ix[
0:-1, "SZ"].sum()
result = result[(result["沪市平均"] < thread_value) |
(result["深市平均"] < thread_value)]
result /= 10000
out_sub_path = os.path.join(out_path, person_name, today,
'ipo_mkt_monitor')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, '新股市值监控.csv')
result.to_csv(out_file)
def ipo_sell(self,
today=datetime.today().strftime("%Y%m%d")):
""" 计算所有基金新股是否打开 """
before_trade_data = Date().get_trade_date_offset(today, -1)
# get holding data
data = MfcData().get_group_security(before_trade_data)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '基金编号', '组合名称', '组合编号', '证券代码', '持仓', '证券类别']]
data.columns = [['基金名称', '基金编号（序号）', '组合名称', '组合编号', '证券代码', '指令数量', '证券类别']]
data = data[data['证券类别'] == '股票']
data['证券代码'] = data['证券代码'].map(CodeFormat().stock_code_add_postfix)
data["交易市场内部编号"] = data['证券代码'].map(CodeFormat().get_stcok_market)
fund_info = MfcData().get_mfc_fund_info()
fund_info = fund_info.dropna(subset=['FundId'])
fund_info['FundId'] = fund_info['FundId'].map(CodeFormat.stock_code_add_postfix)
fund_info['FundId'] = fund_info['FundId'].map(lambda x: x[0:6])
# get ipo data
Stock().load_all_stock_code_now()
Stock().load_ipo_date()
stock = Stock().get_ipo_date()
stock.columns = ['IpoDate', 'DelistDate']
stock['证券代码'] = stock.index
stock['IpoDate'] = stock['IpoDate'].map(lambda x: str(int(x)))
# get New Stock
new_stock_date = datetime.today() - timedelta(days=self.new_days)
new_stock_date = new_stock_date.strftime("%Y%m%d")
all_data = pd.merge(data, stock, on=['证券代码'], how="left")
all_data = all_data[all_data.IpoDate > new_stock_date]
# get Vol and Pct of New Stock
code_list = list(set(all_data['证券代码'].values))
code_str = ','.join(code_list)
pct = w.wsq(code_str, "rt_pct_chg,rt_vol")
pct = pd.DataFrame(pct.Data, columns=pct.Codes, index=['Pct', 'Vol']).T
pct['证券代码'] = pct.index
# filter multi_factor
new_data = pd.merge(all_data, pct, on=['证券代码'], how="left")
new_data = new_data[new_data['Vol'] > 0]
new_data = new_data[new_data['Pct'] < 0.09]
# New Local Folder
out_sub_path = os.path.join(self.data_path, today)
if not os.path.exists(out_sub_path):
os.mkdir(out_sub_path)
# New FTP Folder
ftp = MyFtp()
ftp.connect()
ftp_folder = os.path.join(self.ftp_path, today)
ftp.upload_folder(ftp_folder)
ftp.close()
# manager data
manager_data = MfcData().get_manager_fund()
for i_manager in range(len(manager_data.columns)):
manager_name = manager_data.columns[i_manager]
manager_fund = manager_data[manager_name]
manager_fund = manager_fund.dropna()
fund_data = new_data[new_data['基金名称'].map(lambda x: x in manager_fund.values)]
if len(fund_data) > 0:
warnings.filterwarnings("ignore")
fund_data_out = fund_data[['证券代码', '指令数量', '交易市场内部编号',
'基金编号（序号）', '基金名称', '组合编号']]
fund_data_out['委托方向'] = 2
fund_data_out['指令价格'] = 0
fund_data_out['交易市场内部编号'] = fund_data_out['交易市场内部编号'].map(lambda x: 1 if x == 'SH' else 2)
fund_data_out['价格模式'] = ""
fund_data_out['当前指令市值/净值（%）'] = ""
fund_data_out['目标市值/净值（%）'] = ""
fund_data_out['基金名称'] = ""
fund_data_out['证券代码'] = fund_data_out['证券代码'].map(lambda x: x[0:6])
fund_data_out = fund_data_out[['证券代码', '委托方向', '指令数量', '指令价格',
'价格模式', '交易市场内部编号', '当前指令市值/净值（%）', '目标市值/净值（%）',
'基金编号（序号）', '基金名称', '组合编号']]
file = manager_name + '.xls'
out_file = os.path.join(out_sub_path, file)
print(out_file)
fund_data_out.to_excel(out_file, index=None)
ftp = MyFtp()
ftp.connect()
ftp_file = os.path.join(self.ftp_path, today, file)
ftp.upload_file(ftp_file, out_file)
ftp.close()
def AttributeMfctedaFund(index_code_ratio, fund_code, index_code, fund_name,
beg_date, end_date, fund_id, path, type,
mg_fee_ratio):
"""
将某只基金 一段时间内 每日净值涨跌 拆分
"""
# 参数举例
######################################################################################
# index_code_ratio = 0.95
# fund_code = '162216.OF'
# index_code = '000905.SH'
# fund_name = '泰达中证500指数分级'
# beg_date = '20180101'
# end_date = '20180731'
# fund_id = 38
# path = 'C:\\Users\\doufucheng\\OneDrive\\Desktop\\data\\'
# type = '专户'
# 读取基金复权涨跌幅
################################################################################################
beg_date = Date().get_trade_date_offset(beg_date, -0)
if type == "专户":
fund_pct = MfcData().get_fund_nav_adjust(
fund_name,
Date().get_trade_date_offset(beg_date, -2), end_date)
fund_pct['基金涨跌幅'] = fund_pct['累计复权净值'].pct_change()
else:
fund_pct = MfcData().get_mfcteda_public_fund_pct_wind(
fund_code, beg_date, end_date)
fund_pct.columns = ['基金涨跌幅']
# 指数收益 持仓数据
# 净值 = 股票资产 + 债券资产 + 基金资产 + 回购资产 + 当前现金余额 + 累计应收 - 累计应付
# 累计应收 和 累计应付 代表 每日申赎 计提 交易管理费用等 未结算至现金的部分
# 这里并没有按照每日拆分净值的方式计算 而是按照每日拆分当日总浮动盈亏 = 前日净值 * 当日基金复权涨跌幅
################################################################################################
index_pct = Index().get_index_factor(
index_code,
Date().get_trade_date_offset(beg_date, -1), end_date, ['CLOSE'])
index_pct = index_pct.pct_change()
index_pct.columns = ['指数涨跌幅']
fund_asset = MfcData().get_fund_asset_period(fund_id, beg_date, end_date)
close_unadjust = Stock().get_factor_h5("Price_Unadjust", None,
"primary_mfc")
adjust_factor = Stock().get_factor_h5("AdjustFactor", None, "primary_mfc")
fund_asset['股票资产-汇总'] = fund_asset['股票资产']
data = pd.concat([fund_pct, index_pct, fund_asset], axis=1)
data = data.dropna(subset=['基金涨跌幅', '指数涨跌幅'])
data['昨日净值'] = data['净值'].shift(1)
data['昨日基金份额'] = data['基金份额'].shift(1)
data['昨日单位净值'] = data['单位净值'].shift(1)
# 计算 每一日 新股收益 股票收益
################################################################################################
date_series = Date().get_trade_date_series(beg_date, end_date)
for i_date in range(len(date_series)):
date = date_series[i_date]
new_stock_return, new_stock_asset = CalNewStockReturnDaily(
fund_name,
date,
path,
close_unadjust,
adjust_factor,
cal_type="close")
stock_return, mg_fee, trade_fee, stock_asset = CalStockReturnDaily(
fund_name,
date,
path,
close_unadjust,
adjust_factor,
mg_fee_ratio,
cal_type="close")
data.loc[date, '新股资产'] = new_stock_asset
data.loc[date, '股票资产'] = stock_asset
data.loc[date, '新股盈亏'] = new_stock_return
data.loc[date, '股票盈亏'] = stock_return
data.loc[date, '管理托管费用'] = mg_fee
data.loc[date, '交易印花费用'] = trade_fee
new_stock_return, new_stock_asset = CalNewStockReturnDaily(
fund_name,
date,
path,
close_unadjust,
adjust_factor,
cal_type="average")
stock_return, mg_fee, trade_fee, stock_asset = CalStockReturnDaily(
fund_name,
date,
path,
close_unadjust,
adjust_factor,
mg_fee_ratio,
cal_type="average")
data.loc[date, '新股盈亏-TradePrice'] = new_stock_return
data.loc[date, '股票盈亏-TradePrice'] = stock_return
data = data.dropna(subset=['基金涨跌幅', '指数涨跌幅'])
data[['新股盈亏', '当日股票总盈亏金额']] = data[['新股盈亏', '当日股票总盈亏金额']].fillna(0.0)
data = data[data['股票资产'] > 0.0]
data['股票仓位'] = data['股票资产'] / data['净值']
data['昨日股票仓位'] = data['股票仓位'].shift(1)
# 资产盈亏 = 股票盈亏 + 新股盈亏 + 债券其他 + 托管管理费 + 交易印花费
################################################################################################
cols = ['管理托管费用', '交易印花费用', '股票盈亏', '新股盈亏']
data[cols] = data[cols].fillna(0.0)
data[
'汇总盈亏'] = data['管理托管费用'] + data['交易印花费用'] + data['股票盈亏'] + data['新股盈亏']
data['日内交易盈亏'] = data['股票盈亏'] - data['股票盈亏-TradePrice'] + data[
'新股盈亏'] - data['新股盈亏-TradePrice']
data['资产盈亏'] = data['基金涨跌幅'] * data['昨日净值']
data['固收其他盈亏'] = data['资产盈亏'] - data['汇总盈亏'] - data['日内交易盈亏']
data['昨日股票资产'] = data['股票资产'].shift(1)
data['股票涨跌幅'] = data['股票盈亏'] / data['昨日股票资产']
# 股票盈亏 = 基准盈亏 + 超额盈亏
################################################################################################
data['基准盈亏'] = data['指数涨跌幅'] * data['昨日净值'] * index_code_ratio
data['超额盈亏'] = data['昨日股票仓位'] * data['股票涨跌幅'] * data['昨日净值'] - data['基准盈亏']
# 超额盈亏 = 择时（资产配置能力） + 选股能力
################################################################################################
data['择时盈亏'] = (data['昨日股票仓位'] -
index_code_ratio) * data['指数涨跌幅'] * data['昨日净值']
data['选股盈亏'] = data['昨日股票仓位'] * (data['股票涨跌幅'] -
data['指数涨跌幅']) * data['昨日净值']
data['全仓选股盈亏'] = (data['股票涨跌幅'] - data['指数涨跌幅']) * data['昨日净值']
# 以单位净值计算
################################################################################################
data['净值-资产盈亏'] = data['资产盈亏'] / data['昨日基金份额']
data['净值-管理托管费用'] = data['管理托管费用'] / data['昨日基金份额']
data['净值-交易印花费用'] = data['交易印花费用'] / data['昨日基金份额']
data['净值-股票盈亏'] = data['股票盈亏'] / data['昨日基金份额']
data['净值-新股盈亏'] = data['新股盈亏'] / data['昨日基金份额']
data['净值-固收其他盈亏'] = data['固收其他盈亏'] / data['昨日基金份额']
data['净值-日内交易盈亏'] = data['日内交易盈亏'] / data['昨日基金份额']
data['净值-基准盈亏'] = data['基准盈亏'] / data['昨日基金份额']
data['净值-超额盈亏'] = data['超额盈亏'] / data['昨日基金份额']
data['净值-择时盈亏'] = data['择时盈亏'] / data['昨日基金份额']
data['净值-选股盈亏'] = data['选股盈亏'] / data['昨日基金份额']
data['净值-全仓选股盈亏'] = data['全仓选股盈亏'] / data['昨日基金份额']
index = [
'净值-资产盈亏', '净值-股票盈亏', '净值-新股盈亏', '净值-固收其他盈亏', '净值-日内交易盈亏', '净值-管理托管费用',
'净值-交易印花费用', '净值-基准盈亏', '净值-超额盈亏', '净值-择时盈亏', '净值-选股盈亏', '净值-全仓选股盈亏'
]
# 按照 百分比 收益率计算
################################################################################################
data = data.dropna(subset=['昨日单位净值'])
nav = data.loc[data.index[0], '昨日单位净值']
pct = data['净值-资产盈亏'].sum() / nav
result = pd.DataFrame([], columns=['净值变化', '百分比', '收益率'], index=index)
result.loc[index, '净值变化'] = data.loc[:, index].sum()
result.loc[index,
'百分比'] = result.loc[index, '净值变化'] / result.loc['净值-资产盈亏',
'净值变化']
result.loc[index, '收益率'] = result.loc[index, '百分比'] * pct
# 年化收益率 开始时间 结束时间
################################################################################################
result.index = [
'基金整体', '股票部分', '新股部分', '固收+其他部分', "日内交易部分", '管理托管', '交易印花', '股票基准',
'股票超额', '股票择时', '股票选股', '全仓股票选股'
]
days = (datetime.strptime(end_date, '%Y%m%d') -
datetime.strptime(beg_date, '%Y%m%d')).days
result.loc[:, '年化收益'] = result.loc[:, '收益率'].map(lambda x: (x + 1)**
(365 / days) - 1.0)
result.loc['股票仓位', :] = data['股票仓位'].mean()
result.loc['开始时间', :] = data.index[0]
result.loc['结束时间', :] = data.index[-1]
################################################################################################
# 写入每天的拆分
####################################################################################################################
num_format_pd = pd.DataFrame([], columns=data.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format',
['基金涨跌幅', '指数涨跌幅', '股票仓位', '昨日股票仓位', '股票涨跌幅']] = '0.00%'
num_format_pd.ix['format', [
'单位净值', '昨日单位净值', '净值-管理托管费用', '净值-交易印花费用', '净值-股票盈亏', '净值-新股盈亏',
'净值-固收其他盈亏', '净值-基准盈亏', '净值-择时盈亏', '净值-选股盈亏', '净值-资产盈亏', '净值-全仓选股盈亏'
]] = '0.0000'
begin_row_number = 0
begin_col_number = 1
color = "red"
save_path = os.path.join(path, fund_name, "整体")
file_name = os.path.join(
save_path, "归因_" + fund_name + '_' + str(data.index[0]) + '_' +
str(data.index[-1]) + ".xlsx")
if not os.path.exists(save_path):
os.makedirs(save_path)
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(data,
worksheet,
begin_row_number=begin_row_number,
begin_col_number=begin_col_number,
num_format_pd=num_format_pd,
color=color,
fillna=True)
excel.close()
# 写入汇总的拆分
####################################################################################################################
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', ['净值变化']] = '0.0000'
begin_row_number = 0
begin_col_number = 1
color = "red"
save_path = os.path.join(path, fund_name, "整体")
file_name = os.path.join(
save_path, "归因汇总_" + fund_name + '_' + str(data.index[0]) + '_' +
str(data.index[-1]) + ".xlsx")
if not os.path.exists(save_path):
os.makedirs(save_path)
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result,
worksheet,
begin_row_number=begin_row_number,
begin_col_number=begin_col_number,
num_format_pd=num_format_pd,
color=color,
fillna=True)
excel.close()
def cal_reverse_5days(today, project_path, out_path):
# 参数
###########################################################################
person_list = ['liuxin', 'yangchao', 'caolongjie', 'liuyang']
day_period = 5
# 日期参数
###########################################################################
before_trade_data = Date().get_trade_date_offset(today, -1)
trade_series = Date().get_trade_date_series(end_date=before_trade_data)
trade_series = pd.DataFrame(trade_series, index=trade_series)
trade_series = list(trade_series.index[-day_period:])
# 基金经理管理的基金
###########################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
result = pd.DataFrame([])
# 开始计算每只基金的5日五日反向
#######################################################################################################
for i_col in range(len(person_list)):
person_name = person_list[i_col]
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
print(" Cal Fund Reverse 5 days ", fund_name)
for i_date in range(len(trade_series)):
cur_date = trade_series[i_date]
data = MfcData().get_trade_statement(cur_date)
data = data.dropna(subset=['基金名称'])
data = data[['基金名称', '证券代码', '委托方向', '成交数量', '资产类别']]
data.columns = [
'FundName', 'StockCode', 'Direction', 'TradeNumber', 'Type'
]
data = data[data.FundName == fund_name]
data = data[data.Type == '股票资产']
data.StockCode = data.StockCode.map(stock_code_add_postfix)
data.Direction = data.Direction.map(lambda x: 2
if x == '卖出' else 1)
result_date = pd.DataFrame(data.Direction.values,
index=data.StockCode.values,
columns=[cur_date])
if i_date == 0:
result = result_date
else:
try:
result = pd.concat([result, result_date], axis=1)
except:
pass
result = result.fillna(0)
result.index.name = 'CODE'
result = result.astype(np.int)
my_result = []
for i_row in range(len(result)):
vals_list = list(result.ix[i_row, trade_series])
vals_set = list(set(result.ix[i_row, trade_series]))
if 2 in vals_set:
append_row = [result.index[i_row], 2]
append_row.extend(vals_list)
my_result.append(append_row)
if 1 in vals_set:
append_row = [result.index[i_row], 1]
append_row.extend(vals_list)
my_result.append(append_row)
col = ['CODE', 'FLAG']
col.extend(trade_series)
my_result = pd.DataFrame(my_result, columns=col)
out_sub_path = os.path.join(out_path, person_name, today,
'reverse_5days')
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '.csv')
my_result.to_csv(out_file, index=None)
print(fund_name, len(result), len(my_result))