Using double loop for processing Stock price data & Copying desired Results to summary sheet

Question

The Objective is to compare Price Data of 2 Scrips and append output results in sheet name 'List'.

Step I - Initial Tables ranges are arranged in 2 table ranges Scrip-1 & Scrip-2 in sheet name 'Rank' as shown in pic 1. Tables for Scrip-1 & Scirp-2 in 'Rank' tab

Step II - Scrip 1 is initialized and value is set up in sheet name 'Pair finder' 'B13' cell. Counter initial values are set up for table-1 in Sheet name 'Rank'. Base Calculation setup using counter i & j for carrying out iteration

'i' counter is for scrip-1 table in "Rank" Sheet. 'j' counter is for scrip-2 table in "Rank" Sheet.

Step III - This results in values to be setup in sheet name 'Cointegration'. Their calculations are completed, and output values are summarized in Range 'J2:Q2'. These output values are checked for conditions laid as below. Secondary Calculation setup for getting output results in range 'J2:Q2' in sheet name 'Conintergration'

Step IV -
Following Conditions are checked before final range values transfer as

1. if 'J2:K2' is "Loading" then wait to complete all calculations. Check again for nonblank cells in 'J2:K2'.
2. if 'P2:Q2' is not equal to text value 'no'.
3. if 'R2' is 'TRUE'.
4. if R2 is TRUE range is appended in 'List' tab.
   Output range 'J2:K2' getting copied/appended in 'List' tab

Step V - Then after calculation, range values - 'J2:Q2' is copied in sheet name 'Cointegration' to 'List' Sheet and append for next i & j (up to execution limit) counter respectively, for meeting all conditions.

Step VI - TO counter execution Time limit issue, i & j are getting recorded in 'B9 & D9' in 'Pair finder' sheet and is used for next run of the Macro.

While trying to improve the efficiency, I made some Error and it is causing script finishing before going to last steps.

Link to Google Sheet is as below. https://docs.google.com/spreadsheets/d/1r0_oHLTcT9tmTJPgKVgkGwGWhooBM8uluwmWcC92eOk/edit?usp=sharing

GAS code is as below.

