CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheets
Steve
Steve

Reputation: 21

Need script to loop through stock ticker list, copy resulting output, and paste output to separate sheet within workbook

I would be extremely grateful if someone could help me create the following Google sheets script:

I have three sheets named tickers , data , results within a single workbook.

Currently, I manually enter a stock symbol into cell b9 in the data sheet. The sheet then retrieves data (takes around 30 seconds to retrieve data from google finance, run formulas and return data into range F2:AU2 within the same data sheet.

Request: Create a script to loop through a list of 40 stock symbols from the ticker sheet (range B2:B40), paste 1 symbol at a time into cell b9 in the data sheet, wait for google finance API to run, and then copy the results generated in range F2:AU2, and pasting those results into the results tab, 1 row at a time, until 40 rows of data is produced to correspond with the 40 stock ticker list mentioned earlier.

Can anyone help me with this request? I would be incredibly grateful.

Upvotes: 1

Views: 287

Answers (1)

James VB
James VB

Reputation: 93

This code adds a menu item called "Update Stocks" next to the "Help" menu, which provides triggers to run the script and clear all rows of the results tab except for the header row (row 1). I also added "Ticker" & "Run Date" columns to the "results tab." Let me know if this is what you are seeking.

/** @OnlyCurrentDoc */

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Stocks')
    .addItem('UPDATE STOCKS', 'getNewPrices')
    .addItem('Clear Data','clearData')
    .addToUi();
};

function getNewPrices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tickerSheet = ss.getSheetByName('tickers');
  const dataSheet = ss.getSheetByName('data');
  const resultsSheet = ss.getSheetByName('results');
  let data;


  let myListOfTickers = tickerSheet.getRange('B2:B40').getValues().filter(String).sort();

  myListOfTickers.forEach(ticker => {
    dataSheet.getRange('B9').setValue(ticker[0]);
    data = dataSheet.getRange('F2:AU2').getValues()[0];
    pasteData(resultsSheet, data, ticker);
  });
};

function pasteData(resultsSheet,data, ticker){
  let nextRow = resultsSheet.getLastRow() + 1;

  ////////////////////////////////////////////////
  //
  //  set timezone to your timezone
  //  https://gist.github.com/diogocapela/12c6617fc87607d11fd62d2a4f42b02a
  //
  ////////////////////////////////////////////////
  let timeZone = "America/New_York";
  //let timeZone = "America/Detroit";
  //let timeZone = "America/Chicago";
  //let timeZone = "America/Denver";
  //let timeZone = "America/Los_Angeles";

  resultsSheet.getRange(nextRow,1).setValue(ticker);
  resultsSheet.getRange(nextRow,2).setValue(Utilities.formatDate(new Date(),timeZone,"MM/dd/yyy"));

  data.forEach((datum,index) => {
    resultsSheet.getRange(nextRow,index + 3,1,1).setValue(datum);
  });
};

function clearData(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const resultsSheet = ss.getSheetByName('results');
  const range = resultsSheet.getRange(2,1,resultsSheet.getLastRow(),resultsSheet.getLastColumn());
  range.clearContent();
}

Upvotes: 1

Related Questions