DolphinDB/codes/sample_cal_indicator.dos

login(`admin, `123456)
loadPlugin("ODBC")
clearCachedModules()
use fundit::fundCalculator
use fundit::dataPuller
use fundit::returnCalculator


end_day = 2024.06.28
//end_day = today()

bmk_ret = get_fund_monthly_ret("'IN00000008'", 1990.01.01, end_day, true)
risk_free_rate = get_risk_free_rate("'IN0000000M'", 1990.01.01, end_day)

/* TEST CASE 1  */
// tb_ret = get_fund_monthly_ret("'MF00003PW1','MF00003PW2'", 1990.01.01, end_day, true)
// hedge fund test
tb_ret = get_fund_monthly_ret("'HF000004KN','HF000103EU','HF00018WXG'", 1990.01.01, end_day, true)


/* TEST CASE 2 
tb_updated_funds = get_fund_list_by_nav_updatetime(2024.07.19T10:00:00)

// take 1000 funds for testing
fund_ids = "'" + tb_updated_funds.fund_id[0:1000].concat("','") + "'"

tb_ret = get_fund_monthly_ret(fund_ids, 1990.01.01, end_day, false)
*/


/* annualized return GIPS  */
// 0.122622 -0.163869 -0.077696
ret_incep_a = SELECT fund_id, ret_incep_a FROM tb_ret WHERE end_date = end_day.datetimeFormat("yyyy-MM")
ret_ytd_a = SELECT fund_id, ret_ytd_a FROM tb_ret WHERE end_date = end_day.datetimeFormat("yyyy-MM")

/* annualized standard deviation  */
// 0.210105 null null
std_incep = SELECT ret.std() FROM tb_ret GROUP BY fund_id 
std_incep_a = std_incep * pow(12, 0.5)
// 0.357842 null null
std_ytd = (SELECT ret.std() FROM tb_ret WHERE end_date >= end_day.year()$STRING + "-01" GROUP BY fund_id)
std_ytd_a = std_ytd * pow(12, 0.5)
// 0.357842 null null
std_6m = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-6, "M").temporalFormat("yyyy-MM") GROUP BY fund_id) 
std_6m_a = std_6m * pow(12, 0.5)
// 0.270642 null null
std_1y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-1, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) 
std_1y_a = std_1y * pow(12, 0.5)
// 0.249324 null null
std_2y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-2, "y").temporalFormat("yyyy-MM") GROUP BY fund_id)
std_2y_a = std_2y * pow(12, 0.5)
// 0.248138 null null
std_3y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-3, "y").temporalFormat("yyyy-MM") GROUP BY fund_id)
std_3y_a = std_3y * pow(12, 0.5)
// 0.242779 null null
std_4y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-4, "y").temporalFormat("yyyy-MM") GROUP BY fund_id)
std_4y_a = std_4y * pow(12, 0.5)
// 0.224387 null null
std_5y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-5, "y").temporalFormat("yyyy-MM") GROUP BY fund_id)
std_5y_a = std_5y * pow(12, 0.5)
// 0.210569 null null
std_10y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-10, "y").temporalFormat("yyyy-MM") GROUP BY fund_id)
std_10y_a = std_10y * pow(12, 0.5)

/* annualized downside stdev  numbers are slightly off !
   NOTE: not sure why cnt[0] need to minus 1 copied from Java implementation*/
// risk_free_rate = 0.001208
// 0.130429 null null
t = SELECT *, count(fund_id) AS cnt FROM tb_ret CONTEXT BY fund_id
ds_std_dev_incep = SELECT t.fund_id, (sum2(t.ret - rfr.ret) / (t.cnt[0]-1)).pow(0.5) AS ds_std_dev 
                   FROM t 
                   INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                   WHERE t.ret < rfr.ret
                   GROUP BY t.fund_id
ds_std_dev_incep_a = ds_std_dev_incep * pow(12, 0.5)

