Auto Journal Entry Creation based on the bank statement download

A

Asif_Beginner

New Member
Joined
Apr 25, 2018
Messages
4
Below is the Raw bank statement

[TABLE="width: 1067"]
<tbody>[TR]
[TD]Book Date[/TD]
[TD]Transaction Type[/TD]
[TD]Settled Amount[/TD]
[TD]Bank Reference[/TD]
[TD]All Transaction Details[/TD]
[/TR]
[TR]
[TD]4/9/2018[/TD]
[TD]I/W RTGS PAYMTS[/TD]
[TD]100[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/10/2018[/TD]
[TD]NEFT INWARD[/TD]
[TD]200[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/10/2018[/TD]
[TD]TDS ON DEP. INT[/TD]
[TD]-500[/TD]
[TD]bbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/11/2018[/TD]
[TD]INW.REM.ABROAD[/TD]
[TD]1000[/TD]
[TD]bbbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/11/2018[/TD]
[TD]GST[/TD]
[TD]-100[/TD]
[TD]ccccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/11/2018[/TD]
[TD]DEP LIQUIDATED[/TD]
[TD]1050[/TD]
[TD]ccccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/11/2018[/TD]
[TD]DEP. INTEREST[/TD]
[TD]1050[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/11/2018[/TD]
[TD]TDS ON DEP. INT[/TD]
[TD]-16[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/12/2018[/TD]
[TD]CUSTOM DUTYPYMT[/TD]
[TD]-20[/TD]
[TD]bbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/13/2018[/TD]
[TD]DEP LIQUIDATED[/TD]
[TD]20[/TD]
[TD]bbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/14/2018[/TD]
[TD]DEP. INTEREST[/TD]
[TD]-20[/TD]
[TD]cccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/15/2018[/TD]
[TD]IMPORT COLL'N[/TD]
[TD]20[/TD]
[TD]cccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/16/2018[/TD]
[TD]ETAX OUTWARD[/TD]
[TD]-20[/TD]
[TD]aaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/17/2018[/TD]
[TD]I/W RTGS PAYMTS[/TD]
[TD]-20[/TD]
[TD]aaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/18/2018[/TD]
[TD]NEFT OUTWARD[/TD]
[TD]-20[/TD]
[TD]bbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/19/2018[/TD]
[TD]NEFT INWARD[/TD]
[TD]200[/TD]
[TD]bbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/20/2018[/TD]
[TD]TDS ON DEP. INT[/TD]
[TD]200[/TD]
[TD]cccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/21/2018[/TD]
[TD]POSTAGE / TELEX[/TD]
[TD]100[/TD]
[TD]cccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/22/2018[/TD]
[TD]OUT.REM.ABROAD[/TD]
[TD]-100[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/23/2018[/TD]
[TD]INW.REM.ABROAD[/TD]
[TD]100[/TD]
[TD]aaaaaaaaa[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/24/2018[/TD]
[TD]GST[/TD]
[TD]-60[/TD]
[TD]bbbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/25/2018[/TD]
[TD]DEPOSIT CREATED[/TD]
[TD]200[/TD]
[TD]bbbbbbbbb[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/26/2018[/TD]
[TD]HOUSE TRANSFER[/TD]
[TD]-100[/TD]
[TD]ccccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/27/2018[/TD]
[TD]IB POSITION TRF[/TD]
[TD]-100[/TD]
[TD]ccccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
[TR]
[TD]4/28/2018[/TD]
[TD]CHEQUE PAID-IN[/TD]
[TD]-100[/TD]
[TD]ccccccccccc[/TD]
[TD]asasdsasadasdsadasdasddsadasdasds[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

Based on the transaction type and amount (positive/negative value) it should check the below table which to debit and credit to create the journal entry.

[TABLE="width: 694"]
<tbody>[TR]
[TD]Transaction Type[/TD]
[TD]Debit/Credit [/TD]
[TD]Gl[/TD]
[TD]Debit/Credit [/TD]
[TD]GL[/TD]
[/TR]
[TR]
[TD]CUSTOM DUTYPYMT[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]DEP LIQUIDATED[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]DEP. INTEREST[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]IMPORT COLL'N[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]ETAX OUTWARD[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]I/W RTGS PAYMTS[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]NEFT OUTWARD[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]NEFT INWARD[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]TDS ON DEP. INT[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]POSTAGE / TELEX[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]OUT.REM.ABROAD[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]INW.REM.ABROAD[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]GST[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]DEPOSIT CREATED[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]HOUSE TRANSFER[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]IB POSITION TRF[/TD]
[TD]Credit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Debit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
[TR]
[TD]CHEQUE PAID-IN[/TD]
[TD]Debit[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]Credit[/TD]
[TD]YYYYYYYYYY[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col span="2"></colgroup>[/TABLE]



Below is the output required in the journal entry format

SL.No. Document Date Reference GL account Debit amount Credit amount Line item text Allocation
1 10.04.2017 fdsasasadds XXXXXXXXXX 100 safjlskafjj fdsasasadds
1 10.04.2017 adadasd YYYYYYY 100 sfksl;afjll adadasd
2 10.04.2017 asdasd XXXXXXXXXX 500 afjlasdjla asdasd
2 10.04.2017 adssa YYYYYYY 500 adjsadjas adssa


Can anyone please help on this
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Output required in excel as per below format

[TABLE="width: 515"]
<tbody>[TR]
[TD]Accounting document[/TD]
[TD]Document Date[/TD]
[TD]Reference[/TD]
[TD]GL account[/TD]
[TD]Debit amount[/TD]
[TD]Credit amount[/TD]
[TD]Line item text[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]10.04.2017[/TD]
[TD]fdsasasadds[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]safjlskafjasfj[/TD]
[TD]fdsasasadds[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]10.04.2017[/TD]
[TD]adadasd[/TD]
[TD]YYYYYYY[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]sfksl;afjlksafjl[/TD]
[TD]adadasd[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]10.04.2017[/TD]
[TD]asdasd[/TD]
[TD]XXXXXXXXXX[/TD]
[TD]500000[/TD]
[TD][/TD]
[TD]safjlasdjlasd[/TD]
[TD]asdasd[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]10.04.2017[/TD]
[TD]adssa[/TD]
[TD]YYYYYYY[/TD]
[TD][/TD]
[TD]500000[/TD]
[TD]adjlsadjlas[/TD]
[TD]adssa[/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col span="2"><col><col></colgroup>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top