|
After you download the workbook, to review the macro code simultaneously press
Alt F11. This
action opens the Visual Basic Editor. The macro code is well documented. A brief explanation of the
four macros :
The UpdateHistory macro runs when you click on the Update Historical button. It uses Visual
Basic’s QueryTable object to do the web query. The QueryTable object uses a query string to
retrieve historical pricing information from Yahoo finance. The string is nothing more than the
URL we copied with some modifications. What we did was replace the fixed instrument symbol and
fixed end date settings in the query string with variables which contain information, which
are entered on the WebQuery worksheet. Rather than using a fixed month, day and year in the
query string, we replaced these arguments with variables which contain the month, day and year
for today’s date. This use of variables allows the table containing the historical prices to
expand with the passage of time. After retrieving the historical prices, the
UpdateHistory macro calls 3 other macros:
The CleanUpDivs macro goes through the data query and removes rows which indicate when dividends were
paid. Since the historical prices are numeric values and the information about the dividends is text,
the macro uses an IF statement to check whether the value in the cell is numeric, if it is not, then the
row is deleted.
The MoveRange macro copies the cleaned up web query to the analysis worksheet. It creates two
range objects, one to hold the source data and another to hold the target data. The values from
the source range are then transferred to the target range.
The UpdateScale macro adjusts the vertical scale on the
chart. It is explained in detail in Willow Tips 2002-7
along with using Excel’s CEILING and FLOOR functions
|
