How smart is the QF points calculator

[thewinchester]

While driving around Perth this evening, I was talking to another nutter who loves to travel by air. We were discussing flights and working out points/sc's earned from various flight options we were considering.

The question dawned upon me, how smart is earning calculator on the QF website?

Say I've logged into the site with my QFF a/c (QPNB at the moment), and I quote up a flight - during which I would move up into the next status bracket. Does anyone know if the calculator detect this and adjust the estimated amount of points earned accordingly (add status bonuses)??

If it doesn't, does anyone have any good spreadsheets lying around which already do this for QF, or I could make do this kind of math with some quick adjustments and technical wizardry?

 

[Hvr]

This Qantas site allows you to calculate points and SCs earned on a flight and shows the earning rate for different status levels.

As for going up the levels, my experience (albeit limited) is that the flight after you attain a status level the new level automatically applies.

So if you are an NB on 340 SCs and doing a flight where you would obtain 20 SCs you will earn at NB level for that flight and subsequent flights will be at PS level.

 

[drron]

And even if you have 1140 SCs but only 3 QF sectors then your next flight on QF still earns at the NB rate if that is the level you started your year at.

 

[oz_mark]

Say I've logged into the site with my QFF a/c (QPNB at the moment), and I quote up a flight - during which I would move up into the next status bracket.

You never move up a level during a flight, the SC's are applied after the flight is taken, and the points earned are based on your level at the start of the flight.

As for the case of the calculator applying it if you do a multi-sector itinerary, it would be unlikely that it detects this.

 

[thewinchester]

You never move up a level during a flight, the SC's are applied after the flight is taken, and the points earned are based on your level at the start of the flight.

I knew that

As for the case of the calculator applying it if you do a multi-sector itinerary, it would be unlikely that it detects this.

Cheers, that's what I thought. Would be interested to see if anyone's done some spreadsheets for QF miles/sc calculation. Chances are I could modify this up to do what I'm wanting.

 

[serfty]

So, the QFF point calculator will currently:

• If you are logged in - Calculate points/SC's as per your current status.
• If you are not logged in - Calculate and show points for each status level (NB, PS, SG, WP).

 

[anat0l]

How smart is the QF points calculator? Pretty dumb actually.

It will merely take point to point itineraries and then price these up for you based on a class you select. For non-QFFers and those not logged in, it will tell you how many points/SCs you will earn for that flight for each different QFF status (NB, PS, SG and WP). For logged in QFFers, you will get a single evaluation for your current status.

The calculator will not evaluate itineraries that involve any flights except for QF flights - not JQ flights, BA flights or anything else. So pretty weak calculator. I end up not using the calculator these days and just going straight to GCM, getting the mileages for my itinerary and working the rest in Excel/pen and paper. Much faster, although GCM's mileages are slightly different to that used by QF (although at best this is 1-5 mi = big deal).

Also, you can only put in a maximum of 6 sectors into the engine (or 12 if you are returning via the exact mirror of your original set of sectors).

I was thinking of creating such a spreadsheet, but unfortunately the most fundamental step - calculating the GCM between ports - is difficult to set up (requires some intricate programming). Once you have the GCM, the rest is quite easy to set up using vanilla formulae.

 

[serfty]

Of course, GCM distances do not always equate to Qantas distances betwixt the same ports ...

 

[thewinchester]

Well, I've started developing a spreadsheet to handle this:
thewinchester's frequent flyer tools: Qantas Frequent Flyer earning calculator

I've set it up so far to handle cabin and status bonuses, and there's a field which links directly to great circle mapper to give you the miles for the selected segment.

I'd intend to modify further to handle the earning ratios for different carriers and calculate the acceptable status credits for each segment. These require some double lookups which my brain just can't handle tonight. Additionally, would prefer to reduce the resulting status column to a single field, and use the qualifying mode field at the top of the sheet to generate the result.

First couple of lines in the spreadsheet are just samples which I've used to check that all the calculation and lookup math to date is working.

