Money Flow Index (MFI)

 * Calculates the Money Flow Index (MFI) for a given dataset and period.
 * 
 * @param {array} data - a 2D array where the first column is the  dates, and subsequent columns for  open, high, low, close and volume. 
 * @param {number} period - The number of periods for calculating the MFI.
 * @returns {array} - A 2D array with headers: Date, MFI.
 * @customfunction
 */

function MFI(data, period) {
  // Preprocess data using getData
  const processedData = getData(data);
  const dates = processedData.slice(1).map(row => row[0]); // Extract dates
  const highs = processedData.slice(1).map(row => row[2]); // Extract high prices
  const lows = processedData.slice(1).map(row => row[3]); // Extract low prices
  const closes = processedData.slice(1).map(row => row[4]); // Extract close prices
  const volumes = processedData.slice(1).map(row => row[5]); // Extract volumes

  // Check if the period is greater than the length of the data
  if (period > closes.length) {
    throw new Error("Error: The specified period exceeds the length of the data.");
  }

  // Initialize arrays for MFI calculation
  const mfiValues = [["Date", `MFI(${period})`]]; // Include headers

  // Calculate Typical Price (TP) and Raw Money Flow (RMF)
  const typicalPrices = closes.map((close, i) => (highs[i] + lows[i] + close) / 3);

  const moneyFlows = typicalPrices.map((tp, i) => tp * volumes[i]);

  // Calculate MFI for each period
  for (let i = period - 1; i < typicalPrices.length; i++) {
    let positiveFlow = 0;
    let negativeFlow = 0;

    for (let j = 0; j < period; j++) {
      const currentTP = typicalPrices[i - j];
      const previousTP = typicalPrices[i - j - 1];

      if (currentTP > previousTP) {
        positiveFlow += moneyFlows[i - j];
      } else if (currentTP < previousTP) {
        negativeFlow += moneyFlows[i - j];
      }
    }

    // Calculate Money Flow Ratio (MFR)
    const moneyFlowRatio = positiveFlow / negativeFlow;

    // Calculate MFI
    const mfi = 100 - (100 / (1 + moneyFlowRatio));

    // Append the MFI value with the corresponding date
    mfiValues.push([dates[i], mfi]);
  }

  return mfiValues;
}

/**
 *
 * @param {Array<Array>} data - A 2D array of cell values to process. The first row should contain headers.
 * @returns {Array<Array>} - A 2D array where the first row contains headers and subsequent rows contain processed data.
 *
 * @customfunction
 * 
 */

function getData(data) {
  if (!data || data.length === 0) {
    throw new Error("Input data is empty or invalid.");
  }

  // Check if the first row contains headers
  const headers = Array.isArray(data[0]) && data[0].every(item => typeof item === "string")
    ? data[0]
    : null;

  if (!headers) {
    // If no headers, assume the first column is the date column and process data
    return data.map(row => row.map((value, index) => {
      return index === 0 ? new Date(value) : parseFloat(value);
    }));
  }

  // Process rows with headers
  return [headers, ...data.slice(1).map(row => headers.map((header, index) => {
    const key = header.toLowerCase();
    return key === "date" ? new Date(row[index]) : parseFloat(row[index]) || row[index];
  }))];
}