Return of the Corkscrew with Spilled Arrays

James_Buist 

I really don't understand your question, but I have some sense for the area.

I've made some other complicated posts that touch on this topic, so if you are up for some reading, you can take a look at my serialization to preserve state function which is aimed primarily at reducing the computational demands for independent formulas. If you are happy to have just a single dynamic formula that rolls forward in time, everything comes down to structure.

Basically, you are aggregating data which consists of a few parts - an opening balance, inflows, outflows, and closing balance. What gives you freedom to inject complicated logic for how to change the inflows and outflows is making sure that your data is segregated both from your presentation and your calculations. The primary way that I do that is by having an excel table (ie "list object") that has amounts kept more or less as absolute values in a single column with another column that indicates whether the value is an inflow or outflow for the current purpose. So something like ctrl, date, acct, amt, flow.

I left you with the ability to add an optional filter mask for the transactions so that you can adjust the function relatively easily.

This is the data in "tbl_trans":

This is the output:

getDateMask =
lambda(
    start_date,
    end_date,
    (tbl_trans[date] <= end_date)*(tbl_trans[date]>=start_date)
);

getAcctMask = 
lambda(
    acct_id,
    tbl_trans[acct] = acct_id
);

totalByflow = 
lambda(
    flow,
    filter_mask,
    [optional_filter_mask],
    round(
        sum(
            filter(
                tbl_trans[amt],
                (
                    (tbl_trans[flow]=flow)*
                    filter_mask*
                    if(isomitted(optional_filter_mask),TRUE,optional_filter_mask)
                )
            )
        ),
        2
    )
);


sumInflows = 
lambda(
    date_mask,
    acct_mask,
    [optional_filter_mask], 
    totalByFlow("I", date_mask*acct_mask,optional_filter_mask)
);

sumOutflows = 
lambda(
    date_mask,
    acct_mask,
    [optional_filter_mask], 
    totalByFlow("O", date_mask*acct_mask,optional_filter_mask)
);

getPriorBalance =
lambda(
    accumulator,
    take(accumulator,-1,-1)
);

getPriorDate =
lambda(
    accumulator,
    index(take(accumulator,-1),1,3)
);

calcPeriod =
lambda(
    first_date,
    edate(first_date-1,1)
);

calcClosingBalance = 
lambda(
    opening_balance,
    inflows,
    outflows,
    [optional_add_outflows_df_FALSE],
    if(
        if(
            isomitted(optional_add_outflows_df_FALSE),
            FALSE,
            optional_add_outflows_df_FALSE
        ),
        opening_balance+outflows-inflows,
        opening_balance+inflows-outflows
    )
);

calcCurrentPeriodData =
lambda(
    accumulator,
    acct,
    [optional_start_date],
    [optional_filter_mask],
    [optional_end_date],
    [optional_add_outflows_df_FALSE],
    let(
        opening_balance, getPriorBalance(accumulator),
        start_date, if(isomitted(optional_start_date),getPriorDate(accumulator)+1,optional_start_date),
        end_date, if(isomitted(optional_end_date),calcPeriod(start_date),optional_end_date),
        acct_mask, getAcctMask(acct),
        date_mask, getDateMask(start_date,end_date),
        inflows, sumInflows(date_mask, acct_mask, optional_filter_mask),
        outflows, sumOutflows(date_mask, acct_mask, optional_filter_mask),
        closing_balance, calcClosingBalance(opening_balance, inflows, outflows, optional_add_outflows_df_FALSE),
        result,
            hstack(acct,start_date, end_date, opening_balance, inflows, outflows, closing_balance
        ),
        result
    )
);

running_balance_heading =
    hstack("account","start_date", "end_date", "opening_balance", "inflows","outflows","ending_balance");

makeAcctActivityReport =
lambda(
    start_date,
    initial_balance,
    acct,
    [optional_total_periods_df_3],
    let(
        accumulator, hstack(expand(hstack("","",start_date-1),1,columns(running_balance_heading)-1,""),initial_balance),
        periods, if(isomitted(optional_total_periods_df_3),3,optional_total_periods_df_3),
        result,
            reduce(
                accumulator,
                sequence(periods),
                lambda(
                    acc,
                    period,
                    vstack(acc, calcCurrentPeriodData(acc,acct))
                )
        ),
        vstack(running_balance_heading,drop(result,1))
    )
);

Then you make a few helper formulas depending on your needs.

James_Buist 

The basic strategy is to start of by calculating closing balances using SCAN.  The catch here is that you are accumulating arrays rather than scalars, so SCAN needs to be fooled by passing it a thunk vector.  The problem of evaluating the resulting thunk array is not trivial.  Opening balances follow by offsetting the closing balances by one period.  Once you have the opening balances, the rest of the model follows as one simple array calculation.

In some situations, like a revolver, you may know how that you are only accumulating two interacting rows of data and it may be better to use two passes of MAP to evaluate debt and cash rows individually.

James_Buist Not too sure if this is what you're looking for, but the attached file contains a couple different possibilities using GROUPBY or SCAN...