[GNC] Importing data from QuickBooks Online

[Vincent Dawans]

Just to add that the reconcile flag is sort of a moot point right now
because it seems like there is a bug with the multi-split import where the
flag is ignored and set to cleared for the first account on each
transaction. See  796890 – CSV import of multi-split transactions marks
reconciliation status of first line as cleared (gnucash.org)
<https://bugs.gnucash.org/show_bug.cgi?id=796890>

On Mon, Jul 3, 2023 at 7:37 PM Vincent Dawans <dawansv at gmail.com> wrote:

> Tom:
>
> I tested both files and it seems that it fails on the reconcile column for
> 2022. When I skip the reconcile column it works. 2022 has Y entries in that
> column while 2023 doesn't, and when I replace the 2022 Y entries with c it
> works. So it seems to be related to either the inability to import
> reconciled flag or the flag is different, I am not sure. But that's where
> the problem is, something to do with the Y reconcile flag.
>
> Sincerely,
>
> Vincent Dawans
>
> On Mon, Jul 3, 2023 at 4:41 PM Tom Olin <tom at tomolin.net> wrote:
>
>> Vincent,
>>
>> Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022
>> and 2023. Instructions for importing them are in the documentation of the
>> script, latest version also attached.
>>
>> 2022 still fails, 2023 still works. Create the accounts as needed; there
>> are only a few.
>>
>> --
>> Tom
>>
>> Money is created when banks loan and when the federal government spends.
>> The latter increases someone’s net worth. The former does not, but the
>> interest and fees transfer net worth from the borrower to the lender.
>>
>> On Jul 3, 2023, at 6:44 PM, Vincent Dawans <dawansv at gmail.com> wrote:
>>
>> Tom:
>>
>> I haven't read every single email in this thread but have this advice for
>> you to further diagnose. When I face a puzzle like this, I like to try
>> creating the simplest possible scenario in which I am still able to
>> reproduce the problem. In this case this would start by figuring out how
>> far you can pare down your 2022 csv file while still having the problem.
>> Can you pair it down to just a few transactions, like 5 or 10 max?  Can you
>> then change some of that data in that pared down file and still have the
>> problem? By simplifying you can often more easily find the source of the
>> problem and also might be able to share your csv file when it reaches the
>> point where the data in it is no longer personal.
>>
>> Sincerely,
>>
>> Vincent Dawans
>>
>> On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user <
>> gnucash-user at gnucash.org> wrote:
>>
>>> I would add that I’m more inclined to attribute the problem to GnuCash
>>> logic. However, the code will require a lot of study on my part before I
>>> can make meaningful sense of it. I just wish I could think of some possible
>>> logic that might explain it.
>>>
>>> --
>>> Tom
>>>
>>> Money is created out of thin air when banks loan and when the federal
>>> government spends. Money is destroyed into thin air when bank loans are
>>> repaid and when federal taxes are paid.
>>>
>>> > On Jul 3, 2023, at 5:41 PM, Ken Pyzik <pyz01 at outlook.com> wrote:
>>> >
>>> > Tom -- I believe at the beginning of 2022, QuickBooks went from being
>>> a desktop and online software package  - to being strictly/only online.
>>> With that transition, I believe they also gave a one-year period where you
>>> could export data.  While this may be a stretch, I believe that they may
>>> have somehow added a change flag or some other thing to the data to prevent
>>> you from exporting it as easy as it used to be.  This could be the
>>> discrepancy you are experiencing.  In other words, to prevent people from
>>> doing what you are exactly trying to do -- they may have placed a simple
>>> data offset or some other thing into the data to prevent easy export.
>>> Again, this could be a stretch -- but it would explain why one year comes
>>> over correctly and the next does not.  Just my two cents -- for what it is
>>> worth ( which could be nothing at all!)
>>> >
>>> > Ken
>>> >
>>> > -----Original Message-----
>>> > From: gnucash-user <gnucash-user-bounces+pyz01=outlook.com at gnucash.org>
>>> On Behalf Of Tom Olin via gnucash-user
>>> > Sent: Monday, July 3, 2023 1:55 PM
>>> > To: Kalpesh Patel <kalpesh.patel at usa.net>
>>> > Cc: gnucash-user at gnucash.org
>>> > Subject: Re: [GNC] Importing data from QuickBooks Online
>>> >
>>> > Good shot, but no, date formats are consistent.
>>> >
>>> > --
>>> > Tom
>>> >
>>> > The federal government imposes a tax on you so YOU need THEIR money,
>>> not because they need yours.
>>> >
>>> >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel <kalpesh.patel at usa.net>
>>> wrote:
>>> >>
>>> >> I am likely grasping straws here like David but did you verify the
>>> format of the date that it is consistent throughout? Like it isn’t
>>> switching from two digits to four digits, or replace certain digits with
>>> place holders, etc. QuickBooks is (was?) published by the maker of Quicken
>>> and I remember their exports when it came to Quicken was all over the map
>>> for the format of the date and ended up normalizing it with an external
>>> script when I did a full migration from Quicken to GNC. By far this, the
>>> date format, was biggest PITA.
>>> >>
>>> >> -----Original Message-----
>>> >> From: Tom Olin <tom at tomolin.net>
>>> >> Sent: Monday, July 03, 2023 12:14 PM
>>> >> To: Jean L <ripngo at gmail.com>
>>> >> Cc: gnucash-user at gnucash.org
>>> >> Subject: Re: [GNC] Importing data from QuickBooks Online
>>> >>
>>> >> Jean,
>>> >>
>>> >> Thanks for that. I did play with a few similar options but couldn’t
>>> get anything to work easily. Some were aimed more at Quicken or QuickBooks
>>> desktop instead of QuickBooks Online.
>>> >>
>>> >> As it turns out, my minimal script seems to work very well with this
>>> one weird anomaly. The nature of it suggests something that should be
>>> easily worked around - if I can just figure out what it is.
>>> >>
>>> >> --
>>> >> Tom
>>> >>
>>> >> Federal tax dollars don’t exist. Federal spending creates dollars out
>>> of thin air. With federal tax payments, the opposite occurs.
>>> >>
>>> >>> On Jul 3, 2023, at 12:04 PM, Jean L <ripngo at gmail.com> wrote:
>>> >>>
>>> >>> This may be slightly off topic, or too late to help, but...
>>> >>>
>>> >>> In github, there is a repository
>>> >>> https://github.com/tim-rohrer/move2gnucash
>>> >>> That seems pretty well setup to migrate your data from a quicken csv
>>> export to GC. I haven't used it, but I looked at it for a friend and it
>>> looked interesting.
>>> >>>
>>> >>> Jean
>>> >>>
>>> >>> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote:
>>> >>>> [Resending to the list. Original reply went only to Jim.]
>>> >>>>
>>> >>>> Jim,
>>> >>>>
>>> >>>> Fair questions. Answers below, and I’ve attached the script itself
>>> which includes documentation which addresses some of the questions. I’ve
>>> reviewed all documentation that I can find.
>>> >>>>
>>> >>>> From QBO, I export a journal report to XLS (only usable option in
>>> QBO).
>>> >>>>
>>> >>>> GnuCash 5.3 on macOS 11.7.8
>>> >>>>
>>> >>>> See the script for the specific steps.
>>> >>>>
>>> >>>> The import matcher step of the import process is fully satisfied.
>>> That is the step labeled “Match Import and GnuCash accounts”.
>>> >>>>
>>> >>>> The  problem manifests at the “Match Transactions” screen where all
>>> transactions need to be matched. I’ve attached a screenshot if it is
>>> supported here.
>>> >>>>
>>> >>>> One more data point: I tried changing one of the transactions in
>>> the 2022 import file to 2023. It still failed the same way.
>>> >>>>
>>> >>>> —
>>> >>>> Tom
>>> >>>>
>>> >>>>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt<list+gnucash at jdlh.com>
>>> wrote:
>>> >>>>>
>>> >>>>> Tom:
>>> >>>>>
>>> >>>>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote:
>>> >>>>>> I’m attempting to import data from QuickBooks Online to GnuCash.
>>> I’ve written an awk script which appears to work well except for one major
>>> issue.
>>> >>>>> What format is the data which you export from Quickbooks Online?
>>> CSV (Comma Separated Values text files with tabular data)? QFX (Quicken
>>> Financial Exchange, similar to OFX)?
>>> >>>>>
>>> >>>>> What GnuCash version are you using? On what computer OS?
>>> >>>>>
>>> >>>>> What GnuCash sequence of actions do you use to import the data?
>>> >>>>>
>>> >>>>>
>>> >>>>>> The data I’m importing spans the years 2022 and 2023. All 2023
>>> transactions import cleanly, but all 2022 transactions import unbalanced,
>>> meaning I have to manually match up each of them - doable but tedious.
>>> >>>>> Let's assume you are exporting data in CSV format, and using the
>>> current version of GnuCash (5.3), and importing using the File… Import…
>>> Import Transactions from CSV menu item. You should be directed through an
>>> import matcher. This is the place where GnuCash should assign accounts to
>>> balance each transaction. Is each transaction assigned to an account in the
>>> import matcher?
>>> >>>>>
>>> >>>>> Have you read the section of the documentation explaining how to
>>> import data?
>>> >>>>>
>>> >>>>>> Can anyone think of anything that would cause this behavior? I’ve
>>> ruled out Accounting Period. I’ve imported each year separately. I’ve
>>> exported each year separately from QBO. The behavior persists.
>>> >>>>>>
>>> >>>>>> I’m stumped. Any ideas?
>>> >>>>> I hope these questions help get enough information on the table to
>>> give someone ideas.
>>> >>>>>
>>> >>>>> Best regards,
>>> >>>>>  —Jim DeLaHunt
>>> >>>>>
>>> >>>>>
>>> >>>>> _______________________________________________
>>> >>>>> gnucash-user mailing list
>>> >>>>> gnucash-user at gnucash.org
>>> >>>>> To update your subscription preferences or to unsubscribe:
>>> >>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> >>>>> -----
>>> >>>>> Please remember to CC this list on all your replies.
>>> >>>>> You can do this by using Reply-To-List or Reply-All.
>>> >>>>
>>> >>>> _______________________________________________
>>> >>>> gnucash-user mailing list
>>> >>>> gnucash-user at gnucash.org
>>> >>>> To update your subscription preferences or to unsubscribe:
>>> >>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> >>>> -----
>>> >>>> Please remember to CC this list on all your replies.
>>> >>>> You can do this by using Reply-To-List or Reply-All.
>>> >>> _______________________________________________
>>> >>> gnucash-user mailing list
>>> >>> gnucash-user at gnucash.org
>>> >>> To update your subscription preferences or to unsubscribe:
>>> >>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> >>> -----
>>> >>> Please remember to CC this list on all your replies.
>>> >>> You can do this by using Reply-To-List or Reply-All.
>>> >>
>>> >>
>>> >>
>>> >
>>> > _______________________________________________
>>> > gnucash-user mailing list
>>> > gnucash-user at gnucash.org
>>> > To update your subscription preferences or to unsubscribe:
>>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> > -----
>>> > Please remember to CC this list on all your replies.
>>> > You can do this by using Reply-To-List or Reply-All.
>>>
>>> _______________________________________________
>>> gnucash-user mailing list
>>> gnucash-user at gnucash.org
>>> To update your subscription preferences or to unsubscribe:
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> -----
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>>>
>>
>>