navCalculator.dos 7.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
  1. module fundit::navCalculator
  2. use fundit::dataPuller
  3. /*
  4. * 转交易表为交易日的持仓截面表
  5. * NOTE: 假定所有基金证券都是T+1买入,也就是第一天没有收益
  6. *
  7. *
  8. */
  9. def convert_transaction_to_snapshot(portfolio_ids, end_date) {
  10. // 取数据库中的持仓交易表
  11. tb_transaction = get_portfolio_holding_history(portfolio_ids);
  12. // 所有交易日期
  13. tb_date = SELECT DISTINCT portfolio_id, holding_date FROM tb_transaction;
  14. // 所有基金证券id
  15. tb_id = SELECT DISTINCT portfolio_id, fund_id FROM tb_transaction;
  16. tmp = SELECT tb_date.portfolio_id, tb_date.holding_date, tb_id.fund_id FROM ej(tb_date, tb_id, 'portfolio_id');
  17. // 取各交易日期时的持仓截面, Window Join 的上限设成-1d 是因为买入基金当日无收益,所以计算份额时要排除掉
  18. tb = wj(tmp, tb_transaction.sortBy!('holding_date'), duration('-50y'):duration('-1d'), <[t.fund_share.sum() AS shares]>, ['portfolio_id', 'fund_id', 'holding_date']);
  19. tb.addColumn('nav', DOUBLE);
  20. // 买入的基金份额记为0, 保留原始买入净值
  21. UPDATE tb
  22. SET shares = 0, nav = tb_transaction.nav
  23. FROM ej(tb, tb_transaction, ['portfolio_id', 'holding_date', 'fund_id'],, isNull(tb.shares));
  24. // 删除没用的数据;防一手脏数据
  25. DELETE FROM tb WHERE shares IS NULL OR shares.round(0) < 0;
  26. // 补上个虚拟的未来截面,以免buy-n-hold的证券信息损失;用0当NAV也是没办法,DolphinDB不能SELECT出个全NULL的列
  27. INSERT INTO tb
  28. SELECT portfolio_id, end_date, fund_id, fund_share.sum(), 0
  29. FROM tb_transaction
  30. GROUP BY portfolio_id, fund_id
  31. HAVING fund_share.sum().round(0) > 0;
  32. return tb;
  33. }
  34. /*
  35. * 计算FOF类组合收益
  36. * NOTE: 与MySQL逻辑一致,用户界面输入的交易净值会被暂时忽略,因为我们无法确保同一基金同一时间被输入的净值是相同的;
  37. * 忽略手工净值会导致收益不精确或无法计算的问题,但可能错误的净值将导致错误的结果,两害取其轻。
  38. *
  39. *
  40. * Create: 20240908 用于代替 sp_cal_portfolio_nav Joey
  41. *
  42. * @param portfolio_ids <STRING>: 逗号分隔的组合ID
  43. * @param start_date <DATE>: 持仓证券净值更新的起始日期
  44. * @param cal_method <INT>: 净值使用方法:1-依赖累计净值,但份额数不是真实的、2:依赖单位净值,份额数是真实的
  45. *
  46. *
  47. */
  48. def cal_portfolio_return(portfolio_ids, start_date, cal_method) {
  49. // 取持仓截面
  50. tb_snapshot = convert_transaction_to_snapshot(portfolio_ids, today()).rename!('fund_id', 'sec_id');
  51. // 取涉及到的所有基金证券最早持仓日期
  52. s_json = (SELECT sec_id, holding_date.min() AS price_date FROM tb_snapshot GROUP BY sec_id).toStdJson();
  53. // 取涉及到的所有基金证券有用净值
  54. // TODO: need consider inception date nav
  55. tb_nav = get_holding_nav(s_json);
  56. // 补一下最新界面
  57. tb_latest_snapshot = SELECT sec_id, holding_date, nav.mean().round(6) AS nav
  58. FROM tb_snapshot
  59. WHERE NOT EXISTS ( SELECT 1 FROM tb_nav WHERE sec_id = tb_snapshot.sec_id AND price_date = tb_snapshot.holding_date )
  60. GROUP BY sec_id, holding_date;
  61. // Buggy DolphinDB, INSERT INTO Table1 (Columns) SELECT Columns FROM Table2 会报列数不匹配的奇葩错误
  62. // this is the way to get around it
  63. INSERT INTO tb_nav (sec_id, price_date, cumulative_nav) VALUES (tb_latest_snapshot.sec_id, tb_latest_snapshot.holding_date, tb_latest_snapshot.nav);
  64. // 在各证券持仓时段中,填充所有无净值的但其它证券有净值的合理日期
  65. // 比如 2024-01-10 ~ 2024-01-20区间,组合持有基金A和基金B,基金A有每日净值
  66. // 而基金B只有01-12和01-19两期周五净值,那么基金B需要填充除这两天以外的所有日期
  67. tb_holding_date_range = SELECT portfolio_id, sec_id, holding_date.min() AS oldest_date, holding_date.max() AS latest_date
  68. FROM tb_snapshot GROUP BY portfolio_id, sec_id;
  69. // 所有净值日期
  70. tb_date = SELECT DISTINCT dr.portfolio_id, n.price_date
  71. FROM tb_holding_date_range dr
  72. INNER JOIN tb_nav n ON dr.sec_id = n.sec_id
  73. WHERE n.price_date >= dr.oldest_date
  74. AND n.price_date <= dr.latest_date;
  75. // 所有基金证券id
  76. tb_id = SELECT DISTINCT portfolio_id, sec_id FROM tb_snapshot;
  77. tb_holdings = SELECT id.portfolio_id, dt.price_date, id.sec_id, n.cumulative_nav, n.nav
  78. FROM tb_id id
  79. INNER JOIN tb_date dt ON id.portfolio_id = dt.portfolio_id
  80. INNER JOIN tb_holding_date_range dr ON dr.portfolio_id = id.portfolio_id AND dr.sec_id = id.sec_id
  81. LEFT JOIN tb_nav n ON n.sec_id = id.sec_id AND n.price_date = dt.price_date
  82. WHERE dt.price_date >= dr.oldest_date AND dt.price_date <= dr.latest_date
  83. ORDER BY id.portfolio_id, dt.price_date, id.sec_id;
  84. // 清一下内存
  85. tb_nav = null;
  86. // 为收益计算填充净值
  87. UPDATE tb_holdings SET cumulative_nav = cumulative_nav.ffill(), nav = nav.ffill()
  88. CONTEXT BY portfolio_id, sec_id;
  89. tb_holdings.addColumn(['ret', 'shares', 'market_value', 'total_mkt_value', 'weight'], [DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);
  90. // 计算各持仓证券收益
  91. UPDATE tb_holdings SET ret = (cumulative_nav.ratios()-1).round(6)
  92. CONTEXT BY portfolio_id, sec_id;
  93. // 把交易日截面的份额数用于组合收益表
  94. UPDATE tb_holdings
  95. SET shares = ss.shares
  96. FROM ej(tb_holdings AS pr, tb_snapshot AS ss, ['portfolio_id', 'price_date', 'sec_id'], ['portfolio_id', 'holding_date', 'sec_id']);
  97. // 填充份额数为空的无交易日期,这段时间所有证券基金处于 buy-n-hold
  98. UPDATE tb_holdings
  99. SET shares = shares.bfill()
  100. CONTEXT BY portfolio_id, sec_id;
  101. // 计算各日期的持仓资产及总资产
  102. if(cal_method == 1) {
  103. UPDATE tb_holdings SET market_value = (cumulative_nav * shares).round(6);
  104. } else {
  105. UPDATE tb_holdings SET market_value = (nav * shares).round(6);
  106. }
  107. UPDATE tb_holdings
  108. SET total_mkt_value = market_value.sum()
  109. CONTEXT BY portfolio_id, price_date;
  110. // 计算各持仓的权重
  111. UPDATE tb_holdings
  112. SET weight = (market_value \ total_mkt_value).round(6)
  113. WHERE total_mkt_value <> 0;
  114. // 组合收益计算: RET = ∑( weight_i * ret_i )
  115. tb_portfolio_ret = SELECT portfolio_id, price_date, (weight * ret).sum().round(6) AS ret
  116. FROM tb_holdings
  117. GROUP BY portfolio_id, price_date;
  118. // 初始化净值
  119. tb_portfolio_ret.addColumn('nav', DOUBLE);
  120. UPDATE tb_portfolio_ret SET nav = 1 WHERE ret IS NULL;
  121. // 通过收益反算净值: nav_i = nav_0 * ∏(1 + ret_i)
  122. UPDATE tb_portfolio_ret SET nav = (1+ret).cumprod() WHERE nav IS NULL CONTEXT BY portfolio_id;
  123. // 删掉没有用的数据
  124. DELETE FROM tb_portfolio_ret WHERE price_date >= today();
  125. return tb_portfolio_ret;
  126. }