• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number of days it took a stock to rise/sink above a certain level.

SKYTALKER

New Member
I tried to use multiple IF formulas, but it got too complicated for me and I also couldn't get them to work. I am using Excel 2019, 64-bit. I'm not positive that my explanation fully explains what I am trying to do, so it's probably better to look in file 1.


1. In 'FILE1', find if stock in A1 is found in column A in 'FILE2', if so:
2. In 'FILE1', find if the date in G1 is found in column G of 'FILE2', if so:
3. Add B1+1, then insert how many days for this condition to happen in column J.

4. H1<I1 - this determines whether to buy/sell (can H1>I1 be used in same formula?)

FILE 1 = This is a list of the top "movers" in the stock market for the day.
FILE 2 = This is a list of all the stocks sold on a particular day. This has several sheets, titled 11-09, 11-10, 11-11, etc., which are the list for each day.

Thanks for any help,
Cliff

P.S. I received this advice from another forum, but unfortunately, my lack of knowledge of Excel prevented me from using the info:

You've numbered your problems so I can provide a partial answer.
#1 : simple INDEX EQUIV combinaison. You could also merge your two sources in PowerQuery to get even faster computation results (especially if the list goes on).
#2 : same answer.
#3 : IF(H2=MIN(H2:I2);"SELL";"BUY") should do the trick. Change H2:I2 to the proper value once your INDEX EQUIV works.
#4 : counting with multiple occurrences is very effective with COUNTIFS formula. When dealing with dates, remember to enter the condition like so : ">"&B5

The individual was from Paris, so I just assumed INDEX EQUIV was INDEX/MATCH.
 

Attachments

  • file1.xlsx
    11 KB · Views: 4
  • file2.xlsx
    10.2 KB · Views: 3
Sorry, I thought I was only supposed to write a reference to another forum, not add the actual link to the thread. Here it is:
 
I was able to find some earlier work that I had thought I had lost when my laptop crashed when I dropped it, but I was able to find it and rebuild it. Anyway, I hope someone can understand what I am trying to accomplish and can help.
 

Attachments

  • EXAMPLE.xlsb
    11.5 KB · Views: 1
I have got part of my problem figured out using helper columns, but I am still having problems. I would still like to figure out a way to combine all formulas into 1, but I guess I will just have to take what I can get. The problem I am having is highlighted in yellow, such as K7, L7, etc.

The first problem is with the formula in K7: {=IF($H7>$I7,INDEX($C8:$C$50,MATCH(TRUE,$C8:$C$50<$J7,0)),IF($H7<$I7,INDEX($D8:$D$50,MATCH(TRUE,$D8:$D$50>$J7,0))))}
This formula is based on which cell is higher, H or I. If H is higher, it takes the value in J7 and looks down column C (LOW) and finds the next lower number. If I is higher, it takes the value in J7 and looks down column D (HIGH) and finds the next higher number.
I was trying to come up with a formula based on the following factors:
1 - What is in column A (SYM)
2 - Whether H or I is higher. This determines what goes in J7, which goes in K7, which determines the number we look up in column C or D, which determines what date goes in L7, which we will subtract from G7.
The problem is that at K7, we are looking for a value in column C, which is lower than J7 (115.40), but there aren't any lower, until we come to C18, which has a value of "AA" in column A.

I am trying to figure out a way to do the index/match that ONLY uses the value in column A as the limit which the formulas searches in column C or D, instead of 50 in $C8:$C$50. I want it to stop at row 18 because "AA" is the value, instead of "A". Although I can manually change $C8:$C$50 to $C8:$C$17, I don't want to do that over 3000 times, which is constantly changing. If I can figure this out, I believe it will fix the rest of the errors.

Of course, if anyone can figure how to combine these formulas, I would be VERY happy, as I have been trying to figure this out for months! I have to use 3 helper columns to find each value that goes under the # of days, i.e., 1,2,3,etc. That means an extra 30 columns!

If ANYONE is willing to try to help but are confused about what I am trying to accomplish, please ask me and I will strive to explain better.
 

Attachments

  • 0MAS-OK.xlsb
    30.4 KB · Views: 1
Should anyone care, A very smart individual, BEBO021999, from Vietnam, was able to apparently solve this problem with the following formula:
=IFERROR(INDEX($G:$G,AGGREGATE(15,6,ROW($A3:$A$13)/($A3:$A$13=$A2)/($D3:$D13>=$B2+IF($I2>=$H2,P$1,-P$1)),1))-$G2,"")
I am checking it with my files to see if works correctly, but so far it has done everything I was asking for. This is from another forum at: https://www.excelforum.com/excel-fo...e-sink-above-a-certain-level.html#post5453827
Thanks to ANYONE who tried to work on a formula that did what I requested.
 

Attachments

  • EXAMPLE.xlsb
    14.5 KB · Views: 4
I want to add something to the following formula:
=IFERROR(IF($H2<$I2,INDEX('0MAS.xlsb'!DATE,MATCH($AA2,'0MAS.xlsb'!HIGH,0)),IF($H2>$I2,INDEX('0MAS.xlsb'!DATE,MATCH($AA2,'0MAS.xlsb'!LOW,0)))),"")

The problem I am experiencing is that when it finds the value that MATCH is looking for, it finds the FIRST value, but there are other values that are the SAME number. I want to add a stipulation to H2<I2, which will differentiate it from the others, such as: ('0MAS=.xlsb'!SYM=A2)*($AA2,'0MAS=.xlsb'!HIGH).

OMAS= is the file with ALL of the stocks from every day, which is over 365K rows.
LOW = Column C.
HIGH = Column D.
SYM = Column A.
If ANYONE has ANY ideas, I sure would like to hear from you. Thanks in advance,
Cliff
 
Back
Top