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

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. Podéis ver cuáles son pegando la URL de ejemplo siguiente en el navegador y luego el texto que obtenemos de resultado en un visor de JSON.

https://query1.finance.yahoo.com/v7/finance/quote?symbols=KO

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

TICKERNAMEDIVIDEND %DIVIDEND
GOOGLAlphabet Inc.0.00%0.000
AMZNAmazon.com, Inc.0.00%0.000
SBUXStarbucks Corporation2.16%1.880
BABAAlibaba Group Holding Limited0.00%0.000
JDJD.com, Inc.0.00%0.000
ITX.MCIndustria de Diseño Textil, S.A.3.01%0.630
MCDMcDonald's Corporation2.23%5.250
PEPPepsiCo, Inc.2.61%4.247
KOThe Coca-Cola Company2.77%1.680
PMPhilip Morris International Inc.5.21%4.900

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 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;
  }

  var sector = IMPORTJSONAPI("https://query1.finance.yahoo.com/v10/finance/quoteSummary/" + ticker + "?modules=summaryProfile","$..summaryProfile","sector")
  
  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;
  }
  
  var dividend_data = IMPORTJSONAPI("https://query1.finance.yahoo.com/v10/finance/quoteSummary/" + ticker + "?modules=calendarEvents","$..calendarEvents","exDividendDate.raw,dividendDate.raw");
  console.log(dividend_data);

  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 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) {

  var ticker_list = tickers.join(",");
 
  // We don't cache this data, as it is retrieved in only one call for all the stocks in the range
  var stock_data = IMPORTJSONAPI("https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&region=US&corsDomain=finance.yahoo.com&symbols="+ ticker_list, 
                    "$.quoteResponse.result.*", 
                    data);

  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;
  
}