Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-776887%22%20slang%3D%22en-US%22%3EFind%20winning%20stocks%20(columns)%20based%20on%20unique%20combinations%20of%20indicators%20(rows)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776887%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20attached%20excel%20screenshot.%26nbsp%3B%20I%20have%20a%20list%20of%20tests%20(stock%20indicators)%20in%20the%20rows%20of%20Column%20A.%26nbsp%3B%20Each%20subsequent%20column%20is%20a%20stock%20with%20the%20rows%20of%20the%20column%20indicating%20whether%20a%20test%20is%20true%20or%20false%20for%20the%20stock%20on%20a%20given%20date%20(date%20not%20shown)%2C%20one%20date%20per%20worksheet.%26nbsp%3B%20If%20a%20test%20is%20true%20for%20a%20stock%20then%20the%20value%20%221%22%20is%20placed%20in%20the%20intersecting%20cell.%26nbsp%3B%20If%20the%20test%20is%20false%20then%20the%20intersecting%20cell%20is%20blank%20(zero).%26nbsp%3B%20%26nbsp%3BBy%20looking%20at%20the%20stocks%20on%20a%20later%20date%20I%20can%20determine%20if%20the%20stock%20has%20met%20my%20criteria%20(not%20shown)%20for%20being%20a%20%22winner%22.%26nbsp%3B%20I%20have%20sorted%20the%20stock%20columns%20to%20group%20the%20winners%20before%20the%20losers%20and%20have%20colored%20the%20stock%20symbols%20green.%26nbsp%3B%20What%20I'd%20like%20to%20determine%20is%20if%20there%20are%20combinations%20of%20indicators%20that%20are%20unique%20for%20winners%20and%20exclude%20losers.%26nbsp%3B%20So%20maybe%20the%20combination%20of%20tests%203%2C4%2C8%2C11%2C17%20and%2024%20are%20true%20in%20one%20or%20more%20winning%20stocks%20but%20are%20not%20true%20in%20a%20losing%20stock.%26nbsp%3B%20I'm%20not%20looking%20for%20combinations%20that%20are%20true%20for%20ALL%20winners%20because%20that%20doesn't%20exist.%26nbsp%3B%20I've%20already%20calculated%20the%20percentage%20of%20winners%20for%20each%20test%20(not%20shown%20and%20not%20really%20useful%20since%20each%20worksheet%20is%20for%20a%20given%20day%20and%20a%20single%20indicator%20isn't%20dependable%20over%20multiple%20days)%20but%20I%20can't%20figure%20out%20how%20to%20make%20Excel%20show%20me%20which%20true%20test%20combinations%20are%20unique%20to%20winners.%26nbsp%3B%20Any%20ideas%3F%26nbsp%3B%20The%20data%20comes%20from%20CSV%20files%20created%20by%20a%20stock%20trading%20program%2C%20just%20FYI.%26nbsp%3B%20BTW%2C%20I%20can%20determine%20unique%20winning%20combinations%20up%20to%205%20levels%2Ftests%20deep%20with%20a%20recursive%20bash%20shell%20script%20I%20wrote%20but%20it%20takes%2024%20hours%20to%20run%20on%20a%20high%20performance%20computer%20for%20that%20many%20levels%2C%20so%20not%20useful.%26nbsp%3B%20I'd%20have%20to%20rewrite%20it%20in%20a%20real%20programming%20language%20to%20get%20acceptable%20run%20times%20and%20go%20more%20levels.%26nbsp%3B%20But%20maybe%20some%20clever%20Excel%20guru%20can%20save%20me%20from%20doing%20that.%26nbsp%3B%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-776887%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779956%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20winning%20stocks%20(columns)%20based%20on%20unique%20combinations%20of%20indicators%20(rows)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779956%22%20slang%3D%22en-US%22%3EI%20guess%20this%20must%20have%20been%20an%20impossible%20problem.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780435%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20winning%20stocks%20(columns)%20based%20on%20unique%20combinations%20of%20indicators%20(rows)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780435%22%20slang%3D%22en-US%22%3EThat%20is%20pretty%20tricky%2C%20I'm%20thinking%20maybe%20you%20could%20use%20Power%20Query%20to%20create%20a%20table%20of%20Winners%20and%20Table%20of%20loser%20with%20a%20column%20that%20combines%20(concatenates)%20the%20criteria%20into%20a%20%22Key%22%20column%20in%20both%20tables.%20Then%20merge%20the%20tables%20and%20do%20an%20anti%20join%20so%20that%20only%20%22Keys%22%20unique%20to%20the%20Winners%20table%20remain.%3CBR%20%2F%3E%3CBR%20%2F%3EThat%20sort%20of%20problem%20sounds%20like%20it%20could%20be%20quite%20time%20consuming%20to%20resolve%20and%20is%20therefore%20difficult%20to%20get%20a%20full%20solution%20in%20this%20type%20of%20forum.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782701%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20winning%20stocks%20(columns)%20based%20on%20unique%20combinations%20of%20indicators%20(rows)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20the%20response.%26nbsp%3B%20Don't%20think%20what%20you're%20describing%20will%20work%2C%20if%20I%20understand%20you%20correctly.%26nbsp%3B%20Any%20given%20indicator%20may%20be%20true%20for%20a%20winner%20or%20loser%2C%20as%20you%20can%20see%20in%20the%20screenshot.%26nbsp%3B%20If%20there%20is%20any%20validity%20to%20what%20I'm%20trying%20to%20do%20-%20and%20maybe%20there%20isn't%20-%20then%20only%20a%20combination%20of%20indicators%20is%20going%20to%20be%20unique%20for%20winners.%26nbsp%3B%20Occasionally%20a%20single%20indicator%20will%20only%20show%20up%20in%20winners%20but%20that%20isn't%20reliable.%26nbsp%3B%20I'm%20using%20countif(range%2C1)%20to%20determine%20the%20percentage%20of%20winners%20for%20a%20given%20indicator%20but%20even%20a%20combination%20high%20percentage%20indicators%20doesn't%20yield%20a%20reliable%20signal%20across%20multiple%20days.%26nbsp%3B%20I%20was%20hoping%20for%20some%20kind%20of%20correlation%20tool%20in%20Excel%20that%20would%20provide%20the%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20mentioned%20before%2C%20I'm%20using%20bash%20shell%20scripts%20under%20Windows%2010%20to%20manipulate%20the%20data%20currently.%26nbsp%3B%20Unix%2FLinux%20has%20some%20wonderful%20text%20manipulation%20tools%20that%20let%20you%20sort%2C%20cut%2C%20extract%2C%20and%20combine%20text%20files%20in%20just%20a%20line%20or%20two%20of%20code.%26nbsp%3B%20Last%20night%20I%20came%20up%20with%20an%20idea%20in%20bash%20that%20at%20least%20proved%20my%20concept.%26nbsp%3B%20Across%20multiple%20days%20of%20data%20I've%20found%20repeatable%20unique%20indicator%20combos%20that%20only%20produce%20winners.%26nbsp%3B%20I'll%20be%20testing%20those%20but%20as%20I%20mentioned%20before%2C%20I%20can%20only%20go%204%20indicators%20deep%20before%20processing%20time%20prohibits%20useful%20results%20for%20daily%20use%2C%20at%20least%20until%2Fif%20I%20rewrite%20the%20program%20in%20a%20real%20programming%20language.%26nbsp%3B%20A%20combo%20of%205-7%20winning%20indicators%20would%20be%20better.%26nbsp%3B%20Probably%20fewer%20results%20but%20more%20confidence.%26nbsp%3B%20It's%20an%20interesting%20learning%20experience%20and%20I%20hoped%20I%20could%20learn%20something%20new%20about%20Excel%20as%20well%20as%20decreasing%20the%20processing%20time.%26nbsp%3B%20Oh%2C%20well.%26nbsp%3B%20Thanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
tpallred
New Contributor

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 false for the stock on a given date (date not shown), one date per worksheet.  If a test is true for a stock then the value "1" is placed in the intersecting cell.  If the test is false then the intersecting cell is blank (zero).   By looking at the stocks on a later date I can determine if the stock has met my criteria (not shown) for being a "winner".  I have sorted the stock columns to group the winners before the losers and have colored the stock symbols green.  What I'd like to determine is if there are combinations of indicators that are unique for winners and exclude losers.  So maybe the combination of tests 3,4,8,11,17 and 24 are true in one or more winning stocks but are not true in a losing stock.  I'm not looking for combinations that are true for ALL winners because that doesn't exist.  I've already calculated the percentage of winners for each test (not shown and not really useful since each worksheet is for a given day and a single indicator isn't dependable over multiple days) but I can't figure out how to make Excel show me which true test combinations are unique to winners.  Any ideas?  The data comes from CSV files created by a stock trading program, just FYI.  BTW, I can determine unique winning combinations up to 5 levels/tests deep with a recursive bash shell script I wrote but it takes 24 hours to run on a high performance computer for that many levels, so not useful.  I'd have to rewrite it in a real programming language to get acceptable run times and go more levels.  But maybe some clever Excel guru can save me from doing that.  Thanks in advance.

3 Replies
I guess this must have been an impossible problem.
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.

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

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies