Most online banking websites have options for downloading transactions, for a specified date range and/or since the last such download. The downloaded files are meant for importing into various bookkeeping programs. ACCOUNTS can import transactions downloaded from standard bank accounts (generally chequing or savings accounts) and also from credit card accounts.
Importing downloaded online banking transactions can do one or both of two things: match to existing manually entered transactions (or ones entered as recurring transactions) in ACCOUNTS, or create new transactions in ACCOUNTS based on the imported transactions that didn't match existing ones. So the two purposes for doing that importing would be for a sort of pre-bank reconciliation (for the manually entered transactions) or to do some of your data entry of transactions for you, or a combination of the two.
The downloading options in online banking websites almost always include both Quicken and Intuit QuickBooks. ACCOUNTS can import files downloaded for either of them. Some online banking websites that we are aware of also have options for downloading to Microsoft Money, Sage 50 Accounting, and/or Simply Accounting. ACCOUNTS can also import files downloaded for any of them.
Important: For any one bank account you are doing downloads for, you need to be consistent about which of the acceptable file formats you select to download it as. Otherwise the program may not recognize that the account you want to import the file into has already been linked to that bank or credit card account.
When you install current versions of ACCOUNTS, if the installer observes that you don't have Quicken installed, it will register what is called a file association with the filename extension for Quicken download files, which is ".qfx". What that means is that if you download such a file from the online banking website, and tell your web browser to Open it, ACCOUNTS will automatically start and try to import the file for you.
Similarly, if you don't have QuickBooks Desktop installed, the ACCOUNTS installer will register a file association for the filename extension for QuickBooks download files, which is ".qbo".
The same applies to file associations for files downloaded for Microsoft Money or Sage 50 Accounting, which have a file extension of ".ofx", or for Simply Accounting, which have a file extension of ".aso". File associations will be created for those as well, if you don't already have programs associated with those extensions.
If those file associations aren't created, you can import downloaded online banking transaction files that you have saved to your hard drive, with the Database ⇒ Import Online Banking Download menu option.
Note: Unlike some programs (such as Quicken and QuickBooks), ACCOUNTS will not do the downloading for you! You have to go to the online banking website and select an appropriate download yourself, then when you Open that file in your web browser, if there is an appropriate file association as described above, or Save it and then use Database ⇒ Import Online Banking Download to open it, it will be imported.
Bank Rules
One problem with matching downloaded to manually entered transactions is that the Payee/Description names may not match. Another problem is that downloaded transactions never include the name of an account (usually income or expense) to associate them with.
To solve these problems, you can create what ACCOUNTS calls Bank Rules. They match downloaded payee/description names and/or memos with the one you use for manual entries. They can also help you by assigning counter accounts (such as income or expense accounts) to certain names, and even sales tax codes if you use them. Those additional details are used as part of the process of matching imported transactions to existing ones in the program, and are also used when the program creates new transactions from imported ones that don't match any manually entered ones.
See Bank Rules for full details about this.
How Imported Transactions are Matched to Manually Entered Ones
ACCOUNTS uses a number of pieces of data from the imported transactions, and a number of rules, to match manually entered transactions to imported ones. Of course, it will also match to ones inserted automatically as recurring transactions.
The pieces of data that are relevant to determine the matching are the transaction date, amount, payee/description, memo and reference number (which is most commonly a cheque number). Here is a summary of the rules that are used:
•The manually entered transaction date has to be less than or equal to the imported one, but no more than 2 months prior to the imported one. Matching starts from the latest-dated imported transactions, trying to match them to the best matching latest-dated manually entered transaction.
•The amount has to match, and of course whether it's a payment or a deposit (for bank accounts) or a charge or payment (for credit cards) has to also match!
•The best matches will be if the date is appropriate, the amount matches, there's a reference number that matches, and the payee/description matches. (The exact meaning of saying that the payee/description matches is explained in the Help topic on Bank Rules.)
•After doing those best matches, the program looks for matches on the date, amount, and reference number, ignoring the payee/description.
•After doing those matches, the program looks for matches on the date, amount, and payee/description. (Otherwise possible matches where both the imported and existing transaction have a reference number, and they are different, are never used.)
•The program then optionally looks for matches on only the date and amount. Since that could tend to create incorrect matches, you can turn off that type of matching with a new option in the Maintenance ⇒ Main Options window, the Only match transactions to online banking downloads if more than just the Amount matches checkbox.
This optional matching step defaults to being used unless you choose to turn it off in that window by checking that checkbox, which you might want to do if you observe too many incorrect matches. (This step also never makes matches with mismatched reference numbers, as in the previous point.)
There are a couple of situations where matching cannot work, even though you would think it ought to, both of which involve a different number of imported transactions from the number of manually entered transactions that should match to them.
The first case is when you have manually entered something as two (or more) transactions, but the import file has grouped them together as one transaction. An example might be if you deposited two cheques at the same time, and recorded each one separately in ACCOUNTS, but your bank saw that as one deposit transaction. You would have two choices for the matching there - you could either just ignore that imported transaction, knowing that manually entered transactions matching it are indeed there, or you could edit the manually entered transactions (after using the Manual Match menu option described below to bring up a Register-like window where you can do editing) to merge them together, so they can match.
The second case is when you have one manually entered transaction that the import file has split into two. Again, as long as you are sure the right transactions are there, you could just ignore it, so nothing is matched. Or, you could edit in the Manual Match Register-like window to manually split the transaction into two, matching the two imported ones, and then match them.
Another thing to be aware of is the dates of Recurring Transactions. Because matching only happens if the existing transaction in the register has a date less than or equal to the imported transaction date, you want to be sure that the dates of recurring transactions are the earliest they ever happen. So for instance if a certain recurring payment can vary between the 10th and 13th of a month, depending on when weekends occur etc., be sure to set it up to be inserted on the 10th. Then if an imported transaction is dated on that date or later, it will still match. If you had the recurring transaction inserted on the 13th, and the bank recorded it on the 10th, it would never match in this process.
What Do You See?
The program starts working on an online banking download file after either it is started from that file via a file association with the correct type of file, or you select the file with Database ⇒ Import Online Banking Download. It then reads everything in the file. As long as there are no errors in the process, the next step is to determine which bank or credit card account to import into.
Please note that you can only import only one bank or credit card account from an Online Banking Transaction Download file at a time. Even though some banks provide the opportunity to download transactions from all of your accounts into one single file, please choose to download only one account at a time. Otherwise ACCOUNTS will reject the file.
The first time you import from a given bank or credit card account, the program will show you a list of your bank or credit card accounts that aren't already matched to other account's online banking downloads, and let you pick the appropriate one. Then it remembers that, so you will never be asked about that again. In the unlikely case that you have matched the wrong account in ACCOUNTS to a given bank or credit card's online banking download, you can unlink them by using the Online Banking Info button in the Edit Account window. You can also see details of the match there.
The following window is then displayed for working through the matches, making new matches, and accepting them when you are done:
As you can see each "row" of this window actually contains spaces for two transactions - the Imported one above, and a Matched or New one below. Both sets of fields are almost identical to those in the program's regular Register windows.
You can review each Matched Transaction to make sure you are happy with it.
There will also be partially filled-in New Transactions in the lower area, based either just on the imported values, or on Bank Rules that give conversions of imported payee/description names, and optionally also a counter account and sales tax code. You can make further edits there to complete or correct those transactions.
In many ways, the lower half of each transaction here behave like a regular Register window. As in the Register, you will have to save those changes you make before you can move to another row. You can press Enter to save changes, or if you just click into another row, you will be prompted about saving. In either case, if there are things that would keep that row from being saved, you will receive a detailed message about it.
When you click into an editable field in a row (which are only the ones in the lower half, for the Matched or New Transaction) it becomes highlighted, and the Splits and Actions buttons as shown in the screen shot above appear for that row. The Splits button behaves just as in the regular Register, taking you to a Register Splits window for that transaction.
Other options are the Actions button, which brings up a popup menu (also available by right-clicking in a field), with the following choices: [CHECK NAMES!]
•Manual Match: This brings up a Register-like window, with the imported transaction you are matching to at the top. Below that, it shows all currently unmatched transactions, with a Make Match button that appears on any transaction you click into that has the right dollar amount matching the imported transaction. So you just need to find the correct match, and click that Make Match button to send the match back to the main working window. After a confirmation, it replaces whatever is there with the selected transaction.
For full details, please see the separate help page on the Manual Match Window.
•Unmatch: If you feel an imported transaction has been matched to the wrong manually entered one, choose this menu option to undo that match. This will clear the bottom part of the matched transaction, so that you can manually enter a new one for it or choose another menu option to make a new match.
•Clear: For new or unmatched transactions, this option is available. It does the same thing as Unmatch does for matched transactions.
•Pay Bill: An imported transaction might represent a bill payment, that had been entered into ACCOUNTS as a bill to be paid with the Enter Bills window, but that has not yet been recorded as having been paid, with the Pay Bills window. This menu option will pop up the Pay Bills window, with the knowledge that it is supposed to be a bill for the given Amount of the imported transaction you are working on. If you do pay a bill for that Amount, the associated transaction will be then copied back into this work window as the match, after a confirmation.
•Create Bank Rule: If you have entered a Payee/Description, and possibly also any or all of a memo, counter account and sales tax code, into the Matched area, either manually or by using Manual Match, you can then choose this menu option. It brings up the Bank Rules window, with a potential rule based on the current data added into it. (If there is already a matching rule, it will be selected.) You can edit that rule as desired, then Save it there. If you do create a new rule, when you return to this main window you will be prompted as to whether to apply it further unmatched transactions.
•View/Edit Splits: Same as the Splits button, opens a Register Splits window for this transaction.
•View as Journal Entry: Available only for saved transactions (either Matched, or New ones you have edited and saved): displays the transaction as an uneditable journal entry (debits and credits).
•Insert Memorized Transaction: Allows you to replace the current Matched or New transaction with a memorized transaction, that must match the amount of the imported transaction.
Saving your Changes
Once you are happy with all of the displayed matches and entries, you can click the Save button to attempt to save all of the changes. This will do two things:
•Do a regular save of any unsaved New or edited Matched transactions, and
•Mark those transactions as having been matched to that imported transaction.
A matched transaction that is saved in this way will show a reconciled status, in the field headed "Clr" in a Register window, of "c", unless it was already showing an "R" because it had been reconciled in a regular bank reconciliation. Fields matched with "c", when pulled up as part of a later bank reconciliation, will be assumed to be cleared and thus already show check marks beside them.
Warning About Overlapping Downloads and Transaction Duplication
Each bank assigns a unique ID to each transaction in each account that you download. That unique ID is also saved with the transaction, so it is known that it has been matched to that bank transaction. If you re-import a file from your bank that includes the same unique ID that has already been stored with a transaction in the program, it will be ignored, because you have already dealt with it.
Unfortunately, we have observed that some banks are not always consistent in assigning the same unique ID to the same transaction in different downloads, so it is not entirely reliable to depend on the unique IDs to prevent the program from re-importing the same transaction twice! For such banks, you may need to be very careful to never download overlapping time periods in different downloads. If you do, the program will show you transactions that have already been imported and want to save new transactions for them. If you do that, you will end up with duplication transactions!
To avoid this, for online banking systems that have a download of option of "All Since Last Download" or words to that effect (rather than picking a date range) we recommend to always use that option. That way there should never be any duplicated transactions downloaded, and no chance of the same transaction with different unique IDs being imported twice.
It's important to understand that the "c" reconciled status and unique ID belong to the part of the transaction that is for this specific bank or credit card account you have done the import into! So for instance if you have a transaction that is a transfer of funds between two bank accounts, only the part of it for the bank account you did the import into will show the "c" if you view that transaction in a Register window. If you view the other account in a Register window, you will not see a "c" - unless you also download and import transactions from that other bank account!
In some cases the program will not be able to immediately save all of the imported transactions, because the entries are not complete or not matched correctly. In that case you will get a message, and only the transactions that still need to be saved will remain on the screen. You can take whatever actions you need to take to allow the program to save them, then click Save again.
Once all transactions have been saved, the window will close.
To stop the saving process from being too broken up by warning messages, only limited message boxes will come up to explain why any given row cannot be saved, or to ask you whether issues it detects are OK. Here are the things that can prevent a row from being saved:
•Missing a Date, a Description, an Account, or an amount (either a Payment or a Deposit amount)
•Having the Payment or Deposit amount not matching the imported transaction's corresponding amount
•Having a Reference Number (which is usually a cheque number) that doesn't match the imported transaction's Reference Number (you will be asked what to do about that)
•Having a date that is more than two months earlier than the imported transaction's data (you will be asked what to do about that).
It is also possible to use the Cancel button to give up on the matching in this window, and possibly try it again later with the same file if you wish. Sometimes if there are transactions that you cannot match, as in the examples above where the number of transactions in the register doesn't match the number of imported transactions, the right action is to click Cancel and ignore the remaining ones. Closing the window also does the same thing as the Cancel button.
Undoing Saved Changes
In very unusual circumstances you may decide that you have saved some incorrect matches, and want to eliminate them, so that you can re-try importing the same downloaded file and make more correct matches to existing or new transactions.
You can do this in the normal Register window, with its right-click menu option Unmatch Online Banking Transaction.
This topic was last edited on Aug 16, 2021