DolphinDB/modules/returnCalculator.dos

module fundit::returnCalculator

use fundit::fundCalculator
use fundit::dataPuller

/*
 *  通用月收益计算
 * 
 *  @param entity_type <STRING>: MF, HF, EQ, CF, MI, TI, CI, FA, PF
 *  @param entity_info <TABLE>: COLUMN NEED entity_id, price_date, inception_date, ini_value
 * 
 */
def cal_entity_monthly_returns(entity_type, entity_info) {

    tb_rets = null;

    // 取涉及到的所有基金证券最早持仓日期
    s_json = (SELECT entity_id AS sec_id, price_date.min() AS price_date FROM entity_info GROUP BY entity_id).toStdJson();

	// 取涉及到的所有基金证券有用净值(前值+某日期后所有净值)
    // TODO: need consider inception date nav
    tb_nav = get_nav_for_return_calculation(entity_type, 'm', s_json);


    // 填充好各基金有效期内所有月份的最后一天
    tb_monthly_nav = SELECT sec_id, price_date.month().last() AS end_date, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                     FROM tb_nav.sortBy!(['sec_id', 'price_date'], [1, 1])
                     GROUP BY sec_id, price_date.month();

    // 删掉成立日之前的净值
    tb_monthly_nav = SELECT sec_id AS entity_id, end_date, price_date, cumulative_nav 
                     FROM tb_monthly_nav n // ej(tb_monthly_nav, entity_info, 'sec_id', 'entity_id')
                     INNER JOIN entity_info ei ON n.sec_id = ei.entity_id
                     WHERE n.price_date >= ei.inception_date
                     ORDER BY n.sec_id, n.end_date, n.price_date;

    if(tb_monthly_nav.isVoid() || tb_monthly_nav.size() == 0) { return tb_rets; }

    // 计算月收益
    tb_rets = SELECT entity_id, end_date, price_date, cumulative_nav, cumulative_nav.ratios() - 1 AS ret
              FROM tb_monthly_nav
              CONTEXT BY entity_id;


    // the records without return calculated but do have nav are still useful for some calculations (e.g. max drawdown)
    return ( SELECT * FROM tb_rets WHERE cumulative_nav > 0 );
}

/*
 *  根据基金净值序列计算月收益序列（适合提供给指标运算）
 * 
 *  Create:  20240907                                                  Joey
 *                    TODO: missing pulling data from local
 *                    TODO: ONLY support month return now
 *                    
 *  @param entity_type <STRING>: NAV universe, 'HF','MF','PF','EQ'... defined in get_nav_table_description()
 *  @param fund_ids <STRING VECTOR>: 基金ID
 *  @param isFromMySQL <BOOL>: 净值来源 1 - 远程MySQL、 0 - 本地 DolphinDB
 *  
 *  Example: cal_fund_monthly_returns('HF', "'HF000004KN','HF000103EU','HF00018WXG'", true);
 *  
 */
