Got it. I will think about if I want to add that.

On partner flights that earn less than 100 percent base flights. We really should have a negative class of service percentage on those? BA classes, G, O, Q would have -75% to get to 25% mileage earning?

Yup. That's exactly how I've done it, for the EQM calculations (column M). The formula takes into account the miles already listed in column I and does a subtraction if the EQM multiplier is < 1.0.

If you're talking about the RDM CoS (column K), then I didn't do anything with that based on the new RDM earning.

Cheers.

I was talking about column D on the Fare, Class, Elite - 2016 tab, which I guess is the same thing for non-AA flights.

I still need to put in all the US flights like the PHX and CLT flights as I'm already hitting a lot that aren't in there.

I added a table for QR and it worked perfectly!

Cool. Thanks for the check on the added airline table!

I had added some of the PHX and CLT flights as I found them missing, but these are based on our 2015 and 2016 itineraries, and probably don't match all of yours :)

Cheers.

I have a question...I do not see how the extra EQM are being calculated...my column AA is not populating. The sum of the rows, QMiles (P) is the same value showed in the summary of H-9...

I am guessing the EQM data should be in column M?

If so, what is the purpose of the 2x EQM column? Also what is the B Net Column? Neither of those populates, and I am looking just at the sample XLSX.


I have added in my data to one line and I am still not getting the enhanced EQM values.

First, ignore the "2X Q Miles" (Column AA). Several years back there was a promotion that had double EQM on certain routes and I added the column then. For the particular routes, I'd just hand code those, if appropriate. I never took it out going forward. I guess hope springs eternal :)

As for "B Net," I don't know what that is. There are a number of hidden columns in the original sheet I got from McFlyPHL. If you look at the 2015 tab, this is hidden as well. Not sure how this one got exposed on 2016, but I was doing some column manipulation. So, you can ignore/hide that one as well.

Down to business.

Basic 1X miles should show up in "Flight Miles" (I). Any "Flight Bonus" (to bring to 500) shows up in J. The effects of the Class of Service show up in "Class Bonus EQM" (M). This can be positive (for >1.0 EQM/mile), 0 (for 1.0 EQM/mile) and <0 (for < 1.0 EQM/mile). The final tally (Flight Miles + Flight Bonus + Class Bonus EQM) shows up in "Q Miles" (P). This is also summed in H6 (for miles already flown for the year) and H9 (for total miles booked for the year).

If you change the fare class for some of the example lines shown, you'll see the modifier in column M change accordingly. For example, if I change the Class for line 33 (SJC-LAX) to A, I get M=500, P=1000. If I change it to F, I get M=1000, P=1500. As expected.

Let me know more specifically what might not be working and I will look into it.

Cheers.

Thanks for the note and follow through. My issue was not the spreadsheet but the software I was using, which wasnt Excel and caused an issue. Once I got it to Excel it worked correctly. THANK YOU for your work.

bump
 

I just wanted to bump this as I just got a PM request for the 2016-updated sheet, so figured others might be interested. it can be found on Gargoyle's page, from the first post.

Cheers.

Update will be forthciming
 

I have updated the sheet for the EQD calculations based on the data we have so far, including the special (bulk, vacation) fares. I'm waiting on the details for mixed AA/partner itineraries so that I can code that as well and send an updated sheet.

Cheers.

Thank you for taking the time to do this and offer it to members! ^ ^

I looked and unsure if I am looking at the right file, as I saw no EQD. Can you tell me the name of the file from the link?

Sorry, I was not clear. I have updated my own sheet, but I am still waiting for the mixed itinerary calculations before actually sending an update to Gargoyle for his website.

Cheers.

Ahhh thanks! :D

Cheers.

Award Mile Calculation with multi-destinations & segments
 

First BRP - Thanks for your work! I've been using your spreadsheet since last year, and look forward to your update.

How are you calculating the Fare to Award Miles line by line? I started playing around with my own modification, but when I see how fares are reconciled on AA.com it didn't track initially, and I don't see how the spreadsheet can do it with the current 1 segment per line... because they seem to be rounding down the fare per booked destination (not segment).

I'm not sure if my explanation is going to be very good here... so I'm attaching a simple spreadsheet with the formulas I used to match my statement on AA.com. (change the extension from .pdf to .xlsx)

I'm currently in the middle of a multi-destination itinerary that cost $639.28 (fare only). I booked Round trip from DCA to MSP and Round trip to STT from DCA (via MIA), so I've seen how my first round trip is being awarded on AA.com, which seems to be:

ROUNDDOWN(TOTALFARE/#Destinations),0 to get the per destination cost of $159.00

then for the base and bonus multiplying that by 5 and 3 respectively (I'm platinum).

Which, on the round trip from DCA to MSP it gave me 795 base + 477 bonus for each segment. All that tracks exactly with AA.com. ^

What will be interesting is how the DCA to STT (via MSP) will show up. I'm assuming/extrapolating that it will only calculate award miles based on Origin & Destination instead of by segment, therefore ignorning Miami in the award mile calculation.

So, assuming all that is correct - How would you effectively stick this in a spreadsheet so each line is uniform in its formulas?

First, your attachment doesn't seem to work for me. Acrobat says it's not a pdf.

Second, I am no longer tracking award miles very accurately as I just don't care about them. I'm focused on EQM and (now EQD). I have nor added anything for the new RDM calculations.

Given all that, I'm not sure I understand the nuances here. Once you have determined the eligible portion of the spend, then both EQD and RDM are based on this, and per-segment mileage doesn't matter.

So, for EQD, I just put 0 for all the other legs, put the eligible spend on the last lime and compute EQD based on that for the whole trip.

There are tricks to play for partner and "special" fares, and I've coded those as well. The only thing I (still) don't have are the rules for mixed AA/partner itineraries. Don't know how those will be calculated.

General question: Does it make sense for me to provide the new sheet that covers pure AA, pure partner, pure "special" or continue to wait for the mixed AA/partner rules? The "pure" sheet has 2017 EQD rules for several partners as well as "special" fares.

Cheers.