def load_holding_date(self, date):
load_file_list = [
'01AfterTA.ok', '01BeforeTA.ok', '01HIS成交回报.xls', '01HIS委托流水.xls',
'01TODAY单元资产.xls', '01TODAY基金证券.xls', '01TODAY基金资产.xls',
'01TODAY组合证券.xls'
]
ftp_path = Parameter().get_load_in_file(self.name)
local_path = Parameter().get_load_out_file(self.name)
local_path = os.path.join(local_path, 'raw_file')
date_int = Date().change_to_str(date)
local_sub_path = os.path.join(local_path, date_int)
ftp_sub_path = os.path.join(ftp_path, date_int)
if not os.path.exists(local_sub_path):
os.mkdir(local_sub_path)
ftp = MyFtp()
ftp.connect()
print(
"######################################### Loading Ftp File #########################################"
)
ftp.load_file_folder_change_name(ftp_sub_path, local_sub_path,
load_file_list, load_file_list)
ftp.close()
def load_stock_pool_date(self, date):
pool_file = os.path.join(Parameter().get_read_file("Mfc_Fund"),
"Stock_Pool_Ftp.xlsx")
pool_data = pd.read_excel(pool_file, encoding='gbk')
pool_data = pool_data.dropna(subset=['Pool_Ch', 'Pool_Ftp'])
pool_ch = list(pool_data['Pool_Ch'].values)
pool_ftp = list(pool_data['Pool_Ftp'].values)
ftp_path = Parameter().get_load_in_file(self.name)
local_path = Parameter().get_load_out_file(self.name)
local_path = os.path.join(local_path, 'raw_file')
date_int = Date().change_to_str(date)
local_sub_path = os.path.join(local_path, date_int)
ftp_sub_path = os.path.join(ftp_path, date_int)
if not os.path.exists(local_sub_path):
os.mkdir(local_sub_path)
ftp = MyFtp()
ftp.connect()
print(
"######################################### Loading Ftp File #########################################"
)
ftp.load_file_folder_change_name(ftp_sub_path, local_sub_path,
pool_ftp, pool_ch)
ftp.close()
def load_fund_factor(self, factor_name, beg_date, end_date):
beg_date = Date().change_to_str(beg_date)
end_date = Date().change_to_str(end_date)
new_data = FinDb().load_raw_data_filter_period(factor_name, beg_date,
end_date)
fund_info_data = FundStatic().get_fund_info()
val_name = Parameter().get_load_findb_val_name(factor_name)
new_data = pd.merge(new_data, fund_info_data, on="证券内码", how='inner')
new_data = pd.DataFrame(
new_data[val_name].values,
index=[list(new_data['基金代码'].values),
list(new_data['日期'].values)])
new_data = new_data.sort_index()
new_data = new_data[~new_data.index.duplicated()]
new_data = new_data.unstack()
new_data.columns = new_data.columns.droplevel(level=0)
new_data = new_data.T
new_data = new_data.dropna(how='all')
new_data.index = new_data.index.map(str)
out_file = Parameter().get_read_file(factor_name)
if os.path.exists(out_file):
data = pd.read_csv(out_file, encoding='gbk', index_col=[0])
data.index = data.index.map(str)
data = pandas_add_row(data, new_data)
else:
print(" File No Exist ", factor_name)
data = new_data
data = data.dropna(how='all')
data.to_csv(out_file)
def get_primary_factor_mfc_file(self, factor_name="Pct_chg"):
# 读取 primary data 路径
#############################################################################
param_path = Parameter().get_read_file("Primary_Factor_Mfc")
param_file_list = ['FinancialStatementsDataPar.csv', 'industry.csv',
'DailyMarketDataPar.csv', 'PredictDataPar.csv', 'external.csv']
for i_params in range(len(param_file_list)):
param_file = param_file_list[i_params]
param_file = os.path.join(param_path, param_file)
data_add = pd.read_csv(param_file, encoding='gbk')
if i_params == 0:
data = data_add
else:
data = pd.concat([data, data_add], axis=0)
try:
data = data[['FACTOR', 'PATH']]
data.index = data.FACTOR
primary_path = data.ix[factor_name, "PATH"]
except:
primary_path = "InputData\WindData\DailyReportData"
param_path = Parameter().get_read_file("Factor_Mfc")
primary_factor_file = os.path.join(param_path, primary_path, factor_name + '.h5')
return primary_factor_file
def load_factor_return(self):
print('loading barra factor return......')
in_file = Parameter().get_load_in_file(self.factor_return_name)
data = pd.read_csv(in_file,
index_col=[0],
parse_dates=[0],
encoding='gbk')
output_file = Parameter().get_load_out_file(self.factor_return_name)
data.to_csv(output_file)
def load_stock_residual(self):
in_path = Parameter().get_load_in_file(self.stock_residual_name)
dirpath = os.listdir(in_path)
output_path = Parameter().get_load_out_file(self.stock_residual_name)
output_dirpath = os.listdir(output_path)
mydirpath = list(set(dirpath) - set(output_dirpath))
for i_file in range(len(mydirpath)):
file = mydirpath[i_file]
print('loading barra stock residual ' + file + '......')
data = pd.read_csv(in_path + file, index_col=[0], encoding='gbk')
data.to_csv(output_path + file)
def get_factor_return(self, beg_date=None, end_date=None, type_list=None):
if beg_date is None:
beg_date = "20000101"
if end_date is None:
end_date = datetime.today().strftime("%Y%m%d")
if type_list is None:
type_list = ["STYLE"]
filename = Parameter().get_read_file(self.factor_return_name)
barra_factor_return = pd.read_csv(filename,
index_col=[0],
parse_dates=[0],
encoding='gbk')
barra_factor_return.index = barra_factor_return.index.map(
Date().change_to_str)
name = self.get_factor_name(type_list=type_list)
barra_factor_name = name['NAME_EN'].values
barra_factor_return = barra_factor_return[barra_factor_name]
barra_factor_return = barra_factor_return[~barra_factor_return.index.
duplicated()]
beg_date = Date().change_to_str(beg_date)
end_date = Date().change_to_str(end_date)
barra_factor_return = barra_factor_return.ix[beg_date:end_date, :]
return barra_factor_return
def get_mfc_code(self, fund_name):
path = Parameter().get_read_file("Mfc_Fund")
file = os.path.join(path, "Fund_Info.xlsx")
data = pd.read_excel(file)
name = data[data['Name'] == fund_name].values[0][0]
return name
def load_fund_info(self):
data = FinDb().load_raw_data("Fund_Basic_Info")
out_file = Parameter().get_load_findb_out_file("Fund_Basic_Info")
data['基金代码'] = data['基金代码'].map(fund_code_add_postfix)
print(" Loading Fund Basic InFo " + out_file)
data.to_csv(out_file)
def load_sec_info(self, pool_name=101):
data = FinDb().load_raw_data_filter("Sec_Basic_Info", pool_name)
data['证券代码'] = data['证券代码'].map(stock_code_add_postfix)
out_file = Parameter().get_load_findb_out_file("Sec_Basic_Info")
print(" Loading Security Basic InFo " + out_file)
data.to_csv(out_file)
def get_trade_statement(self, date):
date_int = Date().change_to_str(date)
path = Parameter().get_read_file(self.name)
file = os.path.join(path, '3_成交回报', '成交回报_' + str(date_int) + '.csv')
data = pd.read_csv(file, encoding='gbk', thousands=',')
return data
def get_fund_nav(self, fund_name, begin_date_int, end_date_int):
path = Parameter().get_read_file(self.name)
sub_path = os.path.join(path, '2_基金资产')
date_list = self.get_file_tradedate(sub_path, begin_date_int,
end_date_int)
trade_date_list = Date().get_trade_date_series(begin_date_int,
end_date_int)
date_list = list(set(date_list) & set(trade_date_list))
date_list.sort()
cum_nav_period = pd.DataFrame([],
index=date_list,
columns=['单位净值', '基金份额', '净值'])
for i in range(len(date_list)):
date_int = date_list[i]
asset = self.get_fund_asset(date_int)
asset.index = asset['基金名称']
cum_nav_period.ix[date_int, '基金份额'] = asset.ix[fund_name, '基金份额']
cum_nav_period.ix[date_int, '净值'] = asset.ix[fund_name, '净值']
cum_nav_period[
'单位净值'] = cum_nav_period['净值'] / cum_nav_period['基金份额']
cum_nav_period = pd.DataFrame(cum_nav_period['单位净值'].values,
index=cum_nav_period.index,
columns=['单位净值'])
return cum_nav_period
def get_fund_asset(self, date):
date_int = Date().change_to_str(date)
path = Parameter().get_read_file(self.name)
file = os.path.join(path, '2_基金资产', '基金资产_' + str(date_int) + '.csv')
data = pd.read_csv(file, encoding='gbk', thousands=',')
return data
def get_group_security(self, date):
date_int = Date().change_to_str(date)
path = Parameter().get_read_file(self.name)
file = os.path.join(path, '6_组合证券', '组合证券_' + str(date_int) + '.csv')
data = pd.read_csv(file, encoding='gbk', thousands=',')
return data
def cal_stock_riskfactor_return_daily(self, beg_date, end_date):
"""
计算股票每一日 在行业 风格 上的收益 = 当日在风格、行业上的暴露 * 当日风格、行业的因子收益率
"""
factor_return = self.get_factor_return(
beg_date, end_date, type_list=['COUNTRY', 'STYLE', 'INDUSTRY'])
path = Parameter().get_read_file("Barra_Stock_Factor_Return")
for i_date in range(len(factor_return)):
date = factor_return.index[i_date]
exposure = self.get_factor_exposure_date(
date, type_list=['COUNTRY', 'STYLE', 'INDUSTRY'])
if exposure is not None:
factor_return_date = factor_return.ix[date, :]
factor_return_mat = np.tile(factor_return_date.values,
(len(exposure), 1))
factor_return_mat = pd.DataFrame(factor_return_mat,
index=exposure.index,
columns=factor_return.columns)
stock_factor_return = factor_return_mat.mul(exposure)
print("Cal Stock Riskfactor Return Daily is %s" % date)
file = os.path.join(
path, "Barra_Stock_Factor_Return_" + date + '.csv')
stock_factor_return.to_csv(file)
def get_alpha_factor_mfc_file(self, factor_name="Beta"):
# 读取 alpha data 路径
#############################################################################
param_path = Parameter().get_read_file("Alpha_Factor_Mfc")
alpha_factor_file = os.path.join(param_path, factor_name + '.h5')
return alpha_factor_file
def get_fund_regression_exposure(self, fund):
out_path = Parameter().get_read_file(self.regression_exposure_name)
out_file = os.path.join(out_path,
'Fund_Regression_Exposure_' + fund + '.csv')
exposure = pd.read_csv(out_file, index_col=[0], encoding='gbk')
exposure.index = exposure.index.map(str)
return exposure
def weight_allstock_holding_date(report_date):
report_date = Date().change_to_str(report_date)
data = Fund().get_fund_holding_report_date(report_date)
data = data[['FundCode', 'Weight', 'StockCode']]
pool = Fund().get_fund_pool_code(report_date, "基金持仓基准基金池")
fund_code = list(set(pool))
fund_code.sort()
weight = Fund().get_wind_fund_asset(report_date)
for i_fund in range(len(fund_code)):
fund = fund_code[i_fund]
data_fund = data[data['FundCode'] == fund]
data_fund = data_fund.dropna(subset=['Weight'])
data_fund = data_fund.sort_values(by=['Weight'], ascending=False)
try:
asset = weight.ix[fund, report_date]
asset /= 100000000
except:
asset = 1.0
if i_fund == 0:
data_fund_all = data_fund.copy()
data_fund_all["Asset_Weight"] = data_fund_all['Weight'] * asset
all_weight = data_fund_all['Weight'].sum()
if all_weight < 60:
data_fund_all = pd.DataFrame([], columns=data_fund.columns)
else:
data_fund_all_add = data_fund.copy()
data_fund_all_add["Asset_Weight"] = data_fund_all_add['Weight'] * asset
all_weight = data_fund_all_add['Weight'].sum()
if all_weight < 60:
data_fund_all_add = pd.DataFrame([], columns=data_fund.columns)
data_fund_all = pd.concat([data_fund_all, data_fund_all_add], axis=0)
stock_code = list(set(data_fund_all['StockCode'].values))
stock_code.sort()
weight_sum = data_fund_all['Asset_Weight'].sum()
weight_code = pd.DataFrame([], index=stock_code, columns=['Asset_Weight'])
for i_stock in range(len(stock_code)):
stock = stock_code[i_stock]
data_stock = data_fund_all[data_fund_all['StockCode'] == stock]
stock_weight_sum = data_stock['Asset_Weight'].sum()
weight_code.ix[stock, 'Asset_Weight'] = stock_weight_sum / weight_sum
weight_code.index = weight_code.index.map(lambda x: x[0:6] + '-CN')
out_path = Parameter().get_read_file("Fund_Stock_Holding_BenchMark")
out_path = os.path.join(out_path, "weight_halfyear_all")
if not os.path.exists(out_path):
os.makedirs(out_path)
out_file = os.path.join(out_path, "weight_halfyear_all_" + report_date + '.csv')
print(out_file)
weight_code.to_csv(out_file, header=None)
def get_factor_name(self, type_list=None):
if type_list is None:
type_list = ["STYLE"]
filename = Parameter().get_read_file(self.barra_name)
data = pd.read_excel(filename, encoding='gbk')
data = data[data['TYPE'].map(lambda x: x in type_list)]
return data
def get_fund_factor(self, factor_name, date_list=None, fund_pool=None):
out_file = Parameter().get_read_file(factor_name)
data = pd.read_csv(out_file, index_col=[0], encoding='gbk')
data.index = data.index.map(str)
if date_list is not None:
data = data.ix[date_list, :]
if fund_pool is not None:
data = data.ix[:, fund_pool]
return data
def get_stock_riskfactor_return_date(self, date):
date = Date().change_to_str(date)
path = Parameter().get_read_file("Barra_Stock_Factor_Return")
file = os.path.join(path, "Barra_Stock_Factor_Return_" + date + '.csv')
if os.path.exists(file):
stock_factor_return = pd.read_csv(file,
index_col=[0],
encoding='gbk')
else:
stock_factor_return = None
return stock_factor_return
def get_fund_holding_all(self):
file = Parameter().get_read_file(self.holder_name)
fund_holding = pd.read_csv(file, encoding='gbk')
fund_holding = fund_holding[['基金代码', '股票代码', '占净值比', '截至日期']]
fund_holding.columns = ['FundCode', 'StockCode', 'Weight', 'Date']
fund_holding['Date'] = fund_holding['Date'].map(np.str)
fund_holding = fund_holding.dropna()
fund_holding = fund_holding.reset_index(drop=True)
return fund_holding
def get_wind_fund_asset(self, date=None):
if date is None:
date = Date().get_normal_date_series(period='Q')[-2]
print(date)
date = Date.change_to_str(date)
out_path = Parameter().get_read_file("Fund_Asset")
out_file = "基金规模_" + date + '.csv'
out_file = os.path.join(out_path, out_file)
data = pd.read_csv(out_file, encoding='gbk', index_col=[0])
return data
def get_fund_holder_exposure(self, fund, type_list=["STYLE"]):
out_path = Parameter().get_read_file(self.holder_exposure_name)
out_file = os.path.join(out_path,
'Fund_Holder_Exposure_' + fund + '.csv')
exposure = pd.read_csv(out_file, index_col=[0], encoding='gbk')
exposure.index = exposure.index.map(str)
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
exposure = exposure[factor_name]
return exposure
def load_raw_data(self, factor_name):
table_name, field_en, filter_field, field_ch = Parameter(
).get_load_findb_param(factor_name)
field_en_str = ','.join(field_en)
self.connect()
self.cursor.execute('SELECT ' + field_en_str + ' FROM ' + table_name)
rows = self.cursor.fetchall()
data_df = pd.DataFrame(rows, columns=field_ch)
self.close()
return data_df
def my_main():
project_path = Parameter().get_read_file("Mfc_Fund")
out_path = Parameter().get_read_file("Mfc_Daily")
today = datetime.today().strftime("%Y%m%d")
# today = datetime(2018, 7, 3).strftime("%Y%m%d")
load_mfc_holding_data(today)
load_other_data(today)
cal_reverse_5days(today, project_path, out_path)
cal_ipo_mkt_monitor(today, project_path, out_path)
holding_data_clj(today, project_path, out_path)
holding_data_liuxin(today, project_path, out_path)
holding_data_yangchao(today, project_path, out_path)
holding_data_liuyang(today, project_path, out_path)
mail_for_clj()
mail_for_liuxin()
mail_for_yangchao()
mail_for_liuyang()
def equal_top10stock_holding_date(report_date):
report_date = Date().change_to_str(report_date)
data = Fund().get_fund_holding_report_date(report_date)
data = data[['FundCode', 'Weight', 'StockCode']]
pool = Fund().get_fund_pool_code(report_date, "基金持仓基准基金池")
fund_code = list(set(pool))
fund_code.sort()
for i_fund in range(len(fund_code)):
fund = fund_code[i_fund]
data_fund = data[data['FundCode'] == fund]
data_fund = data_fund.dropna(subset=['Weight'])
data_fund = data_fund.sort_values(by=['Weight'], ascending=False)
if i_fund == 0:
data_fund_top10 = data_fund.iloc[:10, :]
top10_weight = data_fund_top10['Weight'].sum()
if top10_weight < 30:
data_fund_top10 = pd.DataFrame([], columns=data_fund.columns)
else:
data_fund_top10_add = data_fund.iloc[:10, :]
top10_weight = data_fund_top10_add['Weight'].sum()
if top10_weight < 30:
data_fund_top10_add = pd.DataFrame([],
columns=data_fund.columns)
data_fund_top10 = pd.concat([data_fund_top10, data_fund_top10_add],
axis=0)
stock_code = list(set(data_fund_top10['StockCode'].values))
stock_code.sort()
weight_sum = data_fund_top10['Weight'].sum()
weight_code = pd.DataFrame([], index=stock_code, columns=['Weight'])
for i_stock in range(len(stock_code)):
stock = stock_code[i_stock]
data_stock = data_fund_top10[data_fund_top10['StockCode'] == stock]
stock_weight_sum = data_stock['Weight'].sum()
weight_code.ix[stock, 'Weight'] = stock_weight_sum / weight_sum
weight_code.index = weight_code.index.map(lambda x: x[0:6] + '-CN')
out_path = Parameter().get_read_file("Fund_Stock_Holding_BenchMark")
out_path = os.path.join(out_path, "equal_quarter_top10")
if not os.path.exists(out_path):
os.makedirs(out_path)
out_file = os.path.join(out_path,
"equal_quarter_top10_" + report_date + '.csv')
print(out_file)
weight_code.to_csv(out_file, header=None)
def change_holding_date(self, date):
date_before = Date().get_trade_date_offset(date, -1)
local_path = Parameter().get_load_out_file(self.name)
local_path = os.path.join(local_path, 'raw_file')
date_int = Date().change_to_str(date)
date_before = Date().change_to_str(date_before)
before_path = os.path.join(local_path, date_int)
after_path = Parameter().get_read_file(self.name)
change_file_dick = {
'01HIS成交回报.xls': "3_成交回报\\成交回报_",
'01TODAY单元资产.xls': "4_单元资产\\单元资产_",
'01TODAY基金证券.xls': "5_基金证券\\基金证券_",
'01TODAY基金资产.xls': "2_基金资产\\基金资产_",
'01TODAY组合证券.xls': "6_组合证券\\组合证券_"
}
print(
"######################################### Change Ftp File #########################################"
)
for b_file, a_file in change_file_dick.items():
before_file = os.path.join(before_path, b_file)
if os.path.exists(before_file):
data = pd.read_excel(before_file,
index_col=[0],
encoding='gbk')
after_file = after_path + a_file + date_before + '.csv'
print("Change File into ", after_file)
data.to_csv(after_file)
else:
print("Tne File At ", date_int, " is No Exist. ")
def get_fund_nav_adjust(self, fund_name, begin_date_int, end_date_int):
file = '\\\\10.1.9.208\\fe\\fe_public\\业绩归因\\input\\dividend.csv'
did_file = pd.read_csv(file, index_col=[0], encoding='gbk')
did_file['DATETIME'] = did_file['DATETIME'].map(str)
did_data = did_file[did_file.index == fund_name]
path = Parameter().get_read_file(self.name)
sub_path = os.path.join(path, '2_基金资产')
date_list = self.get_file_tradedate(sub_path, begin_date_int,
end_date_int)
trade_date_list = Date().get_trade_date_series(begin_date_int,
end_date_int)
date_list = list(set(date_list) & set(trade_date_list))
date_list.sort()
cum_nav_period = pd.DataFrame([],
index=date_list,
columns=['单位净值', '基金份额', '净值'])
for i in range(len(date_list)):
date_int = date_list[i]
asset = self.get_fund_asset(date_int)
asset.index = asset['基金名称']
cum_nav_period.ix[date_int, '基金份额'] = asset.ix[fund_name, '基金份额']
cum_nav_period.ix[date_int, '净值'] = asset.ix[fund_name, '净值']
cum_nav_period[
'单位净值'] = cum_nav_period['净值'] / cum_nav_period['基金份额']
did_date = pd.DataFrame(did_data['DIVD_ASSET'].values,
index=did_data['DATETIME'],
columns=['分红资产'])
all_data = pd.concat([cum_nav_period, did_date], axis=1)
all_data['分红资产'] = all_data['分红资产'].fillna(0.0)
all_data['分红净值'] = all_data['分红资产'] / all_data['基金份额']
all_data['累计分红净值'] = all_data['分红净值'].cumsum()
all_data['累计净值'] = all_data['累计分红净值'] + all_data['单位净值']
all_data['累计净值涨跌幅'] = all_data['累计净值'].pct_change()
all_data['累计复权分红净值'] = (all_data['累计分红净值'] *
all_data['累计净值涨跌幅']).cumsum()
all_data['累计复权净值'] = all_data['累计净值'] + all_data['累计复权分红净值']
result = pd.DataFrame(all_data['累计复权净值'].values,
index=all_data.index,
columns=['累计复权净值'])
return result
def load_raw_data_filter_period(self, factor_name, beg_val, end_val):
table_name, field_en, filter_field, field_ch = Parameter(
).get_load_findb_param(factor_name)
self.connect()
field_en_str = ','.join(field_en)
print(table_name, field_en, filter_field, field_ch)
self.cursor.execute('SELECT ' + field_en_str + ' FROM ' + table_name +
' WHERE ' + filter_field + '<' + end_val +
" AND " + filter_field + '>' + beg_val)
rows = self.cursor.fetchall()
data_df = pd.DataFrame(rows, columns=field_ch)
self.close()
return data_df