This article supplements our YouTube video about importing OFX bank statements into Sage Accounting. which replaces the manual capturing of bank transactions.
I am only going to cover aspects of getting bank transactions into Sage Accounting. To deal with banking in accounting there is more to do, BUT if you stumble on this step, which is all about getting bulk data into your accounting system it is going to cost you. If you start dealing with your banking transactions the right way, you will have a solid foundation to build the rest of your business tasks and ultimately grow a successful, efficient business.
Step 1: Adding bank transactions
The first step of dealing with your bank receipts or deposits and bank payments or transfers is adding bank transactions to Sage Accounting.
I sometimes still see people manually capturing bank transactions and it is heartbreaking to know that they are wasting their valuable time on an activity that does not generate income when the necessary data is already generated by the banks and available to be inserted into your accounting system. A process that is simple to do and will only take seconds or minutes .
Bank accounting in Sage Accounting: Step 2 & 3
In future articles and videos I will the rest of the bank accounting process which is
- Step 2 – Banking categorizing
- and Step 3 – Bank reconciliations.
Automatic and Manual Bank Uploads in Sage Accounting
To give some context to the topic of getting bank transactions into Sage Accounting, I am going to start with the two methods of adding bank transactions.
Bank Feeds is the preferred method and my favorite for adding bank transactions to Sage Accounting. With Bank Feeds a snapshot is taken of your bank statement and the transactions are then automatically added to your bank account in Sage Accounting. Bank Feeds are only available to the following banks in South Africa:
- ABSA
- FNB
- Standard Bank
- Nedbank &
- Capitec
Another method of getting your bank transactions into Sage Accounting is with Manual Uploads.
The most popular manual uploads are CSV and OFX data formats.
Bank Feeds
Bank Feeds is the automatic download of bank transactions into Sage Accounting.
You set up bank feeds by providing your banking credentials. If you would like to get more information about setting up your bank feeds in Sage Accounting have a look at our previous video showing exactly how to set up bank feeds for ABSA banking customers.
Set ups are easy to do, but you must have your bank login details at hand when doing it.
You do not incur additional costs for bank feeds from Sage or the banks that are participating in bank feeds with Sage.
The standard practice is for your Sage Accounting to get the bank feeds on a daily basis.
It is safe to say that you can go as far back as 90-days when starting your bank feeds. In the past I have imported statements going further back than 90-days but it all depends on the bank, so I would not “bank on” going back further than that.
Bank feeds give you a secure connection and are not a payment mechanism. You cannot generate payments to suppliers, employees or other bills. Netcash is a feature that you can add to Sage whereby you can generate payments directly from Sage. Netcash requires API keys and has a whole different solution to Bank Feeds.
Your Bank Feeds lose connection to Sage Accounting when you change any of your bank login details like your password. In that case you have to link your bank account again with your new credentials.
One of the biggest drawbacks of bank feeds is that not all banking profiles can link to Sage Accounting. This is a challenge for many people who have for example business bank accounts linked to private profiles or bank mobile apps only.
Manual Bank Statement Uploads: CSV imports
The CSV bank file import is the first manual bank data entry that we are going to look at.
The CSV bank file imports are definitely not advisable for all situations. I find it most beneficial in emergency situations with large projects where the client is far behind with their bank data capturing, which can go back multiple years and we only have PDF statements available which are then converted into CSV files.
A huge advantage of CSV data files is that there are no limits really on how far back you can go with historic data which bank feeds obviously don’t give you.
As I mentioned before it is a relatively easy process to convert PDF documents into CSV files and banks love to give their clients bank statements in PDF file formats.
CSV files can be edited to suit your needs as a matter of fact in most circumstances the data must be edited to enable you to import the bank transactions into Sage Accounting.
Only 3 columns can be imported into Sage Accounting which is the
- Date, the
- Description and the
- Amount
Other information that might be useful for you like the PAYEE and specific References are omitted from the import.
The biggest disadvantage of CSV file imports is that finding errors when the import fails tends to be difficult and time consuming especially if you are new to doing CSV data imports and you have not ironed out all the nuances and troubleshooting areas that come with it.
Another disadvantage that goes hand in hand with failing imports is that the CSV data sometimes inherits certain data characteristics from the PDF file that results in a failed import to Sage. You sometimes need experience with CSV files and Sage to identify the problems and I think they can be hard to edit. If the data is large enough and it goes back years it can warrant spending time on the CSV files preparing it for the import into Sage Accounting to work.
Manual Bank Statement Uploads: OFX Imports
The second manual upload and one of my favorites is the OFX bank import into Sage Accounting. OFX bank imports is the quick solution when bank feeds are not available.
Most banks have OFX downloads available.
For me the biggest benefit of OFX imports is that you are not required to work on the data and prepare it for importing.
You can go back further than 90-days depending on how much history your bank gives you.
OFX bank statement imports into Sage Accounting simply works and I have not experienced any errors before.
If you have set up banking rules in Sage Accounting whereby your transactions are already categorised based on your bank descriptions, those rules are applied with OFX bank imports making your life easier.
The biggest disadvantage of OFX bank imports, is that it can lead to duplicate transactions, although duplicate transactions are identified by Sage Accounting, unnecessary time can be spent on removing duplicates. You can prevent duplicates from happening by following a simple procedure which I am going to explain later.
Let’s get into the specifics of OFX banking import into Sage Accounting with a real-life scenario where I show you exactly:
- how to find and download a bank statement in OFX format,
- how the import works,
- Tips on how to prevent duplicate transactions,
- The end-result after the import has been completed.
How to find find and download OFX from your bank account
- Login to your online bank account.
- Select the specific bank account that you need to obtain my banking transactions from.
- Select the transaction history and find the date range of historic transactions, if you would like to import the maximum number of transactions.
- Once the history of the bank transactions are populated in your transaction list, you select “Download”.
- The bank provides you with a selection of bank download format options and you then select the OFX option.
- An OFX file will be downloaded somewhere on your PC, mine goes to the “Download” folder. You select and extract the file if a compressed copy of the file is downloaded.
Sage Accounting – Set the start date to prevent duplicate transactions
You open your Sage Accounting application.
At the top navigation bar you select
- Banking, move down
- to transactions, move your cursor to the right and select
- Banking when the hover colour turns blue indication that you are on it.
Your banking transactions screen will open. This is the place where you manually enter or upload transactions in Sage.
Here we get to the interesting part and my method for preventing duplicate transactions to be imported.
The first step is to find and make a note of the last transaction that you have processed in Sage Accounting. If you follow the correct procedure of marking transactions as reviewed after you have completed them you will find the last transaction under your “Reviewed Transactions” button.
In this example there are more than one page of transactions for the processing date and I have to find the last transaction by clicking on page 2. The last transaction is right at the bottom and I make a note of the date. This date will be later used as a starting point when we import the bank statement.
Sage Accounting – Do This: OFX bank import file
You go back to your “New Transactions” screen and you will see when you click on “import bank statements” that OFX is the standard “Import File Type” already selected in the field.
If you don’t see it you can select OFX in the dropdown list.
You can ignore the “Date Format”.
Browse to find the “Import File” that you have saved on your PC.
Select the imported OFX-file which will most likely be your bank account number followed by a .ofx abbreviation and then click Open.
On the right hand side of the screen at the Date Range you can now define the “starting and end date” of the bank statement that you want to import.
We have made a note of our last transaction date which will now become the starting date for our bank transactions. This will prevent the inclusion of duplicate transactions in our banking section.
You select the “To Date” from the calendar which will be the date of your last bank transaction that you want to import. Normally this is the current date of my bank statement.
The last step is to click on the “Import File” button and see how the magic happens.
Sage Accounting – OFX Import successful
You will get a message that the OFX file import completed successfully with the numbers of rows that were imported and the number of transactions that are unallocated and need to be reviewed.
