Topic Wiki

Here is my Excel template to keep track of all of my credit card stuff.

The first tab contains the instructions. I included some of my own entries as examples so you will know what to fill in where.

I put in almost everything possible I could think of. Only thing I'm missing that I didn't get around to adding is the spending bonuses by category (e.g. travel, gas, etc.).

Enjoy. If you have any suggestions, comments, or improvements, Please let me know, I'd love to fix it.

Update- when editing, DO NOT modify any of the tables in the Dashboards (except expected money and debts), ONLY modify data in the Credit Card master sheet and Bank master sheet. The Dashboards contain what is called Pivot Tables and they automatically update when you refresh the data.

I'm not sure how to include an attachment in a wiki, so the file can be downloaded from the first post in this thread.
Download
« Last edited by shoobi on October 11, 2015, 09:21:06 PM »

Author Topic: Master Excel Template for keeping track of all Credit Card stuff  (Read 27254 times)

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #60 on: June 23, 2015, 01:45:18 PM »
click on the person's name, then select Send PM

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #61 on: June 23, 2015, 01:50:17 PM »
i confess, i am not as proficient as you are in Excel... it looks the same, what did you change?

forget it, i figured it out. good idea.

https://support.office.com/en-nz/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9

Offline SPLP

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2011
  • Posts: 1702
  • Total likes: 6
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #62 on: June 24, 2015, 01:16:31 PM »
Here you go...
Updated some of the fields

Nice the two of you --I just maybe forced to leave my crude excel that I now use - problem is to find the time to re enter info

Offline srap

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2013
  • Posts: 2821
  • Total likes: 169
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #63 on: June 25, 2015, 01:53:35 PM »
Excellent job and great idea.  I LOVE my excel CC/Bank spreadsheets which I use on a daily basis, but will try yours out since they are definitely more detailed and integrated.

Ideas for improvements:
1.  two asterisks in column for ALL of the CC perks MET (first)-RECEIVED (second) (initial bonus, airline credits, global entry, passes, etc.)
2.  columns for date, promised compensation bonuses, REC'D, and Notes (CSR name and reason).
3.  NOTE column for anything extra for that card (reversed late fees/interest, ...)

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25541
  • Total likes: 509
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #64 on: July 16, 2015, 10:04:38 PM »
Just spent a couple hours revising, updating, and improving my spreadsheet.

Now I have a summary tab that, using the master tab, shows--

In increasingly specific order:

--Total global available credit* between all users**
--Total global available credit per user
--Total available credit per user per bank
--Total available credit per user per type***
--Total available credit per user per bank per type

*Available credit = total credit line extended by bank on open cards, regardless of current utilization
**Users = AJK + Mrs. AJK
***Type = Personal + Business

All of which is updated live based on figures in the master tab.  Thus, at any given moment, I can tell you (i) how much credit I have access to from a global perspective, (ii) how much personal credit is available to me that can be seen by banks who pull my report (they cannot see business credit as you know),  all the way to (iii) how much *personal* available credit *I* have at *Citi.*

I find this info useful as it provides me with a quick snapshop of my current credit profile vis a vis the banks.

For the visual learners:

(Note: Data has been randomized and unformatted to protect the innocent)



I also, for the heck of it, added a portion on the summary tab which shows how many miles/points each of us has earned since inception and the total of all miles earned from credit card signup bonuses.

Possible add for the future: Import award wallet figures so I can see total current mileage balances per user. But for another day.
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12424
  • Total likes: 2490
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #65 on: July 17, 2015, 04:23:28 AM »
how often do you update the #s in your master tab?
Workflowy. You won't know what you're missing until you try it.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25541
  • Total likes: 509
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #66 on: July 17, 2015, 09:04:49 AM »
Every time I apply for, upgrade, downgrade, or close a card as well as the status on meeting spend thresholds.
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline DMYD

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jul 2013
  • Posts: 2979
  • Total likes: 1
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Brooklyn
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #67 on: July 17, 2015, 09:24:03 AM »
Every time I apply for, upgrade, downgrade, or close a card as well as the status on meeting spend thresholds.
I like! I would have to add my parents to the list, as I accumulate for them also.

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #68 on: July 17, 2015, 09:35:27 AM »
Very nice AJK... would you be able to share a template of your Excel workbook with us? I am very curious to see it

Offline shalumov

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2015
  • Posts: 181
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: NJ
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #69 on: July 17, 2015, 10:30:58 AM »
shoobi, thank you for putting it together

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25541
  • Total likes: 509
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #70 on: July 20, 2015, 05:21:36 PM »
Just added a new column that autocalculates the date after which I can churn the card, i.e. 18 months from last open or closure in the case of Citi AA cards.
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Online MosheD

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2011
  • Posts: 2326
  • Total likes: 40
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #71 on: July 20, 2015, 05:38:45 PM »
Shoobi, well done. will spend some time adding all my info...

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #72 on: July 20, 2015, 08:15:33 PM »

Just added a new column that autocalculates the date after which I can churn the card, i.e. 18 months from last open or closure in the case of Citi AA cards.

I have that feature in my template

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25541
  • Total likes: 509
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #73 on: July 20, 2015, 09:32:22 PM »
I have that feature in my template

I don't see that.

You have 18 months with respect to Citi open/close, and official amex churning?
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #74 on: July 20, 2015, 10:31:57 PM »
I don't see that.

You have 18 months with respect to Citi open/close, and official amex churning?

No, not that advanced, just you can see your last application date by each bank, and also see when is the next time you can apply for a CC from last date. I set it as a default of 91 days, but it can be edited.

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #75 on: July 20, 2015, 10:33:51 PM »
I am actually working on a revised template with new features like data validation (restricting the entries of some columns to certain entries), and a better way to keep track of min spend.

But unless I include a way to easily migrate the existing template into the new one, I doubt the hassle will be worth it for anyone. I just do these things for fun :)

Offline @Yehuda

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Oct 2013
  • Posts: 9867
  • Total likes: 174
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #76 on: July 20, 2015, 10:45:37 PM »
I just reworked my Excel sheet. Now it just says, "Date Opened" and "Date Closed." ::)

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4395
  • Total likes: 8
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #77 on: July 20, 2015, 10:48:55 PM »
Wow, Just poking around the template and I already learned some new stuff. Thanks

Note: Why the need for Application M&D and  today M&D columns?

If for only checking the upcoming year you can just put the formula in the next column itself

=IF(TEXT(TODAY(),"mm/dd")>=TEXT([@[Application Date]],"mm/dd"),YEAR(TODAY())+1,YEAR(TODAY()))

« Last Edit: July 20, 2015, 10:54:03 PM by smurf »

Offline srap

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2013
  • Posts: 2821
  • Total likes: 169
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #78 on: July 20, 2015, 10:49:55 PM »
I am actually working on a revised template with new features like data validation (restricting the entries of some columns to certain entries), and a better way to keep track of min spend.

But unless I include a way to easily migrate the existing template into the new one, I doubt the hassle will be worth it for anyone. I just do these things for fun :)
Taka I was just going to start inputting my data tonight.  Shall I wait until your new version is up or you know for sure you will be having a way to transfer old information into an updated version?
« Last Edit: July 21, 2015, 01:29:49 AM by srap »

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 642
  • Total likes: 4
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Master Excel Template for keeping track of all Credit Card stuff
« Reply #79 on: July 20, 2015, 10:58:41 PM »
Taka I was just going to start inputing my data tonight.  Shall I wait until your new version is up or you know for sure you will be having a way to transfer old information into an updated version?

I will 100% include a way to migrate from the old into the new, go right ahead.