DolphinDB/modules/indicatorCalculator.dos

module fundit::indicatorCalculator

use fundit::dataPuller
use fundit::returnCalculator
use fundit::navCalculator

/*
 *  Annulized multiple
 */
def get_annulization_multiple(freq) {

  ret = 1;
  
  if (freq == 'd') {
    ret = 252; // We have differences here between Java and DolphinDB, Java uses 365.25 days
  } else if (freq == 'w') {
    ret = 52;
  } else if (freq == 'm') {
    ret = 12;
  } else if (freq == 'q') {
    ret = 4;
  } else if (freq == 's') {
    ret = 2;
  } else if (freq == 'a') {
    ret = 1;
  }
  
  return ret;
}

/*
 *   将VaR包裹一层，使之成为系统认可的聚集函数 
 *   @param returns <DOUBLE VECTOR>: 非空收益率
 *   @param method <STRING>: 'normal', 'logNormal', 'historical', 'monteCarlo'
 *   @param confidenceLevel <DOUBLE>: 置信水平，取值区间(0, 1)
 * 
 */
defg aggVaR(returns, method, confidenceLevel) {

	return returns.VaR(method, confidenceLevel);
}

/*
 *   将CVaR包裹一层，使之成为系统认可的聚集函数 
 *   @param returns <DOUBLE VECTOR>: 非空收益率
 *   @param method <STRING>: 'normal', 'logNormal', 'historical', 'monteCarlo'
 *   @param confidenceLevel <DOUBLE>: 置信水平，取值区间(0, 1)
 * 
 */
defg aggCVaR(returns, method, confidenceLevel) {

	return returns.CVaR(method, confidenceLevel);
}

/*
 *   最大回撤
 * 
 * 
 */
defg maxDrawdown(navs) {

	return max(1 - navs \ cummax(navs));
}


/*
 *   取主基准和BFI的历史月收益率
 *   
 *   @param benchmarks <TABLE>: entity-benchmark 的对应关系表
 *   @param end_day <DATE>: 收益的截止日期
 * 
 *   @return <TABLE>: benchmark_id, end_date, ret
 *   
 */
def get_benchmark_return(benchmarks, end_day) {

    s_index_ids = '';
    s_factor_ids = '';

    if(benchmarks.isVoid() || benchmarks.size() == 0) { return null; }

    // 前缀为 IN 的 benchmark id
    t_index_id = SELECT DISTINCT benchmark_id FROM benchmarks WHERE benchmark_id LIKE 'IN%';
    s_index_ids = iif(isVoid(t_index_id), "", "'" + t_index_id.benchmark_id.concat("','") + "'");

   // 前缀为 FA 的 benchmark id
   t_factor_id = SELECT DISTINCT benchmark_id FROM benchmarks WHERE benchmark_id LIKE 'FA%';
   s_factor_ids = iif(isVoid(t_factor_id), "",  "'" + t_factor_id.benchmark_id.concat("','") + "'");


    // 目前指数的月度业绩存在 fund_performance 表
    t_bmk = SELECT fund_id AS benchmark_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('IX', s_index_ids, 1990.01.01, end_day, true);

    // 而因子的月度业绩存在 cm_factor_performance 表
    INSERT INTO t_bmk SELECT factor_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('FA', s_factor_ids, 1990.01.01, end_day, true);

	return t_bmk;
}

/*
 *   Trailing Monthly Return, Standard Deviation, Skewness, Kurtosis, Max Drawdown, VaR, CVaR, Calmar Ratio
 *     
 *   @param entity_info <TABLE>: xxx_information表，NEED COLUMNS entity_id, inception_date
 *   @param ret <TABLE>: 收益表，需要有 entity_id, price_dat, end_date, nav
 *   @param trailing_month <STRING>: trailing X month or ytd, incep
 *     
 *   NOTE: standard deviation of Java version is noncompliant-GIPS annulized number
 *     
 *   Create:  20240904                                                                     Joey
 *            TODO: max drawdowns are the same with SWAGGER, but are off with SQL
 *            TODO: var, cvar, calmar are off; std dev, skewness, kurtosis are slightly off
 *            TODO: since inception date
 *              
 *     
 */
def cal_basic_performance(entity_info, ret, trailing_month) {

    // accumulate 版的 skewness, kurtosis, var, cvar 似乎都不对劲，只好找个笨办法来实现
    if(trailing_month == 'incep') {
        // 需要至少6个数才计算标准差、峰度、偏度
        t0 = SELECT 
                    prod(1+ret)-1 AS trailing_ret, 
                    prod(1+ret)-1 AS trailing_ret_a,
                    iif(count(entity_id) > 5, std(ret), null) AS std_dev,
                    iif(count(entity_id) > 5, std(ret)*sqrt(12), null) AS std_dev_a,
                    iif(count(entity_id) > 5, skew(ret, false), null) AS skewness,
                    iif(count(entity_id) > 5, kurtosis(ret, false), null)-3 AS kurtosis,
                    min(ret) AS wrst_month
             FROM ret
             WHERE ret > -1
             GROUP BY entity_id
             CGROUP BY end_date
             ORDER BY end_date;

        // 年化收益（给后面计算Calmar用）
        UPDATE t0 
            SET trailing_ret_a = (1+trailing_ret).pow(12\(t0.end_date - ei.inception_date.month()))-1
        FROM ej(t0, entity_info ei, 'entity_id')
        WHERE t0.end_date > ei.inception_date.month() + 12;
        
        // 不会用上面的办法算最大回撤, VaR, CVaR
        t_var = SELECT entity_id, end_date, ret,
                       cummax(1 - nav \ nav.cummax()) AS drawdown,
                       cumpercentile(ret, 5, 'linear') AS var
                FROM ret WHERE ret > -1
                CONTEXT BY entity_id;

        t_cvar = SELECT entity_id, end_date, drawdown, var,
                        - cumavg(iif(ret <= -var, ret, null)) AS cvar
                 FROM t_var
                 CONTEXT BY entity_id;

        t1 = SELECT t0.*, t_cvar.drawdown, t_cvar.var, t_cvar.cvar
             FROM t0 LEFT JOIN t_cvar ON t0.entity_id = t_cvar.entity_id AND t0.end_date = t_cvar.end_date
             ORDER BY t0.entity_id, t0.end_date;

    } else if(trailing_month == 'ytd') {

        t1 = SELECT entity_id, end_date,
                    cumprod(1+ret)-1 AS trailing_ret,
                    cumprod(1+ret)-1 AS trailing_ret_a, // no need annulization for ytd
                    iif(cumcount(entity_id) > 5, cumstd(ret), null) AS std_dev,
                    iif(cumcount(entity_id) > 5, tmoving(skew{, false}, end_date, ret, 12), null) AS skewness,
                    iif(cumcount(entity_id) > 5, tmoving(kurtosis{, false}, end_date, ret, 12), null) AS kurtosis,
                    cummin(ret) AS wrst_month,
                    maxDrawdown(nav) AS drawdown
        FROM ret WHERE ret > -1
        CONTEXT BY entity_id, end_date.year()
        ORDER BY entity_id, end_date;

    // trailing x month
    } else {
        	
        win = trailing_month$INT;
        
        t1 = SELECT entity_id, end_date, 
                    mprod(1+ret, win)-1 AS trailing_ret,
                    iif(trailing_month > 12,
                        mprod(1+ret, win).pow(12\win)-1,
                        mprod(1+ret, win)-1) AS trailing_ret_a,
                    mstd(ret, win) AS std_dev,
                    mskew(ret, win, false) AS skewness,
                    mkurtosis(ret, win, false) - 3 AS kurtosis,
                    mmin(ret, win) AS wrst_month,
                    moving(maxDrawdown, nav, win) AS drawdown,
                    moving(aggVaR{, 'historical', 0.95}, ret, win) AS var,
                    moving(aggCVaR{, 'historical', 0.95}, ret, win) AS cvar
        FROM ret WHERE ret > -1
        CONTEXT BY entity_id
        ORDER BY entity_id, end_date;
    }

    t1.addColumn('calmar', DOUBLE);
    UPDATE t1 SET calmar =  iif(drawdown == 0, null, trailing_ret_a\drawdown);

    return t1;

}


