deposit/withdrawal amounts in CSV/Excel files
CSV/Excel files may amount of a transaction supplied in various ways:
- one amount column with positive and negative amounts (could be reversed for credit card files, see an explanation below)
- separate debit (withdrawal) and credit (deposit) columns (the amount sign does not matter for this layout)
- one amount column and another column (Record type) with words like DEBIT/CREDIT/DR/CR (amounts are usually positive
Bank account (checking, savings) and credit card transactions may have a different meaning in CSV files. Some credit card companies provide CSV files with positive amounts for purchases and negative amounts for credit card payments.
Some credit card companies provide CSV files with positive amounts for purchases and negative amounts for credit card payments.
Bank account files usually have negative amounts for withdrawals and positive amounts for deposits.
To avoid confusion, accounting software like Quickbooks, Quicken, MS Money and others as well as financial format specifications like OFX, QFX, QBO, QIF have one way to for transaction amounts regardless if it is credit card or bank account:
- if a transaction takes money from an account (bank account withdrawal or credit card purchase), it must be negative (minus)
- if a transaction puts money into an account (bank account deposit or credit card payment), if must be positive (plus)
For the layout #1 above, all CSV converters follow this rule treating negative amounts as taking money from an account and positive amounts as putting money into an account. All CSV converters have "Change +/-" button to reverse the sign for all amounts for the whole file as one click action.
For the layout #2 above, if a CSV column is mapped as Debit (withdrawal, credit card purchase), the sign of amounts is not considered as the whole column is declared as negative.
For the layout #3 above, if a transaction Record Type is Debit (withdrawal, credit card purchase), the sign of amount is not considered as the transaction is declared as negative.