KKR

For those of you who are comfortable using Excel, an easy way to hypothesize which promos have posted is to use the Solver function in Excel.

1. Copy paste the Mileage Activity section on aa.com (paste special as text) to spreadsheet so they are arranged like this:

Date l Flight Info l Miles l (Delete the rest)

2. Sort by the date to make it easier to view.

3. Delete the non-flight activity rows.

4. Add 2 columns: "EQM Multiplier" and "Total EQMs" (EQM Multiplier should either be 1 or 2 or 3. Total EQMs should be the Miles column multiplied by the EQM Multiplier).

Here's where it gets tricky: several assumptions need to be made. My assumptions are the following:

a. Assuming you're like me and are eligible for both DEQ11 and DEQAA: TEQMs have posted until 1/15. This cuts down a lot of "possible" scenarios and allows faster calculation. To satisfy this assumption, simply put 3 on "EQM Multiplier" column for eligible flights throughout 1/15. Note that any normal flights or those only eligible for double miles should have 1 or 2 as multipliers.

b. Manually adjust any below-500 miles flights to 500 in the miles column.

c. The promotions post for earlier flights first.

Your sheet should look like this (you don't need to follow the color coding):



The yellow shaded cells are variables which Solver will modify (I just put them as 3). The green shaded cell is the total of all EQMs (do a sum of the "Total EQMs" column).

5. Enable Solver. For Excel 2007: Go to the Office Ribbon -> Excel Options -> Add-Ins -> Go (Beside "Manage Excel Add-Ins") -> Tick the "Solver Add-In" and click "OK". Solver should now be available under the "Data" tab.

6. Set up Solver with the following settings:





Set Target Cell: This should be the sum of the total EQMs (green shaded cell).

Equal To: Choose "Value Of" and input your EQMs posted on aa.com website.

By Changing Cells: Choose the rows from "EQM Multiplier" column after 01/15 (yellow shaded cells).

Subject to Constraints: These are somewhat tricky but just follow the picture. Add four constraints:
1. The value of the yellow shaded cells should be smaller or equal to 3.
2. The value of the yellow shaded cells should an integer.
3. The value of the preceding yellow shaded cell should be bigger than the next yellow shaded cell.
4. The value of the yellow shaded cells should be bigger or equal to 1.

When your solver looks exactly like the picture, press "Solve" and voila, your sheet should turn to something like this:



To interpret my results:

Solver is suggesting that either DEQAA or DEQ11 have completely posted for my flights on 1/20 and 1/21 but partially on 1/22 (hence why the multiplier is 2). But, no promotion has posted for one of my flights on 1/22 and 1/23 (hence why the multiplier is 1).

That's it. Now you know a little about linear programming

MsEverywhere

Oh, my! That's so sweet!

So far I've been able to balance my EQM manually, but I'll try this when I get a chance.

brp

Totally cool! Excellent use of Excel.

I do it with an existing tracking sheet to which I've added a column for extra miles. I list all booked trips and already track current EQMs relative to the volatile TODAY() function. For the extras, I just use an offset (something like TODAY()-5) and just adjust the offset until it matches what I see...and can tell what dates have been processed.

But yours is cooler by a fair bit. Thanks for sharing this.

Cheers.

freeupgrade

I'll get beat up for this duplicate thread - but that's okay.

I found out yesterday from AA Customer Service that DEQ11 miles pst every Tuesday (its an automated batch job that auto runs weekly on Tuesdays).

Hope this helps.

I'm not that good at Excel, so I have to rely on AA...

JDiver

Not really - though the information is cross-posted, given at least two DEQM promos and the difference of this thread, should be no problem; it doesn't much affect the tool here, however, other than to proof it for some operations, and the information is relevant.

And KKR, thanks for the great tip!^

freqflyercoll

awesome stuff! I just tried it on my own situation (I'm using Excel 2011 for Mac), and there is some setting in the solver method section that says to ignore integer constraints, so you have to be sure to uncheck that. Also, the ordering of flights on AA's site is kinda weird, but basically, I think DEQM/TEQM have posted for all my flights except for a red eye that I took on the night of 1/21/2012 (that left SFO around midnight)...and nothing I did on 1/22 has posted. I sure hope they have another sweep before this weekend! because I'd like to have Platinum for my MRs this upcoming weekend (I'm sitting at 47k EQM right now )

KKR

This result is one of the reasons why I bolded "hypothesize" on the OP. The weakness of this method is the fact that there can be multiple scenarios with regards to promotions posting (especially if you are flying the same routes all the time, causing the number of miles to be the same). So, the "true" scenario might be DEQMs have posted for all flights but not TEQM. Solver might hypothesize that TEQMs have posted for all flights till a certain date instead.

freqflyercoll

well so, the sorta saving grace for me...is that in the last week I've done some different routings...so I only have two trips that were the exact same routing (ORD-SFO twice), which made it easier for me to look at and make a guess (because I couldn't modify other trip multipliers and have the numbers come out right, since the number of miles flown was different).

BStrauss3

FWIW, I'm doing only DEQAA and my MR on 1/21 has posted.

wrxmom

Thanks for posting the how do for "tracking" DEQAA and DEQ11.

I set up my spreadsheet as instructed but I'm getting a "Set Target Cell contents must be a formula." message and not sure what I'm doing wrong.

Any thoughts?

Thanks

Non-NonRev

i wish I'd known about the "solver" function - the sheet I built manually does a lot of what solver does (but it took me a while to put it together). One thing mine does is to show both bonus EQMs that have been earned and posted in one cell, and also the bonus EQMs that have been earned but that are still awaiting the next AA sweep. This makes it easier to match what shows in the online summary.

mycorrado

Could you post the Solver parameters? (or email the spreadsheet?) I'm having trouble reading them off the image..

Mycorrado ([email protected]) thanks!

mikelat

It's easier for me to just use my existing excel file and start subtracting the DEQM from my latest segments until it matches on AA.com. I can tell that my latest 2 flights haven't posted yet. Maybe it's easy due to my spreadsheet setup, but seems much easier than using solver to try and figure it out.

KKR

Make sure that the target cell is the SUM of the "Total EQMs" column.

Also, added solver parameter screenshot in the OP.