OFX stands for Open Financial Exchange. It is an open standard framework used for exchanging financal information between customers and financial instituitions.
OFX is exchanged using an online client-server model. A request is sent from client and response is received from server.
Certain banks have websites that can send a HTTP request to OFX server and receive a response containing financial transactions in SGML or XML formatted file.
This file can be saved to users machine and will have an OFX file extension.
In EOL, we are interested to import bank statements from this OFX file.
All banks in EOL UK will support import using OFX file. Some banks can provide bank statements in this format.
If there are banks that does not provide statement in this format, user can use a conversion tool to convert bank specific statement format to OFX.
OFX supports many types of services like Banking, Bill Payment, Bill Presentment, Investments, Tax download. For this change, we will only support Banking services.
Under banking services, there are many type of information available to download like Bank statement, Credit card, Loans, Amortization.
For this change, we are mainly focussed on importing bank statement transactions and balances.
1) Types of OFX file
There are 2 types of OFX file to import; both need to be supported
· OFXSGML format from OFX version 1.x
Header:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
Body:
<OFX>
<SIGNONMSGSRSV1>
…
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
…
<STMTTRN>
<TRNTYPE>PAYMENT
<DTPOSTED>20050824080000
<TRNAMT>-80.32
<FITID>219378
<CHECKNUM>1044
<NAME>FrogKick Scuba Gear
</STMTTRN>
…
</BANKMSGSRSV1>
</OFX>
The file header is specific to OFX file format.
The starting tag does not end with ending tag.
· XML format from OFX version 2.x
Header:
<?xml version="1.0" encoding="ASCII"?>
<?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
Body:
<OFX>
<SIGNONMSGSRSV1>
…
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
…
<STMTTRN>
<TRNTYPE>POS</TRNTYPE>
<DTPOSTED>20050824080000</DTPOSTED>
<TRNAMT>-80</TRNAMT>
<FITID>219378</FITID>
<NAME>FrogKick Scuba Gear</NAME>
</STMTTRN>
…
</BANKMSGSRSV1>
<CREDITCARDMSGSRSV1>
…
</CREDITCARDMSGSRSV1>
</OFX>
Fully XML compliant
2) Supported tags
Tag |
Description |
Exact Attribute |
<SIGNONMSGSRSV1> |
Authentication service |
Not used |
|
|
|
<BANKMSGSRSV1> |
Bank service |
|
<STMTTRNRS> |
|
|
<TRNUID> |
Unique transaction ID |
Not used |
<STATUS> <CODE>0
<SEVERITY>INFO
</STATUS> |
Status of transaction |
Check status code is 0 – OK If status is error, reject the file |
|
|
|
<STMTRS> |
Statement-response |
|
<CURDEF> |
Default currency for the statement |
Default currency; GLTransactions.Currency |
<BANKACCTFROM> <BANKID>
<ACCTID>
<ACCTTYPE>
</BANKACCTFROM> |
Account-from |
BankID is Sort code
AcctID is Account code
BankID + AcctID is Own bank account; GLTransactions.BankAccount
AcctType is not used {AcctType credit line is used for credit card user story} |
<BANKTRANLIST> |
Statement-transaction-data |
|
<DTSTART> |
Start date for transaction data, date |
Not used |
<DTEND> |
Value that client should send in next <DTSTART> request to ensure that it does not miss any transactions, date |
Not used |
|
|
|
<STMTTRN> |
Opening tag for each statement transaction (0 or more) |
|
<TRNTYPE> |
Transaction type.
This element does not change the effect of the transaction upon the balance (increases and decreases are indicated by the sign of the <TRNAMT>). |
Not used |
<DTPOSTED> |
Date transaction was posted to account, datetime |
GLTransactions.EntryDate, Format is YYYYMMDD |
<TRNAMT> |
Amount of transaction, amount |
Negative is Cash out,
Positive is Cash in,
GLTransactions.AmountDC |
<FITID> |
Transaction ID issued by financial institution.
Used to detect duplicate downloads, FITID |
Possible to store this identification to avoid duplicates |
<CHECKNUM> |
Check (or other reference) number |
{To be used in cheques user story} |
<PAYEEID> |
Payee identifier if available |
Customer / Supplier ID assigned by server, maybe used |
<NAME>
-or-
<PAYEE>
<NAME>
<ADDR1>
<CITY>
<STATE>
<POSTALCODE>
<PHONE> </PAYEE> |
Name of payee or description of transaction Note: Provide NAME or PAYEE
Payee aggregate |
Name or Description; GLTransactions.Description
Name or Description; GLTransactions.Description
|
<EXTDNAME> |
Extended name of payee or description of transaction |
GLTransactions.Notes |
<BANKACCTTO>
<BANKID>
<ACCTID>
<ACCTTYPE>
</BANKACCTTO>
-or-
<CCACCTTO> </CCACCTTO> |
If this was a transfer to an account and the account information is available
|
BankID is Sort code
AcctID is Account code
BankID + AcctID is Own bank account; GLTransactions.AccountBankAccount
{CCAcctTo to be used in credit card user story} |
<MEMO>
|
Extra information (not in <NAME>), MEMO |
GLTransactions.Notes |
<IMAGEDATA> </IMAGEDATA> |
Image data aggregate, up to 2 allowed. |
{To be used in cheques user story} |
<CURRENCY> </CURRENCY> -or- <ORIGCURRENCY> </ORIGCURRENCY> |
Currency, if different from CURDEF |
If available, this tag is used in place of default currency as transaction currency; GLTransactions.Currency
|
</STMTTRN> |
End tag for each statement transaction |
|
|
|
|
</BANKTRANLIST> |
|
|
<LEDGERBAL>
<BALAMT>
<DTASOF>
</LEDGERBAL> |
Ledger balance
Ledger balance amount
Balance date |
BalAmt is closing balance; GLTransactions.ClosingBalanceFC
Opening balance is calculated:
Closing balance + Total positive transaction + Total negative transaction |
<AVAILBAL>
<BALLIST>
<MKTGINFO> |
Available balance
Other balance
Marketing info |
Not used |
</STMTRS> |
|
|
|
|
|
</STMTTRNRS> |
|
|
</BANKMSGSRSV1> |
|
|
|
|
|
<CREDITCARDMSGSRSV1> |
Credit card service |
{To be done with credit card user story} |
3) Example of SGML
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<!-- Not used -->
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
<STMTTRNRS>
<TRNUID>0
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<STMTRS>
<CURDEF>USD
<BANKACCTFROM>
<BANKID>000000123
<ACCTID>123456
<ACCTTYPE>CHECKING
</BANKACCTFROM>
<BANKTRANLIST>
<DTSTART>20050801
<DTEND>20050831165056.000[-8:PST]
<STMTTRN>
<TRNTYPE>PAYMENT
<DTPOSTED>20050824080000
<TRNAMT>-80.32
<FITID>219378
<CHECKNUM>1044
<NAME>FrogKick Scuba Gear
</STMTTRN>
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>2156.56
<DTASOF>20050831165056
</LEDGERBAL>
</STMTRS>
</STMTTRNRS>
<STMTTRNRS>
<TRNUID>0
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<STMTRS>
<CURDEF>USD
<BANKACCTFROM>
<BANKID>000000123
<ACCTID>654321
<ACCTTYPE>SAVINGS
</BANKACCTFROM>
<BANKTRANLIST>
<DTSTART>20050801
<DTEND>20050831165056.000[-8:PST]
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>3452.00
<DTASOF>20050831165056
</LEDGERBAL>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
</OFX>
4) Example of XML
<?xml version="1.0" encoding="ASCII"?>
<?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
<OFX>
<SIGNONMSGSRSV1>
<!-- Not used -->
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
<STMTTRNRS>
<TRNUID>0</TRNUID>
<STATUS>
<CODE>0</CODE>
<SEVERITY>INFO</SEVERITY>
</STATUS>
<STMTRS>
<CURDEF>USD</CURDEF>
<BANKACCTFROM>
<BANKID>000000123</BANKID>
<ACCTID>123456</ACCTID>
<ACCTTYPE>CHECKING</ACCTTYPE>
</BANKACCTFROM>
<BANKTRANLIST>
<DTSTART>20050801</DTSTART>
<DTEND>20050831165153.000[-8:PST]</DTEND>
<STMTTRN>
<TRNTYPE>POS</TRNTYPE>
<DTPOSTED>20050824080000</DTPOSTED>
<TRNAMT>-80</TRNAMT>
<FITID>219378</FITID>
<NAME>FrogKick Scuba Gear</NAME>
</STMTTRN>
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>2156.56</BALAMT>
<DTASOF>20050831165153</DTASOF>
</LEDGERBAL>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
<CREDITCARDMSGSRSV1>
<!-- Not used -->
</CREDITCARDMSGSRSV1>
</OFX>
5) Validation
· <CURDEF>, <BANKACCTFROM>, <DTPOSTED>, <TRNAMT> is mandatory
· Either <NAME> or <PAYEE> must be available
· XML format is fully compliant
· SGML header for SGML format
· XML header for XML format
6) Allocation
- The other half of imported bank entries will be allocated to G/L Account: Unallocated
7) Unsupported scenario
· Correction for previously downloaded transactions are not supported
· Investments, Loans, Amortization transactions are not supported
· Connection to OFX server with authentication and request/response communication is not supported