Would appreciate any feedback you might have, look forward to improving this further to make it really useful (or even make it web-based at some point).

 

[yo yo ma]

I tried Sydney-Bali (in starclass with JQ) and got back that QF does not fly this route. I worked out that it would be 100 s/c... sound about right?

 

[anat0l]

Well, I've started developing a spreadsheet to handle this:
thewinchester's frequent flyer tools: Qantas Frequent Flyer earning calculator

Looks really good! My hat goes off to you for undertaking this.

When I think about it, I forgot about Mileage Monkey. If you use the oneworld RTW validator and tell it to ignore errors, it'll give you a good run down on the SC side of things.

Don't forget for your calculator:

• Minimum points guarantees. QF and JQ is easy, but remember it also applies to BA and AA marketed, and the rules are different for them!
• Status bonuses only apply for QF, BA, JQ and AA marketed flights
• You should probably note that for JQ, "Star Class = Business" and "JetSaver = Economy". Just being nit picky
• SC calculations can be automated; you'll end up creating a horrible nested IF statement, however. Perhaps more hidden columns are required.
• Perhaps you should provide a field that asks for the base points rate (e.g. 1, .25 etc. - note that this number must always be between 0 and 1 inclusive). The default value would be 1, and all this does is automate the calculation of the base points/miles.
• I'm sure with some VBA and a whole lot of HTML slicing and dicing you could semi-automate the GCM distance stuff. But that's pretty tough (first challenge is retrieving a data stream of type text/html and perhaps trying to stuff that into an extended String type.... yeesh)

 

[thewinchester]

Thanks anat0l. I started working on this spreadsheet via my mac, and one of the things Microsoft took out of Office 2008 for Mac was VBA and macro creation. Then again, I could have easily worked on it via my Windows VM and could have knocked a bit more of that on the head.

You caught a few things I forgot to factor in which I was thinking about, will be going away and having some fun with these in V0.2

Will post back here soon when the next version is ready.

Looks really good! My hat goes off to you for undertaking this.

When I think about it, I forgot about Mileage Monkey. If you use the oneworld RTW validator and tell it to ignore errors, it'll give you a good run down on the SC side of things.

Don't forget for your calculator:

• Minimum points guarantees. QF and JQ is easy, but remember it also applies to BA and AA marketed, and the rules are different for them!
• Status bonuses only apply for QF, BA, JQ and AA marketed flights
• You should probably note that for JQ, "Star Class = Business" and "JetSaver = Economy". Just being nit picky
• SC calculations can be automated; you'll end up creating a horrible nested IF statement, however. Perhaps more hidden columns are required.
• Perhaps you should provide a field that asks for the base points rate (e.g. 1, .25 etc. - note that this number must always be between 0 and 1 inclusive). The default value would be 1, and all this does is automate the calculation of the base points/miles.
• I'm sure with some VBA and a whole lot of HTML slicing and dicing you could semi-automate the GCM distance stuff. But that's pretty tough (first challenge is retrieving a data stream of type text/html and perhaps trying to stuff that into an extended String type.... yeesh)

 

[thewinchester]

Well with a burst of energy and not being able to sleep, I've just finished V0.2 of this beast.

I've made some improvements based on anat0l's suggestions and fixed up some other things from the first version.

Currently the spreadsheet will:

• Allow you to enter/select the airline, departure and arrival airport, cabin class and number of miles flown.
  • If number of miles is unknown, a single click will pass information on that sector thru to Great Circle Mapper to get the estimated mileage.
• Ensure you've entered in the valid arrival and departure airport codes
• Calculate miles based on the earning ratio you specify (this is soon to be automated to pre-fill with earning ratio for airline and cabin class specified)
  • If flying a carrier in a cabin class where SC's aren't earned, a zero value for all fields after earning ratio while still ensuring rest of spreadsheet calculations remain valid.
