Importing a file

Once you have downloaded the OFX, QFX or CSV file from your online banking, navigate to the Banking tab and then the Bank Statement Import tab. Click the Import New Bank Statement button.

• Bank account: Select the appropriate bank account for the statement being imported.
• File to import: Click on the Browse button and select the OFX, QFX or CSV file that you previously downloaded and saved on your computer.

 

Once you have made your selections, click the Import button to read the transactions in the selected file.

 

If you selected a CSV file for import, you will need to assign each of the fields in the file to an appropriate system field by selecting the available system fields in the column combo boxes provided.

• Don't Import: use this option to ignore the values in a particular field.
• Transaction Date: must be assigned.
• Amount: must be assigned.
• Payee: represents the payee or payer and is the key field for creating an Import Rule.
• Transaction Memo: the values in this field will appear in the Memo field in the saved bank transaction.
• Reference: the values in this field will appear in the Reference field in the saved bank transaction.
• Item Description: the values in this field will appear in the line item Description field in the saved bank transaction.




• First row contains headings: use this option to prevent a row containing headings from being imported.
• Date format: this will be automatically determined if possible and must match the date format in the file. 

 

Cell values can be edited before the Bank Statement is saved. It is not advisabe to edit dates or amounts but the functionality can be useful to add text or comments.

Click the Save button to create the bank statement. You will be transferred to the Bank Satement Detail screen from where you can process the bank statement.

 

If you selected an OFX or QFX file for import, the fields will appear similar to the image below:

• Transaction Date: will be used in the transaction date field of bank transaction.
• Amount: the amount to be used in the bank transaction.
• Payee: represents the payee or payer and is the key field for creating an Import Rule.
• Description: the values in this field will appear in the Memo field in the saved bank transaction.

 

Cell values can be edited before the Bank Statement is saved. It is not advisabe to edit dates or amounts but the functionality can be useful to add text or comments.

Click the Save button to create the bank statement. You will be transferred to the Bank Satement Detail screen from where you can process the bank statement.

 

NOTE

When importing a bank statement, timing differences can arise between the actual transaction date and the value appearing on your bank statement. Time taken for cheques to clear is a good example of this. This is especially relevant if you are registered for Cash Accounting VAT, which has special rules regarding receipt date and does not consider the relevant date to be the date the cash clears in your bank statement (see HMRC website for further details) In some cases, it is therefore more appropriate to make use of Receive Money or Spend Money options to initially record the entry followed by a 'matching' activity when the values clear on the bank statement which has been imported.

 

Tip for users who cannot download Bank Statements

You can create a bank statement import file from scratch by entering a record for each bank transaction in your favourite spreadsheet program and saving the file as a CSV type. This is helpful if you cannot download a statement from your bank but wish to use the Import Bank Statement tool and would like an online record of statements in AccountsPortal.

File preparation:

• Open spreadsheet program.
• Create rows of information representing bank transactions.
• Enter at least 4 columns of information.
• Amount: positive amounts create Receive Money and negative amounts create Spend Money.
  Date: date transaction clears in bank account. Use format DD/MM/YYYY, YYYY/MM/DD, YYYY-MM-DD or DD-MM-YYYY.
  Payer/Payee: the text entered here is matched against the search term of Import Rules.
  Description: the text entered here can be inserted into the Reference, Memo or Description field of the bank transaction.
• Save the file as file type CSV.
  
• If any text or numbers contain commas, then they should be delimited with quotation marks as follows (the spreadsheet program should do this automatically):

10/06/2009,"1,000.21",Endcorp,INV39
11/06/2009,"-1,250.20",Telco Ltd,Telephone Charges
15/06/2009,-25.50,Charges,"Bank Charges, May09"

Go back to the top of this page and process the file as a CSV type.

Importing a PayPal file

Your PayPal account is essentially a bank account and therefore it is advisable to add a new bank account in the Chart of Accounts to manage your PayPal transactions.

Download/Export a file from PayPal in the CSV format. You don't need to modify the file before importing but its worth noting that only the following fields are imported:

PayPal file field	Rule
Date	Mandatory field
Name	Mandatory field
Type
Gross	Mandatory field
Fee	Mandatory field
From Email Address
To Email Address
Transaction ID	Mandatory field
Item Title

Please note that the maximum lines per import file is 250.

Navigate to the Banking tab and then the Bank Statement Import tab. Click the Import New Bank Statement button.

• Bank account: Select the appropriate bank account for the statement being imported.
• PayPal Import: This option must be selected to successfully import PayPal files.
• File to import: Click on the Browse button and select the PayPal file that you previously downloaded and saved on your computer (must be in CSV format).

 

Once you have made your selections, click the Import button to read the transactions in the selected file. You will be notified with a confirmation message if the file is successfully imported.

 

You will notice that there are more transaction items imported than existed in the original PayPal file downloaded. This is because fees charged by PayPal are split into separate transaction lines in the imported statement. This enables the full amount paid by the customer to be allocated to their invoice as well as a separate transaction being created to reflect the fee value.

Assign each of the fields in the PayPal file to an appropriate system field by selecting the available system fields in the column combo boxes provided.

• Don't Import: use this option to ignore the values in a particular field.
• Transaction Date: must be assigned.
• Amount: must be assigned.
• Payee: represents the payee or payer and is the key field for creating an Import Rule. For PayPal files, the default assignment is to the Name field.
• Transaction Memo: the values in this field will appear in the Memo field in the saved bank transaction.
• Reference: the values in this field will appear in the Reference field in the saved bank transaction.
• Item Description: the values in this field will appear in the line item Description field in the saved bank transaction.




• First row contains headings: use this option to prevent a row containing headings from being imported.
• Date format: this will be automatically determined if possible and must match the date format in the file. 

 

Cell values can be edited before the PayPal Bank Statement is saved. It is not advisabe to edit dates or amounts but the functionality can be useful to add text or comments.

Click the Save button to create the bank statement. You will be transferred to the Bank Satement Detail screen from where you can process the bank statement.

Process a Bank Statement

Navigate to the Banking tab and then the Bank Statement Import tab.

 

• Bank account: Bank Account of the statement.
• Start date: The date of the earliest transaction.
• End date: The date of the latest transaction.
• Import date: The date you imported the file into AccountsPortal.
• Statement lines processed: Current status of the statement.
• Delete statement: The option to delete a statement only exists if there are 0 lines processed (see Delete Bank Statement).

 

Click on a bank statement line to open the statement for processing

• Date: The date the transaction cleared at the bank or appeared on the statement.
• Amount: The amount provided in the statement.
• Payee: This is the payee or payer. The text in this field is used to search for the relevant Import Rule. It will also be used as the basis for creating new rules automatically if activated in your organisation's settings.
• Reference: Any relevant information provided by the bank that you assign to the Reference field in the bank transaction.
• Transaction Memo: Any relevant information provided by the bank that you assign to the Memo field in the bank transaction.
• Line Item Description: Any relevant information provided by the bank that you assign to the line item Description field in the bank transaction.
• Status:
  
  • P : Click this button to initiate a new bank transaction using Spend Money for negative amounts and Receive Money for positive amounts.
  • R : This button is only active if an Import Rule is found matching the value in the Payee field. Click the R button to create a Spend Money or a Receive Money bank transaction using the rule.
  • M : This button is only active if an existing unreconciled bank transaction has been entered which matches the statement line item on Amount. An example might be a cheque you received and banked which was already saved as a bank transaction. The cheque has now cleared in your bank account. Avoid creating a new Receive Money which would be a duplicate. Click the M button to match the items (see below for more information on using M).
• Delete line: Any unprocessed statement line can be deleted.

 

Once you have completed the creation of a new bank transaction or matched a statement line item to an existing bank transaction, the status will be updated to reflect the bank transaction (saved bank transactions will be visible in the Bank Transactions tab). There will also be a V and a U button visible.

• Status:
  
  • V : This button is only active for statement line items already processed. Click the V button to view or edit the saved transaction.
  • U : This button is only active for statement line items already processed. Click the U button to unlink statement line items and saved bank transactions. This action will mark the saved bank transaction as unreconciled but does not delete it. The bank transaction can only be deleted in the Bank Transactions screen.
• Delete line: Processed statement lines can no longer be deleted.

 

 

M - Matching statement lines to bank transactions

You can only match one bank transaction to a statement line. Select the appropriate bank transaction and click on Use Selected to create the link.

Delete Bank Statement

Navigate to the Banking tab and then the Bank Statement Import tab.

The Delete indicator on the bank statement line will only be available if none of the line items have been processed.

Click the button and confirm the prompt to delete the statement.

 

