How to create Dashboards using Excel Web Query?
First, what is Excel Web Query?
An Excel Web query allows you to bring data from a Web site into an Excel worksheet. It will find any tables on the Web page and let you select the ones containing data you want to put into your worksheet, allowing for dynamic updates from the Web page. Web queries are not just useful for pulling information from standard HTML pages. They can also be used quite nicely in situations where a standard ODBC connection would be difficult or impossible to create or maintain, such as a worksheet that’s used by salespeople around the country.
Here is the official Web Query tutorial
Kalyan Verma of MyExcelcius.com sent us this dashboard demonstrating some very unique stuff. First it demonstrates how you can easily pull information from the web and query them in Excel and use the data to convert them into useful dashboards.
I used Excel Web Queries connected to AAA data. The data is being pulled from http://www.fuelgaugereport.com/. The IQY files were modified to pick up parameters from certain cells and those cells were dynamically changed by the List Box input. So when the user clicks on a state from the list box, it inserts the state value in the destination cell which is linked to the parameter of the Web Query. The web query is set to refresh when there is a change in the parameter cell.
The web query results were mapped to certain cells which make up the source data for the charts. I had to use Lookups since the web query had a state abbreviation in it.
Stephen Few has developed a strong authority on Dashboard Design issues and there are lot of followers of his priciples. It seems that quiet a few dashboarders design dashboards to impress Stephen Few and get his vote of approval. This dashboard is no exception. Kalyan has effectively followed Stephen Few’s Dashboard Design principles
# Design dashboards for the color blind: Apart from the logos and the URLs, the only color used in the dashboard is Gray. I used 2 shades of gray since I was comparing National and State prices. The darker shade represents National and the lighter is for the State prices.
It is always wise to follow good color scheme but we have been bombarded with the same look a like black and white dashboards, thanks to Stephen Few’s book. And these are the types of dashboards that Stephen Few likes to approve and bash every other Dashboard developer who don’t follow his principles.
# Design dashboards to fit in one page: I personally feel, that dashboards should never be scrollable. They should be used as “At a glance” Decision making tools, rather than scrolling up and down to compare metrics. This dashboards fits perfectly into a A4 size paper when taken a print in the landscape mode.
Although dashboards should fit on one page, We have seen requirements and dashboard examples where it is not wise to stick to one page rule.
Compare this Gas Dashboard with the above one to get a feel of different ways of implementation, their pros and cons.
In Short “take the best, discard the rest”
Kalyan has a very excellent website on Xcelsius based dashboards and it is worth taking a look.