There is so many data on the internet that is continuously changing. Common example is: Currency Exchange Rates and Stock Prices. This type of information is held in the Web Page Construct called Table. Excel allow us to create a web query that goes to page, get the table data and populate the cells in Excel. You can use this query to get data at any time from the page or you can set timed automatic update. We’re going to update Exchange rate in Excel in this tutorial. We’ll use the http://www.x-rates.com/table/?from=USD&amount=1 website for this lesson.

We’ll be using Blank WorkSheet for this tutorial.

Step#1:

Pic1

 

We’ll focus on the first cell A1 in the sheet because this is the place where we’ll extract our Currency Table.

 

Step#2: 

Go to DATA Menu in Ribbon bar. And select From Web.

Pic2

Step#3:

Clicking “From Web” in “DATA” Menu, will open a web browser in front of you.

Pic3

We’ll enter http://www.x-rates.com/table/?from=USD&amount=1 in the Address bar. And click “Go”.

Step#4:

In the browser window, hit the url http://www.x-rates.com/table/?from=USD&amount=1 and it’ll navigate to page containing Currency Tables.

Pic4

These Yellow marks display the table existence on this page.

Step#5:

Selecting Yellow mark will highlight the table and Turns the yellow mark Green.

Pic5.1

After selecting, Click import button. It’ll bring all the data to your worksheet.

Pic5.2

Step#6:

All the data is imported from the table from URL. To get the updated value from the site, Right click on any of data cell in the table, and “Refresh”.

Pic6

It’ll update the values of the table.

Step#7:

To get timed automatic updates from the site, right click on any of the value of table, and Select “Data Range Properties”.

Pic7.1

It’ll show a window dialog containing several option regarding Query.

Pic7.2

You need to check “Refresh every” and define time. You can as low as 1 minute of refresh rate.

 

This was the tutorial showing how to extract Table data from URL using Excel Query. For any question or feedback, kindly leave your reply below.


Leave a Reply