def cal_fund_monthly_returns(entity_type, fund_ids, isFromMySQL){

    tb_rets = null;

    // 暂时只支持公募和私募基金
    if(entity_type != 'HF' && entity_type != 'MF' ) return tb_rets;
    
    // 用于保证老基金也能取到所有历史净值
    very_old_price_date = 1990.01.01;

    // 基金基本信息，包括初始净值
    tb_fund_info = get_fund_info(fund_ids);

    // 基金净值
    tb_nav = SELECT * FROM get_nav_by_price_date(entity_type, fund_ids, very_old_price_date, isFromMySQL);

    tb_month_end = table(100:0, ['fund_id', 'price_date'], [STRING, DATE]);
    // 填充好各基金有效期内所有月份的最后一天
    for( f in tb_fund_info )
    {
        INSERT INTO tb_month_end SELECT fund_id, price_date FROM table(f.fund_id.take(1) AS fund_id).cj(table(temporalSeq(f.inception_date, today(), 'M') AS price_date)) ;
    }

    UPDATE tb_month_end SET end_date = price_date.month();
    
    tb_monthly_nav = SELECT fund_id, monthEnd(price_date).month().last() AS end_date, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                     FROM tb_nav
                     GROUP BY fund_id, monthEnd(price_date);

    // 完整月末日期的净值序列（包括缺失数据为NULL）
    tb_monthly_nav = SELECT me.fund_id, me.end_date, n.price_date, n.cumulative_nav
                     FROM tb_month_end me
                     LEFT JOIN tb_monthly_nav n ON me.fund_id = n.fund_id AND me.end_date = n.end_date
                     ORDER BY me.fund_id, me.end_date;

    // 补一下成立日的初始净值
    // NOTE: DolphinDB 遇见 EXISTS 语句时，似乎主表的 alias 失效，只好用全名
    INSERT INTO tb_monthly_nav 
        SELECT fund_id, inception_date.month(), inception_date, ifNull(ini_value, 1)
        FROM tb_fund_info fi
        WHERE NOT EXISTS ( SELECT * FROM tb_monthly_nav n WHERE fund_id = tb_fund_info.fund_id AND n.price_date = tb_fund_info.inception_date);

    if(tb_monthly_nav.isVoid() || tb_monthly_nav.size() == 0) { return tb_rets; }

    // 算 ratios 之前先把时间顺序排好
    tb_monthly_nav.sortBy!(['fund_id', 'end_date', 'price_date'], [1, 1, 1]);

    // 计算月收益
    tb_rets = SELECT fund_id, end_date, price_date, cumulative_nav, cumulative_nav.ratios() - 1 AS ret
              FROM tb_monthly_nav
              CONTEXT BY fund_id;


    // the records without return calculated but do have nav are still useful for some calculations
    return ( SELECT * FROM tb_rets WHERE cumulative_nav > 0 );
}

/*
 *  月末 fund_performance 表计算
 *  
 *  @param fund_ids <STRING>: 逗号分隔的ID
 *  @param end_date <STRING>: YYYY-MM 
 * 
 *  Example: cal_fund_performance("'HF000004KN','HF00018WXG','HF000103EU'", '2024-06', true);
 */
def cal_fund_performance(fund_ids, month_end) {

    // 获取必要的基金月度净值
    tb_nav = get_nav_for_hedge_fund_performance(fund_ids, month_end);

    tb_rets = SELECT fund_id, price_date.month() AS end_date, price_date, cumulative_nav,
                     cumulative_nav \ nav_1m - 1 AS ret_1m,
                     cumulative_nav \ nav_3m - 1 AS ret_3m,
                     cumulative_nav \ nav_6m - 1 AS ret_6m,
                     cumulative_nav \ nav_1y - 1 AS ret_1y,
                     cumulative_nav \ nav_2y - 1 AS ret_2y,
                     cumulative_nav \ nav_3y - 1 AS ret_3y,
                     cumulative_nav \ nav_4y - 1 AS ret_4y,
                     cumulative_nav \ nav_5y - 1 AS ret_5y,
                     cumulative_nav \ nav_10y - 1 AS ret_10y,
                     cumulative_nav \ nav_ytd - 1 AS ret_ytd,
                     cumulative_nav \ nav_incep - 1 AS ret_incep, inception_date
              FROM tb_nav;

    // NOTE: this is to keep consistance with MySQL, even it is NOT complied with GIPS standard
    UPDATE tb_rets SET ret_1m_a = (1 + ret_1m).pow(12\1) - 1, ret_3m_a = (1 + ret_3m).pow(12\3) - 1, ret_6m_a = (1 + ret_6m).pow(12\6) - 1,
                       ret_1y_a= ret_1y, ret_2y_a = (1 + ret_2y).pow(12\24) - 1, ret_3y_a = (1 + ret_3y).pow(12\36) - 1,
                       ret_4y_a = (1 + ret_4y).pow(12\48) - 1, ret_5y_a = (1 + ret_5y).pow(12\60) - 1, ret_10y_a = (1 + ret_10y).pow(12\120) - 1,
                       ret_ytd_a = (1 + ret_ytd).pow(12\int(temporalFormat(end_date, 'MM')))-1,
                       ret_incep_a = (1 + ret_incep).pow(12\(end_date - inception_date.month())) - 1,
                       ret_incep_a_all = (1 + ret_incep).pow(12\(end_date - inception_date.month()))- 1,
                       ret_incep_a_gips = iif(end_date - inception_date.month() < 12, ret_incep,
                                             (1 + ret_incep).pow(12\(end_date - inception_date.month()))- 1);

    return tb_rets;
}