/*
 *   Lower Partial Moment
 *   NOTE: risk free rate is used as Minimal Accepted Rate (MAR) here
 * 
 */
def cal_LPM(ret, risk_free, trailing_month) {
    
    t = SELECT *, cumcount(entity_id) AS cnt FROM ret WHERE ret > -1 CONTEXT BY entity_id;

    if(trailing_month == 'incep') {

        lpm = SELECT entity_id, end_date,
                    iif(cumcount(end_date) > 5, (cumsum  (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\1), null) AS lpm1, 
                    iif(cumcount(end_date) > 5, (cumsum2 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\2), null) AS lpm2, 
                    iif(cumcount(end_date) > 5, (cumsum3 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\3), null) AS lpm3
              FROM t 
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY entity_id
              ORDER BY entity_id, end_date;
    	
    } else if(trailing_month == 'ytd') {

        lpm = SELECT entity_id, end_date,
                    iif(cumcount(end_date) > 5, (cumsum  (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\1), null) AS lpm1, 
                    iif(cumcount(end_date) > 5, (cumsum2 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\2), null) AS lpm2, 
                    iif(cumcount(end_date) > 5, (cumsum3 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\3), null) AS lpm3
              FROM t 
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY entity_id, end_date.year()
              ORDER BY entity_id, end_date;

    } else {

        win = trailing_month$INT;
        lpm = SELECT t.entity_id, t.end_date,
                     (msum (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\1) AS lpm1, 
                     (msum2(iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\2) AS lpm2, 
                     (moving(sum3, iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\3) AS lpm3
              FROM t 
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY t.entity_id
              ORDER BY entity_id, end_date;

    }
    

    return lpm;
}

/*
 *     Downside Devision, Omega Ratio, Sortino Ratio, Kappa Ratio
 * 
 *     TODO: Java version of Downside Deviation (LPM2) uses cnt-1 as denominator to calculate mean excess return, which might be wrong
 *           Java version of Omega could be wrong because Java uses annualized returns and cnt-1 
 *           Java'version of Kappa could be very wrong
 *           
 */
def cal_omega_sortino_kappa(ret, risk_free, trailing_month) {

    lpm = cal_LPM(ret, risk_free, trailing_month);

    if(trailing_month == 'incep') {

        tb = SELECT t.entity_id, t.end_date,
                l.lpm2 AS ds_dev,
                (t.ret - rfr.ret ).cumavg() \ l.lpm1 + 1 AS omega,
                (t.ret - rfr.ret ).cumavg() \ l.lpm2 AS sortino,
                (t.ret - rfr.ret ).cumavg() \ l.lpm3 AS kappa
              FROM ret t
              INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY t.entity_id;

    } else if(trailing_month == 'ytd') {

        tb = SELECT t.entity_id, t.end_date,
                l.lpm2 AS ds_dev,
                (t.ret - rfr.ret ).cumavg() \ l.lpm1 + 1 AS omega,
                (t.ret - rfr.ret ).cumavg() \ l.lpm2 AS sortino,
                (t.ret - rfr.ret ).cumavg() \ l.lpm3 AS kappa
              FROM ret t
              INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY t.entity_id, t.end_date.year();

    } else {

        win = trailing_month$INT;
        
        tb = SELECT t.entity_id, t.end_date,
                l.lpm2 AS ds_dev,
                (t.ret - rfr.ret ).mavg(win) \ l.lpm1 + 1 AS omega,
                (t.ret - rfr.ret ).mavg(win) \ l.lpm2 AS sortino,
                (t.ret - rfr.ret ).mavg(win) \ l.lpm3 AS kappa
              FROM ret t
              INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
              INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
              CONTEXT BY t.entity_id;
    }

    return tb;
}


/*
 *    Winning Ratio, Tracking Error, Information Ratio
 *   
 *    TODO: Win Rate incept is off, because Java incorrectly takes all end_date as denominator even when benchmark has no price
 *          Information Ratio is way off!
 *          Not sure how to describe a giant number("inf"), for now 999 is used
 */
def cal_benchmark_tracking(ret, benchmarks, bmk_ret, trailing_month) {

    if(trailing_month == 'incep') {

        t0 = SELECT t.entity_id, t.end_date, t.price_date,
                    t.ret, bmk.ret AS ret_bmk,
                    t.entity_id.cumcount() AS cnt, (t.ret - bmk.ret) AS exc_ret, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             WHERE t.ret > -1
               AND bmk.ret > -1
             CONTEXT BY t.entity_id, bm.benchmark_id;

        t = SELECT entity_id, end_date, benchmark_id,
                   cumcount(iif(exc_ret >= 0, 1, null)) \ cnt AS winrate,
                   exc_ret.cumstd() AS track_error, 
                   iif(exc_ret.cumstd() == 0, null, exc_ret.cumavg() / exc_ret.cumstd()) AS info
            FROM t0 
            CONTEXT BY entity_id, benchmark_id
            ORDER BY entity_id, end_date, benchmark_id;

    } else if(trailing_month == 'ytd') {

        t0 = SELECT t.entity_id, t.end_date, t.price_date,
                    t.ret, bmk.ret AS ret_bmk,
                    t.entity_id.cumcount() AS cnt, (t.ret - bmk.ret) AS exc_ret, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             WHERE t.ret > -1
               AND bmk.ret > -1
             CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();

        t = SELECT entity_id, end_date, benchmark_id,
                   cumcount(iif(exc_ret >= 0, 1, null)) \ cnt AS winrate,
                   exc_ret.cumstd() AS track_error, 
                   iif(exc_ret.cumstd() == 0, null, exc_ret.cumavg() / exc_ret.cumstd()) AS info
            FROM t0 
            CONTEXT BY entity_id, benchmark_id, end_date.year()
            ORDER BY entity_id, end_date, benchmark_id;
    } else {

        win = trailing_month$INT;

        t0 = SELECT t.entity_id, t.end_date, t.price_date,
                    t.ret, bmk.ret AS ret_bmk,
                    t.entity_id.mcount(win) AS cnt, (t.ret - bmk.ret) AS exc_ret, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             WHERE t.ret > -1
               AND bmk.ret > -1
             CONTEXT BY t.entity_id, bm.benchmark_id;

        t = SELECT entity_id, end_date, benchmark_id,
                   mcount(iif(exc_ret >= 0, 1, null), win) \ cnt AS winrate,
                   exc_ret.mstd(win) AS track_error, 
                   iif(exc_ret.mstd(win) == 0, null, exc_ret.mavg(win) / exc_ret.mstd(win)) AS info
            FROM t0 
            CONTEXT BY entity_id, benchmark_id
            ORDER BY entity_id, end_date, benchmark_id;
    }

    return t;
}

/*
 *   Alpha & Beta
 *   NOTE: alpha of Java version is wrong because it doesn't use risk free rate
 */
def cal_alpha_beta(ret, benchmarks, bmk_ret, risk_free, trailing_month) {

    t = SELECT t.entity_id, t.end_date, t.ret, bm.benchmark_id, bmk.ret AS ret_bmk
        FROM ret t
        INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
        INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
        WHERE t.ret > -1
          AND bmk.ret > -1;

    if(trailing_month == 'incep') {

        beta = SELECT entity_id, end_date, benchmark_id, ret.cumbeta(ret_bmk) AS beta FROM t CONTEXT BY entity_id, benchmark_id;

        alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta, 
                       (t.ret - rfr.ret).cumavg() - beta.beta * (t.ret_bmk - rfr.ret).cumavg() AS alpha
                FROM t 
                INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
                INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                CONTEXT BY t.entity_id, t.benchmark_id
                ORDER BY t.entity_id, t.end_date, t.benchmark_id;

    } else if(trailing_month == 'ytd') {

        beta = SELECT entity_id, end_date, benchmark_id, ret.cumbeta(ret_bmk) AS beta FROM t CONTEXT BY entity_id, benchmark_id, end_date.year();

        alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta, 
                       (t.ret - rfr.ret).cumavg() - beta.beta * (t.ret_bmk - rfr.ret).cumavg() AS alpha
                FROM t 
                INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
                INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                CONTEXT BY t.entity_id, t.benchmark_id, t.end_date.year()
                ORDER BY t.entity_id, t.end_date, t.benchmark_id;

    } else {

        win = trailing_month$INT;

        beta = SELECT entity_id, end_date, benchmark_id, ret.mbeta(ret_bmk, win) AS beta FROM t CONTEXT BY entity_id, benchmark_id;

        alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta, 
                       (t.ret - rfr.ret).mavg(win) - beta.beta * (t.ret_bmk - rfr.ret).mavg(win) AS alpha
                FROM t 
                INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
                INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                CONTEXT BY t.entity_id, t.benchmark_id
                ORDER BY t.entity_id, t.end_date, t.benchmark_id;
    }

    return alpha;
}


/*
 *  Upside/Down Capture Return/Ratio
 *  
 *  TODO: trailing x month values are way off!
 * 
 */
def cal_capture_ratio(ret, benchmarks, bmk_ret, trailing_month) {

    if(trailing_month == 'incep') {

        t1 = SELECT t.entity_id, t.end_date, (1+t.ret).cumprod() AS upside_ret, (1+bmk.ret).cumprod() AS bmk_upside_ret, bmk.end_date.cumcount() AS bmk_upside_cnt, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret >= 0
             CONTEXT BY t.entity_id, bm.benchmark_id;
    
        t2 = SELECT t.entity_id, t.end_date, (1+t.ret).cumprod() AS downside_ret, (1+bmk.ret).cumprod() AS bmk_downside_ret, bmk.end_date.cumcount() AS bmk_downside_cnt, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret < 0
             CONTEXT BY t.entity_id, bm.benchmark_id;

    } else if(trailing_month == 'ytd') {

        t1 = SELECT t.entity_id, t.end_date, (1+t.ret).cumprod() AS upside_ret, (1+bmk.ret).cumprod() AS bmk_upside_ret, bmk.end_date.cumcount() AS bmk_upside_cnt, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret >= 0
             CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();
    
        t2 = SELECT t.entity_id, t.end_date, (1+t.ret).cumprod() AS downside_ret, (1+bmk.ret).cumprod() AS bmk_downside_ret, bmk.end_date.cumcount() AS bmk_downside_cnt, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret < 0
             CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();

    } else {

        win = trailing_month$INT;

        t1 = SELECT t.entity_id, t.end_date, bm.benchmark_id,
                    (1+t.ret).mprod(win) AS upside_ret, (1+bmk.ret).mprod(win) AS bmk_upside_ret, bmk.end_date.mcount(win) AS bmk_upside_cnt
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret >= 0
             CONTEXT BY t.entity_id, bm.benchmark_id;
    
        t2 = SELECT t.entity_id, t.end_date, bm.benchmark_id,
                    (1+t.ret).mprod(win) AS downside_ret, (1+bmk.ret).mprod(win) AS bmk_downside_ret, bmk.end_date.mcount(win) AS bmk_downside_cnt
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
             WHERE t.ret > -1
               AND bmk.ret < 0
             CONTEXT BY t.entity_id, bm.benchmark_id;
    }

    t = (SELECT * FROM (
             SELECT iif(isNull(t1.entity_id), t2.entity_id, t1.entity_id) AS entity_id,
                    iif(isNull(t1.end_date), t2.end_date, t1.end_date) AS end_date,
                    iif(isNull(t1.benchmark_id), t2.benchmark_id, t1.benchmark_id) AS benchmark_id,
                    t1.upside_ret.pow(1 \ t1.bmk_upside_cnt)-1 AS upside_capture_ret,
                   (t1.upside_ret.pow(1 \ t1.bmk_upside_cnt)-1)/(t1.bmk_upside_ret.pow(1 \ t1.bmk_upside_cnt)-1) AS upside_capture_ratio,
                    t2.downside_ret.pow(1 \ t2.bmk_downside_cnt)-1 AS downside_capture_ret,
                   (t2.downside_ret.pow(1 \ t2.bmk_downside_cnt)-1)/(t2.bmk_downside_ret.pow(1 \ t2.bmk_downside_cnt)-1) AS downside_capture_ratio
             FROM t1 FULL JOIN t2 ON t1.entity_id = t2.entity_id AND t1.benchmark_id = t2.benchmark_id AND t1.end_date = t2.end_date)
             ORDER BY entity_id, benchmark_id, end_date).ffill();

    return t;
}

/*
 *    Sharpe Ratio
 *    NOTE: Java version is noncompliant-GIPS annulized number
 */
def cal_sharpe(ret, std_dev, risk_free, trailing_month) {

    if(trailing_month == 'incep') {

        sharpe = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).cumavg() / std.std_dev AS sharpe
                 FROM ret t
                 INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 WHERE std.std_dev <> 0
                 CONTEXT BY t.entity_id;
                 
    } else if(trailing_month == 'ytd') {

        sharpe = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).cumavg() / std.std_dev AS sharpe
                 FROM ret t
                 INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 WHERE std.std_dev <> 0
                 CONTEXT BY t.entity_id, t.end_date.year();
    } else {

        win = trailing_month$INT;

        sharpe = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).mavg(win) / std.std_dev AS sharpe
                 FROM ret t
                 INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 WHERE std.std_dev <> 0
                 CONTEXT BY t.entity_id;
    }

    return sharpe;
}

/*
 *    Treynor Ratio = annulized excess return / beta
 *    
 *    TODO: ytd is off because Java uses non-GIPS rule to annulize return
 */
def cal_treynor(ret, risk_free, beta, trailing_month) {

    if(trailing_month == 'incep') {

        t = SELECT *, cumcount(entity_id) AS cnt 
            FROM ret t 
            INNER JOIN risk_free rfr ON t.end_date = rfr.end_date 
            WHERE t.ret > -1
              AND rfr.ret > -1
            CONTEXT BY t.entity_id;
           
        treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
                        ((1 + t.ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt))) / beta.beta AS treynor
                  FROM t
                  INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                  CONTEXT BY t.entity_id, beta.benchmark_id;

    } else if(trailing_month == 'ytd') {

        t = SELECT *, cumcount(entity_id) AS cnt 
            FROM ret t 
            INNER JOIN risk_free rfr ON t.end_date = rfr.end_date 
            WHERE t.ret > -1
              AND rfr.ret > -1
            CONTEXT BY t.entity_id, t.end_date.year();
           
        treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
                        ((1 + t.ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt))) / beta.beta AS treynor
                  FROM t
                  INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                  CONTEXT BY t.entity_id, beta.benchmark_id, t.end_date.year();
    } else {

        win = trailing_month$INT;

        t = SELECT *, mcount(entity_id, win) AS cnt 
            FROM ret t 
            INNER JOIN risk_free rfr ON t.end_date = rfr.end_date 
            WHERE t.ret > -1
              AND rfr.ret > -1
            CONTEXT BY t.entity_id;
           
        treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
                        ((1 + t.ret).mprod(win).pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).mprod(win).pow(12\iif(t.cnt<12, 12, t.cnt))) / beta.beta AS treynor
                  FROM t
                  INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                  CONTEXT BY t.entity_id, beta.benchmark_id;
    }

    return treynor;
}

