4

I'm looking to use GoogleSheets to display ticker symbols and prices in real-time of stocks that have created new 26-week highs, i.e. today's highest trading price is greater than any trading price during the last 26-week period.

I think GoogleSheets's native GOOGLEFINANCE function allows this to be done for a 52-week trailing period (e.g. a formula comparing attributes "high52" against current day's "high"). The GOOGLEFINANCE function doesn't have an attribute for a 26-week period.

Does anyone have a script or template of one I can apply to retrieve a stock's highest traded price over a trailing 26-week period?

3

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   |
+----------+
  • Works brilliantly. Great explanations. Thanks. – P.D. Aug 1 '15 at 22:07

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.