Auto Journal Entry Creation based on the bank statement download

[Asif_Beginner]

Below is the Raw bank statement

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

<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 Type	Debit/Credit	Gl	Debit/Credit	GL
CUSTOM DUTYPYMT	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
DEP LIQUIDATED	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY
DEP. INTEREST	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY
IMPORT COLL'N	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
ETAX OUTWARD	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
I/W RTGS PAYMTS	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY
NEFT OUTWARD	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
NEFT INWARD	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY
TDS ON DEP. INT	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
POSTAGE / TELEX	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
OUT.REM.ABROAD	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
INW.REM.ABROAD	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY
GST	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
DEPOSIT CREATED	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
HOUSE TRANSFER	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
IB POSITION TRF	Credit	XXXXXXXXXX	Debit	YYYYYYYYYY
CHEQUE PAID-IN	Debit	XXXXXXXXXX	Credit	YYYYYYYYYY

<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

 

[Asif_Beginner]

Output required in excel as per below format

Accounting document	Document Date	Reference	GL account	Debit amount	Credit amount	Line item text	Allocation
1	10.04.2017	fdsasasadds	XXXXXXXXXX	100		safjlskafjasfj	fdsasasadds
1	10.04.2017	adadasd	YYYYYYY		1000	sfksl;afjlksafjl	adadasd
2	10.04.2017	asdasd	XXXXXXXXXX	500000		safjlasdjlasd	asdasd
2	10.04.2017	adssa	YYYYYYY		500000	adjlsadjlas	adssa

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