Web Query
Are you copying data to excel from some web pages everyday? Why bother when you have web query tool in your arsenal? With web query tool you easily import data to your workbooks and simply refresh them everyday.
There are alot of ways to import external data to excel. But importing a data table from a website is probably the easiest among them. You don’t need to write codes, you don’t even need to mess with menu screens. Here is how it is done:
Lets assume that we need to get daily US stock market figures for our reports. For that task, go to data tab and select “From Web” option from “Get External Data” section.
After selecting “From Web” option, you will be greeted with a browser window within excel. This is where we setup our web query. Browser window will have your homepage loaded by default, for this example type www.investing.com/markets/united-states or google it from browser and navigate from homepage to US Stock Market figures page. You will notice small arrows around upper left corner of data table. Click on it. Here is how it looks at this stage:
After you clicked on the arrow, click import button at lower right corner of browser. Now you will be asked about where do you want your data to be placed. Select a cell for that and you will se a message appaer on that cell stating your data is being imported. This will take a couple of seconds and your data will be placed where you selected. Here have a look:
Now that all the hard! work is done, do some formatting or insert it into your report. You are done. All that remains is right clicking any cell of this table and select “Refresh” from right click menu. Your data will be refreshed.
Notes:
- Try to find pages with clean html tables to use with web query. Tables with animations or rich formatting may mess up your selections.
- If you like to get into writing queries anyway, you may find instructions in this page: http://office.microsoft.com/en-001/excel-help/get-external-data-from-a-web-page-HA010218472.aspx
To see the finished product and check it yourself download our example workbook (Give it a refresh and see what happens).
