Multiple column calculations

Question

I have a output file that lists amounts for 2 accounts by date.

I have 2 things that I need assistance with:

I need the beginning balance amount to carry over each day from the prior day's ending balance

I also need the ABC & DEF accounts summarized (Net total), then this amount added to the beginning balance.

This is the starting file

Desired output

statement.xlsx

AngelosPachis · Accepted Answer

Hi @tiverson ,

Here's how you can achieve this. For each bank you can estimate the net total per day via a summarize tool and then deduce the ending balance and beginning balance for each date.

Then you want to write those three rows of data on the template you have created in the same file. To do that, you first need a block until done tool to make sure that all records are read from your input until you access it again to write an output. The you split into the three distinct rows and in each output tool you define the range you want to write to

The highlighted options are critical to achieve the output in the format you are after. Make sure that you repoint everything to the correct file.

Hope that helps,

Angelos