custom_functions.js

function movingAverage(start_row, end_row, column, length, triggers) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range_string = "Data!"  + column + start_row + ":" + column + end_row;
  var data = sheet.getRange(range_string).getValues();
  var averages = [];
  
  var rows = data.length - 1;
  for (var row = rows; row >= 0; row--) {
    
    var sum = 0;
    for (var offset = length - 1; offset >= 0; offset--) {
      if ((row + offset) <= (rows - 1)) {
        sum += data[row + offset][0];
      } else {
        sum += data[row][0];
      }
    }
    averages[row] = [(sum / length)];
  }
  return averages;
}
//=IF(AND($C26>$D26,$C27<$D27),"BUY", IF(AND($C26<$D26,$C27>$D27),"SELL",))
function calcPosition(start_position, length, triggers) {
  var position = start_position;
  var currency = 1;
  var sheet = SpreadsheetApp.getActiveSheet();
  var ma1_data = sheet.getRange(14,3,length).getValues();
  var ma2_data = sheet.getRange(14,4,length).getValues();
  var close_data = sheet.getRange(14,2,length).getValues();
  
  for (var i = length - 1; i >= 0; i--) {
    var ma1 = ma1_data[i][0];
    var ma2 = ma2_data[i][0];
    if ((i+1) <= (length - 1)) {
      var ma1p = ma1_data[i + 1][0];
      var ma2p = ma2_data[i + 1][0];
    } else {
      var ma1p = ma1;
      var ma2p = ma2;
    }
    
    if ((ma1 > ma2) && (ma1p < ma2p)) {
      if (currency == 2) {
        var diff = (position / close_data[i][0]) - position;
        position = position + diff;
        currency = 1;
      }
    } else if ((ma2 > ma1) && (ma1p < ma2p)) {
      if (currency == 1) {
        var diff = (position * close_data[i][0]) - position;
        position = position + diff;
        currency = 2;
      }
    }
  }
  
  if (currency == 1) {
    return position;
  } else {
    return (position / close_data[0][0]);
  }
}