Transaction Report by Payee - Possible to not show category breakouts?
I moved from Windows to Mac earlier in the year. I have a discrepancy somewhere that is showing a higher online balance than in Quicken. Reconcile feature is no help.
When this happens I usually download transactions from my bank as a CSV file - this time I went back to the beginning of the year. Then I would run a report in Windows showing my transactions for the same date range, then export to CSV. After tuning up the spreadsheets to tighten up formatting differences I would compare line by line to see if I could find the offending transaction(s). Sometimes it's a duplicate, a neglected entry, whatever.
Apparently my big sin here is categorizing my credit card transactions.
When I ran a transaction report in Quicken for the comparison, I noticed it was way longer than the one from my bank - then I realized Quicken broke each credit card transaction into however many category lines happened to be in a given payment.
I'm a Quicken user since 1995 but Mac only since 2023. I know a ton of features is still not there on Mac (especially around reporting), but this is such a basic idea: check number, amount, date? What am I missing?
Comments
-
To put it in perspective - my bank transaction CSV was 172 lines. The Quicken report CSV is 423 lines, and also has no summary amount for a specific check #.
For example, if I pay Visa $1300 on check #3189, and I categorized the expenses from that transaction I could have 9 categories in that split. In that case the Quicken transaction report would show 9 line items for check #3189 which I would have to manually total to see if they match the single line item the bank records for check #3189. It's really hard to reconcile side by side in a spreadsheet when the bank exports 172 line items, and Quicken exports 423 line items.
I typically would color code cells containing the check numbers with matching amounts in my side by side, as not all are always on the same line. That allows me to eliminate the ones that match and focus on items that might take more digging into.
How did the discrepancy get so big? I have no idea. I'm guessing cockpit error as a new Mac user. Maybe I missed something, but it's not intuitively obvious enough to stand out. That's why the side by side comparison
0 -
@MauryJ Okay, there are a few issues here…
[EDITED to correct incorrect information I initially posted]
It is a frustrating shortcoming in Quicken Mac that you can't print or export transaction information without the split lines.
You can export register transactions — not a Transaction Report, but a register export — to a spreadsheet but each transaction with splits shows only the split lines, not the total transaction amount. If you have only a handful of splits, you can do work in the spreadsheet to combine them back into a single transaction, but if you ave more than a handful, it's too painful and time-consuming.
to export register transactions, select the range of transactions you want in your register (click the first, scroll and Shift-click the last, so they're all highlighted), and select File > Export > Register Transactions to CSV File. In the dialog box, be sure to select Export=Selected Transactions Only.
Open the CSV file and you'll initially you have the same problem: each split line for transactions is its own row in the spreadsheet. You can create a new row and sum the split amounts, saving the actual amount rather than a formula. Then sort the spreadsheet by the third column, and all the split lines will be sorted together; select and delete those rows, and what's left is an actual check register, one line per transaction. But again, this is only viable if you manually re-combine splits into a single transaction, making this viable only if you desperately need a check register spreadsheet.
For this reason, there's an Idea thread (feature request) for being able to generate a report of transactions without split lines. Please add your vote and copy-and-past your comments there:
(To add your vote: Click the "View Post" link, and then look for the yellow box under the first post with the vote counter; click the little dark grey arrow under the counter to register your vote.)
I also wanted to touch on something you said above about how you enter your credit card data. You wrote: "if I pay Visa $1300 on check #3189, and I categorized the expenses from that transaction I could have 9 categories in that split." That does work, and perhaps it works best for you. But it's not the optimal way to use Quicken for credit cards. The way most users do it, you'd have a separate account in Quicken for that Visa credit card. In that account, you would capture every credit card transaction; you can download from your credit card company or enter them manually. By recording each transaction, you get a number of benefits: you accurately capture the the actual transaction date of each charge in your records, you can enter memo information about any purchase, you can have splits for any purchase, you see how much you owe at any time in the left sidebar balance. When you pay the credit card bill, you have a simple entry with no splits: enter the payment in your bank account and for the category you use a transfer to the Visa credit card account.
Finally, you said "reconcile is of no help", and I'm curious why. Reconcile should, in fact, allow you to do the exact comparison process you need in order to find a discrepancy, without the need to construct spreadsheets to compare. If your balance is correct as of the prior month, then enter the new balance and date and go to the Reconcile screen. If you've downloaded transactions from your bank, they have blue checkmarks, but if you want to compare Quicken side-by-side with your bank statement, you can click "Mark All as Uncleared" to remove the blue checkmarks. Now, with your bank statement in hand, go down the bank statement one line at a time, clicking the Clr checkbox in Quicken for each item. Along with way, you may find a wrong amount in Quicken, which you can correct, or an item on the statement which is missing from Quicken, so you can enter it. When you reach the end of the statement and marking items cleared in Quicken, the top of the reconcile screen should show a $0 difference, and you can click Finish to turn all those blue checkmarks into green reconciled checkmarks. I do this every month for both my checking account and my credit card accounts, and it works perfectly to lead my to any mistakes I've made in Quicken and matching the real-world account when I'm done. So I'm curious why you said "reconcile is of no help", since what you're doing is a manual reconciliation between Quicken and your checking account.
Quicken Mac Subscription • Quicken user since 19931 -
[Deleted; redundant]
Quicken Mac Subscription • Quicken user since 19930 -
First - thanks! That took a lot of time and is very well explained. Apologies for such a late response. It's been crazy here plus a lot of traveling, so I'm just getting back to it.
Here's my thoughts. First - the spreadsheet idea is a good one. I am thinking I'll create a bunch of summed line items for those transactions on extra line in a neighboring column. Then in the column with the the exported transactions I'll manually transpose the sum from the other column back into the original column so it's not part of a formula and then delete the irrelevant rows. So - a lot of work, but worth it.
Regarding your question of importing credit cards directly, I'll have to experiment with that. In 1995 this wasn't possible (I don't think), and I've just been doing it the same way ever since. I don't even log into my credit cards (I'm pretty sure I have one or 2 logins), so maybe I'll experiment with one to start. It makes sense. I did have a car loan like that where it automatically split the principle and interest and I thought that was pretty cool. I haven't had to set up new institutions for a long time, but last time I did I saw some of my cards pop up - never really thought about doing the work to set that all up. If it saves me labor I'm all for it. Thanks again!
0 -
I think it's a good idea to try one credit card initially to see if it works well and you like it.
Although I do download some transactions in my test file, any my real file, I actually choose to enter transactions manually. I found that my the time I cleaned up Payees and categories, and entered splits on transactions, it wasn't taking me much longer to enter everything manually. But that depends on each user's volume of transactions and patience for manual entry; I also settled into this routine before Quicken Mac had all the tools it does today for Payee renaming and QuickFill rules, and when I had a lot of business expense transactions I no longer do, so I may re-evaluate my process at some point. 😀
Even if you decide not to download transactions, though, I'd encourage you to create a separate account for each credit card. You can enter every transaction, like I do, or enter monthly summary transactions by category if you prefer. An advantage of entering individual transactions is that you have a record of what you purchased from whom and on what date, nothing gets complicated when there are credits/refunds, and at the end of the month paying the bill is a simple transfer transaction instead of grouping and manually adding up transactions from your credit card statement, which can be error-prone.
Quicken Mac Subscription • Quicken user since 19930 -
Great insight. OK - Last things first. I do things a little differently than you. My main focus with banking is categories. We buy the overwhelming majority of things via credit card, so I have not been manually entering transactions within a given card statement. That's just too many line items. What I've been doing is summarizing those line items into categories. Some are recurring, so that's easy, and most aren't. As an example, we might go to the grocery store 6 times, but it may be 2-3 different stores. That will be $X for groceries (totaling those 6 line items), and then on to the next category. When the sums agree with the statement balance, I'm set. If I have something of note in "groceries" that I remember - like a gift card, I might split that out into the gifts category. That would be a rare happenstance. Typically, if there's something of note - a big purchase like a grill, or something I'll want to be able to find later, I'll notate that in the notes field. I do that for a minority of purchases, but with a register that goes back 28 years, I can find pretty much whatever I need to.
Now - back to this crazy discrepancy. I literally just finished the spreadsheet project, and they reconcile (bank vs Quicken export. I did have to add in a bunch of transfers that Quicken didn't export. I probably had that set to ignore transfer because I hate it when Quicken will count that as money spent or income. It skews my category totals and it's useless data for budgeting purposes. Moving money around is not "spend" or "income."
Update: I went back further than YTD (premise was I knew January was clean before I moved over to Mac). I discovered some duplicate transactions and after deleting them, reconciling online vs Quicken came out to the penny! Miller Time!
0 -
PS - don't remember why I said reconcile was useless, but I do remember it seemed nebulous, and this must go back further than one or two statements. I can easily see from my phone using the back app that the nominal number of transactions is not the problem. I'll have to look at that again to see why is seemed so unhelpful.
0
