Usando datos de Yahoo Finance en Google Sheets (parte 4)

Amplío las funcionalidades de la importación de datos de Yahoo Finance mediante el uso de custom functions en Google Apps Script

Usando datos de Yahoo Finance en Google Sheets (parte 4)
Photo by Joshua Aragon / Unsplash
🚨
[JULIO 2023] Actualizado para contemplar la nueva cookie que Yahoo está pidiendo para bajar los datos en todos los endpoints.

En la parte 1 vimos cómo importar datos de Yahoo Finance en nuestras hojas de seguimiento, usando la librería IMPORTJSONAPI. En la parte 2 vimos todos los datos financieros que podíamos obtener de una empresa. Y por último en la parte 3 vimos que también podíamos obtener los datos de la cadena de opciones, para aquellos que operamos con este instrumento.

He estado usando las fórmulas que propuse en los artículos desde entonces, con muy buenos resultados. Pero últimamente, al tener más empresas en seguimiento y más operaciones de opciones abiertas, estaba empezando a notar que la tabla era muy lenta en cargar. Además, si era necesario hacer alguna condición, o sumar varios precios de opciones, por ejemplo en estrategias con opciones, las fórmulas son muy largas y difíciles de seguir.

Vamos a ver una forma de solucionar ambos problemas a la vez.

Custom functions en Google Sheets

En la parte 1 ya vimos que podemos definir funciones propias para ser usadas en Google Sheets. Es lo que hicimos para cargar la función IMPORTJSONAPI.

Google Apps Script es un código en Javascript que puede acceder a las APIs de los productos de Google y realizar operaciones de forma más sencilla que directamente en la hoja de cálculo.

Lo que vamos a hacer en este artículo es definir unas funciones que nos simplifiquen el uso de IMPORTJSONAPI, pues los parámetros para obtener los datos que necesitamos de Yahoo estarán dentro del código y además aprovecharemos para meter en una caché las respuestas de la API de Yahoo, de modo que hagamos menos llamadas a esa API y carguemos más rápido nuestra hoja de seguimiento.

Voy a daros el código necesario para obtener el sector de una acción, las fechas de ex-dividend y dividendo de una acción, y tantos datos como queráis de los datos financieros que veíamos en la parte 2 de una o múltiples acciones.

Para empezar, tenemos que volver a abrir el editor de código de Apps Script. Para ello vamos a Extensiones > Apps Scripts. Una vez en el editor, si no lo tenemos ya, tenemos que añadir el código de IMPORTJSONAPI a un fichero, como contaba en el primer artículo.

Ahora lo que haremos es añadir un archivo pinchando en el + que hay a la derecha de Files. Le podemos poner el nombre que queramos, por ejemplo, Yahoo_API. Una vez que tenemos el fichero, tenemos que copiar el código que está al final del artículo y darle al botón de guardar.

Una vez hemos hecho esto, si volvemos a nuestra hoja, podemos usar las funciones nuevas: =YH_SECTOR(), =YH_DIVDATE(), =YH_STOCKDATA() y =YH_OPTIONPRICE(). Al empezar a escribirlas cada una mostrará una ayuda como la que tienen las funciones de Google Sheets.

Para la fórmula YH_STOCKDATA el segundo parámetro es una cadena con los campos que queremos obtener, dentro de los que nos da la API para la consulta múltiple de acciones. Para poder obtener una lista de todos los campos que muestra este endpoint podemos usar una URL como la de abajo (ejemplo para Coca-Cola).

El CRUMB que tenemos que sustituir por un código se ha de obtener siguiendo los pasos siguientes:

  1. Entramos en https://fc.yahoo.com - dará un error 404, pero almacena un cookie en el navegador
  2. Entramos en https://query2.finance.yahoo.com/v1/test/getcrumb - la página nos responderá con un código que es el CRUMB que tenemos que sustituir en la URL de abajo
https://query1.finance.yahoo.com/v7/finance/quote?symbols=KO&crumb=CRUMB

El resultado de esta URL lo podemos pegar en un visor JSON online para verlo más cómodamente.

Un ejemplo de cadena sería "symbol,longName,trailingAnnualDividendYield,trailingAnnualDividendRate" que os daría una tabla como la de la imagen.

Por supuesto se pueden escribir nuevas funciones, usando otros de los módulos de la API de Yahoo, para obtener por ejemplo los datos financieros.

Espero que estas funciones os sean útiles y os inspiren a escribir otras propias para vuestro uso.


/**
  *    Yahoo API helper functions for Google Apps Script
  *    Copyright (C) 2022-2023 Boveda Inversion
  *
  *    This program is free software: you can redistribute it and/or modify
  *    it under the terms of the GNU General Public License as published by
  *    the Free Software Foundation, either version 3 of the License, or
  *    any later version.
  *
  *    This program is distributed in the hope that it will be useful,
  *    but WITHOUT ANY WARRANTY; without even the implied warranty of
  *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  *    GNU General Public License for more details.
  *
  *    You should have received a copy of the GNU General Public License
  *    along with this program.  If not, see <https://www.gnu.org/licenses/>.
  *
  */


