Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // 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 or immediately before a target date.
- * Includes caching, browser headers, and flexible date parsing.
- *
- * @param {string} symbol The Yahoo Finance ticker symbol (e.g., "0P00008W4T.F", "AAPL").
- * @param {Date|string} date The target date (cell reference or date string).
- * @return {number|string} The closing price or an error message.
- * @customfunction
- */
- function YAHOO_PRICE_ON_DATE(symbol, date) {
- if (!symbol) return "ERROR: Missing symbol.";
- // 1. Normalize Date input
- const d = date instanceof Date ? date : new Date(date);
- if (isNaN(d.getTime())) return "ERROR: Invalid date.";
- const targetTimestamp = Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()) / 1000;
- const cacheKey = `YF_${symbol.toUpperCase()}_${targetTimestamp}`;
- // 2. Check Cache first (reduces quota usage)
- const cache = CacheService.getScriptCache();
- const cachedPrice = cache.get(cacheKey);
- if (cachedPrice !== null) {
- return Number(cachedPrice);
- }
- try {
- const oneDay = 86400;
- const period1 = targetTimestamp - 100 * oneDay;
- const period2 = targetTimestamp + 1 * oneDay;
- const encodedSymbol = encodeURIComponent(symbol.trim());
- const url = `https://query2.finance.yahoo.com/v8/finance/chart/${encodedSymbol}?symbol=${encodedSymbol}&period1=${period1}&period2=${period2}&interval=1d`;
- // 3. Fetch with custom User-Agent to avoid 403 Forbidden errors
- const options = {
- muteHttpExceptions: true,
- headers: {
- 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
- }
- };
- const response = UrlFetchApp.fetch(url, options);
- if (response.getResponseCode() !== 200) {
- return `ERROR: Yahoo Finance returned HTTP ${response.getResponseCode()}`;
- }
- const json = JSON.parse(response.getContentText());
- const result = json?.chart?.result?.[0];
- const timestamps = result?.timestamp;
- const closePrices = result?.indicators?.quote?.[0]?.close;
- if (!timestamps?.length || !closePrices?.length) {
- return "ERROR: No price data returned for this symbol.";
- }
- // 4. Reverse search for the most recent valid close price on or before target date
- for (let i = timestamps.length - 1; i >= 0; i--) {
- if (timestamps[i] <= targetTimestamp && closePrices[i] != null) {
- const price = closePrices[i];
- // Cache historical prices for 30 minutes (1800 seconds)
- cache.put(cacheKey, price.toString(), 1800);
- return price;
- }
- }
- return "Price not found for date.";
- } catch (e) {
- return `ERROR: ${e.message || e.toString()}`;
- }
- }
Advertisement
Comments
-
- You must fetch the Yahoo Finance code for each stock to use it in this function. It very often coincides with the standard ticker, but it can sometimes be different. There is a daily query limit for yahoo finance, so use this function wisely.
- Usage: =YAHOO_PRICE_ON_DATE('GOOG', TODAY())
-
- I added a 30 minutes Cache functionality to prevent too many queries (20,000/day is the limit). This number can be reduced if your query needs are small.
-
User was banned
Add Comment
Please, Sign In to add comment