DolphinDB/modules/navCalculator.dos

module fundit::navCalculator

use fundit::sqlUtilities
use fundit::operationDataPuller
use fundit::performanceDataPuller
use fundit::portfolioDataPuller


/*
 *  转交易表为交易日的持仓截面表
 *  NOTE: 假定所有基金证券都是T+1买入，也就是第一天没有收益
 *        返回每个有交易的日期，以及当天会被纳入净值收益计算的各持仓份额（比如买入基金当天的份额数为0，卖出基金当天的份额是卖前份额）
 *
 *  Example: convert_transaction_to_snapshot("166002,166114", 2024.10.31);
 */
def convert_transaction_to_snapshot(portfolio_ids, end_day) {

    s_portfolio_ids = ids_to_string(portfolio_ids);

    // 取数据库中的持仓交易表
    tb_transaction = get_portfolio_holding_history(s_portfolio_ids);
    
    // 所有交易日期
    tb_date = SELECT DISTINCT portfolio_id, holding_date FROM tb_transaction;

    // 所有基金证券id
    tb_id = SELECT DISTINCT portfolio_id, fund_id FROM tb_transaction;

    tmp = SELECT tb_date.portfolio_id, tb_date.holding_date, tb_id.fund_id FROM ej(tb_date, tb_id, 'portfolio_id');

    // 取各交易日期时的持仓截面， Window Join 的上限设成-1d 是因为买入基金当日无收益，所以计算份额时要排除掉
    tb = wj(tmp, tb_transaction.sortBy!('holding_date'), duration('-50y'):duration('-1d'), <[t.fund_share.sum() AS shares]>, ['portfolio_id', 'fund_id', 'holding_date']);

    tb.addColumn('nav', DOUBLE);
    // 买入的基金份额记为0, 保留原始买入净值
    UPDATE tb
    SET shares = 0, nav = tb_transaction.nav
    FROM ej(tb, tb_transaction, ['portfolio_id', 'holding_date', 'fund_id'],, isNull(tb.shares));

    // 删除没用的数据；防一手脏数据
    DELETE FROM tb WHERE shares IS NULL OR shares.round(0) < 0;

    // 补上个虚拟的未来截面，以免buy-n-hold的证券信息损失；用0当NAV也是没办法，DolphinDB不能SELECT出个全NULL的列
    INSERT INTO tb
    SELECT portfolio_id, end_day, fund_id, fund_share.sum(), 0
    FROM tb_transaction
    GROUP BY portfolio_id, fund_id
    HAVING fund_share.sum().round(0) > 0;

    return tb.sortBy!(['portfolio_id', 'holding_date', 'fund_id'], [1, 1, 1]);

}

/*
 *   通用净值计算，由收益反推 
 *    
 *   @param entity_type <STRING>:
 *   @param entity_ret <TABLE>: [COLUMNS] entity_id, price_date, ret
 *   @param freq <STRING>: d, w, m
 * 
 *   NOTE: 如果没有成立日，则无法计算
 */
def cal_entity_nav_by_return(entity_type, entity_ret, freq) {

	t_nav = table(1000:0, ['entity_id', 'price_date', 'ret', 'nav' ], [iif(entity_type=='PF', INT, SYMBOL), DATE, DOUBLE, DOUBLE]);

	if(entity_ret.isVoid() || entity_ret.size() == 0) return t_nav;

	t_entity_info = get_entity_info(entity_type, entity_ret.entity_id.distinct());
	UPDATE t_entity_info SET inception_date = 1900.01.01 WHERE inception_date IS NULL;

	// 筛掉早于成立日的脏数据
	t_ret = SELECT * FROM ej(entity_ret, t_entity_info, 'entity_id') WHERE price_date >= inception_date;

	s_json = (SELECT entity_id.last() AS sec_id, price_date.min() AS price_date FROM t_ret GROUP BY entity_id).toStdJson();

	// 取净值前值
	t_pre_nav = get_nav_for_return_calculation(entity_type, freq, s_json, 1);
	
	INSERT INTO t_nav
		SELECT entity_id, price_date, ret, double(NULL) FROM t_ret;

	// 设置成立日当天的净值和收益
	UPDATE t_nav
		SET nav = ini_value, ret = NULL
	FROM ej(t_nav, t_entity_info, ['entity_id', 'price_date'], ['entity_id', 'inception_date']);

	// 没有前值时，做一个假记录，把成立日净值和日期填入
	INSERT INTO t_pre_nav
		SELECT entity_id AS sec_id, price_date, nav AS cumulative_nav, nav
		FROM t_nav
		WHERE nav > 0
		  AND NOT exists ( SELECT * FROM tb_pre_nav 
		                   WHERE t_nav.entity_id = tb_pre_nav.sec_id );

    // 通过收益反算净值: nav_i = nav_0 * ∏(1 + ret_i)
    UPDATE t_nav 
        SET nav = (t_pre_nav.cumulative_nav * (1+ret).cumprod()).round(6) 
    FROM ej(t_nav, t_pre_nav, 'entity_id', 'sec_id')
    CONTEXT BY entity_id;

    return t_nav;
}

