Barron_Excel_Lambdas

/* Reverses an n-dimensional array */
REVERSE_ARR =LAMBDA(my_array,
    LET(
        row_length, ROWS(my_array),
        col_length, COLUMNS(my_array),

        IF(col_length = 1,
        /* its a vertical list, reverse it*/
        MAKEARRAY(row_length, 1, LAMBDA(row, col, INDEX(my_array, row_length - row + 1))),
        /* horizontal array */
        MAKEARRAY(1, col_length, LAMBDA(row, col, INDEX(my_array, col_length - col + 1)))
        )
    )
);

COMMITMENT.FIELDVALUES = LAMBDA(fundCode, fieldName,

    LET(
        commitment_code, INDEX(CAISSA.COMMITMENT.ID(fundCode), 2, 3),
        commitment_profile, CAISSA.COMMITMENT.PROFILE(commitment_code),
        XLOOKUP(fieldName, INDEX(commitment_profile,,1), INDEX(commitment_profile,,2))
    )
);

/* 
    Returns vert array of end months between start and end (inclusive of both)
    ex: EOMONTH_SEQUENCE(12/31/2020, 12/31/2021) returns [12/31/2020, 1/31/2021, ..., 12/31/2021]
 */
EOMONTH_SEQUENCE = LAMBDA(eom_start_date, eom_end_date,
    MAKEARRAY(ROUNDUP(DAYS(eom_end_date, eom_start_date)/31, 0) + 1, 1,
        LAMBDA(row, col, EOMONTH(eom_start_date, row-1))
    )
);

EOMONTHS = LAMBDA(start_date, months, incl_start,

    IF(months > 0,
        IF(incl_start,
            MAKEARRAY(months+1, 1, LAMBDA(row, col, EOMONTH(start_date, row-1)))
            ,
            MAKEARRAY(months, 1, LAMBDA(row, col, EOMONTH(start_date, row)))
        )
    ,

        IF(incl_start,
            SORT(MAKEARRAY(ABS(months)+1, 1, LAMBDA(row, col, EOMONTH(start_date, -1*(row-1)))),,1)
            ,
            SORT(MAKEARRAY(ABS(months), 1, LAMBDA(row, col, EOMONTH(start_date, -1*(row)))),,1)
        )
    )
);


/* removes header from Caissa add-in output. if optional_col_index supplied, only return that column */
REMOVE_HEADERS = LAMBDA(caissa_data, optional_col_index,
    // cannot do IF(caissa_data = "#N/A",...) here in the case of valid table returned from Caissa
    // therefore must check for table size = 1x1
    if(AND(ROWS(caissa_data) = 1, COLUMNS(caissa_data) =1 ), MAKEARRAY(1, 1, LAMBDA(row, col, NA())), 
        MAKEARRAY(ROWS(caissa_data)-1, IF(optional_col_index=0, COLUMNS(caissa_data), 1), 
            LAMBDA(row, col, INDEX(caissa_data, row+1, if(optional_col_index = 0, col, optional_col_index)))
        )
    )
);

/*
    Computes Avg Beta exposure between 2 dates given inputs from P.A. Notional/Collateral Daily Values
    Filter to only non-weekend values between (start_date, end_date]
    Avg Beta = Avg(notional values) / Avg(collateral values)
*/
COMPUTE_PA_AVG_BETA = LAMBDA(start_date, end_date, date_list, notional_list, collateral_list,

    IFERROR(
    AVERAGE(FILTER(notional_list,(date_list>start_date)*(date_list<=end_date)* (WEEKDAY(date_list)<>7)  * (WEEKDAY(date_list)<>1)))
    /
    AVERAGE(FILTER(collateral_list,(date_list>start_date)*(date_list<=end_date)* (WEEKDAY(date_list)<>7)  * (WEEKDAY(date_list)<>1)))
    , "-")
);

/*
    Returns sequence of Avg Beta exposure for months between start, end
*/
PA_AVG_BETA_SEQUENCE = LAMBDA(start_date, end_date, date_list, notional_list, collateral_list,

    LET(month_list, EOMONTH_SEQUENCE(start_date, end_date),
        MAKEARRAY(ROWS(month_list) -1, 1, 
            LAMBDA(row, col, COMPUTE_PA_AVG_BETA(INDEX(month_list, row), INDEX(month_list, row+1), date_list, notional_list, collateral_list))
        )
    )
);
/*
    Computes beta adjusted return from start to end 
    Compute Avg beta for each month in the period
    grab return series for the period (monthly)
    multiply together to get beta adj return series
    compound to get beta adj return for the entire period
*/
BETA_ADJ_RETURN = LAMBDA(BM_Code, is_UDB, start_date, end_date, date_list, notional_list, collateral_list,
    IFERROR(
        LET(
            return_series, REMOVE_HEADERS(
                IF(is_UDB,
                    CAISSA.BENCHMARKUDB.RETURN.TS(BM_Code, start_date, end_date),
                    CAISSA.BENCHMARK.RETURN.TS(BM_Code, start_date, end_date)
                ),2
            ),
            beta_series, PA_AVG_BETA_SEQUENCE(start_date, end_date, date_list, notional_list, collateral_list),

            IF(ROWS(beta_series) = ROWS(return_series),
                PRODUCT(1+ (return_series * beta_series)) - 1,
                "Length mismatch return series, beta series"
            )
            // beta_adj_return_series, return_series * beta_series,
            // PRODUCT(1+beta_adj_return_series) - 1
        )
    , "-")
);