/**
 * Return the Sector of a firm
 *
 * @param {string} ticker The ticker desired
 * @return The sector of the firm
 * @customfunction
 */
function YH_SECTOR(ticker) {
  if (!ticker) {
    return null;
  }
  var sector_key = ticker + "_sector";

  var cache = CacheService.getScriptCache();
  var cached = cache.get(sector_key);

  if (cached != null) {
    console.log(sector_key + " -(WHAT): " + cached);
    return cached;
  }

  // No tenemos los datos. Necesitamos recuperarlos de Yahoo
  // Primero necesitamos la nueva cookie y crumb que nos proporciona Yahoo
  let yh_params = yh_cookie();

  var headers = {
    'cookie': yh_params[0]
  }
  var params = "headers=" + JSON.stringify(headers); 

  var sector = IMPORTJSONAPI("https://query1.finance.yahoo.com/v10/finance/quoteSummary/" + ticker + "?modules=summaryProfile" + "&crumb=" + yh_params[1],
                    "$..summaryProfile",
                    "sector",
                    params);
  
  if (sector[0][0].includes("ERROR")) {
    return null;
  }
  else {
    cache.put(sector_key, sector[0][0], 3600);
    return sector[0][0];
  }
};

 /**
 * Return the dividend or ex-dividend date of a stock
 *
 * @param {string} ticker The ticker desired
 * @param {boolean} exDividendDate true to get ex-dividend date
 * @return The dividend or ex-dividend date
 * @customfunction
 */
function YH_DIVDATE(ticker, exDividendDate = false) {
  if (!ticker) {
    return null;
  }

  var ex_div_key = ticker + '_ex_div';
  var div_key = ticker + '_div';
  
  var cache = CacheService.getScriptCache();
  var cached = cache.get((exDividendDate == true) ? ex_div_key : div_key);

  if (cached != null) {
    console.log("Cached: " + cached);
    //return cached;
  }
    
  // No tenemos los datos. Necesitamos recuperarlos de Yahoo
  // Primero necesitamos la nueva cookie y crumb que nos proporciona Yahoo
  let yh_params = yh_cookie();

  var headers = {
    'cookie': yh_params[0]
  }
  var params = "headers=" + JSON.stringify(headers); 

  var dividend_data = IMPORTJSONAPI("https://query1.finance.yahoo.com/v10/finance/quoteSummary/" + ticker + "?modules=calendarEvents" + "&crumb=" + yh_params[1],
                  "$..calendarEvents",
                  "exDividendDate.raw,dividendDate.raw",
                  params);

  if (dividend_data[0][0] == null || dividend_data[0][1] == null) {
      dividend_data = [['No Div','No Div']];
      cache.put(ex_div_key, dividend_data[0][0], 3600); //21600 is max
      cache.put(div_key, dividend_data[0][1], 3600);
      return 'No Div';
  } else if (String(dividend_data[0][0]).includes("ERROR")) {
    return null;
  }

  dividend_data[0][0] = new Date(dividend_data[0][0]*1000);
  dividend_data[0][1] = new Date(dividend_data[0][1]*1000);

  cache.put(ex_div_key, dividend_data[0][0], 3600); //21600 is max
  cache.put(div_key, dividend_data[0][1], 3600);
  console.log(dividend_data);
  return ((exDividendDate == true) ? dividend_data[0][0] : dividend_data[0][1]);
};

/**
 * Return the payout ratio of a firm
 *
 * @param {string} ticker The ticker desired
 * @return The Payout ratio
 * @customfunction
 */
function YH_PAYOUTRATIO(ticker, cache_disabled=false) {
  if (!ticker) {
    return null;
  }

  var sector_key = ticker + "_payoutratio";

  var cache = CacheService.getDocumentCache();
  var cached = cache.get(sector_key);

  if (cached != null && cache_disabled == false) {
    return cached;
  }

  // No tenemos los datos. Necesitamos recuperarlos de Yahoo
  // Primero necesitamos la nueva cookie y crumb que nos proporciona Yahoo
  let yh_params = yh_cookie();

  var headers = {
    'cookie': yh_params[0]
  }
  var params = "headers=" + JSON.stringify(headers); 

  var sector = IMPORTJSONAPI("https://query1.finance.yahoo.com/v10/finance/quoteSummary/" + ticker + "?modules=summaryDetail" + "&crumb=" + yh_params[1],
                    "$..summaryDetail",
                    "payoutRatio.fmt",
                    params);
  
  if (sector[0][0].includes("ERROR")) {
    return null;
  }
  else {
    cache.put(sector_key, sector[0][0], 21600);
    return sector[0][0];
  }
};

 /**
 * Return data from a stock
 *
 * @param {string} ticker The ticker or tickers desired
 * @param {string} data Comma separate list of data elements to retieve
 * @return A matrix with the data
 * @customfunction
 */
