Auto Journal Entry Creation based on the bank statement download

Asif_Beginner

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

Book DateTransaction TypeSettled AmountBank ReferenceAll Transaction Details
4/9/2018I/W RTGS PAYMTS100aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/10/2018NEFT INWARD200aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/10/2018TDS ON DEP. INT-500bbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/11/2018INW.REM.ABROAD1000bbbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/11/2018GST-100cccccccccccasasdsasadasdsadasdasddsadasdasds
4/11/2018DEP LIQUIDATED1050cccccccccccasasdsasadasdsadasdasddsadasdasds
4/11/2018DEP. INTEREST1050aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/11/2018TDS ON DEP. INT-16aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/12/2018CUSTOM DUTYPYMT-20bbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/13/2018DEP LIQUIDATED20bbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/14/2018DEP. INTEREST-20ccccccccccasasdsasadasdsadasdasddsadasdasds
4/15/2018IMPORT COLL'N20ccccccccccasasdsasadasdsadasdasddsadasdasds
4/16/2018ETAX OUTWARD-20aaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/17/2018I/W RTGS PAYMTS-20aaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/18/2018NEFT OUTWARD-20bbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/19/2018NEFT INWARD200bbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/20/2018TDS ON DEP. INT200ccccccccccasasdsasadasdsadasdasddsadasdasds
4/21/2018POSTAGE / TELEX100ccccccccccasasdsasadasdsadasdasddsadasdasds
4/22/2018OUT.REM.ABROAD-100aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/23/2018INW.REM.ABROAD100aaaaaaaaaasasdsasadasdsadasdasddsadasdasds
4/24/2018GST-60bbbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/25/2018DEPOSIT CREATED200bbbbbbbbbasasdsasadasdsadasdasddsadasdasds
4/26/2018HOUSE TRANSFER-100cccccccccccasasdsasadasdsadasdasddsadasdasds
4/27/2018IB POSITION TRF-100cccccccccccasasdsasadasdsadasdasddsadasdasds
4/28/2018CHEQUE PAID-IN-100cccccccccccasasdsasadasdsadasdasddsadasdasds

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

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.

Transaction TypeDebit/Credit GlDebit/Credit GL
CUSTOM DUTYPYMTCreditXXXXXXXXXXDebitYYYYYYYYYY
DEP LIQUIDATEDDebitXXXXXXXXXXCreditYYYYYYYYYY
DEP. INTERESTDebitXXXXXXXXXXCreditYYYYYYYYYY
IMPORT COLL'NCreditXXXXXXXXXXDebitYYYYYYYYYY
ETAX OUTWARDCreditXXXXXXXXXXDebitYYYYYYYYYY
I/W RTGS PAYMTSDebitXXXXXXXXXXCreditYYYYYYYYYY
NEFT OUTWARDCreditXXXXXXXXXXDebitYYYYYYYYYY
NEFT INWARDDebitXXXXXXXXXXCreditYYYYYYYYYY
TDS ON DEP. INTCreditXXXXXXXXXXDebitYYYYYYYYYY
POSTAGE / TELEXCreditXXXXXXXXXXDebitYYYYYYYYYY
OUT.REM.ABROADCreditXXXXXXXXXXDebitYYYYYYYYYY
INW.REM.ABROADDebitXXXXXXXXXXCreditYYYYYYYYYY
GSTCreditXXXXXXXXXXDebitYYYYYYYYYY
DEPOSIT CREATEDCreditXXXXXXXXXXDebitYYYYYYYYYY
HOUSE TRANSFERCreditXXXXXXXXXXDebitYYYYYYYYYY
IB POSITION TRFCreditXXXXXXXXXXDebitYYYYYYYYYY
CHEQUE PAID-INDebitXXXXXXXXXXCreditYYYYYYYYYY

<tbody>
</tbody><colgroup><col><col span="2"><col span="2"></colgroup>



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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Asif_Beginner

New Member
Joined
Apr 25, 2018
Messages
4
Output required in excel as per below format

Accounting documentDocument DateReferenceGL accountDebit amountCredit amountLine item textAllocation
110.04.2017fdsasasaddsXXXXXXXXXX100safjlskafjasfjfdsasasadds
110.04.2017adadasdYYYYYYY1000sfksl;afjlksafjladadasd
210.04.2017asdasdXXXXXXXXXX500000safjlasdjlasdasdasd
210.04.2017adssaYYYYYYY500000adjlsadjlasadssa

<tbody>
</tbody><colgroup><col span="2"><col><col><col span="2"><col><col></colgroup>
 

Forum statistics

Threads
1,148,293
Messages
5,745,912
Members
423,983
Latest member
blackworx

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
Top