Forum Discussion

tpallred's avatar
tpallred
Copper Contributor
Jul 29, 2019

Find winning stocks (columns) based on unique combinations of indicators (rows)

See attached excel screenshot.  I have a list of tests (stock indicators) in the rows of Column A.  Each subsequent column is a stock with the rows of the column indicating whether a test is true or ...
BI & Data Analysis
excel
office 365
tpallred's avatar
tpallred
Copper Contributor
Jul 30, 2019
I guess this must have been an impossible problem.
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 30, 2019
    That is pretty tricky, I'm thinking maybe you could use Power Query to create a table of Winners and Table of loser with a column that combines (concatenates) the criteria into a "Key" column in both tables. Then merge the tables and do an anti join so that only "Keys" unique to the Winners table remain.

    That sort of problem sounds like it could be quite time consuming to resolve and is therefore difficult to get a full solution in this type of forum.
    • tpallred's avatar
      tpallred
      Copper Contributor
      Jul 31, 2019

      Wyn Hopkins  Thanks for the response.  Don't think what you're describing will work, if I understand you correctly.  Any given indicator may be true for a winner or loser, as you can see in the screenshot.  If there is any validity to what I'm trying to do - and maybe there isn't - then only a combination of indicators is going to be unique for winners.  Occasionally a single indicator will only show up in winners but that isn't reliable.  I'm using countif(range,1) to determine the percentage of winners for a given indicator but even a combination high percentage indicators doesn't yield a reliable signal across multiple days.  I was hoping for some kind of correlation tool in Excel that would provide the answer.

       

      As I mentioned before, I'm using bash shell scripts under Windows 10 to manipulate the data currently.  Unix/Linux has some wonderful text manipulation tools that let you sort, cut, extract, and combine text files in just a line or two of code.  Last night I came up with an idea in bash that at least proved my concept.  Across multiple days of data I've found repeatable unique indicator combos that only produce winners.  I'll be testing those but as I mentioned before, I can only go 4 indicators deep before processing time prohibits useful results for daily use, at least until/if I rewrite the program in a real programming language.  A combo of 5-7 winning indicators would be better.  Probably fewer results but more confidence.  It's an interesting learning experience and I hoped I could learn something new about Excel as well as decreasing the processing time.  Oh, well.  Thanks again.

Resources