Extract data from a webpage into an Excel spreadsheet


Posted in: Microsoft Excel

Web Queries are simple but extremely powerful features of Microsoft Excel that help you import live data from external websites into your Excel sheets – all you have to do is visually select parts of a web page in the browser and Excel will do the rest.

With Excel web queries, you can import information like Google search results, the latest CNN headlines, stock quotes, exchange rates or even monitor regular websites for changes.

Get data from web pages in Excel

As shown in the figure above, click on the “From Web” menu in the Data group -> Get External Data. A new web request dialog pop-up – enter web url here (see Google News example below).

Click on the yellow arrows next to the tables you want to import into Excel and import.

You can do the same using IE: just go to the web page with the data and select “Export to Excel” from the context menu of Internet Explorer.

Once the data is in Excel, you can do all kinds of complex tasks like conditional formatting, sorting, charting, etc. You can either keep this data static or configure it to refresh automatically. Excel will therefore automatically update the worksheet whenever the source web page changes.

excel-web-pages

Microsoft also provides another Web Data Add-in for Excel 2007 (link) it’s even smarter. You make a few selections on a web page and it will automatically recognize other parts that match your pattern.

For example, I just selected the first result in the Google web search page and clicked the “Select similar items” button. It recognized all the titles of other web pages appearing in the search results and imported them into Excel.

Now I can just open this Google – Excel spreadsheet, click Refresh, and I’ll know almost instantly if the webpage rankings have changed. Like RSS and Yahoo! Pipes, there could be many creative uses for Excel web queries.

google-in-excel


Comments are closed.