In this article I will explain how you can get data from the web using a Data Query in Excel. I will be providing an example on how to get the CAD to USD currency exchange rate from the website http://finance.yahoo.com/q?s=usdCAd=x.

Note: The owners of the website http://finance.yahoo.com/q?s=usdCAd=x may choose to change the layout of their website. This  may require changes to be made to the worksheet for it to continue to function correctly.

Step 1:

Click on the From Web button on the Data ribbon:

Step 2:

A web browser will open inside Excel. Type the address of the website in the address bar and press Go:

Step 3:

Wait for the page to finish loading. When the page finishes loading the Import button will be enabled. Click on it:

Step 4, Select Import Location:

A dialog will open asking you for the location to import the data to. There are 2 options available:

1. Importing data to a range in one of  the current worksheets.
2. Importing data to a new worksheet.

Note: This may take a while to load.

Result:

The result will look something like this:

Step 5, Processing the Results:

In Step 4 we imported the web page http://finance.yahoo.com/q?s=usdCAd=x into our worksheet. You saw the results in the previous section.

But what do the results mean? Where did all those lines of text and numbers come from? When you use web query to import data from a website, every single line of text is imported into the worksheet. For example consider the text marked below:

The highlighted data came from the section below:

Or for example consider the imported data below:

It comes from the section below:

As mentioned at the start of the article we are looking for the currency exchange rate of the USD to CAD. Therefore we are looking for the 4 numbers marked below:

These 4 numbers can be found in the section below:

You can hide this sheet, and create reference to these cells in a separate sheet:

Updating Values:

You can update the values by pressing the Refresh All button on the Data ribbon:

You can download the sample file for this article from the link below:

• Website Data.xlsx

Note: If the layout of the website http://finance.yahoo.com/q?s=usdCAd=x is changed, the sample file above may cease to work and thus require modification.

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com