/*
 *   根据持仓收益计算组合净值
 * 
 *   @param entity_cal_dates <TABLE>: 组合净值计算时间区间表，记录 [COLUMNS] entity_id, first_cal_date, latest_cal_date
 *   @parm holdings <TABLE>：带有各证券净值前值的截面持仓表 [COLUMNS] entity_id, price_date, sec_id, ret, weight
 *   
 *   @return <TABLE>: [COLUMNS] entity_id, price_date, ret
 */
def cal_nav_by_return(entity_type, entity_cal_dates, holdings) {
// entity_type = 'PF'
// entity_cal_dates=tb_port_first_cal_date
// holdings = tb_holdings

    // 组合收益计算: RET = ∑( weight_i * ret_i )
    tb_portfolio_ret = SELECT entity_id, price_date, (weight * ret).sum() AS ret
                       FROM holdings
                       GROUP BY entity_id, price_date;

    // 取组合净值前值
    s_json = (SELECT entity_id, price_date.max() AS price_date
              FROM ej(tb_portfolio_ret, entity_cal_dates, 'entity_id')
              WHERE tb_portfolio_ret.price_date < entity_cal_dates.first_cal_date
              GROUP BY entity_id).toStdJson();

    tb_pre_nav = get_entity_nav_by_date(entity_type, s_json, true);

    INSERT INTO tb_pre_nav
    	SELECT entity_id, first_cal_date, double(NULL) AS cumulative_nav, double(NULL) AS nav
    	FROM entity_cal_dates
    	WHERE NOT exists( SELECT * FROM tb_pre_nav WHERE tb_pre_nav.entity_id = entity_cal_dates.entity_id);
   
    tb_portfolio_ret.addColumn('nav', DOUBLE);

    // start_cal_date 是最早净值日期
    UPDATE tb_portfolio_ret
        SET nav = 1, ret = 0
    FROM ej(tb_portfolio_ret, ej(entity_cal_dates, tb_pre_nav, 'entity_id'), ['entity_id', 'price_date'], ['entity_id', 'first_cal_date'])
    WHERE tb_pre_nav.cumulative_nav IS NULL;

    // start_cal_date 是最早净值日期，用它作为初始净值日期
    UPDATE tb_pre_nav
        SET price_date = first_cal_date, cumulative_nav = 1
    FROM ej(tb_pre_nav, entity_cal_dates, 'entity_id')
    WHERE cumulative_nav IS NULL;

    tb_portfolio_ret.sortBy!(['entity_id', 'price_date'], [1, 1]);

    // 通过收益反算净值: nav_i = nav_0 * ∏(1 + ret_i)
    UPDATE tb_portfolio_ret 
        SET nav = (tb_pre_nav.cumulative_nav * (1+ret).cumprod()).round(6) 
    FROM ej(tb_portfolio_ret, tb_pre_nav, 'entity_id')
    CONTEXT BY entity_id;

    // 返回有用的数据 
    return (SELECT DISTINCT tb_portfolio_ret.* 
            FROM ej(tb_portfolio_ret, entity_cal_dates, 'entity_id')
            WHERE price_date >= first_cal_date AND price_date <= latest_cal_date
            ORDER BY entity_id, price_date);
}

