Some information about using OFX for obtaining financial data.

2017-01-09

programming

An Open Standard for Exchanging Financial Data

One side project that I have had in mind for a few months is an app to help manage my finances. There are already plenty of applications out there to get the job done - I just want to make my own because it sounds like a fun project.

As I began exploring the challenge of how to monitor my bank accounts and pull in things like transaction data, I felt like I hit a road block because I could not find a way to obtain the information without having to pay companies like Yodlee, MX, and Plaid to use their services and API’s.

I recently discovered Open Financial Exchange (OFX), which is an open standard for exchanging financial data. Though it’s been around since 1997 and is being used by over 7,000 financial institutions, it seems like a somewhat underground and sparsely documented way of interacting with your financial data. From my experience with OFX so far, it’s not the sort of thing you can just Google what you want to know. So, I hope to share some useful information about what I have learned.

Understand These Basics First

The best documentation that I have found about OFX is the official specification PDF document (found here.) It’s like 691 pages long and is quite dense, but provides lots of essential information. Pretty much all of what I will be writing here comes from the OFX spec. document.

1. Versions

One of the first things to realize is that there are several different versions of the OFX standard. I will be talking about Version 2.0+ throughout this post. One great advantage that 2.0 changes about the standard is XML compliance.

Before 2.0, the data was not quite XML, making it more difficult to consume. With XML compliant responses from servers, it’s very easy to use a package or library from your favorite language to consume the data.

2. Structure and File Format

The documentation reads that a financial institution’s OFX server may return any standard HTTP response. As for the clients, they should expect to handle HTTP 200, HTTP 400’s, and HTTP 500’s.

The content type of an HTTP request to an OFX server should be application/x-ofx and content-length should also be included.

The body of an OFX request starts with a normal XML declaration, followed by the OFX declaration.

<?xml version="1.0" encoding="UTF-8"?>
<?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>

These attributes should always be present in the OFX declaration (use "NONE" if the attribute is not needed.)

• OFXHEADER - Version number of the OFX declaration.
• VERSION - Version number of the OFX data block to come.
• SECURITY - The type of application-level security.
• OLDFILEUID - Unique identifier of the request file.
• NEWFILEUID - Unique identifier of the request file.

For the body of the OFX request, here’s a quick explanation of some of the tags. One thing to understand about OFX is that everything is either an aggregate or an element. Aggregate tags contain one or more element tags. An element tag contains data.

Here’s an example of what requesting some credit card transactions might look like:

<?xml version="1.0" encoding="UTF-8"?>
<?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
<OFX>
<SIGNONMSGSRQV1>
<SONRQ>
<DTCLIENT>20170101</DTCLIENT>
<USERID>mybankingid</USERID>
<USERPASS>mypassword</USERPASS>
<LANGUAGE>ENG</LANGUAGE>
<FI>
<ORG>Banking Organization</ORG>
<FID>1111</FID>
</FI>
<APPID>QWIN</APPID>
<APPVER>2300</APPVER>
</SONRQ>
</SIGNONMSGSRQV1>
<CREDITCARDMSGSRQV1>
<CCSTMTTRNRQ>
<TRNUID>fae1f4e9-8012-4441-a681-eb36a357f5a5</TRNUID>
<CLTCOOKIE>4</CLTCOOKIE>
<CCSTMTRQ>
<CCACCTFROM>
<ACCTID>1111222233334444</ACCTID>
</CCACCTFROM>
<INCTRAN>
<DTSTART>20161228</DTSTART>
<INCLUDE>Y</INCLUDE>
</INCTRAN>
</CCSTMTRQ>
</CCSTMTTRNRQ>
</CREDITCARDMSGSRQV1>
</OFX>

• APPID - An id of the client application. This is usually a short string of characters and is kind of interesting. Most apps will just use the app id of Quickbooks, or Quicken (QWIN – I’m not sure which one that is for.) This is called ghosting… I am not sure how one obtains their very own APPID.
• APPVER - The version of the client application.
• FI - This is an aggregate that contains details about the financial institution you’re trying to talk to. ORG and FID elements are included in this. You can find these details here.
• TRNUID - A unique value set by the client that can be used to differentiate responses from the server. The server will send requests with the same value.
• ACCTID - The account number.
• CCACCTFROM - Aggregate used to identify credit card accounts.
• INCLUDE - This is a boolean ("Y" or "N") data element tag that indicates whether to include transactions in the statement download when part of the INCTRAN aggregate tag. There are other aggregates that INCLUDE can be part of, so it’s meaning changes based on which aggregate it’s under. You can look at the OFX spec. document to see other examples.
• DTSTART - The starting date of request, formatted like this: datetime.now().strftime('%Y%m%d%H%m%S')

There is much more you can do with OFX. I plan on posting more information soon.