Let's say that you have downloaded the OFX file from your bank and saved it in a folder of your choice. The file should have a file name extension of .ofx. We will now walk you through the steps for doing the reconciliation in GnuCash using this downloaded statement:
- In GnuCash, select from the menu File | Import | Import OFX/QFX. The Select an OFX/QFX file to process dialog will open. Find the folder, select the OFX file that you want to import and click Import. The Select Account dialog will open as shown in the following screenshot:
- Under Assets, expand Current Accounts, select Checking Account, and click on OK. The Generic import transaction matcher window will open as shown in the following screenshot:
- Click on the Help button and the context-specific Transaction List Help will open as shown in the following screenshot:
- Familiarize yourself with what the colors mean and what actions are available.
- Close the Transaction List Help window.
- Back in the Generic import transaction matcher, the first four lines are green, indicating that GnuCash found the closest matching entries in the Checking Account. As you can see the check dated 05/29, which didn't clear in the month of May has now cleared and shows up in the June statement.
- However, we have two yellow lines that require our intervention. Double click on the INTEREST line. The Select Account dialog, which we saw earlier, will appear. Open the Income account tree, select the Interest Income account and click OK. That line will turn green.
- Double click on the FEES line and open the Select Account dialog again. We earlier created an account to charge bank fees. Now you can select Bank Fees in the Select Account dialog and click OK to see that line also turn green, as shown in the following screenshot:
- Now that we have green signals all the way, let us grab this opportunity to drive right through. Click on OK to complete the import.
As you can see, electronic reconciliation makes your life easier, especially as your business grows and the number of transactions grows with it. GnuCash does most of the matching automatically, leaving only very few that need your manual intervention. Also, GnuCash does the color coding of transactions so that you can quickly browse through the green ones just to make sure nothing is amiss, and then focus on resolving the yellow ones. Finally, you can decide what to do with the red ones, if any.
Note
OFX (Open Financial eXchange) is a data format standard for exchanging and downloading financial information into financial and accounting packages. It was originally created by Intuit, Checkfree, and Microsoft in 1997. According to Wikipedia, 39 different financial and accounting software packages support OFX.
QFX (Quicken Financial eXchange) is Intuit's proprietary version of the standard OFX financial exchange file format.
Use OFX if you can get it, because it is non-proprietary. However, if all you can get from your bank is QFX, GnuCash can use that too.
Here is a list of frequently asked questions about the reconciliation process and our answers:
- Q: I find myself having to go through and manually tick every single transaction. Doesn't GnuCash have a Check all or similar button to tick all the transactions?
- A: No, there is no Check all button. However, as we said earlier, you can use the space bar to check items. That has the additional effect of also moving the selection to the next line. So, for example, you can just hold the space bar down until all the entries in a column are checked.
- Q: What if I don't have an account and have to create a new one for charging an amount to?
- Thankfully, GnuCash has provided a New Account button in the Select Account dialog just for such occasions. You can click that button to create a new account, which you can then select in the Select Account window.
- Q: I am not able to complete the reconciliation. The Finish button is grayed out. How do I enable it?
- A: The Finish button is grayed out because the ending balance you entered in the Reconcile Information window doesn't match the total of the transactions that you checked off individually. In the bottom right pane of the Reconcile window you will see the Ending Balance, Reconciled Balance and Difference. As you keep checking off items, the Reconciled Balance and the Difference will keep changing. As soon as the Reconciled Balance is the same as the Ending Balance or, in other words, the Difference is zero, the Finish button will become enabled. Take a look at step 10 in the Manual Reconciliation tutorial above.
- Q: Every time I launch the Reconcile window, the Interest Payment window keeps popping up automatically. My account doesn't pay interest. How do I switch this off?
- A: The Interest Payment window has a long button at the bottom called No Auto Interest Payments for this Account. If you click this, the Interest Payment window won't bother you again.
- Q: In my Reconcile Information window the Starting Balance is incorrect. GnuCash doesn't provide a place for me to enter the correct Starting Balance. How do I fix this? How does GnuCash compute this number, anyway?
- A: The Starting Balance comes from your previous reconciliation. You may find that this is incorrect, either because you skipped reconciling for one or more months or you changed or deleted a reconciled transaction. Here is how you can handle this situation. Simply ignore the Starting Balance and try to reconcile to the Ending Balance. Of course, you may have to go back and fix any errors you made, such as editing or deleting a reconciled transaction, perhaps inadvertently.
- Q: I need to charge an expense to multiple accounts. How do I do that?
- A: The Select Account dialog will allow you to select only one account to charge into. It doesn't have a feature to handle multiple splits. If you don't select an account and leave the A column checked, GnuCash will automatically charge the amount to Imbalance-USD. After the reconciliation is complete, you can open the Imbalance-USD and change it to the correct multiple split transaction.
- Q: I accidentally marked a reconciled transaction from y to n. Now I am able to mark it c, but not able to change it back to y. How do I do that?
- A: You can mark individual transactions c for cleared in the account register. However, you can't mark individual transactions y for reconciled in the account register. Marking them y for reconciled is done by the reconciliation process. The unreconciled transaction will show up in the Reconcile window and you can run the reconcile process as often as needed.
- Q: My bank does provide electronic statements, but OFX/QFX is not one of the options. What now?
- A: Even though GnuCash supports the import of QIF, CSV (Comma Separated Values) as well as fixed width formats, these are not suitable for reconciliation. As we saw earlier, 39 different financial and accounting software packages support OFX. As a result, most banks do provide support for this format. If your bank does not, you might first try asking your bank for this. If there is sufficient interest among its customers, your bank may be able to add support for this option. If not, then you may have to resort to manual reconciliation.
- Q: My friend says that he doesn't have to enter transactions by hand. Instead, he imports them using the electronic statements from his bank, thus saving a lot of time and effort. Can I not do the same?
- A: If you just take what the bank statement shows on blind faith then you'll never notice if they mistakenly charge you due to any oversight. The very purpose of reconciliation is to use your accounts to make sure the bank doesn't overcharge you by mistake, as well as to use the bank statement to make sure you didn't miss out on some transactions. If you simply import the bank statement, it will defeat this very purpose of double checking. Also, the imported description may not be to your liking. It may be very brief and ambiguous and in all caps.
Income and expense accounts are not directly reconciled, because there is no statement to check them against. However, to the extent your income and expenses go through your bank or credit card, they are being checked indirectly when you do the bank and credit card reconciliation.
With a cash account, though, you might want to adjust the balance every once in a while, so that your actual cash on hand matches the balance in your cash account. In parallel you might want to investigate why it didn't balance. If you are the one handling the cash, perhaps you are failing to enter some of the transactions. If someone else is doing it, you might want to implement some checks and balances to prevent a recurrence.
The way some people rectify this discrepancy is to enter a transaction in the cash account with the destination account as one of the expense accounts, and enter the withdrawal. This way the current cash on hand matches the GnuCash account balance.