DolphinDB/codes/Test_indicatorCalculator.dos

login(`admin, `123456)
loadPlugin("ODBC")

clearCachedModules()
use fundit::fundCalculator
use fundit::dataPuller
use fundit::returnCalculator
use fundit::indicatorCalculator

/* init values for test cases  */
end_day = 2024.11.30; //2024.06.28;
fund_ids = "'MF0002061J','MF0002058K'" //"'MF00003PW1','MF00003RS0'"; //"'HF000004KN','HF000103EU','HF00018WXG'";
entity_type = "MF"; //'HF';


/* codes from cal_fund_indicators */
    very_old_date = 1990.01.01;
    start_month = very_old_date.month();

    fund_info = get_fund_info(fund_ids);
    fund_info.rename!('fund_id', 'entity_id');

    // 从fund_performance表里读月收益
    tb_ret = get_monthly_ret(entity_type, fund_ids, very_old_date, end_day, true);
    v_end_date = tb_ret.end_date.temporalParse('yyyy-MM');
    tb_ret.replaceColumn!('end_date', v_end_date);

    // 取基金和基准的对照表
    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 entity_id AS fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);


entity_info = fund_info;
entity_ids = fund_ids.strReplace("'", "").split(',');

/* Test START */


// trailing 2 year standard indicators
rtn = cal_basic_performance(entity_info, tb_ret, '24');

