Tip #2002-02: Bloomberg's 200 Day Moving Average Field
One of the most widely watched technical indicators, used by market professionals, is the 200 day moving
average. The indicator is nothing more than the sum of the closing price for an instrument over the past
200 days divided by 200. It smoothes out the random up and down action of the instrument and allows you
to recognize a trend for the instrument for which it is plotted. As the instrument continues to trade a
new days closing price is added and the price of 201 days ago is dropped.
Those familiar with technical analysis know that it is important to identify when a financial instrument
crosses above or below a its 200 day moving average. It is indicative of a possible change in trend. The
200 day moving average is also viewed as support and resistance and whether a financial instrument is
above or below its 200 day moving average is a key input into a buy and sell decision for many market
professionals.
No Longer Need to Collect Historical Data
Until recently if you wanted to calculate a 200 day moving average, you needed to retrieve at least 200 days
historical data of the closing price for the financial instrument you were monitoring. If you were monitoring
a good many instruments collecting over 200 days of historical prices for could put quite a toll on system
resources and take a considerable amount of time to update.
Bloomberg subscribers no longer need to collect 200 days of historical data to calculate the value of an
instrument's 200 day moving average. This number is calculated and can be retrieved into an Excel worksheet
by using the field the MOV_AVG_200D. For example the DDE formula to retrieve the value of the 200 day moving
average for IBM is:
| =BLP|MON!'IBM Equity,MOV_AVG_200D' |
Besides the value of 200 day moving average you can also retrieve the value for other moving averages such as 30
day or a 100 day as well as the value of other widely watched technical indicators. If you are a Bloomberg
subscriber you can see what technical indicators you can retrieve for a specific instrument by checking the
fields available in the Technical Indicators category. For example, to see what technical calculated values
are available for IBM:
- Choose the Table Wizard menu choice from the Bloomberg Excel Add-in menu.
- Type IBM in the Ticker List Box click on Equity button.
- Click on the Add button.
- Click on the Next button.
- The fields available for an Equity instrument are organized into category headings which are listed in the Fields Monitor list.
- Double click on the Technical Indicators category to see a display of the 90 technical indicator fields which are available for IBM.
Above Below 200 Day Market Indicator
Now that you can easily retrieve the value of the 200 day moving average into Excel, you can create some
interesting models which can not only monitor the technical health of your portfolios but also various market
Indices. You can now easily answer the question: How many of the Dow Industrial components are above their 200
day moving average?
To show you how, we have created a market monitor model which calculates percentage of the individual components
in the Dow Jones Industrial Average which are above their 200 day moving average. It is available for download
for Bloomberg subscribers.
Our spreadsheet models contain macros and DDE Links. For them to work properly we advise you to download the
model to a local drive and open it with Microsoft Excel rather than opening it with you web browser.
The spreadsheet provides a list of the some real-time data fields for each of the 30 Dow Industrial components
as well as the MOV_AVG_200D field. We used Excel's IF Function to determine whether the component is above
or below its 200 day moving average. The IF formula places a 1 in the cell if the component's last price is
above the price of its 200 day moving average and a 0 if it is not. To calculate the percent above their
200 day moving average, we divided the number above their moving average by the total number of components
in the index.
To make this indicator meaningful, we added some macro code which adds the percent above their moving average
to a database so you can monitor the change in this value over time.
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 using Bloomberg, Bridge, ILX, Factset
or Reuters data.
|
