DolphinDB/modules/indicatorCalculator.dos

module fundit::indicatorCalculator

/*
 *  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;
}


/*
 *     Trailing Return, Standard Deviation, Skewness, Kurtosis, Max Drawdown, VaR, CVaR
 *     @param ret: 收益表，需要有 entity_id, price_dat, end_date, nav 
 *     @param freq: 数据频率，d, w, m, q, s, a
 *     
 *     Create:  20240904                                                                     Joey
 *                       TODO: var and cvar are silightly off compared with Java version
 *     
 */
def cal_basic_performance(ret) {

    t =	SELECT entity_id, max(end_date) AS end_date, max(price_date) AS price_date, min(price_date) AS min_date,
	           (nav.last() \ nav.first() - 1).round(6) AS trailing_ret,
	           iif(price_date.max().month()-price_date.min().month()>12,
	               (nav.last() \ nav.first()).pow(365 \(max(price_date) - min(price_date)))-1, 
	               (nav.last() \ nav.first() - 1)).round(6) AS trailing_ret_a,
               ret.std() AS std_dev,
               ret.skew(false) AS skewness,
               ret.kurtosis(false) - 3 AS kurtosis,
               ret.min() AS wrst_month,
               max( 1 - nav \ nav.cummax() ) AS drawdown
        FROM ret
	    GROUP BY entity_id;

    // var & cvar require return NOT NULL
    // NOTE: DolphinDB supports 4 different ways: normal, logNormal, historical, monteCarlo. we use historical
    t1 = SELECT entity_id, max(end_date) AS end_date, max(price_date) AS price_date,
                ret.VaR('historical', 0.95) AS var,
                ret.CVaR('historical', 0.95) AS cvar
         FROM ret
         WHERE ret.ret > - 1
	     GROUP BY entity_id;

   return (SELECT * FROM t LEFT JOIN t1 ON t.entity_id = t1.entity_id AND t.end_date = t1.end_date AND t.price_date = t1.price_date);

}


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

    lpm = SELECT t.entity_id, max(t.end_date) AS end_date,
                 (sum (rfr.ret - t.ret) \ (t.cnt[0])).pow(1\1) AS lpm1, 
                 (sum2(rfr.ret - t.ret) \ (t.cnt[0])).pow(1\2) AS lpm2, 
                 (sum3(rfr.ret - t.ret) \ (t.cnt[0])).pow(1\3) AS lpm3
          FROM t 
          INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
          WHERE t.ret < rfr.ret
          GROUP BY t.entity_id;

    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_rate) {

    lpm = cal_LPM(ret, risk_free_rate);

    tb = SELECT t.entity_id, 
                l.lpm2[0] AS ds_dev,
                (t.ret - rfr.ret ).mean() \ l.lpm1[0] + 1 AS omega,
                (t.ret - rfr.ret ).mean() \ l.lpm2[0] AS sortino,
                (t.ret - rfr.ret ).mean() \ l.lpm3[0] AS kappa
              FROM ret t
              INNER JOIN lpm l ON t.entity_id = l.entity_id
              INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
              GROUP BY t.entity_id;

	return tb;
}


/*
 *   Alpha & Beta
 *   
 */
def cal_alpha_beta(ret, bmk_ret, risk_free) {

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

    beta = SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY entity_id;

    alpha = SELECT t.entity_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha
            FROM t 
            INNER JOIN beta beta ON t.entity_id = beta.entity_id
            INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
            GROUP BY t.entity_id;

    return ( SELECT * FROM beta AS b INNER JOIN alpha AS a ON a.entity_id = b.entity_id  );
}

/*
 *    Winning Ratio, Tracking Error, Information Ratio
 *    TODO: Information Ratio is way off!
 *          Not sure how to describe a giant number("inf"), for now 999 is used
 */
