indicatorCalculator.dos 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523
  1. module fundit::indicatorCalculator
  2. use fundit::dataPuller
  3. use fundit::returnCalculator
  4. use fundit::navCalculator
  5. /*
  6. * Annulized multiple
  7. */
  8. def get_annulization_multiple(freq) {
  9. ret = 1;
  10. if (freq == 'd') {
  11. ret = 252; // We have differences here between Java and DolphinDB, Java uses 365.25 days
  12. } else if (freq == 'w') {
  13. ret = 52;
  14. } else if (freq == 'm') {
  15. ret = 12;
  16. } else if (freq == 'q') {
  17. ret = 4;
  18. } else if (freq == 's') {
  19. ret = 2;
  20. } else if (freq == 'a') {
  21. ret = 1;
  22. }
  23. return ret;
  24. }
  25. /*
  26. * Trailing Return, Standard Deviation, Skewness, Kurtosis, Max Drawdown, VaR, CVaR
  27. * @param ret: 收益表,需要有 entity_id, price_dat, end_date, nav
  28. * @param freq: 数据频率,d, w, m, q, s, a
  29. *
  30. * NOTE: standard deviation of Java version is noncompliant-GIPS annulized number
  31. *
  32. * Create: 20240904 Joey
  33. * TODO: var and cvar are silightly off compared with Java version
  34. *
  35. */
  36. def cal_basic_performance(ret, freq) {
  37. t = SELECT entity_id, max(end_date) AS end_date, max(price_date) AS price_date, min(price_date) AS min_date,
  38. //(nav.last() \ nav.first() - 1).round(6) AS trailing_ret,
  39. ((1+ret).prod()-1).round(6) AS trailing_ret,
  40. iif(price_date.max().month()-price_date.min().month()>12,
  41. //(nav.last() \ nav.first()).pow(365 \(max(price_date) - min(price_date)))-1,
  42. //(nav.last() \ nav.first() - 1)).round(6) AS trailing_ret_a,
  43. ((1+ret).prod()-1) * sqrt(get_annulization_multiple(freq)),
  44. ((1+ret).prod()-1)).round(6) AS trailing_ret_a,
  45. ret.std() AS std_dev,
  46. ret.skew(false) AS skewness,
  47. ret.kurtosis(false) - 3 AS kurtosis,
  48. ret.min() AS wrst_month,
  49. max( 1 - nav \ nav.cummax() ) AS drawdown
  50. FROM ret
  51. GROUP BY entity_id;
  52. // var & cvar require return NOT NULL
  53. // NOTE: DolphinDB supports 4 different ways: normal, logNormal, historical, monteCarlo. we use historical
  54. t1 = SELECT entity_id, max(end_date) AS end_date, max(price_date) AS price_date,
  55. ret.VaR('historical', 0.95) AS var,
  56. ret.CVaR('historical', 0.95) AS cvar
  57. FROM ret
  58. WHERE ret.ret > - 1
  59. GROUP BY entity_id;
  60. return (SELECT * FROM t LEFT JOIN t1 ON t.entity_id = t1.entity_id AND t.end_date = t1.end_date AND t.price_date = t1.price_date);
  61. }
  62. /*
  63. * Lower Partial Moment
  64. * NOTE: risk free rate is used as Minimal Accepted Rate (MAR) here
  65. *
  66. */
  67. def cal_LPM(ret, risk_free) {
  68. t = SELECT *, count(entity_id) AS cnt FROM ret WHERE ret > -1 CONTEXT BY entity_id;
  69. lpm = SELECT t.entity_id, max(t.end_date) AS end_date,
  70. (sum (rfr.ret - t.ret) \ (t.cnt[0])).pow(1\1) AS lpm1,
  71. (sum2(rfr.ret - t.ret) \ (t.cnt[0])).pow(1\2) AS lpm2,
  72. (sum3(rfr.ret - t.ret) \ (t.cnt[0])).pow(1\3) AS lpm3
  73. FROM t
  74. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  75. WHERE t.ret < rfr.ret
  76. GROUP BY t.entity_id;
  77. return lpm;
  78. }
  79. /*
  80. * Downside Devision, Omega Ratio, Sortino Ratio, Kappa Ratio
  81. *
  82. * TODO: Java version of Downside Deviation (LPM2) uses cnt-1 as denominator to calculate mean excess return, which might be wrong
  83. * Java version of Omega could be wrong because Java uses annualized returns and cnt-1
  84. * Java'version of Kappa could be very wrong
  85. *
  86. */
  87. def cal_omega_sortino_kappa(ret, risk_free) {
  88. lpm = cal_LPM(ret, risk_free);
  89. tb = SELECT t.entity_id,
  90. l.lpm2[0] AS ds_dev,
  91. (t.ret - rfr.ret ).mean() \ l.lpm1[0] + 1 AS omega,
  92. (t.ret - rfr.ret ).mean() \ l.lpm2[0] AS sortino,
  93. (t.ret - rfr.ret ).mean() \ l.lpm3[0] AS kappa
  94. FROM ret t
  95. INNER JOIN lpm l ON t.entity_id = l.entity_id
  96. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  97. GROUP BY t.entity_id;
  98. return tb;
  99. }
  100. /*
  101. * Alpha & Beta
  102. * NOTE: alpha of Java version is noncompliant-GIPS annulized number
  103. */
  104. def cal_alpha_beta(ret, bmk_ret, risk_free) {
  105. t = SELECT t.entity_id, t.end_date, t.ret, bmk.ret AS ret_bmk
  106. FROM ret t
  107. INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
  108. WHERE t.ret > -1
  109. AND bmk.ret > -1;
  110. beta = SELECT ret.beta(ret_bmk) AS beta FROM t GROUP BY entity_id;
  111. alpha = SELECT t.entity_id, (t.ret - rfr.ret).mean() - beta.beta[0] * (t.ret_bmk - rfr.ret).mean() AS alpha
  112. FROM t
  113. INNER JOIN beta beta ON t.entity_id = beta.entity_id
  114. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  115. GROUP BY t.entity_id;
  116. return ( SELECT * FROM beta AS b INNER JOIN alpha AS a ON a.entity_id = b.entity_id );
  117. }
  118. /*
  119. * Winning Ratio, Tracking Error, Information Ratio
  120. * TODO: Information Ratio is way off!
  121. * Not sure how to describe a giant number("inf"), for now 999 is used
  122. */
  123. def cal_benchmark_tracking(ret, bmk_ret) {
  124. t0 = SELECT t.entity_id, t.end_date, t.ret, bmk.ret AS ret_bmk, count(entity_id) AS cnt, (t.ret - bmk.ret) AS exc_ret
  125. FROM ret t
  126. INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
  127. WHERE t.ret > -1
  128. AND bmk.ret > -1
  129. CONTEXT BY t.entity_id;
  130. t = SELECT entity_id,
  131. exc_ret.bucketCount(0:999, 1) \ cnt[0] AS winrate,
  132. exc_ret.std() AS track_error,
  133. exc_ret.mean() / exc_ret.std() AS info
  134. FROM t0 GROUP BY entity_id
  135. return t;
  136. }
  137. /*
  138. * Sharpe Ratio
  139. * NOTE: Java version is noncompliant-GIPS annulized number
  140. */
  141. def cal_sharpe(ret, std_dev, risk_free) {
  142. sharpe = SELECT t.entity_id, (t.ret - rfr.ret).mean() / std.std_dev[0] AS sharpe
  143. FROM ret t
  144. INNER JOIN std_dev std ON t.entity_id = std.entity_id
  145. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  146. WHERE std.std_dev[0] <> 0
  147. GROUP BY t.entity_id;
  148. return sharpe;
  149. }
  150. /*
  151. * Treynor Ratio
  152. */
  153. def cal_treynor(ret, risk_free, beta) {
  154. t = SELECT *, count(entity_id) AS cnt
  155. FROM ret t
  156. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  157. WHERE t.ret > -1
  158. AND rfr.ret > -1
  159. CONTEXT BY t.entity_id;
  160. treynor = SELECT t.entity_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
  161. FROM t
  162. INNER JOIN beta AS beta ON t.entity_id = beta.entity_id
  163. GROUP BY t.entity_id;
  164. return treynor;
  165. }
  166. /*
  167. * Jensen's Alpha
  168. * TODO: the result is slightly off
  169. */
  170. def cal_jensen(ret, bmk_ret, risk_free, beta) {
  171. jensen = SELECT t.entity_id, t.ret.mean() - rfr.ret.mean() - beta.beta[0] * (bmk.ret.mean() - rfr.ret.mean()) AS jensen
  172. FROM ret t
  173. INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
  174. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  175. INNER JOIN beta beta ON t.entity_id = beta.entity_id
  176. GROUP BY t.entity_id;
  177. return jensen;
  178. }
  179. /*
  180. * Calmar Ratio
  181. * TODO: the result is off
  182. *
  183. */
  184. def cal_calmar(ret_a){
  185. calmar = SELECT entity_id, trailing_ret_a \ drawdown AS calmar
  186. FROM ret_a;
  187. return calmar;
  188. }
  189. /*
  190. * Modigliani Modigliani Measure (M2)
  191. * NOTE: M2 = sharpe * std(benchmark) + risk_free_rate
  192. * NOTE: Java version is noncompliant-GIPS annulized number
  193. */
  194. def cal_m2(ret, bmk_ret, risk_free) {
  195. m2 = SELECT t.entity_id, (t.ret - rfr.ret).mean() / t.ret.std() * bmk.ret.std() + rfr.ret.mean() AS m2
  196. FROM ret t
  197. INNER JOIN bmk_ret bmk ON t.end_date = bmk.end_date
  198. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  199. GROUP BY t.entity_id;
  200. return m2;
  201. }
  202. /*
  203. * Morningstar Return, Morningstar Risk-Adjusted Return
  204. *
  205. * TODO: Tax and loads are NOT taken care of
  206. * TODO: Assume Chinese methodology using 3, 5, 10 as number of traling years
  207. *
  208. * NOTE: Morningstar methodology requires monthly return for calculation, so that "12" is hard-coded here
  209. *
  210. *
  211. */
  212. def cal_ms_return(ret, risk_free) {
  213. r = SELECT t.entity_id, t.end_date.max() AS end_date, t.price_date.max() AS price_date, t.price_date.min() AS min_date,
  214. ((1 + t.ret)\(1 + rfr.ret)).prod().pow(12\(t.end_date.max() - t.end_date.min()))-1 AS ms_ret_a,
  215. (1 + t.ret).pow(-2).mean().pow(-12/2)-1 AS ms_rar_a
  216. FROM ret t
  217. INNER JOIN risk_free rfr ON t.end_date = rfr.end_date
  218. GROUP BY t.entity_id;
  219. return r;
  220. }
  221. /*
  222. * Monthly Since_inception_date Indicator Calculation
  223. * @param: ret <TABLE>: 收益表,NEED COLUMNS entity_id, price_dat, end_date, nav
  224. * @param index_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
  225. * @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
  226. * @param freq <CHAR>: 数据频率,d, w, m, q, s, a
  227. *
  228. * @return: indicators table
  229. *
  230. *
  231. * Create 20240904 模仿Java & python代码在Dolphin中实现,具体计算逻辑可能会有不同 Joey
  232. * TODO: some datapoints require more data, we need a way to disable calculation for them
  233. *
  234. */
  235. def cal_indicators(mutable ret, index_ret, risk_free, freq) {
  236. if (! freq IN ['d', 'w', 'm', 'q', 's', 'a']) return null;
  237. // sorting for correct first() and last() value
  238. ret.sortBy!(['entity_id', 'price_date'], [1, 1]);
  239. // 收益、标准差、偏度、峰度、最大回撤、VaR, CVaR
  240. rtn = cal_basic_performance(ret, freq);
  241. // alpha, beta
  242. alpha_beta = cal_alpha_beta(ret, index_ret, risk_free);
  243. // 胜率、跟踪误差、信息比率
  244. bmk_tracking = cal_benchmark_tracking(ret, index_ret);
  245. // 夏普
  246. sharpe = cal_sharpe(ret, rtn, risk_free);
  247. // 特雷诺
  248. treynor = cal_treynor(ret, risk_free, alpha_beta);
  249. // 詹森指数
  250. jensen = cal_jensen(ret, index_ret, risk_free, alpha_beta);
  251. // 卡玛比率
  252. calmar = cal_calmar(rtn);
  253. // 整合后的下行标准差、欧米伽、索提诺、卡帕
  254. lpms = cal_omega_sortino_kappa(ret, risk_free);
  255. // M2
  256. m2 = cal_m2(ret, index_ret, risk_free);
  257. r = SELECT * FROM rtn a1
  258. LEFT JOIN alpha_beta ON a1.entity_id = alpha_beta.entity_id
  259. LEFT JOIN bmk_tracking ON a1.entity_id = bmk_tracking.entity_id
  260. LEFT JOIN sharpe ON a1.entity_id = sharpe.entity_id
  261. LEFT JOIN treynor ON a1.entity_id = treynor.entity_id
  262. LEFT JOIN jensen ON a1.entity_id = jensen.entity_id
  263. LEFT JOIN calmar ON a1.entity_id = calmar.entity_id
  264. LEFT JOIN lpms ON a1.entity_id = lpms.entity_id
  265. LEFT JOIN m2 ON a1.entity_id = m2.entity_id
  266. // 年化各数据点
  267. // GIPS RULE: NO annulization for data less than 1 year
  268. plainAnnu = get_annulization_multiple(freq);
  269. sqrtAnnu = sqrt(get_annulization_multiple(freq));
  270. r.addColumn(['std_dev_a', 'ds_dev_a', 'alpha_a', 'sharpe_a', 'sortino_a', 'jensen_a', 'track_error_a', 'info_a', 'm2_a'],
  271. [DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
  272. UPDATE r
  273. SET std_dev_a = std_dev * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  274. ds_dev_a = ds_dev * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  275. alpha_a = alpha * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1),
  276. sharpe_a = sharpe * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  277. sortino_a = sortino * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  278. jensen_a = jensen * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1),
  279. track_error_a = track_error * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  280. info_a = info * iif(price_date.month() - min_date.month() >= 11, sqrtAnnu, 1),
  281. m2_a = m2 * iif(price_date.month() - min_date.month() >= 11, plainAnnu, 1);
  282. return r;
  283. }
  284. /*
  285. * Calculate trailing 6m, ytd, 1y, 2y, 3y, 4y, 5y, 10y and since inception indicators
  286. *
  287. * @param: entity_info <TABLE>: basic information of entity, NEED COLUMNS entity_id, inception_date
  288. * @param: ret <TABLE>: 收益表,NEED COLUMNS entity_id, price_dat, end_date, nav
  289. * @param: end_day <DATE>: 计算截止日期
  290. * @param index_ret <TABLE>: historical benchmark return table, NEED COLUMNS fund_id, end_date, ret
  291. * @param risk_free <TABLE>: historical risk free rate table, NEED COLUMNS fund_id, end_date, ret
  292. * @param freq <CHAR>: 数据频率,d, w, m, q, s, a
  293. *
  294. */
  295. def cal_all_trailing_indicators(entity_info, mutable tb_ret, end_day, bmk_ret, risk_free_rate, freq) {
  296. r_incep = null;
  297. r_ytd = null;
  298. r_6m = null;
  299. r_1y = null;
  300. r_2y = null;
  301. r_3y = null;
  302. r_4y = null;
  303. r_5y = null;
  304. r_10y = null;
  305. r_ms_3y = null;
  306. r_ms_5y = null;
  307. r_ms_10y = null;
  308. // since inception
  309. if(tb_ret.size() > 0) {
  310. r_incep = cal_indicators(tb_ret, bmk_ret, risk_free_rate, 'm');
  311. }
  312. // ytd
  313. tb_ret_ytd = SELECT * FROM tb_ret WHERE end_date >= end_day.yearBegin().month();
  314. if(tb_ret_ytd.size() > 0) {
  315. r_ytd = cal_indicators(tb_ret_ytd, bmk_ret, risk_free_rate, 'm');
  316. }
  317. // trailing 6m
  318. tb_ret_6m = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  319. WHERE r.end_date > end_day.month()-6 AND (end_day.month() - ei.inception_date.month()) >= 6;
  320. if(tb_ret_6m.size() > 0) {
  321. r_6m = cal_indicators(tb_ret_6m, bmk_ret, risk_free_rate, 'm');
  322. }
  323. // trailing 1y
  324. tb_ret_1y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  325. WHERE r.end_date > end_day.month()-12 AND (end_day.month() - ei.inception_date.month()) >= 12;
  326. if(tb_ret_1y.size() > 0) {
  327. r_1y = cal_indicators(tb_ret_1y, bmk_ret, risk_free_rate, 'm');
  328. }
  329. // trailing 2y
  330. tb_ret_2y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  331. WHERE r.end_date > end_day.month()-24 AND (end_day.month() - ei.inception_date.month()) >= 24;
  332. if(tb_ret_2y.size() > 0) {
  333. r_2y = cal_indicators(tb_ret_2y, bmk_ret, risk_free_rate, 'm');
  334. }
  335. // trailing 3y
  336. tb_ret_3y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  337. WHERE r.end_date > end_day.month()-36 AND (end_day.month() - ei.inception_date.month()) >= 36;
  338. if(tb_ret_3y.size() > 0) {
  339. r_3y = cal_indicators(tb_ret_3y, bmk_ret, risk_free_rate, 'm');
  340. r_ms_3y = cal_ms_return(tb_ret_3y, risk_free_rate);
  341. }
  342. // trailing 4y
  343. tb_ret_4y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  344. WHERE r.end_date > end_day.month()-48 AND (end_day.month() - ei.inception_date.month()) >= 48;
  345. if(tb_ret_4y.size() > 0) {
  346. r_4y = cal_indicators(tb_ret_4y, bmk_ret, risk_free_rate, 'm');
  347. }
  348. // trailing 5y
  349. tb_ret_5y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  350. WHERE r.end_date > end_day.month()-60 AND (end_day.month() - ei.inception_date.month()) >= 60;
  351. if(tb_ret_5y.size() > 0) {
  352. r_5y = cal_indicators(tb_ret_5y, bmk_ret, risk_free_rate, 'm');
  353. r_ms_5y = cal_ms_return(tb_ret_5y, risk_free_rate);
  354. }
  355. // trailing 10y
  356. tb_ret_10y = SELECT * FROM tb_ret r INNER JOIN entity_info ei ON r.entity_id = ei.entity_id
  357. WHERE r.end_date > end_day.month()-120 AND (end_day.month() - ei.inception_date.month()) >= 120;
  358. if(tb_ret_10y.size() > 0) {
  359. r_10y = cal_indicators(tb_ret_10y, bmk_ret, risk_free_rate, 'm');
  360. r_ms_10y = cal_ms_return(tb_ret_10y, risk_free_rate);
  361. }
  362. return r_incep, r_ytd, r_6m, r_1y, r_2y, r_3y, r_4y, r_5y, r_10y, r_ms_3y, r_ms_5y, r_ms_10y;
  363. }
  364. /*
  365. * Calculate fund indicators for one date
  366. *
  367. * @param entity_type <STRING>: MF, HF
  368. * @param fund_ids <STRING>: 逗号和单引号分隔的fund_id
  369. * @param end_day <DATE>: 要计算的日期
  370. * @param isFromNav <BOOL>: 用净值实时计算还是从表中取月收益
  371. * @param isFromSQL <BOOL>: TODO: 从MySQL还是本地DolphinDB取净值/收益数据
  372. *
  373. * Example: cal_fund_indicators('FD', "'HF000004KN','HF000103EU','HF00018WXG'", 2024.06.28, true);
  374. *
  375. */
  376. def cal_fund_indicators(entity_type, fund_ids, end_day, isFromNav) {
  377. very_old_date = 1990.01.01;
  378. fund_info = get_fund_info(fund_ids);
  379. fund_info.rename!('fund_id', 'entity_id');
  380. if(isFromNav == true) {
  381. // 从净值开始计算收益
  382. tb_ret = SELECT * FROM cal_fund_monthly_returns(entity_type, fund_ids, true) WHERE price_date <= end_day;
  383. tb_ret.rename!(['fund_id', 'cumulative_nav'], ['entity_id', 'nav']);
  384. } else {
  385. // 从fund_performance表里读月收益
  386. tb_ret = get_monthly_ret('FD', fund_ids, very_old_date, end_day, true);
  387. tb_ret.rename!(['fund_id'], ['entity_id']);
  388. }
  389. bmk_ret = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('IX', "'IN00000008'", very_old_date, end_day, true);
  390. 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);
  391. return cal_all_trailing_indicators(fund_info, tb_ret, end_day, bmk_ret, risk_free_rate, 'm');
  392. }
  393. /*
  394. * Calculate portfolio indicators for one date
  395. *
  396. * @param portfolio_ids <STRING>: comma-delimited portfolio ids
  397. * @param end_day <DATE>: the date
  398. * @param cal_method <INT>: calculate based on cumulative nav (1) or nav (2)
  399. * @param isFromNav <BOOL>: calculate returns from NAV on-the-fly (true) or get from monthly return table (false)
  400. *
  401. * Example: cal_portfolio_indicators('166002,166114', 2024.08.31, 1, true);
  402. *
  403. */
  404. def cal_portfolio_indicators(portfolio_ids, end_day, cal_method, isFromNav) {
  405. very_old_date = 1990.01.01;
  406. portfolio_info = get_portfolio_info(portfolio_ids);
  407. portfolio_info.rename!('portfolio_id', 'entity_id');
  408. if(isFromNav == true) {
  409. // 从净值开始计算收益
  410. tb_raw_ret = SELECT * FROM cal_portfolio_nav(portfolio_ids, very_old_date, cal_method) WHERE price_date <= end_day;
  411. // funky thing is you can't use "AS" for the grouping columns?
  412. tb_ret = SELECT portfolio_id, price_date.month(), price_date.last() AS price_date, (1+ret).prod()-1 AS ret, nav.last() AS nav
  413. FROM tb_raw_ret
  414. WHERE price_date <= end_day
  415. GROUP BY portfolio_id, price_date.month();
  416. tb_ret.rename!(['portfolio_id', 'month_price_date'], ['entity_id', 'end_date']);
  417. } else {
  418. // 从pf_portfolio_performance表里读月收益
  419. tb_ret = get_monthly_ret('PF', portfolio_ids, very_old_date, end_day, true);
  420. tb_ret.rename!(['portfolio_id'], ['entity_id']);
  421. }
  422. // 沪深300做基准,同SQL保持一致
  423. bmk_ret = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_monthly_ret('IX', "'IN00000008'", very_old_date, end_day, true);
  424. 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);
  425. return cal_all_trailing_indicators(portfolio_info, tb_ret, end_day, bmk_ret, risk_free_rate, 'm');
  426. }