DolphinDB/modules/fundCalculator.dos

module fundit::fundCalculator
/*
 *   RBSA 计算
 *   @param: ret: historical return (double) vector which contains the same number of return as index
 *           index_ret: historical index returen matrix which each row is an index
 *           isLongShort: bool
 *   @return: table
 * 
 *   Create  20240703  模仿python代码在Dolphin中实现，具体计算逻辑完全不懂                          Joey
 *                     原代码见： http://gogs.fundit.cn/FundIt/FinanceCalcPython/src/dev36/pf_scical/v1/calc_rbsa_use_osqp.py
 *                     Python官方示例见：https://osqp.org/docs/examples/least-squares.html
 *                     Dolphin官方示例见：https://docs.dolphindb.cn/zh/funcs/o/osqp.html
 *                     取数据脚本：SELECT fund_id, GROUP_CONCAT(ret_1w SEPARATOR ' ')
 *                                 FROM mfdb.`fund_performance_weekly` 
 *                                 WHERE fund_id IN ( 'MF00003PW1', 'IN00000008', 'IN00000077', 'IN0000009M', 'IN0000007G')
 *                                 AND year_week BETWEEN 202411 AND 202422
 *                                 GROUP BY fund_id
 *
 */
def cal_rbsa(ret, index_ret, isLongShort) {

    // 窗口长度
    m = ret.size()
    // 指数个数
    n = index_ret.cols()
    
    P0 = matrix(float, n, m+n)
    P1 = concatMatrix([matrix(float, m, n), eye(m)])
    P = concatMatrix([P0, P1], false)
    q = array(float, m+n, (m+n)*10, 0)
    
    A0 = concatMatrix( [matrix(index_ret), -eye(m)])
    A1 = concatMatrix( [matrix(take(1, n)).transpose(), matrix(float, 1, m)])
    A2 = concatMatrix( [eye(n), matrix(float, n, m)])
    A = concatMatrix( [A0, A1, A2], false)
    
    // join 1 是为了限制所有权重加总为100%
    // 下限
    lb =(ret join 1) join array(float, n, n*10, iif(isLongShort == false, 0, -2))
    // 上限
    ub=(ret join 1) join array(float, n, n*10, iif(isLongShort == false, 1, 2))
    
    res = osqp( q, P, A, lb, ub)
    
    return res
 }

/*
 * 滚动 rbsa
 * @param: ret: return table, at least with "date" and "ret" as columns
 *         index_ret: index return pivot table, with "date" and all index ids as columns
 *         isLongShort: boolean. true means weightings could be negative values
 *         window: number of return in a window
 *         step: rolling step
 * @return: table, with "date", "status" and "weights" columns. "weights" contains space-delimited numbers
 */
def cal_rolling_rbsa(ret, index_ret, isLongShort, window, step) {


    t = SELECT *
        FROM ret INNER JOIN index_ret ON ret.date = index_ret.date
        ORDER BY ret.date
    t.nullFill!(0)

    // 指数个数
    n = index_ret.cols() - 1
        
    // 计算起始位置
    i = t.size() % step

    // 运行rbsa计算次数
    cnt = (t.size() - i) / step
    
    tb = table(max(cnt,1):0, ["date", "status", "weights"], [STRING, STRING, STRING])

    if(t.size() >= window && cnt > 0) {
        do {
    
            v_ret = t.ret[i:(i+window)]
            
            t_index_ret = t.slice( i:(i+window), ret.cols(): )
        
            // 传入window个收益
            res = cal_rbsa(v_ret, t_index_ret, isLongShort)
    
            // 取窗口内最后（新）的日期
            tb.tableInsert(t.date[i+window-1], res[0], res[1][0:n].round(6).concat(" ")$STRING)
    
            // 往前推进step个收益
            i = i + step
            
            cnt -= 1
    
        } while( cnt > 0)
        
    } else {
    
        tb.tableInsert(null, "error", "The number of joined returns must not be less than window size.")
    }

    return tb

}




/*
 * 年化收益率计算(只支持月收益)
 *
 * date_rets 是个日期排正序的收益率表
 */
def cal_ret_annualized(date_rets, isGIPS) {

    date_format = "y-M"
    
    t = SELECT fund_id, (1 + ret).prod() - 1 AS ret_a, end_date.max().datetimeParse(date_format) - end_date.min().datetimeParse(date_format) AS date_diff
        FROM date_rets
        GROUP BY fund_id
   
    // GIPS 规则是不够一年不年化
    if(isGIPS == true) {
    
        UPDATE t SET ret_a = (1 + ret_a).pow(12 \ date_diff) - 1
        WHERE date_diff > 12
    } else {
    
        UPDATE t SET ret_a = (1 + ret_a).pow(12 \ date_diff) - 1
    }

    return t
    
}