Friday, August 2, 2013

Traffic Part 2 - DIY Excel Web Dashboard

Our endgame - A customized traffic dashboard.
This is a continuation of part 1 of checking traffic on the web. The previous blog mentioned how to check traffic with no more computer knowledge than using a web browser and saving some bookmarks. This time, we're going to look at making a customized Excel based dashboard that at a glance, graphically shows the travel times for different routes.

Option 3 - Use Excel's Web Query - Build your own traffic dashboard aggregator - thing-a-ma-jig!


While the first two options are likely something you know of, but aren't doing, this option is something else. Excel has an amazing ability to pull data from websites, and display that data on an excel sheet. Our goal is to open one excel file, click "Refresh All", and then see the traffic times for various routes all appear at once, on one webpage, with any custom graphs / graphics or colouring we want!

If you're skilled in Internets programming, and have some time on your hands, you could likely make something fancier than this. The point of this was to create something quickly that would be useful.

I've listed the steps, step-by-step, but some level of Excel skill is required to follow this. You may download a copy of my spreadsheet, here. There are no macros in the sheet, but Excel security will ask you to "Enable Data Sources".

NOTE: this excel spreadsheet is built based on the current layout of the Google Maps search results with traffic. If Google changes the layout of their website, the excel will need to be redone. No guarantees are provided with this excel sheet and method.

NOTE2: I describe doing this with Excel 2007. The process should be similar for any newer versions of Excel, although the exact text on buttons and menus may be different.

Step 1 - As previously described, get the short URL for a given route. It's simpler if you have enough waypoints along the route so that in the "Suggested Routes" section, Google Maps only suggests one route.

Step 2 - Open Excel, Go to the Data tab and click "From Web" (1), then put the short URL (2) in the address bar and click "GO" (3). You can try the long URL, but sometimes Excel's web query doesn't like very long URL names.


Step 3 - Once you click the Yellow Arrow (4), it will turn green, then click the Import button (5).


Step 4 - Click New Worksheet (5) and "Ok"

Step 5 - The data from the webpage will be inserted on a new worksheet. By clicking "Refresh All" (7) under "Data", Excel will requery the webpage and update the cells on the sheet. Clicking Properties (8) brings up the dialog box shown, which allows you to set the "Refresh data when opening file" option (9). Note: this setting is per individual web query, so if you have multiple sheets with different queries on them then each one needs the checkbox set.

Callout (6) shows the current travel time based on Google Maps data.


Step 6 - Apply Excel wizardry!

On the Summary Data tab, at the top I have just used a formula that links to the estimated time value on each route's worksheet.

Further down on the sheet, I have fancier Excel that searches through the worksheets, handles multiple suggested routes in one web query, and displays graphically what the current traffic travel time is versus the standard travel time. By creating a file like this, you can see at a glance what route to take, from one single source.


1 comment:

  1. I was searching for a way to compare travel times on several routes, which I'd like to do daily before my commute. Your solution is better than anything I'd come up with on my own. And to use Excel this way is pure genius. Thanks for the great article!

    -Jeff

    ReplyDelete