DolphinDB/codes/Test_portfolioIndicatorCalculation.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.08.31; //2024.06.28;
isFromNav = false;

portfolio_ids = '166002,166114';
cal_method = 1;

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

    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;

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

        // 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']);
        v_end_date = tb_ret.end_date.temporalParse('yyyy-MM');
        tb_ret.replaceColumn!('end_date', v_end_date);
    }

    // 沪深300做基准，同SQL保持一致
    t_dates = table(start_month..end_day.month() AS end_date);
    primary_benchmark = SELECT ei.entity_id, dt.end_date, 'IN00000008' AS benchmark_id 
                        FROM portfolio_info ei JOIN t_dates dt
                        WHERE dt.end_date >= ei.inception_date.month();

    // 取所有出现的基准月收益
    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);



/* Test START */
entity_info = portfolio_info;
entity_ids = portfolio_ids.split(',')$INT;


// 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(5) as trailing_ret 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).trailing_ret == [-0.450874,-0.561324,-0.023055].round(5);
@testing: case = 'trailing 2y std_dev_a' // [FAIL] slightly off
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.03M, 2024.08M)).std_dev_a == [0.324279,0.276651,0.095596].round(4);
@testing: case = 'trailing 2y skewness' // [FAIL] slightly off
assert (select rtn.skewness.round(3) as skewness 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).skewness == [-0.249279,-0.525918,0.179583].round(3);
@testing: case = 'trailing 2y 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.03M, 2024.08M)).kurtosis == [1.493033 ,3.775385 ,-0.910534].round(2);
@testing: case = 'trailing 2y wrst_month' // [FAIL] slightly off
assert (select rtn.wrst_month.round(3) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).wrst_month == [-0.275322,-0.275002,-0.045004].round(2);
@testing: case = 'trailing 2y max drawdown' // values from swagger and disable max drawdown when there is no NAV
assert (select rtn.drawdown.round(3) as drawdown 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).drawdown == [0.577912,0.536835,0.090399].round(3);
@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.03M, 2024.08M)).var == [0.244860,0.239825,0.043650].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.03M, 2024.08M)).cvar == [0.275322,0.275002,0.045004].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.03M, 2024.08M)).calmar == [-0.410715,-0.570759,-0.140442].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(5) as trailing_ret 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).trailing_ret == [-0.161847,-0.313497,-0.026577].round(5);
@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.03M, 2024.08M)).std_dev_a == [NULL  ,0.415779,NULL].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.03M, 2024.08M)).skewness == [NULL   ,-0.644514,NULL].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.03M, 2024.08M)).kurtosis == [NULL  ,2.637834,NULL ].round(3);
@testing: case = 'ytd wrst_month' // [FAIL] Java's bug? 2024.03M is missing
assert (select rtn.wrst_month.round(4) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).wrst_month == [NULL   ,-0.275002,NULL].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.03M, 2024.08M)).drawdown == [0, 0.180933, 0.008318].round(4);
@testing: case = 'ytd calmar'  // [FAIL] Java's bug? why calculate calmar when drawdown is 0
assert (select rtn.calmar.round(4) as calmar
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).calmar == [-0.453785,-0.949412,-0.667054].round(4);

// since-inception-date standard indicators
rtn = cal_basic_performance(entity_info, tb_ret, 'incep');
@testing: case = 'incep return' // data from SWAGGER, SQL is NULL
assert (select rtn.trailing_ret.round(4) as trailing_ret
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).trailing_ret == [0.077187,-0.117711,0.074025].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.03M, 2024.08M)).std_dev_a == [0.353238,0.341759,0.075581].round(2);
@testing: case = 'incep skewness'  // [FAIL] SLIGHTLY OFF
assert (select rtn.skewness.round(3) as skewness
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).skewness == [-0.005075, 0.093952 , -0.285354].round(3);
@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.03M, 2024.08M)).kurtosis == [0.308324,0.454231,0.797176].round(2);
@testing: case = 'incep wrst_month' 
assert (select rtn.wrst_month.round(3) as wrst_month 
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).wrst_month == [-0.275322, -0.275002, -0.060459].round(3);
@testing: case = 'incep drawdown'  // values from swagger and comparing disabled while NAV is not available
assert (select rtn.drawdown.round(4) as drawdown
        from rtn where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).drawdown == [0.688703,0.705232,0.126529].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.03M, 2024.08M)).var == [0.154051,0.150854,0.038232].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.03M, 2024.08M)).cvar == [0.218524,0.218365,0.047898].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.03M, 2024.08M)).calmar == [0.011767 , -0.045007, 0.095769].round(4);

// trailing 2 year lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, '24');
@testing: case = 'trailing 2y downside deviation'  // [FAIL] data from SWAGGER, slightly off
assert (select (lpms.ds_dev).round(2) as ds_dev
        from lpms where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).ds_dev == [0.079688,0.076460,0.020911].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.03M, 2024.08M)).omega == [0.353282,0.398373,0.872859].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.03M, 2024.08M)).sortino_a == [-0.873490,-1.453471,-0.080323].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.03M, 2024.08M)).kappa == [-1.688884,-1.500436,-0.235215].round(4);

