Extending ETFs time series with proxies - How to align dates and values

alignDates.gs

// When backtesting trading stategies with exchange traded funds (ETFs), it is most of the time necessary
// to extend these ETFs closing prices time series to have a longer history.

// When the underlying index of the ETF is available, one problem is to how to merge the index data 
// with the ETF data so that there is no price level discontinuity, but before, the first problem to solve
// is how to reconciliate potentially different dates between:
// - The underlying index calculation dates
// - Available trading dates for the other ETFs also used in the trading strategy

// Fictious example:
// ETF calculation dates: 05/01/2017, ...
// Underlying index calculation dates (to extend the ETF in the past): 01/01/2017, 03/01/2017, 04/01/2017
// Available trading dates for other ETFs: 02/01/2017, 04/01/2017, (05/01/2017, ...)
// => Reconciliated dates: 01/01/2017, 02/01/2017, 03/01/2017, 04/01/2017, (05/01/2017, ...)

// In order to facilitate the resolution of the first problem in Google Sheets, 
// I use the function below directly from my spreadsheet.

// Fictious example illustrated:
// alignDates([02/01/2017, 04/01/2017], [01/01/2017, 03/01/2017, 04/01/2017], [Matching index values])
// => [[02/01/2017, NaN], [04/01/2017, matching index value]]

function alignDates(referenceDates, candidateDates, candidateValues) {
  // Convert the input reference dates (column range) coming from the spreadsheet into an array
  var referenceDatesArray = new Array(referenceDates.length);
  for (var i=0; i<referenceDates.length; ++i) {
    referenceDatesArray[i] = referenceDates[i][0];
  }
  
  // Convert the input candidate dates (column range) coming from the spreadsheet into an array
  var candidateDatesArray = new Array(candidateDates.length);
  for (var i=0; i<candidateDates.length; ++i) {
    try {
      candidateDatesArray[i] = candidateDates[i][0].getTime();
    }
    catch (error) {
      throw new Error( "Not a date at candidateDates index " + i );
    }
  }
  
  // Convert the input candidate values (column range) coming from the spreadsheet into an array
  // Requirement is that there is the same number of input candidate values 
  // as the number of input candidate dates
  if (candidateDates.length != candidateValues.length) {
    throw new Error( "Candidate dates and values size mismatch." );
  }
  var candidateValuesArray = new Array(candidateValues.length);
  for (var i=0; i<candidateValues.length; ++i) {
    candidateValuesArray[i] = candidateValues[i][0];
  }
  
  // Assumptions are that:
  // - Candidates dates/values must be aligned with the matching reference dates
  // - If a reference date has no matching candidate date/value, NaN will be selected as a matching value
  //
  // No specific assumption on candidate/reference dates ordering
  var alignedDatesAndValuesArray = new Array(referenceDatesArray.length);
  for (var i=0; i<referenceDatesArray.length; ++i) {
    // Extract the reference date
    var referenceDate = NaN;
    try {
      referenceDate = referenceDatesArray[i].getTime();
    }
    catch (error) {
      throw new Error( "Not a date at referenceDates index " + i );
    }

    // Browse the candidate dates to find a matching date
    for (var j=0; j<candidateDatesArray.length; ++j) {
      if (candidateDatesArray[j] == referenceDate) {
        alignedDatesAndValuesArray[i] = [referenceDatesArray[i], candidateValuesArray[j]];
        break;
      }
    }
    
    // If a matching date was not found, output NaN
    if (j == candidateDatesArray.length) {
      alignedDatesAndValuesArray[i] = [referenceDatesArray[i], NaN];
    }
  }

  // Return the aligned dates and values as a two consecutive columns range
  return alignedDatesAndValuesArray;
}

alignValues.gs

// Now that dates are aligned thanks to the alignDates function, the second problem
// of how to merge the index data with the ETF data so that there is no price level discontinuity
// can be solved.
//
// The solution requires dates and values to merge (typically, coming from the alignDates
// function, i.e. dates-aligned underlying index data), as well as a reference date and
// a reference value (typically, the first close quote and quotation date of the ETF).
//
// Provided the reference date and value are existing in the dates and values to merge,
// the solution is then to make all the values to merge proportional to the reference value so that
// there is no price level discontinuity between the index data and the ETF data.
//
// In order to facilitate the resolution of this problem in Google Sheets, 
// I use the function below directly from my spreadsheet.

function alignValues(datesAndValues, referenceDate, referenceValue) {
    // Extract the reference date
    var refDate = NaN;
    try {
        refDate = referenceDate.getTime();
    }
    catch (error) {
        throw new Error( "referenceDate is not a date" );
    }
  
    // Browse the datesAndValues 2-column array to find the referenceDate
    //
    // No specific assumption dates ordering
    var indexRefDate = -1;
    for (var i=0; i<datesAndValues.length; ++i) {
        if (refDate == datesAndValues[i][0].getTime()) {
          indexRefDate = i;
        }
        break;
    }
 
    // If the referenceDate was not found, exit in error
    if (indexRefDate == -1) {
        throw new Error( "referenceDate nof found in datesAndValues" );
    }
  
    // Otherwise, align the values x_1,...,x_n in the Values part of datesAndValues with referenceValue
    // so that the aligned values y_1,...,y_n satisty y_indexRefDate = referenceValue
    // and y_i/y_indexRefDate = x_i/x_indexRefDate, i=1..n, i != indexRefDate
    var datesAndAlignedValuesArray = new Array(datesAndValues.length);
    for (var i=0; i<datesAndValues.length; ++i) {
        datesAndAlignedValuesArray[i] = [datesAndValues[i][0], referenceValue * (datesAndValues[i][1] / datesAndValues[indexRefDate][1])];
    }
  
    // Return the dates and aligned values
    return datesAndAlignedValuesArray;
}