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> 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> 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>> 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>> 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>> 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>> 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: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>> >>>> Cc: 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>> 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>> >>>>> Sent: Monday, July 03, 2023 12:14 PM >>>>> To: Jean L <rip...@gmail.com <mailto:rip...@gmail.com>> >>>>> Cc: 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>> 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+gnuc...@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> >>>>>>>> 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 <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 <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 <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 <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. _______________________________________________ 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.
