A web query in Excel allows us to import information published on a public Internet site into our spreadsheet. The query will find the data tables within the indicated web page and will allow us to select the one whose data we want to import into our Excel sheet.
This time we are going to work on a basic example that will give us a clear idea of how web queries work in Excel. To begin we must select the sheet where we want the data to be placed and then go to the file and press the button that is within the group .
Web query in Excel
The dialog box will immediately open and it will be here where we must place the address (URL) of the Web page that contains the data that we need to import. We must place this address in the text box indicated as . For our example I will put the following:
Once the address has been entered, you must press the Enter key or press the Go button that is just to the right of the text box. This will cause the page to load within the web query window.
If the Web Query window is very small and you cannot adequately display the page that has been loaded, you can increase the size of the window by dragging the lower right corner to a suitable size and you can also use the horizontal scroll bars and vertical to scroll the web page.
In case the page you have indicated contains data tables that can be imported by Excel, you will see some small yellow icons in the upper left corner of each table.
These icons indicate that Excel has recognized the data and are candidates for import. On the contrary, if you do not see any yellow icon, it means that Excel does not recognize the data in the format published by the Web page and therefore it will not be possible to import it into Excel.
Once you have identified the correct data table, you should click on its yellow icon and you will see that it will turn green indicating that said table has been selected. To make the import effective, you must press the button .
The dialog box will be displayed immediately which will ask you for the location on the sheet where you want to place the data. Once the appropriate cell is indicated, you must click OK.
Excel will begin to import the data and when finished it will display them within the corresponding sheet.
With this we have imported information contained within a web page, but that is not all as we can tell Excel to update the data without having to revisit the original web site.
Update data from the Web Query
Once the Web query is built, you can refresh the data by just selecting the command that is within the group on the tab . This command will cause Excel to download an updated version of the data automatically without opening the web page.
You must take into account that the data must come from a public domain page, that is, you should not request a username or password or otherwise Excel will not be able to import the data. And of course, if the source page deletes the published data, there will be no way to update.
Another problem that may exist when updating the data is that the original site has modified the Web page, specifically the format of the table that contains the data, so that it can no longer be recognized and imported by Excel.
Automatic update of the Web Query
The command method in the previous section is completely manual, that is, the data will only be updated when you press that button. However, it is possible to instruct Excel to update the data every so often.
To achieve the automatic update of our Web Consultation you must go to the file and press the button next to the button . A dialog box will appear with the list of available connections.
In our example I only have the connection created for the Web query, but if you have more than one connection you will have to select the one that corresponds to the Web query and click on the button which will open the following dialog:
In this new window you should pay special attention to the section which is where you can configure the automatic update of the Web Query. Below I explain each of the options in that section.
- Enable background update: this option is selected by default and it helps us to indicate to Excel that it can update the data even when we are working in another workbook or even in another application.
- Update every: By enabling this option we will be able to indicate to Excel the number of minutes to wait between each data update. You should consider that in each update Excel will use resources from your computer and bandwidth from your Internet connection so make sure to allocate a reasonable amount of minutes according to your needs.
- Update on file open: This option will tell Excel to update the data every time we open the file so that we have updated information when we start our work. Optionally we can tell Excel to remove the data before saving the workbook, that is, the connection will continue to exist and the data will be updated when opening the file, but before saving the workbook, the data will be removed. This is useful when the imported data is very large and we want to avoid that the size of our file increases considerably.
I just have to mention that background update requires Excel to be open. This option will not update the data if Excel is closed and much less if our computer is off.
The applications for web queries can be many. Dashboards could be built that automatically update the information when the file is opened. Web Queries could also be implemented to exchange information from the Internet or in an Intranet (LAN) where some Web application generates information in tables and thus avoid the need to copy and paste the data manually in Excel.