QIF import file construction....

[Derek Atkins]

Hi,

Allan <allanhasmail at gmail.com> writes:

>> No.  You should use the correct "Type" based on the actual account
>> type.  For example, !Type:CCard for a credit card.
>
> This is really unclear. There are types assigned to the QuickBooks
> account, GnuCash account and QIF file:
>
> QIF types: !Type:Bank !Type:Invst !Type:Cash !Type:Oth A !Type:CCard
> !Type:Oth L (!Type:Invoice too?)
> Quickbooks account
> types:BANK,AR,OCASSET,FIXASSET,AP,CCARD,OCLIAB,LTLIAB,EQUITY,INC,EXP,EXINC,EXEXP
> GnuCash account types: Bank,Cash,Asset, Income, Expense, Equity,
> Credit Card, Liability, Stock, Mutual Fund, A/Receivable,A/Payable

It's not a 1:1 mapping; don't over-think it.  For example there's not
really any reason you would need to use "Oth A" instead of "Bank".  But
you would need to use "Invst" or "CCard".

> There's also the mapping which might change the type (a Credit Card to
> just a liability) but assuming the straightforward case, there are
> missing? types in the QIF: what to do with an income TX? (Not an
> invoice into AR) (Quickbooks uses an Undeposited Funds intermediary
> account so I have things like this to convert:

Quicken has no concept of an "Income" account.  It's just a category.
All transactions in QIF MUST involve at least one Asset or Liability
account.

> "1312","Cash Sale","6/2/2009","57","First Bank",,"1210 · Undeposited
> Funds",668.39,""
> ,,,,"First Bank","Checks, cash","4000 · CONSULTING INCOME","",627.60
> ,,,,"Minnesota Department of Revenue","Sales Tax","2200 · Sales Tax
> Payable","",40.79
>
> or an expense account entry (and matching Liability acct)? Like for
> FUTA (Federal Unemployment Tax)
>
> "373","General Journal","2/2/1995",,,,"6050 · FUTA Expense",168.00,""
> ,,,,,,"2160 · FUTA Payable","",168.00

These would be transactions in the Cash and Liability accounts.

> etc.
>
> Why does the GC import QIF function care about these types anyway?

For multple reasons.  First, to know how to parse the input.  For
example, there are different codes for an Investment transaction versus
a Bank/Cash transaction.  It's also a signal to know what kind of
Asset/Liability account to create.  For example, you wouldn't want to
import a credit card transaction into a bank account.

> Just create an imbalance if the mapping is wrong, doesn't balance.

No, this is absolutely the wrong thing to do.

> I can't see a path out of the woods here. I'm not finding on the web
> any QIF tools for these cases, more for just importing a list of
> checks like a bank statement. The OFX format doesn't seem to offer
> this either. Nor documentation of how GnuCash in/out interfaces work
> in enough detail.

Maybe I still don't understand the case.  Every transaction has at least
two sides to it.  You cannot have an "Income" transaction that doesn't
move that value from Income to *somewhere* (generally your pocket or
your bank).

In QIF the transaction must be in that Asset or Liability account, and
then you map the "far end" to the appropriate Income/Expense using the
QIF Category, Transaction Payee, or Transaction Memo.

If it's a transfer between Assets or between an Asset and Liability then
the "L" (Category/Account) field would be a QIF Account
(e.g. L[QifAccountName]) and then you map *THAT to a GnuCash account
too.

> Bigger picture: I have sales/inventory data CSV from the Square
> Android app, inventory on a spreadsheet, payroll/benefits in a
> spreadsheet. The interfaces need to tie it together.

GnuCash might not be the right tool for all that.

> Allan

-derek

-- 
       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