function YH_STOCKDATA (tickers, data, cache_disabled=false) {

  if (tickers == null || data == null) return;

  if (!cache_disabled) {
    var cache = CacheService.getDocumentCache();
  }

  var ticker_list = "";

  if (Array.isArray(tickers)) {
    ticker_list = tickers.join(",");
  } else {
    ticker_list = tickers;
  }

  if (!cache_disabled) {
    var cache_key = hashCode_(ticker_list + data);
    Logger.log("KEY:" + cache_key);
    var cached = cache.get(cache_key);
    Logger.log(cached);
    if (cached != null) {
      return JSON.parse(cached);
    }
  }

  // No tenemos los datos. Necesitamos recuperarlos de Yahoo
  // Primero necesitamos la nueva cookie y crumb que nos proporciona Yahoo
  let yh_params = yh_cookie();

  var headers = {
    'cookie': yh_params[0]
  }
  var params = "headers=" + JSON.stringify(headers); 

  var stock_data = IMPORTJSONAPI("https://query2.finance.yahoo.com/v7/finance/quote?lang=en-US&region=US&corsDomain=finance.yahoo.com&symbols="+ ticker_list +"&crumb=" + yh_params[1], 
                    "$.quoteResponse.result.*", 
                    data,
                    params);

 // Crear manualmente la cadena para la cache - algunos resultados tienen comas en el nombre.
  var json = JSON.stringify(stock_data);

  if (!cache_disabled) {  
    cache.put(cache_key, json, 21600); // Estos datos no cambian... usar el máximo (6h)
  }

  return stock_data;

};


 /**
 * Return the price of an option
 *
 * @param {string} ticker The ticker desired
 * @param {string} callput PUT or CALL
 * @param {float} strike The strike price
 * @param {date} date The setting date
 * @param {boolean} market The price of ask or regular_market - onl
 * @return The price of the option
 * @customfunction
 */
function YH_OPTIONPRICE(ticker, callput, strike, date, market) {
  if (strike == 0)
    return 0;

  var mk = (market == true) ? "ask" : "regularMarketPrice";
  var type = (callput == "PUT") ? "P" : "C";

  option_ticker = ticker + getDate_(date) + type + getStrike_(strike);

  var cache = CacheService.getScriptCache();
  var cached = cache.get(option_ticker+mk);

  if (cached != null) {
    return cached;
  };

  var price = IMPORTJSONAPI("https://query1.finance.yahoo.com/v7/finance/options/" + option_ticker, "$..*.quote", mk);

  if (market == true) {
    cache.put(option_ticker+mk, price, 900);
  }
  else {
    cache.put(option_ticker+mk, price, 3600);
  }
  
  return price;

};

function getDate_(fecha) {

  var formatted_date = Utilities.formatDate(fecha, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyMMdd");

  return formatted_date;
};

function getStrike_(strike) {

  num = strike.toString();
  parts = num.split(".");
  
  var formatted_strike = Utilities.formatString("%05d", parts[0]) + ((parts.length > 1) ? Utilities.formatString("%d00", parts[1]) : "000");

  return formatted_strike;
  
};

function yh_cookie() {
  var url  = "https://fc.yahoo.com/";
  var option = {
    headers: {
    },
    muteHttpExceptions: true
  };
  let response = UrlFetchApp.fetch(url, option);

  let headers = response.getHeaders();
  let cookie = headers['Set-Cookie'];
  let ck = cookie.split(';')[0];

  url = "https://query2.finance.yahoo.com/v1/test/getcrumb";
  option = {
    headers: {
      "cookie": ck,
    },
    muteHttpExceptions: true
  };
  
  response = UrlFetchApp.fetch(url, option);

  let crumb = response.getContentText();

  return [ck, crumb];
};

/**
 * Returns a hash code for a string.
 * (Compatible to Java's String.hashCode())
 *
 * The hash code for a string object is computed as
 *     s[0]*31^(n-1) + s[1]*31^(n-2) + ... + s[n-1]
 * using number arithmetic, where s[i] is the i th character
 * of the given string, n is the length of the string,
 * and ^ indicates exponentiation.
 * (The hash value of the empty string is zero.)
 *
 * @param {string} s a string
 * @return {number} a hash code value for the given string.
 */
function hashCode_(s) {
  var h = 0, l = s.length, i = 0;
  if ( l > 0 )
    while (i < l)
      h = (h << 5) - h + s.charCodeAt(i++) | 0;
  return h;
};