DolphinDB/modules/returnCalculator.dos

module fundit::returnCalculator

use fundit::sqlUtilities
use fundit::operationDataPuller
use fundit::performanceDataPuller


/*
 *  通用月收益计算
 * 
 *  @param entity_info <TABLE>: [COLUMNS] entity_id, price_date, inception_date, ini_value
 *  @param nav <TABLE>: [COLUMNS] entity_id, price_date, cumulative_nav
 * 
 */
def cal_monthly_returns_by_nav(entity_info, mutable nav) {

    tb_rets = null;

    if(nav.isVoid() || nav.size() == 0 || entity_info.isVoid() || entity_info.size() == 0 ) return null;

    // 所有月末日期和净值
    tb_monthly_nav = SELECT entity_id, price_date.month().last() AS end_date, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                     FROM nav.sortBy!(['entity_id', 'price_date'], [1, 1])
                     GROUP BY entity_id, price_date.month();

    // 筛掉成立日之前的净值
    tb_monthly_nav = SELECT n.entity_id, end_date, price_date, cumulative_nav 
                     FROM tb_monthly_nav n
                     INNER JOIN entity_info ei ON n.entity_id = ei.entity_id
                     WHERE n.price_date >= ei.inception_date;

    // 补回来成立日初始净值
    INSERT INTO tb_monthly_nav
        SELECT entity_id, inception_date.month(), inception_date, ini_value
        FROM entity_info
        WHERE inception_date IS NOT NULL;

    if(tb_monthly_nav.isVoid() || tb_monthly_nav.size() == 0) { return tb_rets; }

    // 计算月收益
    tb_rets = SELECT entity_id, end_date, price_date, cumulative_nav, cumulative_nav.ratios() - 1 AS ret
              FROM tb_monthly_nav.sortBy!(['entity_id', 'price_date'], [1, 1])
              CONTEXT BY entity_id;

    // the records without return calculated but do have nav are still useful for some calculations (e.g. max drawdown)
    return ( SELECT * FROM tb_rets WHERE cumulative_nav > 0 );
}

/*
 *  根据最新更新的净值计算收益，并与数据库历史收益合并为完整收益, 数据源是MySQL
 *  
 *  @param entity_type <STRING>:
 *  @param entity_info <TABLE>: COLUMN NEED entity_id, inception_date, benchmark_id, ini_value, price_date
 *  @param start_date <DATETIME>: 净值(datasource='nav')、收益起始日（datasource='perf')、净值更新的最新日期（datasource='mix')
 *  @param data_source <STRING>: nav(nav table), perf(xxx_performance table), mix(new updated nav + old performance table ret_1m)
 *  
 * 
 */
def mix_monthly_returns(entity_type, entity_info) {

    ret = null;

    very_old_day = 1990.01.01;

    end_day = today();

    s_json = (SELECT entity_id AS sec_id, price_date FROM entity_info).toStdJson();
            
     // 取基金组合在包括各自的某净值日期的前值及之后的所有净值
     tb_nav = get_nav_for_return_calculation(entity_type, 'm', s_json);
     tb_nav.rename!('sec_id', 'entity_id');

     // 计算某净值日期所在月份及之后的所有月收益
     ret = cal_monthly_returns_by_nav(entity_info, tb_nav);

     if(ret.isVoid() || ret.size() == 0) return ret;

    // 筛掉引入的前值，这些记录用来计算第一期收益后就不再有用
    // 不知道为什么 delete ret from ej(ret, tb_entities, 'entity_id') where ret.price_date < tb_entities.price_date 会报错
    ret = SELECT ret.* FROM ej(ret, entity_info, 'entity_id')
                       WHERE ret.price_date >= entity_info.price_date;

    // 取数据库中的所有历史收益
    historical_rets = get_monthly_ret(entity_type, entity_info.entity_id, very_old_day, end_day, true);

    // MIX 将新NAV计算的收益和数据库中的历史收益合并，相同月份时用新计算的收益代替
    INSERT INTO ret
        SELECT entity_id, end_date.temporalParse('yyyy-MM'), price_date, nav, ret
        FROM historical_rets h
        WHERE NOT EXISTS ( SELECT * FROM ret WHERE entity_id = historical_rets.entity_id AND end_date = historical_rets.end_date.temporalParse('yyyy-MM') );

    // 过滤掉成立日之前的收益
    return SELECT ret.* 
           FROM ret INNER JOIN entity_info ei ON ret.entity_id = ei.entity_id
           WHERE ret.end_date >= ei.inception_date.month()
           ORDER BY entity_id, end_date, price_date;
}


