[GNC] Importing data from QuickBooks Online

Tue Jul 4 20:31:29 EDT 2023

May I suggest also posting this in the FAQ section on the wiki, there is a
section that mentions Quickbooks import.
See here:
https://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_import_my_data_from_...

You could put your file in a github gist for easy sharing. Then link from
the FAQ. There is already a link there for some other script from Linux
Weekly News but I haven't checked what that looks like.

On Tue, Jul 4, 2023 at 3:44 PM Tom Olin <tom at tomolin.net> wrote:

> Here’s the final version (for now) of my script. I hope someone else finds
> it useful.
>
> Thanks to all who responded to my query with suggestions.
>
> --
> Tom
>
> Federal taxes can be paid with dollars, but the dollars have to be created
> (spent) by the government before anyone has dollars with which to pay their
> taxes.
>
> On Jul 4, 2023, at 8:00 AM, Tom Olin via gnucash-user <
> gnucash-user at gnucash.org> wrote:
>
> Vincent,
>
> Thanks so much for figuring this out! If you don’t mind, I’d like to give
> you credit in my file.
>
> I’m going to rerun the entire dataset as a final test. I will repost the
> final version of the script here for anyone else looking to do this.
>
> Thanks, again!
>
> --
> Tom
>
> Federal spending funds taxes. It is impossible to pay taxes until the
> government has spent money into the economy.
>
> On Jul 3, 2023, at 10: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 <
> mailto:tom at tomolin.net <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 <
> mailto:dawansv at gmail.com <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 <mailto:gnucash-user at gnucash.org
> <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 <
> mailto:pyz01 at outlook.com <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 <
> mailto:outlook.com at gnucash.org <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 <mailto:kalpesh.patel at usa.net
> <kalpesh.patel at usa.net>>>
> Cc: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org
> <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 <
> mailto:kalpesh.patel at usa.net <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 <mailto:tom at tomolin.net <tom at tomolin.net>>>
>
> Sent: Monday, July 03, 2023 12:14 PM
> To: Jean L <ripngo at gmail.com <mailto:ripngo at gmail.com <ripngo at gmail.com>>>
> Cc: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org
> <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 <
> mailto:ripngo at gmail.com <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 <
> 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 <
> mailto:list%2Bgnucash at jdlh.com <list%2Bgnucash 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 <mailto:gnucash-user at gnucash.org
> <gnucash-user at gnucash.org>>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user <
> 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 <mailto:gnucash-user at gnucash.org
> <gnucash-user at gnucash.org>>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user <
> 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 <mailto:gnucash-user at gnucash.org
> <gnucash-user at gnucash.org>>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user <
> 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 <mailto:gnucash-user at gnucash.org
> <gnucash-user at gnucash.org>>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user <
> 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 <mailto:gnucash-user at gnucash.org
> <gnucash-user at gnucash.org>>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user <
> 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.
>
>
>