Automate High Momentum Stock Screening with Google Apps Script

Automation with Google Apps Script

Introduction:

Are you tired of manually updating and analyzing stock data? Google Apps Script can help you automate the process, saving time and enhancing accuracy. In this tutorial, I’ll show you how to create a powerful stock screener that fetches high momentum stock data directly into Google Sheets.

Step 1: Setting Up Your Google Sheet

Create a new Google Sheet and name it something like “Stock Screener”. You’ll use this sheet to list stock tickers and display their metrics.

1. Add Column Headers:

• Column A: Ticker
• Column B: 1-Month Return
• Column C: 3-Month Return
• Column D: 6-Month Return

This is how final output looks like

Step 2: Adding the Google Apps Script

Open the Script Editor by navigating to Extensions > Apps Script in Google Sheets. Delete any existing code and replace it with the following script:

Creating a Custom Menu

The first step is to create a custom menu in Google Sheets. This will allow you to run the script functions directly from the sheet interface.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Functions')
    .addItem('Update Ticker Metrics', 'updateTickerMetrics')
    .addItem('Update from Selected Row', 'promptForStartingRow')
    .addToUi();
}

Step 3: Updating Ticker Metrics

The next function updates ticker metrics, starting from a specified row. It uses GOOGLEFINANCE to fetch historical data for each stock ticker.

function updateTickerMetrics() {
  const startRow = 2; // Start from row 2 (A2) by default
  updateTickerMetricsFromRow(startRow);
}

Prompt for Starting Row

This function prompts you to enter the starting row number for the update. It ensures that you can start updating metrics from any desired row.

function promptForStartingRow() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Start from which row?', 'Please enter the starting row number:', ui.ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() == ui.Button.OK) {
    const startRow = parseInt(response.getResponseText());
    if (!isNaN(startRow) && startRow > 1) {
      updateTickerMetricsFromRow(startRow);
    } else {
      ui.alert('Invalid row number. Please enter a valid row number greater than 1.');
    }
  } else {
    ui.alert('No row selected.');
  }
}

Step 4: Fetching and Analyzing Data

This function retrieves historical stock data using the GOOGLEFINANCE formula and calculates returns over 1, 3, and 6 months. It stores the data in a hidden sheet called HiddenDataFetch.

Prepare Data Sheet

This function prepares a hidden sheet for data fetching.

function prepareDataSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  let dataSheet = sheet.getSheetByName('HiddenDataFetch');
  if (!dataSheet) {
    dataSheet = sheet.insertSheet('HiddenDataFetch');
    dataSheet.hideSheet();
  } else {
    dataSheet.clear(); // Clear the sheet for new data
  }
  return dataSheet;
}

Get Historical Data

This function fetches the historical data for each ticker within the specified date range and formats it for further analysis.

function getHistoricalData(dataSheet, ticker, startDate, endDate) {
  const startDateString = Utilities.formatDate(startDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  const endDateString = Utilities.formatDate(endDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
dataSheet.getRange('A1').setFormula(`=GOOGLEFINANCE("${ticker}", "close", DATE(${startDate.getFullYear()}, ${startDate.getMonth() + 1}, ${startDate.getDate()}), DATE(${endDate.getFullYear()}, ${endDate.getMonth() + 1}, ${endDate.getDate()}), "DAILY")`);
  SpreadsheetApp.flush();
  Utilities.sleep(5000); // Wait for data to fetch
  const dataRange = dataSheet.getRange('A2:B').getValues();
  const data = dataRange.filter(row => row[0] && row[1]);
  if (data.length === 0) {
    throw new Error(`No data available for ${ticker} between ${startDateString} and ${endDateString}`);
  }
  return data.map(row => ({ date: row[0], close: row[1] }));
}

Step 5: Calculating Returns

The calculateReturn function computes the percentage change in stock prices over the given periods and returns the values formatted to two decimal places.

function calculateReturn(data) {
  if (data.length < 2) {
    throw new Error('Insufficient data to calculate returns.');
  }
const initialClose = data[0].close;
  const finalClose = data[data.length - 1].close;
  const returnValue = ((finalClose - initialClose) / initialClose) * 100; // Return in percentage
  return returnValue.toFixed(2); // Return with two decimal places
}

Updating Ticker Metrics

This function updates the ticker metrics by calling getMetrics for each ticker and populating the results in the sheet.

function updateTickerMetricsFromRow(startRow) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataSheet = prepareDataSheet();
  const tickers = sheet.getRange(`A${startRow}:A`).getValues().flat().filter(String);
tickers.forEach((ticker, index) => {
    if (ticker) {
      const metrics = getMetrics(ticker.trim(), dataSheet);
      const rowIndex = startRow + index;
      if (metrics.length === 3) {
        sheet.getRange(`B${rowIndex}:D${rowIndex}`).setValues([metrics]);
      } else {
        sheet.getRange(`B${rowIndex}:D${rowIndex}`).setValues([['Error', 'Error', 'Error']]);
      }
    }
  });
}

Get Metrics

This function aggregates data and calculates returns for different periods.

function getMetrics(ticker, dataSheet) {
  const today = new Date();
  const sixMonthsAgo = new Date(today.getFullYear(), today.getMonth() - 6, today.getDate());
  const threeMonthsAgo = new Date(today.getFullYear(), today.getMonth() - 3, today.getDate());
  const oneMonthAgo = new Date(today.getFullYear(), today.getMonth() - 1, today.getDate());
try {
    const data6Months = getHistoricalData(dataSheet, ticker, sixMonthsAgo, today);
    const data3Months = getHistoricalData(dataSheet, ticker, threeMonthsAgo, today);
    const data1Month = getHistoricalData(dataSheet, ticker, oneMonthAgo, today);
    const return6Months = calculateReturn(data6Months);
    const return3Months = calculateReturn(data3Months);
    const return1Month = calculateReturn(data1Month);
    return [
      return1Month,
      return3Months,
      return6Months
    ];
  } catch (e) {
    return [`Error: ${e.message}`, 'Error', 'Error'];
  }
}

Conclusion:

By following these steps, you now have a powerful tool for screening high momentum stocks using Google Apps Script and Google Sheets. This setup allows you to quickly and efficiently analyze stock performance over different periods, making your investment decisions more data-driven.

Automate with Apps Script

Resources:

• Google Sheet Link: Click Here
• YouTube Tutorial: Click Here
• GitHub Repository: Click Here

— — — — — — — — — — — — — — — — — — — — — —

QuantX-Builder for Mastering Momentum Investing. 4 week comprehensive learning programme.

bit.ly/QuantX-MomentumLAB

— — — — — — — — — — — — — — — — — — — — — —

Important Links
Youtube: https://www.youtube.com/@MomentumLab_IN
Twitter: https://x.com/MomentumLab_IN