|
After you download the workbook, to review the macro code which retrieves the historical data,
simultaneously press
Alt F11. This
action opens the Visual Basic Editor. Double Click on the shtCandle object in the Project Explorer
window to display the macro code. The Visual Basic for Applications programming language (VBA) gives
you the ability to tie your macrocode to a specific workbook or worksheet event. For example, you can
have your macrocode run only when a workbook is opened or a specific worksheet is selected. In our
case, we include it in an event procedure, which will run when you type a new symbol for the
instrument on the worksheet. The macro is contained within the Change event procedure. We have
discussed retrieving historical and real-time data with DDE formulas in previous Willow Tips.
For an in-depth explanation of the macro code and how to contain it in a spreadsheet
Change event see Willow Tips 2001-1.
To retrieve new historical data enter the Bloomberg instrument code for the instrument you want to
display on the candle stick chart. When the macro runs it assigns the instrument code to a string
variable (sSymbol).
| sSymbol = Range("Symbol").Value |
The macro then uses the variable in a DDE array formula and places the historical data in a range on
the worksheet.
| Range("Historical").FormulaArray = "=BLP|H!'" & sSymbol & ",[OPEN,HIGH,LOW,LAST_PRICE],PT=90 END=NOW OR=R PD=D'" |
We got the historical data formula by using the Bloomberg Excel Add in to retrieve the historical data for
a particular instrument. We copied and pasted the formula from an Excel worksheet cell into the macro code
in the Visual Basic editor and replaced the instrument code with the sSymbol variable. You can follow
the same procedure to automate the process of retrieving historical data from any data vendor who uses
DDE array formulas to retrieve historical data.
Getting the High and Low
Before you can adjust the upper and lower boundaries of the candlestick chart to the highest and
lowest price for the historical period of price data you need to find the highest and lowest prices.
We used Excel’s MAX function on the range of data containing the daily high prices to get the upper
boundary and used the MIN function on the range of data containing the daily low prices to get the
lower boundary. We have found that using just the MAX and MIN functions made the boundaries too tight.
To resolve this problem, we nested the MAX and MIN functions within Excel’s CEILING and FLOOR
functions. The CEILING function returns a number rounded up, away from zero. The FLOOR function
returns a number rounded down, toward zero. For example, the formula to obtain the upper boundary
for the scale:
The CEILING function takes two arguments, the first is the number you want to round and the second is
the multiple to which to round. In our case we used a multiple of 1. If the highest price during the
period was 49.375, the CEILING function would round the number to 50, which would be the upper boundary
for our price scale.
The macro, which runs when you press the Update Price Scale button, is very simple. It assigns
the values for the upper and lower boundaries to the price scale on the candlestick chart. To review
the macro code which updates the price scale, simultaneously press
Alt F11. The macro code is well
documented and is in the modCandle module.
|
