Last Updated on 06/01/2025 by Ndanileka

Since its inception, Yahoo Finance has been a cornerstone for investors and market enthusiasts. It offers price quotes, breaking news, and a wealth of financial data. The platform provides a comprehensive snapshot of the markets alongside curated business headlines. Thanks to its clean interface and diverse tools, it’s a favorite among casual investors.

Beyond the basics, Yahoo Finance offers watchlists for tracking multiple tickers, interactive charts for visualizing performance, and aggregated research reports for deeper analysis. Interestingly, Yahoo initially provided an official API, but it was discontinued in 2017 due to misuse. Nevertheless, the financial community quickly adapted, discovering alternative methods to access Yahoo Finance’s rich public data sources.

YFINANCE() Custom Function

Building on the resources available online, I created a custom YFINANCE() function that pulls historical price data from Yahoo Finance into Google Sheets. I designed it to mimic the GOOGLEFINANCE() funtion.

The function accepts the following parameters:

• ticker: The stock ticker symbol for the desired company or asset.
  (ticker must match Yahoo Finance formatting!)
  
• startDate: The start date for the historical data retrieval.
  
• endDate: The end date for the historical data retrieval.
  
• interval (optional): The interval at which the data is aggregated (e.g., “1d” for daily data, “1wk” for weekly data, “1mo” for monthly data).

Returned Data:

Apps Script Code:

/**
 * Fetch historical stock data from Yahoo Finance and return it as a 2D array.
 *
 * @customfunction
 * @param {ticker} ticker - The stock ticker symbol (e.g., "AAPL").
 * @param {date} startDate - The start date for the data (e.g., "2020-01-01").
 * @param {date} endDate - The end date for the data (e.g., "2023-12-31").
 * @param {string} interval - The data interval (e.g., "1d", "1wk", "1mo"). Defaults to "1d".
 * @returns {Array[]} - A 2D array of stock data including Date, Open, High, Low, Close, and Volume.
 */
function yfinance(ticker, startDate, endDate, interval = "1d") {
  // Validate inputs
  if (!ticker || !startDate || !endDate) {
    throw new Error("Parameters ticker, startDate, and endDate are required.");
  }

  // Convert dates to UNIX timestamps
  const period1 = Math.floor(new Date(startDate).getTime() / 1000);
  const period2 = Math.floor(new Date(endDate).getTime() / 1000);

  // Yahoo Finance chart endpoint
  const url = `https://query2.finance.yahoo.com/v8/finance/chart/${ticker}?period1=${period1}&period2=${period2}&interval=${interval}&events=history`;

  try {
    // Fetch data
    const response = UrlFetchApp.fetch(url);
    const json = JSON.parse(response.getContentText());

    // Parse JSON data
    const timestamps = json.chart.result[0].timestamp;
    const quotes = json.chart.result[0].indicators.quote[0];

    // Combine into a 2D array
    const rows = [["Date", "Open", "High", "Low", "Close", "Volume"]];
    timestamps.forEach((timestamp, index) => {
      const date = new Date(timestamp * 1000).toISOString().split("T")[0];
      rows.push([
        date,
        quotes.open[index],
        quotes.high[index],
        quotes.low[index],
        quotes.close[index],
        quotes.volume[index]
      ]);
    });

    return rows; // Return the data as a 2D array
  } catch (error) {
    throw new Error("Error fetching data: " + error.message);
  }
}

Happy Trading!

Disclaimer

Please note that this yfinance() function is a workaround solution, and is not officially supported by Yahoo Finance. As such, it may stop working at any time if changes are made to Yahoo Finance’s public data endpoints. Enjoy it while it lasts!