What format should I choose and what should my statement file look like?
This questions sometimes arises where you may have downloaded an electronic file from your financial institution and don’t seem to be able to import it to Budgets GetReal.
To successfully import a file three questions must be answered:
1. What file format should I select to use.
2. Have I downloaded the correct type of file
3. Do I have the right import configuration for the type of file downloaded.
The answer to the first two questions is discussed in in the following topic. This question of how to correctly configuration your program for import (not needed for OFX formats) is clearly identified in the following documents:
Configuration Wizard
Configuration Editor
Creating a Template - Example 1
Creating a Template - Example 2
Creating a Template - Example 3
Creating a Template - Example 4
How to open and view your files
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 and may add unwanted characters to your file if you inadvertently save it. These extra characters may then prevent you from successfully importing the file.
- 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.
CSV format. Often called Spreadsheet or Excel format
This is the simplest format of electronic statement and is the most universally available. The basic format consists on a number of lines each line representing a transaction or some form of description. The usual implementation has a “header” of one or more lines at the start of the file describing the order of items within the transaction lines. The inclusion of the header is optional.
The items in each transaction line are in a fixed order and the items are “delimited” by either a comma or a tab character. The most usual form uses a comma - hence the term CSV file (i.e. “comma separated value”). Where commas are used as delimiters this may conflict with commas used within item descriptions. For this reason individual transaction items may be optionally enclosed in quotes. Budgets GetReal automatically removes all such quotes that are consistently applied.
The following is an example of a CSV file (account details shown xxxx).
The file is one of the more simple examples. There is a 2 line header (in this case it does not indicate what the transaction items are). The transaction lines are delimited with commas. The first item is a date, the second a description, the third is the transaction amount (debits shown negative) and the forth item is the account balance.
In most cases CSV files are your best alternative where properly maintained OFX files are not available. They are far easier to read and understand than QIF or OFX formats. Since financial institutions output the information in the format you want there is usually no change in the quality of information available between the formats (although CSV may sometimes display longer descriptions).
The term “Excel” or “Excel Spreadsheet” format should be carefully examined. Most time this is used to mean a CSV file. On some occasions, however this may mean an .xls type file which contains a mess of binary characters and is not humanly intelligible in a text editor.
Budgets GetReal will not directly import .xls type files.
However if Excel files are all you have, you can always open the file in Excel (or other spreadsheet program) and “Save As” a “CSV (Comma delimited)” or “Text (Tab delimited)” format. You can then import the saved file into Budgets GetReal. Note that you will not be able to save .xls files to QIF or OFX format.
QIF format
This is the least preferred option to use. It sometimes provides less information than CSV files and shares the same limitations in regard to identifying the uniqueness of each transaction. The following illustrates the first two transactions of a Banking type QIF file.
The first line defines the type of file. Then we have the transactions in groups of 4 lines (typically) each group ending with a line and single ^ character. The first letter on each transaction line indicates its type - D represents date , M represent a memo or description, T represents the transaction amount and L represents the transaction type.
OFX format.
This is an XML based file with a non XML header.The file format is publically defined and openly available. Manually reading a file is tedious and difficult. The following shows the typical first lines and a typical transaction (identifyable as it is enclosed within tags).
What makes OFX attractive is that each transaction has a unique identifier for each transaction (identified by the tag). This provides the primary mechanism for ensuring duplicate transactions are not imported. In addition the file incorporates a rigorous definition of the account and institution details - so each file can be unmistakably identified. These features do not apply to CSV or QIF files.
The other advantage is because OFX is a rigorously defined standard there is no need to define templates to configure the import process. This all depends on the financial institution outputing their statements strictly in accordance with the standard.
OFX format is common in the USA and Canada. In this case there are two variants - one which allows you to automatically connect to the institution and automatically download transactions. Only a minor percentage of institutions support automatic download. The majority of institutions support manually connecting to your bank, and downloading the statement in OFX format. Budgets GetReal fully supports the manual importation of OFX files.
In Australia some institutions nominally provide “OFX” format. In some cases this is not properly implemented - the most obvious indication of this is that the FITID tags are not unique and often vary depending on the date range for which the statement is exported. Such implemenations are very dangerous since it becomes impossible to detect duplicate transactions. In this case CSV or QIF is a safer option.
Budgets GetReal incorporates a utility to profile OFX files and identify some such abnormalities.
So what format should I choose
For the United States, Canada and other countries where properly implemented OFX statements are available then OFX is clearly the best option.
Where properly implemented OFX statements are not available the next best choice is CSV. This is considered preferable to QIF format because it is easier to read, and often contains more usable information. CSV is a more universal standard compatible with most spreadsheet programs.
