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.
Easy:
sudo gem install ofxgen-1.0.gem. 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.
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".
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:
acct_id -- A fake account number at your fake FI.
This can be any alphanumeric sequence up to 22 characters long. I
chose a random nine-digit number.deferral_payee -- A prefix on deposit transactions
that makes it easy to associate deposit transactions automatically
with a transfer account. intu_bid -- The five-digit code determined above.
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
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...
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:
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.
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 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.
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.
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:
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.
benefitstreet on your CSVsbenefitstreet /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.
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.
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 ACCOUNTING BASIS: CASH FUND: All Funds TICKER,TRANSACTION TYPE,TRADE DATE,PRICE,SHARES,DOLLARS 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.