module fundit::indicatorCalculator
use fundit::sqlUtilities
use fundit::operationDataPuller
use fundit::performanceDataPuller
use fundit::ms_dataPuller
use fundit::returnCalculator
use fundit::navCalculator
/*
* 将VaR包裹一层，使之成为系统认可的聚集函数
* @param returns <DOUBLE VECTOR>: 非空收益率
* @param method <STRING>: 'normal', 'logNormal', 'historical', 'monteCarlo'
* @param confidenceLevel <DOUBLE>: 置信水平，取值区间(0, 1)
*
*/
defg aggVaR(returns, method, confidenceLevel) {
if(returns.form() != 1) return null;
return returns.VaR(method, confidenceLevel);
}
/*
* 将CVaR包裹一层，使之成为系统认可的聚集函数
* @param returns <DOUBLE VECTOR>: 非空收益率
* @param method <STRING>: 'normal', 'logNormal', 'historical', 'monteCarlo'
* @param confidenceLevel <DOUBLE>: 置信水平，取值区间(0, 1)
*
*/
defg aggCVaR(returns, method, confidenceLevel) {
if(returns.form() != 1) return null;
return returns.CVaR(method, confidenceLevel);
}
/*
* 最大回撤
*
*
*/
defg maxDrawdown(navs) {
return max(1 - navs \ cummax(navs));
}
/*
* 几何平均值
*
*/
defg geometricMean(x){
return x.log().avg().exp()
}
/*
* Trailing Monthly Return, Standard Deviation, Skewness, Kurtosis, Max Drawdown, VaR, CVaR, Calmar Ratio
*
* @param entity_info <TABLE>: xxx_information表，NEED COLUMNS entity_id, inception_date
* @param ret <TABLE>: 收益表，需要有 entity_id, price_dat, end_date, nav
* @param trailing_month <STRING>: trailing X month or ytd, incep
*
* NOTE: standard deviation of Java version is noncompliant-GIPS annulized number
*
* Create: 20240904 Joey
* TODO: SQL is wrong for max drawdowns
* TODO: var, cvar, calmar are off; std dev, skewness, kurtosis are slightly off
* TODO: SQL is missing for portfolio since inception date return
* TODO: Java calculates max drawdown even there is no nav
* TODO: Java ytd worst month could be wrong (i.e. portfolio 166002, 2024-03)
* TODO: arith_mean & gerom_mean ARE NOT TESTED
*
*/
def cal_basic_performance(entity_info, ret, trailing_month) {
// accumulate 版的 skewness, kurtosis, var, cvar 似乎都不对劲，只好找个笨办法来实现
if(trailing_month == 'incep') {
// 需要至少6个数才计算标准差、峰度、偏度
t0 = SELECT price_date.max() AS price_date, nav, ret,
ret.mean() AS arith_mean, (1+ret).prod().pow(1\count(entity_id))-1 AS geom_mean,
iif(count(entity_id) > 5, std(ret), null) AS std_dev,
iif(count(entity_id) > 5, skew(ret, false), null) AS skewness,
iif(count(entity_id) > 5, kurtosis(ret, false), null)-3 AS kurtosis,
min(ret) AS wrst_month
FROM ret
WHERE ret > -1
GROUP BY entity_id
CGROUP BY end_date
ORDER BY entity_id, end_date;
// 年化收益（给后面计算Calmar用）
t0.addColumn(['trailing_ret', 'trailing_ret_a'], [DOUBLE, DOUBLE]);
// MySQL 有bug导致首月ret_1m为空，所以用 prod(1+ret)-1算的有时不对
UPDATE t0
SET trailing_ret = nav\ini_value - 1,
trailing_ret_a = iif(t0.end_date - ei.inception_date.month() > 12, (nav\ini_value).pow(12\(t0.end_date - ei.inception_date.month())) - 1, nav\ini_value - 1)
FROM ej(t0, entity_info ei, 'entity_id');
// 不会用上面的办法算最大回撤, VaR, CVaR
t_var = SELECT entity_id, end_date, ret,
cummax(1 - nav \ cummax(nav)) AS drawdown,
- cumpercentile(ret, 5, 'linear') AS var
FROM ret WHERE ret > -1
CONTEXT BY entity_id;
t_cvar = SELECT entity_id, end_date, drawdown, var,
- cumavg(iif(ret <= -var, ret, null)) AS cvar
FROM t_var
CONTEXT BY entity_id;
t1 = SELECT t0.*, t_cvar.drawdown, t_cvar.var, t_cvar.cvar
FROM t0 LEFT JOIN t_cvar ON t0.entity_id = t_cvar.entity_id AND t0.end_date = t_cvar.end_date
ORDER BY t0.entity_id, t0.end_date;
} else if(trailing_month == 'ytd') {
t1 = SELECT entity_id, end_date, price_date.cummax() AS price_date, nav, ret,
ret.cumavg() AS arith_mean, (1+ret).cumprod().pow(1\cumcount(entity_id))-1 AS geom_mean,
cumprod(1+ret)-1 AS trailing_ret,
cumprod(1+ret)-1 AS trailing_ret_a, // no need annulization for ytd
iif(cumcount(entity_id) > 5, cumstd(ret), null) AS std_dev,
iif(cumcount(entity_id) > 5, tmoving(skew{, false}, end_date, ret, 12), null) AS skewness,
iif(cumcount(entity_id) > 5, tmoving(kurtosis{, false}, end_date, ret, 12)-3, null) AS kurtosis,
cummin(ret) AS wrst_month,
cummax(1 - nav \ cummax(nav)) AS drawdown
FROM ret WHERE ret > -1
CONTEXT BY entity_id, end_date.year()
ORDER BY entity_id, end_date;
// trailing x month
} else {
// 先转成STRING，避免单字符被认为是CHAR而导致转整型出错的结果
win = trailing_month$STRING$INT;
t1 = SELECT entity_id, end_date, price_date.mmax(win) AS price_date, nav, ret,
ret.mavg(win) AS arith_mean, (1+ret).mprod(win).pow(1\mcount(entity_id, win))-1 AS geom_mean,
mprod(1+ret, win)-1 AS trailing_ret,
iif(win > 12,
mprod(1+ret, win).pow(12\win)-1,
mprod(1+ret, win)-1) AS trailing_ret_a,
mstd(ret, win) AS std_dev,
mskew(ret, win, false) AS skewness,
mkurtosis(ret, win, false) - 3 AS kurtosis,
mmin(ret, win) AS wrst_month,
moving(maxDrawdown, nav, win) AS drawdown,
moving(aggVaR{, 'historical', 0.95}, ret, win) AS var,
moving(aggCVaR{, 'historical', 0.95}, ret, win) AS cvar
FROM ret WHERE ret > -1
CONTEXT BY entity_id
ORDER BY entity_id, end_date;
}
t1.addColumn('calmar', DOUBLE);
UPDATE t1 SET calmar = iif(drawdown.round(4) == 0, null, iif(trailing_ret_a\drawdown > 99999, null, trailing_ret_a\drawdown));
return t1;
}
/*
* Lower Partial Moment
* NOTE: risk free rate is used as Minimal Accepted Rate (MAR) here
*
*/
def cal_LPM(ret, risk_free, trailing_month) {
t = SELECT *, cumcount(entity_id) AS cnt FROM ret WHERE ret > -1 CONTEXT BY entity_id;
if(trailing_month == 'incep') {
lpm = SELECT entity_id, end_date,
iif(cumcount(end_date) > 5, (cumsum (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\1), null) AS lpm1,
iif(cumcount(end_date) > 5, (cumsum2 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\2), null) AS lpm2,
iif(cumcount(end_date) > 5, (cumsum3 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\3), null) AS lpm3
FROM t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY entity_id
ORDER BY entity_id, end_date;
} else if(trailing_month == 'ytd') {
lpm = SELECT entity_id, end_date,
iif(cumcount(end_date) > 5, (cumsum (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\1), null) AS lpm1,
iif(cumcount(end_date) > 5, (cumsum2 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\2), null) AS lpm2,
iif(cumcount(end_date) > 5, (cumsum3 (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0)) \ cumcount(end_date)).pow(1\3), null) AS lpm3
FROM t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY entity_id, end_date.year()
ORDER BY entity_id, end_date;
} else {
win = trailing_month$STRING$INT;
lpm = SELECT t.entity_id, t.end_date,
(msum (iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\1) AS lpm1,
(msum2(iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\2) AS lpm2,
(moving(sum3, iif(rfr.ret > t.ret, rfr.ret - t.ret, 0), win) \ mcount(end_date, win)).pow(1\3) AS lpm3
FROM t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY t.entity_id
ORDER BY entity_id, end_date;
}
return lpm;
}
/*
* Downside Devision, Omega Ratio, Sortino Ratio, Kappa Ratio
*
* TODO: Java version of Downside Deviation (LPM2) uses cnt-1 as denominator to calculate mean excess return, which might be wrong
* Java version of Omega could be wrong because Java uses annualized returns and cnt-1
* Java'version of Kappa could be very wrong
*
*/
def cal_omega_sortino_kappa(ret, risk_free, trailing_month) {
lpm = cal_LPM(ret, risk_free, trailing_month);
if(trailing_month == 'incep') {
tb = SELECT t.entity_id, t.end_date,
l.lpm2 AS ds_dev,
iif(l.lpm1.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm1 + 1) AS omega,
iif(l.lpm2.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm2) AS sortino,
iif(l.lpm3.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm3) AS kappa
FROM ret t
INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id;
} else if(trailing_month == 'ytd') {
tb = SELECT t.entity_id, t.end_date,
l.lpm2 AS ds_dev,
iif(l.lpm1.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm1 + 1) AS omega,
iif(l.lpm2.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm2) AS sortino,
iif(l.lpm3.round(4) == 0, NULL, (t.ret - rfr.ret ).cumavg() \ l.lpm3) AS kappa
FROM ret t
INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
tb = SELECT t.entity_id, t.end_date,
l.lpm2 AS ds_dev,
iif(l.lpm1.round(4) == 0, NULL, (t.ret - rfr.ret ).mavg(win) \ l.lpm1 + 1) AS omega,
iif(l.lpm2.round(4) == 0, NULL, (t.ret - rfr.ret ).mavg(win) \ l.lpm2) AS sortino,
iif(l.lpm3.round(4) == 0, NULL, (t.ret - rfr.ret ).mavg(win) \ l.lpm3) AS kappa
FROM ret t
INNER JOIN lpm l ON t.entity_id = l.entity_id AND t.end_date = l.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id;
}
return tb;
}
/*
* Winning Ratio, Tracking Error, Information Ratio
*
* NOTE: mcount is very unique in mFun, because it doesn't support minPeriods(BUG?), while others default minPeriods = window.
* As a result, we have to delete records having winrate but no tracking error and info ratio for the sake of consisence
*
* TODO: Win Rate incept is off, because Java incorrectly takes all end_date as denominator even when benchmark has no price
* Information Ratio is way off!
* Not sure how to describe a giant number("inf"), for now 999 is used
*/
def cal_benchmark_tracking(ret, benchmarks, bmk_ret, trailing_month) {
if(trailing_month == 'incep') {
t0 = SELECT t.entity_id, t.end_date, t.price_date,
t.ret, bmk.ret AS ret_bmk,
t.entity_id.cumcount() AS cnt,
t.ret - bmk.ret AS exc_ret, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
t = SELECT entity_id, end_date, benchmark_id,
iif(cnt > 5, cumcount(iif(exc_ret >= 0, 1, null)) \ cnt, null) AS winrate,
iif(cnt > 5, exc_ret.cumstd(), null) AS track_error,
iif(cnt > 5, iif(exc_ret.cumstd() == 0, null, exc_ret.cumavg() \ exc_ret.cumstd()), 5) AS info
FROM t0
CONTEXT BY entity_id, benchmark_id
ORDER BY entity_id, end_date, benchmark_id;
} else if(trailing_month == 'ytd') {
t0 = SELECT t.entity_id, t.end_date, t.price_date,
t.ret, bmk.ret AS ret_bmk,
t.entity_id.cumcount() AS cnt, t.ret - bmk.ret AS exc_ret, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();
t = SELECT entity_id, end_date, benchmark_id,
iif(cnt > 5, cumcount(iif(exc_ret >= 0, 1, null)) \ cnt, null) AS winrate,
iif(cnt > 5, exc_ret.cumstd(), null) AS track_error,
iif(cnt > 5, iif(exc_ret.cumstd() == 0, null, exc_ret.cumavg() \ exc_ret.cumstd()), null) AS info
FROM t0
CONTEXT BY entity_id, benchmark_id, end_date.year()
ORDER BY entity_id, end_date, benchmark_id;
} else {
win = trailing_month$STRING$INT;
t0 = SELECT t.entity_id, t.end_date, t.price_date,
t.ret, bmk.ret AS ret_bmk,
t.entity_id.mcount(win) AS cnt,
t.ret - bmk.ret AS exc_ret, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
t = SELECT entity_id, end_date, benchmark_id,
iif(cnt > 5, mcount(iif(exc_ret >= 0, 1, null), win) \ cnt, null) AS winrate,
iif(cnt > 5, mstd(exc_ret, win), null) AS track_error,
iif(cnt > 5, iif(mstd(exc_ret, win) == 0, null, mavg(exc_ret, win) \ mstd(exc_ret, win)), null) AS info
FROM t0
CONTEXT BY entity_id, benchmark_id
ORDER BY entity_id, end_date, benchmark_id;
}
return t; //SELECT * FROM t WHERE track_error IS NOT NULL;
}
/*
* Alpha & Beta
* NOTE: alpha of Java version is wrong because it doesn't use risk free rate
*/
def cal_alpha_beta(ret, benchmarks, bmk_ret, risk_free, trailing_month) {
t = SELECT t.entity_id, t.end_date, t.ret, bm.benchmark_id, bmk.ret AS ret_bmk
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
WHERE t.ret > -1
AND bmk.ret > -1;
if(trailing_month == 'incep') {
beta = SELECT entity_id, end_date, benchmark_id,
iif(cumcount(end_date) > 5, ret.cumbeta(ret_bmk), null) AS beta
FROM t CONTEXT BY entity_id, benchmark_id;
alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta,
(t.ret - rfr.ret).cumavg() - beta.beta * (t.ret_bmk - rfr.ret).cumavg() AS alpha
FROM t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY t.entity_id, t.benchmark_id
ORDER BY t.entity_id, t.end_date, t.benchmark_id;
} else if(trailing_month == 'ytd') {
beta = SELECT entity_id, end_date, benchmark_id,
iif(cumcount(end_date) > 5, ret.cumbeta(ret_bmk), null) AS beta
FROM t CONTEXT BY entity_id, benchmark_id, end_date.year();
alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta,
(t.ret - rfr.ret).cumavg() - beta.beta * (t.ret_bmk - rfr.ret).cumavg() AS alpha
FROM t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY t.entity_id, t.benchmark_id, t.end_date.year()
ORDER BY t.entity_id, t.end_date, t.benchmark_id;
} else {
win = trailing_month$STRING$INT;
beta = SELECT entity_id, end_date, benchmark_id,
iif(mcount(end_date, win) > 5, ret.mbeta(ret_bmk, win), null) AS beta
FROM t CONTEXT BY entity_id, benchmark_id;
alpha = SELECT t.entity_id, t.end_date, t.benchmark_id, beta.beta AS beta,
(t.ret - rfr.ret).mavg(win) - beta.beta * (t.ret_bmk - rfr.ret).mavg(win) AS alpha
FROM t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.benchmark_id = beta.benchmark_id AND t.end_date = beta.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
CONTEXT BY t.entity_id, t.benchmark_id
ORDER BY t.entity_id, t.end_date, t.benchmark_id;
}
return alpha;
}
/*
* Upside/Down Capture Return/Ratio
*
* TODO: trailing x month values are way off!
*
*/
def cal_capture_ratio(ret, benchmarks, bmk_ret, trailing_month) {
if(trailing_month == 'incep') {
t1 = SELECT t.entity_id, t.end_date,
(1 + iif(bmk.ret >= 0, t.ret, 0)).cumprod() AS upside_ret,
(1 + iif(bmk.ret >= 0, bmk.ret, 0)).cumprod() AS bmk_upside_ret,
cumcount(iif(bmk.ret >= 0, 1, null)) AS bmk_upside_cnt,
(1 + iif(bmk.ret < 0, t.ret, 0)).cumprod() AS downside_ret,
(1 + iif(bmk.ret < 0, bmk.ret, 0)).cumprod() AS bmk_downside_ret,
cumcount(iif(bmk.ret < 0, 1, null)) AS bmk_downside_cnt,
bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
} else if(trailing_month == 'ytd') {
t1 = SELECT t.entity_id, t.end_date,
(1 + iif(bmk.ret >= 0, t.ret, 0)).cumprod() AS upside_ret,
(1 + iif(bmk.ret >= 0, bmk.ret, 0)).cumprod() AS bmk_upside_ret,
cumcount(iif(bmk.ret >= 0, 1, null)) AS bmk_upside_cnt,
(1 + iif(bmk.ret < 0, t.ret, 0)).cumprod() AS downside_ret,
(1 + iif(bmk.ret < 0, bmk.ret, 0)).cumprod() AS bmk_downside_ret,
cumcount(iif(bmk.ret < 0, 1, null)) AS bmk_downside_cnt,
bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
t1 = SELECT t.entity_id, t.end_date,
(1 + iif(bmk.ret >= 0, t.ret, 0)).mprod(win) AS upside_ret,
(1 + iif(bmk.ret >= 0, bmk.ret, 0)).mprod(win) AS bmk_upside_ret,
mcount(iif(bmk.ret >= 0, 1, null), win) AS bmk_upside_cnt,
(1 + iif(bmk.ret < 0, t.ret, 0)).mprod(win) AS downside_ret,
(1 + iif(bmk.ret < 0, bmk.ret, 0)).mprod(win) AS bmk_downside_ret,
mcount(iif(bmk.ret < 0, 1, null), win) AS bmk_downside_cnt,
bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id AND t.end_date = bmk.end_date
WHERE t.ret > -1
AND bmk.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
}
t = SELECT entity_id, end_date, benchmark_id,
iif(t1.bmk_upside_cnt == 0, NULL, t1.upside_ret.pow(1 \ t1.bmk_upside_cnt)-1) AS upside_capture_ret,
iif(t1.bmk_upside_cnt == 0, NULL, (t1.upside_ret.pow(1 \ t1.bmk_upside_cnt)-1)/(t1.bmk_upside_ret.pow(1 \ t1.bmk_upside_cnt)-1)) AS upside_capture_ratio,
iif(t1.bmk_downside_cnt == 0, NULL, t1.downside_ret.pow(1 \ t1.bmk_downside_cnt)-1) AS downside_capture_ret,
iif(t1.bmk_downside_cnt == 0, NULL, (t1.downside_ret.pow(1 \ t1.bmk_downside_cnt)-1)/(t1.bmk_downside_ret.pow(1 \ t1.bmk_downside_cnt)-1)) AS downside_capture_ratio
FROM t1
ORDER BY entity_id, benchmark_id, end_date;
return t;
}
/*
* Sharpe Ratio
* NOTE: Java version is noncompliant-GIPS annulized number
*/
def cal_sharpe(ret, std_dev, risk_free, trailing_month) {
if(trailing_month == 'incep') {
sharpe = SELECT t.entity_id, t.end_date, iif(std.std_dev.round(4) == 0, null, (t.ret - rfr.ret).cumavg() \ std.std_dev) AS sharpe
FROM ret t
INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE std.std_dev.round(4) <> 0 AND t.ret > -1
CONTEXT BY t.entity_id;
} else if(trailing_month == 'ytd') {
sharpe = SELECT t.entity_id, t.end_date, iif(std.std_dev.round(4) ==0, null, (t.ret - rfr.ret).cumavg() \ std.std_dev) AS sharpe
FROM ret t
INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE std.std_dev.round(4) <> 0 AND t.ret > -1
CONTEXT BY t.entity_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
sharpe = SELECT t.entity_id, t.end_date, iif(std.std_dev.round(4) == 0, null, (t.ret - rfr.ret).mavg(win) \ std.std_dev) AS sharpe
FROM ret t
INNER JOIN std_dev std ON t.entity_id = std.entity_id AND t.end_date = std.end_date
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE std.std_dev.round(4) <> 0 AND t.ret > -1
CONTEXT BY t.entity_id;
}
return sharpe;
}
/*
* Treynor Ratio = annulized excess return / beta
*
* TODO: ytd is off because Java uses non-GIPS rule to annulize return
*/
def cal_treynor(ret, risk_free, beta, trailing_month) {
if(trailing_month == 'incep') {
t = SELECT *, cumcount(entity_id) AS cnt
FROM ret t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
AND rfr.ret > -1
CONTEXT BY t.entity_id;
treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
iif(beta.beta.round(4) == 0, NULL,
((1 + t.ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt))) \ beta.beta) AS treynor
FROM t
INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
CONTEXT BY t.entity_id, beta.benchmark_id;
} else if(trailing_month == 'ytd') {
t = SELECT *, cumcount(entity_id) AS cnt
FROM ret t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
AND rfr.ret > -1
CONTEXT BY t.entity_id, t.end_date.year();
treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
iif(beta.beta.round(4) == 0, NULL,
((1 + t.ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).cumprod().pow(12\iif(t.cnt<12, 12, t.cnt))) \ beta.beta) AS treynor
FROM t
INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
CONTEXT BY t.entity_id, beta.benchmark_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
t = SELECT *, mcount(entity_id, win) AS cnt
FROM ret t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
AND rfr.ret > -1
CONTEXT BY t.entity_id;
treynor = SELECT t.entity_id, t.end_date, beta.benchmark_id,
iif(beta.beta.round(4) == 0, NULL,
((1 + t.ret).mprod(win).pow(12\iif(t.cnt<12, 12, t.cnt)) - (1 + t.rfr_ret).mprod(win).pow(12\iif(t.cnt<12, 12, t.cnt))) \ beta.beta) AS treynor
FROM t
INNER JOIN beta AS beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
CONTEXT BY t.entity_id, beta.benchmark_id;
}
return treynor;
}
/*
* Jensen's Alpha
* TODO: the result is slightly off
*/
def cal_jensen(ret, bmk_ret, risk_free, beta, trailing_month) {
if(trailing_month == 'incep') {
jensen = SELECT t.entity_id, t.end_date, t.ret.cumavg() - rfr.ret.cumavg() - beta.beta * (bmk.ret.cumavg() - rfr.ret.cumavg()) AS jensen, beta.benchmark_id
FROM ret t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, beta.benchmark_id;
} else if(trailing_month == 'ytd') {
jensen = SELECT t.entity_id, t.end_date, t.ret.cumavg() - rfr.ret.cumavg() - beta.beta * (bmk.ret.cumavg() - rfr.ret.cumavg()) AS jensen, beta.benchmark_id
FROM ret t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, beta.benchmark_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
jensen = SELECT t.entity_id, t.end_date, t.ret.mavg(win) - rfr.ret.mavg(win) - beta.beta * (bmk.ret.mavg(win) - rfr.ret.mavg(win)) AS jensen, beta.benchmark_id
FROM ret t
INNER JOIN beta beta ON t.entity_id = beta.entity_id AND t.end_date = beta.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND beta.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, beta.benchmark_id;
}
return jensen;
}
/*
* Modigliani Modigliani Measure (M2)
* NOTE: M2 = sharpe * std(benchmark) + risk_free_rate
* NOTE: Java version is noncompliant-GIPS annulized number
*/
def cal_m2(ret, benchmarks, bmk_ret, risk_free, trailing_month) {
if(trailing_month == 'incep') {
m2 = SELECT t.entity_id, t.end_date,
iif(t.entity_id.cumcount() > 5,
iif(t.ret.cumstd().round(4) == 0, NULL, (t.ret - rfr.ret).cumavg() \ t.ret.cumstd() * bmk.ret.cumstd() + rfr.ret.cumavg()),
NULL) AS m2, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
} else if(trailing_month == 'ytd') {
m2 = SELECT t.entity_id, t.end_date,
iif(t.entity_id.cumcount() > 5,
iif(t.ret.cumstd().round(4) == 0, NULL, (t.ret - rfr.ret).cumavg() \ t.ret.cumstd() * bmk.ret.cumstd() + rfr.ret.cumavg()),
NULL) AS m2, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id, t.end_date.year();
} else {
win = trailing_month$STRING$INT;
m2 = SELECT t.entity_id, t.end_date,
iif(t.entity_id.mcount(win) > 5,
iif(t.ret.mstd(win).round(4) == 0, NULL, (t.ret - rfr.ret).mavg(win) \ t.ret.mstd(win) * bmk.ret.mstd(win) + rfr.ret.mavg(win)),
NULL) AS m2, bm.benchmark_id
FROM ret t
INNER JOIN benchmarks bm ON t.entity_id = bm.entity_id AND t.end_date = bm.end_date
INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id, bm.benchmark_id;
}
return m2;
}
/*
* Morningstar Return, Morningstar Risk-Adjusted Return
*
* TODO: Tax and loads are NOT taken care of
* TODO: Assume Chinese methodology using 3, 5, 10 as number of traling years
* TODO: need verify with reliable results
*
* NOTE: Morningstar methodology requires monthly return for calculation, so that "12" is hard-coded here
*
*
*/
def cal_ms_return(ret, risk_free, trailing_month) {
win = trailing_month$STRING$INT;
r = SELECT t.entity_id, t.end_date,
iif(t.end_date.mmax(win) == t.end_date.mmin(win), NULL,
((1 + t.ret)\(1 + rfr.ret)).mprod(win).pow(12\(t.end_date.mmax(win) - t.end_date.mmin(win)))-1) AS ms_ret_a,
(1 + t.ret).pow(-2).mavg(win).pow(-12/2)-1 AS ms_rar_a
FROM ret t
INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
WHERE t.ret > -1
CONTEXT BY t.entity_id;
return r;
}
/*
* 有效主体-基准对应表
*
* @param benchmarks <TABLE>: entity-benchmark 的对应关系表 NEED COLUMNS: entity_id, end_date, benchmark_id
* @param end_day <DATE>:
* @param trailing_month <STRING>:
* @param isEffectiveOnly <BOOL>: false时与Java相同； true：多了个限制条件：如果区间内有效基准数少于1/2，不做计算
* 比如过去12个月中某BFI只出现2次，小于需要的6次，此BFI不参与 trailing 1 year 计算
*
*/
def get_effective_benchmarks(benchmarks, end_day, trailing_month, isEffectiveOnly) {
min_pct = 0.5;
if(isEffectiveOnly) {
t_dates = SELECT DISTINCT entity_id, end_date FROM benchmarks WHERE end_date <= end_day.month();
if(trailing_month == 'incep') {
t = SELECT entity_id, end_date, end_date.cumcount() AS cnt FROM t_dates CONTEXT BY entity_id;
bmk = SELECT bmk.* FROM benchmarks bmk
INNER JOIN t ON bmk.entity_id = t.entity_id AND bmk.end_date = t.end_date
CONTEXT BY bmk.entity_id, bmk.benchmark_id
HAVING bmk.end_date.cumcount() >= t.cnt * min_pct;
} else if(trailing_month == 'ytd') {
t = SELECT entity_id, end_date, end_date.cumcount() AS cnt FROM t_dates CONTEXT BY entity_id, end_date.year();
bmk = SELECT bmk.* FROM benchmarks bmk
INNER JOIN t ON bmk.entity_id = t.entity_id AND bmk.end_date = t.end_date
CONTEXT BY entity_id, benchmark_id, end_date.year()
HAVING bmk.end_date.cumcount() >= t.cnt * min_pct;
} else {
win = trailing_month$STRING$INT;
t = SELECT entity_id, end_date, end_date.mcount(win) AS cnt FROM t_dates CONTEXT BY entity_id;
bmk = SELECT bmk.* FROM benchmarks bmk
INNER JOIN t ON bmk.entity_id = t.entity_id AND bmk.end_date = t.end_date
CONTEXT BY entity_id, benchmark_id
HAVING bmk.end_date.mcount(win) >= t.cnt * min_pct;
}
} else {
bmk = SELECT * FROM benchmarks WHERE end_date <= end_day.month();
}
return bmk;
}
/*
* Calculation for monthly indicators which need benchmark
*
* @param entity_info <TABLE>: xxx_information表，NEED COLUMNS entity_id, inception_date
* @param benchmark_mapping <TABLE>: entity-benchmark mapping table, NEED COLUMNS entity_id, end_date, benchmark_id
* @param end_day <DATE>;
* @param tb_ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
* @param index_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
* @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
* @param month <INT>: trailing x month
*
* @return: indicators table
*
*
* Create 20240904 模仿Java & python代码在Dolphin中实现，具体计算逻辑可能会有不同 Joey
* TODO: some datapoints require more data, we need a way to disable calculation for them
*
*/
def cal_indicators_with_benchmark(entity_info, benchmark_mapping, end_day, tb_ret, index_ret, risk_free, month) {
r = table(1000:0,
['entity_id', 'end_date', 'benchmark_id', 'winrate', 'track_error', 'info', 'beta', 'alpha', 'treynor', 'jensen', 'm2',
'upside_capture_ret', 'upside_capture_ratio', 'downside_capture_ret', 'downside_capture_ratio',
'alpha_a', 'jensen_a', 'track_error_a', 'info_a', 'm2_a'],
[entity_info.entity_id[0].type(), MONTH, SYMBOL, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE,
DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
if(entity_info.isVoid() || entity_info.size() == 0 || benchmark_mapping.isVoid() || benchmark_mapping.size() == 0 ) return r;
if(tb_ret.isVoid() || tb_ret.size() == 0 || index_ret.isVoid() || index_ret.size() == 0 || risk_free.isVoid() || risk_free.size() == 0 ) return r;
// sorting for correct first() and last() value
ret = SELECT * FROM tb_ret WHERE ret > -1 AND end_date <= end_day.month() ORDER BY entity_id, price_date;
// get the effective benchmarks
benchmarks = get_effective_benchmarks(benchmark_mapping, end_day, month, true);
if(ret.isVoid() || ret.size() == 0 || benchmarks.isVoid() || benchmarks.size() == 0) return r;
// alpha, beta
alpha_beta = cal_alpha_beta(ret, benchmarks, index_ret, risk_free, month);
// 胜率、跟踪误差、信息比率
bmk_tracking = cal_benchmark_tracking(ret, benchmarks, index_ret, month);
// 特雷诺
treynor = cal_treynor(ret, risk_free, alpha_beta, month);
// 詹森指数
jensen = cal_jensen(ret, index_ret, risk_free, alpha_beta, month);
// M2
m2 = cal_m2(ret, benchmarks, index_ret, risk_free, month);
// 上下行捕获率、收益
capture_r = cal_capture_ratio(ret, benchmarks, index_ret, month);
r = SELECT * FROM bmk_tracking a1
LEFT JOIN alpha_beta ON a1.entity_id = alpha_beta.entity_id AND a1.benchmark_id = alpha_beta.benchmark_id AND a1.end_date = alpha_beta.end_date
LEFT JOIN treynor ON a1.entity_id = treynor.entity_id AND a1.benchmark_id = treynor.benchmark_id AND a1.end_date = treynor.end_date
LEFT JOIN jensen ON a1.entity_id = jensen.entity_id AND a1.benchmark_id = jensen.benchmark_id AND a1.end_date = jensen.end_date
LEFT JOIN m2 ON a1.entity_id = m2.entity_id AND a1.benchmark_id = m2.benchmark_id AND a1.end_date = m2.end_date
LEFT JOIN capture_r ON a1.entity_id = capture_r.entity_id AND a1.benchmark_id = capture_r.benchmark_id AND a1.end_date = capture_r.end_date;
// 年化各数据点
// GIPS RULE: NO annulization for data less than 1 year
plainAnnu = get_annulization_multiple('m');
sqrtAnnu = sqrt(get_annulization_multiple('m'));
r.addColumn(['alpha_a', 'jensen_a', 'track_error_a', 'info_a', 'm2_a'],
[DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
UPDATE r
SET alpha_a = alpha * iif(end_date - inception_date.month() > 12, plainAnnu, 1),
jensen_a = jensen * iif(end_date - inception_date.month() > 12, plainAnnu, 1),
track_error_a = track_error * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1),
info_a = info * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1),
m2_a = m2 * iif(end_date - inception_date.month() > 12, plainAnnu, 1)
FROM ej(r, entity_info, 'entity_id');
return r;
}
/*
* Monthly standard indicator calculation
*
* @param entity_info <TABLE>:
* @param benchmarks <TABLE>: entity-benchmark mapping table
* @param end_day <DATE>:
* @param tb_ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
* @param benchmark_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
* @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
* @param month <STRING>:
*
* @return: indicators table
*
*
* Create 20240904 模仿Java & python代码在Dolphin中实现，具体计算逻辑可能会有不同 Joey
*
*/
def cal_indicators(entity_info, benchmarks, end_day, tb_ret, benchmark_ret, risk_free, month) {
if(entity_info.isVoid() || entity_info.size() == 0 || benchmarks.isVoid() || benchmarks.size() == 0 ) return null;
if(tb_ret.isVoid() || tb_ret.size() == 0 || benchmark_ret.isVoid() || benchmark_ret.size() == 0 || risk_free.isVoid() || risk_free.size() == 0 ) return null;
// sorting for correct first() and last() value
ret = SELECT * FROM tb_ret WHERE end_date <= end_day.month() ORDER BY entity_id, price_date;
// 收益、标准差、偏度、峰度、最大回撤、VaR, CVaR、卡玛比率
rtn = cal_basic_performance(entity_info, ret, month);
// 夏普
sharpe = cal_sharpe(ret, rtn, risk_free, month);
// 整合后的下行标准差、欧米伽、索提诺、卡帕
lpms = cal_omega_sortino_kappa(ret, risk_free, month);
// 需要基准的指标们
indicator_with_benchmark = cal_indicators_with_benchmark(entity_info, benchmarks, end_day, ret, benchmark_ret, risk_free, month);
r = SELECT * FROM rtn a1
LEFT JOIN sharpe ON a1.entity_id = sharpe.entity_id AND a1.end_date = sharpe.end_date
LEFT JOIN lpms ON a1.entity_id = lpms.entity_id AND a1.end_date = lpms.end_date
LEFT JOIN indicator_with_benchmark bmk ON a1.entity_id = bmk.entity_id AND a1.end_date = bmk.end_date;
// 晨星收益和风险
if(month$STRING in ['36', '60', '120']) {
ms = cal_ms_return(ret, risk_free, month);
r = SELECT * FROM r LEFT JOIN ms ON r.entity_id = ms.entity_id AND r.end_date = ms.end_date;
}
// 年化各数据点
// GIPS RULE: NO annulization for data less than 1 year
plainAnnu = get_annulization_multiple('m');
sqrtAnnu = sqrt(get_annulization_multiple('m'));
r.addColumn(['std_dev_a', 'ds_dev_a', 'sharpe_a', 'sortino_a'],
[DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
UPDATE r
SET std_dev_a = std_dev * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1),
ds_dev_a = ds_dev * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1),
sharpe_a = sharpe * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1),
sortino_a = sortino * iif(end_date - inception_date.month() > 12, sqrtAnnu, 1)
FROM ej(r, entity_info, 'entity_id');
return r;
}
/*
* Calculate trailing 3m, 6m, ytd, 1y, 2y, 3y, 4y, 5y, 10y and since inception datapoints
*
* @param: func <FUNCTION>: the calculation function
* @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
* @param benchmarks <TABLE>: entity-benchmark mapping table
* @param: end_day <DATE>: 计算截止日期
* @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
* @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
* @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
*
*
*/
def cal_trailing(func, entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate ) {
r_incep = null;
r_ytd = null;
r_3m = null;
r_6m = null;
r_1y = null;
r_2y = null;
r_3y = null;
r_4y = null;
r_5y = null;
r_10y = null;
// incep
r_incep = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, 'incep');
// ytd
r_ytd = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, 'ytd');
// 3m 只需要支持收益计算
r_3m = cal_basic_performance(entity_info, tb_ret, '3');
// 6m
r_6m = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '6');
// 1y
r_1y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '12');
// 2y
r_2y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '24');
// 3y
r_3y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '36');
// 4y
r_4y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '48');
// 5y
r_5y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '60');
// 10y
r_10y = func(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate, '120');
return r_incep, r_ytd, r_3m, r_6m, r_1y, r_2y, r_3y, r_4y, r_5y, r_10y;
}
/*
* Calculate trailing ytd, 3m, 6m, 1y, 2y, 3y, 4y, 5y, 10y and since inception standard indicators
*
* @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
* @param benchmarks <TABLE>: entity-benchmark mapping table
* @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
* @param: end_day <DATE>: 计算截止日期
* @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
* @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
*
*/
def cal_trailing_indicators(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate) {
return cal_trailing(cal_indicators, entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate);
}
/*
* Calculate trailing ytd, 3m, 6m, 1y, 2y, 3y, 4y, 5y, 10y and since inception bfi indicators
*
* @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
* @param benchmarks <TABLE>: entity-benchmark mapping table
* @param: ret <TABLE>: 收益表，NEED COLUMNS entity_id, price_dat, end_date, nav
* @param: end_day <DATE>: 计算截止日期
* @param bmk_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
* @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
*
* NOTE: 3m 的所有指标没有意义
*
*
*/
def cal_trailing_bfi_indicators(entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate) {
return cal_trailing(cal_indicators_with_benchmark, entity_info, benchmarks, end_day, tb_ret, bmk_ret, risk_free_rate);
}
/*
* 通用月度指标计算
*
* @param entity_type <STRING>:
* @param indicator_type <STRING>: PBI, BFI
* @param monthly_returns <TABLE>: NEED COLUMN: entity_id, end_date, price_date, nav, ret
*
* @return <DICT TABLE>: ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y']
*
*/
def cal_monthly_indicators(entity_type, indicator_type, monthly_returns) {
if(find(['MF', 'HF', 'PF', 'MI', 'FI', 'FA'], entity_type) < 0) return null;
if(monthly_returns.isVoid() || monthly_returns.size() < 1) return null;
oldest_date = EXEC price_date.min() FROM monthly_returns;
v_entity_ids = EXEC DISTINCT entity_id FROM monthly_returns;
entity_info = get_entity_info(entity_type, v_entity_ids);
if(entity_info.isVoid() || entity_info.size() == 0) { return null };
end_day = today();
// 取基金和基准的对照表
if(indicator_type == 'BFI') {
benchmark = SELECT entity_id, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id
FROM get_entity_bfi_factors(entity_type, v_entity_ids, oldest_date.month(), end_day.month());
} else {
// 主基准， 对应 xxx_info 中的 primary_benchmark_id; TODO: 没有基准用沪深300顶，哪怕很多情况下不那么正确
benchmark = SELECT entity_id, end_date, iif(benchmark_id.isVoid(), 'IN00000008', benchmark_id) AS benchmark_id
FROM get_entity_primary_benchmark(entity_type, v_entity_ids, oldest_date.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM')) ;
}
// 取所有出现的基准月收益
bmk_ret = get_benchmark_return(benchmark, end_day);
//if(bmk_ret.isVoid() || bmk_ret.size() == 0) { return null; }
// TODO: risk free指数月收益存在fund_performance表，所以先将就用 fund_id 表示。之后统一改为更准确的名字
risk_free_rate = SELECT entity_id AS fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(oldest_date, end_day);
if(risk_free_rate.isVoid() || risk_free_rate.size() == 0) { return null; }
// 指标计算
if(indicator_type == 'BFI') {
t0 = cal_trailing_bfi_indicators(entity_info, benchmark, end_day, monthly_returns, bmk_ret, risk_free_rate);
v_table_name = ['BFI-INCEP', 'BFI-YTD', 'BFI-3M', 'BFI-6M', 'BFI-1Y', 'BFI-2Y', 'BFI-3Y', 'BFI-4Y', 'BFI-5Y', 'BFI-10Y'];
} else {
t0 = cal_trailing_indicators(entity_info, benchmark, end_day, monthly_returns, bmk_ret, risk_free_rate);
v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y'];
}
return dict(v_table_name, t0);
}
/*
* 通用基金经理/公司月度指标计算
*
* @param entity_type <STRING>:
* @param indicator_type <STRING>: PBI, BFI
* @param monthly_returns <TABLE>: NEED COLUMN: entity_id, curve_type, strategy, end_date, price_date, nav, ret
*
* @return <DICT TABLE>: ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y']
*
*/
def cal_mc_monthly_indicators(entity_type, indicator_type, monthly_returns) {
if(find(['PL', 'CO'], entity_type) < 0) return null;
if(monthly_returns.isVoid() || monthly_returns.size() < 1) return null;
oldest_date = EXEC price_date.min() FROM monthly_returns;
v_entity_ids = EXEC DISTINCT entity_id FROM monthly_returns;
if(entity_type == 'PL') {
entity_info = get_personnel_info_for_perf(v_entity_ids);
entity_info.rename!('manager_id', 'entity_id');
}
else {
entity_info = get_company_info_for_perf(v_entity_ids);
entity_info.rename!('company_id', 'entity_id');
}
if(entity_info.isVoid() || entity_info.size() == 0) { return null };
end_day = today();
// 取基金和基准的对照表
if(indicator_type == 'BFI') {
benchmark = SELECT DISTINCT entity_id, curve_type, strategy, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id
FROM get_mc_bfi_factors(entity_type, v_entity_ids, oldest_date.month(), end_day.month());
} else {
// 主基准， 对应公募混合基金平均 FA00000VNB
benchmark = SELECT DISTINCT entity_id, end_date, iif(benchmark_id.isVoid(), 'FA00000VNB', benchmark_id) AS benchmark_id
FROM ej(entity_info, monthly_returns, ['entity_id', 'curve_type', 'strategy']) ;
}
// 取所有出现的基准月收益
bmk_ret = get_benchmark_return(benchmark, end_day);
//if(bmk_ret.isVoid() || bmk_ret.size() == 0) { return null; }
// TODO: risk free指数月收益存在fund_performance表，所以先将就用 fund_id 表示。之后统一改为更准确的名字
risk_free_rate = SELECT entity_id AS fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(oldest_date, end_day);
if(risk_free_rate.isVoid() || risk_free_rate.size() == 0) { return null; }
// 指标计算，因为 cal_traling_xxx 不支持 curve_type & strategy, 所以做个循环
d_rt = dict(STRING, ANY);
v_curve_type = [1, 4, 7];
for(cur in v_curve_type) {
t_ei = SELECT entity_id, inception_date, benchmark_id, ini_value FROM entity_info WHERE curve_type = cur AND strategy = 0;
t_mr = SELECT entity_id, end_date, price_date, ret, nav FROM monthly_returns WHERE curve_type = cur AND strategy = 0;
if(indicator_type == 'BFI') {
v_indicators = cal_trailing_bfi_indicators(t_ei, benchmark, end_day, t_mr, bmk_ret, risk_free_rate);
for(tb in v_indicators)
tb.cj(table(cur AS curve_type, 0 AS strategy));
v_table_name = ['BFI-INCEP', 'BFI-YTD', 'BFI-3M', 'BFI-6M', 'BFI-1Y', 'BFI-2Y', 'BFI-3Y', 'BFI-4Y', 'BFI-5Y', 'BFI-10Y'];
} else {
v_indicators = cal_trailing_indicators(t_ei, benchmark, end_day, t_mr, bmk_ret, risk_free_rate);
for(tb in v_indicators)
UPDATE tb SET curve_type = cur, strategy = 0;
v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y'];
}
d_indicator = dict(v_table_name, v_indicators);
d_rt[cur$STRING] = d_indicator;
}
return d_rt;
}
/*
* 【Morningstar Integration】通用月度指标计算
*
* @param entity_type <STRING>:
* @param indicator_type <STRING>: PBI, BFI
* @param monthly_returns <TABLE>: NEED COLUMN: entity_id, end_date, price_date, nav, ret
*
* @return <DICT TABLE>: ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y']
*
*/
def ms_cal_monthly_indicators(entity_type, indicator_type, monthly_returns) {
if(find(['MF', 'HF', 'PF', 'FA'], entity_type) < 0) return null;
if(monthly_returns.isVoid() || monthly_returns.size() < 1) return null;
oldest_date = EXEC price_date.min() FROM monthly_returns;
v_entity_ids = (SELECT DISTINCT entity_id FROM monthly_returns).entity_id;
entity_info = get_entity_info(entity_type, v_entity_ids);
if(entity_info.isVoid() || entity_info.size() == 0) { return null };
end_day = today();
// 取基金和基准的对照表
if(indicator_type == 'BFI') {
benchmark = SELECT entity_id, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id
FROM get_entity_bfi_factors(entity_type, v_entity_ids, oldest_date.month(), end_day.month());
} else if(indicator_type == 'CAI') {
benchmark = SELECT fund_id AS entity_id, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id
FROM ms_get_fund_category_average(v_entity_ids, oldest_date.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM'));
} else {
// 主基准， 对应 xxx_info 中的 primary_benchmark_id, TODO: 没有基准用沪深300顶，哪怕很多情况下不那么正确
benchmark = SELECT entity_id, end_date, iif(benchmark_id.isNull(), 'IN00000008', benchmark_id) AS benchmark_id
FROM ms_get_entity_primary_benchmark(entity_type, v_entity_ids, oldest_date.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM')) ;
}
// 取所有出现的基准月收益
bmk_ret = get_benchmark_return(benchmark, end_day);
//if(bmk_ret.isVoid() || bmk_ret.size() == 0) { return null; }
// TODO: risk free指数月收益存在fund_performance表，所以先将就用 fund_id 表示。之后统一改为更准确的名字
risk_free_rate = SELECT entity_id AS fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM ms_get_risk_free_rate(oldest_date, end_day);
if(risk_free_rate.isVoid() || risk_free_rate.size() == 0) { return null; }
// 指标计算
if(indicator_type == 'BFI') {
t0 = cal_trailing_bfi_indicators(entity_info, benchmark, end_day, monthly_returns, bmk_ret, risk_free_rate);
v_table_name = ['BFI-INCEP', 'BFI-YTD', 'BFI-3M', 'BFI-6M', 'BFI-1Y', 'BFI-2Y', 'BFI-3Y', 'BFI-4Y', 'BFI-5Y', 'BFI-10Y'];
} else if(indicator_type == 'CAI') {
t0 = cal_trailing_bfi_indicators(entity_info, benchmark, end_day, monthly_returns, bmk_ret, risk_free_rate);
v_table_name = ['CAI-INCEP', 'CAI-YTD', 'CAI-3M', 'CAI-6M', 'CAI-1Y', 'CAI-2Y', 'CAI-3Y', 'CAI-4Y', 'CAI-5Y', 'CAI-10Y'];
} else {
t0 = cal_trailing_indicators(entity_info, benchmark, end_day, monthly_returns, bmk_ret, risk_free_rate);
v_table_name = ['PBI-INCEP', 'PBI-YTD', 'PBI-3M', 'PBI-6M', 'PBI-1Y', 'PBI-2Y', 'PBI-3Y', 'PBI-4Y', 'PBI-5Y', 'PBI-10Y'];
}
return dict(v_table_name, t0);
}