Tip #2002-7: Some Simple but Useful Macros
If you find yourself doing a task repetitively then you should consider automating the task with a
macro. In Willow Tip 2002-6 we discussed how to use historical price data to create a candlestick chart.
Although Excel’s chart wizard simplified the process of creating the chart, we needed to do some
additional tweaking to make the chart into visually attractive analytical tool.
The most significant change we made was adjusting the price scale on the Y axis so that the upper and
lower boundaries were close to the highest and lowest price for the historical period being charted.
If you are a financial market professional and you wanted to analyze the price action for many
instruments, having to manually adjust the price scale to make the data meaningful would be a
tiresome process. It is exactly this type of tedious task which could be eliminated with an
Excel macro.
We have built upon the previous Willow Tip and created a mini-app which automatically retrieves
90 days of historical data. It runs when you change the symbol in a cell on the worksheet. When
historical data is refreshed a single button click runs a macro, which adjusts the price scale.
A great deal of time is saved. You can concentrate on analyzing the chart rather than editing it.
The macro in our workbook uses Bloomberg as its historical and real-time data source. However,
we have documented and identified the two lines of macro code which enter the DDE formulas on the
worksheet. If you have access to another data vendor who supports Excel DDE formulas, you can
easily modify the code.
How the Macros Work
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.
|