// ytd lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd downside deviation'  // data from SWAGGER, slightly off
assert (select (ds_dev).round(2) as ds_dev_a
        from lpms where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).ds_dev_a == [NULL, 0.109539 , NULL].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.03M, 2024.08M)).omega == [NULL,0.453621,NULL].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.03M, 2024.08M)).sortino_a == [NULL,-1.228103,NULL].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.03M, 2024.08M)).kappa == [NULL, -1.324956, NULL].round(4);

// since inception lpms
lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate, 'incep');
@testing: case = 'incep downside deviation' // data from SWAGGER, slightly off
assert (select (lpms.ds_dev)round(2) as ds_dev_a
        from lpms where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).ds_dev_a == [0.070906,0.069713,0.016293].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.03M, 2024.08M)).omega == [ 1.231226, 0.908086, 1.110811].round(4);
@testing: case = 'incep 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.03M, 2024.08M)).sortino_a == [0.590138,0.063619,0.179043].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.03M, 2024.08M)).kappa == [ 0.510057 ,  -0.207099,  0.163405].round(4);

// 2y bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, '24');
@testing: case = 'trailing2y win rate' // data from SWAGGER, so Java use what benchmark for portfolio?
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).winrate == [0.458333,0.375,0.666667].round(4);
@testing: case = 'trailing2y tracking error' // data from SWAGGER
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.03M, 2024.08M)).track_error_a == [0.250859,0.219641,0.159818].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.03M, 2024.08M)).info == [2.672563 ,2.860281 ,10.190751].round(4);
  
// ytd bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, 'ytd');
@testing: case = 'ytd win rate' // data from SWAGGER
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).winrate == [NULL,0.25,NULL].round(4);
@testing: case = 'ytd tracking error' // datav_end_date from SWAGGER
assert (select (track_error * sqrt(12)).round(4) as track_error_a
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).track_error_a == [NULL,0.276661,NULL].round(4);
@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.03M, 2024.08M)).info == [NULL,1.450085,NULL].round(4);

// incep bechmark tracking
bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret, 'incep');
@testing: case = 'incep win rate' // [FAIL] data from SWAGGER, slightly off (could be fixed by read return rather than calculate it)
assert (select winrate.round(4) as winrate
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).winrate == [0.557692,0.526315,0.566667].round(4);
@testing: case = 'incep tracking error' // [FAIL] data from SWAGGER, slightly off
assert (select (track_error * sqrt(12)).round(4) as track_error_a
        from bmk_tracking where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).track_error_a == [0.304504,0.293106,0.154709].round(4);
@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.03M, 2024.08M)).info == [3.107132 ,3.118240 ,11.039744].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(4) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).beta == [1.100336,1.024391,0.279759].round(4);
@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.03M, 2024.08M)).alpha_a == [-0.173090,-0.273727,0.010882].round(3);

// ytd alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd beta' 
assert (select beta.round(4) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).beta == [NULL,2.332485,NULL].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.03M, 2024.08M)).alpha_a == [NULL,-0.380200,NULL].round(3);

// incep alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
@testing: case = 'incep beta' 
assert (select beta.round(4) as beta
        from alpha_beta where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).beta == [0.991306,1.012644,0.201240].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.03M, 2024.08M)).alpha_a == [0.077511,0.046797,0.017298].round(3);


// 2y capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, '24');
@testing: case = 'trailing2y upside capture return' // [FAIL]
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.03M, 2024.08M)).upside_capture_ret == [-0.003743,-0.003743, 0.002156, 0.001314].round(4);
@testing: case = 'trailing2y upside capture ratio' // [FAIL]
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.03M, 2024.08M)).upside_capture_ratio == [-0.223084,-0.223084, 0.365611, 0.237662].round(4);
@testing: case = 'trailing2y downside capture return' // [FAIL]
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.03M, 2024.08M)).downside_capture_ret == [-0.022388,-0.023754,-0.004776,-0.004776].round(4);
@testing: case = 'trailing2y downside capture ratio' // [FAIL]
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.03M, 2024.08M)).downside_capture_ratio == [1.341475,1.391050,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.03M, 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.03M, 2024.08M)).upside_capture_ratio == [1.622489,1.622489,0.501649,0.224863].round(4);
@testing: case = 'ytd downside capture return' // [FAIL] we don't have null values
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.03M, 2024.08M)).downside_capture_ret == [-0.043629,-0.041610,null,null].round(4);
@testing: case = 'ytd downside capture ratio' // [FAIL] we don't have null values
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.03M, 2024.08M)).downside_capture_ratio == [3.491326,3.412813,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.03M, 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.03M, 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(2) as downside_capture_ret 
        from capture_r where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).downside_capture_ret == [-0.044225,-0.044131,-0.002280,-0.002280].round(2);
@testing: case = 'incep downside capture ratio'  // [FAIL] slightly off
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.03M, 2024.08M)).downside_capture_ratio == [1.490631,1.497454,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' // [FAIL]
assert (select (sharpe *sqrt(12)).round(4) as sharpe_a
        from sharpe where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).sharpe_a == [ -0.553607, -1.380549, -0.046602].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.03M, 2024.08M)).sharpe_a == [ NULL,-1.159093,NULL].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] 
assert (select (sharpe *sqrt(12)).round(4) as sharpe_a
        from sharpe where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).sharpe_a == [0.332846, 0.043442, 0.121411].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.03M, 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.03M, 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.03M, 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.03M, 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.03M, 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.03M, 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.03M, 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.03M, 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.03M, 2024.08M)).m2_a == [0.064601,0.063365,0.026901,0.025155].round(4);


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