/*
 *  计算FOF类组合净值
 *  NOTE: 与MySQL逻辑一致，用户界面输入的交易净值会被暂时忽略，因为我们无法确保同一基金同一时间被输入的净值是相同的；
 *        忽略手工净值会导致收益不精确或无法计算的问题，但可能错误的净值将导致错误的结果，两害取其轻。
 *  
 *  
 *  Create:  20241101 用于代替 sp_cal_portfolio_nav                                               Joey
 * 
 *  @param portfolio_info <TABLE>: NEED COLUMNS portfolio_id, sec_id, start_cal_date, end_cal_date, org_id
 *  
 *  Example：cal_portfolio_nav(get_portfolio_list_by_fund_nav_updatetime([166002,166114], 2024.10.28, true));
 *  
 */
 def cal_portfolio_nav(portfolio_info) {

    if(portfolio_info.isVoid() || portfolio_info.size() == 0) return NULL;

    // 取持仓截面get_nav_for_return_calculation
    tb_snapshot = convert_transaction_to_snapshot(portfolio_info.portfolio_id, today()).rename!('fund_id', 'sec_id');

    if(tb_snapshot.isVoid() || tb_snapshot.size() == 0) return NULL;

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子
    v_universe = ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA'];
    v_prefix = ['HF%', 'MF%', 'CF%', 'EQ%', 'IN%', 'IN%', 'CI%', 'FA%'];
    d_universe = dict(v_universe, v_prefix);
    
    tb_nav = table(100:0, ['sec_id', 'price_date', 'cumulative_nav', 'nav'], [SYMBOL, DATE, DOUBLE, DOUBLE]);
    // 取计算所需的所有持仓净值数据
    for(u in d_universe.keys()) {

        // 取涉及到的所有基金证券最早持仓日期
    	s_json = (SELECT sec_id, start_cal_date.min() AS price_date FROM portfolio_info WHERE sec_id LIKE d_universe[u] GROUP BY sec_id).toStdJson();

        // 取涉及到的所有基金证券有用净值
        // TODO: need consider inception date nav
        tmp_nav = get_nav_for_return_calculation(u, 'd', s_json);

        if(tmp_nav.isVoid() || tmp_nav.size() == 0) continue;
        
        INSERT INTO tb_nav SELECT * FROM tmp_nav;
    }


    // 补一下最新截面(虽然是个”假的”截面)
    tb_latest_snapshot = SELECT sec_id, holding_date, nav.mean().round(6) AS nav
                         FROM tb_snapshot
                         WHERE holding_date = today()
                           AND NOT EXISTS ( SELECT 1 FROM tb_nav WHERE sec_id = tb_snapshot.sec_id AND price_date = tb_snapshot.holding_date )
                         GROUP BY sec_id, holding_date;

    // Funky DolphinDB, INSERT INTO Table1 (Columns) SELECT Columns FROM Table2 会报列数不匹配的奇葩错误
    // this is the way to get around it
    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);

    // 在各证券持仓时段中，填充所有无净值的但其它证券有净值的合理日期
    // 比如 2024-01-10 ~ 2024-01-20区间，组合持有基金A和基金B，基金A有每日净值
    // 而基金B只有01-12和01-19两期周五净值，那么基金B需要填充除这两天以外的所有日期
    tb_holding_date_range = SELECT p.portfolio_id, p.sec_id, n.price_date.max() AS oldest_date, today() AS latest_date
                            FROM portfolio_info p 
                            INNER JOIN tb_nav n ON n.sec_id = p.sec_id
                            WHERE n.price_date < p.start_cal_date
                            GROUP BY p.portfolio_id, p.sec_id;
    // 所有净值日期+前值日期
    tb_date = SELECT DISTINCT dr.portfolio_id, n.price_date
              FROM tb_holding_date_range dr
              INNER JOIN tb_nav n ON dr.sec_id = n.sec_id
              WHERE n.price_date >= dr.oldest_date 
                AND n.price_date <= dr.latest_date;

    // 所有基金证券id
    tb_id = SELECT DISTINCT portfolio_id, sec_id FROM tb_snapshot;

    // NOTE: 因为同一个组合下的持仓私募基金的净值前值日期会不一样, 所以在 tb_date里会混入多余的脏数据，导致某些私募的净值前值及日期被赋予错误的数据
    // 好消息是最后返回的收益及净值会把这些错误的前值筛掉，但最好想个办法在这里清除掉
    tb_holdings = SELECT id.portfolio_id, dt.price_date, id.sec_id, n.cumulative_nav, n.nav
                  FROM tb_id id
                  INNER JOIN tb_date dt ON id.portfolio_id = dt.portfolio_id
                  INNER JOIN tb_holding_date_range dr ON dr.portfolio_id = id.portfolio_id AND dr.sec_id = id.sec_id 
                  LEFT JOIN tb_nav n ON n.sec_id = id.sec_id AND n.price_date = dt.price_date
                  WHERE dt.price_date >= dr.oldest_date AND dt.price_date <= dr.latest_date
                  ORDER BY id.portfolio_id, dt.price_date, id.sec_id;
    // 清一下内存
    tb_nav = null;

    // 为收益计算填充净值
    UPDATE tb_holdings SET cumulative_nav = cumulative_nav.ffill(), nav = nav.ffill()
    CONTEXT BY portfolio_id, sec_id;

    tb_holdings.addColumn(['ret', 'shares', 'market_value', 'total_mkt_value', 'weight'], [DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE]);

    // 计算各持仓证券收益
    UPDATE tb_holdings SET ret = cumulative_nav.ratios()-1
    CONTEXT BY portfolio_id, sec_id;

    // 把交易日截面的份额数用于组合收益表
    UPDATE tb_holdings
        SET shares = ss.shares
    FROM ej(tb_holdings AS pr, tb_snapshot AS ss, ['portfolio_id', 'price_date', 'sec_id'], ['portfolio_id', 'holding_date', 'sec_id']);

    // 填充份额数为空的无交易日期，这段时间所有证券基金处于 buy-n-hold
    UPDATE tb_holdings
        SET shares = shares.bfill()
    CONTEXT BY portfolio_id, sec_id;

    // 记录每个组合最早的净值计算日期
    tb_port_first_cal_date = SELECT portfolio_id, start_cal_date.min() AS first_cal_date, end_cal_date.max() AS latest_cal_date, org_id[0] AS org_id 
                             FROM portfolio_info GROUP BY portfolio_id;

    // 计算各日期的持仓资产及总资产
    UPDATE tb_holdings
        SET market_value = (cumulative_nav * shares).round(6)
    FROM ej(tb_holdings, tb_port_first_cal_date, 'portfolio_id')
    WHERE org_id = '1';

    UPDATE tb_holdings
        SET market_value = (nav * shares).round(6)
    FROM ej(tb_holdings, tb_port_first_cal_date, 'portfolio_id')
    WHERE org_id = '2';

    UPDATE tb_holdings
        SET total_mkt_value = market_value.sum()
    CONTEXT BY portfolio_id, price_date;

    // 计算各持仓的权重
    UPDATE tb_holdings
        SET weight = (market_value \ total_mkt_value).round(6)
    WHERE total_mkt_value <> 0;

	// 通过持仓收益反算组合收益，再计算组合净值
	tb_port_first_cal_date.rename!('portfolio_id', 'entity_id');
	tb_holdings.rename!('portfolio_id', 'entity_id');

	return cal_nav_by_return('PF', tb_port_first_cal_date, tb_holdings);

 }