// 0.277208 null null
t = SELECT *, count(fund_id) AS cnt FROM tb_ret WHERE end_date >= end_day.year()$STRING + "-01"  CONTEXT BY fund_id
ds_std_dev_ytd = SELECT fund_id, (sum2(ret - rfr.ret) / (t.cnt[0]-1)).pow(0.5) AS ds_std_dev
                 FROM t
                 INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                 WHERE t.ret < rfr.ret
                 GROUP BY fund_id 
ds_std_dev_ytd_a = ds_std_dev_ytd * pow(12, 0.5)


/* beta  */
//  0.530483 null null
t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk
     FROM tb_ret t
     INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date

beta_incep = SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY fund_id

// 1.819822 null null
t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk
     FROM tb_ret t
     INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
     WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")

beta_ytd =SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY fund_id

/* annualized alpha -- numbers are off ！ because Java doesn't substract risk free rate */

 //  0.110500 null null
t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk
      FROM tb_ret t
      INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date

alpha_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha
              FROM t 
              INNER JOIN beta_incep beta ON t.fund_id = beta.fund_id
              INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
              GROUP BY t.fund_id
alpha_incep_a = alpha_incep * 12

 // -0.11019 null null
t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk
    FROM tb_ret t
    INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
    WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")

 alpha_ytd  = SELECT t.fund_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha
              FROM t 
              INNER JOIN beta_ytd beta ON t.fund_id = beta.fund_id
              INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
              GROUP BY t.fund_id
alpha_ytd_a = alpha_ytd * 12

/* win rate -- numbers are slightly way off ！effective data count issue? */

 // 0.585185 null null
 t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk, count(fund_id) AS cnt
     FROM tb_ret t
     INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
     CONTEXT BY t.fund_id

winrate_incep = SELECT fund_id, count(fund_id) \ cnt[0] AS winrate
                FROM t
                WHERE ret > ret_bmk
                GROUP BY fund_id

// 0.666667 null null
 t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk, count(fund_id) AS cnt
     FROM tb_ret t
     INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
     WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
     CONTEXT BY t.fund_id

winrate_ytd = SELECT fund_id, count(fund_id) \ cnt[0] AS winrate
              FROM t
              WHERE ret > ret_bmk
              GROUP BY fund_id

/* skewness */
// -0.090416
skewness_incep = SELECT fund_id, ret.skew(false) AS skewness FROM tb_ret GROUP BY fund_id
// -0.513185
skewness_ytd = SELECT fund_id, ret.skew(false) AS skewness FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id

/* kurtosis */
// 0.527901
kurtosis_incep = SELECT fund_id, ret.kurtosis(false) - 3 AS kurtosis FROM tb_ret GROUP BY fund_id
// 0.961329
kurtosis_ytd = SELECT fund_id, ret.kurtosis(false) - 3 AS kurtosis FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id

/* worst month */
// --0.172580
wrst_month_incep = SELECT ret.min() AS ret FROM tb_ret GROUP BY fund_id
// -0.172580
wrst_month_ytd = SELECT ret.min() AS ret FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id

/* longest down month*/

/* max drawdown */
// 0.305496 0.5621 0.120200
drawdown_incep = SELECT fund_id, max( 1 - nav \ nav.cummax() ) AS drawdown FROM tb_ret GROUP BY fund_id

// 0.05645 0 0.06553
drawdown_ytd = SELECT fund_id, max( 1 - nav \ nav.cummax() ) AS drawdown FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id

/* sharpe ratio */
// 0.568739
sharpe_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe
               FROM tb_ret t
               INNER JOIN std_incep std ON t.fund_id = std.fund_id
               INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
               GROUP BY t.fund_id
sharpe_incep_a = sharpe_incep * pow(12, 0.5)

// -0.186892
sharpe_ytd   = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe
               FROM tb_ret t
               INNER JOIN std_ytd std ON t.fund_id = std.fund_id
               INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
               WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
               GROUP BY t.fund_id
sharpe_ytd_a = sharpe_ytd * pow(12, 0.5)

// -0.568024
sharpe_1y =     SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe
                FROM tb_ret t
                INNER JOIN std_1y std ON t.fund_id = std.fund_id
                INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                WHERE t.end_date > end_day.temporalAdd(-1, "y").temporalFormat("yyyy-MM")
                GROUP BY t.fund_id
