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

Here's a script that you can tweak further for your use case. It adds a couple of custom new commands to the menu when the spreadsheet opens:

The commands copy the content of the currently active sheet to the new one, named in the ISO date format, e.g., 2015-05-15.

function onOpen() {
  var menu = [{name: "New Sheet as First", functionName: "newSheet"}, {name: "New Sheet as Last", functionName: "newSheetLast"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function newSheet() {
  var sheetName = formatDate();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getActiveSheet();
  ss.insertSheet(sheetName, 0, {template: templateSheet});
}

function newSheetLast() {
  var sheetName = formatDate();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getActiveSheet();
  ss.insertSheet(sheetName, ss.getSheets().length, {template: templateSheet});
}

function formatDate() {
  var month, day, d = new Date();
  month = ('0'+(d.getUTCMonth()+1)).slice(-2);
  day = ('0'+d.getUTCDate()).slice(-2);
  return d.getUTCFullYear()+'-'+month+'-'+day;
}

The GoogleFinance command retrieves prices for a particular stock, specified as the first argument of the command. In order to receive data for multiple stocks, you need multiple GoogleFinance commands, one for each stock.

Possible implementation:

• Column A lists the stocks: GOOG, APPL, MSFT, etc.
• Column B lists today's high, like B2 =GOOGLEFINANCE(A2,"high")
• Column C lists 26-week high (as explained in my answer; the last option from there should be used to get a single-cell result).

Then, on another sheet, you put

=Query(Sheet1!A:C,"select * where B=C") 

or (to get only the stock symbols without prices)

=Query(Sheet1!A:C,"select A where B=C") 

Notes

• You'll have to obtain and enter (copy/paste) the list of stocks yourself.
• This is a data-intensive process, especially when many stocks are involved. You will likely find the spreadsheet sluggish, or will encounter an error when a Google server decides you are using too much of their resources.
• A spreadsheet is not a trader's dashboard and should not be used as one.