Question
My bank statement imports no longer work correctly. What’s the problem?
Answer
The fact that you have successfully imported electronic statements before indicates that your problem is not with the Budgets Get Real program.
One of the following is most likely the cause:
1. You have somehow changed the import configuration template
2. You have changed the “Can write cheques” option (Bank and Loan Account types only) for the Account
3. Your financial institution has changed its export standard and this is preventing import.
It is not uncommon for financial institutions to occasionally change their export standards. Smaller institutions get amalgamated into larger ones and adopt the larger ones processing standards, or sometimes extra information is added to the download file.
We have seen cases where the web site has been updated and changed to provide both statement “Download” (usually as PDF files) and statement “Export” which may include CSV, QIF or OFX download. It may be that your download links have changed.
The following procedures will help you to eliminate the cause. Which check you chose to run first is up to you, based on how you have used the program since the last known, good import.
Check 1: Determine if the export files have changed
Files are best checked using a text editor like WordPad or NotePad on Windows and BBEdit or TextWrangler (both freeware) on Mac OS X. BBedit and TextWrangler are particularly useful since you can use them to see any “invisible” characters that may be effecting import.
- Do not use a word processing program like Word. These do not accurately show all characters.
- Do not open the files in a spreadsheet like Excel. These programs will automatically convert date and amount columns and you cannot be certain what is the raw data.
Open your last known, good import file and the file with which you are having a problem with into your text editor. You are looking for any change in the file structure like one of the following:
- The file format has changed.
- Extra decriptions (like account details) have been added or removed at the start of the file
- A header describing the columns has been added or removed
- The column order has been changed
- The amount column has changed from a single column to two columns (one for debits and the other for credits)
- The date format has changed e.g. “12-Dec-07” has gone to “12 Dec 07”. Note that quoting of line items is not usually an issue since Budgets GetReal automatically removes consistently added quotes
For the file which is causing problems it is usual that the start of the file is to the old standard and the remainder of the file is of the new standard. If this is the case you need to split the file into two components and save these as two different files for separate import
1. Ensure you keep a copy of the downloaded file, pre-splitting
2. Find the point in the file where the structure changes i.e. the “transition date”. Save all transactions and headers up to the transition date to another file that you clearly label.
3. What you do with the transactions (including and after the transition date) depends a bit on the changes made. Whatever happens, you will delete the transactions up to the transition date. You may need to include the original header or a new header into the file saved for import after the transition date.
4. Backup your database
5. Import the transactions up to the transition date. These should import correctly.
6. Make a backup copy of your import configuration and save this with your statement downloads. To do this Select Set & Maintain > Configuration from the main side menu. Select the correct Institution and then “Export Template”.
7. Import the transactions after the transition date. You will need to update your template for this. Do not update your template until you have performed steps 4, 5 and 6 above. It is possible that a new managed template is available which you can download. Otherwise, you will need to manually update your existing template. Click here for details
8. If the transactions do not import correctly in Step 7 then review your template and then your import data. If the transactions do import, carefully check each entry in the Transaction Register. Pay particular attention to whether dates, amounts, cheques and excluded transactions have been correctly handled. If there is an anomaly then select “Clear Statement” to remove the transactions just added, review your template and data and try again.
Please email us at contact@budgetsgetreal.com and let know about the changes you have found. We can then publish this data to help other Budgets GetReal users
Check 2: Determine if your import configuration has changed
If your import configuration has somehow changed then you will not be able to import a file that previously imported. You can use this to determine if your template has changed. This may also check for a change in the “Can write cheques” option.
You must strictly follow the following procedures:
1. Backup your database. Select Miscellaneous > Backup dBase from the main side menu.
2. You cannot re-import the file into the same account since all duplicate imports are rejected. You will therefore need to create a new Account for this test.
Create a new Account with the same Account type and same Institution. This new account will inherit the existing import configuration. You don’t need to worry about balance details – only ensure the setting of the “Can write cheques” option is correct (Bank and Loan Account types only).
3. Import the known good file to the new account. If it imports correctly you know that the import configuration is OK. If it does not import correctly you have somehow changed this template.
4. If the file imports correctly check the imported transactions in the Register. They should appear correct and many will have automatically categorised. Select “Clear Statement” to remove these transactions.
5. Delete the temporary account used for this test.
6. Proceed and run Check 1 to determine if the export files have changed