/*
 *    Jensen's Alpha
 *    TODO: the result is slightly off
 */
def cal_jensen(ret, bmk_ret, risk_free, beta, trailing_month) {

    if(trailing_month == 'incep') {
    	
        jensen = SELECT t.entity_id, t.end_date, t.ret.cumavg() - rfr.ret.cumavg() - beta.beta * (bmk.ret.cumavg() - rfr.ret.cumavg()) AS jensen, beta.benchmark_id
                 FROM ret t
                 INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                 INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 CONTEXT BY t.entity_id, beta.benchmark_id;

    } else if(trailing_month == 'ytd') {

        jensen = SELECT t.entity_id, t.end_date, t.ret.cumavg() - rfr.ret.cumavg() - beta.beta * (bmk.ret.cumavg() - rfr.ret.cumavg()) AS jensen, beta.benchmark_id
                 FROM ret t
                 INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                 INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 CONTEXT BY t.entity_id, beta.benchmark_id, t.end_date.year();

    } else {

        win = trailing_month$INT;

        jensen = SELECT t.entity_id, t.end_date, t.ret.mavg(win) - rfr.ret.mavg(win) - beta.beta * (bmk.ret.mavg(win) - rfr.ret.mavg(win)) AS jensen, beta.benchmark_id
                 FROM ret t
                 INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
                 INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
                 INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
                 CONTEXT BY t.entity_id, beta.benchmark_id;

    }

    return jensen;
}

