Transferring data between two Gnucash files

Tue Jan 10 11:57:42 EST 2012

Comments inline...

On Jan 10, 2012, at 10:48 AM, Derek Atkins wrote:

> Hi,
> 
> Jesse C <crimson.corelio at gmail.com> writes:
> 
>> Hey Derek,
>> 
>> Thanks for the reply.
>> 
>> I can definitely make a QIF file (or an OFX file).  I've never used the QIF
>> importer in Gnucash.  QIF (and also OFX) appear to be single-entry formats,
>> rather than double-entry formats.  I assume that GnuCash does some guessing as
>> to what balancing account a transaction belongs in based on the memo
>> associated with the transaction?  Which would mean there really isn't a fully
>> automated way to import the information, which would less than stellar, but
>> still better than having to manually enter all the information. 
> 
> Well, yes and no.  QIF does allow you to claim a transaction's far
> account by specifying a Category (for income/expense) or Account (for
> asset/liability) through the 'L' field:
> 
> LCategory
> L[Account]
> 
> Then in the importer you can map the QIF Categories and QIF Accounts
> into Gnucash Accounts.
> 
>> It also seems like (from the mailing list archive) that the OFX importer can
>> match up transactions from different OFX files with the same date and amount. 
>> So I could technically generate OFX files for each account and once they are
>> all imported, GnuCash will automatically match them up.  Is that correct?
> 
> I'm not sure what you mean by this.  What do you mean by "matching
> ... from different OFX files"?

Possibly "different OFX files" means files from the same source downloaded at different times. e.g., my credit union always includes 30 days of transaction data any time I ask for a download. If I have today's download and last Tuesdays download on my hard drive, then the files have three weeks of data in common.

If you are thinking along the lines of not having to carefully pick transactions to be exported without having to fuss with duplicates on the far end of the transfer, then yes, OFX has a built-in capability for automatically eliminating duplicates. However, that capability is not based on date and amount, it's based on an internal unique identifier for each transaction. You will have to generate these IDs prior to exporting, since there is no user-accessible way to view or set the IDs in Gnucash. It would be a good idea to generate the ID's, attach them to gnucash's data record for each transaction (avoiding reassigning numbers, or overwriting IDs that might originate from a bank download), and then export the transactions.

> 
>> Is there a preference in GnuCash for QIF vs OFX?  There appear to be java
>> bindings in some form for both (and QIF is so simple that writing a generator
>> for it would be trivial), so I can create either with minimal effort.
> 
> IMHO QIF is easier to generate, and QIF also has a way to create Split
> transactions.  OFX AFAIK does not provide any means to specify a Split
> Txn nor a way to pre-specify target categories or accounts.

I'm with Derek on this one. Since you're going from gnucash to gnucash, you can work around all of qif's ambiguities. The only thing you can't do easily is deal with duplicate transactions. You could possibly use the ofx unique ID field as a way of selecting transactions for export -- if there's anything in that field, don't export it. and then write new ID's to all transactions in the current stream during the export so that both sides know not to export that transaction ever again.

The lack of split transactions in ofx is a real pain. And qif exports can be a single file. For ofx, it will be one file per account.

> 
>> Once I get this working, I'll push out the results of my work to the
>> community, in case there are other people who are interested in doing
>> something similar.
> 
> That would be good.  :)
> 
>> cheers
>> 
>> jesse
> 
> -derek
> 
>> On Mon, Jan 9, 2012 at 1:20 PM, Derek Atkins <warlord at mit.edu> wrote:
>> 
>>    Hi,
>> 
>>    "David T." <sunfish62 at yahoo.com> writes:
>> 
>>> As I understand it, there is no facility to do what you want.
>>> 
>>> You already seem to have the mechanics of coordinating the two sets of
>>    books; I think that's the prescribed method.
>>> 
>>> Have you considered merging your books? It would reduce the overhead...
>> 
>>    My other recommendation is that if you can take the entries from one
>>    file and programatically decide what you need entered in the other file,
>>    just generate a QIF file and use the QIF Importer to import those
>>    additional transactions.
>> 
>>> David
>> 
>>    -derek
>> 
>>> 
>>> ________________________________
>>>  From: Jesse C <crimson.corelio at gmail.com>
>>> To: gnucash-user at gnucash.org
>>> Sent: Sunday, January 8, 2012 1:18 PM
>>> Subject: Transferring data between two Gnucash files
>>> 
>>> My wife and I both use Gnucash to keep track of our finances, in
>>    separate
>>> Gnucash files.  There are some expenses that one of us incurs which are
>>> shared expenses, that the other person is liable for a portion of
>>    (usually
>>> half).  Currently all transfer of these expenses is done manually, by
>>> exporting a report that contains the expenses, which the other person
>>    then
>>> has to enter by hand.  That's a lot of work and it sucks.  So I'm
>>    looking
>>> for a better way to accomplish this.
>>> 
>>> It is easy to export the list of transactions to be transferred as a
>>    html
>>> file and it is simple to parse the html and obtain a programmatic
>>> representation of the data (my languages of choice are Java and Scala,
>>    but
>>> python, php, lua, etc. are also options, though I don't have any scheme
>>> experience).  But once I have that programmatic representation of the
>>    data,
>>> I don't know what to do with it.  What sort of file can I write that
>>    will
>>> allow me to import transactions, complete with account information, into
>>> gnucash/  I've never used the QIF or OFX functionality in Gnucash, so
>>    I'm
>>> not sure if one of those would be answer.
>>> 
>>> part of me wants to just switch to the SQL backend and stuff the data in
>>> there, but from reading on the wiki this sounds like a terrible idea.
>>> 
>>> To present a quick example:
>>> 
>>> I have a Rent transaction, for a month where I paid the entire amount.
>>> 
>>> (Asset Account) Wife A/L:Shared Expenses:Owed To Me:Housing:Rent  +50
>>> (Asset Account) Assets:Current Assets:Checking Account   -100
>>> (Expense Account) Expenses:Rent   +50
>>> 
>>> I then export the entire Wife A/L:Shared Expenses account to a report
>>> file.  I'd then like to write some new file that she can import which
>>    will
>>> contain the following transaction
>>> 
>>> (Liability Account) Husband A/L:Shared Expenses:Owed to Him:Merged  +50
>>> (Expense Account) Expenses:Housing:Rent  +50
>>> 
>>> I'm not opposed to doing some development work to solve this problem,
>>    but
>>> some direction would be great.  Thanks for any help.
>>> 
>>> cheers,
>>> 
>>> jesse
>>> 
>> 
>>    --
>>          Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
>>          Member, MIT Student Information Processing Board  (SIPB)
>>          URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
>>          warlord at MIT.EDU                        PGP key available
>> 
> 
> -- 
>       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
>       Member, MIT Student Information Processing Board  (SIPB)
>       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
>       warlord at MIT.EDU                        PGP key available

--
David Reiser
dbreiser at earthlink.net