/*
 *  批量计算公募历史基金月度收益(fund_performance)
 *  NOTE: 任何数据频率快于或等于月度的净值数据都可以用此函数一次性计算完整历史记录。双月频、季频甚至更低频率的基金只能按月计算
 *
 *  cal_mutual_fund_performance("'HF000004KN','HF00018WXG','HF000103EU'", true)
 *
 */
def cal_mutual_fund_performance(fund_ids, isFromMySQL) {

    // 计算月收益
    tb_tmp = cal_fund_monthly_returns('MF', fund_ids, isFromMySQL);

    tb_rets = SELECT fund_id, end_date, ret_1m,
                     (1 + ret_1m).mprod(3) - 1 AS ret_3m, (1 + ret_1m).mprod(6) - 1 AS ret_6m, (1 + ret_1m).mprod(12) - 1 AS ret_1y,
                     (1 + ret_1m).mprod(24) - 1 AS ret_2y, (1 + ret_1m).mprod(36) - 1 AS ret_3y, (1 + ret_1m).mprod(48) - 1 AS ret_4y,
                     (1 + ret_1m).mprod(60) - 1 AS ret_5y, (1 + ret_1m).mprod(120) - 1 AS ret_10y
              FROM tb_tmp
              CONTEXT BY fund_id;

    // NOTE: this is to keep consistance with MySQL, even it is NOT complied with GIPS standard
    UPDATE tb_rets SET ret_1m_a = (1 + ret_1m).pow(12) - 1, ret_3m_a = (1 + ret_3m).pow(4) - 1, ret_6m_a = (1 + ret_6m).pow(2) - 1, ret_1y_a= ret_1y,
                       ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1, ret_4y_a = (1 + ret_4y).pow(1\4) - 1, 
                       ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1;
    
    // ytd 不会用上面的CONTEXT BY语句实现
    tb_ret_ytd = SELECT a.fund_id, a.end_date, a.price_date, a.cumulative_nav, -1 + a.cumulative_nav \ b.cumulative_nav AS ret_ytd,
                       (a.cumulative_nav \ b.cumulative_nav).pow(12\(a.end_date - b.end_date)) - 1 AS ret_ytd_a
                 FROM tb_rets a
                 INNER JOIN tb_rets b ON a.fund_Id = b.fund_id 
                   AND b.end_date = a.price_date.yearEnd().datetimeAdd(-1y).month()
    
    // since inception 不会用上面的CONTEXT BY语句实现
    tb_ret_incep = SELECT a.fund_id, a.end_date, a.price_date,  cumulative_nav, -1 + cumulative_nav \ ini_value AS ret_incep
                   FROM tb_rets a
                   INNER JOIN tb_fund_info fi ON a.fund_id = fi.fund_id

    
    UPDATE tb_ret_incep SET ret_incep_a = (1 + ret_incep).pow(12\(end_date - end_date.first())) - 1 CONTEXT BY fund_Id
    UPDATE tb_ret_incep SET ret_incep_a_gips = iif( end_date - end_date.first() < 12, ret_incep, ret_incep_a ), ret_incep_a_all = ret_incep_a CONTEXT BY fund_id
    
    // 只选需要更新的记录
    tb_fund_performance = SELECT a.fund_id, a.end_date.datetimeFormat("yyyy-MM") AS end_date, c.price_date, c.cumulative_nav,
                                 a.ret_1m, a.ret_1m_a, a.ret_3m, a.ret_3m_a, a.ret_6m, a.ret_6m_a,
                                 a.ret_1y, a.ret_1y_a, a.ret_2y, a.ret_2y_a, a.ret_3y, a.ret_3y_a,
                                 a.ret_4y, a.ret_4y_a, a.ret_5y, a.ret_5y_a, a.ret_10y, a.ret_10y_a,
                                 b.ret_ytd, b.ret_ytd_a, c.ret_incep, c.ret_incep_a, c.ret_incep_a_all, c.ret_incep_a_gips
                                 // , 123 AS creatorid, now() AS createtime, 123 AS updaterid, now() AS updatetime, 1 AS isvalid
                          FROM tb_rets a 
                          LEFT JOIN tb_ret_ytd b ON a.fund_id = b.fund_id AND a.end_date = b.end_date
                          LEFT JOIN tb_ret_incep c ON a.fund_id = c.fund_id AND a.end_date = c.end_date
                          WHERE c.price_date IS NOT NULL
                          ORDER BY a.fund_id, c.price_date

/*    
    // 把这些数据写回mySQL数据表
    save_table(tb_fund_performance, "mfdb.fund_performance", true)

    // 把这些数据写到本地数据表
    save_table(tb_fund_performance, "mfdb.fund_performance", false)
*/

    return tb_fund_performance
}


