Creating A Stock Trading Tool Using Google Sheets: Part I

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

I’ll provide two examples of how to pull ticker data by leveraging Wikipedia. We will pull the tickers in

The S&P 500
The Dow Jones Industrial Average

Both of these functions will leverage the IMPORTHTML function. In addition, we will also use the QUERY function too. All of this will be part of a Google Apps Script to streamline the process and allow for it to be repeated.

Tickers Of S&P 500

function fetchSPTickers() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock List");

  // Clear the existing data in the Stock List tab
  sheet.clearContents();

  var formula = '=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies", "table", 1)';
  var range = sheet.getRange("A1");
  range.setFormula(formula);

  // Apply formatting to the first row
  var firstRowRange = sheet.getRange("A1:H1");
  firstRowRange.setFontWeight("bold");
  firstRowRange.setHorizontalAlignment("center");

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

  // Autofit columns
  sheet.autoResizeColumns(1, sheet.getLastColumn());

  // Select the Stock List tab
  sheet.activate();
}

Sample Output

Tickers Of Dow Jones Industrial Average

function fetchDJITickers() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock List");

  // Clear the existing data in the Stock List tab
  sheet.clearContents();

  var formula = '=query(IMPORTHTML("https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average", "table", 2),"Select Col3, Col1, Col2, Col4, Col5, Col6, Col7")';
  var range = sheet.getRange("A1");
  range.setFormula(formula);

  // Wait for the formula to update
  SpreadsheetApp.flush();

  // Apply formatting to the first row
  var firstRowRange = sheet.getRange("A1:G1");
  firstRowRange.setFontWeight("bold");
  firstRowRange.setHorizontalAlignment("center");

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

  // Autofit columns
  sheet.autoResizeColumns(1, sheet.getLastColumn());

  // Select the Stock List tab
  sheet.activate();
}