[GNC] Importing data from QuickBooks Online
Tom Olin
tom at tomolin.net
Tue Jul 4 18:44:27 EDT 2023
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>> 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>> 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>> 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>> 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>> 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>>
>>>> Cc: gnucash-user at gnucash.org <mailto: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>> 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>>
>>>>> Sent: Monday, July 03, 2023 12:14 PM
>>>>> To: Jean L <ripngo at gmail.com <mailto:ripngo at gmail.com>>
>>>>> Cc: gnucash-user at gnucash.org <mailto: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>> 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>> 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>
>>>>>>>> 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>
>>>>>>> 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>
>>>>>> 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>
>>>> 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>
>>> 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.
More information about the gnucash-user
mailing list