OFX for USAA via Ruby
My wife and I have been through roughly 10-15 different budget/financial tracking systems. We started with every penny in MS Money, used several different spreadsheets, spent several years in Mint and have pretty much dropped all of that for a top-down strategy that has us budgeting savings, non-discretionary spending, and a rainy day buffer and arriving at a fixed weekly budget for groceries, clothes, snacks, eating out and random household supplies. We use a debit card for this, and transfer the allotted amount every Thursday into the daily spending account. The problem is that we started pushing money into the account whenever it runs low, and we end up losing our focus and even the ability to track how much we spend in a given week. In an audit of last year’s spending, it was surprising to see that we were routinely 100{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} over our budget when we looked at other spending sources.
Since I code web applications, I decided to play with bringing in some of the data we create, both household and financial to ultimately create a personal dashboard for our family. In doing so, we aren’t locked into any one system and we can create something custom that works for us. This way, we can track our fitness, finances, journal and home systems all in one place and own the data and experience. One lesson learned is that our tracking systems need to be on autopilot as our different interests surge. A fragile system doesn’t work. Our needs will vary, but we want any tracking system to be able to produce a report on request.
While fun and useful, this takes familiarity with some new protocols (OXF for finance and LUUP for home automation). On a plane flight to Las Vegas, I was able to get OFX to successfully connect to USAA. First I had to set a module with USAA’s specifics:
With this in place, I can generate a valid OFX request:
This request passes all the assertions designed to test for a valid signon response:
def verify_usaa_signon_response(response_document)
signon_message = response_document.message_sets[0]
assert signon_message.kind_of?(OFX::SignonMessageSet)
assert_equal(1, signon_message.responses.length)
signon_response = signon_message.responses[0]
assert signon_response.kind_of?(OFX::SignonResponse)
assert_not_equal(nil, signon_response.status)
assert signon_response.status.kind_of?(OFX::Information)
assert signon_response.status.kind_of?(OFX::Success)
assert_equal(0, signon_response.status.code)
assert_equal(:information, signon_response.status.severity)
assert_not_equal(nil, signon_response.status.message)
assert_not_equal(nil, signon_response.date)
assert_equal(nil, signon_response.user_key)
assert_equal('ENG', signon_response.language)
#assert_not_equal(nil, signon_response.date_of_last_profile_update)
#assert_not_equal(nil, signon_response.date_of_last_account_update)
assert_not_equal(nil, signon_response.financial_institution_identification)
assert_equal('USAA', signon_response.financial_institution_identification.organization)
assert_equal('24591', signon_response.financial_institution_identification.financial_institution_identifier)
assert_equal(nil, signon_response.session_cookie)
end
One of the difficult parts was to determine the required length of my account number in the absence of documentation. It took some experimentation to find out that USAA wants exactly nine digits for the username (member number) and ten digits for an account number. Instead of making code that robustly input padded zeros (through sprintf or similiar), I just changed the input values.
I also noticed that USAA did have
<LEDGERBAL><BALAMT>290.51<DTASOF>20140211120000</LEDGERBAL></STMTRS>
, but did not have the available balance fields that the gem expected. In any case, I can now get transactions and full access to my bank programmatically, which is pretty cool.
Links:
- GnuCash specs. This is the most helpful link. It contains the login details, API url and basic settings.
- ofxblog Similar details as the GnuCash specs
- ofx-parser. Awesome ruby gem.
- big nerd ranch. Just a writeup complaining about how bad financial APIs are.
- someone trying in php. Good to see the OFX standard in action.
- cashflow app — operates off downloaded statement. good to see ofx parsing in action.
- parsing details. Documentation for the OFX gem.
