Importing data from web
You can create or run a Web query to retrieve text or data from a Web page. Web pages often contain information that is perfect for analysis in Excel. Depending on your needs, you can retrieve data that is refreshable. That is, you can update the data in Excel with the latest data on the Web page. Or you can retrieve data from a Web page and keep it static on the worksheet.
Excel is an excellent tool for analyzing data. With data in Excel, you can chart, sort, apply filters, implement grouping with outlining, use pivot tables reports, build regression models, and more. But first you need to get data into Excel and this can be costly and time consuming. This article explains several ways of extracting web page data and importing it into Excel.
Web Queries are an easy, built-in way to bring data into Microsoft Excel from the Web. You can point a Web query at an HTML document that resides on a Web server and pull part or all of the contents into your spreadsheet. You can use a Web query to retrieve refreshable data that is stored on your intranet or the Internet, such as a single table, multiple tables, or all of the text on a Web page. Then you can analyze the data by using the tools and features in Excel.
Web Queries actively use table elements inside the HTML of the page to specify the content to extract. Unfortunately, the dependence on table elements is Achilles’ heel of Web Queries. Table-based web page design used to be very popular when Web Queries were first introduced in Excel 97. Nowadays table-based layout is considered obsolete and inefficient. It has been almost completely replaced by Cascading Style Sheets (CSS). Web Queries do not recognize structured data presented using CSS.
Limitations of Web Queries:
- They have no support for client-side scripting.
- You can import data from only one URL per query.
- There’s no frames support. For parameterized queries, you must be prepared to create or edit an iqy file.
- Sites requiring authentication and passwords provide additional challenges. They may require coded workarounds or may be unsolvable.
- Web Queries lack basic automation like processing multi-page tables or loading data from details pages linked to a primary table.
How to Import data form web?
For an example, now I get some data from Dhaka Stock Exchange Limited web page. For doing this with yourself, please do the following:
1. In Office Excel, on the Data tab, in the Get External Data group, click From Web.
Because of this webpage is not support importing data in above graphical mode I select the text mode for my table selection which is looks like below:
Special Note: If you are redirecting to a new window, don’t worry. Just copy the url from new window and paste to your previous window and close the new one. The maximum length of a URL is 255 characters.
Now my web query just looks like:
3. Now I select my required table and hit import button
4. Select Ok button, after sleeting my location where I want to import my data.
5. All are set guys. a notification is showing in desired cell that excel is getting data from web page and view the data after getting from web page. Just like below two images
How to save a web query in excel?
For the above mentioned example, if you want to save a web query then you should do the following:
1. Click on saved button in the top right corner of the window after selection the table. Do not hit import button.
2. Define your location where you want to save your query file. I select desktop.
4. Now double click on the query icon and automatically you required data is loaded from the web page.
If need to edit the web query, open the with notepad and edit as required.