I have been curious about handling real-time flight data using Excel. One of the scenarios of interest in the Airline world is realtime data about flights, airports & delays. FlightStats.com has real-time data available on their site and interestingly also have an API that can be accessed using a developer account. To begin with I signed up for a developer account with FlightStats.Com via https://developer.flightstats.com/. The FlightStats API can easily be accessed using Power Query for Excel 2013 without having to be a coder.
With Excel 2013 & Power Query you really DON’T need to know how to code. Once you register for a developer evaluation account and its approved you will have an Application ID and Application Key as shown below:
The API Documentation for FlightStats is available via https://developer.flightstats.com/api-docs/and there are several APIs that you can use:
· Requests· Flight Status & Track by Airport· Flight Status & Track by Flight· Flight Status by Route· Flights Near· Flight Status & Track by Fleet· Responses· Flight Position Response· Flight Status Response· Flight Track Response· FIDS API
For experimenting with with Real-Time data in Excel 2013 I decided to use the Delay Index API to view airport delays. Information about the Delay Index API can be found at https://developer.flightstats.com/api-docs/delayindex/v1. The Delay Index API offers access to current departure performance at airports. FlightStats samples a relevant time range and considers the magnitude of delay, cancellations, and time of day to generate a normalized score. The delay index is a number from 0-5, used to restrict results to airports experiencing delays [0-5, 5=maximum delay].
You can test out the Delay Index API using the interactive console on the page. I used the Delay Indexes by Region option to get the delays for the major airports in North America:
Once you send the Request, it generates the Request string that you can copy and use in Power Query for Excel 2013. The Request String that I generated with this was:
I used the query string in Power Query’s From Weboption:
This gave me a table with the airports that were experiencing major delays at the moment. This morning, the ones with most delays turned out to be Cleveland, DFW & O’Hare
I wanted to setup Excel to automatically update the delay index in near real time every 5 minutes. To do this, I set the connection properties for the connection in Excel to update automatically.
The Power Map for Excel Screen Capture below shows the delays around 1PM PST today (Dec 30, 2013)
FIDS (Flight Information Display System) displays Flight Arrival & Departure Information such as those commonly deployed in airport terminals & hotel lobbies. I used the FIDS API from FlightStats, Inc. to get a data analyst view of the FIDS with insights on number of flights headed to a destination, from a gate, by an airline.
Here are some screen captures from 11AM to 3PM PST that shows the changes in delays across the country: