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

Value of dollar - Part 1

A Simple Perspective Will Do The date is 2000-05-28. Don't you get tired of all the bad news bears reminding you of all these instabilities, excesses, and 'potential' tensions in the global economy? After all, hasn't it always been like that? Yes it has, but not in money it hasn't. Increasingly, investors find it harder to know where to put their savings. What about Government Bonds? Wrong. Their recent record of capital losses have wiped out your guaranteed yields, probably because the stock market keeps crowding them out, and this even in a strong dollar and low inflation environment. Furthermore, there is no reliable liquidity and potentially poor quality debt in the corporate sector. Foreign assets? Wrong. Most of the world's economies are riskier, have been under performing, and also, there is this thing called currency risk. Like how is the average person gonna cope with currency...
Post a Comment
Read more

Depreciation of British Pound 1900-2000

When the Bank of England was formed the powers to create money was finally transferred to private hands. The creation of Fed in US, was just a part of this cycle. Though it is a common knowledge US Dollar has depreciated nearly 100% since the creation of Federal Reserve, the same is the case of all the currencies across the globe. For example, below is the UK Parliament data that highlights the depreciating value of Pound.
2 comments
Read more