Results 1 to 2 of 2

Thread: double sort and percentrank function with NA in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default double sort and percentrank function with NA in VBA

    Hello guys,

    My data set is like this:


    Stock Return Criteria1 Criteria2 Percentrank.INC
    1 0.1 W 0.9
    2 0.3 M 0.7
    3 0.2 NA 0.3
    4 0.1 L 0.5
    5 0.5 M 0.33
    6 0.2 W 0.88
    7 0.1 W NA
    8 0.4 L 0.2
    9 0.2 L 0.77

    What I want to do is to run double sorts in VBA. The sample here is cross-sectional, but my data also has time dimension. What I need to calculate is the percentrank.INC of each stock. Lets take stock 1 as an instance, this stock belongs to group W, and its value of criteria 2 is 0.9. I want to calculate the percentranl.Inc of its criteria 2 within the W group. (We also have NA in both criteria1,2 columns)

    As what I mentioned, I also need to repeat this step over a long period. Therefore, I prefer to using VBA code for this scenario.

    Thank you guys so much.

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: double sort and percentrank function with NA in VBA

    If you want automatic calculation of the results then having a preference of vba over native worksheet functions could be a false economy. If your reason for wanting vba is slow response when entering data then I would suggest formulas in the worksheet with a button to toggle between manual and automatic calculation.

    It would be benificial to add the results that you expect to the example, along with a bit more information about the expected handling of the NA entries. Should all NA's in criteria1 be treated as a group the same as W, M or L would be, or ranked individually (high or low). Should the W in criteria1 with NA in criteria 2 be considered as a third W stock or do we only look at the 2 with a number in criteria2? If it should be included as part of the ranking, how should it compare to the other stocks in the same group?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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