Multi-Account Journal Entry Import from Excel Spreadsheet?

[Derek Atkins]

Hi,

Please remember to CC gnucash-user on all replies using your mailer's
reply-to-list or reply-all functionality.  This way others get the
chance to see your question (and the answer), or possibly answer faster
than I can personally...

Aaron Lee <alazyguy at gmail.com> writes:

> Wait...  does this mean I can't have multiple accounts on BOTH debit AND
> credit side?

No, it certainly does not mean that at all.  In QIF there is a "primary"
account for the transaction, and then any number of "other" accounts in
the transaction.  Within one transaction you can most certainly have
multiple debits and credits.  However one is "assumed" by the
transaction itself, and all the rest must be listed.

> Does this also mean that II have to map the account manually every time I
> import a QIF?

No, GnuCash remembers the mappings and will apply them next time.
However if you use QIF Categories/Accounts (L for the default split and
S for the secondary splits) that look like GnuCash accounts then there
wont be any mappings to do.  The issue is that AFAIK there is no way for
the Calc2QIF macro to generate those entries in the QIF.

> If so, I might as well just duplicate the General Ledger transaction from
> within GnuCash, and change the dates and numbers every pay period...

Honestly, you're probably better off doing this anyways.  This is
certainly what I do.  I got the paycheck entered in once; then every
subsequent pay period I just use the register autofill to duplicate the
transaction and edit the amounts.  It takes me under 30 seconds to enter
in my 10-12 split payroll transaction.

-derek

> On Wed, Apr 23, 2014 at 11:14 AM, Derek Atkins <warlord at mit.edu> wrote:
>
>     Hi,
>    
>     Aaron Lee <alazyguy at gmail.com> writes:
>    
>     > Hi Derek,
>     >
>     > Thanks for the assistance.  I can't seem to get this working.
>     > http://puu.sh/8jKvB.png
>    
>     This page of the dialog is saying "all the transaction in this file are
>     for Account "C:\Users\alazguy\Desktop\testest" -- you probably want to
>     change that to something more reasonable.  Note that this is a
>     QIFAccount name, not a GNC Account.  Later on in the import process
>     there is a page to map QIFAccounts to GNC (Asset/Liability) Accounts.
>     There's also a place to map QIFCategories to GNC (Income/Expense)
>     Accounts.
>    
>     > Is there something wrong with my mapping?
>     > http://puu.sh/8jKkZ.png
>    
>     You probably don't want to make the "account" number the payee.
>    
>     Apparently there is no way to set QIFCategory of QIFAccount via the
>     macro, so you may need to process the file by hand.  But putting an
>     account name as the Payee is *probably* wrong.  The Payee is generally,
>     well, the Payee!  The person or company to whom (or from whom) the money
>     went (came).
>    
>     > Gnu - XL2QIF
>     > Date - Date
>     > Number - Check Number
>     > Account - Payee
>     > Debit - Debit
>     > Credit - Credit
>     > Description - Memo
>    
>     -derek
>    
>     > On Tue, Apr 22, 2014 at 11:14 AM, Derek Atkins <derek at ihtfp.com> wrote:
>     >
>     >     On Tue, April 22, 2014 2:09 pm, Aaron Lee wrote:
>     >     > Hello,
>     >     >
>     >     > Looks like I'll need to find an excel => QIF converter.
>     >
>     >     Cal2QIF?
>     >
>     >     > Is there any documentation on what format my data must be in?
>      Though I
>     >     > guess that would be dependent on the converter I use.
>     >     > Can I use account codes, or must I use account names?
>     >
>     >     You must use names.  These can match the existing gnucash account
>     names
>     >     (including the hierarchy) -- or you can map the QIFAccount names to
>     the
>     >     GNCAccount names.
>     >
>     >     > Googling for the past few days isn't getting me anything useful.
>      I
>     >     wonder
>     >     > if my google-fu has degraded...
>     >     >
>     >     > Thanks,
>     >
>     >     -derek
>     >
>     >     --
>     >            Derek Atkins                 617-623-3745
>     >            derek at ihtfp.com             www.ihtfp.com
>     >            Computer and Internet Security Consultant
>     >
>    
>     --
>            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