def cal_benchmark_tracking(ret, bmk_ret) {

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

     t = SELECT entity_id, 
                exc_ret.bucketCount(0:999, 1) \ cnt[0] AS winrate,
                exc_ret.std() AS track_error, 
                exc_ret.mean() / exc_ret.std() AS info
         FROM t0 GROUP BY entity_id

     return t;
}

/*
 *    Sharpe Ratio
 */
def cal_sharpe(ret, std_dev, risk_free_rate) {

	sharpe = SELECT t.entity_id, (t.ret - rfr.ret).mean() / std.std_dev[0] AS sharpe
             FROM ret t
             INNER JOIN std_dev std ON t.entity_id = std.entity_id
             INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
             GROUP BY t.entity_id;

    return sharpe;
}

/*
 *    Treynor Ratio
 */
def cal_treynor(ret, risk_free_rate, beta) {

	t = SELECT *, count(entity_id) AS cnt 
	   FROM ret t 
	   INNER JOIN risk_free_rate 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, ((1 + t.ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0])) - (1 + t.rfr_ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0]))) / beta.beta[0] AS treynor
              FROM t
              INNER JOIN beta AS beta ON t.entity_id = beta.entity_id
              GROUP BY t.entity_id;

    return treynor;
}

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

	jensen = SELECT t.entity_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen
             FROM ret t
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
             INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
             INNER JOIN beta beta ON t.entity_id = beta.entity_id
             GROUP BY t.entity_id;
               
    return jensen;
}

/*
 *    Calmar Ratio
 *    TODO: the result is off
 *
 */
def cal_calmar(ret_a){

	calmar = SELECT entity_id, trailing_ret_a \ drawdown AS calmar
             FROM ret_a;

    return calmar;
}

/*
 *     Modigliani Modigliani Measure (M2)
 *     NOTE: M2 = sharpe * std(benchmark) + risk_free_rate
 */
def cal_m2(ret, bmk_ret, risk_free_rate) {

    m2 = SELECT t.entity_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean() AS m2
         FROM ret t
         INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
         INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
         GROUP BY t.entity_id;

    return m2;
}


/*
 *   Monthly Since_inception_date Indicator Calculation
 *   @param: ret: historical return table
 *           index_ret: historical benchmark return table
 *           risk_free: historical risk free rate table
 *
 *   @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(mutable ret, index_ret, risk_free, freq) {

    if (! freq IN ['d', 'w', 'm', 'q', 's', 'a']) return null;

    // sorting for correct first() and last() value
    ret.sortBy!(['entity_id', 'price_date'], [1, 1]);

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

    // alpha, beta
    alpha_beta = cal_alpha_beta(ret, index_ret, risk_free);

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

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

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

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

    // 卡玛比率
    calmar = cal_calmar(rtn);

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

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

    r = SELECT * FROM rtn a1
                 LEFT JOIN alpha_beta ON a1.entity_id = alpha_beta.entity_id
                 LEFT JOIN bmk_tracking ON a1.entity_id = bmk_tracking.entity_id
                 LEFT JOIN sharpe ON a1.entity_id = sharpe.entity_id
                 LEFT JOIN treynor ON a1.entity_id = treynor.entity_id
                 LEFT JOIN jensen ON a1.entity_id = jensen.entity_id
                 LEFT JOIN calmar ON a1.entity_id = calmar.entity_id
                 LEFT JOIN lpms ON a1.entity_id = lpms.entity_id
                 LEFT JOIN m2 ON a1.entity_id = m2.entity_id

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

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

    UPDATE r 
      SET ds_dev_a = ds_dev * sqrtAnnu,
          alpha_a = alpha * plainAnnu,
          sharpe_a = sharpe * sqrtAnnu,
          sortino_a = sortino * sqrtAnnu,
          jensen_a = jensen * plainAnnu,
          track_error_a = track_error * sqrtAnnu,
          info_a = info * sqrtAnnu,
          m2_a = m2 * plainAnnu
    WHERE price_date.month() - min_date.month() >= 12;
    
    return r;
}