fund_service.py

# -*- encoding: utf-8 -*-
# -----------------------------------------------------------------------------
# @File Name  : fund_service.py
# @Time       : 2021/1/14 下午5:31
# @Author     : X. Peng
# @Email      : acepengxiong@163.com
# @Software   : PyCharm
# -----------------------------------------------------------------------------
from app.service.portfolio_diagnose import *
from app.utils.draw import draw_index_combination_chart


def get_tamp_nav(fund, start_date, rollback=False, invest_type=2):
    """获取基金ID为fund, 起始日期为start_date, 终止日期为当前日期的基金净值表

    Args:
        fund[str]:基金ID
        start_date[date]:起始日期
        rollback[bool]:当起始日期不在净值公布日历中，是否往前取最近的净值公布日
        invest_type[num]:0:公募 1:私募 2:优选

    Returns:df[DataFrame]: 索引为净值公布日， 列为复权净值的净值表; 查询失败则返回None

    """
    with TAMP_SQL(tamp_product_engine) as tamp_product, TAMP_SQL(tamp_fund_engine) as tamp_fund:
        tamp_product_session = tamp_product.session
        tamp_fund_session = tamp_fund.session
        # if invest_type == "private":
        #     sql = "SELECT fund_id, price_date, cumulative_nav FROM fund_nav " \
        #           "WHERE fund_id='{}'".format(fund)
        #     # df = pd.read_sql(sql, con).dropna(how='any')
        #     cur = tamp_product_session.execute(sql)
        if invest_type == 0:
            sql = """select distinct `fund_id`, `price_date`, `cumulative_nav` from `tx_fund_nav` where `fund_id`='{}' and `delete_tag`=0 order by `price_date` ASC""".format(
                fund)
            cur = tamp_fund_session.execute(sql)
        elif invest_type == 1:
            sql = """select distinct `fund_id`, `price_date`,`cumulative_nav` from `fund_nav` where `fund_id`='{}'  order by `price_date` ASC""".format(
                fund)
            cur = tamp_fund_session.execute(sql)
        elif invest_type == 2:
            sql = """select distinct `fund_id`,`price_date`,`cumulative_nav` from `fund_nav` where `fund_id`='{}'  order by `price_date` ASC""".format(
                fund)
            cur = tamp_product_session.execute(sql)
        elif invest_type == 3:
            sql = """select distinct `fund_id`,`price_date`,`cumulative_nav` from `ifa_imported_fund_nav` where `fund_id`='{}'  order by `price_date` ASC""".format(
                fund)
            cur = tamp_fund_session.execute(sql)

        data = cur.fetchall()
        df = pd.DataFrame(data, columns=['fund_id', 'price_date', 'cumulative_nav']).dropna(how='any')
        df.rename({'price_date': 'end_date', 'cumulative_nav': 'adj_nav'}, axis=1, inplace=True)
        df['end_date'] = pd.to_datetime(df['end_date'])

        if rollback and df['end_date'].min() < start_date < df['end_date'].max():
            while start_date not in list(df['end_date']):
                start_date -= datetime.timedelta(days=1)

        df = df[df['end_date'] >= start_date]
        df.drop_duplicates(subset='end_date', inplace=True, keep='first')
        df.set_index('end_date', inplace=True)
        df.sort_index(inplace=True, ascending=True)
    return df


