[GNC] Importing data from QuickBooks Online

Mon Jul 3 22:37:29 EDT 2023

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