fund_performance.py

#!/usr/bin/python3.6
# -*- coding: utf-8 -*-
# @Time    : 2020/12/25 13:16
# @Author  : Jie. Z
# @Email   : zhaojiestudy@163.com
# @File    : fund_performance.py
# @Software: PyCharm
import pandas as pd


# 数据处理 入库操作
def dataprepare(fund_id , fav_freq , index_code, enddate, strategy, data_source, status):

    #银行利率，暂时写死，后期改为动态获取
    BANK_RATE = 0.015
    # 同时读取基金净值和指数，条件是日期同时存在
    '''
    global  trade_df
    if trade_df.empty:
        trade_df = gettradecal('2001-01-01', datetime.date.strftime(datetime.date.today(), '%Y-%m-%d'))
    '''

    df1 = gettradecal('2001-01-01', datetime.date.strftime(datetime.date.today(), '%Y-%m-%d'))


    global current_env ,origion_env
    pool = PooledDB(pymysql,  maxconnections=100, blocking= True,  host=origion_env[current_env]['host'], port=int(origion_env[current_env]['port']) ,user=origion_env[current_env]['user'], password=origion_env[current_env]['password'],database=origion_env[current_env]['database'], charset="utf8")  # 5为连接池里的最少连接数
    conn = pool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur = conn.cursor()

    if index_code == '000000':
        handle_indexes = True
    else:
        handle_indexes = False

    if handle_indexes:
        #待修改
        sql = """SELECT distinct a.price_date as datetime,a.close as net_worth, a.close as benchmark, a.close as net FROM fund_market_indexes a WHERE  a.index_id= '%s' and a.price_date<= '%s' order by a.price_date ASC""" % (fund_id, enddate)
    else:
        sql = """SELECT distinct a.price_date as datetime,a.cumulative_nav as net_worth,b.close as benchmark, a.nav as net FROM fund_nav a ,fund_market_indexes b WHERE a.price_date=b.price_date and b.index_id= '%s' and a.fund_id= '%s' and a.price_date<= '%s' order by a.price_date ASC""" % (index_code, fund_id, enddate)
    print(sql)
    cur.execute(sql)

    # 5.获取数据
    data = cur.fetchall()

    if len(data)==0:
        return
    # 处理数据
    col_result = cur.description
    columns = []
    for i in range(len(col_result)):
        columns.append(col_result[i][0])

    # 设置dataframe的columns
    df = pd.DataFrame(columns=columns)

    # 插入数据，把查询结果存入dataframe

    for i in range(len(data)):
        df.loc[i] = list(data[i])
    df['datetime'] = pd.to_datetime(df['datetime'], format='%Y/%m/%d')

    # 6.关闭cursor
    cur.close()

    # 7.关闭connection
    conn.close()

    # 字符串转化为float
    df['net_worth'] = handle_data(df['net_worth'])
    df['net'] = handle_data(df['net'])
    df['benchmark'] = handle_data(df['benchmark'])

    if len(df) < 1:
        return

    print('重采样前：',len(df))
    print(df)
    # 判断基金净值更新频率，进行dataframe处理对齐
    # 如果是日更，则按照交易日补齐，没有数据的用昨日数据；如果是周更，取当周最后一个更新，如果没有数据则取前一周数据；如果是月更，取当月最后一个数据，如果没有数据则取前一个月数据；
    # 收益率类型，1: 天；2: 周；3: 半月；4: 月；5: 季【fund_profit_rate，天/周/月】
    df = df.drop_duplicates(['datetime'])
    df.set_index('datetime', inplace=True)
    df.dropna(axis=0, how='any', inplace=True)  # 删除表中含有任何NaN的行


    #把df重采样
    #df = resample(df=df, trading_cal=df1, freq=fav_freq)
    print('重采样后：',len(df))
    print(df)

    #保留原始dataframe
    origin_df = df

    # 获取序列中的开始和结束时间
    datestart = df.index[0]
    dateend = df.index[-1]
    strstartdate = datestart.strftime("%Y%m%d")
    strenddate = dateend.strftime("%Y%m%d")

    #避免出现指数还没更新，但净值已更新的情况
    if dateend < enddate:
        return

    print('---Step1: 开始准备数据---')
    #最近净值日期、最近净值
    price_date = origin_df.index[-1]
    cumulative_nav = origin_df.iloc[-1,0]
    cumulative_index = origin_df.iloc[-1,1]
    cumulative_ori_nav = origin_df.iloc[-1,2]
    print('最近净值日期/值: %s and %s ' %(price_date,cumulative_nav))


    # 根据基金更新频率产生对应的N,数据类型， 周（52）， 月（12）， 日（250)
    if fav_freq == 1:
        N = 250  # 日更新
    elif fav_freq == 2:
        N = 52  # 周更新
    elif fav_freq == 3:
        N = 24  # 半周更新
    elif fav_freq == 4:
        N = 12  # 月更新
    elif fav_freq == 5:
        N = 3  # 季度更新
    else:
        N = 250  #没有设置freq默认是天更
    print(N)

    print('开始计算:')
    # 区间收益
    # 前1/3/6收益率
    print('当前日期', dateend)
    m = [1, 3, 6]
    range_return_m = []
    b_range_return_m = []
    cum_returns_m = []
    b_cum_returns_m = []

    for i in range(len(m)):
        pre_result , pre_date = dfmonth_minus(df,dateend, m[i])
        print('pre_date:' , pre_date, 'nowdate:' , dateend)
        print('.....近N月时间.', pre_date)

        if pre_result == 1:
            now_value = df.loc[df.index == dateend]['net_worth'][0]
            old_value = df.loc[df.index == pre_date]['net_worth'][0]
            r = '%.6f' % (((now_value/old_value)-1))
            range_return_m.append(r)
            print (now_value,old_value,r)


            now_value = df.loc[df.index == dateend]['benchmark'][0]
            old_value = df.loc[df.index == pre_date]['benchmark'][0]
            b_r = '%.6f' % (((now_value/old_value)-1))
            print (now_value,old_value,b_r)
            b_range_return_m.append(b_r)


            # 简单收益率
            # print(y_time['net_worth'])
            y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)]
            y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
            y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark'])

            # 计算累积收益率
            y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1
            y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1
            print(y_time)


            cum_returns_m.append(y_time['cum_returns'].iloc[-1])
            b_cum_returns_m.append(y_time['b_cum_returns'].iloc[-1])


    # 最近1/2/3/4/5/10年、今年以来、成立以来、收益率，波动率，夏普，回撤
    y = [1, 2, 3, 4, 5, 10]
    range_return_y = []  #收益率
    b_range_return_y = []
    annual_return_y = []  #年化收益率
    b_annual_return_y = []
    date_y = [] #日期
    range_md_y = []  #回撤
    b_range_md_y = []
    range_md_y_begin = []
    range_md_y_end = []
    b_range_md_y_begin = []
    b_range_md_y_end = []
    sigma_y = []
    b_sigma_y = []
    sharpe_y = []
    b_sharpe_y = []
    alpha_y = []
    beta_y = []
    sortino_y = []
    b_sortino_y = []
    sortion_ratio_y = []
    b_sortion_ratio_y = []

    cum_returns_y = []
    b_cum_returns_y = []

    for i in range(len(y)):
        pre_result , pre_date = dfyear_minus(df,dateend, y[i])
        print('.....近N年时间.', 'pre_date:' , pre_date, 'nowdate:' , dateend)

        if pre_result == -1:
            r = b_r = np.nan
            n_m = b_m = np.nan
        else:
            y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)]

            #基础收益率、基准收益率
            r , b_r = simple_2days_return(y_time , pre_date ,dateend )
            print ('base 基准率：' , pre_date,dateend,r, b_r)

            range_return_y.append(r)
            b_range_return_y.append(b_r)

            #最大回撤、基准最大回撤（回撤值，开始位置，结束位置）
            n_m, n_m_begin, n_m_end = max_drawdown(list(y_time['net_worth']))
            range_md_y.append('%.6f' % (n_m))
            range_md_y_begin.append(y_time.index[n_m_begin])
            range_md_y_end.append(y_time.index[n_m_end])

            b_m, b_n_m_begin, b_n_m_end = max_drawdown(list(y_time['benchmark']))
            b_range_md_y.append('%.6f' % (b_m ))
            b_range_md_y_begin.append(y_time.index[b_n_m_begin])
            b_range_md_y_end.append(y_time.index[b_n_m_end])


            date_y.append(pre_date)

            # 简单收益率
            # print(y_time['net_worth'])
            y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
            y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark'])

            # 计算累积收益率
            y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1
            y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1
            cum_returns_y.append(y_time['cum_returns'].iloc[-1])
            b_cum_returns_y.append(y_time['b_cum_returns'].iloc[-1])


            #计算超额收益率
            y_time.loc[:, 'excess_returns'] = excess_return(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE / N)
            y_time.loc[:, 'b_excess_returns'] = excess_return(y_time.loc[:, 'b_returns'].astype("float64"), BANK_RATE / N)


            #（年化）区间收益率、基准收益率
            print('test_0:', r ,b_r)
            an_r = annual_return(float(r) , y_time ,N)
            b_an_r = annual_return(float(b_r) , y_time , N)
            print('test:', an_r , b_an_r)
            annual_return_y.append('%.6f' % (an_r))
            b_annual_return_y.append('%.6f' % (b_an_r))

            # 夏普比率
            sharpe_y.append('%.6f' %sharpe_ratio(y_time.loc[:, 'excess_returns'], y_time.loc[:, 'returns'].astype("float64"), N))
            b_sharpe_y.append('%.6f' %sharpe_ratio(y_time.loc[:, 'b_excess_returns'], y_time.loc[:, 'b_returns'].astype("float64"), N))
            # 波动率
            sigma_y.append('%.6f' %volatility(y_time.loc[:, 'returns'].astype("float64"), N))
            b_sigma_y.append('%.6f' %volatility(y_time.loc[:, 'b_returns'].astype("float64"), N))
            print(sharpe_y)
            print(sigma_y)
            #alpha/beta
            alpha , beta = alpha_beta (y_time.loc[:, 'returns'].astype("float64") , y_time.loc[:, 'b_returns'].astype("float64") , N)
            alpha_y.append('%.6f' %alpha)
            beta_y.append(('%.6f' % beta))
            #索提诺比率

            n_downside = downside_risk(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE, N)
            b_downside = downside_risk(y_time.loc[:, 'b_returns'].astype("float64"), BANK_RATE, N)
            n_sortino_ratio = sortino_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), n_downside, N)
            b_sortino_ratio = sortino_ratio(y_time.loc[:, 'b_excess_returns'].astype("float64"), b_downside, N)
            sortino_y.append('%.6f' %n_downside)
            b_sortino_y.append('%.6f' %b_downside)
            sortion_ratio_y.append('%.6f' %n_sortino_ratio)
            b_sortion_ratio_y.append('%.6f' % b_sortino_ratio)


    #日/周、收益率/基准收益率
    y_time = df
    print(y_time.loc[:, 'net_worth'])
    y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
    y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark'])
    print('len(y_time):')
    print(fav_freq,len(y_time))
    if fav_freq in [1, 2] and len(y_time)>0 :
        return_1 = '%.6f' % (y_time['returns'].iloc[-1])  # 日收益
        b_return_1 = '%.6f' % (y_time['b_returns'].iloc[-1] )  #日基准收益

        if fav_freq == 1:
            return_d = return_1
            b_return_d = b_return_1

            #计算周收益
            pre_result, pre_date = dfweek_minus(df, dateend, 1)

            if pre_result == 1:
                now_value = df.loc[df.index == dateend]['net_worth'][0]
                old_value = df.loc[df.index == pre_date]['net_worth'][0]
                return_w = '%.6f' % (((now_value / old_value) - 1) )
                print(now_value, old_value, r)
                now_value = df.loc[df.index == dateend]['benchmark'][0]
                old_value = df.loc[df.index == pre_date]['benchmark'][0]
                b_return_w = '%.6f' % (((now_value / old_value) - 1) )
            else:
                return_w = np.nan
                b_return_w = np.nan
        else:
            return_d = np.nan
            b_return_d = np.nan
            return_w = return_1
            b_return_w = b_return_1
    else:
        return_d = np.nan
        b_return_d = np.nan
        return_w = np.nan
        b_return_w = np.nan

    #今年以来收益率/基准收益率、成立以来收益率/基准收益率/年化收益率/年化基准收益率

    #今年以来

    pre_date = datetime.datetime(dateend.year, 1, 1)
    private_count = len(origin_df.loc[origin_df.index>=pre_date])
    location = len(origin_df)  - private_count
    if location<0:
        pre_date = origin_df.index[0]
    else:
        pre_date = origin_df.index[location]

    print(pre_date,dateend)
    short_r, b_short_r = simple_2days_return(origin_df, pre_date, dateend)

    y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)]
    # 简单收益率
    # print(y_time['net_worth'])
    y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
    y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark'])

    # 计算累积收益率
    y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1
    y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1

    short_cum_returns = y_time['cum_returns'].iloc[-1]
    short_cum_returns_b = y_time['b_cum_returns'].iloc[-1]

    #波动率
    short_sigma = '%.6f' % volatility(origin_df.loc[pre_date:origin_df.index[-1], 'returns'].astype("float64"), N)
    #alpha、beta
    if len(origin_df.loc[pre_date:origin_df.index[-1]]) > 1 :
        short_alpha, short_beta = alpha_beta(origin_df.loc[pre_date:origin_df.index[-1],'returns'].astype("float64"), origin_df.loc[pre_date:origin_df.index[-1],'b_returns'].astype("float64"),N)
    else:
        short_alpha = 0
        short_beta = 0


    #成立以来
    y_time = origin_df
    long_r, b_long_r = simple_2days_return(y_time, y_time.index[0], y_time.index[-1])
    # （年化）基础收益率、基准收益率
    an_long_r = annual_return(float(long_r), y_time, N)
    b_an_long_r = annual_return(float(b_long_r), y_time, N)
    #波动率
    long_sigma = '%.6f' % volatility(y_time.loc[:, 'returns'].astype("float64"), N)
    #alpha、beta
    long_alpha, long_beta = alpha_beta (y_time.loc[:, 'returns'].astype("float64") , y_time.loc[:, 'b_returns'].astype("float64") , N)

    # 简单收益率
    # print(y_time['net_worth'])
    y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
    y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark'])

    # 计算累积收益率
    y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1
    y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1

    long_cum_returns =  y_time['cum_returns'].iloc[-1]
    long_cum_returns_b = y_time['b_cum_returns'].iloc[-1]

    #sharperatio_incep  fund_riskadjret_stats  成立以来夏普比例
    y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth'])
    y_time.loc[:, 'excess_returns'] = excess_return(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE / N)
    long_sharpe = '%.6f'%sharpe_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), y_time.loc[:, 'returns'].astype("float64"), N)

    #
    long_downside = downside_risk(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE, N)
    #b_downside = downside_risk(y_time.loc[:, 'b_returns'], BANK_RATE, N)
    long_sortino_ratio = sortino_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), long_downside, N)
    #b_sortino_ratio = sortino_ratio(y_time.loc[:, 'b_excess_returns'], b_downside, N)

    #成立以来最大回撤
    long_n_m, long_n_m_begin, long_n_m_end = max_drawdown(list(y_time['net_worth']))
    long_n_m_begin_date = y_time.index[long_n_m_begin]
    long_n_m_end_date = y_time.index[long_n_m_end]


    print('---Step2：数据准备结束，把数据存入DB---')


    #结果存入fund_performance

    print('结果存入fund_performance')
    #str_id = str(int(time.mktime(datetime.datetime.now().timetuple())))  #Table ID，时间戳，10位
    #str_id = now_to_timestamp(13)
    str_id = xuehuaid()
    str_fund_id = fund_id  #基金ID
    dt_end_date = dateend.strftime('%Y-%m-%d')  #截至日期
    dt_price_date = dateend.strftime('%Y-%m-%d')   #最近累计净值日期
    print(dt_end_date)
    dec_cumulative_nav = cumulative_nav  #最近累计净值


    ts_create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(ts_create_time)

    #开始数据库操作

    pool = PooledDB(pymysql, maxconnections=100, blocking=True, host=origion_env[current_env]['host'],
                    port=int(origion_env[current_env]['port']), user=origion_env[current_env]['user'],
                    password=origion_env[current_env]['password'], database=origion_env[current_env]['database'],
                    charset="utf8")  # 5为连接池里的最少连接数
    conn = pool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur = conn.cursor()

    #如果是指数，则把数据插入fund_nav
    sql ="select id from fund_nav where fund_id='%s' and price_date='%s'" % (fund_id, enddate)
    fetch_count = cur.execute(sql)
    data = cur.fetchall()
    if fetch_count == 0:
        str_id = xuehuaid()
        sql = "INSERT INTO fund_nav(id, fund_id, price_date, nav, cumulative_nav, cumulative_nav_withdrawal, create_time,delete_tag) VALUES ('%s',  '%s', str_to_date('%s','%%Y-%%m-%%d'), %s, %s, %s , '%s', %d)" % (
        str_id, str_fund_id, dt_end_date, dec_cumulative_nav, dec_cumulative_nav,dec_cumulative_nav, ts_create_time, 0)
        print(sql)
        try:
            cur.execute(sql)
            conn.commit()

        except Exception as e:
            print(e.message)
    else:
        #获取id
        str_id = data[0][0]
        #sql = "update fund_nav set fund_id='%s',  price_date='%s', nav='%s',cumulative_nav='%s', cumulative_nav_withdrawal='%s' where id='%s'" % (str_fund_id, dt_price_date, dec_cumulative_nav, dec_cumulative_nav,dec_cumulative_nav, str_id)




    #更新fund_performance
    sql ="select id from fund_performance where fund_id='%s' and end_date='%s'" % (fund_id, enddate)
    print(sql)
    fetch_count = cur.execute(sql)
    data = cur.fetchall()

    if fetch_count == 0:
        str_id = xuehuaid()
        sql = "INSERT INTO fund_performance(id, fund_id, end_date, price_date, cumulative_nav, ret_ytd, ret_ytd_bm1 , ret_incep, ret_incep_bm1, ret_incep_a, ret_incep_bm1_a, ret_cum_ytd, ret_cum_ytd_bm1, ret_cum_incep, ret_cum_incep_bm1, create_time, delete_tag) VALUES ('%s',  '%s', str_to_date('%s','%%Y-%%m-%%d'), str_to_date('%s','%%Y-%%m-%%d'), %s, %s, %s, %s, %s,  %s, %s, %s, %s, %s,  %s, '%s', %d)" % (str_id, str_fund_id, dt_end_date, dt_price_date, dec_cumulative_nav, short_r, b_short_r, long_r, b_long_r,an_long_r, b_an_long_r, short_cum_returns, short_cum_returns_b, long_cum_returns, long_cum_returns_b, ts_create_time, 0)

    else:
        #获取id
        str_id = data[0][0]
        sql = "update fund_performance set fund_id='%s', end_date='%s', price_date='%s', cumulative_nav='%s', ret_ytd='%s', ret_ytd_bm1='%s' , ret_incep='%s', ret_incep_bm1='%s', ret_incep_a='%s', ret_incep_bm1_a='%s',ret_cum_ytd='%s',ret_cum_ytd_bm1='%s',ret_cum_incep='%s',ret_cum_incep_bm1='%s' where id='%s'" % (str_fund_id, dt_end_date, dt_price_date, dec_cumulative_nav, short_r, b_short_r, long_r, b_long_r,an_long_r, b_an_long_r, short_cum_returns, short_cum_returns_b, long_cum_returns, long_cum_returns_b,str_id)




    print(sql)

    try:
        cur.execute(sql)
        conn.commit()

    except Exception as e:
        print(e.message)

    #更新日数据和周数据

    if fav_freq == 1:
        sql = "update fund_performance set ret_1day='%s', ret_1day_bm1 = '%s', ret_1w='%s', ret_1w_bm1='%s' where id='%s'" % (return_d, b_return_d, return_w, b_return_w, str_id)
        print(sql)
        cur.execute(sql)
        conn.commit()
    elif fav_freq == 2:
        sql = "update fund_performance set ret_1w='%s',ret_1w_bm1='%s' where id='%s'" % (return_w, b_return_w, str_id)
        print(sql)
        cur.execute(sql)
        conn.commit()

    #处理月份数据
    print('开始月份数据处理：',range_return_m, cum_returns_m)
    if len(range_return_m) >= 1:
        sql = "update fund_performance set ret_1m='%s', ret_1m_bm1= '%s',ret_cum_1m ='%s', ret_cum_1m_bm1='%s' where id='%s'" % (range_return_m[0],b_range_return_m[0],cum_returns_m[0],b_cum_returns_m[0],str_id)
        print(sql)
        cur.execute(sql)
    if len(range_return_m) >= 2:
        sql = "update fund_performance set ret_3m='%s', ret_3m_bm1= '%s' ,ret_cum_3m ='%s', ret_cum_3m_bm1='%s' where id='%s'" % (range_return_m[1],b_range_return_m[1],cum_returns_m[1],b_cum_returns_m[1], str_id)
        print(sql)
        cur.execute(sql)
    if len(range_return_m) >= 3:
        sql = "update fund_performance set ret_6m=%s, ret_6m_bm1= %s ,ret_cum_6m ='%s', ret_cum_6m_bm1='%s' where id='%s'" % (range_return_m[2], b_range_return_m[2],cum_returns_m[2],b_cum_returns_m[2], str_id)
        print(sql)
        cur.execute(sql)

    #处理年份数据
    if len(date_y) >= 1:
        sql = "update fund_performance set ret_1y='%s', ret_1y_bm1= '%s', ret_cum_1y='%s', ret_cum_1y_bm1='%s' where id='%s'" % (range_return_y[0],b_range_return_y[0],cum_returns_y[0],b_cum_returns_y[0],str_id)
        print(sql)
        cur.execute(sql)
    if len(date_y) >= 2:
        sql = "update fund_performance set ret_2y='%s', ret_2y_bm1= '%s' , ret_2y_a='%s' , ret_2y_bm1_a= '%s',ret_cum_2y='%s', ret_cum_2y_bm1='%s' where id='%s'" % (range_return_y[1],b_range_return_y[1], annual_return_y[1], b_annual_return_y[1], cum_returns_m[1],b_cum_returns_m[1],str_id)
        print(sql)
        cur.execute(sql)
    if len(date_y) >= 3:
        sql = "update fund_performance set ret_3y='%s', ret_3y_bm1= '%s' , ret_3y_a='%s' , ret_3y_bm1_a= '%s',ret_cum_3y='%s', ret_cum_3y_bm1='%s' where id='%s'" % (range_return_y[2],b_range_return_y[2], annual_return_y[2], b_annual_return_y[2], cum_returns_m[2],b_cum_returns_m[2],str_id)
        print(sql)
        cur.execute(sql)
    if len(date_y) >= 4:
        sql = "update fund_performance set ret_4y='%s', ret_4y_bm1= '%s' , ret_4y_a='%s' , ret_4y_bm1_a= '%s',ret_cum_4y='%s', ret_cum_4y_bm1='%s'  where id='%s'" % (range_return_y[3],b_range_return_y[3], annual_return_y[3], b_annual_return_y[3], cum_returns_y[3],b_cum_returns_y[3],str_id)
        print(sql)
        cur.execute(sql)
    if len(date_y) >= 5:
        sql = "update fund_performance set ret_5y='%s', ret_5y_bm1= '%s' , ret_5y_a='%s' , ret_5y_bm1_a= '%s',ret_cum_5y='%s', ret_cum_5y_bm1='%s'  where id='%s'" % (range_return_y[4],b_range_return_y[4], annual_return_y[4], b_annual_return_y[4], cum_returns_y[4],b_cum_returns_y[4], str_id)
        print(sql)
        cur.execute(sql)

    conn.commit()


    #结果存入fund_risk_stats

    print('结果存入fund_risk_stats')

    sql ="select id from fund_risk_stats where fund_id='%s' and end_date='%s'" % (fund_id, enddate)
    print(sql)
    fetch_count = cur.execute(sql)
    data = cur.fetchall()

    if fetch_count == 0:
        str_id = xuehuaid()
        sql = "INSERT INTO fund_risk_stats(id, fund_id, end_date, create_time, delete_tag) VALUES ('%s',  '%s', str_to_date('%s','%%Y-%%m-%%d'),  '%s', %d)" % (str_id, str_fund_id, dt_end_date, ts_create_time, 0)

    else:
        #获取id
        str_id = data[0][0]
        sql = "update fund_risk_stats set fund_id='%s', end_date='%s' where id='%s'" % (str_fund_id, dt_end_date, str_id)

    print(sql)

    try:
        cur.execute(sql)
        conn.commit()

    except Exception as e:
        print(e.message)

    #处理年份数据

    if len(sigma_y) >= 1:
        int_range_month = month_differ(range_md_y_begin[0],range_md_y_end[0])
        sql = "update fund_risk_stats set stddev_1y='%s', alpha_1y = '%s' , beta_1y = '%s' , maxdrawdown_1y= '%s' , maxdrawdown_months_1y = %s, maxdrawdown_peakdate_1y = '%s' , maxdrawdown_valleydate_1y = '%s' where id='%s'" % (sigma_y[0],alpha_y[0],beta_y[0], range_md_y[0],int_range_month,range_md_y_begin[0], range_md_y_end[0],str_id)
        print(sql)
        cur.execute(sql)
    if len(sigma_y) >= 2:
        int_range_month = month_differ(range_md_y_begin[1],range_md_y_end[1])
        sql = "update fund_risk_stats set stddev_2y='%s', alpha_2y = '%s' , beta_2y = '%s' , maxdrawdown_2y= '%s' , maxdrawdown_months_2y = %s, maxdrawdown_peakdate_2y = '%s' , maxdrawdown_valleydate_2y = '%s' where id='%s'" % (sigma_y[1],alpha_y[1],beta_y[1], range_md_y[1],int_range_month,range_md_y_begin[1], range_md_y_end[1],str_id)
        print(sql)
        cur.execute(sql)
    if len(sigma_y) >= 3:
        int_range_month = month_differ(range_md_y_begin[2],range_md_y_end[2])
        sql = "update fund_risk_stats set stddev_3y='%s', alpha_3y = '%s' , beta_3y = '%s' , maxdrawdown_3y= '%s' , maxdrawdown_months_3y = %s, maxdrawdown_peakdate_3y = '%s' , maxdrawdown_valleydate_3y = '%s' where id='%s'" % (sigma_y[2],alpha_y[2],beta_y[2], range_md_y[2],int_range_month,range_md_y_begin[2], range_md_y_end[2],str_id)
        print(sql)
        cur.execute(sql)
    if len(sigma_y) >= 4:
        int_range_month = month_differ(range_md_y_begin[3],range_md_y_end[3])
        sql = "update fund_risk_stats set stddev_4y='%s', alpha_4y = '%s' , beta_4y = '%s' , maxdrawdown_4y= '%s' , maxdrawdown_months_4y = %s, maxdrawdown_peakdate_4y = '%s' , maxdrawdown_valleydate_4y = '%s' where id='%s'" % (sigma_y[3],alpha_y[3],beta_y[3], range_md_y[3],int_range_month,range_md_y_begin[3], range_md_y_end[3],str_id)
        print(sql)
        cur.execute(sql)
    if len(sigma_y) >= 5:
        int_range_month = month_differ(range_md_y_begin[4],range_md_y_end[4])
        sql = "update fund_risk_stats set stddev_5y='%s', alpha_5y = '%s' , beta_5y = '%s' , maxdrawdown_5y= '%s' , maxdrawdown_months_5y = %s, maxdrawdown_peakdate_5y = '%s' , maxdrawdown_valleydate_5y = '%s' where id='%s'" % (sigma_y[4],alpha_y[4],beta_y[4], range_md_y[4],int_range_month,range_md_y_begin[4], range_md_y_end[4],str_id)
        print(sql)
        cur.execute(sql)
    if len(sigma_y) >= 6:
        int_range_month = month_differ(range_md_y_begin[5],range_md_y_end[5])
        sql = "update fund_risk_stats set stddev_10y='%s', alpha_10y = '%s' , beta_10y = '%s' , maxdrawdown_10y= '%s' , maxdrawdown_months_10y = %s, maxdrawdown_peakdate_10y = '%s' , maxdrawdown_valleydate_10y = '%s' where id='%s'" % (sigma_y[5],alpha_y[5],beta_y[5], range_md_y[5],int_range_month,range_md_y_begin[5], range_md_y_end[5],str_id)
        print(sql)
        cur.execute(sql)

    #成立以来最大回撤
    int_range_month = month_differ(long_n_m_begin_date, long_n_m_end_date)
    sql = "update fund_risk_stats set maxdrawdown_incep= '%s', maxdrawdown_months_incep = '%s' , maxdrawdown_peakdate_incep = '%s', maxdrawdown_valleydate_incep = '%s'  where id='%s'" % (
    long_n_m, int_range_month, long_n_m_begin_date, long_n_m_end_date, str_id)
    print(sql)
    cur.execute(sql)

    #成立以来/今年回来，波动率
    sql = "update fund_risk_stats set stddev_ytd= '%s', stddev_incep = '%s'  where id='%s'" % (
    short_sigma, long_sigma, str_id)
    print(sql)
    cur.execute(sql)

    #成立以来/今年以来，alpha/beta
    sql = "update fund_risk_stats set alpha_ytd= '%s', alpha_incep = '%s', beta_ytd= '%s', beta_incep = '%s'  where id='%s'" % (
    short_alpha, long_alpha, short_beta, long_beta, str_id)
    print(sql)
    cur.execute(sql)


    conn.commit()

    #结果存入fund_riskadjret_stats

    print('结果存入fund_riskadjret_stats')

    sql ="select id from fund_riskadjret_stats where fund_id='%s' and end_date='%s'" % (fund_id, enddate)
    fetch_count = cur.execute(sql)
    data = cur.fetchall()

    if fetch_count == 0:
        str_id = xuehuaid()
        sql = "INSERT INTO fund_riskadjret_stats(id, fund_id, end_date, sharperatio_incep, create_time, delete_tag) VALUES ('%s',  '%s', str_to_date('%s','%%Y-%%m-%%d'),  '%s' ,'%s', %d)" % (str_id, str_fund_id, dt_end_date, long_sharpe, ts_create_time, 0)

    else:
        #获取id
        str_id = data[0][0]
        sql = "update fund_riskadjret_stats set fund_id='%s', end_date='%s' ,sharperatio_incep ='%s' where id='%s'" % (str_fund_id, dt_end_date, long_sharpe , str_id)

    print(sql)

    try:
        cur.execute(sql)
        conn.commit()

    except Exception as e:
        print(e.message)

    #处理年份数据

    if len(sharpe_y) >= 1:
        sql = "update fund_riskadjret_stats set sharperatio_1y='%s', sortinoratio_1y='%s' , sortinoratio_MAR_1y='%s' where id='%s'" % (sharpe_y[0],sortino_y[0],sortion_ratio_y[0],str_id)
        print(sql)
        cur.execute(sql)
    if len(sharpe_y) >= 2:
        sql = "update fund_riskadjret_stats set sharperatio_2y='%s' , sortinoratio_2y='%s', sortinoratio_MAR_2y='%s' where id='%s'" % (sharpe_y[1],sortino_y[1],sortion_ratio_y[1],str_id)
        print(sql)
        cur.execute(sql)
    if len(sharpe_y) >= 3:
        sql = "update fund_riskadjret_stats set sharperatio_3y='%s', sortinoratio_3y='%s' ,sortinoratio_MAR_3y='%s' where id='%s'" % (sharpe_y[2],sortino_y[2],sortion_ratio_y[2],str_id)
        print(sql)
        cur.execute(sql)
    if len(sharpe_y) >= 4:
        sql = "update fund_riskadjret_stats set sharperatio_4y='%s', sortinoratio_4y='%s', sortinoratio_MAR_4y='%s' where id='%s'" % (sharpe_y[3],sortino_y[3],sortion_ratio_y[3],str_id)
        print(sql)
        cur.execute(sql)
    if len(sharpe_y) >= 5:
        sql = "update fund_riskadjret_stats set sharperatio_5y='%s', sortinoratio_5y='%s', sortinoratio_MAR_5y='%s' where id='%s'" % (sharpe_y[4],sortino_y[4],sortion_ratio_y[4],str_id)
        print(sql)
        cur.execute(sql)

    #成立以来夏普，索提诺
    int_range_month = month_differ(long_n_m_begin_date, long_n_m_end_date)
    sql = "update fund_riskadjret_stats set sharperatio_incep= '%s', sortinoratio_incep = '%s' , sortinoratio_MAR_incep = '%s'  where id='%s'" % (long_sharpe, long_downside, long_sortino_ratio, str_id)
    print(sql)
    cur.execute(sql)

    conn.commit()

    #更新fund_count
    sql ="select id , price_date from fund_count where fund_id='%s' " % (fund_id)
    fetch_count = cur.execute(sql)

    data = cur.fetchall()

    if fetch_count == 0:
        str_id = xuehuaid()
        sql = "insert into fund_count(id, fund_id , strategy, data_sources, status, create_time, delete_tag) values('%s','%s', '%s', '%s', '%s', '%s', 0)" % (str_id, str_fund_id, strategy, data_source, status, ts_create_time)
        print(sql)
        cur.execute(sql)
        conn.commit()
        fund_count_date = datetime.datetime.strptime("1900-01-01", "%Y-%m-%d").date()
    else:
    #更新收益率
        fund_count_date = data[0][1]

    if fund_count_date is None:
        fund_count_date = datetime.datetime.strptime("1900-01-01", "%Y-%m-%d").date()
    print('当前日期：',enddate)
    print('之前日期：',fund_count_date)

    dt_end_date = datetime.datetime.strptime(strenddate, "%Y%m%d").date()
    if dt_end_date >= fund_count_date:

        #更新单位净值
        sql = "update fund_count set net_nav='%s' , data_sources ='%s', end_date = '%s', price_date='%s',status ='%s'  where fund_id = '%s'" % (cumulative_ori_nav, data_source, enddate, enddate, status, fund_id )
        print(sql)

        sql1 ="update fund_count a inner join fund_performance b  on a.fund_id=b.fund_id set a.end_date=b.end_date, a.price_date=b.price_date, a.cumulative_nav=b.cumulative_nav,a.ret_1day=b.ret_1day, a.ret_1day_bm1=b.ret_1day_bm1, a.ret_1w=b.ret_1w, a.ret_1w_bm1=b.ret_1w_bm1, a.ret_1m=b.ret_1m, a.ret_1m_bm1=b.ret_1m_bm1, a.ret_3m=b.ret_3m, a.ret_3m_bm1=b.ret_3m_bm1, a.ret_6m=b.ret_6m, a.ret_6m_bm1=b.ret_6m_bm1, a.ret_1y=b.ret_1y, a.ret_1y_bm1=b.ret_1y_bm1, a.ret_2y=b.ret_2y, a.ret_2y_bm1=b.ret_2y_bm1, a.ret_2y_a=b.ret_2y_a, a.ret_2y_bm1_a=b.ret_2y_bm1_a, a.ret_3y=b.ret_3y, a.ret_3y_bm1=b.ret_3y_bm1, a.ret_3y_a=b.ret_3y_a, a.ret_3y_bm1_a=b.ret_3y_bm1_a,a.ret_4y=b.ret_4y, a.ret_4y_bm1=b.ret_4y_bm1, a.ret_4y_a=b.ret_4y_a, a.ret_4y_bm1_a=b.ret_4y_bm1_a,a.ret_5y=b.ret_5y, a.ret_5y_bm1=b.ret_5y_bm1, a.ret_5y_a=b.ret_5y_a, a.ret_5y_bm1_a=b.ret_5y_bm1_a, a.ret_ytd=b.ret_ytd, a.ret_ytd_bm1=b.ret_ytd_bm1, a.ret_incep=b.ret_incep, a.ret_incep_bm1=b.ret_incep_bm1, a.ret_incep_a=b.ret_incep_a, a.ret_incep_bm1_a=b.ret_incep_bm1_a  where b.fund_id ='%s' and b.price_date='%s'" % (fund_id, enddate)
        print(sql1)


        try:
            cur.execute(sql)
            cur.execute(sql1)
            conn.commit()

        except Exception as e:
            print(e.message)

        #更新年化波动和最大回撤
        sql ="UPDATE fund_count fc inner join fund_risk_stats t1 on fc.fund_id=t1.fund_id and fc.end_date=t1.end_date and t1.fund_id='%s' and t1.end_date='%s' SET fc.stddev_1y = t1.stddev_1y, fc.stddev_2y = t1.stddev_2y, fc.stddev_3y = t1.stddev_3y, fc.stddev_4y = t1.stddev_4y, fc.stddev_5y = t1.stddev_5y, fc.stddev_10y = t1.stddev_10y, fc.stddev_ytd = t1.stddev_ytd, fc.stddev_incep = t1.stddev_incep, fc.maxdrawdown_1y = t1.maxdrawdown_1y, fc.maxdrawdown_2y = t1.maxdrawdown_2y, fc.maxdrawdown_3y = t1.maxdrawdown_3y, fc.maxdrawdown_4y = t1.maxdrawdown_4y, fc.maxdrawdown_5y = t1.maxdrawdown_5y, fc.maxdrawdown_10y = t1.maxdrawdown_10y, fc.maxdrawdown_incep = t1.maxdrawdown_incep" % (fund_id, enddate)

        print(sql)
        #更新夏普比例
        sql1 = "UPDATE fund_count fc inner join fund_riskadjret_stats t2 on fc.fund_id=t2.fund_id and fc.end_date=t2.end_date and t2.fund_id='%s' and t2.end_date='%s' SET fc.sharperatio_1y=t2.sharperatio_1y, fc.sharperatio_2y=t2.sharperatio_2y, fc.sharperatio_3y=t2.sharperatio_3y , fc.sharperatio_4y=t2.sharperatio_4y, fc.sharperatio_5y=t2.sharperatio_5y , fc.sharperatio_incep=t2.sharperatio_incep " % (fund_id, enddate)
        print(sql1)

        try:
            cur.execute(sql)
            cur.execute(sql1)
            conn.commit()
            print("1条已更新,ID:", cur.lastrowid)

        except Exception as e:
            print(e.message)

    cur.close()
    conn.close()