My Flyertalker Yearly Spreadsheet
#16
Join Date: Oct 2007
Posts: 4
_______
http://StatusMatcher.com - Travel like a champ!
#17
FlyerTalk Evangelist
Original Poster
Join Date: Mar 2004
Location: Under an ORD approach path
Programs: DL PM, MM. Coffee isn't a drug, it's a vitamin.
Posts: 12,935
I've updated the spreadsheet collection with a nice contrib from N515CR, a spreadsheet which tracks multiple airline programs with miles/city pair data built in. http://stonecarver.com/flyertalk/spreadsheet.html
#18
FlyerTalk Evangelist
Original Poster
Join Date: Mar 2004
Location: Under an ORD approach path
Programs: DL PM, MM. Coffee isn't a drug, it's a vitamin.
Posts: 12,935
I've updated the spreadsheet collection once more, a couple new ones.
If you have a different spreadsheet template that you would like to share with the community, please PM me or e-mail me through the spreadsheet page. URL in my sig.
If you have a different spreadsheet template that you would like to share with the community, please PM me or e-mail me through the spreadsheet page. URL in my sig.
#22
Join Date: Feb 2012
Location: Ohio
Programs: Marriott Gold, Hilton Gold, UA Silver; 800K+ points 2012
Posts: 115
I'm personally a spreadsheet junkie... I'm sure some of these other ones are documented extensively elsewhere, but just in case anyone else is looking for them, here's some other good tables/spreadsheets:
MillionMileSecrets has a good section putting together many of the different award charts.
The Man of a Thousand Places blog has put together some excellent spreadsheets/resources as well, including:
Anyone have any other good spreadsheets/workbooks?
- John
MillionMileSecrets has a good section putting together many of the different award charts.
The Man of a Thousand Places blog has put together some excellent spreadsheets/resources as well, including:
- A churn planning tool that I used for my first 410,000 mile/point churn!
- Airline miles valuation
- Hotel points valuation
- Several others
Anyone have any other good spreadsheets/workbooks?
- John
#24
Join Date: Dec 2010
Location: MCO via TUL + CMH
Programs: AA EXP, Former Hilton Diamond and Marriott Plat
Posts: 46
I don't travel for business anymore, but I still do a bunch for personal and fun, so I decided to keep maintaining status. However, this has necessitated a few mileage runs, and last year I didn't do the math until so late in the year, I spent Christmas in Vegas.
So I decided to make a spreadsheet so that I would always know where I was/will be.
First try was to take the ARPT.DAT out of the old Flying Fish program and calculate distance using various Great Circle formulas that I found throughout the web. However, I kept getting slight differences between the formulas and the AA / IATA distance (ORD-MCO was 1006 instead of 1005 and PHL-DUB was 3263 instead of 3273).
Then I got directed to this thread and was able to use the table of AA city pairs from one of the spreadsheets posted above. However, its from 2005 and especially with the US merger, there are tons of routes missing.
So... A) Anyone have any idea why none of these formulas work and all are slightly off base? The source data is from Flying Fish and that gets the right distance, so it's something in the calculation.
=3958*((2*ASIN(SQRT((SIN((RADIANS(H10)-RADIANS(J10))/2)^2)+ COS(RADIANS(H10))*COS(RADIANS(J10))* (SIN((RADIANS(I10)-RADIANS(K10))/2)^2)))))
=ACOS(COS(RADIANS(90-H10)) *COS(RADIANS(90-J10)) +SIN(RADIANS(90-H10)) *SIN(RADIANS(90-J10)) *COS(RADIANS(I10-K10))) *3958
=ACOS( SIN(H10*PI()/180)*SIN(J10*PI()/180) + COS(H10*PI()/180)*COS(J10*PI()/180)*COS(K10*PI()/180-I10*PI()/180) ) * 6371000 * 0.000621371
Or... B) Does anyone have an updated AA route chart, or something scrapable to create one with all the new routes?
So I decided to make a spreadsheet so that I would always know where I was/will be.
First try was to take the ARPT.DAT out of the old Flying Fish program and calculate distance using various Great Circle formulas that I found throughout the web. However, I kept getting slight differences between the formulas and the AA / IATA distance (ORD-MCO was 1006 instead of 1005 and PHL-DUB was 3263 instead of 3273).
Then I got directed to this thread and was able to use the table of AA city pairs from one of the spreadsheets posted above. However, its from 2005 and especially with the US merger, there are tons of routes missing.
So... A) Anyone have any idea why none of these formulas work and all are slightly off base? The source data is from Flying Fish and that gets the right distance, so it's something in the calculation.
=3958*((2*ASIN(SQRT((SIN((RADIANS(H10)-RADIANS(J10))/2)^2)+ COS(RADIANS(H10))*COS(RADIANS(J10))* (SIN((RADIANS(I10)-RADIANS(K10))/2)^2)))))
=ACOS(COS(RADIANS(90-H10)) *COS(RADIANS(90-J10)) +SIN(RADIANS(90-H10)) *SIN(RADIANS(90-J10)) *COS(RADIANS(I10-K10))) *3958
=ACOS( SIN(H10*PI()/180)*SIN(J10*PI()/180) + COS(H10*PI()/180)*COS(J10*PI()/180)*COS(K10*PI()/180-I10*PI()/180) ) * 6371000 * 0.000621371
Or... B) Does anyone have an updated AA route chart, or something scrapable to create one with all the new routes?
#25
Join Date: Jul 2014
Location: LAX
Programs: Hyatt Globalist, AA EXP, Hilton Diamond, Wyndham Diamond, DL PM, Marriott Platinum, IHG Platinum
Posts: 1,305
I don't travel for business anymore, but I still do a bunch for personal and fun, so I decided to keep maintaining status. However, this has necessitated a few mileage runs, and last year I didn't do the math until so late in the year, I spent Christmas in Vegas.
So I decided to make a spreadsheet so that I would always know where I was/will be.
First try was to take the ARPT.DAT out of the old Flying Fish program and calculate distance using various Great Circle formulas that I found throughout the web. However, I kept getting slight differences between the formulas and the AA / IATA distance (ORD-MCO was 1006 instead of 1005 and PHL-DUB was 3263 instead of 3273).
Then I got directed to this thread and was able to use the table of AA city pairs from one of the spreadsheets posted above. However, its from 2005 and especially with the US merger, there are tons of routes missing.
So... A) Anyone have any idea why none of these formulas work and all are slightly off base? The source data is from Flying Fish and that gets the right distance, so it's something in the calculation.
=3958*((2*ASIN(SQRT((SIN((RADIANS(H10)-RADIANS(J10))/2)^2)+ COS(RADIANS(H10))*COS(RADIANS(J10))* (SIN((RADIANS(I10)-RADIANS(K10))/2)^2)))))
=ACOS(COS(RADIANS(90-H10)) *COS(RADIANS(90-J10)) +SIN(RADIANS(90-H10)) *SIN(RADIANS(90-J10)) *COS(RADIANS(I10-K10))) *3958
=ACOS( SIN(H10*PI()/180)*SIN(J10*PI()/180) + COS(H10*PI()/180)*COS(J10*PI()/180)*COS(K10*PI()/180-I10*PI()/180) ) * 6371000 * 0.000621371
Or... B) Does anyone have an updated AA route chart, or something scrapable to create one with all the new routes?
So I decided to make a spreadsheet so that I would always know where I was/will be.
First try was to take the ARPT.DAT out of the old Flying Fish program and calculate distance using various Great Circle formulas that I found throughout the web. However, I kept getting slight differences between the formulas and the AA / IATA distance (ORD-MCO was 1006 instead of 1005 and PHL-DUB was 3263 instead of 3273).
Then I got directed to this thread and was able to use the table of AA city pairs from one of the spreadsheets posted above. However, its from 2005 and especially with the US merger, there are tons of routes missing.
So... A) Anyone have any idea why none of these formulas work and all are slightly off base? The source data is from Flying Fish and that gets the right distance, so it's something in the calculation.
=3958*((2*ASIN(SQRT((SIN((RADIANS(H10)-RADIANS(J10))/2)^2)+ COS(RADIANS(H10))*COS(RADIANS(J10))* (SIN((RADIANS(I10)-RADIANS(K10))/2)^2)))))
=ACOS(COS(RADIANS(90-H10)) *COS(RADIANS(90-J10)) +SIN(RADIANS(90-H10)) *SIN(RADIANS(90-J10)) *COS(RADIANS(I10-K10))) *3958
=ACOS( SIN(H10*PI()/180)*SIN(J10*PI()/180) + COS(H10*PI()/180)*COS(J10*PI()/180)*COS(K10*PI()/180-I10*PI()/180) ) * 6371000 * 0.000621371
Or... B) Does anyone have an updated AA route chart, or something scrapable to create one with all the new routes?
Looks like a pretty sophisticated spreadsheet!
#26
Moderator: Southwest Airlines, Capital One
Join Date: Sep 1999
Location: California
Programs: WN Companion Pass, A-list preferred, Hyatt Globalist; United Club Lietime (sic) Member
Posts: 21,585
That looks like a Haversine formula, which is very close but not an exact match for the Earth's actual shape. I don't think you can get much more accurate in an Excel formula.
#27
FlyerTalk Evangelist
Original Poster
Join Date: Mar 2004
Location: Under an ORD approach path
Programs: DL PM, MM. Coffee isn't a drug, it's a vitamin.
Posts: 12,935
I've updated my page of mile tracking spreadsheets. It now includes the 2016 AA Flight Tracker, courtesy of JDiver. You can download a copy at http://stonecarver.com/flyertalk/spreadsheet.html