0

I already have a query that returns a list of 26-Week Highs (from a sheet with my list of stocks (Column A), column of today's high (Column B), column of 26-Week High (Column C)). I'd like to have a query that returns a list of stocks that have hit new 26-Week highs twice in the last 4 days (does not have to have been today). Is there an eloquent way to do this with nesting queries?

I am unsure how to compare the list of stocks to see if two match in any of the four columns.

I am trying to nest queries with an if statement so: IF(Return cells if they repeat in 2 of the 4 columns from the following queries)

=Query(Sheet2!A:I,"select * where B>=C");
=Query(Sheet2!A:I,"select * where D>=E");
=Query(Sheet2!A:I,"select * where F>=G");
=Query(Sheet2!A:I,"select * where H>=I")
1
0

You want to know if any stocks from a list have reached 26-week highs in the last 4 days.

You should refer to How to Run a Query in GoogleSheets for New 26-Week Highs Using GOOGLEFINANCE Function and How to Create a Google Sheets Script for Reporting 26-Week New Highs using GOOGLEFINANCE Function?, as well as any source regarding "filtering dates in a QUERY".

Try this formula (refer cell K3 in the image below):
=if(count(QUERY({QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select Col1, Col2 order by Col1 desc limit 4 label Col1 '', Col2 ''")},"select Col1, Col2 where Col1>= date '"&TEXT(today()-4+1,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(today(),"yyyy-mm-dd")&"'",1))>=2,QUERY({QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select Col1, Col2 order by Col1 desc limit 4 label Col1 '', Col2 ''")},"select Col1, Col2 where Col1>= date '"&TEXT(today()-4+1,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(today(),"yyyy-mm-dd")&"'",1),)

Let's take the stock "GOOG".

  • =GOOGLEFINANCE("GOOG","high"): this returns the current day's high price.
  • =GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()): this returns high price on every day within the last 26 weeks.
  • =QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select Col1, Col2 order by Col1 desc limit 4 label Col1 '', Col2 ''"): this returns the four highest prices in the last 26 days

Today's date: =today(), and the date four days ago is =today()-4+1.

So, the dates and values of the four highest 26-week values is:

=QUERY({QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select Col1, Col2 order by Col1 desc limit 4 label Col1 '', Col2 ''")},"select Col1, Col2 where Col1>= date '"&TEXT(today()-4+1,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(today(),"yyyy-mm-dd")&"'",1)

The number of instances returns by the query:
=count(QUERY({QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select Col1, Col2 order by Col1 desc limit 4 label Col1 '', Col2 ''")},"select Col1, Col2 where Col1>= date '"&TEXT(today()-4+1,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(today(),"yyyy-mm-dd")&"'",1))

So if the number of instances is >=2, then there are stocks that have hit their high price in the last four days.


Count and List of high's in last 4 days

countandlist


Detailed breakdown

detail

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.