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