topic Re: Tracking CC Rewards & Expenses in Personal Finance

Tracking CC Rewards & Expenses

robsteriam — Mon, 27 Feb 2023 23:29:16 GMT

Hello,

I'm curious about what people use to track their credit card expenses and rewards. I currently use custom spreadsheets with very little automation. I have considered using 3rd party applications such as rocket money, mint, maxrewards, awardwallet, etc. However, I'm concerned with providing my financial information to companies. All my credit cards are set to autopay statement balance on the due date, not sure if I should change that to the close date.

Do you use a 3rd party application or custom spreadsheets?

Re: Tracking CC Rewards & Expenses

SeaProbe — Tue, 28 Feb 2023 00:17:04 GMT

I tried the MaxRewards app but was uncomfortable with linking my accounts. Further, it required - at least for one issuer - my password every single time it updated. The risk of sharing my bank login credentials with a relatively unknown developer was too much, so I initially I unlinked my accounts. Of course, that made the app pretty useless. It is now deleted.

I do have my bank and investment accounts linked in Quicken anyway, so now I just use that with some custom reports. The only real limitation with this approach is my Apple Card which cannot automatically download… I have to pull down an import file once a month when the statement cuts.

Re: Tracking CC Rewards & Expenses

Zoostation1 — Tue, 28 Feb 2023 04:57:32 GMT

For expense and spending tracking I just made myself a pretty basic Excel spreadsheet that I update most days of the week. I do put my rewards into that sheet but don't do anything special to tally them up. I just spend 5-10 minutes a day 5-6 days/week to check all my accounts.

Re: Tracking CC Rewards & Expenses

markbeiser — Tue, 28 Feb 2023 05:38:16 GMT

@SeaProbe wrote:
I tried the MaxRewards app but was uncomfortable with linking my accounts. Further, it required - at least for one issuer - my password every single time it updated. The risk of sharing my bank login credentials with a relatively unknown developer was too much, so I initially I unlinked my accounts. Of course, that made the app pretty useless. It is now deleted.

Even if you get over letting the developer have your data, having to manually go through the 2 factor ID to sync the app, and the number of cards not supported at all, is kind of deal killer to its usability...

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Tue, 28 Feb 2023 22:58:53 GMT

I don't track credit card rewards.

I don't track expenses related to credit cards either. But it isn't clear if "credit card expenses" is referring to expenses relating to having credit cards or if you mean "credit card expenses" as purchases to be tracked (spending) and NOT related to credit cards.

I DO track "expenses", which I think of more as "spending". For that, I use a spreadsheet. I enter things on one tab, and view the summary on another tab. I've arranged it so I can easily copy-paste the values into my "master planning document" without much trouble.

I use a new "monthly spend tracking" spreadsheet every month. One tab is for entry, the other is for a nicely formatted view of spending by category and sub-category, with things summed up appropriately. It's formatted to make a nice single-page PDF doc that I can save for future reference.

BESIDES THAT, I have my "master planning document". It tracks all kinds of stuff and is VERY complicated, uses MASSIVE AMOUNTS of complex formulae and conditional formatting to do various things. It tracks all checking and savings credits and deposits, spending from the "monthly spend" documents, utility bills, income & deductions (from paycheck), etc. It also has my budget and various other things. I use it DAILY. I have refined it over the years and couldn't live my financial life without it at this point. It is very robust and feature rich.

Among other things....it has a "checkbook register" style worksheet for every month of the year that is "read only". It uses entries on the main entry page to produce the displays and colour codes things based on whether they are unscheduled, scheduled or paid, uses formatting to highlight the lowest anticipated or actual account balance in the month, different formatting if a debit will make an account go negative or below a threshold amount, and various other things.

Overall, it makes understanding my financial position now or on any future day of the year very clear, ensures that all spending is tracked, ensures that all payments are made on time and generally that nothing falls between the cracks. All I really have to do is keep it current with data entry, which is a low burden.

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Tue, 28 Feb 2023 23:09:47 GMT

Regarding my post here are some screen shots of the monthly spend documents. This one is for Feb 2023 and is complete (no more spending today).

The data entry tab looks like this:

You will note that the "Buyer" and "Category" are chosen from drop-down menus, for data integrity. The categories also line up with my bud worksheet, which I will show in another post

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Tue, 28 Feb 2023 23:16:09 GMT

The "Planning Document" has multiple tabs. The foundation of course is income - information from our paystubs. It uses formulae to make things easy and to minimize data entry. Fundamentally, I enter the "baseline info", and the values for every month get determined by the number of payperiods in that month and calculated. It looks something like this:

There is another part in the bottom that you can't see, that has info for bonuses, stock sales, account disbursements, the related taxes and deductions, etc.

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Tue, 28 Feb 2023 23:28:25 GMT

The next most important worksheet is the "payment matrix". This is basically where all the data entry is. A couple of snippets from this one:

The "Variable Expenses" are copy/pasted from the monthly spend documents. The order is the same, to make it easy.

I think you get the idea here.

The "rank" of a payment (the absolute order from 1..N for the month) is determined by this formula:

=IF(I5="","",IFERROR(RANK.EQ(I5,I$5:I$115,1)  +  COUNTIF(I$5:I5,I5)-1,""))

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Tue, 28 Feb 2023 23:42:49 GMT

All of THAT info is used in the "Monthly Checkbook Register" tabs. There are 12 of these, one for every month. These are READ ONLY and basically provide a convenient, easy-to-read view of the information in the payment matrix. Here is one:

