How to Run a Query in Google Sheets for New 26-Week Highs 2 of Last 4 Days Using GOOGLEFINANCE Function

Question

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")

Answer 1

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

---

Detailed breakdown