Staying organized: A sheet for gift card purchases

Greg recently wrote a post about The Tools We Use (for playing the game) that talked about many of the tools we use to earn more miles and points. In email chatter between the three of us (Greg, Stephen, and me) there was a lot of talk about various tools we use to stay organized in this hobby of collecting miles and points. One tool that #TeamFM relies upon highly is Google Sheets, which is a free web-based spreadsheet app (like Microsoft Excel, but free). Before I worked for Frequent Miler, I had a very basic understanding of how a spreadsheet worked. After almost two years here at Frequent Miler, I still feel like I have a very basic understanding as compared to Greg’s mastery of all things spreadsheet, but I’ve come to use them to stay organized. That is especially important for anyone doing any level of manufactured spending – small mistakes can wipe out the value of any rewards being earned. Today, I wanted to share a simple spreadsheet that could be used to help keep track of gift card purchases.

 

In our Manufactured Spending Complete Guide, we list a lot of different techniques – form healthy & easy to risky, difficult, or some that are totally dead. One technique listed under the “Healthy

but risky, difficult, or local” heading involves buying certain debit gift cards and using them to purchase money orders. This technique is highly location dependent; in some areas it is difficult or impossible to purchase either debit gift cards with a credit card or money orders with a gift card or both. Beyond that, there is the chance (likelihood in some cases) that your bank account may be shut down for depositing many money orders, etc. It’s worth reading the pertinent information in the Complete Guide before diving in (and even then it is advisable to crawl before you run).

That said, I know there are readers who use this technique. Just the other day in our Frequent Miler Insiders group, a member lamented the fact that he thought he had lost a couple of valuable gift cards. My stomach sank for them because that’s a fear that anyone who has bought gift cards has probably shared at some point (and in fact, we had gone through a brief similar scare in my household only a few nights prior before finding the cards in question had just been “put away” in the wrong place).

When I spoke at the Chicago Seminars in October, I mentioned the fact that I use Google Sheets to keep track of the cards I buy and how I use them. A couple of people asked me if I could share the spreadsheet with them. In the shuffle in the days following the seminars (I went from Chicago to the Conrad Fort Lauderdale, where I’ve previously shared that my family was taken down by what they are convinced was The Plague), I lost email addresses and thus hadn’t been able to share it with those who had asked.

Today, I’ve created a sharable version of my sheet that you can access here. To use it, you’ll first want to make a copy (File–>Make a copy) so that you can edit the cells as necessary.

The sheet itself is fairly basic. It includes columns for things like the date purchased, store, receipt number, etc. I record all of that information in the hopes that it might be helpful should I lose something and catch it fast enough to try to have the card replaced.

The column that says “Activation last 4” is where I record the 4 digits that show up on the gift card activation receipt. The “Card Number” field is one I’ve included but I think may or may not be wise to use. Personally, I wouldn’t want to record the full card numbers of a bunch of gift cards on a spreadsheet that is available via the Internet. That said, if you use some type of encryption (I believe Google Sheets does offer the ability to encrypt it), your comfort level may vary. The reason the column exists in the sheet is two-fold: by having the numbers recorded, you have the information you need to get the card replaced if you misplace it (and catch it before someone else uses it). You can also have the information you need to check balances and be sure that you’ve fully used your cards. If you’re going to record those numbers, I’d want to have your sheet password-protected with a strong password at the very least. Alternatively, you might consider keeping the sheet locally via Excel or something like that.

The column that says “Credit Card” is where I’d record which card I used to make the purchase. I have the sheet set up to then automatically fill in the “Rewards Rate” column based on the second tab of the sheet (titled, “Earn” at the bottom). You’ll see that tab is totally incomplete — I only list a few credit cards. That’s for two reasons: First, we list more than 150 cards on our Best Offers page and I was not going to enter all of them in the “Earn” tab. Second, I thought the sheet will likely be more useful if you spend a little time customizing it. Enter the cards you have on the “Earn” sheet and their base earning rate (1x, 1.5x, etc). Then, make sure to sort that sheet A->Z in column A (if you don’t sort it, Sheet 1 won’t populate the rewards rate properly):

Once you’ve sorted it, you should be able to go back to Sheet 1 and enter your card names and it will populate the base earn rate. Back in Sheet 1, column “N” is labeled “bonus”. This is where you would enter any bonus category points. For example, the Ink Cash card earns 4 additional points per dollar at Office Supply stores, so you’ll see that I’ve added 4 under the “bonus” section of row 5.

The formula in row O should then calculate the number of rewards you should have earned from your purchase. From there, following further to the right on the sheet gives you the ability to enter information about how you’ve used the card and at what cost so that you can calculate your cost per point.

You’ll notice the “#N/A” under columns M and O. That’s because I dragged the formula in those columns down — you can always copy the formula onward as you run down the sheet (right now, it will fill in those spaces as you enter the relevant numbers in those rows).

Note that this sheet is simple and isn’t exactly the same as the one I use, but it’s a starting tool for those who may find themselves in need of a bit more organization. The fun thing about Google Sheets is that you can totally customize yours to fit your needs, adding formulas and changing or deleting columns. A key to organization is making it your own so that it makes sense to your brain, so I expect that many people will have a different way to organize and/or want more or less detail, etc.

