Tip #2002-8: Using Excel Web Queries to Retrieve Global Historical Data from the Internet
In Willow Tips 2001-11, we discussed using Excel web queries to retrieve Income Statement
information from the internet. A Web query can retrieve the data on a Web page and return it to
Excel for analysis. You can use a web query to retrieve a single table, multiple tables or the
entire web page into an Excel worksheet. Although the data is not self updating, a web query can bring in a lot of information quickly and is ideal for types of
information which are not real-time, such as the accounting information in income statements or
historical pricing information.
Once you have historical pricing information in an Excel worksheet, you can manipulate the
data to create decision making tools such as the charting models discussed in
Willow Tips 2002 - 6 and Willow Tips 2002 – 7. Equity market professionals need historical
pricing information to create performance reports of their holdings against various benchmarks
indices. Month-to-date, quarter-to-date and year-to-date reports rely on historical pricing
information. Another attractive feature is that if you have a high speed internet connection,
historical pricing information can be retrieved quickly. By retrieving historical pricing information on equity instruments from
Yahoo’s finance section, you are not limited to equities which are traded in the US. You can
use web queries to access historical pricing information on equities traded in international
markets. See the Tips and Tricks section for further details.
In this Willow Tip we are going to focus on the how to get historical equity prices from the
internet into an Excel worksheet using a web query. We have created a mini-app which lets you
enter the instrument code into worksheet cell, click on a button and retrieve historical pricing
information from the Yahoo Finance web site. The mini app uses two worksheets. The
Analysis
worksheet displays a table of the historical prices along with a chart. The
WebQuery worksheet
contains the web query. The reason we put the web query on a
separate sheet is that the data needs to be cleaned before it can be displayed in a chart. The historical prices which are
retrieved from Yahoo include rows of data which indicate when dividends have been paid. We
needed to write a macro to remove the dividend data from the historical data.
Creating the Web Query
A Review of the Macro Code
Some Tips and Tricks
Willow Solutions Inc. is a Microsoft Certified Partner with proven expertise and knowledge in developing
proprietary applications used primarily in the financial services industry. The company specializes in solutions
that combine the power of Microsoft Excel and Visual Basic, with an in-depth knowledge of financial markets.
Contact us if you would like to employ our services to help you enhance this model or help you design financial
models or customized applications.
|
