I’ve posted my script here:
https://gist.github.com/trolin522581/cd9b8d684b0f9e0c8aed8876b256e31e
I’ll leave the wiki link to someone else.
--
Tom
For the government, which came first? (a) first $ taxed; (b) first $ borrowed;
(c) first $ spent. (Hint: state or federal?)
> On Jul 4, 2023, at 8:31 PM, Vincent Dawans <[email protected]> wrote:
>
> 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_...
> <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 <[email protected]
> <mailto:[email protected]>> 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
>> <[email protected] <mailto:[email protected]>> 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 <[email protected]
>>> <mailto:[email protected]>> 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 <[email protected]
>>> <mailto:[email protected]> <mailto:[email protected]
>>> <mailto:[email protected]>>> 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 <[email protected]
>>>> <mailto:[email protected]> <mailto:[email protected]
>>>> <mailto:[email protected]>>> 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
>>>> <[email protected] <mailto:[email protected]>
>>>> <mailto:[email protected] <mailto:[email protected]>>> 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 <[email protected]
>>>>> <mailto:[email protected]> <mailto:[email protected]
>>>>> <mailto:[email protected]>>> 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 <[email protected]
>>>>> <mailto:[email protected]>
>>>>> <mailto:[email protected] <mailto:[email protected]>>> On
>>>>> Behalf Of Tom Olin via gnucash-user
>>>>> Sent: Monday, July 3, 2023 1:55 PM
>>>>> To: Kalpesh Patel <[email protected] <mailto:[email protected]>
>>>>> <mailto:[email protected] <mailto:[email protected]>>>
>>>>> Cc: [email protected] <mailto:[email protected]>
>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>> 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 <[email protected]
>>>>>> <mailto:[email protected]> <mailto:[email protected]
>>>>>> <mailto:[email protected]>>> 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 <[email protected] <mailto:[email protected]>
>>>>>> <mailto:[email protected] <mailto:[email protected]>>>
>>>>>> Sent: Monday, July 03, 2023 12:14 PM
>>>>>> To: Jean L <[email protected] <mailto:[email protected]>
>>>>>> <mailto:[email protected] <mailto:[email protected]>>>
>>>>>> Cc: [email protected] <mailto:[email protected]>
>>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>>> 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 <[email protected]
>>>>>>> <mailto:[email protected]> <mailto:[email protected]
>>>>>>> <mailto:[email protected]>>> 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>
>>>>>>> <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<[email protected]
>>>>>>>>> <mailto:[email protected]> <mailto:list%[email protected]
>>>>>>>>> <mailto:list%[email protected]>>> 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
>>>>>>>>> [email protected] <mailto:[email protected]>
>>>>>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>>>>>> To update your subscription preferences or to unsubscribe:
>>>>>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>>>>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>>>>>>>>> <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
>>>>>>>> [email protected] <mailto:[email protected]>
>>>>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>>>>> To update your subscription preferences or to unsubscribe:
>>>>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>>>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>>>>>>>> <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
>>>>>>> [email protected] <mailto:[email protected]>
>>>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>>>> To update your subscription preferences or to unsubscribe:
>>>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>>>>>>> <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
>>>>> [email protected] <mailto:[email protected]>
>>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>>> To update your subscription preferences or to unsubscribe:
>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>>>>> <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
>>>> [email protected] <mailto:[email protected]>
>>>> <mailto:[email protected] <mailto:[email protected]>>
>>>> To update your subscription preferences or to unsubscribe:
>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>>>> <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
>> [email protected] <mailto:[email protected]>
>> 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
[email protected]
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.