It’s also worth noting that these days, Amex has been known to deny welcome bonuses to those who it deems have met the spending requirements by purchasing gift cards. Also note that not all gift cards have PINs. Again, I’d suggest reading the guide and experimenting to see what works/doesn’t in your area. I’d also suggest being careful not to get too invested in something that can change at any minute. Have a backup plan or two or three when you buy gift cards.

I’d also love to get your suggestions / additions / changes / customization ideas as this is a document that could surely be improved upon. Again, it’s meant as a basic organization tool — and one that think everyone should develop in some form if you’re going to participate in collecting credit card rewards. Staying organized is one of the most important tools of the game, whether for these purposes or others and I’ll look to highlight some other organization tools in weeks to come.

Last updated on March 31st, 2019

Regarding comments: Comments posted at the bottom of Frequent Miler pages and posts are not provided or commissioned by the bank advertiser. Responses have not been reviewed, approved or otherwise endorsed by the bank advertiser. It is not the bank advertiser’s responsibility to ensure all posts and/or questions are answered.

About Nick Reyes

Nick Reyes is a (fairly) regular guy with an animalistic passion for maximizing the value of miles and money to travel the world in comfort and style. There is little in life that he loves more than finding a fantastic deal and helping you shop smarter & harder to achieve your travel dreams.

More articles by Nick Reyes »

10
Leave a Reply

avatar
8 Comment threads
2 Thread replies
10 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
DJGodeskyDaveZipZapKaZoomiamamyMark Schroeder Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of
MSer
Guest
MSer

Anyone who does any meaningful volume would spend more time doing this than it’s worth.. And it won’t help if you lose/misplace gcs in any event. Small-timers might start with this system, but it’s not necessary.

CaveDweller
Guest
CaveDweller

Good post I’m staying away from Amex just for life or till a Super Deal comes up . I have my points list Nailed on my wall then current CC’s and when to cancel ..I got 3 sheets on a clip board with the websites and pw’s which have saved me a lot time looking for them .
Less Time,Stress is always better so u can watch for a Deal..

CHEERs

Radster
Guest
Radster

FYI Missing a vital component ie. tracking the MO deposit back into your bank account.

iahphx
Guest
iahphx

I guess everyone is different, but I kind of think the best system is one where you only deal with a handful of GCs at one time — a number that you can keep track of in your head — and you have an organized, repetitive system to keep track of the cards (like you always put unused cards immediately in the same place, like your wallet). Personally, I also have a “set procedure” to deal with gift cards once I’ve drained them (it’s not complicated – I put them in my back pocket!) and then dispose of them promptly to avoid any possible confusion. Basically, I close the loop fast so I don’t have to think too hard about it (or have cash instruments lying around).

Mind you, this is “hobby spending,” and anyone doing this as a “job” where they’re moving lots of money around would have to have a better system.

One wrinkle is if you buy Visa or MC gift cards online and have to wait for them to arrive. I find that you do need to keep better track of these cards given the delay in receiving them. Personally, for those just starting out, I think it’s best to buy the GCs in stores where you get them immediately and can drain them immediately, giving you less to worry about and think about.

Mark Schroeder
Guest
Mark Schroeder

Hey Nick, I briefly met you at the DC FTU a few months ago. I love the work you guys do but am cringing at the incorrect grammar and misspellings. When you say more than two, it’s “among,” not between, as noted in your post above. Also, you misspelled “shareable.” Sorry, I just can’t help but to notice these errors. I am probably the only one to see these, but that’s how I am wired.

Keep up the good work, otherwise. I have been reading for years.

DJGodesky
Guest
DJGodesky

New at this – just responded I guess with a tweet?! I’ll get the hang of this. Anyway, my solution has been to use a business card wallet that is large and zippers shut, to keep all my GCs in categories and by vendor, it’s my go-to place before pulling out a credit card. Used up ones don’t go back in. The wallet has slots and sleeves, slots for cards, and print out eCards and put the paper into the sleeves. Working so far. Will give the spreadsheet a try.

iamamy
Guest
iamamy

I would love to see a picture of the type wallet you are using. Do you mind sharing?

DJGodesky
Guest
DJGodesky

Sure, but I don’t see a way to post photos to this string of comments. Sorry.

ZipZapKaZoom
Guest
ZipZapKaZoom

This is a great idea. You do need to track bank balances to avoid over-drafts and to how much you owe at all times to make sure you don’t forget a payment. And Google Sheets is a really great tool for tracking purchases and balances! I can see the numbers on my phone or laptop and know immediately when I accidentally drop a VGC under my car seat or forget and don’t deposit a MO.

But I agree with other commentators, this is just way too complex. Some days I buy and sell $10,000 worth of GCs and keeping track of every single card would mean that I would spend insane numbers of hours a day doing tedious paperwork.

I keep a single sheet with columns for every CC balance on one side and assets on the other. First purchases are added CC balance on one side and to the GCM or GCc balance column on the other then they are transferred the inventory column when they arrive and then to the bank balance column when the checks are deposited and finally I make a deduction from both sides of the spreadsheet when I pay off my CC. If I need detailed records, I can always consult my MO purchase receipts.

Dave
Guest
Dave

this is really a waste of time, low return on time spent. even if you are doing a handful of MS per year, this really kills time sitting in front of a computer filling out an excel spreadsheet that you will never see again.