Bank Statement transaction reconciliation

I have constructed a workflow to perform the cross calculations shown in the image below (opening balance + credit - debit = closing balance). What I want to happen, as shown in the image, is for the workflow to run the "opening balance + credits- debits" calculations for the current day using the "closing balance" of the previous day as the "opening balance" of the current day. I am attaching the screenshot for reference, sample dates chosen for reference is 14th Sept 2015- 17th Sept 2015.

Also, I am attaching the workflow also for reference.

Data sets screenshot (15th Sept 2015):

Data sets screenshot (16th Sept 2015):

Data sets screenshot (17th Sept 2015):

Workflow

Datasets

Macros

Connectors

Database Connection

Run Command

Accepted answers

All comments

IraWatt

Hey @tarunveer, could you attach the example workflow and also provide an example calculation, cant quite follow just looking at the screenshots?

Thanks,

Ira

tarunveer

Hi Ira,

I am attaching the workflow. Please let me know if this works. I explain by an example:
1. Opening Balance + Credit - Debit (14th Sept) = Closing balance of (14th Sept)

2. Closing Balance of (14th Sept) = Opening Balance of (15th Sept).

3. Opening Balance + Credit - Debit (15th Sept) = Closing balance of (15th Sept)

This cycle must repeat for all the dates.

Regards,

Sample workflow Bank.yxmd

IraWatt

thanks @tarunveer you may need to send over the data too as it is not attached

tarunveer

I am attaching the sample data.

Bank Statement Sample.xlsx

Ladarthure

Hi @tarunveer

one way to do so is to first calculate the movement per day, then use a running total, and calculate everything from there. You need to have your data sorted to do it easily.

I attached you a sample I made so taht you can replicate what I did in your case.

Calculate the daily balance
sort my data by date
calculate a running total of the daily balance to know how much have moved since the beginning
add this value to the opening balance amount you have to calculate the daily closing balance
substract to this value (closing balance) what has moved during this day

Hope it helps

balance.yxmd

tarunveer

Hi Ladarthure,
Thank you for your response.

Wanted certain clarifications regarding the workflow shared:

- How to calculate the opening balance. As per the workflow, it has been taken as 50 for the first entry, is the value inserted?

- Also, do we calculate the opening balance first or the ending balance? (According to me, the opening balance should be calculated first)

Regards,

Tarun

Ladarthure

Hi @tarunveer,

with my method, I just set a value for the first opening balance I have, set at 50 but, you could change it to whatever value needed. And surprisingly, with this method, you first calculate the closing everyday, and based on this, you then calculate the opening one.

Arthur

Brad_Coenen

In the workflow that you built, are you connecting directly to your bank site, treasury module, or general ledger to pull any of the information in and reconcile it?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703