DolphinDB/codes/Test_portfolioIndicatorCalculation.dos

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

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

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

    // 混合因子做基准，同SQL保持一致
    t_dates = table(start_month..end_day.month() AS end_date);
    primary_benchmark = SELECT ei.entity_id, dt.end_date, 'FA00000VNB' 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' // 
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.324279,0.276651,0.095596].round(2);
@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
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.375000,0.250000,0.458333].round(4);
@testing: case = 'trailing2y tracking error' // data 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 == [0.276632,0.237425,0.092227].round(4);
@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' 
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.125000,NULL].round(4);
@testing: case = 'ytd tracking error' // dat 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.345445,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' // 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 == [0.538462,0.491228,0.516667].round(4);
@testing: case = 'incep tracking error' // 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 == [0.317142,0.306286,0.083942].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' // SWAGGER
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 == [2.254328,2.092425,0.591983].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.224853,-0.323083,-0.002128].round(3);

// ytd alpha, beta
alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd beta' // SWAGGER
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,4.674789,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.559542,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' // SWAGGER
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 == [2.084304,2.129578,0.426432].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.029 , -0.004361, 0.007401].round(3);

             
// 2y capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, '24');
@testing: case = 'trailing2y upside capture return' // SWAGGER
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.021896,-0.000820,0.012359].round(4);
@testing: case = 'trailing2y upside capture ratio' // SWAGGER
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.003778 ,-0.048845,0.566586].round(4);
@testing: case = 'trailing2y downside capture return' // SWAGGER
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.062405, -0.060766, -0.012114].round(4);
@testing: case = 'trailing2y 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.03M, 2024.08M)).downside_capture_ratio == [3.137552,3.471522,0.609053].round(4);

// ytd capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, 'ytd');
@testing: case = 'ytd upside capture return' // [FAIL] because Java doesn't calculate ytd for first 5 months
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 == [NULL, 0.033020 ,NULL].round(4);
@testing: case = 'ytd upside capture ratio' // [FAIL] because Java doesn't calculate ytd for first 5 months
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 == [NULL, 1.984191, NULL].round(4);
@testing: case = 'ytd downside capture return' // [FAIL] because Java doesn't calculate ytd for first 5 months
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 == [NULL, -0.118846, NULL].round(4);
@testing: case = 'ytd downside capture ratio' // [FAIL] because Java doesn't calculate ytd for first 5 months
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 == [NULL, 8.745656, NULL].round(4);

// incep capture indicators
capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret, 'incep');
@testing: case = 'incep upside capture return' // SWAGGER
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.040915,0.037614,0.008132].round(4);
@testing: case = 'incep upside capture ratio'  // SWAGGER
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 == [2.009389,1.964035,0.434803].round(4);
@testing: case = 'incep downside capture return'  // SWAGGER
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.04274,-0.044638,-0.00723].round(4);
@testing: case = 'incep 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.03M, 2024.08M)).downside_capture_ratio == [2.172258,2.405102,0.383957].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' // SWAGGER
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.801451, -1.380549, -0.239731].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' // SWAGGER
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' // SWAGGER
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.175911, 0.043442, 0.010115].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' // SWAGGER
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.122531,-0.169323,-0.048736].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 == [NULL,-0.09536,NULL].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' // SWAGGER
assert (select treynor.round(3) as treynor
        from treynor where entity_id in entity_ids and end_date in (2024.03M, 2024.08M)).treynor == [0.000023,-0.020028,-0.005189].round(3);

// 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' // SWAGGER
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.203378,-0.305063,-0.009113].round(4);

// 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'  // SWAGGER
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 == [NULL,-0.506273,NULL].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' // SWAGGER
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.047106, 0.014283 ,-0.002047].round(4);

// 2y m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, '24');
@testing: case = 'trailing2y m2' // SWAGGER
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.055401, -0.093355 , -0.004574].round(4);

// ytd m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'ytd');
@testing: case = 'ytd m2' // SWAGGER
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 == [NULL,-0.07832, NULL].round(4);

// incep m2
m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate, 'incep');
@testing: case = 'incep m2' // SWAGGER
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.032060,0.020154,0.017319].round(4);


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