FWIW the qif failure when "S" field being empty is now fixed for the next release; and will default to the "Unspecified" account. The internal-transfer duplication is not fixed yet.
On Sun, 23 Feb 2020 at 17:10, James Peterson <l...@austin.rr.com> wrote: > Actually that example should be easy. You have three transactions. > The first (and the last) will be simple double entry bookkeeping. > > (At this point, I can either (a) try to fake the QIF or (b) actually > use Quicken to create the QIF. If I do (a), I'll probably miss some > minor details, and you could use that against my saying it's easy, > but to do (b) requires turning on my Windows laptop, bringing up > Quicken, creating a test file, putting in all the accounts, > and the transactions, then exporting a QIF file and bringing that > over to my Linux laptop. Sigh. I'm going to go eat dinner first. > > ... > > So attached is test3.qif, which is the QIF file for what you > described. It's easy to match the transactions. Notice that > when a transfer is done, two transaction records get created, > one for the source, one for the destination. But the source > lists the destination as it's L field > L Category (Category/Subcategory/Transfer/Class) > > in checking: > D2/23'20 > U-100.00 > T-100.00 > NTXFR > PSavings > L[Savings1] > ^ > > and the destination lists the source as it's L field: > > in savings1: > D2/23'20 > U100.00 > T100.00 > PSavings > L[Checking] > ^ > > So you match transactions if (a) the date is the same, > (b) the amount is the same, and (c) both transactions > point at each other. > > So the main problem is if there are more than one transfer > from account A to account B on the same day, for the same > amount. test4.qif shows this. Here we transfer two $100 > amounts from checking to savings1. Now (a) why would we > do that? Well, it shouldn't matter, but in this case I > took the previous case (of savings1 and savings2), deleted > all the savings2 transactions and added a new $100 transfer > from checking to savings1. One is memoed "Grandma" and > the other "Aunt" (so birthday gifts maybe). Looking at > the QIF file, we see that both transactions are memoed > the same too, so we can add to our "matched transactions" > have to (d) match memos, if any are given. > > for checking: > D2/23'20 > U-100.00 > T-100.00 > NTXFR > PSavings > MAunt > L[Savings1] > ^ > > for savings1: > D2/23'20 > U100.00 > T100.00 > PSavings > MAunt > L[Checking] > ^ > > but we have uncovered a peculiarity -- presumably a minor bug > in Quicken. Note that for the transaction that was created with > a memo (Aunt), both are the same. But for the transaction that > was created without a memo, and then the memo was added later > (Grandma), the Memo was not copied to the destination > entry. > > for checking: > D2/23'20 > U-100.00 > T-100.00 > NTXFR > PSavings > MGrandma > L[Savings1] > ^ > > for savings1: > D2/23'20 > U100.00 > T100.00 > PSavings > L[Checking] > ^ > > So for (d) matching memos, we have to elaborate it as: > (d) if both have non-empty memos, they must match. > > Now we do still have the case, suppose the transactions > have the same date, same amount, point to each other, and > have the same memo (or don't have memos). Now how do > we match them? > > It doesn't matter. If we make two transactions (1) and (2) > that have the same date, the same amount, the same accounts, > and the same memos, then match them any way you want. If > you match (checking:1) with (savings:2) instead of (savings:1), > who cares? The two are effectively identical. > > Of course, obviously, we need to only match 1 to 1 -- so once > we match checking:1 to savings:1, we can't then match checking:2 > to savings:1 too. > > So the most difficult matching would be two identical matching > transactions, but one has a memo and the other doesn't and > another in the same situation like: > > checking:1, no memo > checking:2, memo A > savings:1, memo B > savings:2, no memo > > We can match checking:1 with savings:1, and checking:2 with > savings:2, but if we match checking:1 with savings:2 (exactly > the same non-memo), and then we can't match checking:2 and > savings:1 -- different memos. (Actually, if we produce the > memo/no memo by editting the memo after the transactions > are created, and Quicken doesn't "forward" the memo, I wonder > if I can create matching transactions with different memos? > Let's try. We will create the transaction, then edit each of > the two separately.... Yes. See test5.qif -- I could edit the > Grandma transaction to be Aunt in the entry that is in Savings1, > and the Grandma memo did not change. So if memos differ, we may > still need to match the transactions, so matching rule (d) is > a suggestion, but not necessary. > > Now, for Quicken, the transactions are listed in the order that > they are in the transaction registers, and double-entry transactions > will always be created at the same time, and so will appear in the > same order in both lists. So if we have a bunch of transactions > for account 1 and account 2, for the same day, they should match > in order (for the subset that point to each other -- you could > have transactions from other accounts interleaved), so the amounts > should match, and the memos "should" match (but may not). > But QIF files, in general, would not have to be sorted that way, > so probably best not to assume that. If we have one transaction > for $73.47 and another for $23.55, on the same day, and somehow > they are in one order for one account and the other order for > the second account, we should still match them (absent other > transactions for those amounts for those days). I would think > that "most" of the time, the days and amounts make it unique > when accounts point at each other, and order and memo are > unnecessary. So do we really want to code for when order and/or > memo are deciding factors, or just match anyway. > > Now splits are a complicating case. > But it seems a transaction A with splits > 1, 2, 3 should be identical to 3 separate transactions A1, A2, A3 > in the obvious way. So if we map splits into multiple transactions > when things are read in, > can we just drop the concept of a split? > > jim > > > > On Sat, 2020-02-22 at 22:54 +0000, Christopher Lam wrote: > > Re: duplicate internal transfers -- I guess would be difficult in any > > software. Consider the following transactions, *all* in the same book -- > and > > *all* accounts (bank chequing, savings1, savings2) are selected for QIF > > exports. > > > > 10/01/20 Save $100 > > Chequing -$100 > > Savings1 +$100 > > > > 10/01/20 Save $100 > > Chequing -$100 > > Savings1 +$20 > > Savings2 +$80 > > > > 10/01/20 Save $100 > > Chequing -$100 > > Savings2 +$100 > > > > The QIF will have: > > chequing > > 10/01/20 -100 save $100 > > 10/01/20 -100 save $100 > > 10/01/20 -100 save $100 > > savings1 > > 10/01/20 +20 save $100 > > 10/01/20 +100 save $100 > > savings2 > > 10/01/20 +80 save $100 > > 10/01/20 +100 save $100 > > > > And somewhere internal *must* contain logic to marry up the 7 splits > into 3 > > separate transactions *without* an unique transaction_ID. I don't think > it's > > possible to make one safely. The easy answer is to export each account > into > > a separate QIF, and import piecemeal. i.e. after importing chequing.qif, > > import savings1.qif and match the transactions manually, then > savings2.qif. > > Even then I'm not 100% sure the gnucash QIF importer or any other > software > > can handle this safely. > > > > On Sat, 22 Feb 2020 at 19:03, James Peterson <l...@austin.rr.com> wrote: > > > I'm seeing generic problems with QIF importing with > > > both gnucash and Kmymoney and I think skrooge too, > > > that can't handle the semantics of my QIF file. For > > > example with splits and unassigned categories and > > > such. Also, Quicken seems to assume an unnamed cash > > > balance for investment accounts as the source and > > > destination for buy/sell/interest/dividends, which > > > no one seems to handle properly. > > > > > > So I'm currently planning to write a QIF to QIF > > > program that will "clean up" and "simplify" these > > > sorts of things. It can create a named account for > > > the anonymous cash balance (which seems to be what > > > the brokerages are doing anyway), delete zero balance > > > and empty lines, and output a new QIF that doesn't > > > have these problems. Don't know what I can do about > > > duplicate internal transfers -- that's sort of the > > > core of double-entry bookkeeping. > > > > > > I started by looking in github for "QIF file" and > > > have 130 different programs which take in or produce > > > QIF files, so I'll review those first, although most > > > of those seem to be in Python, Ruby, JavaScript, C#, ... > > > > > > jim > > > > > > > > > > > > On Sat, 2020-02-22 at 05:15 +0000, Christopher Lam wrote: > > > > Thank you, very useful. > > > > > > > > We can already try fix the empty-category issue to allow importing, > but > > > is > > > > likely to cause other problems. > > > > > > > > The internal transfers issue is much more difficult. The QIF importer > > > > attempts to detect duplicates whereby the datafile already has QIF > > > > transactions, but not for internal transfers. Hence some more data > files > > > > (e.g. internal transfers between 3 accounts all in QIF, or between 3 > > > > accounts whereby 2 are in QIF etc) are still useful. > > > > > > > > C > > > > > > > > On Fri, 21 Feb 2020 at 18:27, James Peterson <l...@austin.rr.com> > wrote: > > > > > This second QIF file illustrates the original problem I had -- > > > > > it simply "Failed" with no errors or warnings or messages > > > > > of any kind (that I can find). Adding the debug print to > > > > > print each transaction as it is processed in qif-to-gnc.scm > > > > > showed it was a transaction with no category listed. > > > > > > > > > > So this QIF file, test2.QIF, picks up from the previous > > > > > test1 -- two accounts, checking and credit card, the same > > > > > payment from checking to the credit card, but now I actually > > > > > try to buy something with my $400. > > > > > > > > > > First I spend $100 at Fry's for a Flash Drive, but give no > > > > > category at all. So this is a simple transaction with no > > > > > category. > > > > > > > > > > Then I buy two CDs at Best Buy, using a split transaction. > > > > > The split transaction as a whole has a category (Music, as > > > > > a sub-category of Entertainment), but within the split, I > > > > > don't give an additional category, so you get the "S" > > > > > line with no information at all -- just S on a line by itself. > > > > > > > > > > To really make it complicated, we have the two purchases > > > > > I thought I made (Beatles and Adele) (both without categories) > > > > > and then I remembered another Dr. Demento CD. Quicken, when it > > > > > does Split transactions, opens up a window for the Split with > > > > > 30 lines (16 showing and you can scroll down for the next > > > > > 14). Each line has a Category (S), Memo (E), and Amount ($) > > > > > The Beatles was on line 1, the Adele on line 2, then two blank > > > > > lines and the Dr.Demento on line 5, so you have two entries > > > > > with no category, no memo, and $0.00 for the amount. > > > > > > > > > > gnucash can't load this file at all, but I suspect that if > > > > > you fix the "Failed" on the import, the amounts would still > > > > > be all screwed up. > > > > > > > > > > I don't know how to take screen shots on a Windows 10 system, > > > > > but the QIF files is all you should need. > > > > > > > > > > I hope these test cases help. Now I have to go back and try > > > > > to repair my quicken files. One of the most maddening things > > > > > about Quicken is that there is no journal of what I've done. > > > > > If you are typing in January, but forget the new year, and enter > > > > > a transaction, it will immediately be sorted by date and > > > > > disappear from view -- the view is still at the end of the > > > > > account, but the just entered transaction is way back in > > > > > January of the previous year. And woe betide you if your > > > > > fat fingers mess up the year entirely and you don't even > > > > > know what year you typed -- the transaction is safely > > > > > sorted back into 2001 or someplace. And everything is > > > > > automatically updated, on disk, in memory. There is no > > > > > undo, no trace of what you did. I seem to have put the > > > > > wrong category into one of my previously missing categories > > > > > from back in 1985 and now I have accounts that should be > > > > > zeroed out and closed that have $-36K and $-7K balances > > > > > which I need to fix. At least I can diff the QIF files > > > > > to see how they changed in the last few days. > > > > > > > > > > > > > > > jim > > > > > > > > > > > > > > > > > > > > On Fri, 2020-02-21 at 07:09 +0800, Christopher Lam wrote: > > > > > > Sorry to see you go. > > > > > > > > > > > > You have, in one swoop, uncovered 3 issues: > > > > > > > > > > > > 1. S field being empty is not processed correctly. It would > still be > > > > > nice if > > > > > > you could attach some screenshots from quicken, and a tiny qif. > > > > > > > > > > > > 2. Duplicated transactions. This happens because I think qif is > not > > > > > limited > > > > > > to 1 account. If it has more than 1 account and there are > internal > > > > > > transactions, qif importer cannot reliably detect those because > the > > > > > > specification, unlike ofx, does not have a unique transaction ID. > > > Not > > > > > sure > > > > > > whether KMyMoney handles this well. Again a small test case is > still > > > > > welcome > > > > > > to try fix this, including quicken screenshots. > > > > > > > > > > > > 3. Importing stock or multi currency transactions is difficult. > The > > > > > importer > > > > > > does try to handle it. Ditto test case and screenshots. > > > > > > > > > > > > Lastly someone else said gnuc 3.1 managed to import; may be worth > > > > > trying? > > > > > > > > > > > > > > > > > > On Fri, 21 Feb 2020, 1:46 am James Peterson, <l...@austin.rr.com > > > > > wrote: > > > > > > > I appreciate your responses to my postings -- you really helped > > > > > > > me find why gnucash did not like my particular QIF file. > > > > > > > > > > > > > > But once I got past that, it's clear that gnucash is badly > > > > > > > mangling the meaning of the transactions I have. I end up > > > > > > > with an overall balance of -2 million. And my investment > accounts > > > > > > > (like my IRA) with multiple mutual funds are particularly > > > > > > > mangled. Plus treating categories as accounts makes it hard > > > > > > > to concentrate on just the account balances. And it seems that > > > > > > > when I write a check from my checking account to Discover > > > > > > > to cover my balance, it ends up being creditted twice, so > > > > > > > instead of a zero balance, I end up knowing that I paid $518K > > > > > > > over the past 27 years. While I want to be able to find that > > > > > > > out (maybe), it's not something I need to know all the time. > > > > > > > (I realize this is a problem of some kind with double entry > > > > > > > bookkeeping, but that means everything like this is probably > > > > > > > wrong.) > > > > > > > > > > > > > > So gnucash does not look like a suitable replacement for > > > > > > > my use of Quicken. KMyMoney seems to be closer to being > > > > > > > correct, so I'm going to see if I can fix the problems I'm > > > > > > > running into with that, and see if that works better. > > > > > > > > > > > > > > jim > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thu, 2020-02-20 at 11:47 +0800, Christopher Lam wrote: > > > > > > > > Qif importer does have special handling for empty categories. > > > > > Changing > > > > > > > this > > > > > > > > is likely to break things elsewhere though. > > > > > > > > > > > > > > > > It would be useful to attach the minimal qif file from > selective > > > qif > > > > > > > export > > > > > > > > from quicken, and insert screenshots from quicken too. Maybe > > > best > > > > > file > > > > > > > bug > > > > > > > > in Bugzilla. > > > > > > > > > > > _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel