1

On the Submit Tab. In cell B2, I enter a ticker. The cell has a reference at C2 that searches for the ticker, name, sector, industry, sub-industry, price and market cap in the Universe tab using VLOOKUP. The Universe tab is a database of stocks. The cells C2:I2 get populated with the relevant information. The cells from J2:N2 are manually entered. Using cell references again the cells B4:N4 are populated. Once I have all the information on cells C4:N4, I have a script that I found (I have no prior experience with app scripts) which I run by clicking the “Submit” button on cell P3. This basically moves the data from cell C4:N4 to a new sheet ‘watchlist’. I repeat the process to add multiple tickers and stock information to the watchlist tab. This works perfectly however I’m facing a problem with the price and market cap in column F and G on the watchlist tab. The script does not seem to pick up the GOOGLEFINANCE formula that is used in the Universe tab from which the data is being looked up with the VLOOKUP function in the submit tab (H2:I2). Is there a way I could somehow implement the GOOGLEFINANCE function to show the real time price and market cap from the script or any other way so it could show up in the watchlist tab?

function Submit() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sinput = ss.getSheetByName("Submit");
  var soutput = ss.getSheetByName("Watchlist");


  var input = [[sinput.getRange("C4").getValues(),
            sinput.getRange("D4").getValues(),
            sinput.getRange("E4").getValues(),
            sinput.getRange("F4").getValues(),
            sinput.getRange("G4").getValues(),
            sinput.getRange("H4").getValues(),
            sinput.getRange("I4").getValues(),
            sinput.getRange("J4").getValues(),
            sinput.getRange("K4").getValues(),
            sinput.getRange("L4").getValues(),
            sinput.getRange("M4").getValues(),
            sinput.getRange("N4").getValues()]];              
  sinput.getRange("C4").clearContent(),
  sinput.getRange("D4").clearContent(),
  sinput.getRange("E4").clearContent(),
  sinput.getRange("F4").clearContent(),
  sinput.getRange("G4").clearContent(),
  sinput.getRange("H4").clearContent(),
  sinput.getRange("I4").clearContent(),
  sinput.getRange("J4").clearContent(),
  sinput.getRange("K4").clearContent(),
  sinput.getRange("L4").clearContent(),
  sinput.getRange("M4").clearContent(),
  sinput.getRange("N4").clearContent(),

  soutput.getRange(soutput.getLastRow()+1, 
  1,1,12).setValues(input);  
  soutput.insertRowAfter(soutput.getLastRow());
  Logger.log(input);
}

Here are the screenshots as well as the file for reference:

Watchlist

Submit

Universe

Amar Patel
  • 11
  • 4
  • Related:https://stackoverflow.com/questions/50656540/reading-the-values-of-cells-that-summarize-google-finance-data-results-via-apps/50661857#50661857 – TheMaster Jul 06 '22 at 08:29
  • 1
    Do not use images of code. Provide [mcve]. – TheMaster Jul 06 '22 at 08:33
  • Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Jul 06 '22 at 08:33
  • I think your issue is it takes too long for Spreadsheet to complete all the =GOOGLEFINANCE() formulas. I tried opening your supplied spreadsheet and went to the `Universe` page and was waiting and waiting and finally killed it. You will probable have to look for a market watch API to request all your symbols at once and then parse the results. – TheWizEd Jul 06 '22 at 13:54

0 Answers0