Mining The Web

by Neville Millar

Can we really use something that’s already available on our computers to get the data we want to help us in our trading? Find out here.

It may have its limitations, but Microsoft’s popular spreadsheet program Excel can be used to do everything from archiving historical financial data, to tracking technical indicators, to maintaining options valuation/volatility models. Oftentimes, you use cell formula scripting to create Excel utilities involving mathematical computation. An alternative to cell formula scripting is the Excel Visual Basic for Applications (VBA) programming engine, which can be used to build software programs that do everything from processing advanced analytical tools or trading system models to data mining the Internet.

IT CAN BE DONE
VBA programming code is easier to understand than advanced cell formula scripting, and Excel provides an easy-to-learn programming environment. The built-in macro recorder is an excellent set of training wheels for the beginner, showing you how various Excel tasks translate into programming code. Learning VBA at your own pace also gives you the opportunity to discover different ways to build market-timing indicators and fundamental analysis tools that are in line with your trading/investment decision-making temperament.

My objective in this article is to show you how Microsoft Excel VBA can help speed up the process of downloading data from financial websites. Manually transcribing data from web pages can be tedious, and Excel workbook downloads available on popular financial websites such as Yahoo.com and NASDAQ.com are formatted in the inverse order of the standard database architecture.

FIGURE 1: YAHOO HLC TAB. Click the “reformat raw data” button to generate the data in the correct sequential order in columns AA to AG. Here, Apple (AAPL) data has been reformatted.

…Continued in the June issue of Technical Analysis of Stocks & Commodities

Return to Contents