[GNC] OFX import when bank doesn't use unique ACCTID

[David Reiser]

> On Oct 22, 2018, at 9:27 PM, Ryan Boder <ryan.boder at gmail.com> wrote:
> 
> I'm new to gnucash and have run into a frustrating problem importing my
> bank accounts. My bank is putting a non-unique number in the ACCTID field
> of the OFX files. They only put the last 4 digits of what they call the
> "member number product ID" in as the ACCTID. I have multiple accounts with
> this bank and all my accounts have the same last 4 digits in that ID.

Are they sending you more than one account per file. That is permissible in the OFX standard, but there would have to be something that identified the subaccount uniquely. Is there some other line in the file that provides ID of the subaccount? If so, one option is to process each file via script (that you’d have to write) before importing. If your bank doesn’t give you any way to tell which account one OFX file is from vs another OFX file, that’s a user hostile bank.

> 
> The result is that when I try to import transactions into Gnucash, once
> that 4 digit ID has been associated with a Gnucash account then Gnucash
> wants to import transactions for all my OFX files into that same account.
> 
> I can think of a few work arounds for the issue but I wanted to ask the
> list since I'm a newbie and don't want to pick a bad solution.
> 
> 1) I could edit the OFX files from my bank before importing them into
> Gnucash and replace the 4 digit ID with the full account number which is
> unique. This seems like the best solution to me but also a bit annoying and
> it would not work if starting using automatic imports.

It wouldn’t work with automatic imports, for sure, but if the bank supports direct connect downloads, there has to be something in each data stream that identifies the subaccount, or it can’t possibly work in any other software, either. I’d vote with a custom script that you use to preprocess each download. One possible complication is that Quicken is trying to get banks to switch to Express Web Connect vs older Direct Connect connections. EWC is totally proprietary to Quicken, so we might get shut out of the data downloading direct to gnucash sometime in the not too distant future.

> 
> 2) I could edit my Gnucash file and remove the online_id slot from the XML
> before importing. That would make Gnucash prompt me again for which account
> to import transactions into. That seems risky. Should I avoid editing my
> Gnucash file manually like this?

Not only is manually editing a gnucash file at least slightly risky, it also gets old really fast. I tried that many years ago and decided that manually editing text//OFX files before import was preferable.

> 
> 3) My bank supports CSV export as well as OFX/QFX. I could avoid OFX all
> together and try to make something work with CSV. This doesn't seem ideal
> as it's not a standard file format and it looks like I would have to do
> some manipulation of some of the fields to make it work in Gnucash.

The Gnucash version 3.x CSV importer is much better than prior versions. If your CSV has any structure at all, it should be possible to set up the import settings to get the data, and save those settings so you never have to edit them again — just pick the settings definition in a dropdown menu during the import. The CSV importer has the added advantage that you get a look at the file you’re importing and can tweak the settings and see the filtering results before actually importing the file.

> 
> What do more experienced users recommend? Also, have others had this
> problem before? If so, maybe we can find a way to solve it in a future
> version such as optionally being able to force Gnucash to prompt for the
> account to import into instead of just assuming it's what's in
> the online_id slot.
> 
> - Ryan
> _______________________________________________


--
Dave Reiser
dbreiser at icloud.com