Creating A Stock Trading Tool Using Google Sheets: Part II

function fetchStockData(symbol, startDate, endDate) {
  var startDateUnix = Math.floor(startDate.getTime() / 1000); // Convert to Unix timestamp in seconds
  var endDateUnix = Math.floor(endDate.getTime() / 1000); // Convert to Unix timestamp in seconds

  var url =
    "https://query1.finance.yahoo.com/v7/finance/chart/" +
    symbol +
    "?period1=" +
    startDateUnix +
    "&period2=" +
    endDateUnix +
    "&interval=1d&indicators=quote&includeTimestamps=true";

  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());

  if (data.chart.result.length === 0) {
    return []; // Return an empty array if no data is available
  }

  var timestamps = data.chart.result[0].timestamp;
  var ohlcData = data.chart.result[0].indicators.quote[0];

  var stockData = [];
  for (var i = 0; i < timestamps.length; i++) {
    var date = new Date(timestamps[i] * 1000);
    var open = ohlcData.open[i].toFixed(2);
    var high = ohlcData.high[i].toFixed(2);
    var low = ohlcData.low[i].toFixed(2);
    var close = ohlcData.close[i].toFixed(2);
    stockData.push({ date: date, open: open, high: high, low: low, close: close });
  }

  return stockData;
}

function pullOHLCData() {
  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);

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

  var startDate = new Date();
  startDate.setDate(startDate.getDate() - 200); // 10 days
  startDate.setHours(0, 0, 0, 0); // Set the time to the beginning of the day

  var headerRow = ["Symbol", "Date", "Open", "High", "Low", "Close"];
  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);

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

        ohlcData.push([symbol, date, open, high, low, close]);
      }
    } catch (error) {
      console.error("Error fetching OHLC data for symbol:", symbol, error);
    }
  }

  if (ohlcData.length > 0) {
    var range = ohlcSheet.getRange(2, 1, ohlcData.length, ohlcData[0].length);
    range.setValues(ohlcData);
  }

  var endTime = new Date(); // Record the end time
  var elapsedTime = (endTime - startTime) / 1000; // Calculate the elapsed time in seconds
  Logger.log("Time taken: " + elapsedTime + " seconds");

  // Select the "OHLC" sheet
  ss.setActiveSheet(ohlcSheet);
}