Merging imported transactions
Greetings, I download all my transactions from my back in quickbooks OFX format. I import them into gnu cash. This works well; however, I’ve hit a snag. I have a credit card, a paypal account and a bank account. These all have different formats for downloading the data from, which I can convert to something gnu cash can import. When I do this, though, I have a small problem. The bank account comes in via OFX, so has lots of Imbalance-USD entries. I’ve used this account for the import of some items from the credit card and paypal accounts as well. So I wind up with a transaction in the bank ledger that is a payment to the credit card, but doesn’t credit that account. There’s also a separate transaction in the credit card that’s a payment from the bank, but doesn’t debit the bank account. In a sense, there’s two double entries I’d like to combine to eliminate the Imbalance-USD account entirely since I don’t think that’s a real account and find it sloppy book keeping. Is there anything I can use to merge entries when I’m replying on downloading the bulk of the data rather than entering it myself? Thanks for any help you can give me here. Warner signature.asc Description: Message signed with OpenPGP using GPGMail ___ gnucash-user mailing list gnucash-user@gnucash.org 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.
Re: Merging imported transactions
The OFX import assistant includes a step to match newly imported transactions to existing transactions, as you probably noticed. The problem is that it is error prone and has to be monitored carefully during the import process. I prefer to import a small number of transactions at a time so that I am less likely to be overwhelmed. The program is supposed to learn from previous imports and improve it's accuracy eventually, but in my experience it still seems to make a lot of mis-matches. I still end up with duplicates when it fails to find the correct match or I just get tired of searching and just let the duplicates through. I am less likely to miss a transaction if I use the import assistant, so I continue to use it. David C On Mon, Sep 25, 2017 at 11:00 AM, Warner Losh wrote: > Greetings, > > I download all my transactions from my back in quickbooks OFX format. I > import them into gnu cash. This works well; however, I’ve hit a snag. > > I have a credit card, a paypal account and a bank account. These all have > different formats for downloading the data from, which I can convert to > something gnu cash can import. When I do this, though, I have a small > problem. The bank account comes in via OFX, so has lots of Imbalance-USD > entries. I’ve used this account for the import of some items from the > credit card and paypal accounts as well. So I wind up with a transaction in > the bank ledger that is a payment to the credit card, but doesn’t credit > that account. There’s also a separate transaction in the credit card that’s > a payment from the bank, but doesn’t debit the bank account. In a sense, > there’s two double entries I’d like to combine to eliminate the > Imbalance-USD account entirely since I don’t think that’s a real account > and find it sloppy book keeping. Is there anything I can use to merge > entries when I’m replying on downloading the bulk of the data rather than > entering it myself? > > Thanks for any help you can give me here. > > Warner > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > 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 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.
Re: Merging imported transactions
Warner: I may be wrong but: it sounds like the Bayesian matching routine has been taught to match these transactions to "Imbalance-USD". You need to do several small imports of ofx/qfx files, from any and all of these financial institutions that use ofx/qfx. The Bayesian matching routine is going to have to be "UN-taught" to point at the wrong account. Do several small imports. I do not know how many transactions you run monthly but; for sake of argument let us say import only a half-dozen transactions at a time (over time you can increase the number as it learns). The Bayesian matching routine catches on faster that way, short fast lessons not long boring ones. Since you are getting a lot of Imbalance transactions select the "A" box and manually select the account where the transaction should go. After 2 or 3 imports the importer should slowly begin to catch on so you can use the "R"econcile option more often. It will help some if you can already have some of the transactions already entered manually, and always check to see if the "R"econcile brings up the right transaction, if so reward it and select "R"econcile otherwise correct it by manually selecting the proper account with the "A" option. Keep in mind you have to double click either side of the "A", "U", "R" box's to see what it is matching to. Right now all it knows is all imported transactions balance with "Imbalance-USD". Think of it as a 2 year old who's favorite word is "NO", latter it will be "CAN I HAVE THIS ONE?". Until it "learns" to associate a specific payer to a specific payee you will have to manually assign each transaction with the "A" option. Like David said, the matching routine is not perfect so you need to double check each transaction as you accept the match. QIF works a lot differently. And when you get to importing PayPal at individual transaction level, well aspirin and a ball bat help. --JEffrey Black M.B.A. ________ From: gnucash-user on behalf of David Carlson Sent: Monday, September 25, 2017 9:23 PM To: Warner Losh Cc: gnucash-user@gnucash.org Subject: Re: Merging imported transactions The OFX import assistant includes a step to match newly imported transactions to existing transactions, as you probably noticed. The problem is that it is error prone and has to be monitored carefully during the import process. I prefer to import a small number of transactions at a time so that I am less likely to be overwhelmed. The program is supposed to learn from previous imports and improve it's accuracy eventually, but in my experience it still seems to make a lot of mis-matches. I still end up with duplicates when it fails to find the correct match or I just get tired of searching and just let the duplicates through. I am less likely to miss a transaction if I use the import assistant, so I continue to use it. David C On Mon, Sep 25, 2017 at 11:00 AM, Warner Losh wrote: > Greetings, > > I download all my transactions from my back in quickbooks OFX format. I > import them into gnu cash. This works well; however, I’ve hit a snag. > > I have a credit card, a paypal account and a bank account. These all have > different formats for downloading the data from, which I can convert to > something gnu cash can import. When I do this, though, I have a small > problem. The bank account comes in via OFX, so has lots of Imbalance-USD > entries. I’ve used this account for the import of some items from the > credit card and paypal accounts as well. So I wind up with a transaction in > the bank ledger that is a payment to the credit card, but doesn’t credit > that account. There’s also a separate transaction in the credit card that’s > a payment from the bank, but doesn’t debit the bank account. In a sense, > there’s two double entries I’d like to combine to eliminate the > Imbalance-USD account entirely since I don’t think that’s a real account > and find it sloppy book keeping. Is there anything I can use to merge > entries when I’m replying on downloading the bulk of the data rather than > entering it myself? > > Thanks for any help you can give me here. > > Warner > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-user gnucash-user Info Page<https://lists.gnucash.org/mailman/listinfo/gnucash-user> lists.gnucash.org This (occasionally high-volume) mailing list is devoted to the discussion of general accounting topics as well as how to use GnuCash to implement specific > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-
Re: Merging imported transactions
The problem I have is that the bank has OFX files. But AmEx and PayPal do not (well, AmEx does, but I'd have to pay extra and it's for a business that's not making enough money to pay extra). I've hacked together some python code so I can import transactions this way via csv lists (which I wish there was an option to say 'use line 1 for the headings, they match the strings you are expecting'). I don't want to DELETE one of the transactions, I want to MERGE them. Why? Because the Bank side of the transaction has the bank's confirmation number and some other information. And the other side of the transaction has things like who the seller was and what I bought. I'd like both bits of information preserved. And trying to cut and paste between notes has proven to be somewhat difficult due to GTK "issues" I was able to do the QucikBooks 2007 -> GnuCash migration based on https://lwn.net/Articles/729087/ coupled with several bug fixes for stupid things like commas, two digit years, etc. I've been able to automate my monthly rent transaction posting by copying old transactions and setting the date. I haven't found a good python OFX parser yet, or I'd take a stab at trying to match the transactions as well and create fully-formed entries. With the OFX importing and python scripts, I was able to catch up with a year backlog in a couple of hours, despite the difficulties (a chore that usually takes me days). I'd hoped to find some way to take it a step further to automate the tedium further (though to do that, I'd need some kind of spider that downloads the data from three different sources as well)... Maybe next month i'll try loading the .csv transactions first to see if that helps... Warner On Mon, Sep 25, 2017 at 8:23 PM, David Carlson wrote: > The OFX import assistant includes a step to match newly imported > transactions to existing transactions, as you probably noticed. > > The problem is that it is error prone and has to be monitored carefully > during the import process. I prefer to import a small number of > transactions at a time so that I am less likely to be overwhelmed. > > The program is supposed to learn from previous imports and improve it's > accuracy eventually, but in my experience it still seems to make a lot of > mis-matches. I still end up with duplicates when it fails to find the > correct match or I just get tired of searching and just let the duplicates > through. I am less likely to miss a transaction if I use the import > assistant, so I continue to use it. > > David C > > On Mon, Sep 25, 2017 at 11:00 AM, Warner Losh wrote: > >> Greetings, >> >> I download all my transactions from my back in quickbooks OFX format. I >> import them into gnu cash. This works well; however, I’ve hit a snag. >> >> I have a credit card, a paypal account and a bank account. These all have >> different formats for downloading the data from, which I can convert to >> something gnu cash can import. When I do this, though, I have a small >> problem. The bank account comes in via OFX, so has lots of Imbalance-USD >> entries. I’ve used this account for the import of some items from the >> credit card and paypal accounts as well. So I wind up with a transaction in >> the bank ledger that is a payment to the credit card, but doesn’t credit >> that account. There’s also a separate transaction in the credit card that’s >> a payment from the bank, but doesn’t debit the bank account. In a sense, >> there’s two double entries I’d like to combine to eliminate the >> Imbalance-USD account entirely since I don’t think that’s a real account >> and find it sloppy book keeping. Is there anything I can use to merge >> entries when I’m replying on downloading the bulk of the data rather than >> entering it myself? >> >> Thanks for any help you can give me here. >> >> Warner >> >> ___ >> gnucash-user mailing list >> gnucash-user@gnucash.org >> 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 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.