+ Reply to Thread
Results 1 to 2 of 2

Equity Screening via Relative Value Calculation with Many Indexes

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Canada
    MS-Off Ver
    Mac Excel 2011
    Posts
    10

    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. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    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...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1