I am a first-time user getting set up. I have used the CSV bulk import template to manually add historical transaction data for two banks (four asset accounts - a chequing and savings at each institution) and for two credit cards (liability accounts - credit card). The asset accounts have negative values for credits and positive values for debits, and the reverse for the liability accounts i.e. the credit card payments are negative amounts in both the chequing account and the credit card transaction record. All the transactions are in the single Transactions sheet, and all the payments for monthly credit card bills are marked as Transfers.
Here’s my problem: when I create a P&L report, it seems like asset and liability accounts are treated the same way, and expenses within a category appear to be summing by their “face value” of positive or negative, without regard to whether the expenses were paid directly from the chequing account, or paid with the credit card.
I feel like I must be missing something obvious here about how I am entering transactions or generating reports?
thanks very much for your help.
When you use your credit card, the category should be whatever it is you’re using it for.
Then, when you pay your credit card (if it’s paid in full), you use transfer from your bank and then transfer to your card. This way, the only thing you see is the actual usage on the card.
Day 1, Cat: Caffeine, Amount: -$2.50, Institution: Credit Card
Day 29, Cat: Transfer, Amount: -$2.50, Institution: Bank
Day 30, Cat: Transfer, Amount: $2.50, Institution: Credit Card
Monthly Spending: $2.50
What you’ve described is exactly what I’m doing, but the issue is that by default, the first transaction on the credit card shows up on the CSV statement as “2.50”, and not “-2.50”, and the bill payment as “-2.50” rather than “2.50” because it’s a liability account and not an asset account. But if it’s entered in the Transactions sheet that way, it results in incorrect sums in the P&L because asset and liability accounts seem to be summed up “at face value”.
For the P&L Report to work the way you want, you’ll need to stick with the convention of expenses being negative in your Transactions sheet. If you are importing transactions manually via CSV with a bulk import template, I’d consider making the import-ready Amount column derived off of the raw Amount from your data source. The derived version can then manually or via-formula flip the sign on credit card transactions.