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.
Step 4, Select Import Location:
A dialog will open asking you for the location to import the data to. There are 2 options available:
- Importing data to a range in one of the current worksheets.
- Importing data to a new worksheet.
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:
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.