/*
 *     Modigliani Modigliani Measure (M2)
 *     NOTE: M2 = sharpe * std(benchmark) + risk_free_rate
 *     NOTE: Java version is noncompliant-GIPS annulized number 
 */
def cal_m2(ret, benchmarks, bmk_ret, risk_free, trailing_month) {

    if(trailing_month == 'incep') {
    	
        m2 = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).cumavg() / t.ret.cumstd() * bmk.ret.cumstd() + rfr.ret.cumavg() AS m2, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
             CONTEXT BY t.entity_id, bm.benchmark_id;

    } else if(trailing_month == 'ytd') {

        m2 = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).cumavg() / t.ret.cumstd() * bmk.ret.cumstd() + rfr.ret.cumavg() AS m2, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
             CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();

    } else {

        win = trailing_month$INT;

        m2 = SELECT t.entity_id, t.end_date, (t.ret - rfr.ret).mavg(win) / t.ret.mstd(win) * bmk.ret.mstd(win) + rfr.ret.mavg(win) AS m2, bm.benchmark_id
             FROM ret t
             INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
             INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
             CONTEXT BY t.entity_id, bm.benchmark_id;

    }

    return m2;
}

/*
 *    Morningstar Return, Morningstar Risk-Adjusted Return
 *    
 *    TODO: Tax and loads are NOT taken care of
 *    TODO: Assume Chinese methodology using 3, 5, 10 as number of traling years
 *    TODO: need verify with reliable results
 *    
 *    NOTE: Morningstar methodology requires monthly return for calculation, so that "12" is hard-coded here
 * 
 * 
 */
