I have a Google Sheets I am curious if there is a method to do the following:

1. Define the formulas for each column in the code.
2. Run the script each morning and populate the next empty row with the values of the formulas.

The biggest item I need help with is adding the following formulas to the code so they will not be present in the worksheet. Additionally, some of the formulas rely on the value in a cell. For example the first formula is to create the date based on the date in the last row cell (A). The remaining formulas use the date from the new row columnm (a) and inputs from other columns in the new row. So when you see A2736 that is in last row and A2737 is the new row that was create when I last ran the script.

//DATE(A)=workday(A2736,1,'NYSE Holidays'!$A$2:$A$27)
//VIX9D(B)=INDEX(GOOGLEFINANCE("INDEXCBOE:VIX9D","close",$A2737),2,2)
//VIX1D(C)=INDEX(GOOGLEFINANCE("INDEXCBOE:VIX","close",$A2737),2,2)
//VIX3M(D)=index(GOOGLEFINANCE("INDEXCBOE:VIX3M","close",$A2737),2,2)
//VIX6M(E)=index(GOOGLEFINANCE("INDEXCBOE:VIX6M","close",$A2737),2,2)
//VIX1Y(F)=index(GOOGLEFINANCE("INDEXCBOE:VIX1Y","close",$A2737),2,2)
//VVIX(G)=index(GOOGLEFINANCE("INDEXCBOE:VVIX","close",$A2737),2,2)
//SPX(H)=index(GOOGLEFINANCE("INDEXSP:.INX","close",$A2737),2,2)
//VIX9D:VIX(I)=B2737/C2737
//VIX:VIX3M(J)=C2737/D2737
//VIX:VIX6M(K)=C2737/E2737
//VIX:VIX1Y(L)=C2737/F2737
//CORR, SPX , VVIX, 5(M)=correl(H2733:H2737,G2733:G2737)
//CORR, SPX , VIX, 10(N)=correl(H2728:H2737,C2728:C2737)
//Contango VIX2:VIX1(O) No formula yet
//Vix4:Vix7 Contango(P) No formula yet
//Log Ret(Q)=ln(H2737/H2736)
//V10(R)=stdev(Q2728:Q2737)
//V20(S)=stdev(Q2719:Q2737)
//HV10(T)=sqrt(252)*R2737
//HV20(U)=sqrt(252)*S2737
//VIX - HV10(V)=C2737-(T2737*100)
//VIX - hv20(W)=C2737-(U2737*100)
//VIX9D % Rank(X)=PERCENTRANK(B$2:B,B2737)
//VIX1D % Rank(Y)=PERCENTRANK(C$2:C,C2737)
//VIX3M % Rank(Z)=PERCENTRANK(D$2:D,D2737)
//VIX6M % Rank(AA)=PERCENTRANK(E$2:E,E2737)
//VIX1Y % Rank(AB)=PERCENTRANK(F$2:F,F2737)
//VVIX % Rank(AC)=PERCENTRANK(G$2:G,G2737)
//VIX9D Median(AD)=MEDIAN(B$2:B)
//VIX1D Median(AE)=MEDIAN(C$2:C)
//VIX3M Median(AF)=MEDIAN(D$2:D)
//VIX6M Median(AG)=MEDIAN(E$2:E)
//VIX1Y Median(AH)=MEDIAN(F$2:F)
//VVIX Median(AI)=MEDIAN(G$2:G)
//VDelta (VIX-VIX9D)(AJ)=C2737-B2737

Current code is below.

// Add Run button to menu 
function onOpen() { 
 var ui = SpreadsheetApp.getUi();

ui.createMenu("Auto Trigger") 
 .addItem("Run","runAuto") 
 .addToUi(); 
}

// Define function to run with menu button 
function runAuto() { 
 recordValue() 
}

function createTimeDrivenTrigger() {
    // Trigger every Weekday at 09:00.
  ScriptApp.newTrigger('recordValue')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay)
      .atHour(9)
      .create();
}

// Record history from a cell and append to next available row 
function recordValue() { 
 if (isNotHoliday()){ 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historical_Data"); 
  var lastRow = sheet.getLastRow(); 
  var oldDate = sheet.getRange(lastRow,1).getValue(); 
  var rng = sheet.getRange(lastRow,1,1,36); 
  rng.copyTo(sheet.getRange(lastRow 1,1,1,36)); 
  rng.setValues(rng.getValues()); 
 } 
} 
function isNotHoliday(){ 
 var yesterday = new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1); 
 var formattedDate = Utilities.formatDate(yesterday, Session.getScriptTimeZone(), "M/d/yy") 
 var holidays = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NYSE Holidays').getRange('A2:A').getDisplayValues().join().split(","); 
 return (! ~holidays.indexOf(formattedDate)) 
}

CodePudding user response：

If you wish to run the script once every morning you could use programmatically triggers. This way you can easily set up a script run inside your desired time frame. Then you only would need to get the range of the last row and paste the formula there. I see that the script already gets the range, so you only need Range.setFormula() to drop the formula.

CodePudding user response：

There is a much simpler way to do this - which is to flip time the other way.

You leave the google finance formulas in row 2 along with a =TODAY() in cell B2, with headers in row 1.

every night at 11pm, your sheet inserts a row above row 3, copies the values from row 2 into the new row 3 as values only.

That's it. super simple. you never need to copy formulas at all.

The newest prices are always at the top. This is how most people track portfolios.