login(`admin, `123456) loadPlugin("ODBC") clearCachedModules() use fundit::fundCalculator use fundit::dataPuller use fundit::returnCalculator end_day = 2024.06.28 //end_day = today() bmk_ret = get_fund_monthly_ret("'IN00000008'", 1990.01.01, end_day, true) risk_free_rate = get_risk_free_rate("'IN0000000M'", 1990.01.01, end_day) /* TEST CASE 1 */ // tb_ret = get_fund_monthly_ret("'MF00003PW1','MF00003PW2'", 1990.01.01, end_day, true) // hedge fund test tb_ret = get_fund_monthly_ret("'HF000004KN','HF000103EU','HF00018WXG'", 1990.01.01, end_day, true) /* TEST CASE 2 tb_updated_funds = get_fund_list_by_nav_updatetime(2024.07.19T10:00:00) // take 1000 funds for testing fund_ids = "'" + tb_updated_funds.fund_id[0:1000].concat("','") + "'" tb_ret = get_fund_monthly_ret(fund_ids, 1990.01.01, end_day, false) */ /* annualized return GIPS */ // 0.122622 -0.163869 -0.077696 ret_incep_a = SELECT fund_id, ret_incep_a FROM tb_ret WHERE end_date = end_day.datetimeFormat("yyyy-MM") ret_ytd_a = SELECT fund_id, ret_ytd_a FROM tb_ret WHERE end_date = end_day.datetimeFormat("yyyy-MM") /* annualized standard deviation */ // 0.210105 null null std_incep = SELECT ret.std() FROM tb_ret GROUP BY fund_id std_incep_a = std_incep * pow(12, 0.5) // 0.357842 null null std_ytd = (SELECT ret.std() FROM tb_ret WHERE end_date >= end_day.year()$STRING + "-01" GROUP BY fund_id) std_ytd_a = std_ytd * pow(12, 0.5) // 0.357842 null null std_6m = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-6, "M").temporalFormat("yyyy-MM") GROUP BY fund_id) std_6m_a = std_6m * pow(12, 0.5) // 0.270642 null null std_1y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-1, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_1y_a = std_1y * pow(12, 0.5) // 0.249324 null null std_2y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-2, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_2y_a = std_2y * pow(12, 0.5) // 0.248138 null null std_3y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-3, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_3y_a = std_3y * pow(12, 0.5) // 0.242779 null null std_4y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-4, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_4y_a = std_4y * pow(12, 0.5) // 0.224387 null null std_5y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-5, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_5y_a = std_5y * pow(12, 0.5) // 0.210569 null null std_10y = (SELECT ret.std() FROM tb_ret WHERE end_date > end_day.temporalAdd(-10, "y").temporalFormat("yyyy-MM") GROUP BY fund_id) std_10y_a = std_10y * pow(12, 0.5) /* annualized downside stdev numbers are slightly off ! NOTE: not sure why cnt[0] need to minus 1 copied from Java implementation*/ // risk_free_rate = 0.001208 // 0.130429 null null t = SELECT *, count(fund_id) AS cnt FROM tb_ret CONTEXT BY fund_id ds_std_dev_incep = SELECT t.fund_id, (sum2(t.ret - rfr.ret) / (t.cnt[0]-1)).pow(0.5) AS ds_std_dev FROM t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.ret < rfr.ret GROUP BY t.fund_id ds_std_dev_incep_a = ds_std_dev_incep * pow(12, 0.5) // 0.277208 null null t = SELECT *, count(fund_id) AS cnt FROM tb_ret WHERE end_date >= end_day.year()$STRING + "-01" CONTEXT BY fund_id ds_std_dev_ytd = SELECT fund_id, (sum2(ret - rfr.ret) / (t.cnt[0]-1)).pow(0.5) AS ds_std_dev FROM t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.ret < rfr.ret GROUP BY fund_id ds_std_dev_ytd_a = ds_std_dev_ytd * pow(12, 0.5) /* beta */ // 0.530483 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date beta_incep = SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY fund_id // 1.819822 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") beta_ytd =SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY fund_id /* annualized alpha -- numbers are off ！ because Java doesn't substract risk free rate */ // 0.110500 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date alpha_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha FROM t INNER JOIN beta_incep beta ON t.fund_id = beta.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date GROUP BY t.fund_id alpha_incep_a = alpha_incep * 12 // -0.11019 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") alpha_ytd = SELECT t.fund_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha FROM t INNER JOIN beta_ytd beta ON t.fund_id = beta.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date GROUP BY t.fund_id alpha_ytd_a = alpha_ytd * 12 /* win rate -- numbers are slightly way off ！effective data count issue? */ // 0.585185 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk, count(fund_id) AS cnt FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date CONTEXT BY t.fund_id winrate_incep = SELECT fund_id, count(fund_id) \ cnt[0] AS winrate FROM t WHERE ret > ret_bmk GROUP BY fund_id // 0.666667 null null t = SELECT t.fund_id, t.end_date, t.ret, bmk.ret AS ret_bmk, count(fund_id) AS cnt FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id winrate_ytd = SELECT fund_id, count(fund_id) \ cnt[0] AS winrate FROM t WHERE ret > ret_bmk GROUP BY fund_id /* skewness */ // -0.090416 skewness_incep = SELECT fund_id, ret.skew(false) AS skewness FROM tb_ret GROUP BY fund_id // -0.513185 skewness_ytd = SELECT fund_id, ret.skew(false) AS skewness FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id /* kurtosis */ // 0.527901 kurtosis_incep = SELECT fund_id, ret.kurtosis(false) - 3 AS kurtosis FROM tb_ret GROUP BY fund_id // 0.961329 kurtosis_ytd = SELECT fund_id, ret.kurtosis(false) - 3 AS kurtosis FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id /* worst month */ // --0.172580 wrst_month_incep = SELECT ret.min() AS ret FROM tb_ret GROUP BY fund_id // -0.172580 wrst_month_ytd = SELECT ret.min() AS ret FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id /* longest down month*/ /* max drawdown */ // 0.305496 0.5621 0.120200 drawdown_incep = SELECT fund_id, max( 1 - nav \ nav.cummax() ) AS drawdown FROM tb_ret GROUP BY fund_id // 0.05645 0 0.06553 drawdown_ytd = SELECT fund_id, max( 1 - nav \ nav.cummax() ) AS drawdown FROM tb_ret WHERE end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY fund_id /* sharpe ratio */ // 0.568739 sharpe_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe FROM tb_ret t INNER JOIN std_incep std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date GROUP BY t.fund_id sharpe_incep_a = sharpe_incep * pow(12, 0.5) // -0.186892 sharpe_ytd = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe FROM tb_ret t INNER JOIN std_ytd std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY t.fund_id sharpe_ytd_a = sharpe_ytd * pow(12, 0.5) // -0.568024 sharpe_1y = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe FROM tb_ret t INNER JOIN std_1y std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date > end_day.temporalAdd(-1, "y").temporalFormat("yyyy-MM") GROUP BY t.fund_id sharpe_1y_a = sharpe_1y * pow(12, 0.5) // -0.131570 sharpe_2y = SELECT t.fund_id, (t.ret - rfr.ret).mean() / std.std_ret[0] AS sharpe FROM tb_ret t INNER JOIN std_2y std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date > end_day.temporalAdd(-2, "y").temporalFormat("yyyy-MM") GROUP BY t.fund_id sharpe_2y_a = sharpe_2y * pow(12, 0.5) /* sortino ratio NOTE: sortino is LPM2*/ // 0.916167 sortino_incep = SELECT t.fund_id, (t.ret - rfr.ret ).mean() / std.ds_std_dev[0] AS sortino FROM tb_ret t INNER JOIN ds_std_dev_incep std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date GROUP BY t.fund_id sortino_incep_a = sortino_incep * pow(12, 0.5) // -0.241256 sortino_ytd = SELECT t.fund_id, (t.ret - rfr.ret ).mean() / std.ds_std_dev[0] AS sortino FROM tb_ret t INNER JOIN ds_std_dev_ytd std ON t.fund_id = std.fund_id INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY t.fund_id sortino_ytd_a = sortino_ytd * pow(12, 0.5) /* sortino MAR -- what's the MAR number? */ /* treynor ratio ytd numbers are way off ! because current java calcuation is not follow GIPS annualization rule*/ // 0.195812 t= SELECT *, count(fund_id) AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id treynor_incep = SELECT t.fund_id, ((1 + t.ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0])) - (1 + t.rfr_ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0]))) / beta.beta[0] AS treynor FROM t INNER JOIN beta_incep beta ON t.fund_id = beta.fund_id GROUP BY t.fund_id // -0.064390 t= SELECT *, count(fund_id) AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id treynor_ytd = SELECT t.fund_id, ((1 + t.ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0])) - (1 + t.rfr_ret).prod().pow(12\iif(t.cnt[0]<12, 12, t.cnt[0]))) / beta.beta[0] AS treynor FROM t INNER JOIN beta_ytd beta ON t.fund_id = beta.fund_id GROUP BY t.fund_id /* jensen's alpha numbers are slightly off ! */ // 0.101781 jensen_incep = SELECT t.fund_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date INNER JOIN beta_incep beta ON t.fund_id = beta.fund_id GROUP BY t.fund_id jensen_incep_a = jensen_incep * 12 // -0.098310 jensen_ytd = SELECT t.fund_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date INNER JOIN beta_ytd beta ON t.fund_id = beta.fund_id WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY t.fund_id jensen_ytd_a = jensen_ytd * 12 /* calmar ratio numbers are way off ! */ // 0.376378 -0.279307 -0.81280 calmar_incep = SELECT fund_id, ar.ret_incep_a \ dd.drawdown AS calmar FROM ret_incep_a ar INNER JOIN drawdown_incep dd ON ar.fund_id = dd.fund_id // 2.567034 999999. -0.81280 calmar_ytd = SELECT fund_id, ar.ret_ytd_a \ dd.drawdown AS calmar FROM ret_ytd_a ar INNER JOIN drawdown_ytd dd ON ar.fund_id = dd.fund_id /* omega ratio numbers are off ! could because Java uses annualized returns and cnt-1 NOTE: omega is LPM1 */ // 1.471981 t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id t1 = SELECT fund_id, (ret - rfr_ret).sum() \ cnt[0] AS ex_ret FROM t0 WHERE ret > rfr_ret GROUP BY fund_id t2 = SELECT fund_id, (rfr_ret - ret).sum() \ cnt[0] AS ds_ret FROM t0 WHERE ret < rfr_ret GROUP BY fund_id omega_incep = SELECT t1.fund_id, t1.ex_ret \ t2.ds_ret AS omega FROM t1 INNER JOIN t2 ON t1.fund_id = t2.fund_id // 0.790864 t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret FROM t0 GROUP BY fund_id t2 = SELECT fund_id, (rfr_ret - ret).sum() \ cnt[0] AS ds_ret FROM t0 WHERE ret < rfr_ret GROUP BY fund_id omega_ytd = SELECT t1.fund_id, 1 + t1.ex_ret \ t2.ds_ret AS omega FROM t1 INNER JOIN t2 ON t1.fund_id = t2.fund_id /* kappa ratio NOTE: kappa is LMP3 numbers are off ! Java's implementation could be very wrong */ // 0.848648 t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date CONTEXT BY t.fund_id t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret FROM t0 GROUP BY fund_id t2 = SELECT fund_id, (rfr_ret - ret).sum3() \ cnt[0] AS ds_ret FROM t0 WHERE ret < rfr_ret GROUP BY fund_id kappa_incep = SELECT t1.fund_id, 1 + t1.ex_ret \ t2.ds_ret.pow(1\3) AS kappa FROM t1 INNER JOIN t2 ON t1.fund_id = t2.fund_id // -0.501813 t0 = SELECT *, fund_id.count() AS cnt FROM tb_ret t INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") CONTEXT BY t.fund_id t1 = SELECT fund_id, (ret - rfr_ret).mean() AS ex_ret FROM t0 GROUP BY fund_id t2 = SELECT fund_id, (rfr_ret - ret).sum3() \ cnt[0] AS ds_ret FROM t0 WHERE ret < rfr_ret GROUP BY fund_id kappa_ytd = SELECT t1.fund_id, 1+ t1.ex_ret \ t2.ds_ret.pow(1\3) AS kappa FROM t1 INNER JOIN t2 ON t1.fund_id = t2.fund_id /* tracking error */ // 0.203268 track_error_incep = SELECT fund_id, (t.ret - bmk.ret).std() AS track_error FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date GROUP BY t.fund_id track_error_incep_a = track_error_incep * pow(12, 0.5) // 0.193291 track_error_ytd = SELECT fund_id, (t.ret - bmk.ret).std() AS track_error FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY t.fund_id track_error_ytd_a = track_error_ytd * pow(12, 0.5) /* information ratio numbers are way off! */ // 5.472180 info_incep = SELECT fund_id, (t.ret - bmk.ret).mean() / (t.ret - bmk.ret).std() AS info FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date GROUP BY t.fund_id info_incep_a = info_incep * pow(12, 0.5) /* modigliani = sharpe * std(benchmark) + risk_free_rate )*/ // 0.141025 m2_incep = SELECT t.fund_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean() FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date GROUP BY t.fund_id m2_incep_a = m2_incep * 12 // -0.020145 m2_ytd = SELECT t.fund_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean() FROM tb_ret t INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date INNER JOIN risk_free_rate rfr ON t.end_date = rfr.end_date WHERE t.end_date >= end_day.datetimeFormat("yyyy-01") GROUP BY t.fund_id m2_ytd_a = m2_ytd * 12 /* historical var numbers are off ! due to different implementations */ CONFIDENCE_LEVAL = 95 // 0.093206 var_incep = SELECT fund_id, - ret.percentile(100-CONFIDENCE_LEVAL) AS var FROM tb_ret GROUP BY fund_id // 0.162951 var_2y = SELECT fund_id, - ret.percentile(100-CONFIDENCE_LEVAL) AS var FROM tb_ret WHERE end_date > end_day.temporalAdd(-2, "y").datetimeFormat("yyyy-MM") GROUP BY fund_id /* historical cvar numbers are slightly off ! due to different implementations of var */ // 0.129327 cvar_incep = SELECT t.fund_id, - t.ret.mean() AS cvar FROM tb_ret t INNER JOIN var_incep var ON t.fund_id = var.fund_id WHERE t.ret < - var.var GROUP BY t.fund_id // 0.17258 cvar_2y = SELECT t.fund_id, - t.ret.mean() AS cvar FROM tb_ret t INNER JOIN var_2y var ON t.fund_id = var.fund_id WHERE end_date > end_day.temporalAdd(-2, "y").datetimeFormat("yyyy-MM") AND t.ret < - var.var GROUP BY t.fund_id /* SMDD var */ /* stutzer index, used by Tian Xiang rating */ // stutzer = (tb_ret - risk_free_rate) \ cvar