DolphinDB/modules/bfiMatcher.dos

module fundit::bfiMatcher

use fundit::sqlUtilities
use fundit::dataPuller


/*
 *  取BFI所需要的指数/因子ID
 * 
 *  NOTE: Java使用的逻辑如下（除了FA)，暂时没有差别
 *                    SELECT a.fund_Id FROM mfdb.fund_performance AS a
                      LEFT JOIN pfdb.cm_class_asset_index AS b ON a.fund_id = b.index_id 
                      WHERE a.end_date = 'YYYY-MM' AND a.fund_id LIKE 'IN%' AND b.isvalid = 1 ORDER BY a.fund_id ASC

 */
def get_bfi_index_list() {

    return ['FA00000VML','FA00000VMM','FA00000VMN','FA00000VMO','FA00000WKG','FA00000WKH','IN00000008','IN0000000D','IN0000000M','IN0000000S',
            'IN0000000T','IN0000000U','IN0000000V','IN0000000W','IN0000000X','IN0000000Y','IN0000000Z','IN00000010','IN00000011','IN00000012',
            'IN00000013','IN00000014','IN00000015','IN00000016','IN00000017','IN00000077','IN00000078','IN00000079','IN0000007A','IN0000007B',
            'IN0000007C','IN0000007D','IN0000007E','IN0000007F','IN0000007G','IN0000007M','IN0000007N','IN0000007O','IN00000080','IN00000088',
            'IN0000008O','IN0000009M','IN0000028E','IN000002CM'];
}

/*
 *   计算收益月度相关性
 * 
 *   @param ret1 <TABLE>: NEED COLUMN entity_id, price_date, ret
 *   @param ret2 <TABLE>: NEED COLUMN entity_id, price_date, ret
 *   @param win <DURATION>: 1y, 3y, 5y
 *   
 *   NOTE: price_date 应统一为各周中的同一天（比如周收益都用 price_date.weekEnd(4) 转化为周五， 月收益都用 price_date.monthEnd() 转为月末日
 */
def cal_monthly_correlation(ret1, ret2, win) {

    t =  SELECT r1.price_date, tmcorr(r1.price_date, r1.ret, r2.ret, win) AS corr
         FROM ret1 r1 
         LEFT JOIN ret2 r2 ON r1.price_date = r2.price_date
         ORDER BY r1.price_date;

    return SELECT price_date.month()[0] AS end_date, corr.last() AS corr FROM t  WHERE corr IS NOT NULL GROUP BY price_date.month();

}

/*
 *   计算目标和BFI所用指数因子的相关系数
 *   
 *   NOTE: 与Java把月末日期作为截止日期不同的是，这里用每月最后一个周五作为截止日，所以数值会与MySQL中存储的略为不同
 * 
 */
def cal_entity_index_coe(entity_type, entity_info) {


    if(entity_info.isVoid() || entity_info.size() == 0) return null;

    // 简单起见，取数据集中最新日期作为截止日期
    end_day = entity_info.price_date.max();

	ret_entity = get_entity_weekly_rets(entity_type, entity_info);

	if(ret_entity.isVoid() || ret_entity.size() == 0) return null;

    // 取BFI用得到的指数/因子列表
    v_indexes = get_bfi_index_list();

    // 手搓一个带日期的数据表
    index_info = table(v_indexes AS entity_id, take(end_day, v_indexes.size()) AS price_date);
    ret_index = get_entity_weekly_rets('MI', index_info);

	if(ret_index.isVoid() || ret_index.size() == 0) return null;

    // 两次循环遍历所有entity和指数
    entity_coe = table(1000:0, ['entity_id', 'index_id', 'end_date', 'coe_1y', 'coe_3y', 'coe_5y'],
                               [SYMBOL, SYMBOL, MONTH, DOUBLE, DOUBLE, DOUBLE]);

    for(entity in entity_info.entity_id) {

    	for(index in v_indexes) {

            ret1 = SELECT fund_id, price_date.weekEnd(4) AS price_date, ret_1w AS ret FROM ret_entity WHERE fund_id = entity AND price_date.weekEnd(4) <= end_day;
            ret2 = SELECT index_id, price_date.weekEnd(4) AS price_date, ret_1w AS ret FROM ret_index WHERE index_id = index AND price_date.weekEnd(4) <= end_day;

    		corr_1y = cal_monthly_correlation(ret1, ret2, 1y);

    		corr_3y = cal_monthly_correlation(ret1, ret2, 3y);

    		corr_5y = cal_monthly_correlation(ret1, ret2, 5y);

    		INSERT INTO entity_coe
        	    SELECT entity, index, corr_1y.end_date, corr_1y.corr AS coe_1y, corr_3y.corr AS coe_3y, corr_5y.corr AS coe_5y
                FROM corr_1y
                LEFT JOIN corr_3y ON corr_1y.end_date = corr_3y.end_date
                LEFT JOIN corr_5y ON corr_1y.end_date = corr_5y.end_date;
    	}
    }

	return entity_coe;
}