/*
 *  根据基金净值序列计算周收益序列
 * 
 *  Create:  20240907                                                  Joey
 *                    TODO: missing pulling data from local
 *                    
 *  @param entity_type <STRING>: NAV universe, 'HF','MF','PF','EQ'... defined in get_nav_table_description()
 *  @param entity_info <TABLE>: COLUMN NEED entity_id, price_date, inception_date
 *  
 *  
 */
def cal_weekly_returns(entity_type, entity_info){

    tb_rets_1w = null;

    if(!(entity_type IN ['HF', 'MF', 'PF', 'MI', 'FI', 'FA'])) return tb_rets_1w;
    
    // 将每支证券ID+某个日期转为JSON用于调用sp
    s_json = (SELECT entity_id AS sec_id, price_date FROM entity_info).toStdJson();
            
     // 取基金组合在包括各自的某净值日期的前值及之后的所有净值
     tb_nav = get_nav_for_return_calculation(entity_type, 'w', s_json);
     tb_nav.rename!('sec_id', 'entity_id');

    UPDATE tb_nav SET year_week = get_year_week(price_date);
    
    // 这里选最简单的计算方式：不补任何净值空洞，净值前值日期不做任何限制
    // TODO: 可以考虑将月收益也改为这种方式
    tb_rets_1w = SELECT entity_id, year_week, price_date, cumulative_nav, cumulative_nav.ratios()-1 AS ret_1w 
                 FROM tb_nav
                 ORDER BY entity_id, year_week;
    
    return SELECT * FROM tb_rets_1w WHERE ret_1w > -1;
}


/*
 *  批量计算区间收益
 *  TODO: mySQL version 向前取4天，向后不做限制。这里的逻辑是向前取4个交易日, 遇到大长假后可能取不到数据
 *        【老程序，可优化】
 *
 *  get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
 *
 */
def get_trailing_return(table_last_nav, table_nav, duration, return_column_name) {

    tb =  SELECT a.entity_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret
          FROM table_last_nav a
          INNER JOIN table_nav b ON a.entity_id = b.entity_id 
          WHERE b.price_date <= a.price_date.datetimeAdd(duration)
            AND b.price_date >= a.price_date.datetimeAdd(duration).datetimeAdd(-4d).businessDay()
          GROUP BY a.entity_id
          ORDER BY entity_id;

    tb.rename!("ret", return_column_name);

    return tb;
}

	
/*
 *  批量计算区间收益
 *  TODO: mySQL version 向前取4天，向后不做限制。这里的逻辑是向前取4个交易日, 遇到大长假后可能取不到数据
 *
 *  
 *
 */
def get_trailing_return2(entity_type, tb_last_nav, duration, return_column_name) {

    // 在往前减duration代表的区间后往后加1天才能取前值
	s_json = (SELECT sec_id, price_date.temporalAdd(duration).temporalAdd(1d) AS price_date FROM tb_last_nav).toStdJson();
	tb_pre_nav = get_nav_for_return_calculation(entity_type, 'd', s_json, pre_nav_incld=1);

    tb = SELECT n.sec_id AS entity_id, n.price_date, n.cumulative_nav \ p.cumulative_nav - 1 AS ret
         FROM tb_last_nav n
         INNER JOIN tb_pre_nav p ON n.sec_id = p.sec_id
         WHERE n.cumulative_nav > 0
           AND p.cumulative_nav > 0
           AND n.price_date.temporalAdd(duration) <= p.price_date.temporalAdd(4B);

    tb.rename!("ret", return_column_name);

    return tb;
}