/*
 *   通用净值计算，由收益反推 
 *    
 *   @param entity_type <STRING>: PL, CO
 *   @param entity_ret <TABLE>: [COLUMNS] entity_id, curve_type, strategy, effective_date <STRING>, ret
 *   
 *   NOTE: 1) 如果没有成立日，则无法计算
 *         2) monthly 时 effective_date 对应MySQL里的 end_date; weekly时对应 year_week
 */
def cal_mc_nav_by_return(entity_type, entity_ret, freq='m') {

	t_nav = table(1000:0, ['entity_id', 'curve_type', 'strategy', 'effective_date', 'ret', 'nav'], [SYMBOL, INT, INT, STRING, DOUBLE, DOUBLE]);

	if(!(entity_type IN ['PL', 'CO'])) return t_nav;
	if(entity_ret.isVoid() || entity_ret.size() == 0) return t_nav;

	s_json = (SELECT entity_id AS entity_id, curve_type, strategy, effective_date.min() AS effective_date 
	          FROM entity_ret
	          GROUP BY entity_id, curve_type, strategy).toStdJson();

	t_nav = entity_ret.join(take(double(NULL), entity_ret.size()) AS nav);

	// 取净值前值
	t_pre_nav = get_mc_nav_for_return_calculation(entity_type, s_json, 1, freq);

	if(t_pre_nav.size() == 0) {

		// 没有前值时候, 做一个假记录，把净值1和日期填入
		INSERT INTO t_pre_nav
			SELECT entity_id, curve_type, strategy, effective_date.min() AS effective_date, 1 AS cumulative_nav
	        FROM entity_ret
	        GROUP BY entity_id, curve_type, strategy;
		// 设置初始净值为1，收益为0
	    UPDATE t_nav n
	    	SET ret = 0, nav = 1
	    FROM ej(t_nav, t_pre_nav, ['entity_id', 'effective_date']);
	}
	
    // 通过收益反算净值: nav_i = nav_0 * ∏(1 + ret_i)
    UPDATE t_nav 
        SET nav = (t_pre_nav.cumulative_nav * (1+ret).cumprod()).round(6) 
    FROM ej(t_nav, t_pre_nav, 'entity_id')
    CONTEXT BY entity_id;

    return t_nav;
}