def cal_ms_return(ret, risk_free, trailing_month) {

    win = trailing_month$INT;

    r = SELECT t.entity_id, t.end_date, t.price_date.mmax(win) AS price_date, t.price_date.mmin(win) AS min_date,
               ((1 + t.ret)\(1 + rfr.ret)).mprod(win).pow(12\(t.end_date.mmax(win) - t.end_date.mmin(win)))-1 AS ms_ret_a,
               (1 + t.ret).pow(-2).mavg(win).pow(-12/2)-1 AS ms_rar_a
        FROM ret t
        INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
        CONTEXT BY t.entity_id;

    return r;
}

/*
 *   Calculation for monthly indicators which need benchmark
 *   
 *   @param entity_info <TABLE>: xxx_information表，NEED COLUMNS entity_id, inception_date
 *   @param tb_ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
 *   @param benchmarks <TABLE>: entity-benchmark mapping table 
 *   @param index_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   @param month <INT>: trailing x month
 *   
 *   @return: indicators table
 *    
 *    
 *   
 *   Create  20240904  模仿Java & python代码在Dolphin中实现，具体计算逻辑可能会有不同                          Joey
 *                     TODO: some datapoints require more data, we need a way to disable calculation for them
 *
 */
def cal_indicators_with_benchmark(entity_id, tb_ret, benchmarks, index_ret, risk_free, month) {

    // sorting for correct first() and last() value
    ret = SELECT * FROM tb_ret ORDER BY entity_id, price_date;
    
    // alpha, beta
    alpha_beta = cal_alpha_beta(ret, benchmarks, index_ret, risk_free, month);

    // 胜率、跟踪误差、信息比率
    bmk_tracking = cal_benchmark_tracking(ret, benchmarks, index_ret, month);

    // 特雷诺
    treynor = cal_treynor(ret, risk_free, alpha_beta, month);

    // 詹森指数
    jensen = cal_jensen(ret, index_ret, risk_free, alpha_beta, month);

    // M2
    m2 = cal_m2(ret, benchmarks, index_ret, risk_free, month);

    // 上下行捕获率、收益
    capture_r = cal_capture_ratio(ret, benchmarks, index_ret, month);

    r = SELECT * FROM bmk_tracking a1
                 LEFT JOIN alpha_beta ON a1.entity_id = alpha_beta.entity_id AND a1.benchmark_id = alpha_beta.benchmark_id AND a1.end_date = alpha_beta.end_date
                 LEFT JOIN treynor ON a1.entity_id = treynor.entity_id AND a1.benchmark_id = treynor.benchmark_id AND a1.end_date = treynor.end_date
                 LEFT JOIN jensen ON a1.entity_id = jensen.entity_id AND a1.benchmark_id = jensen.benchmark_id AND a1.end_date = jensen.end_date
                 LEFT JOIN m2 ON a1.entity_id = m2.entity_id AND a1.benchmark_id = m2.benchmark_id AND a1.end_date = m2.end_date
                 LEFT JOIN capture_r ON a1.entity_id = capture_r.entity_id AND a1.benchmark_id = capture_r.benchmark_id AND a1.end_date = capture_r.end_date;

    // 年化各数据点
    // GIPS RULE: NO annulization for data less than 1 year
    plainAnnu = get_annulization_multiple('m');
    sqrtAnnu = sqrt(get_annulization_multiple('m'));

    r.addColumn(['alpha_a', 'jensen_a', 'track_error_a', 'info_a', 'm2_a'],
                [DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);

    UPDATE r 
      SET alpha_a = alpha * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1),
          jensen_a = jensen * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1),
          track_error_a = track_error * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
          info_a = info * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
          m2_a = m2 * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1);
    
    return r.dropColumns!(['price_date', 'min_date']);
}

/*
 *   Monthly standard indicator calculation
 *   @param tb_ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param benchmark_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   
 *   @return: indicators table
 *   
 *   
 *   Create  20240904  模仿Java & python代码在Dolphin中实现，具体计算逻辑可能会有不同                          Joey
 *                     TODO: some datapoints require more data, we need a way to disable calculation for them
 *
 */
def cal_indicators(entity_info, tb_ret, benchmarks, benchmark_ret, risk_free, month) {

    // sorting for correct first() and last() value
    ret = SELECT * FROM tb_ret ORDER BY entity_id, price_date;

    // 收益、标准差、偏度、峰度、最大回撤、VaR, CVaR、卡玛比率
    rtn = cal_basic_performance(entity_info, ret, month);

    // 夏普
    sharpe = cal_sharpe(ret, rtn, risk_free, month);

    // 整合后的下行标准差、欧米伽、索提诺、卡帕
    lpms = cal_omega_sortino_kappa(ret, risk_free, month);

    // 需要基准的指标们
    indicator_with_benchmark = cal_indicators_with_benchmark(entity_info, ret, benchmarks, benchmark_ret, risk_free, month);

    r = SELECT * FROM rtn a1
                 LEFT JOIN sharpe ON a1.entity_id = sharpe.entity_id AND a1.end_date = sharpe.end_date
                 LEFT JOIN lpms ON a1.entity_id = lpms.entity_id AND a1.end_date = lpms.end_date
                 LEFT JOIN indicator_with_benchmark bmk ON a1.entity_id = bmk.entity_id AND a1.end_date = bmk.end_date;

    // 年化各数据点
    // GIPS RULE: NO annulization for data less than 1 year
    plainAnnu = get_annulization_multiple('m');
    sqrtAnnu = sqrt(get_annulization_multiple('m'));

    r.addColumn(['std_dev_a', 'ds_dev_a', 'sharpe_a', 'sortino_a'],
                [DOUBLE, DOUBLE, DOUBLE, DOUBLE]);

    UPDATE r 
      SET std_dev_a = std_dev * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
          ds_dev_a = ds_dev * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
          sharpe_a = sharpe * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
          sortino_a = sortino * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1);
    
    return r;
}


