Running the CSV importer on the Citi file that had fields: Status, Date, Description, Debit, Credit, MemberName I mapped Date --> Date, Description --> Description, Debit --> Deposit, Credit --> Withdrawl Noticed that there were three rows with amounts in the Credit column and all said values where negative! Ran the importer and saw that those three rows were treated as charges. Could I map both columns as Deposit? Counter-intuitive but so is Citi Cards right now! This would be semilar to pre-processing the file to put both fields as one (since Citi put negative values into the "Credit" column).
I'm a very new GnuCash user and came across this same issue with another bank. I also tried setting multiple deposit/withdrawal columns but found that it wouldn't permit it. How about an "ignore sign" option on the CSV importer? I'm willing to try to implement that if others agree it's a reasonable way to handle this case.
Thank you for your offer. I'm however working at allowing multiple Deposit and Withdrawal columns. The basics are in place on my local system. Before I can commit it though there are a few details to iron out: 1. I want to show a notice when more than one deposit or withdrawal column is selected. It would not be a blocking error, but it could be very confusing if a user did this accidentally/unintentionally. A visual feedback in the notifications would somewhat alleviate this. 2. I'm trying to come up with better names for "Deposit" and "Withdrawal" as they don't map very well to all use cases. Yet I'd like to be able to limit the names to only two. If a csv file has separate columns for deposits and withdrawals and the amounts in both columns our listed as positive numbers, the column names match the csv file contents. However as in case of this bug report some banks also use two columns but will list withdrawals as negative numbers. In that case the importer's internal logic would require you to select two deposit columns (which currently is not possible yet). And if the csv file has one column with positive and negative amounts, this column would hold both deposits and withdrawals, but you should choose "Deposit" as column name. As a better match I was thinking of "Amount" and "Amount (invert sign)" which would be a more accurate description of how gnucash will interpret the values in these columns. But a native English speaker may be able to come up with a more concise way of expressing that. Note that "Negative Amount" may be too ambiguous. And I want to double check amount maps properly to how gnucash stores these values internally. For single currency transactions this is probably not too important. But I have little experience with multi-currency or even commodity transactions. I know internally there's an amount and a value. I will need to figure out how to properly map to those and I'm not sure "Amount" is the right choice in the csv context. Perhaps it should be "Value" and "Value (inverted sign)" ? What if I later extend to importer to handle stock transactions as well ? Are both amount and value sign sensitive in GnuCash ? (I realize this is more geared towards the other devs than the OP or commentors)...
Thank you for replying, I'm glad you have taken this issue up. I'm ambivalent toward (1). WRT (2), I fully agree "Deposit" and "Withdrawal" are extremely confusing unless you importing to an account type that uses those terms. Bank accounts use "Deposit" and "Withdrawal", but other account types use different terms. The first data I imported was credit card transactions. Credit card accounts use "Payment" and "Charge" and I did not correctly map those to "Deposit" and "Withdrawal" the first time I tried to import. I think the terminology should be based on the type of the selected account since this is exactly how GnuCash will interpret the value, perhaps even for stocks. I also wonder if it would make sense to ignore the negative sign if both column types are selected, but I haven't thought through all of the cases. A preview feature would be helpful as well, but that's probably outside the scope of this particular issue. Again, I'm a very new user (less than 24 hours at this point), so I may be missing something obvious.
(In reply to yogensha from comment #3) > Thank you for replying, I'm glad you have taken this issue up. > > I'm ambivalent toward (1). > > WRT (2), I fully agree "Deposit" and "Withdrawal" are extremely confusing > unless you importing to an account type that uses those terms. Bank > accounts use "Deposit" and "Withdrawal", but other account types use > different terms. The first data I imported was credit card transactions. > Credit card accounts use "Payment" and "Charge" and I did not correctly map > those to "Deposit" and "Withdrawal" the first time I tried to import. > > I think the terminology should be based on the type of the selected account > since this is exactly how GnuCash will interpret the value, perhaps even for > stocks. While that's an interesting idea it is only valid for a limited set of use cases, namely those where the account to import into is known beforehand by the importer. This is not always the case. The importer supports csv files in which a certain column defines the account to import into. This column is only interpreted in a later step of the import process, so during the column selection it won't help the import to determine which column types to offer. It would be possible always present the Deposit/Withdrawal and Payment/Charge column types as options and leave it to the user to select the proper one. But even that fails in certain imports as the importer is also capable of importing transactions between arbitrary accounts in the same file. Imagine there's one transaction for a bank account and one for the credit card account. Which headers should I then choose ? "Deposit/Withdrawal" or "Payment/Charge" ? No matter what solution we try to come up with the user will always have to apply a mapping from one representation system (the csv file) to another (gnucash). As we don't have much info to work with upfront, I prefer to use very generic names and let the user do this mapping. It may still be ambiguous to say "Amount/Reversed Amount", but to me it's at least applicable in all scenarios where "Deposit/Withdrawal" really only covers one and is misleading/confusing in all other scenarios. Most people repeatedly import the same type of csv files time and again, so while the first time it may require some experimentation, once the correct parameters are set, this can be saved for future imports anyway. > > I also wonder if it would make sense to ignore the negative sign if both > column types are selected, but I haven't thought through all of the cases. > That's an interesting idea as well. It does however nothing to fix the confusing choice of the terms "Deposit/Withdrawal". These terms still won't map very well in case you're not importing bank statements. What it would do is reduce the need to allow multiple debit/credit columns to be selected. On the other hand, allowing multiple debit/credit columns to be selected, opens up even more options than we had before. I can imagine csv files that present values with one column the total amount and a second column a discount. If you don't care to record the discount separately, you can mark both columns as amount and the net amount will be recorded in gnucash. Or import file having a base amount and a tax amount (but no total) where you're not interested in recording tax separately. > A preview feature would be helpful as well, but that's probably outside the > scope of this particular issue. > Agreed. And that would require a(nother) major rethinking of the importer. > Again, I'm a very new user (less than 24 hours at this point), so I may be > missing something obvious. Welcome :)
You could use "funds in" and "funds out" like a Journal register does.
While it's more generic it's not mapping very well to the different csv formats we can deal with. Let me give 3 examples: Date Description Amount 2019-08-16 Deposit 100.00 2019-08-17 Withdrawal -100.00 This is the example of a csv file with only one amount column. Some amounts represent funds in, others represent funds out. A first-time user would probably be unsure what to select as there's no funds (in and out) column type. Date Description Debit Credit 2019-08-16 Deposit 100.00 0.00 2019-08-17 Withdrawal 0.00 100.00 The two-column example where all amounts are positive. This is the easiest: first column is funds in, the second is funds out. Internally gnucash will use the numbers in the first column as they are and will invert the sign on the numbers in the second column. This ensures everything balances in the end. Date Description Debit Credit 2019-08-16 Deposit 100.00 0.00 2019-08-17 Withdrawal 0.00 -100.00 The two-column example where credit amounts are negative (the one that started this enhancement request). The normal reflex would be to still mark the debit column as funds in and the credit columns as funds out. After all that's what these numbers mean right ? Unfortunately that would cause gnucash to invert the sign on the numbers in the credit column so the net effect would be there are now two positive numbers, or two funds in. So also for the third case the terms "funds in" and "funds out" are all but intuitive in the current importer. The terms should really indicate "use the amounts unchanged" or "use the amounts with inverted signs". We could reconsider the idea of ignoring signs if both funds in and funds out are selected. That would eliminate the confusion for example 3. It's still slightly ambiguous for example 1 and in addition we would lose the possibility to import csv files where the signs are backwards (that is generated from some opposite point of view compared to gnucash'). The terms 'Amount'/'Amount (inverted sign)' preserve all these options.
I think it's pretty obvious that separate terms are required for the case where both inputs and outputs are in a single column differentiated by sign, though there's an ambiguity there too: Does a negative value mean a credit or a reduction in balance? In an asset account there's no difference in meaning, but in a liability account they're opposites. In the case of two columns we're looking for terms more user-friendly than credit and debit. The ambiguity of negative values in that case is different: It's possible if perhaps nonsensical to use both a negative value and placing the value in the credit column to mean funds out--iow the negative values should be treated as positive credits and a positive value in the credit column should be treated as a debit. All of which is utterly orthogonal to a more user-friendly column name for debit and credit. It's perhaps worth mentioning that from a user's viewpoint (neglecting transfers) a credit is always a funds out because it matches a debit in some expense account; conversely a debit is always funds in because is matches a credit in some income account. So recognizing all of this, how do we express it in a UI? We need a friendly name for each of the following possibilities: 1. Single column, positive increases balance of asset account and decreases balance of liability account. 2. Single column, positive increases balance of any account type 3. One of two columns for values, positive increases the balance of assets and decreases the balance of liabilities 4. One of two columns for values, positive decreases the balance of assets and increases the balance of liabilities 5. One of two columns for values, positive increases the balance of any account type 6. One of two columns for values, negative increases the balance of any account type In the unlikely event it's not blindingly obvious, type 3 is an accounting debit and type 4 a credit. Funds (or amount) in and out match the user's perception: One receives money with the first and spends it with the second. Geert's case three is a bit odd: It has two columns of type 3 or 5, can't tell which from the one exemplary transaction. It's a bit hard on the little gray cells to accept that that's what's in the file and that the importer should accommodate it, but there shouldn't be any programming obstacle to allowing it.
(In reply to John Ralls from comment #7) > Geert's case three is a bit odd: It has two columns of type 3 or 5, can't > tell which from the one exemplary transaction. It's a bit hard on the little > gray cells to accept that that's what's in the file and that the importer > should accommodate it, but there shouldn't be any programming obstacle to > allowing it. I didn't invent it: reread the OP's first message. Citi bank provides csv files in which credits are in a separate column as negative values.
Having said that, my quest for simple alternative names is turning into a complex topic in itself and is only tangentially related to the original bug request. I won't have time to go into such depth soon and certainly not before the 3.7 release. So I propose to split this discussion off into a separate report and apply my work so far to allow multiple Deposit and Withdrawal columns to be selected. That way the importer at least is capable of importing these types of files and the choice of good names can be done later.
OK.
*** Bug 797173 has been marked as a duplicate of this bug. ***
This bit will appear in GnuCash 3.7
