Abnormal Return & market choice

Today I had a student who wanted the abnormal return for a couple of lists of stocks but wanted to choose different markets depending on the stock. To get the Abnormal Return from datastream you can use the following formula:

REGR#(PCH#(X(LI),1D),PCH#(X*1.0000000,1D))

I posted about the formula earlier and the post explains that the LI stands for the comparison Local Market Index. In this case that is not the index that was needed for some of the lists that the student wanted to use, because, LI usually is the index of the main exchange where a company is listed. See the previous post on how to find out which index for each equity this is.

To change the formula, you can exchange the market by replacing the X(LI) with a Datastream mnemnonic for an index. Example for the Dutch midkap index AMSMKAP:

REGR#(PCH#(AMSMKAP,1D),PCH#(X*1.0000000,1D))

If you also need to change to multiple indices/markets for a list the same method can be used to generate formulas like I did for generating a list of BETA formulas. You can download an example here for a list of Abnormal Return formulas. Using such a list an example Request table sheet in Datastream would look like this:

The same thing also applies to other Datastream formulas, like the Alpha formula:

REGA#(LN#(X(LI)/LAG#(X(LI),1M)),LN#(X/LAG#(X,1M)),60M)

You can download an example Excel sheet here as well.

Email

Using Excel to download the BETA

In a few earlier posts I explained how to use the formula to get the BETA from Datastream using the following example formula:

REGB#(LN#(X/LAG#(X,1M)),LN#(Y/LAG#(Y,1M)),60M)

The formula can be used and in Excel you can generate a list of formula’s. I explained this in a follow-up post. The important thing to remember is that the list you can generate this way can only be used in a Request Table type search.

To make it easier I have now posted an example Excel sheet that allows you to more easily create a list of equities (with the markets/indices). You can download it here.

Please remember that this excel worksheet is just an example! I randomly used equities and indices. When using the formula it is necessary to choose the right list of equities and indices/markets that are right for your research!

Email

Beta and choosing the market

In the past I have posted several times about the BETA and using the formula that is available to download a historical (rolling) BETA using Datastream. Using the formula in combination with a Request Table you can download varying BETA’s for many equities for many specific time periods.

To quickly recap, the BETA is “a measure of the risk arising from exposure to general market movements as opposed to idiosyncratic factors. The market portfolio of all investable assets has a beta of exactly 1. A beta below 1 can indicate either an investment with lower volatility than the market, or a volatile investment whose price movements are not highly correlated with the market. . . . A beta above one generally means both that the asset is volatile and tends to move up and down with the market.” (See wikipedia).

Now what can you choose as the benchmark/market if you have a mixed selection of equities from different exchanges? The benchmark is often chosen to be similar to the  chosen equities. For example, for an S&P 500 index fund and gold, the index would be the S&P 500 and the price of gold. In practice a standard index is used. If your selection of equities is large and very mixed it may not be easy to determine the standard index.

Using Datastream, however, it is possible to use the option of a static search to find out what Datastream considers to be the benchmark for each equity. It is the same static search that let’s you find out what the benchmark is when using the Datastream formula for the abnormal return using the code LI#MNEM or LI#NAME. Example:

Using the codes that you then get, you can easily use Excel to generate the BETA formula that can then be used in a Request Table search for different time periods. Example:

The BETA formula that I use here compares the result of 1 month to that for a period of 5 years (60 months), but the formula can be changed to days or other (sensible) options. You can download an example Excel sheet here.

Email

Using the BETA in Datastream

In an earlier post I explained how to get a historical, changing, BETA for equities from Datastream. Using the specific formula it is not that difficult. If you need to get the BETA for a larger selection of equities in one go I explained in a follow-up post how excel can be used to easily combine the formula with different equities (and even markets,) should you wish to do this.

What I may not have explained clearly is, that you can not upload the list of formulas like you would a regular upload of a list of equities. If you try to do this and then use the list the Datastream software is unable to handle this search request and will display as the end result error messages like:
$$”ER”, E200, EXPRESSION TOO LONG

or:
$$”ER”, E100, INVALID CODE OR EXPRESSION ENTERED.

The only way to use a list of equities and download a historical BETA, is using the Request Table (RT) option. Just copy the list of formulas in this and it should work. Here you see an example how the RT should look:

The download result will look as follows:

NB: A Request Table search will also allow you to use different time frames for different formulas/equities for instance if you want to do an event study using Datastream. You can download an example Request table here.

Email

Stock Volatility and the P/H value

Volatility of stocks and indices has increased in importance the last few years. It is a measure to indicate the risk involved in investing in a stock or stock options. There are several methodes that indicate the volatility for individual stocks. One of these measures is the BETA of a stock. In previous blog posts I have shown how to get the BETA from Datastream using the BETA formula and how to use the formula for several stocks in one go.

Another method to get an indication of the volatility of a stock is the P/H value. The basis of this easy measure for volatility is the difference between the highest and lowest stock price over the last 12 months. The P/H value is calculated by dividing the difference of the highest and lowest price by the sum of these and multiplying this by the standard value 200. The formula looks as follows:

PH = ((PriceHighest – PriceLowest) / (PriceHighest + PriceLowest)) * 200

If the PH value is low then the stock price did not vary very much the last 12 months and has a low volatitlity. If the PH value is high, the stock has a high volatility and it may be riskier to invest in it (or in the stock options).

Here is an example of this I did in Datastream using the functions Max# and MIN# for Unilever and AIR/France KLM for the past year: 7-3-2011 to 7-3-2012. Below you see a screen-capture of the Time Series search I did using the Datastream AFO in Excel:

The formulas (divided by a comma) in Datastream were:

MAX#(X(P),07/03/11,07/03/12)
MIN#(X(P),07/03/11,07/03/12)

The result of this search for both Unilever and AIR/France KLM is shown in Column C of the following screen-capture:

The P/H value for Unilever is much lower than for AIR/France KLM. This shows that the stock of the latter was much more volatile over the last year. If I compare the rebased price for both stocks over the last year this is also immediately apparent:

 Email