If you are unfamiliar with web queries its OK, their easy, and I’m gonna explain how they work and how to manipulate them beyond their intended use to increase their flexibility.
Excel Web Queries Overview
Briefly, an Excel Web Query is a great way to automate the mundane task of going to a web page and copying the data into an Excel sheet. Basically you tell Excel where to look (web page) and what to copy (tables of data) and Excel will automatically import the data into a worksheet for you. These are what we call Excel Web Queries.
Creating Excel Web Queries
When you open a Microsoft Excel workbook in the browser, the workbook opens in a Web-based viewer or in a Web-based editor (if you are using Excel Web App). This viewer and editor support most features of an Excel workbook, but not all. In addition, they support some features differently than the Excel desktop program. Excel for the web looks a lot like the Excel desktop app. However, there are some differences to be aware of. For example, not all file formats are supported, and some features may work differently than the desktop app. This article explains these differences. File formats that are supported in Excel for the web. Excel workbook files (.xlsx). Excel: Changing the web query browser from IE to firefox or chrome Hi, anyone knows how to change this? Im currently trying to load web query for a website (https.
Mar 30, 2017 Open Power BI Desktop, and click Get Data, then click Web. If you are using Excel (with Power Query Add-In installed) you can find the Web connector in the Power Query tab. If you have Excel 2016, click Data – New Query – From Other Source s – From Web. We will use Power BI Desktop in this tutorial, but the steps are the same in Excel.
Lets get started by creating a simple Excel Web Query. I decided to use some basketball stats from espn.com. So to create my basic Excel Web Query I open up a new workbook, find the ‘Data’ item from the menu then go to ‘Import external data’ and ‘New web query’.
In the URL portion of the Excel Web Query window enter the URL of the web site you want to pull data from and click Go. I used ‘http://sports.espn.go.com/nba/teams/stats?team=pho’.
Next you may want to setup some options as far as how the import is gonna look. To do this click on the ‘options’ button in the top left corner of the Excel Web Query window.
In the Excel Web Query options window I chose to return the data using full HTML formatting.
After clicking OK you should be returned to the Excel Web Query window. Now we need to select the tables of data on the page to return.
Note: If the page layout changes frequently you may want to just return the entire page. In that case skip this step.
To select the table of data simply click on the yellow arrow next to the data and it should turn green.
The last thing before we actually import the data that were going to want to do is to save the Excel Web Query somewhere.
To save the web query simply click on the save icon next to the options button of the query window.
After clicking import you may see a dialogue box asking you where to put it and to select any additional properties. Some that you may want to play with here would be the ‘overwrite existing cells’ and ‘fill down formulas’.
Ok, so now that we have our basic web query setup, lets have some fun with it.
Hacking Excel Web Queries
I have compiled a list of NBA teams and their associated 3 character abbreviation used in the URL on espn.com. I’m going to load these into a visual basic combo/edit box and get the web query to update automatically when I select a new team. If you are unfamiliar with how to setup a visual basic combo/edit box please read my tutorial on this found here.
With our teams loaded into a combo box I’m going to open up the saved Excel Web Query and make some changes that will allow it to accept the three character team abbreviation as a parameter when executing.
To edit the Excel Web Query file simply right click on it and select ‘edit with notepad’.
WEB
1
sports.espn.go.com/nba/teams/stats?team=pho
Selection=2
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
To make this Excel Web Query accept parameters for our selected team we need to replace the ‘pho’ of the URL with ‘[“team”,””]’. The new web query will look like this…
WEB
1
sports.espn.go.com/nba/teams/stats?team=%5B“team”,””%5D
Selection=2
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
Note: You can setup multiple parameters here for any part of the URL
After making the adjustments to your URL save the Excel Web Query.
Now that we have a dynamic Excel Web Query ready to be used, we need to first delete our old Excel Web Query cached in the workbook and use the new one we just created. Once you have deleted the original web query in the workbook, go to Data>>Import External Data>>Import Data. A dialogue box will open asking you to select a data source, browse to the web query you just saved and select it.
If everything worked as planned the ‘Import Data’ box should have the ‘Parameters’ button available.
After setting any properties from the properties box, click on Parameters.
As you can see our ‘team’ parameter is available to us now. On the ‘teams’ tab setup we are going to want to have a field that looks up the three character code for the selected team. Then choose the third option on the ‘parameters’ box and browse to that cell.
You may also wish to check the ‘refresh automatically’ check box so that when you change your selected team, the information returned updates as well.
If everything went well you should be done besides any formatting changes you may need to make…
One thing that I did have to mess with to get to work was in the cell that had the three character code used in the URL. I had to use the TEXT() formula to convert make sure it was in the correct format when being passed to the web query. I’ve found that no matter what you’re passing, (dates, numbers, text, etc…) its always good to use the TEXT() function to make sure the value is being read in the correct format.
Downloads:
Want to learn more about Excel?
Or dive in deeper and learn Data Analysis Fundamentals
Excel Web Query - What in the world is that? If you are like the other 99.9% of MS Excel users, you probably have never heard of microsoft excel web queries (note: statistic made up).
Excel web queries are powerful! Web queries are basically like having a web browser built into Excel that attempts to format the content, putting individual pieces of data into separate cells. You can then use Excel formulas (like =A1/B2) to work directly with the data you've downloaded. And you don't have to know anything about perl, cgi, php, javascript, etc.
Getting Started with Sample Excel Web Queries
The example web queries that come with Excel are usually for importing stock quote data into Excel. For more information about using web queries to download Stock Quotes into Excel, take a look at the Excel Stock Quotes Template.
Although importing stock quotes into Excel happened to be the reason I came across Excel web queries, I have since found many other ways to use them.
It is not the web query itself that is the secret that this article is about. Instead, it's the ability to make a DYNAMIC Web Query!
By dynamic, I'm not talking about using 'refresh' to update the data. What I mean is the ability to change the parameters of the web query, such as typing in a new stock symbol into a cell and having the table update automatically.
If you find yourself using the internet to gather data by filling out forms and copying and pasting data over and over, an excel web query might be the solution to your monotonous woes. It's not always going to be better, and it's not always going to work, but it's worth a try, especially since a simple web query could boost your productivity!
Excel Web Query '.iqy' Files
The real key to creating a dynamic excel web query is to create your own '.iqy' file. In it's basic form, the '.iqy' file is simply a TEXT file with three main lines:
You can create the file using a simple text editor! Most text editors will save a new file with the .txt extension, but you can change the extension to .iqy after you create your text file. If it is already a .iqy file and you want to edit it, you may need to right-click on it to select NotePad or WordPad (or some other text editor) to open it.
The third line is the important one! It's simply the URL or web page address. Notice that it contains two parameters. If you don't know what parameters are for, just browse the web for a while and pay attention to what shows up in the address bar of your browser. Go to Google.com for instance, and look up 'excel web query'.
Parameter name / value pairs are listed after the '?' in the URL and are separated by an '&'.
Make the Web Query Dynamic
To make the query dynamic, replace the value of each parameter in the web query file (queryname.iqy) with:
Want to see how this would apply to a Google search? The form that I used above consists of HTML code that looks like this:
Notice that 'q' is the name of the parameter, and the action tells you what the URL should be. The dynamic web query file for a simple google search would look like this:
Reference
Let's Create a Web Query
- Open up a text editor and copy the Google example. Save the file as GoogleSearch.iqy
- Open up Excel and enter a search term in Cell B3
- Open the web query file you just saved (GoogleSearch.iqy) by double-clicking on the file, or open Excel and go to Data > Existing Connections > Browse for More.
- When prompted for the search term, enter =B3. If that doesn't work, just enter a search term for now.
Play around with the web query options and properties by right-clicking within the query results and selecting Parameters or Data Range Properties or Edit Query.
When you go to Edit Query, you can choose to select a specific portion or table within the page or the entire page (by clicking on one of the yellow arrow boxes in the preview). Then, you can save your new query as a .iqy file and look at what parameters it uses.
When you have the query looking just the way you want it, save the Excel web query (an icon or button for saving the web query is in the 'Edit Query' window. When you save the new query, your '.iqy' file will include the options you have selected.
Other Example Web Queries
Below are a few examples of .iqy files that you might try. Websites often change, or they may change the way their APIs work, so these examples might not work forever.
Example 1: Get historical daily stock prices from Investopedia. Note that the start and end dates are text, so if you are setting up date parameters in Excel, the cells you link to need to be text rather than date values. This query uses 3 parameters: Symbol, StartDate, EndDate.
See this article for other sources for historical stock quotes.
Example 2: Get data from a published Google Spreadsheet. See my article Excel Stock Quotes in Excel to see how you can publish a Google Spreadsheet and then use a web query to get the data into Excel.
Become a Web Query Wiz
Take a few minutes to think about what mundane or repetitive tasks YOU do on the internet, particularly cases where you go to the same sites over and over to get data. If you find yourself copying and pasting or re-typing information into Excel so that you can perform calculations with the data, you may have found an ideal use for an excel web query.
There are two main things that you need in order to become a Web Query Wiz.
- A working knowledge of Excel formulas. By this, I mean the ability to use text-manipulation and other formulas. A GREAT book for both learning and reference (and the one that I use all the time) is John Walkenbach's 'Excel Formulas'. If you are an avid Excel user, it will probably be the best investment you will make for a long time! The only problem I have with the book is that I need TWO copies - one for work and one for home.
- A working knowledge of HTML. This is the hard part unless you are a web designer or programmer. The articles listed in the side bar will help to some extent, but ultimately this issue was the reason why I didn't write a more detailed tutorial. Something that will be a great help is to learn about how <form> tags work. Here is my favorite reference.
If you found this article interesting, be sure to check out some of my other Excel tips.
- Jon Wittwer, President
Vertex42, LLC
Excel Web Query File
Cite This Article
To reference this article from your website or blog, please use something similar to the following citation:Excel New Web Query
Wittwer, J.W., 'Excel Web Query Secrets Revealed' From Vertex42.com, 2003