/*
 *  批量计算区间最大回撤
 *  
 *  NOTE: 简单起见，和计算收益不同，这里并不往前找可用净值
 *
 */
def get_trailing_max_drawdown(entity_type, tb_last_nav, duration, drawdown_column_name) {

    // 取区间净值
	s_json = (SELECT sec_id, price_date.temporalAdd(duration) AS price_date FROM tb_last_nav).toStdJson();
	tb_pre_nav = get_nav_for_return_calculation(entity_type, 'd', s_json, pre_nav_incld=0);

    tb = SELECT n.sec_id, cumulative_nav.maxDrawdown() AS max_drawdown
         FROM tb_last_nav n
         GROUP BY n.sec_id;

    tb.rename!(['sec_id', 'max_drawdown'], ['entity_id', drawdown_column_name]);

    return tb;
}

/*
 *  批量计算最新收益
 *
 *  @param entity_info <TABLE>: [COLUMNS] entity_id, price_date, inception_date, benchmark_id, ini_value 
 *
 *  NOTE: 取消了最大回撤和卡玛
 *
 */
def cal_latest_performance2(entity_type, entity_info, isFromMySQL) {

    // 取最新净值
    s_json = (SELECT entity_id AS sec_id, today() AS price_date FROM entity_info).toStdJson();
    tb_last_nav = get_nav_for_return_calculation(entity_type, 'd', s_json, pre_nav_incld=1);

    if(tb_last_nav.isVoid() || tb_last_nav.size() == 0) return;
    
    // 近1期收益，对应mySQL fund_latest_nav_performance 中的 net_value_change
    s_json = (SELECT sec_id, price_date FROM tb_last_nav).toStdJson();
    tb_pre_nav = get_nav_for_return_calculation(entity_type, 'd', s_json, pre_nav_incld=1);

    tb_last_return = SELECT n.sec_id AS entity_id, n.price_date, p.price_date AS pre_price_date, n.cumulative_nav, n.nav, n.cumulative_nav \ p.cumulative_nav - 1 AS net_value_change
                     FROM tb_last_nav n
                     INNER JOIN tb_pre_nav p ON n.sec_id = p.sec_id
                     WHERE p.cumulative_nav > 0
                       AND n.cumulative_nav > 0;
    
    // 近1交易日收益
    tb_1d = SELECT entity_id, price_date, net_value_change AS ret_1d
            FROM tb_last_return
            WHERE price_date = pre_price_date.temporalAdd(1d).businessDay();

    
    // 近1周、1/3/6月、1/2/3/4/5/10年收益
    tb_1w = get_trailing_return2(entity_type, tb_last_nav, -7d, "ret_1w");
    tb_1m = get_trailing_return2(entity_type, tb_last_nav, -1M, "ret_1m");
    tb_3m = get_trailing_return2(entity_type, tb_last_nav, -3M, "ret_3m");
    tb_6m = get_trailing_return2(entity_type, tb_last_nav, -6M, "ret_6m");
    tb_1y = get_trailing_return2(entity_type, tb_last_nav, -1y, "ret_1y");
    tb_2y = get_trailing_return2(entity_type, tb_last_nav, -2y, "ret_2y");
    tb_3y = get_trailing_return2(entity_type, tb_last_nav, -3y, "ret_3y");
    tb_4y = get_trailing_return2(entity_type, tb_last_nav, -4y, "ret_4y");
    tb_5y = get_trailing_return2(entity_type, tb_last_nav, -5y, "ret_5y");
    tb_10y = get_trailing_return2(entity_type, tb_last_nav, -10y, "ret_10y");

    // ytd return
    s_json = (SELECT sec_id, price_date.yearBegin() AS price_date FROM tb_last_nav).toStdJson();
    tb_pre_nav = get_nav_for_return_calculation(entity_type, 'd', s_json, pre_nav_incld=1);
    tb_ytd = SELECT n.sec_id AS entity_id, n.price_date, n.cumulative_nav \ p.cumulative_nav - 1 AS ret_ytd
             FROM tb_last_nav n
             INNER JOIN tb_pre_nav p ON n.sec_id = p.sec_id
             WHERE p.cumulative_nav > 0
               AND n.cumulative_nav > 0
               AND n.price_date.yearBegin() <= p.price_date.temporalAdd(4B);
    
    // since inception return
    tb_incep = SELECT a.sec_id AS entity_id, a.price_date, -1 + cumulative_nav \ ini_value AS ret_incep, fi.inception_date
               FROM tb_last_nav a
               INNER JOIN entity_info fi ON a.sec_id = fi.entity_id
    
    // annulized since reception return following GIPS rule
    UPDATE tb_incep SET ret_incep_a = iif((price_date-inception_date)<=365, ret_incep, (1 + ret_incep).pow(365.25\(price_date-inception_date)) - 1)
    UPDATE tb_incep SET ret_incep_a_all = ret_incep_a,
                        ret_incep_a_gips = ret_incep_a
/*
    // 最大回撤， max_drawdown_incep 太耗流量会导致 out-of-memory 错误
    tb_drawdown_1m = get_trailing_max_drawdown(entity_type, tb_last_nav, -1M, "drawdown_1m");
    tb_drawdown_3m = get_trailing_max_drawdown(entity_type, tb_last_nav, -3M, "drawdown_3m");
    tb_drawdown_1y = get_trailing_max_drawdown(entity_type, tb_last_nav, -1y, "drawdown_1y");
*/

    tb_rets = SELECT a.entity_id, a.price_date.datetimeFormat("yyyy-MM") AS end_date, a.price_date, a.pre_price_date, a.nav, a.cumulative_nav,
                     a.net_value_change, d1.ret_1d, w1.ret_1w, m1.ret_1m, m3.ret_3m, m6.ret_6m,
                     y1.ret_1y, y2.ret_2y, y3.ret_3y, y4.ret_4y, y5.ret_5y, y10.ret_10y,
                     ytd.ret_ytd, incep.ret_incep, incep.ret_incep_a, incep.ret_incep_a_all, incep.ret_incep_a_gips,
                     double(NULL) AS maxdrawdown_1m, double(NULL) AS maxdrawdown_3m, double(NULL) AS maxdrawdown_1y,
                     double(NULL) AS maxdrawdown_incep,
                     double(NULL) AS calmarratio_incep
              FROM tb_last_return a
              LEFT JOIN tb_1d d1 ON a.entity_id = d1.entity_id
              LEFT JOIN tb_1w w1 ON a.entity_id = w1.entity_id
              LEFT JOIN tb_1m m1 ON a.entity_id = m1.entity_id
              LEFT JOIN tb_3m m3 ON a.entity_id = m3.entity_id
              LEFT JOIN tb_6m m6 ON a.entity_id = m6.entity_id
              LEFT JOIN tb_1y y1 ON a.entity_id = y1.entity_id
              LEFT JOIN tb_2y y2 ON a.entity_id = y2.entity_id
              LEFT JOIN tb_3y y3 ON a.entity_id = y3.entity_id
              LEFT JOIN tb_4y y4 ON a.entity_id = y4.entity_id
              LEFT JOIN tb_5y y5 ON a.entity_id = y5.entity_id
              LEFT JOIN tb_10y y10 ON a.entity_id = y10.entity_id
              LEFT JOIN tb_ytd ytd ON a.entity_id = ytd.entity_id
              LEFT JOIN tb_incep incep ON a.entity_id = incep.entity_id
              ORDER BY a.entity_id

    // 忽略掉非GIPS标准的所有年化收益字段（包括ytd_a）
    UPDATE tb_rets SET ret_1y_a = ret_1y, ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1,
                       ret_4y_a = (1 + ret_4y).pow(1\4) - 1, ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1


    return tb_rets

}


/*
 *  批量计算最新收益
 *
   @param entity_info <TABLE>: [COLUMNS] entity_id, price_date, inception_date, benchmark_id, ini_value 
 *
 */
def cal_latest_performance(entity_type, entity_info, isFromMySQL) {

    // 用于保证老基金也能取到所有历史净值
    very_old_price_date = 1990.01.01

    tb_nav = get_nav_by_price_date(entity_type, entity_info.entity_id, very_old_price_date, isFromMySQL)
    
    tb_last_nav = SELECT entity_id, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                  FROM tb_nav
                  GROUP BY entity_id
                  ORDER BY entity_id
    
    // 近1期收益，对应mySQL fund_latest_nav_performance 中的 net_value_change
    // 因为是倒序，所以算出来的 ratios() = n0 / n1, 要把它改换成 n1 / n0 - 1 的收益
    tb_last_return = SELECT TOP 2 entity_id, price_date.first() AS price_date, price_date AS pre_preice_date,
                                  nav.first() AS nav, cumulative_nav.first() AS cumulative_nav, 1\cumulative_nav.ratios() - 1 AS net_value_change
                     FROM ( SELECT * FROM tb_nav ORDER BY price_date DESC )
                     CONTEXT BY entity_id
                     ORDER BY entity_id
    tb_last_return = SELECT * FROM tb_last_return WHERE net_value_change IS NOT NULL


    // 近1交易日收益
    tb_1d = SELECT a.entity_id, a.price_date, a.cumulative_nav \ b.cumulative_nav - 1 AS ret_1d
            FROM tb_last_nav a
            INNER JOIN tb_nav b ON a.entity_id = b.entity_id AND b.price_date = a.price_date.datetimeAdd(-1d).businessDay()
            ORDER BY entity_id

    // 近1周、1/3/6月、1/2/3/4/5/10年收益
    tb_1w = get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
    tb_1m = get_trailing_return(tb_last_nav, tb_nav, -1M, "ret_1m")
    tb_3m = get_trailing_return(tb_last_nav, tb_nav, -3M, "ret_3m")
    tb_6m = get_trailing_return(tb_last_nav, tb_nav, -6M, "ret_6m")
    tb_1y = get_trailing_return(tb_last_nav, tb_nav, -1y, "ret_1y")
    tb_2y = get_trailing_return(tb_last_nav, tb_nav, -2y, "ret_2y")
    tb_3y = get_trailing_return(tb_last_nav, tb_nav, -3y, "ret_3y")
    tb_4y = get_trailing_return(tb_last_nav, tb_nav, -4y, "ret_4y")
    tb_5y = get_trailing_return(tb_last_nav, tb_nav, -5y, "ret_5y")
    tb_10y = get_trailing_return(tb_last_nav, tb_nav, -10y, "ret_10y")

    // ytd return
    tb_ytd =  SELECT a.entity_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret_ytd
              FROM tb_last_nav a
              INNER JOIN tb_nav b ON a.entity_id = b.entity_id 
              WHERE b.price_date < a.price_date.yearBegin()
                AND b.price_date >= a.price_date.yearBegin().datetimeAdd(-4d)
              GROUP BY a.entity_id

    // since inception return
    tb_incep = SELECT a.entity_id, a.price_date, -1 + cumulative_nav \ ini_value AS ret_incep, fi.inception_date
               FROM tb_last_nav a
               INNER JOIN entity_info fi ON a.entity_id = fi.entity_id
    
    // annulized since reception return following GIPS rule
    UPDATE tb_incep SET ret_incep_a = iif((price_date-inception_date)<=365, ret_incep, (1 + ret_incep).pow(365.25\(price_date-inception_date)) - 1)
    UPDATE tb_incep SET ret_incep_a_all = ret_incep_a,
                        ret_incep_a_gips = ret_incep_a

    // 最大回撤
    tb_drawdown_1m = SELECT a.entity_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_1m
                     FROM tb_last_return a
                     INNER JOIN tb_nav b ON a.entity_id = b.entity_id
                     WHERE b.price_date >= a.price_date.datetimeAdd(-1M)
                     GROUP BY a.entity_id

    tb_drawdown_3m = SELECT a.entity_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_3m
                     FROM tb_last_return a
                     INNER JOIN tb_nav b ON a.entity_id = b.entity_id
                     WHERE b.price_date >= a.price_date.datetimeAdd(-3M)
                     GROUP BY a.entity_id

    tb_drawdown_1y = SELECT a.entity_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_1y
                     FROM tb_last_return a
                     INNER JOIN tb_nav b ON a.entity_id = b.entity_id
                     WHERE b.price_date >= a.price_date.datetimeAdd(-1y)
                     GROUP BY a.entity_id
                     
    tb_drawdown_incep = SELECT entity_id, max( 1 - cumulative_nav \ cumulative_nav.cummax() ) AS drawdown_incep
                        FROM tb_nav GROUP BY entity_id

    tb_rets = SELECT a.entity_id, a.price_date.datetimeFormat("yyyy-MM") AS end_date, a.price_date, a.pre_preice_date, a.nav, a.cumulative_nav,
                     a.net_value_change, d1.ret_1d, w1.ret_1w, m1.ret_1m, m3.ret_3m, m6.ret_6m,
                     y1.ret_1y, y2.ret_2y, y3.ret_3y, y4.ret_4y, y5.ret_5y, y10.ret_10y,
                     ytd.ret_ytd, incep.ret_incep, incep.ret_incep_a, incep.ret_incep_a_all, incep.ret_incep_a_gips,
                     dd_m1.drawdown_1m AS maxdrawdown_1m, dd_m3.drawdown_3m AS maxdrawdown_3m, dd_y1.drawdown_1y AS maxdrawdown_1y,
                     dd_incep.drawdown_incep AS maxdrawdown_incep,
                     iif(dd_incep.drawdown_incep.round(4) == 0 || incep.ret_incep_a > 1000, NULL, incep.ret_incep_a \ dd_incep.drawdown_incep) AS calmarratio_incep
              FROM tb_last_return a
              LEFT JOIN tb_1d d1 ON a.entity_id = d1.entity_id
              LEFT JOIN tb_1w w1 ON a.entity_id = w1.entity_id
              LEFT JOIN tb_1m m1 ON a.entity_id = m1.entity_id
              LEFT JOIN tb_3m m3 ON a.entity_id = m3.entity_id
              LEFT JOIN tb_6m m6 ON a.entity_id = m6.entity_id
              LEFT JOIN tb_1y y1 ON a.entity_id = y1.entity_id
              LEFT JOIN tb_2y y2 ON a.entity_id = y2.entity_id
              LEFT JOIN tb_3y y3 ON a.entity_id = y3.entity_id
              LEFT JOIN tb_4y y4 ON a.entity_id = y4.entity_id
              LEFT JOIN tb_5y y5 ON a.entity_id = y5.entity_id
              LEFT JOIN tb_10y y10 ON a.entity_id = y10.entity_id
              LEFT JOIN tb_ytd ytd ON a.entity_id = ytd.entity_id
              LEFT JOIN tb_incep incep ON a.entity_id = incep.entity_id
              LEFT JOIN tb_drawdown_1m dd_m1 ON a.entity_id = dd_m1.entity_id
              LEFT JOIN tb_drawdown_3m dd_m3 ON a.entity_id = dd_m3.entity_id
              LEFT JOIN tb_drawdown_1y dd_y1 ON a.entity_id = dd_y1.entity_id
              LEFT JOIN tb_drawdown_incep dd_incep ON a.entity_id = dd_incep.entity_id
              ORDER BY a.entity_id

    // 忽略掉非GIPS标准的所有年化收益字段（包括ytd_a）
    UPDATE tb_rets SET ret_1y_a = ret_1y, ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1,
                       ret_4y_a = (1 + ret_4y).pow(1\4) - 1, ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1


    return tb_rets

}