/*
 *  批量计算私募基金周收益

 *  TODO: 需要用每周最后一个交易日？
 *
 *  cal_hedge_fund_weekly_returns("'HF000004KN','HF00018WXG','HF000103EU'", true)
 *
 */
def cal_hedge_fund_weekly_returns(fund_ids, isFromMySQL) {

    // 用于保证老基金也能取到所有历史净值
    very_old_price_date = 1990.01.01

    tb_nav = get_nav_by_price_date('HF', fund_ids, very_old_price_date, isFromMySQL)

    UPDATE tb_nav SET year_week = price_date.year()$STRING + (price_date.weekOfYear()$STRING).lpad(2, "0")
    
    tb_weekly_nav = SELECT fund_id, year_week, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                    FROM tb_nav
                    GROUP BY fund_id, year_week
                    ORDER BY fund_id, year_week
    
    // 这里选最简单的计算方式：不补任何净值空洞，净值前值日期不做任何限制
    // TODO: 可以考虑将月收益也改为这种方式
    tb_rets_1w = SELECT fund_id, year_week, price_date, cumulative_nav, cumulative_nav.ratios()-1 AS ret_1w 
                 FROM tb_weekly_nav
                 ORDER BY fund_id, year_week
    
    return tb_rets_1w

}

/*
 *  批量计算私募基金区间收益
 *  TODO: mySQL version 向前取4天，向后不做限制。这里的逻辑是向前取4个交易日
 *
 *  get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
 *
 */
def get_trailing_return(table_last_nav, table_nav, duration, return_column_name) {

    tb =  SELECT a.fund_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret
          FROM table_last_nav a
          INNER JOIN table_nav b ON a.fund_id = b.fund_id 
          WHERE b.price_date <= a.price_date.datetimeAdd(duration)
            AND b.price_date >= a.price_date.datetimeAdd(duration).datetimeAdd(-4d).businessDay()
          GROUP by a.fund_id
          ORDER BY fund_id
    tb.rename!("ret", return_column_name)

    return tb    
}

/*
 *  批量计算私募基金最新收益
 *
 *
 *  cal_hedge_fund_weekly_returns("'HF000004KN','HF00018WXG','HF000103EU'", true)
 *
 */
