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.