# is the rank - the absolute order from 1..N

#"D" is the date in the month the item occurs

"S" is the status: unscheduled (white), scheduled (blue) or paid (red, and it means it has entered/exited the account)

"Description" is automatic colour-coded based on the TYPE of thing it is (income, transfer, deposit, credit card, etc.)

The "Checking" and "Savings" columns have formatting to indicate the "low point" in the month, and if a floor value is reached.

There are some complex formulae in here. Some of them include:

=VLOOKUP($A$1,Balances!$A$3:$H$14,2,FALSE)

=MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255)

=IFERROR(INDEX(INDIRECT("Due_"&@INDIRECT("A1")),MATCH($A7, INDIRECT("Rank_"&@INDIRECT("A1")),0)),"")

=IFERROR(IF(INDEX(Account,MATCH($A10, INDIRECT("Rank_"&@INDIRECT("A1")),0))=E$3,INDEX(INDIRECT("Charge_"&@INDIRECT("A1")),MATCH($A10,INDIRECT("Rank_"&@INDIRECT("A1")),0)),""),"")

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Wed, 01 Mar 2023 00:39:41 GMT

Finally, thre is the Budget Plan. This uses info already entered elsewhere and is READ-ONLY. Everything is entered or calculated elsewhere is presented in an easy-to-read format that summarizes things nicely.

"Budget values" are pencilled in for the year, and get replaced with actual spend as the year progresses (I will update for February tomorrow), and at the end of the year I end up with a good record of what money went where.

So........that's how we do it (:

Re: Tracking CC Rewards & Expenses

MarkintheHV — Wed, 01 Mar 2023 00:40:57 GMT

I am lazier than @ privacyadvocate69 You can get everything at https://www.tillerhq.com. I have used their spreadsheet for years now and love it. It doesnt do rewards, but, they are a pretty good bunch over there that will help you out if you screw something up. With them, you can choose their product on google sheets or excel.

Re: Tracking CC Rewards & Expenses

Anonymous — Wed, 01 Mar 2023 04:41:47 GMT

@privacyadvocate69 wrote:
All of THAT info is used in the "Monthly Checkbook Register" tabs. There are 12 of these, one for every month. These are READ ONLY and basically provide a convenient, easy-to-read view of the information in the payment matrix. Here is one:

@privacyadvocate69

Given your user name, I assume you have modified the data in these sheets. I would hate to waste the time of my ex-GRU buddies to look for a Jane and Dirk Diggler (OK, that is suspicious!) living in PA who work at Nasa and FBI, associated with a Suzi, have a Buick, Jeep, Yamaha and Honda, living somewhere near Juliana's Restaurant, Hovis Auto Supply etc etc

Re: Tracking CC Rewards & Expenses

robsteriam — Wed, 01 Mar 2023 07:43:12 GMT

Thank you so much for sharing.

Re: Tracking CC Rewards & Expenses

Aeon — Wed, 01 Mar 2023 10:58:52 GMT

I use YNAB to track both. I have a budget category/line item called "Interest/Cashback/Rebates/SUBs" and put all money I receive from those sources for the month in that line item. It's not difficult for me to parse out the CC rewards in the reports.

(I use an older version of YNAB that doesn't have the option to sync to financial institutions. Users of the current app have the option to sync via Plaid. Manual entry is either a deal-breaker or "no big deal" for many YNABers.)

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Wed, 01 Mar 2023 12:21:36 GMT

The important values have been edited (;

I can't believe you don't know who Dirk Diggler is (;

Re: Tracking CC Rewards & Expenses

Anonymous — Wed, 01 Mar 2023 12:33:33 GMT

@privacyadvocate69 wrote:
The important values have been edited (;

I can't believe you don't know who Dirk Diggler is (;

Good. Note the (OK, that's suspicious) after the name!

Re: Tracking CC Rewards & Expenses

TheRedHat — Wed, 01 Mar 2023 17:48:08 GMT

*looks at the various charts*

I just use YNAB, record the cash back, and move on. *shrug*

Re: Tracking CC Rewards & Expenses

robsteriam — Wed, 01 Mar 2023 22:59:55 GMT

That's good to know. I can take advantage of my student status to get YNAB for free for 1 year. I will look into that.

Re: Tracking CC Rewards & Expenses

robsteriam — Wed, 01 Mar 2023 23:02:21 GMT

Do you happen to be an accountant? Those records are extremely detailed. I essentially have a "budget" sheet and track recurring/large purchases and put misc for everything else. How much time do you think you spend on updating the information?

Re: Tracking CC Rewards & Expenses

privacyadvocate69 — Thu, 02 Mar 2023 11:33:40 GMT

@robsteriam wrote:
Do you happen to be an accountant? Those records are extremely detailed. I essentially have a "budget" sheet and track recurring/large purchases and put misc for everything else. How much time do you think you spend on updating the information?

Me? No, not accountant. Just a project manager.

I only spend a few minutes a day updating info. That's all it takes, since the spreadsheets are already set up (which originally took hours, and have been refined over the years).

Basically, it amounts to two things:

1). What bills do we have and how do we ensure everything is getting paid?

2). Where does our money go?

The ability to forecast income and bill payments months into the future is very helpful.

We spend virtually all of our "daily variable expenses" on a credit card, so it's easy to track. The location of the spend general indicates what it is...for example Aldi and Costco are groceries, unless wife tells me she got something else there.

Overall, it's pretty easy at this point.