To import transactions in personal financial software, banks provide files in a variety of format such as:
This file format is used to import data in spreadsheets and databases. Each lines is a list of fields separated by a comma. If a field contains one or more commas, the field is between two " characters (Ie: "Hello, this is a sample"). If the field contains the character ", it must be doubled ("Character"" must be doubled").
Example:
Name,Age,Comment
Andrew,23,"Comma, is a special character"
John,35,"This character "" is a special character"
The back draw of this format is that no information is given on the meaning of the fields. To use such a file we need to know what contains each column. For bank transaction columns may be Date/Amount/Payee/Memo or Amount/Payee/Date or any other combination... We need more information to process such a file in a program.
This file format is derived from CSV format, the separation character is a tabulation character. The first line of the file contains the title of the column. Each line contains the same number of fields (the number of columns). A field can't contain a tabulation character.
Example: (<TAB> stand for the tabulation character)
Name<TAB>Age<TAB>Comment
AndrewTAB>23<TAB>Comma, is not a special character
John<TAB>35<TAB>Charactere " is not a special character
This file format was defined in the middle of 1990 for the software Quicken by Intuit. It is a very simple format and the meaning of each field is defined, and a header give the type of account for the transactions in the file :
| Header | Meaning |
|---|---|
| !Type:Bank | Bank transaction |
| !Type:CCard | Credit card |
| ... | ... |
Follow, for each transaction, a line for each field, the first character of the line define the meaning of the field:
| Character | Field |
|---|---|
| D | Date |
| T | Amount |
| N | Number |
| P | Name |
| M | Comment |
| A | Adress |
| L | Category |
| S | Category (partial) |
| E | Comment (partial) |
| $ | Amount (partial) |
| ^ | Record end |
This file can be processed by a program, each data is identified. One main information is missing : account identification. This will be corrected with OFC and OFX file formats.
Example:
!Type:Bank
D10/11/04
T-50.00
NVIRT PCREDIT LYONNAIS
PVIR.PERMANENT
^
D09/11/04
T-17.00
NPrelvmt
PNC NUMERICABLE
^
Ce format a été créé par Microsoft, mais Microsoft ne réclame aucun droit pour son utilisation. Il ne s'agit pas seulement d'un format de fichier mais également d'un protocole conçu pour que Microsoft Money puisse interroger directement les serveurs bancaires qui adopteraient ce standard. Dans ce qui suit nous nous limitons à la description des fichiers.
Les fichiers OFC sont des fichiers utilisant des balises, comme le HTML utilisé pour les pages Web. Un fichier OFC débute avec une balise <OFC> et se termine avec une balise de fin </OFC>. La structure générale du fichier est la suivante :
| Tag | Meaning |
|---|---|
| <OFC> | Begin of file |
| <DTD>n | Document Type Definition version |
| <CPAGE> | Page code |
| ... | data |
| </OFC> | End of file |
Les mouvements bancaires sont entre une balise <ACCTSTMT> et une balise </ACCTSTMT>. Les couples de balise <xxxx>...</xxxx> définissent des agrégats (esemble de données), les données simples sont représentées par <xxxx>Information, sans balise de cloture. Les mouvements bancaires sont dans la structure suivante (engras les données obligatoires) :
| Tag | Signification |
|---|---|
| <ACCTSTMT> | Account statment start |
| <ACCTFROM> | Account from |
| <BANKID> | Bank Id |
| <BRANCHID> | Branch Id |
| <ACCTID> | Account Id |
| <ACCTTYPE> | Account type |
| </ACCTFROM> | |
| <STMTTRS> | Start transactions |
| <DTSTART> | Start date |
| <DTEND> | End date |
| <LEDGER> | Account ledger |
| <STMTTRN> | Tansaction start |
| <TRNTYPE> | Transaction type |
| <DTPOSTED> | Date |
| <TRNAMT> | Amount |
| <FITID> | Transaction Id |
| <CLTID> | Client transaction Id |
| <SRVRTID> | Server transaction Id |
| <CHKNUM> | Check number |
| <SIC> | Standard Industrial Code |
| <PAYEEID> | Payee Id |
| <NAME> | (NAME et PAYEE sont exclusifs) |
| <PAYEE> | (NAME et PAYEE sont exclusifs) |
| <ACCTTO> | Account to |
| <MEMO> | Memo |
| </STMTTRN> | |
| </STMTRS> | |
| </ACCTSTMT> |
Compare with CSV, TSV or QIF, this format is more significant. The account for each group of transaction is defined. The field FITID is a key field for ech transaction, it's possible to cope with doubled transactions.
This format is an evolution of OFC format, specification can be found on this web site : http://www.ofx.org. This file format is an international open format. Microsoft don't support OFC files in Money 2005, Intuit will not support QIF anymore, the two editors will support OFX file format.
The version 2 of this format is an XML version. XML files are tagged files with a very strict syntaxe (compare to HTTP) and can be easyly processed in programs.