|
Formatting Imported Record
Illustration example: Formatting General Ledger
The general ledger exported from accounting package can't be imported directly into the system.
We have to convert excel worksheet from Format 1 to Format 2.
The conversion requires excel skill, such as
1.Use of excel IF condition formula to produce separate column for Account code, Account name and Balance column.
2.Use of data filtering function to delete unnecessary row or column.
3.Use of text-to-column method to formulate date column.
If you are familiar with those excel skills, you should be able to prepare a set of GL in 10 minutes.
Click here to download pre-built excel formula that speed up the formatting process in preparation of imported account records.
Format 1: Data exported from accounting package
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
|
1
|
Date
|
Transaction description
|
Journal
|
Ref I
|
Ref II
|
Debit
|
Credit
|
Balance
|
|
2
|
Account No. :1100/000 : DIVIDEND PAID
|
|
3
|
|
|
|
BALANCE B/F
|
|
35,000.00
|
|
35,000.00
|
|
4
|
23/11/2016
|
PBB-DIVIDEND
|
Bank
|
PBB 011101
|
|
|
17,500.00
|
17,500.00
|
|
5
|
23/11/2016
|
PBB-DIVIDEND
|
Bank
|
PBB 011102
|
|
|
17,500.00
|
0
|
|
6
|
Account No. :1200/000 : TAXATION
|
|
7
|
|
|
|
BALANCE B/F
|
|
|
|
-500.00
|
Format 2: The standard format required for GL import for AuditSME
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
|
1
|
Date
|
Account code
|
Account name
|
Transaction description
|
Journal
|
Ref I
|
Ref II
|
Balance
|
|
2
|
|
1050/000
|
Retained earning
|
|
|
BALANCE B/F
|
|
80,150.49
|
|
3
|
|
1100/000
|
Dividend paid
|
|
|
BALANCE B/F
|
|
35,000.00
|
|
4
|
23/11/2016
|
1100/000
|
Dividend paid
|
PBB-DIVIDEND
|
Bank
|
PBB 011101
|
|
17,500.00
|
|
5
|
23/11/2016
|
1100/000
|
Dividend paid
|
PBB-DIVIDEND
|
Bank
|
PBB 011102
|
|
17,500.00
|
|
6
|
|
1200/000
|
Taxation
|
|
|
BALANCE B/F
|
|
-500.00
|
|