sharpe_1y_a = sharpe_1y * pow(12, 0.5)

// -0.131570
sharpe_2y =     SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe
                FROM tb_ret t
                INNER JOIN std_2y std ON t.fund_id = std.fund_id
                INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                WHERE t.end_date > end_day.temporalAdd(-2, "y").temporalFormat("yyyy-MM")
                GROUP BY t.fund_id
sharpe_2y_a = sharpe_2y * pow(12, 0.5)


/* sortino ratio  NOTE: sortino is LPM2*/

// 0.916167
sortino_incep = SELECT t.fund_id, (t.ret - rfr.ret ).mean() / std.ds_std_dev[0] AS sortino
                FROM tb_ret t
                INNER JOIN ds_std_dev_incep std ON t.fund_id = std.fund_id
                INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                GROUP BY t.fund_id
sortino_incep_a = sortino_incep * pow(12, 0.5)

// -0.241256
sortino_ytd   = SELECT t.fund_id, (t.ret - rfr.ret ).mean() / std.ds_std_dev[0] AS sortino
                FROM tb_ret t
                INNER JOIN ds_std_dev_ytd std ON t.fund_id = std.fund_id
                INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date
                WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
                GROUP BY t.fund_id
sortino_ytd_a = sortino_ytd * pow(12, 0.5)

/* sortino MAR  -- what's the MAR number? */



/* treynor ratio  ytd numbers are way off ! because current java calcuation is not follow GIPS annualization rule*/
// 0.195812
t= SELECT *, count(fund_id) AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id
treynor_incep = SELECT t.fund_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_incep beta ON t.fund_id = beta.fund_id
                GROUP BY t.fund_id

// -0.064390
t= SELECT *, count(fund_id) AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id
treynor_ytd =   SELECT t.fund_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_ytd beta ON t.fund_id = beta.fund_id
                GROUP BY t.fund_id


/* jensen's alpha  numbers are slightly off ! */
// 0.101781
jensen_incep = SELECT t.fund_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen
               FROM tb_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_incep beta ON t.fund_id = beta.fund_id
               GROUP BY t.fund_id
jensen_incep_a = jensen_incep * 12

// -0.098310
jensen_ytd   = SELECT t.fund_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen
               FROM tb_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_ytd beta ON t.fund_id = beta.fund_id
               WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
               GROUP BY t.fund_id
jensen_ytd_a = jensen_ytd * 12

/* calmar ratio   numbers are way off !  */
// 0.376378 -0.279307 -0.81280
calmar_incep = SELECT fund_id, ar.ret_incep_a \ dd.drawdown AS calmar
               FROM ret_incep_a ar
               INNER JOIN drawdown_incep dd ON ar.fund_id = dd.fund_id
// 2.567034 999999. -0.81280
calmar_ytd =   SELECT fund_id, ar.ret_ytd_a \ dd.drawdown  AS calmar
               FROM ret_ytd_a ar
               INNER JOIN drawdown_ytd dd ON ar.fund_id = dd.fund_id

/* omega ratio   numbers are off ! could because Java uses annualized returns and cnt-1  NOTE: omega is LPM1 */
// 1.471981
t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id
       
t1 = SELECT fund_id, (ret - rfr_ret).sum() \ cnt[0] AS ex_ret
     FROM t0
     WHERE ret > rfr_ret
     GROUP BY fund_id
t2 = SELECT fund_id, (rfr_ret - ret).sum() \ cnt[0] AS ds_ret
     FROM t0
     WHERE ret < rfr_ret
     GROUP BY fund_id

omega_incep = SELECT t1.fund_id,  t1.ex_ret \ t2.ds_ret AS omega
              FROM t1
              INNER JOIN t2 ON t1.fund_id = t2.fund_id

// 0.790864
t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id
       
t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret
     FROM t0
     GROUP BY fund_id
t2 = SELECT fund_id, (rfr_ret - ret).sum() \ cnt[0] AS ds_ret
     FROM t0
     WHERE ret < rfr_ret
     GROUP BY fund_id

