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

Cognitive rules of business presentations

In his recent book, Clear and to the Point, Kosslyn explained that the four rules of PowerPoint are: The Goldilocks Rule, The Rudolph Rule, The Rule of Four, and the Birds of a Feather Rule. Here's how they work. The Goldilocks Rule refers to presenting the "just right" amount of data. Never include more information than your audience needs in a visual image. As an example, Kosslyn showed two graphs of real estate prices over time. One included ten different numbers, one for each year. The other included two numbers: a peak price, and the current price. For the purposes of a presentation about today's prices relative to peak price, those numbers were the only ones necessary. The Rudolph Rule refers to simple ways you can make information stand out and guide your audience to important details -- the way Rudolph the reindeer's red nose stood out from the other reindeers' and led them. If you're presenting a piece of relevant data in a list, why not mak...
Post a Comment
Read more

Monetary inflation, Spiritual devaluation

Its been sometime I have been trying to make some special people understand the evils of inflation. Inflation is an abstract subject most of us dont know about, let alone understand the technicalities amidst jargons. I have in my previous post have briefly touched the social part of inflation but never in a concentrated way. I understand what my friends mean when they say "tell me in layman’s language." It is not a heartening sign, that they avoid technicalities. But it could well be that knowing where they stand, their role and understanding the social changes in the light of inflation may motivate them to understand the term "inflation." This is just to highlight the brief points. First and the foremost, is there any link between inflation numbers and society. Yes. The relation is same as the relation between society and money. What is money? Money is an easy means of exchange. If I am selling my horses to a pig-farmer and I am not interested in taking pigs in ret...
2 comments
Read more

Unprecedented External Demand Shock Underway

India’s export growth averaged 24.8% over the last three years, driven by strong global growth. However, over the last three months, export growth has decelerated sharply. While until recently the strong demand from emerging markets including Latin America, Emerging Europe, the Middle East and Africa ensured that export growth remained healthy, over the last three months disruptions in the macro environment of these economies have been evident. Apart from weakening demand, exports have also been affected by the lack of availability of foreign trade credit and inventory liquidation. India’s exports declined by 12.1%Y in October 2008 compared with 10.4% in September and 26.9% in August. While we expect some improvement in the second half of 2009, exports are likely to be unusually weak over the next six months. We now expect exports to decline by 5.3%Y in 2009 compared with 12.7% in 2008 (estimated) and 23.1% in 2007 Excerpt source
Post a Comment
Read more