Idea 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.