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 <dawa...@gmail.com> 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 <t...@tomolin.net > <mailto:t...@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@gnucash.org <mailto:gnucash-user@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 <dawa...@gmail.com >>> <mailto:dawa...@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 <t...@tomolin.net >>> <mailto:t...@tomolin.net> <mailto:t...@tomolin.net >>> <mailto:t...@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 <dawa...@gmail.com >>>> <mailto:dawa...@gmail.com> <mailto:dawa...@gmail.com >>>> <mailto:dawa...@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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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 <py...@outlook.com >>>>> <mailto:py...@outlook.com> <mailto:py...@outlook.com >>>>> <mailto:py...@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....@gnucash.org >>>>> <mailto:gnucash-user-bounces+pyz01=outlook....@gnucash.org> >>>>> <mailto:outlook....@gnucash.org <mailto:outlook....@gnucash.org>>> On >>>>> Behalf Of Tom Olin via gnucash-user >>>>> Sent: Monday, July 3, 2023 1:55 PM >>>>> To: Kalpesh Patel <kalpesh.pa...@usa.net <mailto:kalpesh.pa...@usa.net> >>>>> <mailto:kalpesh.pa...@usa.net <mailto:kalpesh.pa...@usa.net>>> >>>>> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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.pa...@usa.net >>>>>> <mailto:kalpesh.pa...@usa.net> <mailto:kalpesh.pa...@usa.net >>>>>> <mailto:kalpesh.pa...@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 <t...@tomolin.net <mailto:t...@tomolin.net> >>>>>> <mailto:t...@tomolin.net <mailto:t...@tomolin.net>>> >>>>>> Sent: Monday, July 03, 2023 12:14 PM >>>>>> To: Jean L <rip...@gmail.com <mailto:rip...@gmail.com> >>>>>> <mailto:rip...@gmail.com <mailto:rip...@gmail.com>>> >>>>>> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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 <rip...@gmail.com >>>>>>> <mailto:rip...@gmail.com> <mailto:rip...@gmail.com >>>>>>> <mailto:rip...@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> >>>>>>> <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+gnuc...@jdlh.com >>>>>>>>> <mailto:list+gnuc...@jdlh.com> <mailto:list%2bgnuc...@jdlh.com >>>>>>>>> <mailto:list%2bgnuc...@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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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> >>>>>>>>> <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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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> >>>>>>>> <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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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> >>>>>>> <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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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> >>>>> <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@gnucash.org <mailto:gnucash-user@gnucash.org> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@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> >>>> <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@gnucash.org <mailto:gnucash-user@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@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.