function matchnsort() {
  var wb        = SpreadsheetApp.getActiveSpreadsheet();  
  var rank      = wb.getSheetByName('Rank');
  var pf_sheet  = wb.getSheetByName('Pair finder');
  var checking  = wb.getSheetByName('Cointergration');
  var list      = wb.getSheetByName('List');

  var countA  = rank.getRange('S9:S');                                      // Scirp-1 Table Range  
  var lenA    = countA.getDisplayValues().flat().filter(String).length;     // Scirp-1 Table length  
  var countB  = pf_sheet.getRange('D13:D');                                 // Scirp-2 Table Range   
  var lenB    = countB.getDisplayValues().flat().filter(String).length;     // Scirp-2 Table length  
  
  var rowA  = countA.getRowIndex();                                         // Scrip-1 Starting Row - 1st (scrip-1) to start Macro run  `
  var rowB  = countB.getRowIndex();                                         // Scrip-2 Starting Row - 1st (scrip-2) to start Macro run
  var col1    = countA.getColumn();                                         // Range identification for Scrip-1
  var col2    = countB.getColumn();                                         // Range identification for Scrip-2  
  var cellA   = pf_sheet.getRange('B9');                                    // Scrip-1 counter record - for next cycle - execution time limits  
  var cellB   = pf_sheet.getRange('D9');                                    // counter recording for Scrip-2
  var cellAVal  = cellA.getDisplayValue();                                  // Scrip-1 counter initialization 
  var cellBVal  = cellB.getDisplayValue();                                  // Scrip-2 counter initialization 
  // Logger.log(cellAVal);
  //if(cellAVal === "" || cellBVal === ""){                                   // setting up Scrip-1 & 2 counter for 1st run   
    
    //cellA.setValue(rowA);
    cellB.setValue(rowB);
    // SpreadsheetApp.flush();      
  //}

  var scrip1  = pf_sheet.getRange('B16');                                   // Scrip-1 setting Range Value 
  var scrip2  = checking.getRange('H4');                                    // Scrip-2 setting Range Value 
  var rows_lenA  = lenA + 9;
  var rows_lenB  = lenB + 14;
    
  if(cellAVal === ""){                                                      // final Check Scrip-1 values copied to desired cell for 1st run 
  
    SpreadsheetApp.flush();
    Logger.log(cellAVal);
    Logger.log(rowB);
  }
  else{                                                                       
    for (i=cellAVal; i<rows_lenA; i++){                                      // Loop-1 for Scirp-1 
      // set the number i on the sheet  into the cell 'Pair finder!B16'    
      var dataOutRang1  = rank.getRange(i,col1);
      var dataOut1      = dataOutRang1.getDisplayValue();
      scrip1.setValue(dataOut1);
      
      for (j=cellBVal; j<rows_lenB; j++){                                    // Loop-2 for Scirp-2 
        // set the number j on the sheet  into the cell 'Coint!H4'
        var dataOutRang2 = pf_sheet.getRange(j,col2);
        var dataOut2 = dataOutRang2.getValue();                              // *** Array Values for appending in 'List' Sheet
        Logger.log(dataOut2) 
        
        scrip2.setValue(dataOut2);
                  
        var dataOutRang3  = checking.getRange('J2:Q2');                   // Output Range to be appended & stored 
        var condition1 = checking.getRange('P2').getDisplayValue();       // Scrip -1
        var condition2 = checking.getRange('Q2').getDisplayValue();       // Scrip -2
        var condition3 = checking.getRange('J2').getDisplayValue();       // ADF Value
        var condition4 = checking.getRange('K2').getDisplayValue();       // KPSS Value
        var condition5 = checking.getRange('R2').getDisplayValue();       // Check for Data Repeation 

        if(scrip1 !== scrip2){ 
          if((condition3 === "Loading" || condition4 === "Loading")){
            SpreadsheetApp.flush();
          }
          else{
            if((condition1 !== "no" || condition2 !== "no") && ( condition3 !== "" || condition4 !== "") && condition5 === "TRUE"){
              Logger.log(condition1)
              Logger.log(condition2)
              Logger.log(condition3)
              Logger.log(condition4)  
              var dataOut3  = dataOutRang3.getValues();
              dataOut3  = dataOut3.flat();
              // Logger.log(dataOut3);       
              list.appendRow(dataOut3);
            }
          }
          
          cellB.setValue(j);                                              // Record last j value - to used in next cycle
          Logger.log(dataOut3);
        }
      
      cellA.setValue(i);                                                  // Record last i value - to used in next cycle 
      }
      
    }        
  }
}

Lines 3 to 7 - Initial references for Spreadsheet names are defined.

Lines 9 to 12 - Number of total scrips i.e. Table Length is evaluated and Range + Length is calculated.

Lines 14 to 21 - To initialize counter i & j, Row index and Col number are evaluated.

Lines 23 to 28 records counter values of i & j.

Lines 30 to 33 define range required for computation setup.

Lines 38 to 42 checks calculation and values are in relevant cells, before proceeding further.

Lines 44 to 49 start 1st Loop for counter i.

Lines 51 to 57 start 2st Loop for counter j.

Lines 59 to 75 Condition laid and Checked.

Lines 76 to 79 Appends output data if conditions are satisfied to 'List' Sheet.

Lines 83 to 87 records counter i & j value for next cycle.

Pl. do suggest error elimination and improvement for efficiency / runtime.

Execution Log is attached as suggested. Sample data Execution Log

Answer 1

One of the problems with the code is the expression scrip1 !== scrip2 as it always will return true because both variables have assigned Class Range objects. In Google Apps Script / JavaScript only primitives (1, 'hello world', etc.) could be compared directly.

Maybe it should be replaced by dataOut1 !== dataOut2 because few lines above the operands of this expression were used to set the values (Range.setValue()) of scrip1 and scrip2 respectively.

Related

• How to determine equality for two JavaScript objects?