Esempio n. 1
0
    def save_file_asset_daily(self, data, name, cal_type, date):
        """ 资产收益(股票、新股、期货)计算过程文件存储 """

        sub_path = os.path.join(self.data_path, self.fund_name, "资产收益", name)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        file = "%s收益分解_%s_%s_%s.xlsx" % (name, cal_type, self.fund_name, date)
        file_name = os.path.join(sub_path, file)

        num_format_pd = pd.DataFrame([],
                                     columns=data.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0.00'

        excel = WriteExcel(file_name)
        worksheet = excel.add_worksheet(self.fund_name)
        excel.write_pandas(data,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=0,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)
        excel.close()
Esempio n. 2
0
def rank_all_manager(end_date):

    """ 所有基金经理业绩 """

    path = r'E:\Data\mfcteda_data\基金经理'
    param_file = os.path.join(path, '泰达宏利基金经理排名.xlsx')
    data = pd.read_excel(param_file, index_col=[0])
    data = data.iloc[0:19, :]
    performance = pd.DataFrame()

    for i in range(len(data)):
        index = data.index[i]
        manager = data.loc[index, "基金经理"]
        mage_date = str(int(data.loc[index, "管理开始日"]))
        fund_name = data.loc[index, "名称"]
        fund_code = data.loc[index, "代码"]
        rank_pool = data.loc[index, '公司分类']
        print(manager, fund_name)
        rank_percent, rank_str = rank_fund_manager(end_date, fund_code, rank_pool, fund_name, mage_date)
        performance = pd.concat([performance, rank_percent], axis=0)

    file = os.path.join(path, '泰达宏利基金经理排名%s.xlsx' % end_date)
    we = WriteExcel(file)

    # write pandas
    sheet_name = "泰达宏利基金经理排名百分比"
    num_format_pd = pd.DataFrame([], columns=performance.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    worksheet = we.add_worksheet(sheet_name)
    we.write_pandas(performance, worksheet, begin_row_number=0, begin_col_number=1,
                    num_format_pd=num_format_pd, color="blue", fillna=True)
    we.conditional_format(worksheet, 0, 1, len(performance), len(performance.columns) + 1,
                          reverse=True)
    we.close()
Esempio n. 3
0
    def get_all_fund_index(self, beg_date, end_date):

        """ 得到所有基金的得分数据汇总 """

        file = os.path.join(self.data_path, "基金经理考核范围.xlsx")
        all_data = pd.read_excel(file, index_col=[0])
        all_data.index = all_data['代码']
        result = pd.DataFrame([])

        for i_fund in range(0, len(all_data)):

            my_fund_code = all_data.index[i_fund]
            fund_pool_name = all_data.loc[my_fund_code, "考核分类"]
            fund_type = all_data.loc[my_fund_code, "类型2"]
            add_data = self.get_fund_index(fund_pool_name, my_fund_code, beg_date, end_date, fund_type)
            result = pd.concat([result, add_data], axis=0)

        result = pd.concat([all_data, result], axis=1)
        result = result.drop(labels=['代码', '考核标准', '收益名次', '仓位'], axis=1)
        file = os.path.join(self.data_path, "基金得分_%s_%s.xlsx" % (beg_date, end_date))

        excel = WriteExcel(file)
        num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        num_format_pd.loc['format', ['信息比率']] = '0.00'
        num_format_pd.loc['format', ['现任经理管理开始日']] = '0'

        sheet_name = "%s_%s" % (beg_date, end_date)
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="red", fillna=True)
Esempio n. 4
0
    def generate_excel_history(self, end_date):
        """ 大股东增减持历史信息 2017年至今 """

        # 一段时间内增减持额时间序列
        from quant.stock.index import Index
        index_data = Index().get_index_factor(index_code="000300.SH")

        date_series = Date().get_trade_date_series("20150101", end_date, "M")
        result = pd.DataFrame([])

        ed_date = end_date

        for i in range(len(date_series) - 1):
            bg_date = Date().get_trade_date_offset(ed_date, -20)
            print("Major Holder Deal %s %s" % (bg_date, ed_date))
            result_add = self.major_holder_deal_period_sum(bg_date, ed_date)
            result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
            result = pd.concat([result, result_add], axis=0)
            ed_date = bg_date

        result = result.sort_index()

        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        filename = os.path.join(sub_path, '重要股东二级市场交易历史.xlsx')
        excel = WriteExcel(filename)
        sheet_name = "读写EXCEL测试"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00'
        excel.write_pandas(result,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="orange",
                           fillna=True)
        excel.chart_columns_plot(worksheet,
                                 sheet_name=sheet_name,
                                 series_name=["增减持金额(亿)", "沪深300"],
                                 chart_name="历史每月大股东增减持总和",
                                 insert_pos="O2",
                                 cat_beg="D2",
                                 cat_end="D40",
                                 val_beg_list=["E2", "G2"],
                                 val_end_list=["E40", "G40"])
        excel.close()
Esempio n. 5
0
    def cal_summary(self, all_beg_date=None, all_end_date=None):
        """
        计算组合在每年的 收益率、波动率等表现情况
        """

        self.get_port_return()
        all_port_return = self.port_return
        all_port_return["Year"] = all_port_return.index.map(lambda x: x[0:4])
        if all_beg_date is None:
            all_beg_date = all_port_return.index[0]
        if all_end_date is None:
            all_end_date = all_port_return.index[-1]
        year_list = list(set(all_port_return["Year"]))
        year_list.sort()

        for i_year in range(len(year_list)):

            year = str(year_list[i_year])
            port_return = all_port_return.loc[all_port_return["Year"] ==
                                              year, :]
            beg_date = port_return.index[0]
            end_date = port_return.index[-1]

            if i_year == 0:
                data = self.cal_summary_period(beg_date, end_date)
            else:
                data_add = self.cal_summary_period(beg_date, end_date)
                data = pd.concat([data, data_add], axis=1)

        data_add = self.cal_summary_period(all_beg_date, all_end_date)
        data_add.columns = ['All']
        data = pd.concat([data, data_add], axis=1)
        data = data.T
        sub_path = os.path.join(self.save_path, self.port_name)
        file = os.path.join(sub_path, self.port_name + '_Summary.xlsx')

        excel = WriteExcel(file)
        worksheet = excel.add_worksheet("Summary")
        num_format_pd = pd.DataFrame([],
                                     columns=data.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        excel.write_pandas(data,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)

        excel.close()
Esempio n. 6
0
    def check_alpha_factor_update_date(self):
        """ 检查所有Alpha因子最后更新时间 """

        factor_name_list = AlphaFactor().get_all_alpha_factor_name()
        result = pd.DataFrame([],
                              columns=['开始日期', '结束日期'],
                              index=factor_name_list)

        for i in range(0, len(factor_name_list)):

            factor_name = factor_name_list[i]
            try:
                print("######### 检查更新日期 %s 数据 ############" % factor_name)
                factor = AlphaFactor().get_alpha_factor_exposure(factor_name)
                factor = factor.T.dropna(how='all').T
                result.loc[factor_name, '开始日期'] = factor.columns[0]
                result.loc[factor_name, '结束日期'] = factor.columns[-1]
                result.loc[factor_name,
                           "最后一天有效数据个数"] = factor.iloc[:, -1].count()
                result.loc[factor_name, "最后一天股票个数"] = len(factor.iloc[:, -1])
                result.loc[factor_name,
                           "最后一天有效数据比率"] = factor.iloc[:, -1].count() / len(
                               factor.iloc[:, -1])
            except Exception as e:
                print(e)
                result.loc[factor_name, '开始日期'] = ""
                result.loc[factor_name, '结束日期'] = ""
                result.loc[factor_name, "最后一天有效数据个数"] = ""
                result.loc[factor_name, "最后一天股票个数"] = ""
                result.loc[factor_name, "最后一天有效数据比率"] = ""
                print("########### %s 检查更新数据 为空 !!!###########" % factor_name)

        out_file = os.path.join(self.data_path, "AlphaFactorUpdateDate.xlsx")
        we = WriteExcel(out_file)
        ws = we.add_worksheet("更新数据")

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0'
        num_format_pd.loc['format', ['最后一天有效数据比率']] = '0.00%'
        we.write_pandas(result,
                        ws,
                        begin_row_number=0,
                        begin_col_number=1,
                        num_format_pd=num_format_pd,
                        color="blue",
                        fillna=True)

        we.close()
Esempio n. 7
0
    def generate_excel(self, end_date):

        """ 陆股通信息 输出到Excel """

        # beg_date = Date().get_trade_date_offset(end_date, -60)
        # beg_1m_date = Date().get_trade_date_offset(end_date, -20)

        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        # 信息写入文件
        filename = os.path.join(sub_path, '股票基金风格仓位.xlsx')
        excel = WriteExcel(filename)

        ###################################################################################
        sheet_name = "普通股票型风格仓位"
        worksheet = excel.add_worksheet(sheet_name)

        date_1m = Date().get_trade_date_offset(end_date, -21)
        style_series, position_series = self.get_fund_style_position("885000.WI", date_1m, end_date)

        num_format_pd = pd.DataFrame([], columns=style_series.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        excel.write_pandas(style_series, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        num_format_pd = pd.DataFrame([], columns=position_series.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        excel.write_pandas(position_series, worksheet, begin_row_number=0, begin_col_number=12,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        ###################################################################################
        sheet_name = "偏股混合型风格仓位"
        worksheet = excel.add_worksheet(sheet_name)
        style_series, position_series = self.get_fund_style_position("885001.WI", date_1m, end_date)

        num_format_pd = pd.DataFrame([], columns=style_series.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        excel.write_pandas(style_series, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        num_format_pd = pd.DataFrame([], columns=position_series.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        excel.write_pandas(position_series, worksheet, begin_row_number=0, begin_col_number=12,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        excel.close()
Esempio n. 8
0
    def generate_excel(self, end_date):

        """ 大股东增减持信息 输出到Excel """

        beg_date = "20050101"

        # 一段时间内市值占比时间序列
        result, ratio, ratio_free = self.get_all_data(beg_date, end_date)

        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        # 信息写入文件
        filename = os.path.join(sub_path, '市场整体占比.xlsx')
        excel = WriteExcel(filename)
        sheet_name = "市场整体占比"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
        num_format_pd.ix['format', :] = '0.00'
        excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="red", fillna=True)

        num_format_pd = pd.DataFrame([], columns=ratio.columns, index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        excel.write_pandas(ratio, worksheet, begin_row_number=0, begin_col_number=15,
                           num_format_pd=num_format_pd, color="red", fillna=True)

        num_format_pd = pd.DataFrame([], columns=ratio_free.columns, index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        excel.write_pandas(ratio_free, worksheet, begin_row_number=0, begin_col_number=30,
                           num_format_pd=num_format_pd, color="red", fillna=True)

        summary = pd.concat([result.T[result.index[-1]], ratio.T[ratio.index[-1]],
                             ratio_free.T[ratio_free.index[-1]]], axis=1)
        summary.columns = ["市值", 'A股总市值占比', 'A股流通市值占比']
        num_format_pd = pd.DataFrame([], columns=summary.columns, index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.ix['format', ['市值']] = '0.00'
        excel.write_pandas(summary, worksheet, begin_row_number=0, begin_col_number=46,
                           num_format_pd=num_format_pd, color="red", fillna=True)

        excel.close()
Esempio n. 9
0
    def generate_excel_history(self, end_date):
        """ 陆股通信息 输出到Excel """

        # 一段时间内增减持额时间序列
        from quant.stock.index import Index
        index_data = Index().get_index_factor(index_code="000300.SH")
        date_series = Date().get_trade_date_series("20170301", end_date, "M")
        result = pd.DataFrame([])

        ed_date = end_date
        for i in range(len(date_series) - 1):
            bg_date = Date().get_trade_date_offset(ed_date, -20)
            print("Hk Inflow Period %s %s" % (bg_date, ed_date))
            result_add = self.hk_inflow_period(bg_date, ed_date)
            result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
            result = pd.concat([result, result_add], axis=0)
            ed_date = bg_date

        result = result.sort_index()
        result['累计净流入(亿元)'] = result['期间净流入(亿元)'].cumsum()
        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        # 信息写入文件
        filename = os.path.join(sub_path, '陆股通北上资金历史.xlsx')
        excel = WriteExcel(filename)
        sheet_name = "陆股通北上资金"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(result,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="orange",
                           fillna=True)
        excel.close()
Esempio n. 10
0
    def generate_excel_history(self, end_date):
        """  历史ETF净申购 和沪深300"""

        # 一段时间内增减持额时间序列
        date_series = Date().get_trade_date_series("20080101", end_date, "M")
        result_all = pd.DataFrame([])

        from quant.stock.index import Index
        index_data = Index().get_index_factor(index_code="000300.SH")

        ed_date = end_date
        for i in range(len(date_series) - 1):
            bg_date = Date().get_trade_date_offset(ed_date, -20)
            print("ETF Fund %s %s" % (bg_date, ed_date))
            result_add = self.get_etf_data_period_sum(bg_date, ed_date)
            result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
            result_all = pd.concat([result_all, result_add], axis=0)
            ed_date = bg_date

        result_all = result_all.sort_index()

        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        filename = os.path.join(sub_path, 'ETF流入历史.xlsx')
        excel = WriteExcel(filename)
        sheet_name = "ETF流入"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([],
                                     columns=result_all.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(result_all,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=20,
                           num_format_pd=num_format_pd,
                           color="orange",
                           fillna=True)
        excel.close()
Esempio n. 11
0
    def save_file_excel(self, new_data, type, name, num_format):
        """
        将一段时间内的每日拆分写入文件(增量写入)
        将一段时间内的每日暴露写入文件(增量写入)
        """

        if len(new_data) > 0:

            save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
            if not os.path.exists(save_path):
                os.makedirs(save_path)

            file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
            file_name = os.path.join(save_path, file)
            print("写入", file_name)

            if os.path.exists(file_name):
                old_data = pd.read_excel(file_name, index_col=[0])
                old_data.index = old_data.index.map(str)
                data = FactorOperate().pandas_add_row(old_data, new_data)
            else:
                data = new_data

            num_format_pd = pd.DataFrame([],
                                         columns=data.columns,
                                         index=['format'])
            num_format_pd.loc['format', :] = num_format

            excel = WriteExcel(file_name)
            worksheet = excel.add_worksheet(self.fund_name)
            excel.write_pandas(data,
                               worksheet,
                               begin_row_number=0,
                               begin_col_number=0,
                               num_format_pd=num_format_pd,
                               color="red",
                               fillna=True)
            excel.close()
        else:
            save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
            file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
            file_name = os.path.join(save_path, file)
            print("写入数据为0", file_name)
Esempio n. 12
0
    def load_ipo_data(self, beg_date):

        """ 下载IPO数据 上市日期 发行价 中签率 申购上限 等等"""

        data = self.get_new_stock_list(beg_date)
        code_str = ','.join(data.index.values)

        data = w.wss(code_str,
                     "sec_name,ipo_date,ipo_price,ipo_cashratio,ipo_lotteryrate_abc,ipo_otc_cash_pct,ipo_op_uplimit",
                     "instituteType=1")

        data_pd = pd.DataFrame(data.Data, index=data.Fields, columns=data.Codes).T
        data_pd["IPO_DATE"] = data_pd["IPO_DATE"].map(lambda x: x.strftime('%Y-%m-%d'))
        data_pd.columns = ['股票名称', '上市日期', '发行价格', '网上中签率(%)',
                           '网下A类中签率(%)', '网下总计中签率(%)', '申购上限数量(万股)']
        data_pd['申购上限金额(万元)'] = data_pd["申购上限数量(万股)"] * data_pd['发行价格']

        data_pd = data_pd.dropna()
        data_pd = data_pd.sort_values(by=['上市日期'], ascending=True)

        for i_code in range(0, len(data_pd)):

            code = data_pd.index.values[i_code]
            ipo_date = data_pd.ix[i_code, '上市日期']
            open_date, open_pct, open_price = self.get_open_date_pct(code, ipo_date)
            data_pd.ix[i_code, '开板日期'] = open_date
            data_pd.ix[i_code, '开板价格'] = open_price
            data_pd.ix[i_code, '开板收益'] = open_pct

        print(data_pd)
        file = os.path.join(self.data_path, 'ipo_data.xlsx')
        data = pd.read_excel(file, index_col=[1])
        data = data.T.dropna(how='all').T

        concat_data = FactorOperate().pandas_add_row(data, data_pd)
        concat_data = concat_data.sort_values(by=['上市日期'], ascending=True)
        excel = WriteExcel(file)
        worksheet = excel.add_worksheet("新股检测")
        excel.write_pandas(concat_data, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=None, color="orange", fillna=True)
        excel.close()
Esempio n. 13
0
    def plan(self, benchmark_array, invest_days, invest_money,
             beg_date, end_date, file_prefix):

        """ 定投计划回测 """

        data = self.get_benchmark_pct(benchmark_array)
        data = data.loc[beg_date:end_date, :]
        beg_date = data.index[0]
        end_date = data.index[-1]
        filename = "%s_%s_%s.xlsx" % (file_prefix, beg_date, end_date)

        data['星期数'] = data.index.map(lambda x: datetime.strptime(x, '%Y%m%d').strftime("%A"))
        data['当日投资金额'] = data['星期数'].map(lambda x: invest_money if x == invest_days else 0)
        data['累计投资金额'] = data['当日投资金额'].cumsum()
        data['当日投资收益'] = data['累计投资金额'] * data['投资指数日收益']
        data['累计投资收益'] = data['当日投资收益'].cumsum()
        data['累计投资收益率'] = data['累计投资收益'] / data['累计投资金额']

        file = os.path.join(self.data_path, filename)
        excel = WriteExcel(file)
        worksheet = excel.add_worksheet("定投")

        num_format_pd = pd.DataFrame([], columns=data.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', ['累计投资收益率', '投资指数日收益']] = '0.00%'
        excel.write_pandas(data, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="blue", fillna=True)

        data_select = pd.DataFrame(data['累计投资收益率'])
        num_format_pd = pd.DataFrame([], columns=data_select.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'
        excel.write_pandas(data_select, worksheet, begin_row_number=0, begin_col_number=10,
                           num_format_pd=num_format_pd, color="red", fillna=True)

        excel.line_chart_time_series_plot(worksheet, 0, 10, data_select,
                                          ["累计投资收益率"], file_prefix + '累计投资收益率', "K2", "定投")

        excel.close()
Esempio n. 14
0
    def filter_fund_pool(self, index_code, index_name, end_date,
                         track_error_up):
        """ 得到沪深300 、中证500基金池 """

        # 参数
        # end_date = "20181231"
        # index_name = '沪深300'
        # index_code = '000300.SH'
        # track_error_up = 0.03
        beg_date = Date().get_trade_date_offset(end_date, -250)

        # 读取数据
        fund_nav = Fund().get_fund_factor("Repair_Nav")
        index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
        index_close.columns = [index_code]
        result = pd.DataFrame([],
                              index=fund_nav.columns,
                              columns=['跟踪误差', '数据长度'])

        # 计算最近1年跟踪误差数据
        fund_nav = fund_nav.loc[index_close.index, :]
        fund_pct = fund_nav.pct_change()
        index_pct = index_close.pct_change()
        index_pct = index_pct[index_code]
        fund_excess_pct = fund_pct.sub(index_pct, axis='index')
        fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
        result.loc[:, "数据长度"] = fund_excess_pct_period.count()
        result.loc[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)

        # 筛选
        result = result.dropna()
        result = result[result['数据长度'] > self.data_min_length]
        result = result[result['跟踪误差'] < track_error_up]

        # concat fund basic info
        data_pd = Fund().get_wind_fund_info()
        data_pd = data_pd[[
            'BenchMark', 'Name', 'FullName', 'SetupDate', 'InvestType'
        ]]
        data_pd.columns = ['基金基准', '基金简称', '基金全称', '上市日期', '基金类型']
        data = pd.concat([data_pd, result], axis=1)
        data = data.dropna()
        data = data[data["基金基准"].map(lambda x: index_name in x)]
        data = data[data["上市日期"] < beg_date]
        data = data[data["基金全称"].map(lambda x: "交易型开放式指数" not in x)]
        data = data[data["基金全称"].map(lambda x: "联接" not in x)]
        data['A类基金'] = data['基金简称'].map(Fund().if_a_fund)
        data = data[data['A类基金'] == 'A类基金']

        # 输出结果
        out_path = os.path.join(self.data_path, "filter_fund_pool")
        file_name = os.path.join(out_path, '基金最终筛选池_' + index_name + '.xlsx')

        sheet_name = "基金筛选池"
        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'

        excel = WriteExcel(file_name)
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(data,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)
Esempio n. 15
0
    def write_excel(self):
        """ 写入Excel """

        # cal need data
        fund_basic_info = self.get_fund_basic_info()
        update_date = self.get_update_date()
        asset_allocation = self.get_fund_asset_allocation()
        top10_stock = self.get_fund_top10_stock()
        industry_allocation = self.get_fund_industry_allocation()
        fund_strategy_info = self.get_fund_strategy()
        last_performance = self.get_fund_last_performance()
        characteristic = self.get_stock_characteristic()

        # write xlsx
        sub_path = os.path.join(self.data_path, self.last_trade_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        xlsx_file = os.path.join(sub_path,
                                 '%s_%s.xlsx' % (self.prefix, self.fund_name))
        excel = WriteExcel(xlsx_file)
        worksheet = excel.add_worksheet(self.fund_name)

        # update date
        num_format_pd = pd.DataFrame([],
                                     columns=update_date.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = ''
        excel.write_pandas(update_date,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 15])

        # fund_basic_info
        num_format_pd = pd.DataFrame([],
                                     columns=fund_basic_info.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = ''
        excel.write_pandas(fund_basic_info,
                           worksheet,
                           begin_row_number=5,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 15])

        # fund_strategy_info
        num_format_pd = pd.DataFrame([],
                                     columns=fund_strategy_info.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = ''
        excel.write_pandas(fund_strategy_info,
                           worksheet,
                           begin_row_number=12,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 15])
        blank = '     '
        excel.insert_merge_range(worksheet, 13, 2, 13, 9,
                                 self.asset_allocation_strategy)
        excel.insert_merge_range(worksheet, 14, 2, 14, 9, self.fund_strategy)
        excel.insert_merge_range(worksheet, 15, 2, 15, 18, self.outlook)
        excel.rewtite_cell_format(worksheet, 13, 2,
                                  blank + self.asset_allocation_strategy, "",
                                  "left")
        excel.rewtite_cell_format(worksheet, 14, 2, blank + self.fund_strategy,
                                  "", "left")
        excel.rewtite_cell_format(worksheet, 15, 2, blank + self.outlook, "",
                                  "left")

        # asset_allocation
        num_format_pd = pd.DataFrame([],
                                     columns=asset_allocation.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        excel.write_pandas(asset_allocation,
                           worksheet,
                           begin_row_number=17,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 15])

        # characteristic_size
        num_format_pd = pd.DataFrame([],
                                     columns=characteristic.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        beg_row_num = 24
        excel.write_pandas(characteristic,
                           worksheet,
                           begin_row_number=beg_row_num,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 15, 8])

        excel.insert_merge_range(worksheet, beg_row_num + 1, 1,
                                 beg_row_num + 3, 1, "市值分布")
        excel.insert_merge_range(worksheet, beg_row_num + 4, 1,
                                 beg_row_num + 6, 1, "板块分布")
        excel.insert_merge_range(worksheet, beg_row_num + 7, 1,
                                 beg_row_num + 9, 1, "估值情况")
        excel.insert_merge_range(worksheet, beg_row_num + 10, 1,
                                 beg_row_num + 14, 1, "盈利能力")
        excel.rewtite_cell_format(worksheet, beg_row_num + 7, 3,
                                  characteristic.iloc[6, 2], "0.00")
        excel.rewtite_cell_format(worksheet, beg_row_num + 8, 3,
                                  characteristic.iloc[7, 2], "0.00")

        # performance
        num_format_pd = pd.DataFrame([],
                                     columns=last_performance.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        excel.write_pandas(last_performance,
                           worksheet,
                           begin_row_number=5,
                           begin_col_number=6,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 10, 10, 10])

        # top10 multi_factor
        num_format_pd = pd.DataFrame([],
                                     columns=top10_stock.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.loc['format', '重仓股票(%s)' % self.last_trade_date] = ''
        excel.write_pandas(top10_stock,
                           worksheet,
                           begin_row_number=17,
                           begin_col_number=6,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 10, 10, 10])

        # industry_allocation
        num_format_pd = pd.DataFrame([],
                                     columns=industry_allocation.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.loc['format', '行业配置(%s)' % self.last_trade_date] = ''
        excel.write_pandas(industry_allocation,
                           worksheet,
                           begin_row_number=29,
                           begin_col_number=6,
                           num_format_pd=num_format_pd,
                           color="blue",
                           fillna=True,
                           need_index=False,
                           header_font_color="white",
                           cell_delta=0.5,
                           cell_basic=4.0,
                           cell_len_list=[15, 10, 10, 10])
        excel.close()
Esempio n. 16
0
    def fund_index_exposure(self,
                            index_code,
                            index_name,
                            end_date,
                            halfyear_date,
                            recal_exposure=False):
        """ 计算和得到 上个半年报或者年报指数和基金的Barra暴露 """

        # index_name = '沪深300'
        # index_code = "000300.SH"

        file = os.path.join(self.data_path, 'filter_fund_pool',
                            '基金最终筛选池_' + index_name + '.xlsx')
        fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')

        halfyear_trade_date = Date().get_normal_date_month_end_day(
            halfyear_date)
        index_exposure = Index().get_index_exposure_date(
            index_code, halfyear_trade_date)
        exposure_fund = pd.DataFrame()

        for i_fund in range(0, len(fund_code.index)):

            fund = fund_code.index[i_fund]
            beg_date = Date().get_trade_date_offset(end_date, -260)
            if recal_exposure:
                Fund().cal_fund_holder_exposure_halfyear(fund_code=fund,
                                                         beg_date=beg_date,
                                                         end_date=end_date)
            exposure_add = Fund().get_fund_holder_exposure_halfyear(
                fund, type_list=['STYLE'])
            try:
                exposure_add = pd.DataFrame(exposure_add.loc[halfyear_date, :])
                exposure_add.columns = [fund]
            except Exception as e:
                print(e)
                exposure_add = pd.DataFrame([], columns=[fund])

            exposure_fund = pd.concat([exposure_fund, exposure_add], axis=1)

        exposure_fund = exposure_fund.T
        exposure = pd.concat([exposure_fund, index_exposure], axis=0)
        exposure = exposure.dropna()

        file = "BARRA风格暴露_" + index_name + "_" + halfyear_date + ".xlsx"
        file_name = os.path.join(self.data_path, "exposure", index_name, file)
        sheet_name = "BARRA风格暴露"

        num_format_pd = pd.DataFrame([],
                                     columns=exposure.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00'

        excel = WriteExcel(file_name)
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(exposure,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=0,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)
Esempio n. 17
0
    def fund_score_date(self,
                        index_code,
                        index_name,
                        end_date,
                        halfyear_normal_date,
                        adjust=False):
        """ 从上述4个方面打分 """

        # 读取 基金指标
        print("Score %s %s %s" % (index_name, end_date, halfyear_normal_date))
        sub_path = os.path.join(self.data_path, 'cal_fund_factor', index_name)
        filename = os.path.join(
            sub_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')
        fund_factor = pd.read_excel(filename, index_col=[0], encoding='gbk')

        # 读取 风格暴露
        sub_path = os.path.join(self.data_path, 'exposure', index_name)
        filename = os.path.join(
            sub_path,
            'BARRA风格暴露_' + index_name + '_' + halfyear_normal_date + '.xlsx')
        exposure = pd.read_excel(filename, index_col=[0], encoding='gbk')
        exposure = exposure.dropna()

        exposure_diff = exposure - exposure.ix[index_code, :]
        exposure = exposure_diff.abs()
        exp_col = [
            '贝塔', '市净率', '市盈率', '成长', '杠杆', '流动性', '动量', '残差波动率', '市值', '非线性市值'
        ]
        exposure.columns = exp_col
        exposure_diff.columns = exp_col
        exposure_diff = exposure_diff.round(3)
        exposure_diff = exposure_diff.drop(index_code)

        fund_code_list = list(
            set(fund_factor.index) & set(exposure_diff.index))
        fund_code_list.sort()
        fund_factor = fund_factor.loc[fund_code_list, :]
        exposure_diff = exposure_diff.loc[fund_code_list, :]

        # 计算跟踪误差得分
        result = pd.DataFrame([], index=fund_factor.index)
        result.loc[:, '基金全称'] = fund_factor.loc[:, '基金全称']

        if index_name == '中证500':
            val_25 = max(0.03, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
            val_75 = min(0.08, fund_factor.loc[:, '跟踪误差'].quantile(0.80))
        else:
            val_25 = max(0.02, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
            val_75 = min(0.05, fund_factor.loc[:, '跟踪误差'].quantile(0.80))

        result.loc[:, '跟踪误差'] = fund_factor.loc[:, '跟踪误差']
        if adjust and "001733.OF" in result.index:
            result.loc["001733.OF", '跟踪误差'] -= 0.001

        result.loc[:, '跟踪误差得分'] = fund_factor.loc[:, '跟踪误差'].map(
            lambda x: self.score_quarter_reverse(x, val_25, val_75)).round(2)

        # 计算超额收益得分
        if index_name == '中证500':
            val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
            val_75 = min(0.15, fund_factor.loc[:, '超额收益'].quantile(0.80))

        else:
            val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
            val_75 = min(0.10, fund_factor.loc[:, '超额收益'].quantile(0.80))

        result.loc[:, '超额收益'] = fund_factor.loc[:, '超额收益']
        if adjust and "001733.OF" in result.index:
            result.loc["001733.OF", '超额收益'] += 0.001
        result.loc[:, '超额收益得分'] = fund_factor.loc[:, '超额收益'].map(
            lambda x: self.score_quarter(x, val_25, val_75)).round(2)

        # 计算信息比率得分
        val_25 = fund_factor.loc[:, '信息比率'].quantile(0.20)
        val_75 = fund_factor.loc[:, '信息比率'].quantile(0.80)

        result.ix[:, '信息比率'] = fund_factor.loc[:, '信息比率'].round(2)
        result.loc[:, '信息比率得分'] = fund_factor.loc[:, '信息比率'].map(
            lambda x: self.score_quarter(x, val_25, val_75)).round(2)

        # 计算风格偏露得分(十个风格 每个打1分,10个总共打10分,再乘以0.5)
        result = result.dropna(subset=["基金全称"])
        result.loc[:, '风格暴露得分'] = 0.0
        result = pd.concat([result, exposure_diff], axis=1)

        for i_col in range(len(exposure_diff.columns)):
            col = exposure_diff.columns[i_col]
            result.loc[:, '风格暴露得分'] += result.loc[:, col].map(self.score_ex)

        result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'] * 0.5
        if adjust and "001733.OF" in result.index:
            result.loc["001733.OF",
                       '风格暴露得分'] = min(5,
                                       result.loc["001733.OF", '风格暴露得分'] + 0.5)
        val_25 = result.loc[:, '风格暴露得分'].quantile(0.20)
        val_75 = result.loc[:, '风格暴露得分'].quantile(0.80)

        result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'].map(
            lambda x: self.score_quarter(x, val_25, val_75)).round(2)

        # 计算总得分(后期调整沪深300、中证500权重不一样,可以给中证500在超额收益上多一点权重)
        result['总得分'] = 0.0

        if index_name == "中证500":
            result['总得分'] += result['跟踪误差得分'] * 0.10
            result['总得分'] += result['超额收益得分'] * 0.40
            result['总得分'] += result['信息比率得分'] * 0.20
            result['总得分'] += result['风格暴露得分'] * 0.30
        else:
            result['总得分'] += result['跟踪误差得分'] * 0.10
            result['总得分'] += result['超额收益得分'] * 0.40
            result['总得分'] += result['信息比率得分'] * 0.30
            result['总得分'] += result['风格暴露得分'] * 0.20

        result = result.sort_values(by=['总得分'], ascending=False)

        col = [
            "基金全称", '总得分', '跟踪误差', '跟踪误差得分', '超额收益', '超额收益得分', '信息比率',
            '信息比率得分', '风格暴露得分'
        ]
        col.extend(exposure_diff[0:10])
        result = result[col]
        result = result.dropna()

        # 写到EXCEL表
        sub_path = os.path.join(self.data_path, 'score_fund', index_name)
        filename = os.path.join(
            sub_path, '基金得分_' + index_name + '_' + end_date + '.xlsx')

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00'
        num_format_pd.ix['format', ['跟踪误差', '超额收益']] = '0.00%'

        sheet_name = "基金得分"
        excel = WriteExcel(filename)
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(result,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=0,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)
Esempio n. 18
0
def concat_file(path, report_date, last_date):

    #########################################################################################################
    # path = 'E:\\3_Data\\4_fund_data\\8_fund_index_exposure_weekly\\'
    # report_date = '20171231'
    # last_date = '20180831'

    #########################################################################################################
    exposure_file = os.path.join(
        path, "halfyear_holding_exposure",
        'FundHalfYearExposure_' + report_date + '.csv')
    fund_halyear_exposure = pd.read_csv(exposure_file,
                                        index_col=[0],
                                        encoding='gbk')

    cols = list(fund_halyear_exposure.columns)
    quant_fund_exposure = fund_halyear_exposure[
        fund_halyear_exposure['Type'].map(lambda x: '主动量化' in x)].mean()
    quant_fund_exposure = pd.DataFrame(quant_fund_exposure,
                                       columns=['重点量化基金平均']).T
    active_fund_exposure = fund_halyear_exposure[
        fund_halyear_exposure['Type'].map(lambda x: '主动股票' in x)].mean()
    active_fund_exposure = pd.DataFrame(active_fund_exposure,
                                        columns=['重点主动基金平均']).T
    fund_halyear_exposure_add = pd.concat(
        [quant_fund_exposure, active_fund_exposure, fund_halyear_exposure],
        axis=0)
    fund_halyear_exposure_add['Type'] = fund_halyear_exposure_add[
        'Type'].fillna('基金平均')
    fund_halyear_exposure_add = fund_halyear_exposure_add[cols]

    exposure_file = os.path.join(
        path, "halfyear_holding_exposure",
        'IndexHalfYearExposure_' + report_date + '.csv')
    index_halyear_exposure = pd.read_csv(exposure_file,
                                         index_col=[0],
                                         encoding='gbk')

    exposure_file = os.path.join(path, "lastyear_holding_exposure",
                                 'IndexLastDateExposure_' + last_date + '.csv')
    index_lastdate_exposure = pd.read_csv(exposure_file,
                                          index_col=[0],
                                          encoding='gbk')

    exposure_file = os.path.join(path, "lastyear_holding_exposure",
                                 'FundLastDateExposure_' + last_date + '.csv')
    fund_lastdate_exposure = pd.read_csv(exposure_file,
                                         index_col=[0],
                                         encoding='gbk')

    halfyear_exposure = pd.concat(
        [index_halyear_exposure, fund_halyear_exposure_add], axis=0)
    lastdate_exposure = pd.concat(
        [index_lastdate_exposure, fund_lastdate_exposure], axis=0)

    #########################################################################################################
    exposure_file = os.path.join(path, "output_exposure",
                                 'IndexFundExposure' + last_date + '.xlsx')

    excel = WriteExcel(exposure_file)
    worksheet = excel.add_worksheet("最近交易日风格暴露")
    num_format_pd = pd.DataFrame([],
                                 columns=lastdate_exposure.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.000'
    excel.write_pandas(lastdate_exposure,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=1,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)
    excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
                             len(lastdate_exposure.columns) - 1, None)
    excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
                             1 + len(lastdate_exposure),
                             len(lastdate_exposure.columns),
                             {'type': 'data_bar'})

    worksheet = excel.add_worksheet("最近半年报风格暴露")
    num_format_pd = pd.DataFrame([],
                                 columns=halfyear_exposure.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.000'
    excel.write_pandas(halfyear_exposure,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=1,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)
    excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
                             len(halfyear_exposure.columns) - 1, None)
    excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
                             1 + len(halfyear_exposure),
                             len(halfyear_exposure.columns),
                             {'type': 'data_bar'})
    excel.close()

    exposure_file = os.path.join(path, "output_exposure",
                                 '最近交易日风格暴露' + last_date + '.xlsx')

    excel = WriteExcel(exposure_file)
    worksheet = excel.add_worksheet("最近交易日风格暴露")
    num_format_pd = pd.DataFrame([],
                                 columns=lastdate_exposure.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.000'
    excel.write_pandas(lastdate_exposure,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=1,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)
    excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
                             len(lastdate_exposure.columns) - 1, None)
    excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
                             1 + len(lastdate_exposure),
                             len(lastdate_exposure.columns),
                             {'type': 'data_bar'})
    excel.close()

    exposure_file = os.path.join(path, "output_exposure",
                                 '最近半年报风格暴露' + report_date + '.xlsx')
    excel = WriteExcel(exposure_file)
    worksheet = excel.add_worksheet("最近半年报风格暴露")
    num_format_pd = pd.DataFrame([],
                                 columns=halfyear_exposure.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.000'
    excel.write_pandas(halfyear_exposure,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=1,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)
    excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
                             len(halfyear_exposure.columns) - 1, None)
    excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
                             1 + len(halfyear_exposure),
                             len(halfyear_exposure.columns),
                             {'type': 'data_bar'})
    excel.close()
Esempio n. 19
0
    def calculate_fund_factor(self, index_code, index_name, end_date):
        """ 计算基金最近一段时间内 跟踪误差、超额收益、信息比率 """

        # 参数
        # index_code = '000905.SH'
        # index_name = '中证500'
        # end_date = '20151231'
        beg_date = Date().get_trade_date_offset(end_date, -self.data_length)

        # 读取数据 基金池 基金净值数据 指数收盘价数据
        file = os.path.join(self.data_path, 'filter_fund_pool',
                            '基金最终筛选池_' + index_name + '.xlsx')
        fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
        fund_code['上市日期'] = fund_code['上市日期'].map(str)

        fund_nav = Fund().get_fund_factor("Repair_Nav")
        index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
        index_close.columns = [index_code]

        # 筛选新基金 并下载基金规模
        fund_code = fund_code.loc[:, ['上市日期', '基金全称', '基金简称']]
        fund_code = fund_code[fund_code['上市日期'] < beg_date]

        fund_code_str = ','.join(fund_code.index)
        fund_asset = w.wss(fund_code_str, "netasset_total",
                           "unit=1;tradeDate=" + str(end_date))
        fund_asset = pd.DataFrame(fund_asset.Data,
                                  index=['基金规模'],
                                  columns=fund_asset.Codes).T
        fund_asset['基金规模'] /= 100000000.0
        fund_asset['基金规模'] = fund_asset['基金规模'].round(2)
        fund_asset = fund_asset[fund_asset['基金规模'] > 0.45]
        fund_info = pd.concat([fund_code, fund_asset], axis=1)
        fund_info = fund_info.dropna()

        # 计算最近1年 各项指标
        result = pd.DataFrame([], index=fund_code.index, columns=['跟踪误差'])
        fund_nav = fund_nav.ix[index_close.index, fund_code.index]
        fund_pct = fund_nav.pct_change()
        index_pct = index_close.pct_change()
        index_pct = index_pct[index_code]
        fund_excess_pct = fund_pct.sub(index_pct, axis='index')
        fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
        fund_nav_period = fund_nav.loc[beg_date:end_date, :]
        index_close_period = index_close.loc[beg_date:end_date, :]
        result.ix[:, "数据长度"] = fund_excess_pct_period.count()
        result.ix[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)

        fund_return_log = (fund_nav_period.pct_change() + 1.0).applymap(
            np.log).cumsum().ix[-1, :]
        fund_return = fund_return_log.map(np.exp) - 1
        last_date_close = index_close_period.iloc[len(fund_nav_period) - 1, :]
        first_date_close = index_close_period.iloc[0, :]
        result.ix[:, "基金涨跌"] = fund_return
        result.ix[:, "指数涨跌"] = (last_date_close / first_date_close -
                                1.0).values[0]
        result.ix[:, "超额收益"] = result.ix[:, "基金涨跌"] - result.ix[:, "指数涨跌"]
        result.ix[:, "信息比率"] = result.ix[:, "超额收益"] / result.ix[:, "跟踪误差"]

        result = result[result['数据长度'] > self.data_min_length]
        result = pd.concat([fund_info, result], axis=1)
        result = result.sort_values(by=['信息比率'], ascending=False)
        result = result.dropna()
        result = result.fillna("")

        # 写到EXCEL表
        out_path = os.path.join(self.data_path, "cal_fund_factor", index_name)
        file_name = os.path.join(
            out_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.ix['format', '数据长度'] = '0'
        num_format_pd.ix['format', '信息比率'] = '0.00'
        num_format_pd.ix['format', '基金规模'] = '0.00'
        num_format_pd.ix['format', '信息比率'] = '0.00'

        sheet_name = "基金指标"
        excel = WriteExcel(file_name)
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(result,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=0,
                           num_format_pd=num_format_pd,
                           color="red",
                           fillna=True)
Esempio n. 20
0
    def risk_factor_performance(self,
                                factor_name,
                                stock_pool_name="AllChinaStockFilter",
                                beg_date=None,
                                end_date=None,
                                period='M'):

        """ 计算单风险因子的因子收益率波动率、自相关性、T值大于2的比例等等 找到有定价能力的风险因子 """

        exposure = self.get_risk_factor_exposure(factor_name)
        price = Stock().read_factor_h5("Price_Unadjust")
        num = Date().get_period_number_for_year(period)

        if beg_date is None:
            beg_date = exposure.columns[0]
        if end_date is None:
            end_date = exposure.columns[-1]

        date_series = Date().get_trade_date_series(beg_date, end_date, period)
        date_series = list(set(date_series) & set(exposure.columns) & set(price.columns))
        date_series.sort()

        factor_return = pd.DataFrame([], index=date_series, columns=['因子收益率'])

        for i_date in range(0, len(date_series)-1):

            cur_date = date_series[i_date]
            buy_date = cur_date
            sell_date = date_series[i_date + 1]
            stock_list = Stock().get_invest_stock_pool(stock_pool_name, cur_date)
            stock_pct = price[sell_date] / price[buy_date] - 1.0
            exposure_date = exposure[cur_date]
            exposure_next = exposure[sell_date]

            data = pd.concat([exposure_date, exposure_next], axis=1)
            data = data.dropna()
            stock_list_finally = list(set(stock_list) & set(data.index))
            stock_list_finally.sort()
            data = data.loc[stock_list_finally, :]
            auto_corr = data.corr().iloc[0, 1]

            data = pd.concat([exposure_date, stock_pct], axis=1)
            stock_list_finally = list(set(stock_list) & set(data.index))
            stock_list_finally.sort()
            data = data.loc[stock_list_finally, :]
            data.columns = ['x', 'y']
            data = data.dropna()

            if len(data) > 0:

                print("Risk Factor  %s %s %s" % (factor_name, stock_pool_name, cur_date))
                y = data['y'].values
                x = data['x'].values
                x_add = sm.add_constant(x)
                model = sm.OLS(y, x_add).fit()

                factor_return_date = model.params[1]
                rank_corr = data.corr(method="spearman").iloc[0, 1]
                t_value = model.tvalues[1]
                r2 = model.rsquared_adj

                factor_return.loc[cur_date, '因子收益率'] = factor_return_date
                factor_return.loc[cur_date, 'IC'] = rank_corr
                factor_return.loc[cur_date, 'T值'] = t_value
                factor_return.loc[cur_date, '自相关系数'] = auto_corr
                factor_return.loc[cur_date, 'R2'] = r2
            else:
                print("Risk Factor is Null %s %s %s" % (factor_name, stock_pool_name, cur_date))

        factor_return = factor_return.dropna(subset=['因子收益率', 'T值'])
        factor_return['因子累计收益率'] = factor_return['因子收益率'].cumsum()

        factor_return_mean = factor_return['因子收益率'].mean() * num
        factor_return_std = factor_return['因子收益率'].std() * np.sqrt(num)

        rank_ic_mean = factor_return['IC'].mean()
        rank_ic_ir = rank_ic_mean / factor_return['IC'].std() * np.sqrt(num)

        if len(factor_return) > 0:

            abs_t_2_ratio = len(factor_return[factor_return['T值'].abs() > 2]) / len(factor_return)
            data_beg_date = factor_return.index[0]
            data_end_date = factor_return.index[-1]
            abs_t_mean = factor_return['T值'].abs().mean()
            auto_corr_mean = factor_return['自相关系数'].mean()
            r2_mean = factor_return['R2'].mean()

            summary = pd.DataFrame([], columns=['因子表现'])
            summary.loc['因子年化收益率', "因子表现"] = factor_return_mean
            summary.loc['因子年化波动率', "因子表现"] = factor_return_std
            summary.loc['IC均值', "因子表现"] = rank_ic_mean
            summary.loc['ICIR', "因子表现"] = rank_ic_ir
            summary.loc['平均R2', "因子表现"] = r2_mean
            summary.loc['T值绝对值大于2的比率', "因子表现"] = abs_t_2_ratio
            summary.loc['T值绝对值平均数', "因子表现"] = abs_t_mean
            summary.loc['自相关系数平均', "因子表现"] = auto_corr_mean
            summary.loc['期数', "因子表现"] = str(len(factor_return))
            summary.loc['开始日期', "因子表现"] = data_beg_date
            summary.loc['结束日期', "因子表现"] = data_end_date

            path = os.path.join(self.factor_performance_path, stock_pool_name)
            if not os.path.exists(path):
                os.makedirs(path)
            file = os.path.join(path, 'Summary_%s.xlsx' % factor_name)

            excel = WriteExcel(file)
            num_format_pd = pd.DataFrame([], columns=summary.columns, index=['format'])
            num_format_pd.loc['format', :] = '0.00%'

            worksheet = excel.add_worksheet(factor_name)
            excel.write_pandas(summary, worksheet, begin_row_number=0, begin_col_number=1,
                               num_format_pd=num_format_pd, color="red", fillna=True)

            num_format_pd = pd.DataFrame([], columns=factor_return.columns, index=['format'])
            num_format_pd.loc['format', :] = '0.00%'
            excel.write_pandas(factor_return, worksheet, begin_row_number=0, begin_col_number=4,
                               num_format_pd=num_format_pd, color="red", fillna=True)
            excel.close()

        else:
            print("Risk Factor %s is Null in %s" % (factor_name, stock_pool_name))
Esempio n. 21
0
def write_public_qf(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '泰达宏利启富'
    fund_code = '003912.OF'
    fund_type = "公募"

    benchmark_code = '885003.WI'
    benchmark_name = '偏债混合型基金总指数'
    benchmark_code_2 = "881001.WI"
    benchmark_name_2 = "WIND全A"
    benchmark_ratio = 0.95

    setup_date = '20170315'
    date_array = np.array([["2019年", '20190101', end_date, '20180930'],
                           ["2018年", "20180101", '20181231', "20170930"],
                           ["2017年", setup_date, '20171231', setup_date],
                           ["成立以来", setup_date, end_date, setup_date]])

    benchmark_array = np.array([["沪深300",
                                 "000300.SH"], ["WIND全A", "881001.WI"],
                                ["中证全债", "H11001.CSI"],
                                ["偏债混合基金指数", '885003.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table(fund_name, fund_code,
                                                     fund_type, date_array,
                                                     benchmark_array)
    rank0 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "灵活配置型基金_30",
                                        excess=False)
    rank1 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "wind",
                                        excess=False)
    performance_table = pd.concat([performance_table, rank0, rank1], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        setup_date, end_date)

    benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return2 = fs.get_bencnmark_cum_return_series(setup_date, end_date)

    # 写入基金和基准时间序列
    ###########################################################################################
    cum_return = pd.concat([cum_return, cum_return2], axis=1)
    cum_return.columns = [fund_name, benchmark_name, benchmark_name_2]
    cum_return = cum_return.dropna()

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 基金和基准时间序列图
    ###########################################################################################
    chart_name = fund_name + "累计收益(成立以来)"
    series_name = [fund_name, benchmark_name, benchmark_name_2]
    insert_pos = 'B12'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)
    excel.close()
    ###########################################################################################
    return True
def write_public_lh(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '泰达宏利量化增强'
    fund_code = '001733.OF'
    fund_type = "公募"

    benchmark_code = '000905.SH'
    benchmark_name = '中证500'
    benchmark_ratio = 0.95

    setup_date = '20160830'
    date_array = np.array(
        [["2019年", '20190101', end_date, '20180930'],
         ["2018年", "20180101", '20181231', "20170930"],
         ["2017年", "20170101", '20171231', "20160930"],
         ["2016年", setup_date, "20161231", setup_date],
         ["成立(20160830)以来", setup_date, end_date, setup_date]])

    benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
                                ["股票型基金", '885012.WI'], ["创业板指", '399006.SZ'],
                                ["WIND全A", '881001.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(
        fund_name, fund_code, fund_type, date_array, benchmark_array)
    rank0 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "中证500基金",
                                        excess=False)
    rank1 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "普通股票型基金",
                                        excess=False)
    # rank2 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "中证500基金", excess=True)
    # rank3 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "指数增强型基金", excess=True)

    performance_table = pd.concat([performance_table, rank0, rank1], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入增强基金表现
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_enhanced_fund(
        fund_name, fund_code, fund_type, date_array, benchmark_code,
        benchmark_name, benchmark_ratio)

    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data),
                         benchmark_ratio)

    # 写入超额收益时间序列
    ###########################################################################################
    excess_cum_return = fs.get_cum_excess_return_series(setup_date, end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=excess_cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(excess_cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 超额收益图
    ###########################################################################################
    chart_name = fund_name + "累计超额收益(成立以来)"
    insert_pos = 'B16'
    excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number,
                                                 excess_cum_return, chart_name,
                                                 insert_pos, sheet_name)

    col_number = col_number + excess_cum_return.shape[1] + 2

    # 写入基金收益时间序列
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        setup_date, end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 写入基金收益时间序列图
    ############################################################################################
    series_name = [fund_name, benchmark_name]
    chart_name = fund_name + "累计收益(成立以来)"
    insert_pos = 'B32'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)
    excel.close()
    ###########################################################################################
    return True
Esempio n. 23
0
    def generate_excel(self, end_date):
        """ ETF净申购 输出到Excel """

        beg_date = Date().get_trade_date_offset(end_date, -120)
        beg_1m_date = Date().get_trade_date_offset(end_date, -20)

        # 一段时间内增减持额时间序列
        date_series = Date().get_trade_date_series(beg_date, end_date, "W")
        result = pd.DataFrame([])

        from quant.stock.index import Index
        index_data = Index().get_index_factor(index_code="000300.SH")

        ed_date = end_date
        for i in range(len(date_series) - 1):
            bg_date = Date().get_trade_date_offset(ed_date, -5)
            print("ETF Fund %s %s" % (bg_date, ed_date))
            result_add = self.get_etf_data_period_sum(bg_date, ed_date)
            result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
            result = pd.concat([result, result_add], axis=0)
            ed_date = bg_date

        result = result.sort_index()

        # 不同类型ETF基金流入流出
        fund_type = self.get_etf_data_period_type(beg_1m_date, end_date)

        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        # 信息写入文件
        filename = os.path.join(sub_path, 'ETF流入.xlsx')
        excel = WriteExcel(filename)
        sheet_name = "ETF流入"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([],
                                     columns=result.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(result,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=1,
                           num_format_pd=num_format_pd,
                           color="orange",
                           fillna=True)

        excel.chart_columns_plot(worksheet,
                                 sheet_name=sheet_name,
                                 series_name=["净申购金额", '沪深300'],
                                 chart_name="每周ETF净申购金额(亿元)",
                                 insert_pos="F8",
                                 cat_beg="B2",
                                 cat_end="B25",
                                 val_beg_list=["D2", "F2"],
                                 val_end_list=["D25", "F25"])

        num_format_pd = pd.DataFrame([],
                                     columns=fund_type.columns,
                                     index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(fund_type,
                           worksheet,
                           begin_row_number=0,
                           begin_col_number=15,
                           num_format_pd=num_format_pd,
                           color="orange",
                           fillna=True)
        excel.chart_columns_plot(worksheet,
                                 sheet_name=sheet_name,
                                 series_name=["净申购占比", '净申购金额'],
                                 chart_name="规模前10类ETF基金最近1月净流入",
                                 insert_pos="F24",
                                 cat_beg="P2",
                                 cat_end="P11",
                                 val_beg_list=["S2", "R2"],
                                 val_end_list=["S11", "R11"])

        excel.close()
def write_public_zz500_adjust(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name_adjust = '泰达宏利中证500_adjust'
    fund_code_adjust = '162216.OF_adjust'
    fund_name = '泰达宏利中证500'
    fund_code = '162216.OF'
    fund_type = "公募"

    benchmark_code = '000905.SH'
    benchmark_name = '中证500'
    benchmark_ratio = 0.95

    setup_date = '20141003'
    today = datetime.strptime(end_date, "%Y%m%d")
    before_1y = datetime(year=today.year - 1, month=today.month,
                         day=today.day).strftime("%Y%m%d")
    before_2y = datetime(year=today.year - 2, month=today.month,
                         day=today.day).strftime("%Y%m%d")
    before_3y = datetime(year=today.year - 3, month=today.month,
                         day=today.day).strftime("%Y%m%d")

    date_array = np.array([
        ["2019年", '20190101', end_date, '20180930'],
        ["2018年", "20180101", '20181231', "20170930"],
        ["2017年", "20170101", '20171231', "20160930"],
        ["2016年", "20160101", "20161231", "20150930"],
        ["2015年", "20150101", "20151231", "20150101"],
        ["管理(20141003)以来", setup_date, end_date, setup_date],
        ["2015年以来", "20150101", end_date, setup_date],
        ["过去1年", before_1y, end_date, before_1y],
        ["过去2年", before_2y, end_date, before_2y],
        ["过去3年", before_3y, end_date, before_3y],
    ])

    benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
                                ["WIND全A", '881001.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name_adjust + '.xlsx')
    sheet_name = fund_name_adjust
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(
        fund_name, fund_code, fund_type, date_array, benchmark_array)
    # rank0 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "wind", excess=False)
    # rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "被动指数型基金", excess=True)
    rank2 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "中证500基金",
                                        excess=False)
    performance_table = pd.concat([performance_table, rank2], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入增强基金表现
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_enhanced_fund(
        fund_name, fund_code, fund_type, date_array, benchmark_code,
        benchmark_name, benchmark_ratio)

    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data),
                         benchmark_ratio)

    # 写入超额收益时间序列
    ###########################################################################################
    excess_cum_return = fs.get_cum_excess_return_series("20150101", end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=excess_cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(excess_cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 超额收益图
    ###########################################################################################
    chart_name = fund_name + "累计超额收益(2015年以来)"
    insert_pos = 'B12'
    excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number,
                                                 excess_cum_return, chart_name,
                                                 insert_pos, sheet_name)

    col_number = col_number + excess_cum_return.shape[1] + 2

    # 写入基金收益时间序列
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        "20150101", end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 写入基金收益时间序列图
    ############################################################################################
    series_name = [fund_name, benchmark_name]
    chart_name = fund_name + "累计收益(2015年以来)"
    insert_pos = 'B26'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)
    excel.close()
    ###########################################################################################
    return True
Esempio n. 25
0
def write_quant12(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '光大量化组合12号'
    fund_code = fund_name
    fund_type = "专户"

    benchmark_code = "000905.SH"
    benchmark_name = "中证500"

    setup_date = "20160714"
    date_array = np.array([["2019年", '20190101', end_date],
                           ["2018年", "20180101", '20181231'],
                           ["2017年", "20170101", '20171231'],
                           ["成立(20160714)至2016年末", setup_date, '20161231'],
                           ["成立(20160714)以来", setup_date, end_date]])

    benchmark_array = np.array([["沪深300", "000300.SH"],
                                ["中证500", "000905.SH"],
                                ["股票型基金", '885012.WI'],
                                ["WIND全A", '881001.WI']])
    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array)
    col_number = 1
    num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="red", fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入增强基金表现
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_enhanced_fund(fund_name, fund_code,
                                                        fund_type, date_array, benchmark_code, benchmark_name)

    num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="red", fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))

    # 写入超额收益时间序列
    ###########################################################################################
    excess_cum_return = fs.get_cum_excess_return_series(setup_date, end_date)

    num_format_pd = pd.DataFrame([], columns=excess_cum_return.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(excess_cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="blue", fillna=True)

    # 超额收益图
    ###########################################################################################
    chart_name = fund_name + "累计超额收益(成立以来)"
    insert_pos = 'B12'
    excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number, excess_cum_return,
                                                 chart_name, insert_pos, sheet_name)

    col_number = col_number + excess_cum_return.shape[1] + 2

    # 写入基金收益时间序列
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(setup_date, end_date)

    num_format_pd = pd.DataFrame([], columns=cum_return.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="blue", fillna=True)

    # 写入基金收益时间序列图
    ############################################################################################
    series_name = [fund_name, benchmark_name]
    chart_name = fund_name + "累计收益(成立以来)"
    insert_pos = 'B26'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos, sheet_name)
    excel.close()
    ###########################################################################################
    return True
Esempio n. 26
0
    def generate_excel(self, end_date):

        """ 陆股通信息 输出到Excel """

        beg_date = Date().get_trade_date_offset(end_date, -60)
        beg_1m_date = Date().get_trade_date_offset(end_date, -20)
        beg_1m_date = "20190201"

        # 一段时间内增减持额时间序列
        from quant.stock.index import Index
        index_data = Index().get_index_factor(index_code="000300.SH")
        date_series = Date().get_trade_date_series(beg_date, end_date, "W")
        result = pd.DataFrame([])

        ed_date = end_date
        for i in range(len(date_series)-1):
            bg_date = Date().get_trade_date_offset(ed_date, -5)
            print("Hk Inflow Period %s %s" % (bg_date, ed_date))
            result_add = self.hk_inflow_period(bg_date, ed_date)
            result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
            result = pd.concat([result, result_add], axis=0)
            ed_date = bg_date

        result = result.sort_index()
        # 最近一个月平均持股金额最大、最小的几个股票
        stock = self.hk_inflow_period_stock(beg_1m_date, end_date)

        # 最近一个月平均持股金额行业排序
        industry = self.hk_inflow_period_industry(beg_1m_date, end_date)

        # 数据存贮位置
        sub_path = os.path.join(self.data_path, end_date)
        if not os.path.exists(sub_path):
            os.makedirs(sub_path)

        # 信息写入文件
        filename = os.path.join(sub_path, '融资融券情况春节后.xlsx')
        print(filename)
        excel = WriteExcel(filename)
        sheet_name = "融资融券情况"
        worksheet = excel.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
                                 series_name=["净流入金额", '沪深300'],
                                 chart_name="最近3个月每周融资余额净买入金额(亿元)",
                                 insert_pos="I15", cat_beg="B2", cat_end="B13",
                                 val_beg_list=["F2", "H2"], val_end_list=["F13", "H13"])

        num_format_pd = pd.DataFrame([], columns=stock.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(stock, worksheet, begin_row_number=0, begin_col_number=8,
                           num_format_pd=num_format_pd, color="orange", fillna=True)

        num_format_pd = pd.DataFrame([], columns=industry.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00'
        num_format_pd.loc['format', "净流入占比"] = '0.00%'
        excel.write_pandas(industry, worksheet, begin_row_number=0, begin_col_number=15,
                           num_format_pd=num_format_pd, color="orange", fillna=True)
        excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
                                 series_name=["净流入占比", '净流入金额'],
                                 chart_name="行业最近1月融资净买入金额",
                                 insert_pos="I32", cat_beg="P2", cat_end="P30",
                                 val_beg_list=["U2", "S2"], val_end_list=["U30", "S30"])

        excel.close()
Esempio n. 27
0
    def alpha_contribution(self, fmp_name, type):
        """
        risk factor return multiply risk factor exposure_return of fmp
        can contribute alpha return on risk factor
        """

        for i_date in range(len(self.change_date_series) - 1):

            # date
            ####################################################################################################
            date = self.change_date_series[i_date]
            next_date = self.change_date_series[i_date + 1]
            change_date = Date().get_trade_date_offset(date, 1)
            next_change_date = Date().get_trade_date_offset(next_date, 1)

            # data
            ####################################################################################################
            stock_return = self.get_stock_return(change_date, next_change_date)

            fmp = self.get_fmp(fmp_name, type)
            fmp_date = pd.DataFrame(fmp[date])
            fmp_date.columns = ['FmpWeight']
            fmp_date = fmp_date.dropna()

            exposure_date = self.get_risk_factor_exposure(date)
            risk_return = self.get_risk_factor_return().T
            risk_return_date = pd.DataFrame(risk_return[date])

            code_list = list(
                set(exposure_date.index) & set(fmp_date.index)
                & set(stock_return.index))
            code_list.sort()

            exposure_date = exposure_date.loc[code_list, :]
            fmp_date = fmp_date.loc[code_list, :]
            stock_return = stock_return.loc[code_list, :]

            if len(fmp_date) > self.min_stock_num:

                # risk factor return multiply alpha exposure_return on risk factor
                ####################################################################################################
                fmp_exposure = np.dot(fmp_date.T, exposure_date)
                fmp_exposure = pd.DataFrame(fmp_exposure,
                                            index=[date],
                                            columns=exposure_date.columns)

                fmp_risk_factor = fmp_exposure.mul(risk_return_date.T)
                fmp_alpha_factor = np.dot(fmp_date.T, stock_return)

                col = list(fmp_risk_factor.columns)

                fmp_risk_factor.loc[date, 'Res_Alpha'] = fmp_alpha_factor[0][
                    0] - fmp_risk_factor.sum().sum()
                fmp_risk_factor.loc[date, 'Industry'] = fmp_risk_factor[
                    self.industry_factor_name].sum().sum()
                fmp_risk_factor.loc[date, 'Style'] = fmp_risk_factor[
                    self.style_factor_name].sum().sum()
                fmp_risk_factor.loc[date, 'Raw_Alpha'] = fmp_alpha_factor[0][0]

                col.insert(0, 'Res_Alpha')
                col.insert(0, 'Industry')
                col.insert(0, 'Style')
                col.insert(0, 'Raw_Alpha')
                fmp_risk_factor = fmp_risk_factor[col]
                print("Contribution for %s %s %s %s" %
                      (self.alpha_factor_name, fmp_name, type, date))

                # 4 concat
                ####################################################################################################
                if i_date == 0:
                    fmp_risk_factor_all = fmp_risk_factor
                    fmp_exposure_all = fmp_exposure
                else:
                    fmp_risk_factor_all = pd.concat(
                        [fmp_risk_factor_all, fmp_risk_factor], axis=0)
                    fmp_exposure_all = pd.concat(
                        [fmp_exposure_all, fmp_exposure], axis=0)

        # summary
        ####################################################################################################
        sub_path = os.path.join(self.path, 'summary')
        fmp_risk_summary = pd.DataFrame()
        fmp_risk_summary['Contribution'] = fmp_risk_factor_all.mean(
        ) * self.annual_number
        fmp_risk_summary['IR'] = fmp_risk_factor_all.mean(
        ) / fmp_risk_factor_all.std() * np.sqrt(self.annual_number)

        risk_return = risk_return.T
        risk_return_mean = pd.DataFrame(
            risk_return.mean()) * self.annual_number
        risk_return_mean.columns = ['Factor Return']

        exposure_mean = pd.DataFrame(fmp_exposure_all.mean())
        exposure_mean.columns = ['Avg Exposure']

        exposure = pd.concat([risk_return_mean, exposure_mean], axis=1)

        # write excel
        ####################################################################################################
        filename = os.path.join(
            sub_path,
            '%s_%s_%s_Summary.xlsx' % (self.alpha_factor_name, fmp_name, type))
        sheet_name = "Contribution"

        we = WriteExcel(filename)
        ws = we.add_worksheet(sheet_name)

        num_format_pd = pd.DataFrame([],
                                     columns=fmp_risk_summary.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.ix['format', 'IR'] = '0.00'
        we.write_pandas(fmp_risk_summary,
                        ws,
                        begin_row_number=0,
                        begin_col_number=1,
                        num_format_pd=num_format_pd,
                        color="blue",
                        fillna=True)

        num_format_pd = pd.DataFrame([],
                                     columns=exposure.columns,
                                     index=['format'])
        num_format_pd.ix['format', :] = '0.00%'
        num_format_pd.ix['format', 'Avg Exposure'] = '0.000'
        we.write_pandas(exposure,
                        ws,
                        begin_row_number=4,
                        begin_col_number=2 + len(fmp_risk_summary.columns),
                        num_format_pd=num_format_pd,
                        color="blue",
                        fillna=True)
        we.close()

        # Write Csv
        ####################################################################################################
        sub_path = os.path.join(self.path, 'fmp_risk_factor')
        filename = os.path.join(
            sub_path, '%s_%s_%s_RiskContributionFMP.csv' %
            (self.alpha_factor_name, fmp_name, type))
        fmp_risk_factor_all.to_csv(filename)
        sub_path = os.path.join(self.path, 'fmp_exposure')
        filename = os.path.join(
            sub_path, '%s_%s_%s_RiskExposureFMP.csv' %
            (self.alpha_factor_name, fmp_name, type))
        fmp_exposure_all.to_csv(filename)
Esempio n. 28
0
def write_zlhl2018(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '广州农商行梓霖惠利1号'
    fund_code = fund_name
    fund_type = "专户"

    benchmark_code = '885007.WI'
    benchmark_name = "混合债券二级基金指数"
    benchmark_code_2 = "H11001.CSI"
    benchmark_name_2 = "中证全债指数"

    setup_date = '20180628'
    date_array = np.array([["2019年", '20190101', end_date],
                           ["2018年", "20180101", '20181231'],
                           ["20180628以来", '20180628', end_date]])

    benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
                                ["股票型基金", '885012.WI'],
                                ["混合债券二级基金指数", '885007.WI'],
                                ["中证全债指数", "H11001.CSI"]])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '2018年.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入增强基金表现 相对基准
    ###########################################################################################
    col_number = 1
    performance_table = MfcTable().cal_summary_table_enhanced_fund(
        fund_name, fund_code, fund_type, date_array, benchmark_code,
        benchmark_name)
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入增强基金表现  相对指数
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_enhanced_fund(
        fund_name, fund_code, fund_type, date_array, benchmark_code_2,
        benchmark_name_2)

    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入基金绝对表现
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table(fund_name, fund_code,
                                                     fund_type, date_array,
                                                     benchmark_array)
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    # 写入基金和基准收益时间序列 相对基准
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        setup_date, end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 基金和基准收益图 相对基准
    ###########################################################################################
    series_name = [fund_name, benchmark_name]
    chart_name = fund_name + "相对" + benchmark_name + " 累计超额收益(成立以来)"
    insert_pos = 'B16'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)

    col_number = col_number + cum_return.shape[1] + 2

    # 写入基金和基准收益时间序列 相对指数
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        setup_date, end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 基金和基准收益图 相对指数
    ###########################################################################################
    series_name = [fund_name, benchmark_name_2]
    chart_name = fund_name + "相对" + benchmark_name_2 + " 累计超额收益(成立以来)"
    insert_pos = 'B32'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)
    excel.close()
    ###########################################################################################
    return True
Esempio n. 29
0
def write_public_fxwy(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '泰达宏利复兴伟业'
    fund_code = '001170.OF'
    fund_type = "公募"

    benchmark_code = '885001.WI'
    benchmark_name = '偏股混合基金总指数'
    benchmark_code_2 = "000300.SH"
    benchmark_name_2 = "沪深300"
    benchmark_ratio = 0.95

    setup_date = '20150421'  # 吴华开始管理 也是成立日
    today = datetime.strptime(end_date, "%Y%m%d")
    before_1y = datetime(year=today.year-1, month=today.month, day=today.day).strftime("%Y%m%d")
    before_2y = datetime(year=today.year-2, month=today.month, day=today.day).strftime("%Y%m%d")
    before_3y = datetime(year=today.year-3, month=today.month, day=today.day).strftime("%Y%m%d")
    before_5y = datetime(year=today.year-5, month=today.month, day=today.day).strftime("%Y%m%d")

    date_array = np.array([["2019年", '20190101', end_date, '20180930'],
                           ["2018年", "20180101", '20181231', "20170930"],
                           ["2017年", "20170101", '20171231', "20160930"],
                           ["2016年", '20160101', '20161231', "20150930"],
                           ["成立以来(吴华管理)", setup_date, end_date, setup_date],
                           ["过去1年", before_1y, end_date, before_1y],
                           ["过去2年", before_2y, end_date, before_2y],
                           ["过去3年", before_3y, end_date, before_3y],
                           ])

    benchmark_array = np.array([["沪深300", "000300.SH"],
                                ["中证500", "000905.SH"],
                                ["股票型基金总指数", '885012.WI'],
                                ["WIND全A", '881001.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array)
    rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "偏股混合型基金", excess=False)
    rank2 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "灵活配置型基金_60", excess=False)
    rank3 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "股票+灵活配置60型基金", excess=False)
    performance_table = pd.concat([performance_table, rank1, rank2, rank3], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="red", fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(setup_date, end_date)

    benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return2 = fs.get_bencnmark_cum_return_series(setup_date, end_date)

    # 写入基金和基准时间序列
    ###########################################################################################
    cum_return = pd.concat([cum_return, cum_return2], axis=1)
    cum_return.columns = [fund_name, benchmark_name, benchmark_name_2]
    cum_return = cum_return.dropna()

    num_format_pd = pd.DataFrame([], columns=cum_return.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="blue", fillna=True)

    # 基金和基准时间序列图
    ###########################################################################################
    chart_name = fund_name + "累计收益(管理以来)"
    series_name = [fund_name, benchmark_name, benchmark_name_2]
    insert_pos = 'B12'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos, sheet_name)
    excel.close()
    ###########################################################################################
    return True
Esempio n. 30
0
    def fund_score_all(self, end_date):

        """ 所有基金得分 """

        file = os.path.join(self.data_path, "基金经理绩效考核.xlsx")
        data = pd.read_excel(file, sheetname="基金经理考核范围", index_col=[0])
        data['现任经理管理开始日'] = data['现任经理管理开始日'].map(str)
        data.index = data['代码']
        result = pd.DataFrame()
        end_date = Date().change_to_datetime(end_date)
        before_1y = datetime(year=end_date.year, month=1, day=1).strftime("%Y%m%d")
        before_3y = datetime(year=end_date.year-2, month=1, day=1).strftime("%Y%m%d")
        before_3y = max(before_3y, "20160101")
        before_5y = datetime(year=end_date.year-4, month=1, day=1).strftime("%Y%m%d")
        before_5y = max(before_5y, "20160101")
        end_date = Date().change_to_str(end_date)

        for i in range(0, len(data)):

            fund_code = data.index[i]
            index_code = data.loc[fund_code, "基准代码"]
            rank_pool = data.loc[fund_code, "考核分类"]
            mg_date = data.loc[fund_code, "现任经理管理开始日"]
            fund_name = data.loc[fund_code, "名称"]
            fund_type = data.loc[fund_code, "基金类型"]

            if mg_date <= before_1y:

                res = self.fund_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
                res.loc[fund_code, "当年研究贡献得分"] = 1.00
                res.loc[fund_code, "过去2年研究贡献得分"] = 1.00

                if mg_date >= before_5y:

                    res.loc[fund_code, "5年收益"] = res.loc[fund_code, "管理以来收益"]
                    res.loc[fund_code, "5年排名"] = res.loc[fund_code, "管理以来排名"]
                    res.loc[fund_code, "5年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
                    res.loc[fund_code, "5年得分"] = res.loc[fund_code, "管理以来得分"]

                if mg_date >= before_3y:
                    res.loc[fund_code, "3年收益"] = res.loc[fund_code, "管理以来收益"]
                    res.loc[fund_code, "3年排名"] = res.loc[fund_code, "管理以来排名"]
                    res.loc[fund_code, "3年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
                    res.loc[fund_code, "3年得分"] = res.loc[fund_code, "管理以来得分"]

                res.loc[fund_code, "长期得分"] = 0.0
                res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "过去2年研究贡献得分"]
                res.loc[fund_code, "长期得分"] += 0.40 * res.loc[fund_code, "3年得分"]
                res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "5年得分"]
                res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "管理以来得分"]

                res.loc[fund_code, "当年得分"] = 0.0
                res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "当年研究贡献得分"]
                res.loc[fund_code, "当年得分"] += 0.40 * res.loc[fund_code, "1年得分"]
                res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "3年得分"]
                res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "5年得分"]
                res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "管理以来得分"]

                result = pd.concat([result, res], axis=0)

        file = os.path.join(self.data_path, "基金经理效绩得分.xlsx")
        excel = WriteExcel(file)
        num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'

        sheet_name = ""
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="red", fillna=True)
        excel.close()

        # 超额收益
        result = pd.DataFrame()
        for i in range(0, len(data)):

            fund_code = data.index[i]
            index_code = data.loc[fund_code, "基准代码"]
            rank_pool = data.loc[fund_code, "考核分类"]
            mg_date = data.loc[fund_code, "现任经理管理开始日"]
            fund_name = data.loc[fund_code, "名称"]
            fund_type = data.loc[fund_code, "基金类型"]

            if (mg_date <= before_1y) and (fund_type == "行业基金"):
                res = self.fund_excess_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
                if mg_date >= before_5y:
                    res.loc[fund_code, "5年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
                    res.loc[fund_code, "5年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
                    res.loc[fund_code, "5年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
                    res.loc[fund_code, "5年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
                if mg_date >= before_3y:
                    res.loc[fund_code, "3年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
                    res.loc[fund_code, "3年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
                    res.loc[fund_code, "3年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
                    res.loc[fund_code, "3年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
                result = pd.concat([result, res], axis=0)

        file = os.path.join(self.data_path, "基金经理效绩超额得分.xlsx")
        excel = WriteExcel(file)
        num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
        num_format_pd.loc['format', :] = '0.00%'

        sheet_name = ""
        worksheet = excel.add_worksheet(sheet_name)
        excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
                           num_format_pd=num_format_pd, color="red", fillna=True)
        excel.close()