Import your bank statement file

Before following the steps below, you must download your online bank statement from your bank, in the correct format.

1. Click Bank accounts, select the required bank account and from the Reconcile drop-down, click E-reconcile.
2. Enter the Statement End Date and Statement End Balance and click OK.
3. Click File then click Import Bank Transactions and browse to the bank statement file to be imported then click Open.

If the statement file doesn't appear, this is because the file isn't in the correct format. You must download the file again from your bank software in the correct format. To find out more information about the file formats, proceed to the file formats section. This doesn't import transactions into the audit trail.

File formats

Banking software	iBusiness Banking (iBB)
Required component	Allied Irish iBB
E-Reconcile file type	The statement file is a text file but has the file extension OUT

The statement file is a text file and the default file name is <account number>.out, for example 12345678.out - However, you can change this file name if required.

Each statement file consists of the following:

• One header record - This is always the first line in the file.
• One or more transaction records - Each transaction is represented on its own line.
• One end of file record - This is always the last line in the file.

Header record format null;null;AIB8

The header record consists of six fields separated by a comma, as follows:

Position	Name	Additional information	Example
1	Record Type	This is always '01'	01
2	Last Refresh	This is always in the format: YYYY-MM-DD-HH.MM.SS.000000	2011-10-31-11.39.06.000000
3	Sort Code		112233
4	Account Number		12345678
5	A/C Name		Stationery Mart Ltd
6	Reserved	This field should be left blank as it's currently not in use.

View screenshotnull;null;ss11

Transaction record format null;null;AIB9

The transaction record consists of 11 fields separated by a comma, as follows:

Position	Name	Additional information	Example
1	Record Type	This is always '02'	02
2	Posted Date	The format of this is always DD/MM/YYYY	01/10/2011
3	Value Date	The format of this is always DD/MM/YYYY   NOTE: Only applicable to Exact and MT940 accounts, this is always blank for branch banking accounts.
4	Narrative	This field may contain a comma which will invalidate the file format. You must remove the comma when you import the file into Sage Accounts.	Interest Charged
5	Currency		EUR
6	Debit Amount	This is blank for credit transactions. It can contain up to 13 digits before the decimal point and two after the decimal point.	83.70
7	Credit Amount	This is blank for debit transactions.   It can contain up to 13 digits before the decimal point and two after the decimal point.
8	Closing Ledger Balance	For dates with more than one transaction, this is blank for all except the last transaction of the day. It can contain up to 13 digits before the decimal point and two after the decimal point.	-1532.98
9	Bank Reference	This is only valid for MT940 accounts.
10	Customer Reference	This is only valid for MT940 accounts.
11	Closing Value Balance	For dates with more than one transaction, this is blank for all except the last transaction of the day. It can contain up to 13 digits before the decimal point and two after the decimal point.

View screenshotnull;null;ss15

End of file record format null;null;AIB10

The end of file record consists of two fields separated by a comma, as follows:

Position	Name	Additional information	Example
1	Record Type	This is always 99	99
2	Description	This is always 'End of File'	End of File

View screenshotnull;null;ss20

Choose your banking software:

Please choose...Corporate Online (CO)Business Internet Banking

Banking software	Corporate Online (CO) Formerly known as Corporate Internet Banking (CIB) or CHOBS
Required component	Bank of Scotland CHOBS
E-Reconcile file type	The statement file is a text file but has the file extension TRN

The file consists of a number of records, each line represents one transaction and contains eight fields enclosed in speech marks and separated by a comma, as follows:

Position	Name	Additional information	Example
1	Posting Date	This is always in the format YYYYMMDD	20111001
2	Amount	This is two decimal places and is pre-filled with spaces up to 19 characters. NOTE: The TransType in position 5 determines whether it's a debit or a credit.	83.70
3	TransCode	A three character string which represents the nature of transaction.	S/O
4	TransDesc	A description for the transaction.	Interest Charged
5	TransType	A single character: 'C' for credit or 'D' for debit.	D
6	FwdPostedFlag	A single character: 'T' if forward posting is on, 'F' if forward posting is off.	F
7	SortCode		112233
8	Account No		12345678

Banking software	Business Internet Banking
Required component	HBOS Business Internet Banking
E-Reconcile file type	Comma separated file (*.CSV)

To import the statement file, the format should be as follows:

Position	Name	Additional information	Example
1	Sort Code		11-22-33
2	Account Number		12345678
3	Date	Always in the format DD-MMM-YY where MMM is a three letter representation of the month.	01-Oct-11
4	Transaction Type		CHQ
5	Narrative	The transaction details. If the transaction type is CHQ, the narrative populates the reference of the transaction if it isn't CHQ it populates the details of the transaction.	100019
6	Payment Amount	This is blank for receipts.	83.70
7	Receipt Amount	This is blank for payments.	83.70
8	Balance		1514.92

