Hi Tom First of all many thanks for the script. It has been a very busy for these few weeks, and I got no time to try out yet...sorry for that. I believe it's work. haha.... Anyway, I will try it out next week and update the status here. Once again, thanks for your kind help:) kelvin ... View more

Hi Tom Or I should re-phrase as below:- 1st transaction:- Credited 20 & debited 10 on 1Jan2014 transaction look alike:- Date               Credit     Debit     Balance     Credit Balance 1Jan2014        20          10           10              10 2nd Transaction :- Debited 5 on  5Jan2014 transaction look alike:- Date               Credit     Debit     Balance     Credit Balance 1Jan2014        20          10           10              10    5 (Balance left only 5 due to second debit transaction) 5Jan2014         0            5             5                0 3rd Transaction :- Credited 30 on 15Jan2014 transaction look alike:- Date               Credit     Debit     Balance     Credit Balance 1Jan2014        20          10           10               5 (Balance remain as the 3rd transaction was not debit transaction) 5Jan2014         0            5             5                0 15Jan2014      30           0           35                30 (Balance for this new credit transaction) Assuming the new transaction come in, 4th transaction:- Debited 15 on 20Jan2014 transaction look alike:- Date               Credit     Debit     Balance     Credit Balance 1Jan2014        20          10           10               5    0 (The new debit transaction of 15 take from the this balance 1st) 5Jan2014         0            5             5                0 15Jan2014      30           0           35                30  20 (The balance of the new debit transaction 10, (15-5) will deduct from this transaction as the previous transaction had already become 0) 20Jan2014      0           15           20                0 In summary, whenever there is a new debit transaction comes in, it will deduct from the what ever is left on previous credit balance 1st base on FIFO logic. by end of the day, the latest 'Balance' value, which is here is 20 have to be equal to sum of all 'Credit balance' Not sure this will make things even complicated? :smileyconfused: ... View more

Good morning Tom   Sorry for my poor explanation.   What I am trying to do is forecasting the expiry credit balance. The transactional balance will only exist at all the credit transaction.   Assuming, I have credited 20 on 01Jan2014 Debited 10 on 01Jan2014 Another debit transaction of 5 on 5Jan2014       I need the 1 st credit transactional balance appeared as 5, which is (20-(10+5)) If I got another credit transaction of 30 on 15Jan2014, the transactional balance will appeared to be the same value with the credit value. This is because the 1 st credit transaction still with balance of 5. Hope the above can explain better… ... View more

Good morning Tom Thanks for the code, i have my calculation on balance column, but I am still figuring out the logic of "transactional balance":smileyconfused:. I try to apply the FIFO rule on "Transactional Balance" column, which the 1st debit amount have to minus off from the 1st credit amount (if any). If the 1st debit amount is greather than 1st credit amount, the balance will deduct from 2nd credit transaction... ... View more

Hi Tom Yeap, I am okay with SAS program window, but is only familiar to some of the codes. ... View more