def single_evaluation(fund_id, invest_type=2, index_id='000905.SH'):
    """
       1、该基金整体表现优秀/良好/一般，收益能力优秀/良好/合格/较差，回撤控制能力优秀/良好/合格/较差，风险收益比例较高/一般/较低；
       2、在收益方面，该基金年化收益能力高于/持平/低于同类基金平均水平，有x%区间跑赢大盘/指数，绝对收益能力优秀/一般；
       3、在风险方面，该基金抵御风险能力优秀/良好/一般，在同类基金中处于高/中/低等水平，最大回撤为x%，高于/持平/低于同类基金平均水平；
       4、该基金收益较好/较差的同时回撤较大/较小，也就是说，该基金在用较大/较小风险换取较大/较小收益，存在较高/较低风险；
       5、基金经理，投资年限5.23年，经验丰富；投资能力较强，生涯中共管理过X只基金，历任的X只基金平均业绩在同类中处于上游水平，其中x只排名在前x%；生涯年化回报率x%，同期大盘只有x%；
    :param fund_id: 基金ID
    :param index_id: 指数ID
    :param invest_type: 投资类型：0：公募 1：私募 2：探普优选
    """
    end_date = datetime.datetime(datetime.date.today().year,
                                 datetime.date.today().month, 1) - datetime.timedelta(1)
    start_date = cal_date(end_date, 'Y', 1)
    if invest_type == 0:
        rank_df = tx_fund_rank
    else:
        rank_df = fund_rank
    z_score = search_rank(rank_df, fund_id, metric='z_score')
    total_level = np.select([z_score >= 80,
                             70 <= z_score < 80,
                             z_score < 70], [0, 1, 2]).item()

    index_return_monthly = get_index_monthly(index_id, start_date)
    fund_nav = get_tamp_nav(fund_id, start_date, invest_type=invest_type)
    fund_nav_monthly = fund_nav.groupby([fund_nav.index.year, fund_nav.index.month]).tail(1)
    fund_nav_monthly = rename_col(fund_nav_monthly, fund_id)
    fund_return_monthly = simple_return(fund_nav_monthly[fund_id].astype(float))
    index_return_monthly.index = index_return_monthly.index.strftime('%Y-%m')
    fund_return_monthly.index = fund_return_monthly.index.strftime('%Y-%m')
    compare = pd.merge(index_return_monthly, fund_return_monthly, how='inner', left_index=True, right_index=True)
    fund_win_rate = ((compare[fund_id] - compare['pct_chg']) > 0).sum() / compare[fund_id].count()

    return_rank = search_rank(rank_df, fund_id, metric='annual_return_rank')
    return_level = np.select([return_rank >= 0.8,
                              0.7 <= return_rank < 0.8,
                              0.6 <= return_rank < 0.7,
                              return_rank < 0.6], [0, 1, 2, 3]).item()
    return_bool = 1 if return_level > 2 else 0
    return_triple = return_level - 1 if return_level >= 2 else return_level

    drawdown_rank = search_rank(rank_df, fund_id, metric='max_drawdown_rank')
    drawdown_value = search_rank(rank_df, fund_id, metric='max_drawdown')
    drawdown_level = np.select([drawdown_rank >= 0.8,
                                0.7 <= drawdown_rank < 0.8,
                                0.6 <= drawdown_rank < 0.7,
                                drawdown_rank < 0.6], [0, 1, 2, 3]).item()
    drawdown_bool = 1 if drawdown_level > 2 else 0
    drawdown_triple = drawdown_level - 1 if drawdown_level >= 2 else drawdown_level

    sharp_rank = search_rank(rank_df, fund_id, metric='sharp_ratio_rank')
    sharp_level = np.select([sharp_rank >= 0.8,
                             0.6 <= sharp_rank < 0.8,
                             sharp_rank < 0.6], [0, 1, 2]).item()

    data = {1: [total_level, return_level, drawdown_level, sharp_level],
            2: [return_triple, format(fund_win_rate, '.2%'), return_bool],
            3: [drawdown_triple, drawdown_triple, format(drawdown_value, '.2%'), drawdown_triple],
            4: [return_bool, drawdown_bool, drawdown_bool, return_bool, drawdown_bool]}

    x = '30%'
    content = {
        # 第一个评价
        1: [["优秀", "良好", "一般"],
            ["优秀", "良好", "合格", "较差"],
            ["优秀", "良好", "合格", "较差"],
            ["高", "一般", "较低"]],
        # 第二个评价
        2: [["高于", "持平", "低于"],
            x,
            ["优秀", "一般"]],
        # 第三个评价
        3: [["优秀", "良好", "一般"],
            ["高", "中", "低"], x,
            ["高于", "持平", "低于"]],
        # 第四个评价
        4: [["较好", "较差"],
            ["较小", "较大"],
            ["较小", "较小"],
            ["较大", "较小"],
            ["较低", "较高"]]}

    sentence = {
        1: "该基金整体表现%s，收益能力%s，回撤控制能力%s，风险收益比例%s；\n",
        2: "在收益方面，该基金年化收益能力%s同类基金平均水平，有%s区间跑赢指数，绝对收益能力%s；\n",
        3: "在风险方面，该基金抵御风险能力%s，在同类基金中处于%s等水平，最大回撤为%s，%s同类基金平均水平；\n",
        4: "该基金收益%s的同时回撤%s，也就是说，该基金在用%s风险换取%s收益，存在%s风险；\n"}

    ret = []
    fund_name = get_fund_name(fund_id, fund_type=invest_type).values[0][0]

    i = 1
    for k, v in data.items():
        single_sentence = str(i) + "、" + sentence[k] % translate_single(content, k, v)
        ret.append(single_sentence)
        i += 1

    evaluation_dict = {'fund_name': fund_name, 'status': '', 'evaluation': ret, 'radar_chart_path': fund_index_compare(fund_id=fund_id, invest_type=invest_type)}

    # if objective:
    #     if fund_id in self.abandon_fund_score + self.abandon_fund_corr:
    #         evaluation_dict['status'] = "换仓"
    #     elif fund_id in self.portfolio:
    #         evaluation_dict['status'] = "保留"
    # else:
    #     evaluation_dict['status'] = ""
    return evaluation_dict


def fund_index_compare(fund_id, invest_type=2, index_id='000905.SH'):
    start_date = datetime.datetime(2000, 1, 1)
    index_daily = get_index_daily(index_id, start_date)
    fund_nav = get_tamp_nav(fund_id, start_date, invest_type=invest_type)
    fund_nav = rename_col(fund_nav, fund_id)
    compare = pd.merge(index_daily, fund_nav, how='inner', left_index=True, right_index=True)
    compare[index_id + '_return_ratio'] = (compare[index_id] / compare[index_id].iloc[0] - 1) * 100
    compare[fund_id + '_return_ratio'] = (compare[fund_id] / compare[fund_id].iloc[0] - 1) * 100

    xlabels = ["" for i in range(len(compare))]

    # index_name = get_index_name(index_id).values[0][0]
    # fund_name = get_fund_name(fund_id).values[0][0]
    chart_data = {
        "xlabels": xlabels,
        "index": {'name': '中证500', 'data': compare[index_id + "_return_ratio"].values},
        "fund": {'name': fund_id, 'data': compare[fund_id + "_return_ratio"].values},
    }
    r = draw_index_combination_chart(chart_data)
    return r


if __name__ == '__main__':
    # print(single_evaluation(fund_id='HF00005AFK'))
    print(fund_index_compare(fund_id='HF00002G4A'))