Reconciliation window

The following values appear and can't be amended:

In the Bank Reconciliation window, the following information appears for each of the imported bank transactions and Sage Accounts transactions. Initially this information is sorted by transaction number. To change the order in which they are sorted, click any of the column headings. For example, to sort these transactions by date order, click the Date column heading.

Automatically reconcile

NOTE: If you exit e-reconciliation with out reconciling part way through, any matches made will need to be matched again.

1. From the Reconciliation window, select the required Automatic option, for example:
   

   Match Full	To automatically match transactions with the same reference and amount, click Match Full.
   Match Amounts	To automatically match transactions with the same amount, click Match Amounts.
   Match Refs	To automatically match transactions with the same reference, click Match Refs.

2. If more than one matching transaction is found, the Duplicate Transaction window appears and the following options then are available:
   
   
   • To confirm the match, select the transaction to be matched click Match.
     
     If required, to unmatch a transaction, open the Tasks menu then choose Unmatch. You can either unmatch all matched transactions or select specific transactions to unmatch.
     
     
   • To proceed with the automatic matching process, without confirming the matching transactions > Next.
   • To close the window without matching transactions click Cancel.
     
     The matching transactions are highlighted in green.
     
     The Matched With column displays the number of the transaction that has been confirmed as a match. The values in Difference, Uncleared Items and Reconciled Balance are amended as transactions are selected.
     
     
3. To verify the matched transactions click Confirm.
   
   To view the confirmed transactions, open the View menu then choose Confirmed, this displays only the confirmed transactions.
   
   Alternatively, to display all transactions, open the View menu then choose All. The matched and confirmed transactions are in grey.
   
   
4. To reconcile the confirmed transactions click Reconcile.
When using e-Reconcile no PDF statement is generated when you reconcile the transactions.
5. To reconcile the matched transactions and return to the Bank Accounts window click Yes.
   
   If you use e-Reconciliation, you can't save your matched or confirmed transactions unless you complete the reconciliation. If you close the Reconciliation window and choose to ignore your changes, when you next use this option, the transactions reappear in the bank transactions list.

Manually reconcile

NOTE: If you exit e-reconciliation with out reconciling part way through, any matches made will need to be matched again.

1. From either the Sage transaction list, or the bank transaction list, select the transaction(s) you want to reconcile.
   
   To select more than one transaction, hold the Shift key.
   
   
2. From the opposite list, select the transaction(s) you want to reconcile against.
3. To match the transactions, select Manual Match.
   
   If the matched totals are different, you receive a message to confirm the match. If the Automatically confirm Manual Matches check box is selected, the Match Manual option confirms the transactions and removes them from the window. To select this check box, open the Tools menu then choose Options.
   
   
4. To verify the matched transactions click Confirm.
   
   To view only the confirmed transactions, from the menu bar open the View menu then choose Confirmed. To view all transactions, from the menu bar open the View menu then choose All. The matched and confirmed transactions are in grey.
   
   
5. To reconcile the confirmed transactions click Reconcile.
   
   When using e-Reconcile no PDF statement is generated when you reconcile the transactions.
   
   
6. To reconcile the matched transactions and return to the Bank Accounts window click Yes.
   
   If you use e-Reconciliation, you can't save your matched or confirmed transactions unless you complete the reconciliation. If you close the Reconciliation window and choose to ignore your changes, when you next use this option, the transactions reappear in the bank transactions list.

Reconcile reversed transactions

There may be circumstances where you enter a transaction in the bank account and then you post a reversing transaction. For example, you may transfer some money to another bank in error and then transfer it back to reverse it. You can't reconcile these in the e-Reconciliation window as there are no transactions to match these to. Therefore you must manually reconcile these transactions in Corrections.

1. Click Transactions, select the required transaction click Edit.
2. Select the Bank rec on check box and enter a date.
3. Click Save and click Yes.

Avoid duplicate statement info on a network

If you're on a network and you've more than one user importing statement files into e-Reconcile you may have duplicate entries in the Reconcile window.

To avoid this you must edit the path for the Reconciled statement information in BANKSERV.INI file on each PC which uses e-Reconcile.

1. In Windows browse to the following folder:
   

   Windows 8, Windows 7 and Windows Vista	C:\ProgramData\Sage\Sage EBanking
   Windows XP	C:\Documents and Settings\All Users\Application Data\Sage\Sage Ebanking

2. Right-click BANKSERV.INI then choose Open With, select Notepad then click Open.
3. Locate the entry for BankTransactionDir=
   
   This is normally at the top of the list.
   
   
4. Type in the common shared directory where the e-reconciliation files are held, this should be a path on the computer which holds your Sage Accounts data.
5. Click File and click Save.
6. Repeat the procedure on all relevant computers.