NOTE

Line items that have been processed (thus preventing deletion of a statement) can be reset to unprocessed.  Click the U button to unlink statement line items and saved bank transactions. This action will mark the saved bank transaction as unreconciled but does not delete it. The bank transaction can only be deleted in the Bank Transactions screen (see Process a Bank Statement).

Manage Import Rules

During bank statement import, AccountsPortal checks for existing rules which match the Payee (payee/payer) field in the bank statement line. If a rule is found, it can be used to quickstart the bank transaction by selecting the appropriate Transaction Type and where applicable the Customer/Supplier, Account and VAT code. The purpose of this functionality it to increase efficiency and consistency of how bank transactions are created from bank statement lines.

If activated in your organisation's settings, Import Rules will be created automatically when a bank statement line is processed using button P (see Process a  Bank Statement). The rule is based on a search term that is derived from the Payee field in the bank statement line and is only created once. If you want to change the settings of a rule or delete the rule, then follow the instructions below.

Navigate to the Banking tab, click on Bank Statement Import and then Manage Import Rules.

 

Click on an existing rule to edit the settings. All fields can be changed including the Transaction Type.

The existing field values are based on the bank transaction created from the first statement imported. It is not necessary to differentiate at a rule level between payments and receipts as this determined whilst processing a bank statement line based on the sign of the Amount field. 

• Search Term: This is the key text checked to see if it matches a value in the Payee field of the statement line. A positive match results in this rule being available for use during bank statement processing. During automatic creation of a rule the text is set to lowercase.
• Customer/Supplier: Customer/supplier to be used with this rule. Must be maintained if Transaction Type is Invoice Payment or Refund.
• Account: Acount to be used with this rule. Only applicable if Transaction Type is General Payment.
• VAT Treatment: This option is only visible if your organisation is registered for VAT. It is mostly relevant to UK organisations with European sales or purchases. See VAT UK - Box by Box for more information.
• VAT: VAT code to be used with this rule. Only applicable if Transaction Type is General Payment.
• Category: Optional field for further classification of line item.
• Transaction Type: This determines the type of bank transaction that will be created when using the rule (see Receive Money or Spend Money).

Automatic PayPal Import Tool

AccountsPortal can automatically import your PayPal data each day. In order for this to function correctly, please follow the steps below closely:

• Note that you must have a PayPal Business Account; you will not be able to use this tool on PayPal Personal Accounts.
• Imports take place automatically at approximately 05.00AM each morning, and will import transactions from the previous day
• Only transactions marked by PayPal as Completed, Cleared or Reversed will be imported.
• Transactions that have been imported using this tool will be ready for processing as per normal bank imports. This means that you will have to process the transactions once they have been imported. In the future, we will be adding in additional functionality to help automate this part of the process as well.

 

STEP 1

1.1 Log into AccountsPortal and navigate to the Chart of Accounts.

1.2 Select the Bank Account in AccountsPortal that you wish to use for your PayPal Imports.

1.3 In the Bank Account popup, select the checkbox 'Enable automatic PayPal imports for this account'

1.4 Enter your PayPal email address. Make sure to type this email address correctly.

1.5 Optionally, select whether you would like all the PayPal fees to be grouped into a single transaction for each import or not.

1.6 Click the Save button.

 

STEP 2

Log into your PayPal account and click on the Profile menu link.

 

STEP 3

Click on the 'API Access' link.

 

STEP 4

Click on the 'Add or edit API permissions' link.

 

STEP 5

• In the API account username field, enter paypal_api1.accountsportal.com.
• IMPORTANT: You may be presented with one of two screens as follows. If you are presented with the first screen, then select the 'Reporting and Backoffice APIs' option only. If you are presented with the second screen, then select 'Obtain information about a single transaction' and 'Search your transactions for items that match specific criteria and display the results' options only
• Click the Submit/Addbutton.

OPTION 1

OPTION 2

STEP 6

Confirm that the information is correct and click the Give Permission button.

 

STEP 7

The last step is to send an email from your registered PayPal email address to our support ticket desk requesting that we enable PayPal imports for your account. For security reasons, this email MUST be sent from your registered PayPal email address.

 

NOTE THAT YOUR PAYPAL IMPORTS WILL NOT COMMENCE UNTIL SUCH TIME AS ALL OF THE ABOVE STEPS HAVE BEEN COMPLETED