/*
 *   Monthly BFI indicator calculation
 *   @param ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param benchmark_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   
 *   @return: BFI indicators table
 *   
 *   【NOTE】JAVA IMPLEMENTATION ASSUMES BFI FACTORS OF CURRENT MONTH ARE SAME AS HISTORICAL ONES!   
 *   
 *   Create  20240914                                                                 Joey
 *
 */
def cal_bfi_indicators(entity_info, ret, benchmarks, benchmark_ret, risk_free, month) {

    // 需要基准的指标们
    r = cal_indicators_with_benchmark(entity_info, ret, benchmarks, benchmark_ret, risk_free, month);
    
    return r;
}

/*
 *   Monthly Morningstar indicator calculation
 *   
 *   @param ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param benchmarks <USELESS>:
 *   @param benchmark_ret <USELESS>:
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 * 
 */
def cal_ms_indicators(ret, benchmarks, benchmark_ret, risk_free, month) {

	r = cal_ms_return(ret, risk_free, month);

	return r;
}

/*
 *   Calculate Trailing x month indicators
 * 
 *   @param: func <FUNCTION>: the calculation function
 *   @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param: end_day <DATE>: 计算截止日期
 *   @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   @param month <INT>: X month
 *   @param isCal <BOOL>: 是否计算此区间
 *   
 *   NOTE: 与Java不同，这里多了个限制条件：如果区间内有效基准数少于1/2，不做计算；
 *         比如过去12个月中某BFI只出现2次，小于需要的6次，此BFI不参与 trailing 1 year 计算
 *   
 * 
 */
def cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, index_ret, risk_free_rate, isCal, month) {

    ret = null;

    ret = SELECT * FROM tb_ret r 
          INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
          WHERE r.end_date > end_day.month() - month 
            AND end_day.month() - ei.inception_date.month() >= month
          ORDER BY entity_id, end_date;

    bmk = SELECT * FROM benchmarks 
          WHERE end_date > end_day.month() - month
          CONTEXT BY entity_id, benchmark_id
          HAVING count(end_date) >= month/2
          ORDER BY entity_id, end_date, benchmark_id;

    bmk_ret = SELECT * FROM index_ret WHERE end_date > end_day.month() - month;

    if(ret.size() > 0 && bmk.size() > 0 && bmk_ret.size() > 0 && isCal == 1) {
        ret = func(ret, bmk, bmk_ret, risk_free_rate);
    }

	return ret;
}

/*
 *   Calculate trailing 6m, ytd, 1y, 2y, 3y, 4y, 5y, 10y and since inception datapoints
 *   
 *   @param: func <FUNCTION>: the calculation function
 *   @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param: end_day <DATE>: 计算截止日期
 *   @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   @param periods <BOOL VECTOR>: 是否计算的区间向量，分别对应 incep, ytd, 6m, 1y, 2y, 3y, 4y, 5y, 10y
 *   
 * 
 */
def cal_trailing(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods) {

    r_incep = null;
    r_ytd = null;
    r_6m = null;
    r_1y = null;
    r_2y = null;
    r_3y = null;
    r_4y = null;
    r_5y = null;
    r_10y = null;

    // since inception
    // TODO: 怎么给since inception加上是否计算的限制条件，暂时用3
    if(tb_ret.size() > 0 && benchmarks.size() > 0 && periods[0] == 1) {

        bmk = SELECT * FROM benchmarks 
              CONTEXT BY entity_id, benchmark_id
              HAVING count(end_date) >= 3
              ORDER BY entity_id, end_date, benchmark_id;

        r_incep = func(tb_ret, bmk, bmk_ret, risk_free_rate);
    }

    // ytd
    r_ytd = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[1], end_day.month()-end_day.yearBegin().month()+1);

    // trailing 6m
    r_6m = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[2], 6);

   // trailing 1y
    r_1y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[3], 12);
   
   // trailing 2y
    r_2y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[4], 24);

   // trailing 3y
    r_3y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[5], 36);

   // trailing 4y
    r_4y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[6], 48);

   // trailing 5y
    r_5y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[7], 60);

   // trailing 10y
    r_10y = cal_trailing_month(func, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, periods[8], 120);

/*
    // trailing 1y
    tb_ret_1y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-12 AND (end_day.month() - ei.inception_date.month()) >= 12;
    if(tb_ret_1y.size() > 0 && periods[3] == 1) {
        r_1y = func(tb_ret_1y, benchmarks, bmk_ret, risk_free_rate, 12/2);
    }
    
    // trailing 2y
    tb_ret_2y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-24 AND (end_day.month() - ei.inception_date.month()) >= 24;
    if(tb_ret_2y.size() > 0 && periods[4] == 1) {
        r_2y = func(tb_ret_2y, benchmarks, bmk_ret, risk_free_rate, 24/2);
    }
    
    // trailing 3y
    tb_ret_3y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-36 AND (end_day.month() - ei.inception_date.month()) >= 36;
    if(tb_ret_3y.size() > 0 && periods[5] == 1) {
        r_3y = func(tb_ret_3y, benchmarks, bmk_ret, risk_free_rate, 36/2);
    }

    // trailing 4y
    tb_ret_4y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-48 AND (end_day.month() - ei.inception_date.month()) >= 48;
    if(tb_ret_4y.size() > 0 && periods[6] == 1) {
        r_4y = func(tb_ret_4y, benchmarks, bmk_ret, risk_free_rate, 48/2);
    }
    
    // trailing 5y
    tb_ret_5y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-60 AND (end_day.month() - ei.inception_date.month()) >= 60;
    if(tb_ret_5y.size() > 0 && periods[7] == 1) {
        r_5y = func(tb_ret_5y, benchmarks, bmk_ret, risk_free_rate, 60/2);
    }

    // trailing 10y
    tb_ret_10y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
                WHERE r.end_date > end_day.month()-120 AND (end_day.month() - ei.inception_date.month()) >= 120;
    if(tb_ret_10y.size() > 0 && periods[8] == 1) {
        r_10y = func(tb_ret_10y, benchmarks, bmk_ret, risk_free_rate, 120/2);
    }
*/
    return r_incep, r_ytd, r_6m, r_1y, r_2y, r_3y, r_4y, r_5y, r_10y;
}



