One moment please...
 

 

Introduction

 

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.


 

 

Work Requirements

 

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

 


Attachments
BankStatementOFX.docx 48.5 KB View Download
Sample1.OFX 2.4 KB Download
Sample2.OFX 2.4 KB Download
Sample3.OFX 1.7 KB Download
Sample4.OFX 2.0 KB Download