MS Excel Web Query Feature

Spending time online searching for information is a common task today. Need to find information on how things work or recent developments in weight loss products, going online is usually the answer. Who hasn’t heard the phrase; “Google it…”?

It seems that we can find anything on the web. It has become a great resource for everyone. And now you are probably saying; “ok, Loretta, what has Excel got to do with the web?” If you have been following my previous blog posts, you know I love Excel. I think it is a wonder tool with many flexible features. Excel and the web, have I gone too far?!

No, I have not gone far enough. Excel is great at analyzing data. Web browsers, like Google Chrome or Windows Internet Explorer are great at displaying web sites and web pages. Web sites can contain large amounts of data; the key word here is “data”. Now, you can see where I am going. Excel has a special feature that will extract data from a live web page. That feature is called a web query.

What is an Excel Web Query

Queries are mostly associated with databases, like Microsoft Access. Database queries will allow you to extract specific information from a database. Excel’s web query will take data from one of more tables on a web page and insert it into the cells in a worksheet. Once in the worksheet, the data is subject to analysis just as any other Excel worksheet.

What about good old copy and paste, isn’t that a web query? No, copy and paste gives you static data. The data will never change, although you will be able to analyze it in Excel. If you need data that will not change, then copy and paste could be your answer.

If you need data that is changeable or updated data, then web queries are the answer. The data derived from a web query comes from a live and changing web page. The extracted data is updated data not static data from two weeks ago or yesterday’s values. If you need current data to analyze, a web query is your solution.

Web Queries Have Some Limits

Web queries sound fantastic, but I must warn you of some problems with using a web query.

The time you extract data through a web query, it tends to work perfectly. You might have a problem when you use that same web query the next time. Web queries require a web page that structurally stays the same. If the webmaster or owner of the web page makes changes to the look or formatting of the web page, the web query might not work as well. Web queries are best used on a static, unchangeable web page.

Web queries are associated to a specific web address. If the web page has been redirected or its address changed, the web query will not work.

If the web page requires you to login, a web query does not recognize whether you have logged in to a web page. The web query most likely will fail.

Given the above problems, web queries are useful when the web page structure never changes. Also, look for a web page where the data is in a structured table. Examples of candidates for a web query are; stock quoting and weather related pages.

To show the process of creating a web query, this post will use the National Multi Housing Council web site for data. The “about” statement from the NMHC site, www.nmhc.org, follows;

“Based in Washington, DC, NMHC is a national association representing the interests of the larger and most prominent apartment firms in the U.S. NMHC’s members are the principal officers of firms engaged in all aspects of the apartment industry, including ownership, development, management, and financing.”

The Steps to Creating a Web Query

Decide where to go to get data
The best way is to open your web browser and go to the web page that will have the data that you will put into your Excel worksheet. Copy the web address of the web page. Using your mouse, select the entire web address, then click the combination of the Control key and the letter “c” key; [ CTRL ] + c. That key combination will copy the web page address into your computer’s clipboard.
Open the worksheet you want to use
The worksheet can be a new blank worksheet or an existing worksheet.
Data –> Get External Data –> From Web
Go to the Excel Ribbon and execute the above steps.

Excel will open a “New Web Query” window that looks like a web browser.
Enter web address

In the “Address:” area, paste the copied web address if needed. Click inside the “Address:” area and click a Control key plus the letter “v” combination, [ CTRL ] + v to paste the address.

Select the table to extract

In the New Web Query window notice the black arrows with yellow background boxes. The boxes are the tables that Excel recognizes as data that can be extracted.

newQuery

For this example, the “What Type of Structure Do Renter Households Live In?” will be extracted. Hovering over the arrow produces the blue border seen below.
To make the selection, click the arrow and it will change to a check mark and highlight the entire data area.
- Click Import button
- Choose where data will go
In Excel, an “Import Data” dialog will be opened confirming where you want to place the data in the worksheet. At this point the default location is in the existing worksheet. You also have the option to place the data into a new worksheet. For this example, the data will be placed into the existing worksheet.

The extracted or imported data is now in Excel. The data is ready to be analyzed, formatted or used to create a chart. It is no different than any other Excel values within cells. Except that this data is tied to a web page. When you save the worksheet, Excel will store all the data along with information about the web query’s data source.

If you need the latest information regarding, “What Type of Structure Do Renter Households Live In?” you do not need to recreate the above web query. Just access the worksheet that has the original web query, click anywhere in one of the data cells and select Data –> Connections –> Refresh All. Excel reestablishes the connection to the web page and retrieves the latest data into the worksheet.

In the above example, only one data area was imported. You can import or extract as many data areas or tables as you need within a worksheet. Web queries may not be for everyone, however it is an Excel feature worth trying when researching data on real estate trends and marketing conditions.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.