Skip to main content

Creating Dynamic Web Query to download fundamental data

I have, at various times, wanted to download the data from financial websites for prices, calculation or tracking or some other purposes. I distinctly remember when I wanted to download the Soyabean prices and quotes from NBOT (which were not publicly downloadable and intra-day charts/data is still not available), and ended up creating a resource hogging monstrous excel which had more downsides than any use.

But almost all the times, have only found frustration in not being able to download the data. Even if the stock data could be downloaded, they were not in the format that will help easy calculation with help of MS Excel. It was in this search that I came across Gummy Stuff and Mr. Ponzo's remarkable macros that helped us download the data from Yahoo Finance. But the only problem was that it could only download the EOD stock prices or market related information. Downloading the fundamental data from the sites like Reuters and Yahoo Finance looked near impossible. Then came web query.

Peter Ponzo's method of downloading the data from Yahoo Finance gave a very good method of how the information could be downloaded from sites, for us to get the data from the static but non-download friendly sites like Reuters, we needed to utilize Ponzo's URL concatenation as well as use the web query. In other words, dynamic web query.

Even though there were some good material available on creating dynamic web queries, the pursuit always ended in distress. So, after long last when I finally made it (yeah!) I decided to write a post on how I did it for everybody who would like to know more about how they can build a dynamic web query to download data and information from sites like Yahoo Finance, Bloomberg, Reuters and Marketwatch. Though I have not tested this against other sites, it should be possible.

Step 1: Open the web page that you want to download the data from and copy the URL (Here I am downloading the Balance Sheet of IBM)


Step 2: Open the Web Query browser in Excel and the use the above URL



Step 3: Import the data from the website, ensure all the data is downloaded. (It could be possible in some cases to download only the relevant table, but with sites like Reuters it is not possible)


Step 4: When the data is loaded, before you click import, save the Web Query


Step 5: The file will be saved as an IQY file. Open the file in a notepad, it should look like this:


Step 6: Replace the parameter with a distinct phrase enclose in square brackets. Here the distinctive parameters is IBM.


When done, Save and close the file.

Step 7: Open a New or Excel file where you want the data and Import the Query


Step 8: Select the query and Import the data


Step 9: The system should ask you for the Parameter to be entered by displaying the phrase you had quoted


Enter the Reuters Symbol for the stock, and the specific data will be downloaded on to the system.

The URLs are similar for most of the downloadable content, so by playing with the URLs and the parameters defined, users should be able to configure most of their requirements for download. For example Yahoo Finance's fundamental page has the following URL: http://finance.yahoo.com/q/ks?s=IBM, here the distinct parameter being IBM; replace "IBM" and you will have your query ready.

See a sample sheet here

Labels:

Comments

Post a Comment

Popular posts from this blog

How-to overcome sendentary lifestyle.

Some interesting points on overcoming sedentary lifestyle. There are many other routine ones, just try googling that. 1. Some not so routine tips on avoiding the sedentary lifestyle 2. Walking while talking on phone is a good way to get expressive and burn calories too. 3. Don't use the phone to call someone a short walking distance away. Walk and visit whenever possible. Same for grocery shopping. 4. Catch a different bus that makes you walk a bit more. You can catch a bus from a different stop, a little farther from your regular stop. 5. Pacing the length of the subway platform whenever the train/bus is late. 6. Dance Source: from various places on net
Post a Comment
Read more

Net Neutrality - Fight for free expression

The "Net neutrality" issue, where we fear networks/infrastructure providers will decide (euphemism for censorship) what customers will read/watch/listen/buy on net. Many of the features that we fear in the "Net-neutrality" have already been implemented across various other platforms like telecom. For example, my telecom service provider has blocked the competitors GPRS sites and also does not allow access to many other sites on Internet, from where the free goodies like mobile applications, software, games, wallpapers, tones etc can be downloaded. This leaves me with the only service provider for above products, the service provider himself. This is totally blocking my freedom and my right to choose. This is just a glimpse; with "Net-neutrality" issues at stake are enormous. There should be no way; any entity (maybe, other than individual governments.) should be able to determine what is accessible and what is not. :) Falkor
Post a Comment
Read more

Definition of Cult Stocks

From a blog: A classification describing stocks that have a sizable investor following, despite the fact that the underlying company has somewhat insignificant fundamentals. Typically, investors are initially attracted to the company's potential and accumulate positions in speculation that its potential will be fulfilled, providing the investors with a substantial payout. While most of these cult stocks promise they will be the next big story after they make a new discovery or get the newest contract from the government, most do not provide investors with anything other than the story. Furthermore, these stocks typically generate very little, if any, revenue at all. For example, many micro-cap biotech stocks are cult stocks. While they promise that they are going to be working on a miracle compound or drug, most of them do not have any source of income as they slowly burn away their initial capital in research and development. However, some cult stocks do occasionally make good on...
Post a Comment
Read more