Being able to perform a web query to retrieve text or data from a web page is a really useful feature in Excel. Web pages often contain really useful information that is perfect for further data analysis in Excel. Depending on what you need, you can retrieve data that is refreshable or alternatively you can also retrieve data from a Web page and keep it static on your Excel worksheet. This article will walk you through the process of creating and executing a web query in Excel.
So how exactly could this be useful? I would be looking at exchange rates on a regular basis, so to always have the exchange rate in particular for USD and GBP refreshing automatically on a regular basis is a huge benefit.
Here is a simple example to demonstrate the power of this feature of this web query in Excel.
- First we need to select the cell where we want the results of the query to appear in our worksheet. In my example I am going to have the results displayed in cell A1 on my worksheet.
- Select the Data Tab
- Get External Data Group
- Get Data From Web option
- New Web Query which will open a dialog box
- Type in the URL or search for the website you want to connect to
- Click the Go button
- Any available data tables will have a yellow arrow next to them
- Once selected the table will have a green tick displayed next to it
- You can then go ahead and click the Import button
- The selected data will then be displayed in your worksheet. How cool is that?
So, what we have done here is automated the mundane task of copying the exchange rates from the Irish Times Website. All well and good what else can we do with it. Right click on any of the cells that you have imported into your worksheet and you will see a few options that are available to you.
You can go ahead and select Refresh which will do exactly that- refresh your query, if we hit the Data Range Properties option, we will then see many more options available to us.
I am setting the query to refresh every single time I open the workbook. My data will now refresh with the latest exchange rates, just by opening the Excel workbook which saves me some time looking for the same data over and over again.