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

google sheetsgoogle-finance

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

Best Answer

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

Formula

=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)

=ARRAYFORMULA(                           // ARRAYFORMULA start
   VLOOKUP(                              // VLOOKUP start
     B2:B,                               // search_criterion                 
     DATA!A2:G,                          // array
                                         // index start
     IF(                                 // IF start
       C2:C="1st",                       // test 
         {4,5},                          // then_value
         {6,7}                           // otherwise_value
     ) *                                 // IF end
     SIGN(                               // SIGN start
       ROW(                              // ROW start
         A2:A                            // reference
       )                                 // ROW end
     ),                                  // SIGN end
                                         // index end
     0                                   // sort_order
   )                                     // VLOOKUP end
 )                                       // ARRAYFORMULA end

// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))

Explained

The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.

Screenshot

enter image description here

Example

I've copied your file and added my solution to it: teacher references

Google Sheets – How to Create a Script for Reporting 26-Week Highs

I'll describe the process in stages. Here is a command that returns a table with high price on every day within the last 26 weeks:

=GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY())

Output:

+-------------------+----------+
|       Date        |   High   |
+-------------------+----------+
| 2/2/2015 16:00:00 | 533      |
| 2/3/2015 16:00:00 | 533.4    |
| ................. | .......  |

Since you only want the maximum of these numbers, let's add a QUERY selecting the maximum of the second column:

=QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select max(Col2)")

Output:

+----------+
| max High |
+----------+
| 678.64   |
+----------+

This may be already good enough for your purposes. If you want to get rid of the column header "max High", end the query string with label max(Col2) '':

=QUERY(GOOGLEFINANCE("GOOG","high",TODAY()-26*7,TODAY()),"select max(Col2) label max(Col2) ''")

Output:

+----------+
| 678.64   |
+----------+

Best Answer

Related Solutions

Google Sheets – Add a Conditional Statement to a QUERY Function

Formula

Explained

Screenshot

Example

Google Sheets – How to Create a Script for Reporting 26-Week Highs

Related Topic