+ Reply to Thread
Results 1 to 8 of 8

Scoring/Ranking 2 Columns of Stock Symbols

  1. #1
    Bohica
    Guest

    Scoring/Ranking 2 Columns of Stock Symbols

    There is probably a very simple solution to this problem, but I can't figure
    out how to address it.

    I'm ranking a list of stock symbols, best to worst, by 2 different criteria
    through a separate program. The list consists of about 7000 symbols.

    I want to dump the list (based on external software rankings) into 2 Excel
    columns, based on the rank from each of the 2 criteria (this ranking will be
    performed by external software). Then, I wish to instruct Excel to rank the
    symbols based on the combined scores of the two column, from best to worst,
    in a third column (or however many columns it takes to accomplish this).

    Additionally, I'm hoping that Excel can be instructed to list the stocks
    from best to worst, based on the combined scores.

    Can somebody help me with this? I'm sure this is basic and I'm happy to pay
    for any assistance.





  2. #2
    Pete_UK
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    Your data will occupy columns A, B and C (assume it starts on row 1).
    In D1 you can add this formula:

    = B1 + C1

    to add the ranks together. To copy this formula down 7000 rows in one
    simple operation, click on the cell then double-click the fill-handle
    (the small black square in the bottom right corner of the cursor). You
    might want to fix these values - click <copy> as the range will still
    be highlighted, then Edit | Paste Special | Values (check) OK and
    <Esc>.

    You can then highlight all the data in columns A to D and Data | Sort,
    choosing to sort on column D in ascending order.

    Hope this helps - no need to pay!

    Pete


  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    http://xldynamic.com/source/xld.RANK.html

    go here

  4. #4
    Pete_UK
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    Dave,

    the OP stressed that the ranking would be done externally.

    Pete


  5. #5
    Manfred
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    Thank you very much for taking the time to respond, but this solution does
    not seem to work. Shouldn't I only need 2 columns? Also, the formula
    yields "#VALUE?" when I paste the stock symbols into the spreadsheet.

    Perhaps I should have been clearer that each of the two Excel columns will
    consist of STOCK SYMBOLS (not number rankings).

    So, for example, the first column will be pasted into Excel from an external
    ranking program as:

    LECT
    MFBC
    CTIG
    FBEI
    MHJ
    WRLT
    .....etc.for 6994 more rows

    And then the second column, using a different formula (but consisiting of
    ALL the SAME symbols) will be pasted, for example, as follows:

    PLFE
    WTU
    BLKB
    EPIC
    AFBA
    MTG
    ....etc. (all the same as above, ultimately, as above but in a different
    order) for another 6994 rows.

    I'd like to take these two columns, as rank them from highest to lowest
    based upon each symbol's positions in BOTH columns. So, if a symbol ranks
    232 in column 1, and 153 in column 2, the combined rank is 385.




  6. #6
    Biff
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    Hi!

    If you want as the last step to sort on the rank then you'll need a third
    column of the symbols (in any order).

    Assume the symbols are in one order in column A, A1:A7000, and are in
    another order in column B, B1:B7000.

    Copy one of the columns of symbols (either one, doesn't matter) to another
    column, say, column F, F1:F7000.

    Enter this formula in G1:

    =MATCH(F1,A$1:A$7000,0)+MATCH(F1,B$1:B$7000,0)

    Copy down to G7000. For quick copying just double click the fill handle.

    Now, sort on column G.

    You're probably going to end up with lots of ties!

    For example:

    249+1
    1+249
    125+125
    10+240
    150+100

    Biff

    "Manfred" <[email protected]> wrote in message
    news:[email protected]...
    > Thank you very much for taking the time to respond, but this solution does
    > not seem to work. Shouldn't I only need 2 columns? Also, the formula
    > yields "#VALUE?" when I paste the stock symbols into the spreadsheet.
    >
    > Perhaps I should have been clearer that each of the two Excel columns will
    > consist of STOCK SYMBOLS (not number rankings).
    >
    > So, for example, the first column will be pasted into Excel from an
    > external ranking program as:
    >
    > LECT
    > MFBC
    > CTIG
    > FBEI
    > MHJ
    > WRLT
    > ....etc.for 6994 more rows
    >
    > And then the second column, using a different formula (but consisiting of
    > ALL the SAME symbols) will be pasted, for example, as follows:
    >
    > PLFE
    > WTU
    > BLKB
    > EPIC
    > AFBA
    > MTG
    > ...etc. (all the same as above, ultimately, as above but in a different
    > order) for another 6994 rows.
    >
    > I'd like to take these two columns, as rank them from highest to lowest
    > based upon each symbol's positions in BOTH columns. So, if a symbol ranks
    > 232 in column 1, and 153 in column 2, the combined rank is 385.
    >
    >
    >




  7. #7
    Biff
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    >Now, sort on column G.

    Before you sort, select BOTH columns F AND G, then sort on column G.

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > Hi!
    >
    > If you want as the last step to sort on the rank then you'll need a third
    > column of the symbols (in any order).
    >
    > Assume the symbols are in one order in column A, A1:A7000, and are in
    > another order in column B, B1:B7000.
    >
    > Copy one of the columns of symbols (either one, doesn't matter) to another
    > column, say, column F, F1:F7000.
    >
    > Enter this formula in G1:
    >
    > =MATCH(F1,A$1:A$7000,0)+MATCH(F1,B$1:B$7000,0)
    >
    > Copy down to G7000. For quick copying just double click the fill handle.
    >
    > Now, sort on column G.
    >
    > You're probably going to end up with lots of ties!
    >
    > For example:
    >
    > 249+1
    > 1+249
    > 125+125
    > 10+240
    > 150+100
    >
    > Biff
    >
    > "Manfred" <[email protected]> wrote in message
    > news:[email protected]...
    >> Thank you very much for taking the time to respond, but this solution
    >> does not seem to work. Shouldn't I only need 2 columns? Also, the
    >> formula yields "#VALUE?" when I paste the stock symbols into the
    >> spreadsheet.
    >>
    >> Perhaps I should have been clearer that each of the two Excel columns
    >> will consist of STOCK SYMBOLS (not number rankings).
    >>
    >> So, for example, the first column will be pasted into Excel from an
    >> external ranking program as:
    >>
    >> LECT
    >> MFBC
    >> CTIG
    >> FBEI
    >> MHJ
    >> WRLT
    >> ....etc.for 6994 more rows
    >>
    >> And then the second column, using a different formula (but consisiting of
    >> ALL the SAME symbols) will be pasted, for example, as follows:
    >>
    >> PLFE
    >> WTU
    >> BLKB
    >> EPIC
    >> AFBA
    >> MTG
    >> ...etc. (all the same as above, ultimately, as above but in a different
    >> order) for another 6994 rows.
    >>
    >> I'd like to take these two columns, as rank them from highest to lowest
    >> based upon each symbol's positions in BOTH columns. So, if a symbol
    >> ranks 232 in column 1, and 153 in column 2, the combined rank is 385.
    >>
    >>
    >>

    >
    >




  8. #8
    Manfred
    Guest

    Re: Scoring/Ranking 2 Columns of Stock Symbols

    That works! You've helped me out immensely. Great thanks to both of you
    for helping me with this.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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