Using Web Queries to Import Data to Excel

The other day, I bumped upon this powerful and really simple tool in excel. I must say, its one of those gems that is kept inside excel. I felt like a kid getting excited discovering a candy in his pant pockets. Well, to the point now!

Often, business data and analytics deal with lots of Ctrl+C, Ctrl+Vs. For example you might be downloading a financial statement from Google Finance or a rating for a select list of companies form Fitch! or AM Best or even simple top 250 movies list from IMDB. What we generally do is open the web page and copy/paste the data on to our excel sheets. In the end when we perform the analysis and conclude something, it is always "as of some past date". Well, with the help of Web Queries, the analytics can be real time, well, almost.

5 Steps to using Web Queries to Import Data to Excel Sheets:

1. First up, locate [Just locate, you need not load that page in excel] the site / webpage you want the data to be copied from. Remember, this should be on public domain without login access. Simply put, if you type the URL in the browser, the page should load, no matter whether you are logged in to that site or not. (Ex: IMDB Top 250 Movies)
2. Open Excel. Go to Data -> Import External Data -> New Web Query
   
   
3. In the Dailog box for webquery enter the URL you want and say 'Go'.
   
   Select the table you want like this. The table outline would turn 'Green' or something differant. Click on it and you would see the mark becoming like a tick like this.
4. Say Import. In the next dailog enter the starting cell. You can click on the properties to change the behaviour of webimport, but it may not be necessary. While the import is going on you see a cyan colored globe trotting around itself on the status bar. If you cant, blame your office vendor.
5. Once the table is loaded, you can right click anywhere on it and say Refresh Data! and it would fetch the new data from web automatically.

Potential Applications of Web Queries Data Import Tool:

• Automated Portfolio Managers: Enable automatic refresh on File open and load MF, Share price related data from public sources.
• Realtime graphs, Charts and dash boards: You might have some application running on your LAN and generating data. Now assume there is a way to convert such data into HTML on the fly. Bingo! you dont have to copy paste the data to excel now. Just link up once and you are done.
• So many more, I think of almost anything and I see a possibility of using Web Query Imports to simplify that task.