omega_ytd =   SELECT t1.fund_id,  1 + t1.ex_ret \ t2.ds_ret AS omega
              FROM t1
              INNER JOIN t2 ON t1.fund_id = t2.fund_id

/* kappa ratio NOTE: kappa is LMP3  numbers are off ! Java's implementation could be very wrong */ 

// 0.848648
t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id
t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret
     FROM t0
     GROUP BY fund_id
t2 = SELECT fund_id, (rfr_ret - ret).sum3() \ cnt[0] AS ds_ret
     FROM t0
     WHERE ret < rfr_ret
     GROUP BY fund_id

kappa_incep = SELECT t1.fund_id,  1 + t1.ex_ret \ t2.ds_ret.pow(1\3) AS kappa
              FROM t1
              INNER JOIN t2 ON t1.fund_id = t2.fund_id
// -0.501813
t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id
t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret
     FROM t0
     GROUP BY fund_id
t2 = SELECT fund_id, (rfr_ret - ret).sum3() \ cnt[0] AS ds_ret
     FROM t0
     WHERE ret < rfr_ret
     GROUP BY fund_id

kappa_ytd =   SELECT t1.fund_id,  1+ t1.ex_ret \ t2.ds_ret.pow(1\3) AS kappa
              FROM t1
              INNER JOIN t2 ON t1.fund_id = t2.fund_id

/* tracking error */
// 0.203268
track_error_incep = SELECT fund_id,  (t.ret - bmk.ret).std() AS track_error
                    FROM tb_ret t
                    INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
                    GROUP BY t.fund_id
track_error_incep_a = track_error_incep * pow(12, 0.5)
// 0.193291
track_error_ytd =   SELECT fund_id,  (t.ret - bmk.ret).std() AS track_error
                    FROM tb_ret t
                    INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
                    WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
                    GROUP BY t.fund_id
track_error_ytd_a = track_error_ytd * pow(12, 0.5)

/* information ratio  numbers are way off! */
// 5.472180
info_incep = SELECT fund_id, (t.ret - bmk.ret).mean() / (t.ret - bmk.ret).std() AS info
             FROM tb_ret t
             INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
             GROUP BY t.fund_id
info_incep_a = info_incep * pow(12, 0.5)


/* modigliani  = sharpe * std(benchmark) + risk_free_rate )*/ 
// 0.141025
m2_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean()
           FROM tb_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.fund_id
m2_incep_a = m2_incep * 12
// -0.020145
m2_ytd   = SELECT t.fund_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean()
           FROM tb_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
           WHERE t.end_date >= end_day.datetimeFormat("yyyy-01")
           GROUP BY t.fund_id
m2_ytd_a = m2_ytd * 12

/* historical var  numbers are off ! due to different implementations */
CONFIDENCE_LEVAL = 95
// 0.093206
var_incep = SELECT fund_id, - ret.percentile(100-CONFIDENCE_LEVAL) AS var
            FROM tb_ret
            GROUP BY fund_id
// 0.162951
var_2y    = SELECT fund_id, - ret.percentile(100-CONFIDENCE_LEVAL) AS var
            FROM tb_ret
            WHERE end_date > end_day.temporalAdd(-2, "y").datetimeFormat("yyyy-MM")
            GROUP BY fund_id



/* historical cvar  numbers are slightly off ! due to different implementations of var */
// 0.129327
cvar_incep = SELECT t.fund_id, - t.ret.mean() AS cvar
             FROM tb_ret t
             INNER JOIN var_incep var ON t.fund_id = var.fund_id 
             WHERE t.ret < - var.var
             GROUP BY t.fund_id

// 0.17258
cvar_2y    = SELECT t.fund_id, - t.ret.mean() AS cvar
             FROM tb_ret t
             INNER JOIN var_2y var ON t.fund_id = var.fund_id 
             WHERE end_date > end_day.temporalAdd(-2, "y").datetimeFormat("yyyy-MM")
                AND t.ret < - var.var
             GROUP BY t.fund_id

/* SMDD var */


/* stutzer index, used by Tian Xiang rating */ 
// stutzer = (tb_ret - risk_free_rate) \ cvar