# Equity Screening via Relative Value Calculation with Many Indexes

1. ## Equity Screening via Relative Value Calculation with Many Indexes

Hi,
I am currently working on an equity screening model and I would greatly appreciate some help.

I would like to get relative values of a large list of stocks in terms of its corresponding index.
For example, a 1 month relative value of a stock is calculated by taking the absolute value of a stock and subtracting it from the absolute value of an index of the same period of time. 1 month absolute value being today's value minus last month's value, all divided by last month's value [(a1-a2)/a2].

What this ends up looking like for a relative value calculation is {[(a1-a2)/a2]-[(b1-b2)/b2]}, whereby the (a) row signifies the row for stock absolute value calculation, and (b) row is for the index absolute value calculation.

That's simple enough but it gets much more complex with 4,000 stocks which belong to over 100 different indexes and data gets updated all the time.

I have devised a solution, although I do not know how to technically execute it. I will explain.
The stocks are updated automatically via a Bloomberg add-on. All I do is enter the ticker name, for instance IBM US Equity, and I receive the information that I request in the adjacent rows. In this case I ask for the GICS Industry name. On another page, using this industry name that I got earlier, I derive the index performance for that industry using Bloomberg. I then get the values of the index for the absolute calculations. All of these index absolute calculations are on another page. I did this for all the industries represented and now I have many different tables with all the absolute values for all the indexes for my stated period of time.

I figured that since I have the GICS Industry Name adjacent to the stock name I I could use the GICS Industry name to redirect the calculations for the relative value. For instance if its a beverage company, the adjacent cell under GICS Industry will say "Beverages", I can use that name in VLookUp table.

VLookUp Table will look like this.
When the cell shows
Beverage -> B2 (Beverage index absolute values)
Biotechnology -> C2 (Biotechnology index absolute values)
Chemicals -> D2 (Chemicals index absolute values)

Now that it shows what cells to go to, I would like to use that in a relative value calculation. For instance, a beverage company companies absolute value should minus the associated index absolute value whereby the cell location is specified in the look up table.

Is this possible or have I completely lost you?

2. ## Re: Equity Screening via Relative Value Calculation with Many Indexes

Name the ranges ValueIndex & AbsoluteIndex (highlight the ranges - Formulas - Define Name)

Then you can use the lookups vlookup(B2,ValueIndex,5,0) (where 5 is the column number the info comes from)
vlookup(B2,AbsoluteIndex,5,0)

Use the vlookups as the variable in your formula i.e. =if(and(B2<>"",C2<>""),C2/vlookup(B2,ValueIndex,5,0),"") etc...

There are currently 1 users browsing this thread. (0 members and 1 guests)