Saturday, October 25, 2014

yogi_Compute Stats For Weekly HIGHs And Weekly LOWs For Specified TICKERs

                 Google Spreadsheet   Post  #1804
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct-25-2014
post by  Solarmatt:
Working with aggregating functions and GOOGLEFINANCE function
Firstly, where can I find exhaustive instructions on how to work with ARRAYFUNCTION and other array functions? The canned Google instructions gives only one example about creating hyperlinks. I want to do math!

Second, I have a specific "how-to" question but want to learn in general. I have a spreadsheet (
where I want to add a column that returns a "TRUE" if the trading range (or high - low) of the stick listed in column A is greater than any of the weekly trading ranges since the date in column P.

All the data can come from: GOOGLEFINANCE(A3, "high", P3, TODAY(), "weekly") and GOOGLEFINANCE(A3, "low", P3, TODAY(), "weekly"), or GOOGLEFINANCE(A3, "all", P3, TODAY(), "weekly")

But I do not know how to perform operations (in this case take the absolute value of the difference between low & high) across two columns in that [unseen] array and then compare the recent calculated value with the history of calculated values.

Thank you.
a) I want to add a column that returns a "TRUE" if this week's trading range (absolute value of this week's high - this week's low) for the stock whose ticker is found in column A is greater than the trading range of any prior week since the date found in column P.
b) All of the cells in a new column. Each row's value would be based on the stock from column A and date from column P.

c) Please see either my attached screenshot and/or the second page of my linked spreadsheet. This is the manual version of doing what I want done for one stock ("AAPL") from 8/1/2014 to today. If this week's range would have been larger than any of the other's in the array then "TRUE" is returned.

Attachments (1)
Screen Shot 2014-10-23 at 5.03.15 PM.png
61 KB   View   Download