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 |
+----------+
Whenever it refreshes, the data is copied as opposed to new data being
added. It seems like it is adding the formula itself to the
spreadsheet as opposed to putting in the value itself.
If I understand correctly, you want the columns to keep a "snapshot" of the prices at the time the column was added, rather than have the values continue updating.
In order to do this, we can replace the formulas with the calculated values at the time to "freeze" the values:
// select the range you want to "freeze":
var range = SpreadsheetApp.getActiveSheet().getRange('B:B');
// replace the cell formulas with the calculated values:
range.setValues(range.getValues());
I manually tested this with the GOOGLEFINANCE
function and it did replace the function with the current price as a numerical value. You could add this to the end of your function to "freeze" the prices after getting them.
Another solution might be to use the start_date
parameter on the GOOGLEFINANCE
function. You could get the current date as a value and then pass that into the formula as the start_date
so that would always fetch the price for that date.
Best Answer
I think this will help you
This will fetch the previous day market high.