• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Daily Balances and Opening Balance from Closing Balance and Transaction data

Renjitv

New Member
Hi Friends,

I am facing an issue to daily stock balances and opening stock for a given day for an item where I know only closing stock and daily transacted quantities.

My transaction data is like below
Item CodeQuantityUOMMovementTransaction Date
XXX29681
-4​
EADamage Write off
29-Mar-20​
XXX29681
-7​
EASales
16-Apr-20​
XXX29681
8​
EAReturn from Customer
04-Jun-20​
XXX29681
8​
EAPurchase
15-Jun-20​
XXX29681
-8​
EASales
15-Jun-20​

Closing stock data is here

XXX29681
8​
EAClosing Balance
01-Jul-20​
The output I want is like this
Balance RemarksDateBalance Qty
Opening Balance
29-Mar-20​
11​
Closing Balance
29-Mar-20​
7​
Closing Balance
16-Apr-20​
0​
Closing Balance
04-Jun-20​
8​
Closing Balance
15-Jun-20​
16​
Closing Balance
15-Jun-20​
8​
Closing Balance
01-Jul-20​
8​

Appreciate your help.
 

Attachments

Renjitv

New Member
Thanks for your reply

I want to find out opening balance from closing balance which is known to me by add or subtracting transaction quantities. For:-

This case I know closing stock of 1 July 2020 is 8 PCs then there is no transactions till 16th June 2020 so all those days stock balance was 8 PCs itself.

But on 15th June a sales happened for 8 PCS and purchase of 8 PCS to on the same day so opening balance will be 8 PCs. like this I need some kind reverse running total to find it.

I did a mistake in output shown 15 June Closing balance in my output there one duplicate line 16 PCs need to deleted.
 

AlanSidman

Well-Known Member
So what you are telling us is:

1. You know the current (closing balance)
2. You know the transactions to get to the closing balance
3. You do not know the opening balance and want to find it out from the data you have i 1 and 2 above.

Is this a correct understanding of your issue?
 

Renjitv

New Member
Yes, what you mentioned the right understanding of the issue. Thanks for your time

As I got huge number of around 5000 items to find approximately 1 Million total rows.
 
Top