YAHOO_PRICE_ON_DATE

 // var url = 'https://query2.finance.yahoo.com/v8/finance/chart/0P0001BM48.F?symbol=0P0001BM48.F&period1=1733961600&period2=1734393600&interval=5d'
/**
 * Fetches the closing price for a Yahoo Finance symbol on a specific date.
 *
 * @param {string} symbol The Yahoo Finance ticker symbol (e.g., "0P00008W4T.F").
 * @param {Date} date The target date (e.g., a cell reference like A2).
 * @return {number} The closing price, or an error message if not found.
 * @customfunction
 */
function checkDataStructure(result) {
    // 1. Check if the 'result' object is falsy (null, undefined, etc.)
    if (!result) {
      return "ERROR: The data object itself is missing or empty.";
    }

    // 2. Check for the 'timestamp' property
    if (!result.timestamp) {
      return "ERROR: 'timestamp' property is missing or invalid in the data.";
    }

    // 3. Check for the nested structure step-by-step
    const quoteData = result.indicators?.quote;

    // Check for 'indicators' or 'quote' array
    if (!quoteData) {
      return "ERROR: The 'indicators.quote' array is missing or empty.";
    }

    // Check if the 'quote' array has at least one element
    if (quoteData.length === 0) {
      return "ERROR: 'indicators.quote' array is empty.";
    }

    const firstQuote = quoteData[0];

    // Check for the 'close' property on the first element
    if (!firstQuote.close) {
      return "ERROR: The 'close' price is missing or invalid in the first quote element.";
    }

    // If all checks pass, the data structure is valid
    return null; // Or return the data if you prefer
}

function YAHOO_PRICE_ON_DATE(symbol, date) {
  // Use the date components to create a new Date object at UTC midnight.
  // This is the most reliable way to handle dates passed from Google Sheets.
  const targetDateUTC = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const targetTimestamp = Math.floor(targetDateUTC.getTime() / 1000); // Target day's midnight UTC

  // Set a range (e.g., 10 days before to 1 day after)
  const oneDay = 24 * 60 * 60; // seconds
  const period1 = targetTimestamp - (10 * oneDay);
  const period2 = targetTimestamp + (1 * oneDay);
  // 2. Construct the URL with a DAILY interval
  const url = `https://query2.finance.yahoo.com/v8/finance/chart/${symbol}?symbol=${symbol}&period1=${period1}&period2=${period2}&interval=1d`;

  try {
    const response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    const data = JSON.parse(response.getContentText());

    const result = data?.chart?.result?.[0];

    const validationError = checkDataStructure(result);
    if (validationError) {
      // The checkDataStructure function returned an error string, so we return it.
      return validationError;
    }

    const timestamps = result.timestamp;
    const closePrices = result.indicators.quote[0].close;

    // 3. Find the matching price
    for (let i = 0; i < timestamps.length; i++) {
      // API timestamps are usually midnight UTC for the end of the trading day.
      if (timestamps[i] === targetTimestamp) {
        const price = closePrices[i];
        if (price !== null) {
          return price;
        }
      }
    }

    // Fallback: if the exact timestamp isn't found, return the most recent price
    // *before* the target date, as market data may be delayed or unavailable for the exact day.
    for (let i = timestamps.length - 1; i >= 0; i--) {
        if (timestamps[i] <= targetTimestamp && closePrices[i] !== null) {
            return closePrices[i];
        }
    }


    return "Price not found for date.";

  } catch (e) {
    return `ERROR: ${e.toString()}`;
  }
}