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

clifford eaton · 01/09/21

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.

file1.xlsx

file2.xlsx

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

clifford eaton · 01/09/21

Just to be clear, the first post was written in Nov. I updated my post today with this:

0MAS-OK.xlsx

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.

Kelly Moore · 01/11/21

Hello @clifford eaton

I don't have a solution as I don't quite understand the end result you need- which of the spreadsheet columns are true End result columns vs helping the helpers? I can make some of the helper columns easier for smartsheet but this doesn't really solve your problem. Instead of using Index/Match which really only allows for one criteria, have you tried Index/Collect? Collect allows for many criteria such as looking for a value less than J7 while matching @cell = SYM@row. This would eliminate the AA's showing up when you only want to evaluate the A's.

In addition, by adding the system column Auto-number you can look for row IDs greater than your current [Row ID]@row, which could be a way to automatically generate your dynamic range.

I'm happy to continue to try to assist. This post may be of interest to you.

cheers,

Kelly

clifford eaton · 01/12/21

Thanks Kelly. I have never heard of Index/Collect, but I will research it. What I am trying to accomplish in a nutshell is: find how many days it took for the price in column B to get above/below the next highest/lowest value in column D/C depending upon whether H is higher/lower than I. Once the value is found in Column C or D, find the corresponding date in column G and subtract it from the date of the stock you're looking up.

Taking row 2:

A2 = "A", so take 114.57, from row B, and add 1, to it since I am looking for how many days it took for the price to get at or above 115.57, since H2 is lower than I2. At this stage, I am looking for a value that is equal to "A" AND a value at or above 115.57 AND a date past 12/2.

Since H2<I2, I look in column D and find the first value that satisfies all of these criteria, which is 116.44. I take the date from that row, which is 12/7 and subtract G2 (12/2) from it, which is 5. That is the number that goes in T2.

Now we do the same procedure for 114.57 + 2, which means we're looking for a number equal to or above 116.57. The value that is equal to "A" AND a value at or above 116.57 AND a date past 12/2 is 117.34 in C6. We take the date from G6, which is 12/8 and subtract G2 from it, which equals 6, this goes in U2.

Is for the helper columns, this was from an earlier attempt to try to solve this problem. I would prefer that all of the formulas were to be combined into 1. Either way, I still haven't figured out how to solve it, but I am too stubborn to stop.

Kelly Moore · 01/12/21

Try this. I decided the Index/Collect wasn't necessary. I think min(collect) works for you. I imported your spreadsheet into smartsheet so I think these column names are the same as yours.

=IF(Buy@row<Sell@row, MIN(COLLECT(High:High, Symbol:Symbol, Symbol@row, Date:Date, >Date@row, High:High, >=(Last@row + 1))), MIN(COLLECT(Low: Low, Symbol:Symbol, Symbol@row, Date:Date, >Date@row, Low:Low, >=(Last@row - 1))))

The collect function is finding the Minimum value in High (if Buy<Sell) when symbol=@row, Date>Date@row, High>(Last@row+1) <this eliminates the need for atleast some of the helper columns>. The false of the IF statement does the other Collect.

For your excel row 2 (since excel column titles in row1) or smartsheet row 1, my formula finds the $116.44. We should be able to use Index/match with this value to get the correct date pulled into a date column to set up finding the number of days.

I don't understand adding the 2, etc in the subsequent iterations. If there is a way to determine that value programmatically, let me know and we can work on that.

Kelly

clifford eaton · 01/12/21

Thank you SO MUCH Kelly. It might not work, but it gives me a straw to grasp at. I will let you know how it works. If anything, I have learned about a new term that I can use in formulas, i.e., COLLECT. I have never heard of it!

clifford eaton · 01/12/21

Kelly, Is it possible to send the worksheet you used. I am still not able to get this to work. I keep getting the error message: There's a problem with this formula. You can either post it here or send it to my email address: ATC1114@COX.NET.

Again, thank you for the help.

Cliff

clifford eaton · 01/12/21

Oh, I see what the problem is, You're using Smartsheet, instead of Excel. Will this formula work in an Excel worksheet?

Kelly Moore · 01/12/21

Here's the combined formula (your excel column T), which I think will eliminate all the helper columns. This gives you the number of days between the ups or downs. There are some hitches now where the formula still is not returning exactly the same data but I think that is a matter of tweaking. I will try the Index/Collect again for kicks.

=IF(Buy@row < Sell@row, MIN(COLLECT(Date:Date, Symbol:Symbol, Symbol@row, Date:Date, >Date@row, High:High, >=(Last@row + 1))) - Date@row, Date@row - MIN(COLLECT(Date:Date, Symbol:Symbol, Symbol@row, Date:Date, >Date@row, Low:Low, >=(Last@row - 1))))

Kelly

clifford eaton · 01/13/21

Kelly, Did you see my earlier post about this formula working in Excel? I have tried every combination that I can think of to get this formula to work and I keep getting the same error message. I tried researching "COLLECT" being used in Excel and have come up empty. I don't know if your formula will work in Excel, but I am just not knowledgeable enough to say for sure. It might only work in Smartsheet.

Kelly Moore · 01/13/21

Sorry, I don't know if it works in excel. I had assumed you were moving your excel spreadsheet to smartsheet. Have you tried excel forums?

clifford eaton · 01/13/21

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-formulas-and-functions/1331382-number-of-days-it-took-a-stock-to-rise-sink-above-a-certain-level.html#post5453827

Thanks to ANYONE who tried to work on a formula that did what I requested.

Kelly Moore · 01/13/21

I'm glad you found your answer. Come back anytime you have a smartsheet question

cheers

Paul Newcome · 01/13/21

As a note... The original question is for Excel. While Excel and Smartsheet do have things in common, there are quite a number of things they also do not have in common.

One of those things is the COLLECT function. This function does not exist at all in Excel, so Smartsheet solutions that utilize this function will be very different in Excel.

clifford eaton · 01/16/21

https://community.smartsheet.com/discussion/comment/274922#Comment_274922

That's OK Kelly. Thanks a lot for at least trying. I appreciate the effort.

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

Comments

Help Article Resources

Categories