/*
 *   Calculate trailing 6m, ytd, 1y, 2y, 3y, 4y, 5y, 10y and since inception standard indicators
 *   
 *   @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param: end_day <DATE>: 计算截止日期
 *   @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 * 
 */
def cal_trailing_indicators(entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate) {

    return cal_trailing(cal_indicators, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, [1,1,1,1,1,1,1,1,1]);

}

/*
 *   Calculate trailing 6m, ytd, 1y, 2y, 3y, 4y, 5y, 10y and since inception bfi indicators
 *   
 *   @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
 *   @param benchmarks <TABLE>: entity-benchmark mapping table
 *   @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav 
 *   @param: end_day <DATE>: 计算截止日期
 *   @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
 *   @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
 *   
 * 
 */
def cal_trailing_bfi_indicators(entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate) {

    return cal_trailing(cal_bfi_indicators, entity_info, benchmarks, tb_ret, end_day, bmk_ret, risk_free_rate, [1,1,1,1,1,1,1,1,1]);

}

/*
 *   Calculate trailing 3y, 5y, 10y Morningstar Return, Risk-Adjested Return and Risk
 * 
 */
def cal_trailing_ms_indicators(entity_info, tb_ret, end_day, risk_free_rate) {
	
    return cal_trailing(cal_ms_indicators,  entity_info, , tb_ret, end_day, , risk_free_rate, periods=[0,0,0,0,0,1,0,1,1]);
}

/*
 *   Calculate fund indicators for one date
 * 
 *   @param entity_type <STRING>: MF, HF
 *   @param fund_ids <STRING>: 逗号和单引号分隔的fund_id
 *   @param end_day <DATE>: 要计算的日期
 *   @param isFromNav <BOOL>: 用净值实时计算还是从表中取月收益
 *   @param isFromSQL <BOOL>: TODO: 从MySQL还是本地DolphinDB取净值/收益数据
 *   
 *   @return <DICT TABLE>: ['PBI-INCEP', 'PBI-YTD', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y', 'MS-3Y', 'MS-5Y', 'MS-10Y']
 *   
 *   TODO: primary_benchmark_id seems not be used as benchmark, when it is FA00000VNB
 * 
 *   Example: cal_fund_indicators('HF', "'HF000004KN','HF000103EU','HF00018WXG'", 2024.06.28, true);
 * 
 */
def cal_fund_indicators(entity_type, fund_ids, end_day, isFromNav) {

    very_old_date = 1990.01.01;

    start_month = 1990.01M;

    fund_info = get_fund_info(fund_ids);
    
    if(fund_info.isVoid() || fund_info.size() == 0) { return null };
    
    fund_info.rename!('fund_id', 'entity_id');

    if(isFromNav == true) {
        // 从净值开始计算收益
        tb_ret = SELECT * FROM cal_fund_monthly_returns(entity_type, fund_ids, true) WHERE price_date <= end_day;
        tb_ret.rename!(['fund_id', 'cumulative_nav'], ['entity_id', 'nav']);
    } else {
        // 从fund_performance表里读月收益
        tb_ret = get_monthly_ret('FD', fund_ids, very_old_date, end_day, true);
        tb_ret.rename!(['fund_id'], ['entity_id']);
    }

    // 取基金和基准的对照表
    primary_benchmark = SELECT fund_id AS entity_id, end_date, iif(benchmark_id.isNull(), 'IN00000008', benchmark_id) AS benchmark_id 
                        FROM get_fund_primary_benchmark(fund_ids, start_month.temporalFormat('yyyy-MM'), end_day.month().temporalFormat('yyyy-MM')) ;

    // 取所有出现的基准月收益
    bmk_ret = get_benchmark_return(primary_benchmark, end_day);

    risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);

    // 标准的指标
    t0 = cal_trailing_indicators(fund_info, primary_benchmark, tb_ret, end_day, bmk_ret, risk_free_rate);

    // Morningstar 指标
    t1 = cal_trailing_ms_indicators(fund_info, tb_ret, end_day, risk_free_rate);

    // PBI stands for "Primary Benchmark Index", MS stands for "MorningStar"
    v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y', 'MS-3Y', 'MS-5Y', 'MS-10Y'];
    
    return dict(v_table_name, t0 <- t1[5] <- t1[7] <- t1[8]);
}

/*
 *   Calculate fund BFI indicators for one date
 * 
 *   @param entity_type <STRING>: MF, HF
 *   @param fund_ids <STRING>: 逗号和单引号分隔的fund_id
 *   @param end_day <DATE>: 要计算的日期
 *   @param isFromNav <BOOL>: 用净值实时计算还是从表中取月收益
 *   @param isFromSQL <BOOL>: TODO: 从MySQL还是本地DolphinDB取净值/收益数据
 *   
 *   @return <DICT TABLE>: ['BFI-INCEP', 'BFI-YTD', 'BFI-6M', 'BFI-1Y', 'BFI-2Y', 'BFI-3Y', 'BFI-4Y', 'BFI-5Y', 'BFI-10Y']
 *   
 *   TODO: primary_benchmark_id seems not be used as benchmark, when it is FA00000VNB
 *   TODO: intergrate with cal_fund_indicators
 * 
 *   Example: cal_fund_bfi_indicators('MF', "'MF00003PW2', 'MF00003PW1', 'MF00003PXO'", 2024.08.31, true);
 * 
 */
