AA Elite Status Tracker (Google Sheets)
Feb 11, 2015, 8:43 pm
#1
Original Poster
Join Date: Jan 2015
Location: LHR
Programs: AA
Posts: 773
AA Elite Status Tracker (Google Sheets)
Hey folks,
Recently, mainly for my own amusement, I put together a quick spreadsheet in Google Sheets to track my progress towards AA elite status. You type in your flight dates and mileage, and it calculates running totals and percentages, with some other fun features.
I'm a lowly non-elite flier who until 2015 never bothered consolidating all my travel on to one FF program, and I didn't realize until I found FT that I had been throwing away a lot of benefits. My 2015 resolution is to hit at least Gold (shooting for Plat though) and I thought it'd be fun to see my progress. THEN I thought there might be a couple other FTers who might want to take my spreadsheet and fill it in with their own data.
So I made a duplicate blank version of it, and here it is:
BLANK STATUS TRACKER DOC:
https://docs.google.com/spreadsheets...ndY/edit#gid=0
To use this you must be signed into a Google account and go to File->Make a copy. You will not be able to edit it directly at this link!
—The reason it has all that garish red is to make it really clear that you should not change or delete those red cells because they include all the complex formulas that make the whole thing work.
—Red cells mean don't edit or delete that cell. Yellow cells are drop-down menus. (Drop-down menus only work when viewing on desktop browsers, not mobile, in my experience.)
—If you want to delete data you put into the spreadsheet, delete the data from the non-red cells and the red cells will also empty out automatically.
—Until there is at least one flight's worth of data, there will be several "#DIV/0!" errors. These will go away once there there actual numbers to crunch.
Here is my non-editable filled in sheet showing an example of what real data looks like:
MY EXAMPLE STATUS TRACKER DOC:
https://docs.google.com/spreadsheets...3Og/edit#gid=0
Basically, you fill this up line by line with your flight information (you'll have to do the per-flight EQP calculation based on booking code yourself; I was going to automate it but the differences with all the partner airlines made it too much of a pain), and as you put in more flights, the spreadsheet will update with your progress based on EQM, EQP, and segments.
—There is a drop-down menu under "CHOOSE TIER" to change the goal between GLD, PLT, and EXP. (It's really fun to switch between them and see all the percentages update!)
—Departure, Arrival, Flight Number, Airline, and Booking Code columns don't actually do anything, but I thought they were useful to have a record of.
—You can also add in any EQM, EQP, or segment bonuses you might be receiving under the "BONUS" field, and you can choose whether bonuses are included in your calculations with the "Use/Don't Use" toggle underneath. (For example, I entered 10,000 bonus EQM because I plan to hit $40k spend on my Citi Exec this year.)
—Average Stats show you, on average, how many EQM you're earning per segment, how many EQP per day, how many segments per week, and so on and so on. Projections gives you a VERY ROUGH indication as to how many days, weeks, or segments it will take for you to qualify for the selected tier by EQM, EQP, or segments, based on your current rates. This is mainly for fun, not meant as any kind of real prediction.
—Your average earnings per day/week/segment do not take into account any bonuses you have input, since bonuses aren't accrued from your actual activity. However, bonuses will still affect your projections for how much time you have remaining.
—Data and projections are calculated based on all flights you have entered, NOT only on flights up to the current date. So your stats per day reflect your stats from January 1 to the date of the last flight you have entered. Dates that have already passed in your list of flights will be highlighted green to show your progress.
—Similarly, the "TIME" stats will show how many days/weeks have passed or are left in the year based on the last flight you have entered.
I haven't shown this to anyone except my fiancee so I apologize if it's unclear or confusing. Again, I just made it as a fun tool for myself, not for public consumption, but hey, maybe someone else might find it useful. If you have any questions I'll try to address them, although I can't really promise I'll implement any feature requests.
Recently, mainly for my own amusement, I put together a quick spreadsheet in Google Sheets to track my progress towards AA elite status. You type in your flight dates and mileage, and it calculates running totals and percentages, with some other fun features.
I'm a lowly non-elite flier who until 2015 never bothered consolidating all my travel on to one FF program, and I didn't realize until I found FT that I had been throwing away a lot of benefits. My 2015 resolution is to hit at least Gold (shooting for Plat though) and I thought it'd be fun to see my progress. THEN I thought there might be a couple other FTers who might want to take my spreadsheet and fill it in with their own data.
So I made a duplicate blank version of it, and here it is:
BLANK STATUS TRACKER DOC:
https://docs.google.com/spreadsheets...ndY/edit#gid=0
To use this you must be signed into a Google account and go to File->Make a copy. You will not be able to edit it directly at this link!
—The reason it has all that garish red is to make it really clear that you should not change or delete those red cells because they include all the complex formulas that make the whole thing work.
—Red cells mean don't edit or delete that cell. Yellow cells are drop-down menus. (Drop-down menus only work when viewing on desktop browsers, not mobile, in my experience.)
—If you want to delete data you put into the spreadsheet, delete the data from the non-red cells and the red cells will also empty out automatically.
—Until there is at least one flight's worth of data, there will be several "#DIV/0!" errors. These will go away once there there actual numbers to crunch.
Here is my non-editable filled in sheet showing an example of what real data looks like:
MY EXAMPLE STATUS TRACKER DOC:
https://docs.google.com/spreadsheets...3Og/edit#gid=0
Basically, you fill this up line by line with your flight information (you'll have to do the per-flight EQP calculation based on booking code yourself; I was going to automate it but the differences with all the partner airlines made it too much of a pain), and as you put in more flights, the spreadsheet will update with your progress based on EQM, EQP, and segments.
—There is a drop-down menu under "CHOOSE TIER" to change the goal between GLD, PLT, and EXP. (It's really fun to switch between them and see all the percentages update!)
—Departure, Arrival, Flight Number, Airline, and Booking Code columns don't actually do anything, but I thought they were useful to have a record of.
—You can also add in any EQM, EQP, or segment bonuses you might be receiving under the "BONUS" field, and you can choose whether bonuses are included in your calculations with the "Use/Don't Use" toggle underneath. (For example, I entered 10,000 bonus EQM because I plan to hit $40k spend on my Citi Exec this year.)
—Average Stats show you, on average, how many EQM you're earning per segment, how many EQP per day, how many segments per week, and so on and so on. Projections gives you a VERY ROUGH indication as to how many days, weeks, or segments it will take for you to qualify for the selected tier by EQM, EQP, or segments, based on your current rates. This is mainly for fun, not meant as any kind of real prediction.
—Your average earnings per day/week/segment do not take into account any bonuses you have input, since bonuses aren't accrued from your actual activity. However, bonuses will still affect your projections for how much time you have remaining.
—Data and projections are calculated based on all flights you have entered, NOT only on flights up to the current date. So your stats per day reflect your stats from January 1 to the date of the last flight you have entered. Dates that have already passed in your list of flights will be highlighted green to show your progress.
—Similarly, the "TIME" stats will show how many days/weeks have passed or are left in the year based on the last flight you have entered.
I haven't shown this to anyone except my fiancee so I apologize if it's unclear or confusing. Again, I just made it as a fun tool for myself, not for public consumption, but hey, maybe someone else might find it useful. If you have any questions I'll try to address them, although I can't really promise I'll implement any feature requests.
Feb 12, 2015, 2:45 am
#3
FlyerTalk Evangelist
Join Date: Jun 2005
Location: QLA
Programs: SBUX Gold
Posts: 14,507
Do you have EQP earning tables for partners like AS, so it knows how many EQPs to assign on partner fare codes, depending on the airline?
Nice work though... I use Google seats as well, in more simplified (read: manual) manner.
Nice work though... I use Google seats as well, in more simplified (read: manual) manner.
Feb 12, 2015, 2:52 am
#4
Original Poster
Join Date: Jan 2015
Location: LHR
Programs: AA
Posts: 773
I don't currently do this automatically, but I theoretically could. I'd probably put all the tables on a second tab of the spreadsheet and link it, so it doesn't clutter up the whole thing, but it would be possible. The reason I didn't is just because there are so many different partner airlines, and I personally only really fly AA and AS, so it was easier to just quickly figure it out per flight than it would have been to support every possible AA partner. But maybe I'll hook it up with just AA and AS when I get some time, as a start.
Feb 13, 2015, 10:11 am
#5
Join Date: Feb 2015
Location: BWI, IAD, DCA
Programs: SW Comp Pass, Alaska MVP Gold 75, SPG Platinum, Hyatt Diamond, HHonors Diamond, AA Platinum,
Posts: 10
Wow! This is Great.
Thanks for all the hard work you put into this and for sharing it! I would probably PAY to have a set of these for each airline and with partner/ code share fields.
I have been struggling for some time trying to decide where it is best to accrue miles from each airline.
Right now I am Alaska MVP75, so I think its best to send all my miles there when possible. But I have USAir (SLC-HNL) & United (IAD-DUB) flights coming up, of which I have no status. I would love to begin working on status for other airlines but trying to decide which one is tough, especially since I tend to pick up deals and they never seem to be on my preferred carrier at the time.
Great Job!
Thanks for all the hard work you put into this and for sharing it! I would probably PAY to have a set of these for each airline and with partner/ code share fields.
I have been struggling for some time trying to decide where it is best to accrue miles from each airline.
Right now I am Alaska MVP75, so I think its best to send all my miles there when possible. But I have USAir (SLC-HNL) & United (IAD-DUB) flights coming up, of which I have no status. I would love to begin working on status for other airlines but trying to decide which one is tough, especially since I tend to pick up deals and they never seem to be on my preferred carrier at the time.
Great Job!
Feb 13, 2015, 12:54 pm
#6
FlyerTalk Evangelist
Join Date: Jun 2005
Location: QLA
Programs: SBUX Gold
Posts: 14,507
I don't currently do this automatically, but I theoretically could. I'd probably put all the tables on a second tab of the spreadsheet and link it, so it doesn't clutter up the whole thing, but it would be possible. The reason I didn't is just because there are so many different partner airlines, and I personally only really fly AA and AS, so it was easier to just quickly figure it out per flight than it would have been to support every possible AA partner. But maybe I'll hook it up with just AA and AS when I get some time, as a start.
Feb 13, 2015, 2:52 pm
#7
Join Date: Aug 2010
Location: LAX
Programs: AA Lifetime Gold but PlatPro thanks to LPs
Posts: 4,439
Really nice. I am an Excel expert and understand exactly what you did.
Suggestions:
Do the EQP lookup table for the two airlines you personally fly (AA and AS). Then, others downloading your spreadsheet can add their own lookups for the partner airlines they fly. There's no real need for a totally comprehensive lookup table. The client user would just add their own partner airline info to your template.
I usually put lookup tables in a separate "Lookup" sheet to make the main sheet neater.
Similarly, create a lookup table for the city pairs mileage. Many people flying for business tend to fly between the same city pairs most of the time. Thus, a lookup table for those city pairs would be useful. As with the EQP, I'd just fill out your own selected city pairs, and let others add their own following your template. The client user would add a new city pair to the end of the lookup list when flying to a new place. This would avoid duplicate mileage data entry and allow for a quick update.
A small quibble: You have duplicate info with the flight number and airline column both containing the airline code. You might consider swapping the columns and just putting flight numbers in the right column. It would still read "correctly": AA 123. As you might have guessed, I am really good about avoiding duplicate data entry. Not a big deal here, but when you get into Really Big Data as I am every day, it starts to add up.
Finally, I noticed your past flight dates are in green. Did you do that with conditional formatting or did you just change the fill color manually? Conditional formatting would work great here.
Suggestions:
Do the EQP lookup table for the two airlines you personally fly (AA and AS). Then, others downloading your spreadsheet can add their own lookups for the partner airlines they fly. There's no real need for a totally comprehensive lookup table. The client user would just add their own partner airline info to your template.
I usually put lookup tables in a separate "Lookup" sheet to make the main sheet neater.
Similarly, create a lookup table for the city pairs mileage. Many people flying for business tend to fly between the same city pairs most of the time. Thus, a lookup table for those city pairs would be useful. As with the EQP, I'd just fill out your own selected city pairs, and let others add their own following your template. The client user would add a new city pair to the end of the lookup list when flying to a new place. This would avoid duplicate mileage data entry and allow for a quick update.
A small quibble: You have duplicate info with the flight number and airline column both containing the airline code. You might consider swapping the columns and just putting flight numbers in the right column. It would still read "correctly": AA 123. As you might have guessed, I am really good about avoiding duplicate data entry. Not a big deal here, but when you get into Really Big Data as I am every day, it starts to add up.
Finally, I noticed your past flight dates are in green. Did you do that with conditional formatting or did you just change the fill color manually? Conditional formatting would work great here.
Feb 13, 2015, 2:55 pm
#8
Original Poster
Join Date: Jan 2015
Location: LHR
Programs: AA
Posts: 773
QueenOfCoach:
Thanks for the feedback!
Yes I think you have the right idea with the booking class lookup tables. When I get some free time I will probably set them up for AA and AS, as you and VNAFlyer suggested.
The reason I have duplicate data in the airline and flight number fields is because I frequently fly AA/AS codeshare flights--currently the way I'm doing it is to have the issuing airline in the "flight" column and the operating airline in the "airline" column. This is probably not really strictly necessary, and I would have to change that convention if I used lookup tables for EQP calculation anyway.
Yes, past flight date highlighting is done with conditional formatting!
Thanks for the feedback!
Yes I think you have the right idea with the booking class lookup tables. When I get some free time I will probably set them up for AA and AS, as you and VNAFlyer suggested.
The reason I have duplicate data in the airline and flight number fields is because I frequently fly AA/AS codeshare flights--currently the way I'm doing it is to have the issuing airline in the "flight" column and the operating airline in the "airline" column. This is probably not really strictly necessary, and I would have to change that convention if I used lookup tables for EQP calculation anyway.
Yes, past flight date highlighting is done with conditional formatting!
Feb 13, 2015, 3:24 pm
#9
FlyerTalk Evangelist
Join Date: Aug 2006
Location: SNA
Posts: 18,240
I'm still using the old spreadsheet from 10 years ago? I just keep updating it every year.
The one that says "This sheet is in no way affiliated with or endorsed by AA, AMR, FlyerTalk, my dog, or the city of Cleveland"
Anyone? Anyone?
The one that says "This sheet is in no way affiliated with or endorsed by AA, AMR, FlyerTalk, my dog, or the city of Cleveland"
Anyone? Anyone?
Feb 13, 2015, 3:29 pm
#10
Join Date: Mar 2008
Location: SFO, SJC
Programs: AA - EXP 2MM+, Marriott PLT, UA, National Exec
Posts: 495
Excellent! Thanks so much for sharing. btw, if the data listed is your actual planned flights, you might see if you'd qualify for a platinum challenge. Looks awful close.
Cheers.
Cheers.
Feb 13, 2015, 3:38 pm
#11
Join Date: Nov 2004
Location: Oakland
Programs: AA Explat, UA former 1K + PremExec, DL
Posts: 1,151
My own version of this is messier because I've chosen to make a distinction BIS and RDM and also to add a "Bonus" column for EQM to cover 500 mile minimums and the occasional bonus program. This way it's easy to keep running totals of BIS, EQM and RDM. I also track equipment and upgrade time and success, though I'll be dropping to Platinum and the latter may no longer be interesting to me. I have columns to count down to thresholds, etc. There's no automation beyond fill downs, etc.