DolphinDB/codes/sample_cal_portfolio.dos

login(`admin, `123456)
loadPlugin("ODBC")

clearCachedModules()
use fundit::dataPuller
use fundit::returnCalculator
use fundit::indicatorCalculator
use fundit::navCalculator

very_old_date = 1990.01.01;

/* TEST CASE 1, calculation_method = 1 */
portfolio_ids = '166002,166114';
cal_method = 1;

/*  TEST CASE 2, calculation_method = 2

portfolio_ids = '364771';

portfolio_ids = '364771,364772,364773,364774,364775,364776,364777,365070,364778,364779,365179,365180,365181,365164,365189,365198,365199,365200,365204,365205,365165,365166,365186,365187,365197,365201,365206,365167,365168,365171,365182,365183,365169,365170,365172,365173,365174,365175,365178,365194,365176,365177,365184,365185,365188,365190,365191,365192,365193,365195,365196,365202,365203';

cal_method = 2;
*/

// calculate navs
tb_portfolio_ret = cal_portfolio_return(portfolio_ids, very_old_date, cal_method);
tb_portfolio_ret.sortBy!(['portfolio_id', 'price_date'], [1, 1]);

select portfolio_id, price_date.month(), price_date.last(), (1+ret).prod()-1, nav.last()
from tb_portfolio_ret 
where price_date <= end_day
group by portfolio_id, price_date.month()

// calculate indicators
end_day = 2024.08.31

bmk_ret = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_fund_monthly_ret("'IN00000008'", 1990.01.01, end_day, true); 
risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate("'IN0000000M'", 1990.01.01, end_day);

// calculate monthly return
// funky thing is you can't use "AS" for the grouping columns?
tb_ret = SELECT portfolio_id, price_date.month(), price_date.last() AS price_date, (1+ret).prod()-1 AS ret, nav.last() AS nav
         FROM tb_portfolio_ret
         WHERE price_date <= end_day
         GROUP BY portfolio_id, price_date.month();

tb_ret.rename!(['portfolio_id', 'month_price_date'], ['entity_id', 'end_date']);

tb_indicators = cal_all_trailing_indicators(tb_ret, end_day, bmk_ret, risk_free_rate, 'm');

select * from tb_indicators[8]