Learning is a never ending thing and the same apply to even basic software’s like Ms-Excel, Ms-Word etc. Recently I tried one of the amazing features of Excel 2007 called WebQuery.
WebQuery is a feature in Ms-Excel which provides us the mechanism to pull data from internet and websites to Ms-Excel spreadsheets. With the help of this feature we can pull data such as stock quotes, cities temperatures, currency rates or anything which we want to pull from various websites. This feature is quite simple to understand and work with. In this post, I have described my experiment with the WebQuery.
What do you need for WebQuery?
To use the WebQuery feature in the Ms-Excel all you need is Ms-Excel and you should be connected with the internet. Since you pull the data from live websites you need to be connected with the internet to use this amazing feature.
So where is this WebQuery feature located in Ms-Excel?
To find the WebQuery feature in Ms-Excel 2007, you can go to the Data tab. You will find the button From Web. In the tooltip it will show Get External Data from Web; this is the WebQuery magic button.
When you click on From Web button it will open the New Web Query window. This is the window where we have to tell which website data we want to pull into spreadsheet. We need to type the website address in the address bar shown in the New Web Query window.
For the demonstration purpose, I want to pull Stock quotes of Bharat Heavy Electricals Limited (BHEL) from Google Finance. My URL in this case is http://www.google.com/ig/api?stock=BHEL. This URL returns the stock price of BHEL) in XML format.
I entered the URL and clicked on Go. The New Web Query window shows the XML value which it pulled from Google finance.
Once you view the data in the web query window, you need to click on little yellow arrow button. This little yellow arrow tells the locations of data points in the web page. This little yellow arrow will turn to green once we click it. It may happen that your website has many data points but you want to pull a specific data points in your spreadsheet. When the yellow button turns to green you have to click on Import button.
Since we are pulling XML data from Google Finance the MS-Excel will track if the website data has a schema or not. If there is no schema the Ms-Excel will create one.
In the next step Ms-Excel asks where you want to put the data. You can pull the data in a XML table in existing worksheet or in an existing worksheet or in a new worksheet.
You can also specify Properties of the WebQuery from which you are importing data from website. You can specify when you want to refresh the data, what data format and layout you want to keep for the pulled data. You may like your spreadsheet to pull the data from websites when it opens or after every 5 minutes. You can specify all these settings in the Properties window.
Once I click on OK, the XML data was pulled from the Google finance and it pulled all the XML tags in the column and their values in the row.
This is how we can use the WebQuery of Excel to pull the data from website. The next thing I did was to extend my experiment with the WebQuery. I have following companies name listed in the spreadsheet and I want to pull their stock prices.
I used the following URL to pull the stock prices of these companies from Google finance.
http://www.google.com/ig/api?stock=SJVN
http://www.google.com/ig/api?stock=BHEL
http://www.google.com/ig/api?stock=GSPL
http://www.google.com/ig/api?stock=KFA
http://www.google.com/ig/api?stock=RCOM
http://www.google.com/ig/api?stock=SAIL
For each company I pull the data in XML format from Google finance and stored the result in Sheet2. The sheet two looks like following.
In sheet 1 I am simply showing the stock quotes which the WebQuery pulls and store in Sheet 2. I have setup my WebQuery properties to pull the data when I open the spreadsheet. I open my spreadsheet two times after interval and each time the latest stock price was listed.
Sheet opened at 14:40 PM IST
Sheet opened at 3:08 PM IST PM IST
I hope you like the article. Thanks! for reading it.