def cal_fund_bfi_indicators(entity_type, fund_ids, end_day, isFromNav) {

    very_old_date = 1990.01.01;

    start_month = 1990.01M;

    fund_info = get_fund_info(fund_ids);
    
    if(fund_info.isVoid() || fund_info.size() == 0) { return null };
    
    fund_info.rename!('fund_id', 'entity_id');

    if(isFromNav == true) {
        // 从净值开始计算收益
        tb_ret = SELECT * FROM cal_fund_monthly_returns(entity_type, fund_ids, true) WHERE price_date <= end_day;
        tb_ret.rename!(['fund_id', 'cumulative_nav'], ['entity_id', 'nav']);
    } else {
        // 从fund_performance表里读月收益
        tb_ret = get_monthly_ret('FD', fund_ids, very_old_date, end_day, true);
        tb_ret.rename!(['fund_id'], ['entity_id']);
    }

    // 取基金和基准的对照表
    bfi_benchmark = SELECT fund_id AS entity_id, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id 
                           FROM get_fund_bfi_factors(fund_ids, start_month.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM'));

    if(bfi_benchmark.isVoid() || bfi_benchmark.size() == 0) { return null; }

    bmk_ret = get_benchmark_return(bfi_benchmark, end_day);

    risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);

    t0 = cal_trailing_bfi_indicators(fund_info, bfi_benchmark, tb_ret, end_day, bmk_ret, risk_free_rate);

    // BFI stands for "Best Fit Index"
    v_table_name = ['BFI-INCEP', 'BFI-YTD', 'BFI-6M', 'BFI-1Y', 'BFI-2Y', 'BFI-3Y', 'BFI-4Y', 'BFI-5Y', 'BFI-10Y'];
    
    return dict(v_table_name, t0);
}

/*
 *  Calculate portfolio indicators for one date
 *  
 *  @param portfolio_ids <STRING>: comma-delimited portfolio ids
 *  @param end_day <DATE>: the date
 *  @param cal_method <INT>: calculate based on cumulative nav (1) or nav (2)
 *  @param isFromNav <BOOL>: calculate returns from NAV on-the-fly (true) or get from monthly return table (false)
 *  
 *  Example: cal_portfolio_indicators('166002,166114', 2024.08.31, 1, true);
 * 
 */
def cal_portfolio_indicators(portfolio_ids, end_day, cal_method, isFromNav) {

    very_old_date = 1990.01.01;

    portfolio_info = get_portfolio_info(portfolio_ids);

    if(portfolio_info.isVoid() || portfolio_info.size() == 0) { return null };
    
    portfolio_info.rename!('portfolio_id', 'entity_id');

    if(isFromNav == true) {
        // 从净值开始计算收益
        tb_raw_ret = SELECT * FROM cal_portfolio_nav(portfolio_ids, very_old_date, cal_method) WHERE price_date <= end_day;
       
        // funky thing is you can't use "AS" for the grouping columns?
        tb_ret = SELECT portfolio_id, price_date.month(), price_date.last() AS price_date, (1+ret).prod()-1 AS ret, nav.last() AS nav
                 FROM tb_raw_ret
                 WHERE price_date <= end_day
                 GROUP BY portfolio_id, price_date.month();
        tb_ret.rename!(['portfolio_id', 'month_price_date'], ['entity_id', 'end_date']);

    } else {
        // 从pf_portfolio_performance表里读月收益
        tb_ret = get_monthly_ret('PF', portfolio_ids, very_old_date, end_day, true);
        tb_ret.rename!(['portfolio_id'], ['entity_id']);
    }

    // 沪深300做基准，同SQL保持一致
    primary_benchmark = SELECT entity_id, 'IN00000008' AS benchmark_id FROM portfolio_info;

    // 取所有出现的基准月收益
    bmk_ret = get_benchmark_return(primary_benchmark, end_day);

    risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);

    t0 = cal_trailing_indicators(portfolio_info, primary_benchmark, tb_ret, end_day, bmk_ret, risk_free_rate);

    v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y'];
    
    return dict(v_table_name, t0);

}


/*
 *  Calculate portfolio bfi indicators for one date
 *  
 *  @param portfolio_ids <STRING>: comma-delimited portfolio ids
 *  @param end_day <DATE>: the date
 *  @param cal_method <INT>: calculate based on cumulative nav (1) or nav (2)
 *  @param isFromNav <BOOL>: calculate returns from NAV on-the-fly (true) or get from monthly return table (false)
 *  
 *  TODO: intergrate with cal_portfolio_indicators
 *   
 *  Example: cal_portfolio_bfi_indicators('166002,166114', 2024.08.31, 1, true);
 * 
 */
def cal_portfolio_bfi_indicators(portfolio_ids, end_day, cal_method, isFromNav) {

    very_old_date = 1990.01.01;

    start_month = 1990.01M;

    portfolio_info = get_portfolio_info(portfolio_ids);

    if(portfolio_info.isVoid() || portfolio_info.size() == 0) { return null };
    
    portfolio_info.rename!('portfolio_id', 'entity_id');

    if(isFromNav == true) {
        // 从净值开始计算收益
        tb_raw_ret = SELECT * FROM cal_portfolio_nav(portfolio_ids, very_old_date, cal_method) WHERE price_date <= end_day;
       
        // funky thing is you can't use "AS" for the grouping columns?
        tb_ret = SELECT portfolio_id, price_date.month(), price_date.last() AS price_date, (1+ret).prod()-1 AS ret, nav.last() AS nav
                 FROM tb_raw_ret
                 WHERE price_date <= end_day
                 GROUP BY portfolio_id, price_date.month();
        tb_ret.rename!(['portfolio_id', 'month_price_date'], ['entity_id', 'end_date']);

    } else {
        // 从pf_portfolio_performance表里读月收益
        tb_ret = get_monthly_ret('PF', portfolio_ids, very_old_date, end_day, true);
        tb_ret.rename!(['portfolio_id'], ['entity_id']);
    }

    // 取组合和基准的对照表
    bfi_benchmark = SELECT portfolio_id AS entity_id, end_date, factor_id AS benchmark_id 
                    FROM get_portfolio_bfi_factors(portfolio_ids, start_month.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM'));

    if(bfi_benchmark.isVoid() || bfi_benchmark.size() == 0) { return null; }

    bmk_ret = get_benchmark_return(bfi_benchmark, end_day);

    risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);

    t0 = cal_trailing_bfi_indicators(portfolio_info, bfi_benchmark, tb_ret, end_day, bmk_ret, risk_free_rate);

    v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y'];
    
    return dict(v_table_name, t0);

}