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

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