Excel Getting Data From the Web

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.


Contents

Step 1:

Click on the From Web button on the Data ribbon:
From Web


Step 2:

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


Step 3:

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


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.

Import Location
Note: This may take a while to load.


Result:

The result will look something like this:
1
2
3


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:
1-Process
The highlighted data came from the section below:
Highlighted data
Or for example consider the imported data below:
3 - Process
It comes from the section below:
From Web 2
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:
CADUSD
These 4 numbers can be found in the section below:
USD CAD, Data
You can hide this sheet, and create reference to these cells in a separate sheet:
USD CAD Reference


Updating Values:

You can update the values by pressing the Refresh All button on the Data ribbon:
Refresh Values
You can download the sample file for this article from the link below:

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *