First off, use XY Scatter not a line graph in Excel. This will allow you to vary the distance between stations based on mileage, instead of each stop being equivalently spaced.How did you add the vertical lines for the stations? In office or in Photoshop?
I'm working on the CZ and SWC.
I will try that for the vertical lines. I'm plotting roomette/bedroom as they are listed on AmSnag. Should I add the coach fare to it? Also, what about the station symbol text next to the lines?First off, use XY Scatter not a line graph in Excel. This will allow you to vary the distance between stations based on mileage, instead of each stop being equivalently spaced.How did you add the vertical lines for the stations? In office or in Photoshop?
I'm working on the CZ and SWC.
So doing that, your "X" value (let's say the first column) should be the mileage from the origin. If the origin is not the terminus (like Atlanta), you can use negative values to represent mileage.
The second column should be the low-bucket coach fare. The third column should be roomette upcharge + coach fare. Fourth would be bedroom upcharge + coach fare.
Now, for the vertical lines:
Whatever the highest fare is, round to the nearest 50 and make that the top of the graph. Create a 5th and 6th column in your spreadsheet. For whichever stations you want a vertical line, in the fifth column, put the mileage in the row for that station. Copy that number into the row right below it. Next to the first mileage in the 6th column, enter the number 0. Next to the second mileage in the 6th column (right below the 0 you just entered), put whatever the top of the graph is + 1. For the next city you want a vertical line, the max+1 goes above the 0. This creates a zig-zagging vertial line in all the appropriate places.
PM me if you still have questions. Glad to hear you're working on other routes.
The way AmSnag displays data is the same way Amtrak's website would. So, for instance, the coach fare might be $57. On Amtrak, were you to select a sleeper upgrade, it might say "add $241" for a roomette. So that would be 57 + 241 or $298.I will try that for the vertical lines. I'm plotting roomette/bedroom as they are listed on AmSnag. Should I add the coach fare to it? Also, what about the station symbol text next to the lines?
I checked this. The Empire Builder is probably #4 for a mid-route destination with Minneapolis (24.1%), and the Zephyr is #3 with Denver (33.9%). #2 is Atlanta (37.6%). #1 is Orlando/Tampa, which combined account for negligibly under 40% of the business of the Silvers (290,587 of 745,872)...not strictly comparable, I know. The Silvers are odd, though, in that their endpoint city is actually a pretty limited-sized destination (90k) compared to overall ridership (745k), though this is probably due to both having about four other stops on the way down the coast (Fort Lauderdale and Hollywood have more business combined than Miami) and due to the amount of business Orlando gets. It's still interesting, though: Tampa alone has more business than Miami, and it's only got one train per day (33.3% of the Silver Star's business is strictly Tampa).These charts are neat. Useful way to see where the peak market pricing is for LD trains. Of course, if people going to Atlanta start booking roomettes or bedrooms to stops beyond Atlanta but getting off in Atlanta becomes widespread, Amtrak will have to adjust the pricing to compensate once Amtrak figures out that people are doing this.
The FY2010 numbers for the Crescent and Atlanta are interesting.
Crescent: 298,688 total passengers
Atlanta boarding & alightings: 112,364
Since Crescent is the only train serving Atlanta, that means a full 38% of the Crescent passengers are either traveling to or from Atlanta. Which has got to be close to the largest market share for a city that is not the endpoint of one of Amtrak's LD trains. Shows that Atlanta is an under-served market that could support more Amtrak trains if Georgia and Atlanta political leadership were so inclined.
Another thing to point out about the Crescent, is that it is one of the 5 LD trains that is to be the subject of a Performance Improvement Plan for FY11. The FY10 PIP reports for the Cardinal, CL, CZ, Texas Eagle and Sunset Limited were released last October. The FY11 PIP reports are due for the Silver Star & Meteor, Palmetto, LSL, and the Crescent. So there must be a staff group inside Amtrak that has either started or will get started soon to review options for improving the Crescent. But I guess they are not set up to take public comments or suggestions.
Ryan,That's pretty clever, I copied the graph into PPT and added the lines and station codes there. Kind of a pain in the neck, though, your way sounds easier.
And I did it in Word.Yep - looks like you can do it all in Excel as well...
Good work, 2104. One thing, though, you drew a false conclusion (or at least an unsupported one) regarding the Zephyr.Here they are: No real surprises, unfortunately.
The California Zephyr from Chicago. Note that the fare in all classes increases after DEN, and again after SLC. The jump in the beginning is from the shared portion of the route east of GBB to the first stop west of GBB. Also, the fares in CA and the sparsely populated areas between SLC and REN are relatively flat, probably a means of CalTrans to encourage Amtrak travel.
None of the major trains to the Pacific Northwest ever originated in Minneapolis, and the Empire Builder doesn't stop there now. Remember that the CB&Q was controlled by the Great Northern and Northern Pacific, so all the name trains (the Empire Builder, the Mainstreeter, the Western Star, the North Coast Limited) started in Chicago. That's true in the postwar era, though I might be wrong about the 60s, when many trains were consolidated. The same is true with the Milwaukee Road's trains, as long as they lasted.I would take this moment to point out that Denver-Chicago and Minneapolis-Chicago were two decent-sized markets for intercity travel, and that most of the trains headed to the West Coast originated there (I think possibly with through car service from Chicago in both cases, but they both had plenty of dedicated trains running that route).
Enter your email address to join: