How to import your bank statement from MCB into GnuCash
June 18, 2019
GnuCash is a great open source program that you can download for free to do double entry bookkeeping with on your PC or Laptop. It’s fairly easy to use and I also like how easy it is to create various accounts and sub accounts for various categories of your assets, incomes and expenditures. Some basic accounting knowledge will be a plus. Else you can easily get into it by following some tutorials. The GnuCash community is also very helpful, they are accessible via an online forum.
In this article I will write about how you can import your bank statements from MCB (Mauritius Commercial Bank) into GnuCash. This tutorial is meant more for those that already use GnuCash. It is not an introduction into how to use GnuCash from scratch. There are some good resources out there for that on the GnuCash Wiki and forum to begin with. Am also writing this article because in the past I never considered the option of doing a bank statement import and used to do it manually. The copy and paste method, which is quite time consuming!
Doing an import of your transactions is very handy, especially if you are running a business or if you have a lot of entries. The import tool in GnuCash allows you to verify first all the entries that you will import, in this way you can exclude some entries such as the sales of stocks on the stock exchange of Mauritius for example which is better done manually.
When you are logged in at MCB online banking, just click onto your account and view your current transactions. You can also select different dates for different periods.
Then scroll to the bottom where you have an option to export these entries. For GnuCash, download them in Excel Format and save the file on your desktop.
In case you bank with State Bank Mauritius, HSBC Mauritius, Barclays Mauritius, ABC Corporation, Bank One, etc. if they offer you to export your transactions in the OFX, QIF or Excel format, you should be able to import these entries. I don’t like the OFX and QIF format because when you do an import into GnuCash, a lot of details get truncated. I will use the Excel .CSV format so that I can do some edits beforehand I do the import. You can also import your statement entries with OFX or QIF and then manually copy paste the description details of each transaction from your bank statement. But I think that makes more work and defeats the purpose.
Open the .CSV file in Excel. You’ll need to clean up that file a bit.
1.a. Delete rows 1-25 including the row where it says Transaction Date, Value Date, Reference etc. and the blank row below it. Like in this screenshot.
1.b. Remove the additional date column as it’s not required.
Edit the date column. Else the GnuCash file importer will have difficulty reading your dates column. When I do an import from MCB, my dates are like this format in the downloaded .CSV file:
17-Jun-2019
the GnuCash reader doesn’t like that. We’ll change it into 17/06/2019. You can do that quite quick with the Find & Replace tool in LibreOffice Calc which I use to edit spreadsheet files (it’s free and open source).
There is one more unnecessary column in the MCB .CSV file that I will delete. It is the balance column, the last column next to the deposit column. Then save the file and close it.
2. Now import the file in GnuCash. Go to import transactions from .CSV
3. An assistant will open to guide you through. Read what it says and click on Next.
4. Select your .CSV file and press on OK. You’ll see that the Next button is not activated in that window. Only OK works.
5. In the screen that follows, select your account into which you want to import these transactions. It is a field at the top right corner.
Encoding you leave on Unicode (UTF-8)
Date format you adapt depending on how your dates are in your CSV file. The MCB format is day/month/year. So we will set the Date Format on d-m-y
Then you will see above your transactions those fields that say None. You must do the following settings for each column:
Date | Num | Description | Withdrawal | Deposit
Then go on Next.
6. The window will proceed to the Transaction Information page where it will give you some details. Please read it.
7. After you pressed Next, you will be now at the Match Transaction page. Here you will see entries which have different background colours and when you scroll to the right you will see 3 columns which say A | U+R | R and then a fourth column for info. Just leave all entries ticked in column A. You can also double click on each entry and select their accounts or do that after you have done the import. Click on Apply.
If you want to know more about what the colour backgrounds mean you can read the following which I am quoting:
6.15.5. Import Matcher
Reference: https://www.gnucash.org/docs/v3/C/gnucash-help/trans-import.html
The Import Matcher uses a Bayesian approach to assign a destination account, if it is not specified in the imported data, to each imported transaction based on the previous import history of the import account. It also attempts to match the transactions being imported to any existing transactions based on the date and the description fields.
Transaction rows which match existing transactions already in the import account are flagged not to be imported. They will have a light green background and the A and U+R checkboxes will be unchecked and the R checkbox will be checked. To override and import the transaction, check the A checkbox. The U and R boxes will be unchecked automatically. The reliability of the match is indicated by a bar display in the Info column. If a destination account for the second split is assigned by the matcher if will be appended to the info column.
Transaction rows which do not match existing transactions in the import account, for which an assignment of a destination account cannot be made on the basis of the previous import history to the account, will be displayed with an orange-yellow background and the A box will be checked and U+R and R unchecked. A destination account must be specified for these transactions.”
The importer tool will inform you that it has successfully imported the transactions. Go to the account where you imported the entries and adjust as necessary. For most entries you will need to set their accounts. Then also check if the final balance matches with your bank statement and then you can reconcile everything. This is one way to do it.
You can also experiment, play around with the functions and maybe you have a different scenario or method of doing this. Please leave me a comment below if this article was helpful to you and share with us if you have a better method or if you found any flaws in my methodology.
Great info and easy-to-follow guide. Thanks for sharing!