|
|
|
|
|
| |
|
|
|
|
Some Tips and Tricks
- Use dynamic range names to keep your chart updating automatically. If you are going to use
this mini-app to retrieve historical data over time, the range containing the historical data
will continue to expand. We created dynamic range names, Date, High, Low and
Close and used them
in the chart series. As new data is added to the table, the chart will expand automatically.
No macro code is needed. Creating dynamic range names is discussed in detail in
Willow Tips 2000-3
- To maintain flexibility let Excel do as much of your calculation as possible. For example, we
could have written macro code which would have calculated the month day and year for our end
date. If for some reason you want to use a different end date, you can easily change the
value in a single cell rather than trying to change the macro code.
- Each time a web query is run Excel creates a name for it. This would not be a problem if the
web query was always the same. However, each time historical data is retrieved it is a new name is created. In order to keep this workbook from becoming bogged down
with a numerous web query names, we created a macro which runs when the workbook is closed and
deletes all the web query names. The macro is contained in the Workbook
On_Close event. Excel
puts a number at the end of each new web query. When the workbook is
closed the macro deletes all
names which end in a number.
- You are not limited to retrieving historical prices just for US traded equities. Yahoo has
local sites which allow you to retrieve historical prices on issues traded in
other countries.
We were able to use web queries to retrieve historical data on equity instruments traded in
the UK and Germany by going to the finance sections of those countries.
|
|
 |
|
