I'll show you mine if you show me yours: post your reservation-tracking spreadsheet
Feb 8, 2009 | 10:01 am
#1
Original Poster
Moderator: Southwest Airlines, Capital One
Join Date: Sep 1999
Location: California
Programs: WN A-list preferred, United Club Lietime (sic) Member
Posts: 22,825
I'll show you mine if you show me yours: post your reservation-tracking spreadsheet
Let's put our heads together and see if we can come up with a killer spreadsheet for tracking reservations. Then some kind person can add it to the FAQ.
Here are my spreadsheet columns:
A: Passenger initials or, for a canceled or past reservation, hand-entered numeric value of available funds. I have no way to show residual value in a current reservation due to re-faring to a lower fare. You need to add a column if you want that capability. Note that I manually set this column to zero after the flight is flown or the funds are used.
B: Current value in the Bank of Southwest, meaning either the residual value from column A or the total fare from column H. For row 83, this is computed as "=IF(ISBLANK(D83),0,IF(ISERR(VALUE(A83)),H83,VALUE (A83)))". The ISBLANK(D83) checks for a blank PNR, because I often populate the spreadsheet with planned future flights before I buy them. This way the Bank of Southwest balance properly excludes those rows.
C: Expiration date of funds.
D: PNR
E: Type of booking. D for Ding, C for Companion, R for award, F for freedom award, P if booked by phone, O if booked online
F: Flown? Y if yes, blank if unflown, X if canceled.
G: RR credit: blank if unposted, hyphen if no credit is due (awards), 1 (or 1.25 or 2 for BS) if credit is posted.
H: Total ticket price. I do not retain any breakdown of the fare.
I: Origin airport code
J: Outbound date
K: Outbound time
L: Outbound flight number
M: Destination or connection airport code
N: Return or connection date. For a connection (same date as outbound) I put arrival time for the outbound here.
O: Return or connection time
P: Return or connection flight number
I can show one connecting trip on one line or a RT using nonstops. More than that requires 2 lines. I do not have a column for arrival time at the destination, but you could add one.
Q: Source of funds. This column is vital if you hope to keep track of where your funds went. I could probably have broken this into multiple columns, but I show multiple PNRs plus a credit card like this: "ABC123 25.60 + DEF456 38.40 + SW4321 12.00"
R: Comments. Anything that doesn't fit in the other columns. Bonus promo booking, arrival time at destination, passenger initials (because they disappear from column A after I enter zero there).
I have a separate worksheet for awards, showing award number, expiration date, PNR for "A" half, and PNR for "B" half.
A third worksheet is a simple list of refunds applied for: amount due, PNR, credit card identifier (e.g., SW4321), date requested, type of request (online or phone), and date received. Phone refunds sometimes require a second request.
Here are my spreadsheet columns:
A: Passenger initials or, for a canceled or past reservation, hand-entered numeric value of available funds. I have no way to show residual value in a current reservation due to re-faring to a lower fare. You need to add a column if you want that capability. Note that I manually set this column to zero after the flight is flown or the funds are used.
B: Current value in the Bank of Southwest, meaning either the residual value from column A or the total fare from column H. For row 83, this is computed as "=IF(ISBLANK(D83),0,IF(ISERR(VALUE(A83)),H83,VALUE (A83)))". The ISBLANK(D83) checks for a blank PNR, because I often populate the spreadsheet with planned future flights before I buy them. This way the Bank of Southwest balance properly excludes those rows.
C: Expiration date of funds.
D: PNR
E: Type of booking. D for Ding, C for Companion, R for award, F for freedom award, P if booked by phone, O if booked online
F: Flown? Y if yes, blank if unflown, X if canceled.
G: RR credit: blank if unposted, hyphen if no credit is due (awards), 1 (or 1.25 or 2 for BS) if credit is posted.
H: Total ticket price. I do not retain any breakdown of the fare.
I: Origin airport code
J: Outbound date
K: Outbound time
L: Outbound flight number
M: Destination or connection airport code
N: Return or connection date. For a connection (same date as outbound) I put arrival time for the outbound here.
O: Return or connection time
P: Return or connection flight number
I can show one connecting trip on one line or a RT using nonstops. More than that requires 2 lines. I do not have a column for arrival time at the destination, but you could add one.
Q: Source of funds. This column is vital if you hope to keep track of where your funds went. I could probably have broken this into multiple columns, but I show multiple PNRs plus a credit card like this: "ABC123 25.60 + DEF456 38.40 + SW4321 12.00"
R: Comments. Anything that doesn't fit in the other columns. Bonus promo booking, arrival time at destination, passenger initials (because they disappear from column A after I enter zero there).
I have a separate worksheet for awards, showing award number, expiration date, PNR for "A" half, and PNR for "B" half.
A third worksheet is a simple list of refunds applied for: amount due, PNR, credit card identifier (e.g., SW4321), date requested, type of request (online or phone), and date received. Phone refunds sometimes require a second request.
Feb 8, 2009 | 10:54 am
#2
Join Date: Aug 2001
Location: LAS
Programs: SWA
Posts: 1,320
Nsx, there is a lot more detail in yours than I track in mine. I am almost embarrassed, but here goes.
1. Trip Date
2. Departure-Arrival airports
3. Passenger name
4. PRN #
5. Funds expiration date
6. Cost of ticket
7. Type of ticket (CP, RR or paid)
8. Flight #
In separate columns, I track available PRN funds with these columns:
1. PRN #
2. Amount available on PRN
3. Expiration Date
4. Passenger
I broke this out of the main spreadsheet because I hide the flights once completed, and I wanted the PRN funds at the top of the columns for wasy viewing.
On separate tabs, I track
a) Award Tickets
b) CP flight costs & value.
c) gift card balances
1. Trip Date
2. Departure-Arrival airports
3. Passenger name
4. PRN #
5. Funds expiration date
6. Cost of ticket
7. Type of ticket (CP, RR or paid)
8. Flight #
In separate columns, I track available PRN funds with these columns:
1. PRN #
2. Amount available on PRN
3. Expiration Date
4. Passenger
I broke this out of the main spreadsheet because I hide the flights once completed, and I wanted the PRN funds at the top of the columns for wasy viewing.
On separate tabs, I track
a) Award Tickets
b) CP flight costs & value.
c) gift card balances
Feb 8, 2009 | 11:46 am
#3
Join Date: May 2004
Location: Night Vale
Posts: 1,872
I use two systems:
1. I have a SWA drink coupon book from 2001 with a spring clip that stores all my accumulated drink coupons (I still have a bunch of the old ones).
2. I use something called southwest.com to track my awards.
System 1 is the most important and most operational-critical of the two.
Bill
1. I have a SWA drink coupon book from 2001 with a spring clip that stores all my accumulated drink coupons (I still have a bunch of the old ones).
2. I use something called southwest.com to track my awards.
System 1 is the most important and most operational-critical of the two.
Bill
Feb 8, 2009 | 12:18 pm
#4
Join Date: Jul 2006
Location: ATL
Programs: DL, AA
Posts: 6,033
SW RR tracking looks different than many other FF programs (no surprise). However, you might get a few ideas from other spreadsheets. And, if you have a great spreadsheet you would like to share, Gargoyle hosts a number of mileage & hotel tracking spreadsheets (link is also in Gargoyle's signature). I'm sure he would be glad to add a RR-formated spreadsheet.
Feb 8, 2009 | 7:19 pm
#5
Moderator, Southwest Airlines and Choice Privileges
Join Date: Mar 2008
Location: Central Texas
Posts: 3,112
Sorry, I'm too bashful to show mine. 
Interesting that nsx has a reservation tracking spreadsheet. I have a funds tracking spreadsheet. I don't think initials would be enough for me to keep track of pax; last I checked I had reservations for twelve different people in my spreadsheet (somewhat explains why I know the web site so well, when my own bookings are mostly leisure travel).
I'd like to see what other folks think are important in their tracking spreadsheets before going into a lot of detail, but for now I'd like to point out that if you take the time to put the correct first and last names in the spreadsheet you can easily make hotlinks to view/modify/cancel your reservations, as well as for OLCI. Example: assuming we are on row 10 with PNR in A, first name in B, and last name in C, just enter these formulae (Note: change all ; to , if using Excel instead of Open Office):
Notice the inconsistency in parameter names. The PNR is variously called the "recordLocator," the "confNum," and the "cConfirmationNumber." I have no idea whether that is intentional or not, but it does mean you must enter each formula precisely as shown above. Don't try to enter just one formula and copy it, modifying only the cgi-bin program name; it won't work. If the passenger's last name contains any spaces, special handling may be required. One pax on my list has a last name with a space; the web site substitutes a "+" for the space. I don't know whether that is universal, so I didn't build it into the formulae.
Interesting that nsx has a reservation tracking spreadsheet. I have a funds tracking spreadsheet. I don't think initials would be enough for me to keep track of pax; last I checked I had reservations for twelve different people in my spreadsheet (somewhat explains why I know the web site so well, when my own bookings are mostly leisure travel).
I'd like to see what other folks think are important in their tracking spreadsheets before going into a lot of detail, but for now I'd like to point out that if you take the time to put the correct first and last names in the spreadsheet you can easily make hotlinks to view/modify/cancel your reservations, as well as for OLCI. Example: assuming we are on row 10 with PNR in A, first name in B, and last name in C, just enter these formulae (Note: change all ; to , if using Excel instead of Open Office):
Code:
=HYPERLINK("https://www.southwest.com/cgi-bin/viewPnr?recordLocator="&A10&"&firstName="&B10&"&lastName="&C10;"View Itinerary")
=HYPERLINK("http://www.southwest.com/cgi-bin/modifyAir?confNum="&A10&"&firstName="&B10&"&lastName="&C10;"Modify Itinerary")
=HYPERLINK("http://www.southwest.com/cgi-bin/airCancel?cConfirmationNumber="&A10&"&firstName="&B10&"&lastName="&C10;"Cancel Itinerary")
=HYPERLINK("http://www.southwest.com/cgi-bin/selectBoardingPass?recordLocator="&A10&"&firstName="&B10&"&lastName="&C10;"Check In")
Last edited by ftnoob; Feb 8, 2009 at 10:05 pm Reason: Note re: Excel/Open Office difference
Feb 9, 2009 | 1:30 pm
#6
Original Member and FlyerTalk Evangelist
Join Date: May 1998
Location: Kansas City, MO, USA
Programs: DL PM/MM, AA ExPlat, Hyatt Glob, HH Dia, National ECE, Hertz PC
Posts: 16,619
I only track unused funds, which is simply Confirmation, Amount Remaining, Expiration Date, and Passenger Name (if not my own). I use Google Spreadsheets so it's accessible from anywhere I have an internet connection on any computer.
Aug 2, 2009 | 9:50 am
#7
Join Date: Feb 2004
Location: USA
Programs: AC SE100K, F9 Plat, UA *S, Hyatt Glob, Bonvoy Titanium
Posts: 5,478
For quite some time I've been thinking that someone with programming skills needs to come up with an application that auto updates a spreadsheet/database.
all it would require people to do is forward all mail from southwest.com to another address. This other address would auto process the emails and would be able to tell if it was a new purchase cancellation etc.
It could keep track of all spent $, remaining credits, and expiration dates. as long as the emails from SWA keep coming, I don't see how the accuracy would be lost. Possibly it would require some interaction from the user if a flight is purchased but not flown (since SWA doesn't like bots/agents querying the site for those details)
It could then send out Outlook 'meeting request/task request' entries to schedule each flight on someone's calendar. And/or possibly auto sync with a google spreadsheet
Since I don't have the skills required to do so, it may be a moot point. But I'm sure someone with talent could do it for a hobby for free. Hiring a programmer would cost $1000 on up. But if it saved 10 FTers $100 a year in lost credit it would be worth it..
all it would require people to do is forward all mail from southwest.com to another address. This other address would auto process the emails and would be able to tell if it was a new purchase cancellation etc.
It could keep track of all spent $, remaining credits, and expiration dates. as long as the emails from SWA keep coming, I don't see how the accuracy would be lost. Possibly it would require some interaction from the user if a flight is purchased but not flown (since SWA doesn't like bots/agents querying the site for those details)
It could then send out Outlook 'meeting request/task request' entries to schedule each flight on someone's calendar. And/or possibly auto sync with a google spreadsheet
Since I don't have the skills required to do so, it may be a moot point. But I'm sure someone with talent could do it for a hobby for free. Hiring a programmer would cost $1000 on up. But if it saved 10 FTers $100 a year in lost credit it would be worth it..
May 9, 2011 | 4:38 am
#8
Join Date: Mar 2011
Posts: 33
Share your spreadsheets?
Now that we have had some experience with RR2.0, it appears that tracking points, tier-qualifying points, progress towards CP, traditional credits and awards, unused funds, etc. has become much more complex ... and maybe more necessary ... than ever before.
Do you folks have some nifty spreadsheets you can share? Can we set up some place where people's spreadsheets can be described (as to what they track) and downloaded?
Do you folks have some nifty spreadsheets you can share? Can we set up some place where people's spreadsheets can be described (as to what they track) and downloaded?