Creating A Stock Trading Tool Using Google Sheets: Part III

This tool is comprised of a few parts. All of the code used in these posts can be purchased in a single spreadsheet if you’d like link to SMA Screener in store

Part I: Pulling stock ticker lists: S&P 500 & Dow Jones Industrial Average Tickers
Part II: Pulling OHLC data from Yahoo Finance
Part III: Generating Buy/Sell Signals Using Technical Indicators
Part IV: Scheduling & Part V: Notifications

We are going add the Simple Moving Average calculation and indicator. Here is a link for the Simple Moving Average https://www.investopedia.com/terms/s/sma.asp.

The below code has 2 functions: SMA and pullOHCL_Signals

function calculateSMA(values, window) {
  const smaValues = [];
  let sum = 0;

  for (let i = 0; i < values.length; i++) {
    sum += values[i];
    if (i >= window - 1) {
      if (i >= window) {
        sum -= values[i - window];
      }
      smaValues.push(sum / window);
    }
  }

  return smaValues;
}

function pullOHLC_Signals() {
  var startTime = new Date(); // Record the start time

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var stockListSheet = ss.getSheetByName("Stock List");
  var stockList = stockListSheet.getRange("A:A").getValues().flat().filter(Boolean);
  stockList = ["MMM"]; // You can change this to process other stocks as well

  var ohlcSheet = ss.getSheetByName("OHLC + Signals");
  if (!ohlcSheet) {
    ohlcSheet = ss.insertSheet("OHLC + Signals");
  } else {
    ohlcSheet.clear();
  }

  var startDate = new Date("2022-12-01"); // Set the start date to December 1, 2021

  var headerRow = ["Symbol", "Date", "Open", "High", "Low", "Close", "SMA", "SMA Signal"];
  ohlcSheet.getRange(1, 1, 1, headerRow.length).setValues([headerRow]);

  // Apply formatting to the first row
  var firstRowRange = ohlcSheet.getRange(1, 1, 1, headerRow.length);
  firstRowRange.setFontWeight("bold");
  firstRowRange.setHorizontalAlignment("center");

  // Freeze the first row
  ohlcSheet.setFrozenRows(1);

  var ohlcData = [];

  for (var i = 0; i < stockList.length; i++) {
    var symbol = stockList[i];
    try {
      var stockData = fetchStockData(symbol, startDate, new Date());
      Logger.log(stockData);

      var closePrices = stockData.map(function (data) {
        return parseFloat(data.close);
      });

      var sma = calculateSMA(closePrices, 20); // Calculate SMA for the entire dataset

      for (var j = 0; j < stockData.length; j++) {
        var date = stockData[j].date;
        var open = parseFloat(stockData[j].open);
        var high = parseFloat(stockData[j].high);
        var low = parseFloat(stockData[j].low);
        var close = parseFloat(stockData[j].close);

        if (j >= 19) {
          var smaValue = sma[j - 19]; // Get the corresponding SMA value for the current data point
          var smaSignal = close > smaValue ? "Buy" : "Sell"; // Determine SMA Signal
        } else {
          var smaValue = ""; // Set SMA to blank for the first 19 rows
          var smaSignal = "Neutral";
        }

        var row = [
          symbol,
          date,
          open,
          high,
          low,
          close,
          smaValue,
          smaSignal
        ];

        ohlcData.push(row);
      }
    } catch (e) {
      Logger.log("Error fetching data for " + symbol + ": " + e);
    }
  }

  ohlcSheet.getRange(2, 1, ohlcData.length, ohlcData[0].length).setValues(ohlcData);

  // Select the "OHLC + Signals" sheet
  ohlcSheet.activate();

  var endTime = new Date(); // Record the end time
  Logger.log("Execution time: " + (endTime - startTime) + " milliseconds");
}