What is it?

ofxgen is a library that can generate OFX files suitable for import into Quicken. It includes an application that converts from a CSV-formatted file obtained from a less than helpful financial institution into OFX.

The less than helpful FI is BenefitStreet; they're my 401(k) administrator and I'm sick of entering their transaction data into Quicken by hand.

ofxgen is distributed under the terms of the GPL. This script works for me and I've put a fair amount of effort into testing it against Quicken 2005 for Windows. However, I CANNOT BE CERTAIN THIS SCRIPT WON'T ACCIDENTALLY SEND YOUR CAT TO MARS. Quicken isn't friendly to develop against and I can be certain it's going to do something crazy because your data is not like mine. TAKE BACKUPS OF ALL YOUR DATA FILES BEFORE USING THIS PROGRAM.


Download and Installation


  1. Get ruby 1.8. I've been using ruby 1.8.5, but you'll probably have luck with most any 1.8.
  2. Download the ofxgen gem.
  3. sudo gem install ofxgen-1.0.gem.

BenefitStreet Usage

Setting this up takes a little work in Quicken before everything will go completely smoothly. Once you take these steps, you should be able to Accept All to the transaction downloads generated by benefitstreet and spend more time doing what you love.

One-time Setup

Find an INTU.BID

The kind folk at Intuit have locked Quicken down to only work with financial institutions (FI) that have paid to be on an Intuit-maintained list. What you need to do is find an institution on Intuit's list with which you will never do real business. You'll use this FI as the source of your transactions.

To do this, open C:\Documents and Settings\All Users\Application Data\Intuit\Quicken\Inet\Common\Localweb\Banklist\fidir.txt on the Windows version of Quicken. This file contains a line for each FI with some details for the FI along with a set of features the FI has paid to have Quicken allow.

Locate an FI that has INVESTMENT&WEBONLY permissions. Pick up the five-digit number in the first column. This is your fake FI which we'll supply to Quicken via INTU.BID.

For what it's worth, I'm using "03079 City National Bank of CA-Portfolio".

Create a .benefitstreet Configuration

Next you'll need to create a small configuration file in your home directory that describes the configuration of the benefitstreet converter. You can actually specify these things on the command-line if you prefer, but I find this much easier.

There are three things you want to specify:

Here's my $HOME/.benefitstreet file, in its entirety:

acct_id = 123-45-7000
deferral_payee = TK 401(k)

# City National Bank of CA-Portfolio
intu_bid = 03079

Take a Backup of your Quicken Data

I'm not kidding about this. Doing what this script is about to do is likely to be highly unsupported by Intuit. Hell, you're probably going to void the warranty on the whole computer...

Create a 401(k) Account in Quicken

You may have already done this, but you'll need to create a new investment account in Quicken to track your 401(k) account. For Quicken 2005, this goes like:

  1. Choose Investing > Investing Accounts > Add Account.
  2. Choose This account is held at the following institution, enter "Benefit Street" (without quotes).
  3. Choose None of these. Use the following name.
  4. Choose 401(k) or 403(b).
  5. Name the account whatever you like. I used "TK 401(k)".

From here the wizard asks some more questions about your employer, the date of the opening balance and the amount of the opening balance. I used my start date and $0.

Create a Second 401(k) Account in Quicken

Our payroll is finicky and doesn't always transfer funds to the 401(k) administrator the same day as paychecks are cut. Occasionally they'll batch up several deferrals into a single deposit. In effect, I have another account with my employer that acts as a holding space until they feel like paying the 401(k) administrator.

In the past, when I had my paychecks depositing directly into my TK 401(k) account above, it was very difficult to reconcile 401(k) statements against paycheck stubs. My solution to this problem was to model this holding space with a separate account.

I advise you to do this too--create a second 401(k) account using the steps above. I called this one "TK 401(k) Paycheck".

This holding account should be at or near zero nearly all the time. When you're done importing transactions this will be a good way to check that all your paycheck deferrals have been properly accounted for.

NOTE: I haven't tried using the benefitstreet script on any setup other than one that includes this kind of holding account.

Setup Paychecks

Setup your paychecks to Contribute to the Holding 401(k) Account. If you already have paychecks you've entered into Quicken, you may need to Find/Replace the category on your 401(k) contributions to your new holding account.

At this point the holding account should have a hefty cash balance.

Create Memorized Payees

Whenever you make a deferral, this shows up in the BenefitStreet CSVs with a memo of "Pre-tax Deferral" associated with each fund purchase. Whenever the firm makes a contribution on your behalf (a matching contribution), this shows up with a memo of "Base Contribution".

Add memorized payees that correspond to the deferral_payee, a space and each of these two strings. Note that total length of the payee name cannot exceed 32 characters.

When adding these memorized payees set the category on them to be the holding 401(k) account. This sets things up such that funds will automatically transfer out of the holding account into the real account in a way that directly mirrors actual deposits to the 401(k) administrator.

I added these memorized payees (remember, I used "TK 401(k)" as my deferral_payee, above.

With this, you should be good to go.

Normal Usage

Download Statement(s)

Download Transaction Detail statements from the 401k.benefitstreet.com website. It's important to be consistent when selecting options for this. I follow these steps every month:

  1. Go to 'Report Center'.
  2. Report: Fund Transaction Detail Report.
  3. Reporting period: monthly.
  4. Ending: <last day of the month of interest>.
  5. Reporting Basis: cash.
  6. Order by: trade date, ascending.
  7. Click 'Download file'.
  8. Click Save, save it in the same folder, every month, as year-month.csv.

On your first time through, you'll need to get complete history even if you don't intend to import that into Quicken so the benefitstreet script can accurately report cumulative positions.

Run benefitstreet on your CSVs

benefitstreet /your/path/to/csvs/*.csv

This will put a .qfx file right next to each source csv file.

When re-running benefitstreet, make sure to supply all csv files each time. The benefitstreet script doesn't keep state, so this is the only way to accurately report cumulative positions. The benefitstreet script will not overwrite existing .qfx files.

Import the QFX file into Quicken

Select File > Import > Web Connect File and then choose the first of your generated QFX files.

The first time through, you'll need to do some setup work at this point since Quicken will need some information to associate the fake FI in your QFX file with an account it's tracking. In this case, associate the account information you specified in the .benefitstreet configuration to correspond to your primary 401(k) account (not the holding account).

You may also need to choose how you want to add securities you haven't tracked before, either as new securities or mapped onto existing similarly named securities.

Version History

1.0 -- 2007-05-20
First useful release.

Available as source and gem.

Sample CSV File

You should be able to get a CSV file pretty easily from benefitstreet if you have an account there. If you're nosing around, the CSVs look like this:

Fund Transaction Detail Report
Tacit Knowledge, LLC 401(k) Profit Sharing Plan
Marek Gilbert  2/1/2005 - 2/28/2005
FUND: All Funds
FRTXX,Pre-tax Deferral      ,2/8/2005,1.00,1250.000,1250.00
FRTXX,Custodian Fee         ,2/10/2005,1.00,-0.307,-.31
FRTXX,Recordkeeping Fee     ,2/10/2005,1.00,-0.523,-.52

Contact Gil <gil (at) fooplanet (dot) com> with comments, feedback, patches, or suggestions.