@testing: case = 'trailing 2y return'
assert (select rtn.trailing_ret.round(4) as trailing_ret 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).trailing_ret == [-0.285094, -0.297968, 0.031337, 0.013295].round(4);
@testing: case = 'trailing 2y std_dev_a'
assert (select (rtn.std_dev * sqrt(12)).round(4) as std_dev_a 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).std_dev_a == [0.152789, 0.153595, 0.035974, 0.037412].round(4);
@testing: case = 'trailing 2y skewness'
assert (select rtn.skewness.round(2) as skewness 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).skewness == [-0.311358, -0.258296, 0.301213, 0.364476].round(2);
@testing: case = 'trailing 2y kurtosis'
assert (select rtn.kurtosis.round(2) as kurtosis 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kurtosis == [2.425262, 2.284389, 4.222244, 3.397426].round(2);
@testing: case = 'trailing 2y wrst_month'
assert (select rtn.wrst_month.round(4) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).wrst_month == [-0.134443, -0.134443, -0.026231, -0.026231].round(4);
@testing: case = 'trailing 2y max drawdown'
assert (select rtn.drawdown.round(4) as drawdown // values from swagger
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).drawdown == [0.376406, 0.376406, 0.044551, 0.044551].round(4);
@testing: case = 'trailing 2y var'
assert (select rtn.var.round(4) as var // [FAIL]
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).var == [0.120004, 0.120004, 0.023467, 0.023467].round(4);
@testing: case = 'trailing 2y cvar'
assert (select rtn.cvar.round(4) as cvar // [FAIL]
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).cvar == [0.134443, 0.134443, 0.026231, 0.026231].round(4);
@testing: case = 'trailing 2y calmar'
assert (select rtn.calmar.round(4) as calmar // [FAIL]
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).calmar == [-0.381017, -0.404476, 0.283223, 0.123738].round(4);

// year-to-day standard indicators
rtn = cal_basic_performance(entity_info, tb_ret, 'ytd');

@testing: case = 'ytd return'
assert (select rtn.trailing_ret.round(4) as trailing_ret 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).trailing_ret == [-0.026889, -0.061460, 0.025689, 0.011772].round(4);
@testing: case = 'ytd std_dev_a'
assert (select (rtn.std_dev * sqrt(12)).round(4) as std_dev_a 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).std_dev_a == [0.236684, 0.222985, 0.060555, 0.059946].round(4);
@testing: case = 'skewness'
assert (select rtn.skewness.round(2) as skewness 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).skewness == [-1.007946, -0.748978, -0.150076, 0.186127].round(2);
@testing: case = 'ytd kurtosis' 
assert (select rtn.kurtosis.round(3) as kurtosis
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kurtosis == [3.206713, 2.737161, 1.998417, 1.046022].round(3);
@testing: case = 'ytd wrst_month'
assert (select rtn.wrst_month.round(4) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).wrst_month == [-0.134443, -0.134443, -0.026231, -0.026231].round(4);
@testing: case = 'ytd max drawdown'  // values from swagger
assert (select rtn.drawdown.round(4) as drawdown
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).drawdown == [0.006631, 0.038057, 0.002126, 0.014966].round(4);
@testing: case = 'ytd calmar'  // [FAIL]
assert (select rtn.calmar.round(4) as calmar
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).calmar == [-0.131580, -0.368421, 0.885691, 0.432796].round(4);

// since-inception-date standard indicators
rtn = cal_basic_performance(entity_info, tb_ret, 'incep');
@testing: case = 'incep return'
assert (select rtn.trailing_ret.round(4) as trailing_ret 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).trailing_ret == [3.918337, 3.743607, 0.268718, 0.251503].round(4);
@testing: case = 'incep std_dev_a'
assert (select (rtn.std_dev * sqrt(12)).round(2) as std_dev_a 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).std_dev_a == [0.215823, 0.215617, 0.026656, 0.027113].round(2);
@testing: case = 'incep skewness'  // [FAIL] SLIGHTLY OFF
assert (select rtn.skewness.round(2) as skewness
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).skewness == [0.032796, 0.039251, -0.172258, -0.189262].round(2);
@testing: case = 'incep kurtosis'  // [FAIL] SLIGHTLY OFF
assert (select rtn.kurtosis.round(2) as kurtosis
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kurtosis == [1.882971, 1.884570, 3.413939, 3.097421].round(2);
@testing: case = 'incep wrst_month'
assert (select rtn.wrst_month.round(4) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).wrst_month == [-0.221418, -0.221418, -0.026231, -0.026231].round(4);
@testing: case = 'incep drawdown'  // values from swagger
assert (select rtn.drawdown.round(4) as drawdown
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).drawdown == [0.545799, 0.545799, 0.044551, 0.044551].round(4);
@testing: case = 'incep var'  // [FAIL]
assert (select rtn.var.round(4) as var
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).var == [0.098647, 0.098641, 0.012435, 0.012578].round(4);
@testing: case = 'incep cvar'  // [FAIL]
assert (select rtn.cvar.round(4) as cvar
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).cvar == [0.139853, 0.139853, 0.016819, 0.017078].round(4);
@testing: case = 'incep calmar'  // [FAIL]
assert (select rtn.calmar.round(4) as calmar
        from rtn where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).calmar == [0.123467, 0.120115, 0.564536, 0.526222].round(4);

// trailing 2 year lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, '24');
@testing: case = 'trailing 2y downside deviation'  // slightly off
assert (select (lpms.ds_dev * sqrt(12))round(2) as ds_dev
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).ds_dev == [0.139780, 0.141592, 0.024837, 0.027034].round(2);
@testing: case = 'trailing 2y omega'  // [FAIL]
assert (select lpms.omega.round(4) as omega
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).omega == [0.412705, 0.405461, 0.971179, 0.797404].round(4);
@testing: case = 'trailing 2y sortino' // [FAIL]
assert (select (lpms.sortino * sqrt(12)).round(4) as sortino_a
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sortino_a == [-1.228926, -1.274774, -0.022708, -0.341091].round(4);
@testing: case = 'trailing 2y kappa' // [FAIL]
assert (select lpms.kappa.round(4) as kappa
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kappa == [-1.400459, -1.456120, -0.050796, -0.406572].round(4);

// ytd lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd downside deviation'  // [FAIL] slightly off
assert (select (lpms.ds_dev * sqrt(12))round(2) as ds_dev_a
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).ds_dev_a == [0.192270, 0.184391, 0.039096, 0.041043].round(2);
@testing: case = 'ytd omega' // [FAIL]
assert (select lpms.omega.round(4) as omega
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).omega == [0.799024,0.670686,1.477292,1.039491].round(4);
@testing: case = 'ytd sortino' // [FAIL]
assert (select (lpms.sortino * sqrt(12)).round(4) as sortino_a
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sortino_a == [-0.187914,-0.470279,0.783559,0.113041].round(4);
@testing: case = 'ytd kappa' // [FAIL]
assert (select lpms.kappa.round(4) as kappa
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kappa == [-0.352469,-0.644631, 0.862919, 0.090396].round(4);

// since inception lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, 'incep');
@testing: case = 'incep downside deviation'
assert (select (lpms.ds_dev * sqrt(12))round(2) as ds_dev_a
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).ds_dev_a == [0.140304, 0.140258, 0.017540, 0.018214].round(2);
@testing: case = 'incep omega'  // [FAIL] slightly off
assert (select lpms.omega.round(4) as omega
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).omega == [1.217150,1.208021,1.541471,1.437023].round(4);
@testing: case = 'incep sortino' // [FAIL] slightly off
assert (select (lpms.sortino * sqrt(12)).round(4) as sortino_a
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sortino_a == [0.509534,0.496273,0.809670,0.671315].round(4);
@testing: case = 'incep kappa' // [FAIL]
assert (select lpms.kappa.round(4) as kappa
        from lpms where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).kappa == [0.372816,0.358662,0.817694,0.684683].round(4);

// 2y bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, '24');
@testing: case = 'trailing2y win rate'
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).winrate == [0.416667,0.416667,0.375000,0.375000].round(4);
@testing: case = 'trailing2y tracking error'
assert (select (track_error * sqrt(12)).round(3) as track_error_a
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).track_error_a == [0.153428,0.153685,0.039662,0.040514].round(3);
@testing: case = 'trailing2y information ratio'  // [FAIL]
assert (select info.round(4) as info
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).info == [5.609963 ,5.569559 ,24.083419,23.449405].round(4);

// ytd bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, 'ytd');
@testing: case = 'ytd win rate'
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).winrate == [0.428571,0.375000,0.571429,0.500000].round(4);
@testing: case = 'ytd tracking error'
assert (select (track_error * sqrt(12)).round(2) as track_error_a
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).track_error_a == [0.172443,0.161193,0.066815,0.063451].round(2);
@testing: case = 'ytd information ratio' // [FAIL]
assert (select info.round(4) as info
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).info == [5.170620 ,5.392240 ,14.194093,14.655729].round(4);

// incep bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, 'incep');
@testing: case = 'incep win rate' // [FAIL]
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).winrate == [0.446494,0.444853,0.458333,0.453608].round(4);
@testing: case = 'incep tracking error'
assert (select (track_error * sqrt(12)).round(3) as track_error_a
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).track_error_a == [0.133228,0.133079,0.026158,0.026478].round(3);
@testing: case = 'incep information ratio' // [FAIL]
assert (select info.round(4) as info
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).info == [7.727110 ,7.713382 ,37.492177,36.942698].round(4);

// 2y alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, '24');
@testing: case = 'trailing2y beta'
assert (select beta.round(2) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).beta == [0.484263,0.497796,0.035154,0.087369].round(2);
@testing: case = 'trailing2y alpha' // [FAIL]
assert (select (alpha * 12).round(3) as alpha_a
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).alpha_a == [-0.147936,-0.155315,0.014126 ,0.002815].round(3);

// ytd alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd beta' // [FAIL] slightly off
assert (select beta.round(4) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).beta == [2.461836 ,2.444270 ,-1.772254,-0.576641].round(4);
@testing: case = 'ytd alpha' // [FAIL]
assert (select (alpha * 12).round(3) as alpha_a
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).alpha_a == [-0.148226,-0.140843,0.199161 ,0.064237].round(3);

// incep alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
@testing: case = 'incep beta' // [FAIL] slightly off
assert (select beta.round(4) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).beta == [1.418055,1.418947,0.530804,0.540277].round(4);
@testing: case = 'incep alpha' // [FAIL]
assert (select (alpha * 12).round(3) as alpha_a
        from alpha_beta where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).alpha_a == [-0.001796,-0.002707,0.007096 ,0.004842].round(3);


// 2y capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, '24');
@testing: case = 'trailing2y upside capture return'
assert (select upside_capture_ret.round(4) as upside_capture_ret
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ret == [-0.003743,-0.003743, 0.002156, 0.001314].round(4);
@testing: case = 'trailing2y upside capture ratio' 
assert (select upside_capture_ratio.round(3) as upside_capture_ratio
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ratio.round(2) == [-0.223084,-0.223084, 0.365611, 0.237662].round(2);
@testing: case = 'trailing2y downside capture return' 
assert (select downside_capture_ret.round(4) as downside_capture_ret 
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ret == [-0.022388,-0.023754,-0.004776,-0.004776].round(4);
@testing: case = 'trailing2y downside capture ratio'
assert (select downside_capture_ratio.round(4) as downside_capture_ratio 
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ratio == [1.341475,1.357044,1.014519,1.014519].round(4);

// ytd capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, 'ytd');
@testing: case = 'ytd upside capture return'
assert (select upside_capture_ret.round(4) as upside_capture_ret
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ret == [0.027001,0.027001,0.003630,0.001464].round(4);
@testing: case = 'ytd upside capture ratio'
assert (select upside_capture_ratio.round(4) as upside_capture_ratio
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ratio == [1.622489,1.622489,0.501649,0.224863].round(4);
@testing: case = 'ytd downside capture return'
assert (select downside_capture_ret.round(4) as downside_capture_ret 
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ret == [-0.043629,-0.041610,null,null].round(4);
@testing: case = 'ytd downside capture ratio' // SWAGGER
assert (select downside_capture_ratio.round(4) as downside_capture_ratio 
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ratio == [3.491326,3.061985,null,null].round(4);

// incep capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, 'incep');
@testing: case = 'incep upside capture return'
assert (select upside_capture_ret.round(3) as upside_capture_ret
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ret == [0.039939,0.039939,0.003983,0.003744].round(3);
@testing: case = 'incep upside capture ratio'  // [FAIL] slightly off
assert (select upside_capture_ratio.round(3) as upside_capture_ratio
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).upside_capture_ratio == [1.411524,1.411524,0.644453,0.612097].round(3);
@testing: case = 'incep downside capture return'
assert (select downside_capture_ret.round(4) as downside_capture_ret 
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ret == [-0.044225,-0.044131,-0.002280,-0.002280].round(4);
@testing: case = 'incep downside capture ratio' 
assert (select downside_capture_ratio.round(4) as downside_capture_ratio
        from capture_r where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).downside_capture_ratio == [1.490631,1.494426,0.498609,0.498609].round(4);


// 2y sharpe
rtn = cal_basic_performance(entity_info, tb_ret, '24');
sharpe = cal_sharpe(tb_ret, rtn, risk_free_rate, '24');
@testing: case = 'trailing2y sharpe' 
assert (select (sharpe *sqrt(12)).round(4) as sharpe_a
        from sharpe where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sharpe_a == [-1.124291,-1.175150,-0.015678,-0.246472].round(4);

// ytd sharpe
rtn = cal_basic_performance(entity_info, tb_ret, 'ytd');
sharpe = cal_sharpe(tb_ret, rtn, risk_free_rate, 'ytd');
@testing: case = 'ytd sharpe' 
assert (select (sharpe *sqrt(12)).round(3) as sharpe_a
        from sharpe where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sharpe_a == [-0.152652,-0.388883, 0.505893, 0.077395].round(3);

// incep sharpe
rtn = cal_basic_performance(entity_info, tb_ret, 'incep');
sharpe = cal_sharpe(tb_ret, rtn, risk_free_rate, 'incep');
@testing: case = 'incep sharpe'  // [FAIL] slightly off
assert (select (sharpe *sqrt(12)).round(4) as sharpe_a
        from sharpe where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).sharpe_a == [0.331242,0.322824,0.532794,0.450987].round(4);

// 2y treynor
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, '24');
treynor = cal_treynor(tb_ret, risk_free_rate, alpha_beta, '24');
@testing: case = 'trailing2y treynor' 
assert (select treynor.round(2) as treynor
        from treynor where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).treynor == [-0.353583,-0.359078,-0.034126,-0.114400].round(2);

// ytd treynor
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
treynor = cal_treynor(tb_ret, risk_free_rate, alpha_beta, 'ytd');
@testing: case = 'ytd treynor'   // [FAIL]
assert (select treynor.round(4) as treynor
        from treynor where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).treynor == [-0.024472,-0.043101,-0.016843,-0.005406].round(4);

// incep treynor
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
treynor = cal_treynor(tb_ret, risk_free_rate, alpha_beta, 'incep');
@testing: case = 'incep treynor' // [FAIL] slightly off
assert (select treynor.round(4) as treynor
        from treynor where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).treynor == [0.035549,0.034146,0.026646,0.022403].round(4);

// 2y jensen
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, '24');
jensen = cal_jensen(tb_ret, bmk_ret, risk_free_rate, alpha_beta, '24');
@testing: case = 'trailing2y treynor' 
assert (select (jensen*12).round(2) as jensen_a
        from jensen where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).jensen_a == [-0.156508,-0.163600,-0.001910,-0.012240].round(2);

// ytd jensen
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
jensen = cal_jensen(tb_ret, bmk_ret, risk_free_rate, alpha_beta, 'ytd');
@testing: case = 'ytd treynor'  // [FAIL] slightly off if annulize it
assert (select jensen.round(4) as jensen_a
        from jensen where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).jensen_a == [-0.127035,-0.119907,0.158975 ,0.041382].round(4);

// incep jensen
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
jensen = cal_jensen(tb_ret, bmk_ret, risk_free_rate, alpha_beta, 'incep');
@testing: case = 'incep treynor' // [FAIL] slightly off
assert (select (jensen*12).round(4) as jensen_a
        from jensen where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).jensen_a == [ 0.006436 , 0.005535 , -0.000363, -0.002461].round(4);

// 2y m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, '24');
@testing: case = 'trailing2y m2' // [FAIL] slightly off
assert (select (m2*12).round(4) as m2_a
        from m2 where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).m2_a == [-0.072046, -0.076473, 0.016349 , 0.012278].round(4);

// ytd m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd m2' // [FAIL] slightly off
assert (select (m2*12).round(4) as m2_a
        from m2 where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).m2_a == [0.002002, -0.015889,  0.021197,  0.015593].round(4);

// incep m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
@testing: case = 'incep m2' // [FAIL] slightly off
assert (select (m2*12).round(4) as m2_a
        from m2 where entity_id in entity_ids and end_date in (2024.07M, 2024.08M)).m2_a == [0.064601,0.063365,0.026901,0.025155].round(4);


// MS risk free  IN000002EI  IN000002I9
risk_free_rate = SELECT entity_id AS fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('MI', "'IN000002EI'", very_old_date, end_day, true); 

// ms returns
ms_rets = cal_ms_return(tb_ret, risk_free_rate, '36');
@testing: case = 'trailing3y ms return'
assert (exec ms_ret_a.round(3)
       from ms_rets where entity_id in entity_ids and end_date = 2024.11M) == [-0.125943, 0.0136005].round(3);
@testing: case = 'trailing3y MRAR'
assert (exec ms_rar_a.round(3)
       from ms_rets where entity_id in entity_ids and end_date = 2024.11M) == [-0.156894, 0.0121763].round(3);


/* Tests for BFI indicators. CURRENTLY IT IS UN-TESTABLE BECAUSE LOGIC OF BENCHMARK COMPARING IS CHANGED */


/* codes from cal_fund_bfi_indicators  */
    start_month = 1990.01M;

    // 取基金和基准的对照表
    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'));

    bfi_bmk_ret = get_benchmark_return(bfi_benchmark, end_day);