def cal_hedge_fund_latest_returns(fund_ids, isFromMySQL) {

    // 用于保证老基金也能取到所有历史净值
    very_old_price_date = 1990.01.01

    tb_nav = get_nav_by_price_date('HF', fund_ids, very_old_price_date, isFromMySQL)
    
    tb_last_nav = SELECT fund_id, price_date.last() AS price_date, cumulative_nav.last() AS cumulative_nav
                  FROM tb_nav
                  GROUP BY fund_id
                  ORDER BY fund_id
    
    // 近1期收益，对应mySQL fund_latest_nav_performance 中的 net_value_change
    // 因为是倒序，所以算出来的 ratios() = n0 / n1, 要把它改换成 n1 / n0 - 1 的收益
    tb_last_return = SELECT TOP 2 fund_id, price_date.first() AS price_date, price_date AS pre_preice_date,
                                  cumulative_nav.first() AS cumulative_nav, 1\cumulative_nav.ratios() - 1 AS net_value_change
                     FROM ( SELECT * FROM tb_nav ORDER BY price_date DESC )
                     CONTEXT BY fund_id
                     ORDER BY fund_id
    tb_last_return = SELECT * FROM tb_last_return WHERE net_value_change IS NOT NULL


    // 近1交易日收益
    tb_1d = SELECT a.fund_id, a.price_date, a.cumulative_nav \ b.cumulative_nav - 1 AS ret_1d
            FROM tb_last_nav a
            INNER JOIN tb_nav b ON a.fund_id = b.fund_id AND b.price_date = a.price_date.datetimeAdd(-1d).businessDay()
            ORDER BY fund_id

    // 近1周、1/3/6月、1/2/3/4/5/10年收益
    tb_1w = get_trailing_return(tb_last_nav, tb_nav, -7d, "ret_1w")
    tb_1m = get_trailing_return(tb_last_nav, tb_nav, -1M, "ret_1m")
    tb_3m = get_trailing_return(tb_last_nav, tb_nav, -3M, "ret_3m")
    tb_6m = get_trailing_return(tb_last_nav, tb_nav, -6M, "ret_6m")
    tb_1y = get_trailing_return(tb_last_nav, tb_nav, -1y, "ret_1y")
    tb_2y = get_trailing_return(tb_last_nav, tb_nav, -2y, "ret_2y")
    tb_3y = get_trailing_return(tb_last_nav, tb_nav, -3y, "ret_3y")
    tb_4y = get_trailing_return(tb_last_nav, tb_nav, -4y, "ret_4y")
    tb_5y = get_trailing_return(tb_last_nav, tb_nav, -5y, "ret_5y")
    tb_10y = get_trailing_return(tb_last_nav, tb_nav, -10y, "ret_10y")

    // ytd return
    tb_ytd =  SELECT a.fund_id, a.price_date.last() AS price_date, a.cumulative_nav.last() \ b.cumulative_nav.last() - 1 AS ret_ytd
              FROM tb_last_nav a
              INNER JOIN tb_nav b ON a.fund_id = b.fund_id 
              WHERE b.price_date < a.price_date.yearBegin()
                AND b.price_date >= a.price_date.yearBegin().datetimeAdd(-4d)
              GROUP by a.fund_id

    // since inception return
    tb_fund_info = get_fund_info(fund_ids)
    tb_incep = SELECT a.fund_id, a.price_date, -1 + cumulative_nav \ ini_value AS ret_incep, fi.inception_date
               FROM tb_last_nav a
               INNER JOIN tb_fund_info fi ON a.fund_id = fi.fund_id
    
    // annulized since reception return
    UPDATE tb_incep SET ret_incep_a = (1 + ret_incep).pow(365.25\(price_date-inception_date)) - 1
    UPDATE tb_incep SET ret_incep_a_all = ret_incep_a,
                        ret_incep_a_gips = iif((price_date-inception_date)<365, ret_incep, ret_incep_a)

    // 最大回撤
    tb_drawdown_1m = SELECT a.fund_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_1m
                     FROM tb_last_return a
                     INNER JOIN tb_nav b ON a.fund_id = b.fund_id
                     WHERE b.price_date >= a.price_date.datetimeAdd(-1M)
                     GROUP BY a.fund_id

    tb_drawdown_3m = SELECT a.fund_id, max( 1 - b.cumulative_nav \ b.cumulative_nav.cummax() ) AS drawdown_3m
                     FROM tb_last_return a
                     INNER JOIN tb_nav b ON a.fund_id = b.fund_id
                     WHERE b.price_date >= a.price_date.datetimeAdd(-3M)
                     GROUP BY a.fund_id

    tb_drawdown_incep = SELECT fund_id, max( 1 - cumulative_nav \ cumulative_nav.cummax() ) AS drawdown_incep
                        FROM tb_nav GROUP BY fund_id

    tb_rets = SELECT a.fund_id, a.price_date.datetimeFormat("yyyy-MM") AS end_date, a.price_date, a.pre_preice_date, a.cumulative_nav,
                     a.net_value_change, d1.ret_1d, w1.ret_1w, m1.ret_1m, m3.ret_3m, m6.ret_6m,
                     y1.ret_1y, y2.ret_2y, y3.ret_3y, y4.ret_4y, y5.ret_5y, y10.ret_10y,
                     ytd.ret_ytd, incep.ret_incep, incep.ret_incep_a, incep.ret_incep_a_all, incep.ret_incep_a_gips,
                     dd_m1.drawdown_1m AS maxdrawdown_1m, dd_m3.drawdown_3m AS maxdrawdown_3m,
                     dd_incep.drawdown_incep AS maxdrawdown_incep,
                     iif(dd_incep.drawdown_incep == 0, null,incep.ret_incep_a \ dd_incep.drawdown_incep) AS calmarratio_incep
              FROM tb_last_return a
              LEFT JOIN tb_1d d1 ON a.fund_id = d1.fund_id
              LEFT JOIN tb_1w w1 ON a.fund_id = w1.fund_id
              LEFT JOIN tb_1m m1 ON a.fund_id = m1.fund_id
              LEFT JOIN tb_3m m3 ON a.fund_id = m3.fund_id
              LEFT JOIN tb_6m m6 ON a.fund_id = m6.fund_id
              LEFT JOIN tb_1y y1 ON a.fund_id = y1.fund_id
              LEFT JOIN tb_2y y2 ON a.fund_id = y2.fund_id
              LEFT JOIN tb_3y y3 ON a.fund_id = y3.fund_id
              LEFT JOIN tb_4y y4 ON a.fund_id = y4.fund_id
              LEFT JOIN tb_5y y5 ON a.fund_id = y5.fund_id
              LEFT JOIN tb_10y y10 ON a.fund_id = y10.fund_id
              LEFT JOIN tb_ytd ytd ON a.fund_id = ytd.fund_id
              LEFT JOIN tb_incep incep ON a.fund_id = incep.fund_id
              LEFT JOIN tb_drawdown_1m dd_m1 ON a.fund_id = dd_m1.fund_id
              LEFT JOIN tb_drawdown_3m dd_m3 ON a.fund_id = dd_m3.fund_id
              LEFT JOIN tb_drawdown_incep dd_incep ON a.fund_id = dd_incep.fund_id
              ORDER BY a.fund_id

    // 忽略掉非GIPS标准的所有年化收益字段（包括ytd_a）
    UPDATE tb_rets SET ret_1y_a = ret_1y, ret_2y_a = (1 + ret_2y).pow(1\2) - 1, ret_3y_a = (1 + ret_3y).pow(1\3) - 1,
                       ret_4y_a = (1 + ret_4y).pow(1\4) - 1, ret_5y_a = (1 + ret_5y).pow(1\5) - 1, ret_10y_a = (1 + ret_10y).pow(1\10) - 1


    return tb_rets

}