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