Local gas prices report in Excel

This is an old trick with a new tool.  Not exactly related to Vista deployment although I'm sure if I tried hard enough I could find a table somewhere in the deployment tools and mine that data instead.  This is just a cool trick, plus if you are interacting with students this is a really valuable trick they can use when building reports.  One of my Finance professors actually taught me how to do this when I was at UIUC.

First off, find a site with valuable information.  Take for example the MSN site that displays gas prices for your ZIP code.


Excel can fully leverage this page as a data source.  See below.

Open the "Data" page on the ribbon.  Click on "From Web".


Open the site from above and search for your own zip code.  Now drop the URL in the Excel field and scroll down to the table.  Select the arrow next to the table.

That's it, click import and then OK to use the current worksheet.  For a minute you'll see it query the site to pull in data.

Your data will pull in.  Now if you go back to the Home ribbon you can format it as a table (you will lose your data connection if you take this shortcut to formatting).

Use the in place filter to leave only the prices and drop any N/As you desire.

And if you're really ambitious, go back to the design page and click summarize to pivot table.  Drag and drop your rows and columns.  This would be valuable in a much larger data set.

Comments (0)

Skip to main content