• Work out if a minimum points guarantee is acceptable to the carrier, and apply accordingly based on the airline and cabin class.
• Calculate if status credits, cabin bonuses and status bonuses are earned and apply accordingly.
• Provide a running total of miles and SC's earned.

Improvements specific to V0.2

• Spreadsheet operating mode function has completed. Using a drop-down at the top of screen you can select if you're looking to qualify from your current status or retain.
• Status credits resolved in full, carriers & class parings which earn no points/status automatically ineligible (and will override any specified earning multiplier).
• points/sc's are calculated based on status at start of sector.
• Minimum points guarantee by carrier and class of travel implemented.

Feel free to download the spreadsheet (MS Excel, 326kb) and let me know what you think.

I'll be working on automating the mileage multipliers tomorrow so it makes the spreadsheet almost brainless. Just need to clean up the earning table from QF and deal with some of the nuances across different carriers (such as different earning levels for, and even within Discount economy etc).

 

[docjames]

Congrats. That's amazing.


/Constructive criticism/

You really need published distances from QF/airlines rather than from GCM as some sectors will differ causing SC and mileage calculation errors.

it would be nice for the sheet to calulate those distances automatically rather than having to plug in manually.

//

Perhaps you should sell this to QF!

 

[anat0l]

You really need published distances from QF/airlines rather than from GCM as some sectors will differ causing SC and mileage calculation errors.

it would be nice for the sheet to calulate those distances automatically rather than having to plug in manually.

The automatic distance thing I mentioned above. Within Excel and even with VBA, this is not an easy thing to do, unless you can somehow write your own GCM (which requires storing the locations of all IATA codes). I'm not even going to entertain the work required for error recovery (e.g. what if the user doesn't know the IATA code and enters the city name instead? What if the IATA code isn't found in the database?). ba97 seems to work out GCMs as well; I wonder what scripting they use to do that....

Speaking of which, where did you get that list of IATA codes from? The list isn't complete (hard to get one of those anyway, but for example AVV and REP can't be found in the list), but it probably mightn't matter once you sort of automate the GCM part.

I find that the difference between the GCM distance and the distance supplied by the QF machine is between 1 and 5 miles until you get long or ultra long haul distances. It seems QF goes slightly over GCM distances, but in my practice I've found it hasn't made much difference. For example, ADL/CBR is barely over 600 mi; GCM gives 604 mi, QF gave me 605 mi. Take the golden triangle and the differences again are very slight:

• BNE/SYD: GCM 467 mi, QF 468 mi
• MEL/SYD: GCM 439 mi, QF 439 mi
• BNE/MEL: GCM 857 mi, QF 859 mi

And then a few long distance examples, perhaps the only place where there might be noticeable differences:

• MEL/LAX: GCM 7921 mi, QF 7923 mi
• BKK/LHR: GCM 5958 mi, QF 5928 mi
• SYD/JNB: GCM 6862 mi, QF 6846 mi

I'm not sure how QF calculates its distances when crediting miles for flights it doesn't operate (for example, BA flight from LHR-JNB). Perhaps that is left up to the operating airline to send back to QF, in which case you're going to run into the same problems of "getting the miles right". In most cases, I think GCM is not going to give you an error greater than 1-3% in the mileage earned and at maximum a 1% chance that the status credits will be wrong (due to a few miles teeter-tottering between QF SC zones).

 

[jas]

Nicely done, thewinchester!

One glitch I noticed: you're applying status bonuses to the MPG. For example, when I put in a MEL-ADL flight (399 miles according to GCM) with my PS status, it applies the MPG to bring me to 1000 points, then gives the 25% bonus (i.e. 1250 points). Unfortunately QF aren't as generous as you, and just give 1000 (i.e. MPG kicks in after status bonus).

Other than that, nice work!

As far as automating distances, maybe some kind of lookup table in a hidden sheet somewhere might make things easier than trying to scrape the GCM site? Not that I know where you'd get the initial source data.

 

[thewinchester]

You really need published distances from QF/airlines rather than from GCM as some sectors will differ causing SC and mileage calculation errors.

it would be nice for the sheet to calulate those distances automatically rather than having to plug in manually.

If the QF information could be easily queried or sourced, it would be achievable. Sadly its not, and per anat0l's comments - the difference margin between GCM and QF is around 1-3%, in favour of QF.

Its not a perfect solution, but it will give you a bloody good estimate that'll be so close to the actual result its not worth arguing over. I will make a note in the next release about how this is an estimate, and YMMV depending on how the carrier calculates distance between ports.

I'm not even going to entertain the work required for error recovery (e.g. what if the user doesn't know the IATA code and enters the city name instead? What if the IATA code isn't found in the database?).

Yes, that's another issue altogether. The target user of this spreadsheet is expert flyers who know the IATA codes for where they're coming from and going to. Because of the way the airport codes fields are structured, it won't let you enter anything other than an IATA code - it will simply spit back an error telling you what you've entered isn't in the relevant list.

Having said that, you can easily google for this information if you're a newbie, or go to the airport codes tab and use Excel's find feature to locate the code. Something else to add to the instructions when I type them up

ba97 seems to work out GCMs as well; I wonder what scripting they use to do that....

I chat with the guy who runs FlightMemory (similar concept to ba97) on the odd occasion, might ask him how they work out the distance between ports.

Speaking of which, where did you get that list of IATA codes from? The list isn't complete (hard to get one of those anyway, but for example AVV and REP can't be found in the list), but it probably mightn't matter once you sort of automate the GCM part.

Google is your friend :mrgreen:

List was sourced from:
International airport codes, IATA 3-letter code for airports

If you notice anything amiss, you can quickly email [email protected] with the details and they'll update accordingly.

Also, the Wikipedia gang maintain a list of airports with IATA and IACO codes.

One glitch I noticed: you're applying status bonuses to the MPG. For example, when I put in a MEL-ADL flight (399 miles according to GCM) with my PS status, it applies the MPG to bring me to 1000 points, then gives the 25% bonus (i.e. 1250 points). Unfortunately QF aren't as generous as you, and just give 1000 (i.e. MPG kicks in after status bonus).

Excellent, thanks for spotting that. I'll alter the calculation methods in the sheet shortly to factor this in.

Does anyone know if the same applies if the flight is eligible for a a cabin bonus? Eg: MEL-ADL in J by a PS, SG, or WP, is the math Points + Cabin Bonus + Status Bonus + MPG difference?


All in all, I'm pretty impressed that in less than 24hrs we have something reasonably useful which will do most of the heavy lifting for calculating points/SC's for trips and deals with the inadequacies and limitations of the QF calculator.

 

[anat0l]

All in all, I'm pretty impressed that in less than 24hrs we have something reasonably useful which will do most of the heavy lifting for calculating points/SC's for trips and deals with the inadequacies and limitations of the QF calculator.

Well, I must admit it will be nice to have such a tool.

For me, the old way was GCM with a pen, paper and a calculator.

Or rather, not pen and paper but Excel in hand, which meant lots of Alt-Tabbing. Lucky for me points are usually a secondary consideration - status is more important to me - and I almost know the status credits table from memory. (Sad, aren't I?)

 

[serfty]

To get an idea of the SC's earned, I simply put my routing into the "Oneworld Explorer Validator" part of Mileage Monkey and click "Ignore errors (for expert users!)" then click [Go!].

A "# of Qantas SC's earned" table should appear down below with estimate for each booking class.

 

[medhead]

Does anyone know if the same applies if the flight is eligible for a a cabin bonus? Eg: MEL-ADL in J by a PS, SG, or WP, is the math Points + Cabin Bonus + Status Bonus + MPG difference?

Yes, the same applies - base miles by all bonus and then MPG.

Well done and thank you for creating this spreadsheet. indeed impressive.