Usando datos de Yahoo Finance en Google Sheets (parte 1)
Explico cómo importar datos en JSON, de Yahoo Finance, en nuestra hoja de cálculo de Google Sheets de seguimiento de cartera.
Imagino que muchos tendréis como yo la hoja de seguimiento de la cartera en Google Sheets, cada uno con el layout y los datos que más le convengan, más o menos complicada.
Una de las cuestiones a las que nos enfrentamos muchos es de dónde obtener los datos que queremos que aparezcan en la hoja. Por supuesto están las funciones de Google Finance que nos dan la mayoría de los datos básicos como precio, % de cambio desde el día anterior, precio histórico a una fecha concreta, ... Pero en algunas ocasiones el dato que queremos no está en estas funciones. Por ejemplo, no tenemos el dato de importe y % de dividendo que paga la acción o en el caso de las opciones Google Finance no proporciona datos de coste de las cadenas de opciones.
Aquí os voy a contar cómo extraer estos datos de Yahoo Finance. Una de las formas sería hacer 'scraping' de su web con las funciones IMPORTHTML e IMPORTXML de Google Sheets, pero en algunos casos como el de las opciones, llegar a la ruta de la que extraer el dato no es sencillo.
Datos de Yahoo Finance API en JSON
Para la extracción de datos nos vamos a basar en que Yahoo Finance sigue proporcionando desde su API en formato JSON.
Desde mayo de 2023, Yahoo ha impuesto la necesidad de obtener una cookie para bajar los datos de algunos de los sitios que describo a continuación. Para obtener el código necesario hay que seguir estos pasos en un navegador:
- Ir a https://fc.yahoo.com - dará un error, pero se almacena una cookie en el navegador.
- Ir a la URL https://query2.finance.yahoo.com/v1/test/getcrumb - la página nos devolverá un código. Ese código es el CRUMB que hay que sustituir en la URL para obtener los datos.
Así por ejemplo, si queremos los datos de Yahoo Finance de una acción no tenemos más que teclear la siguiente URL, que nos devolverá los datos de Coca-Cola:
https://query1.finance.yahoo.com/v7/finance/quote?symbols=KO&crumb=CRUMBSe nos devolverá una cadena de texto en formato JSON con esta pinta
que podemos ver de forma estructurada copiandola en un visor de JSON online
Usando JSON en Google Sheets
Para poder usar JSON en Google Sheets necesitamos una librería que nos ayude a traer estos datos de la web y luego poder obtener de forma ordenada los que queremos. Para ello vamos a usar IMPORTJSONAPI.
qeetEsta librería nos permite con una fórmula de este estilo obtener todos los datos que necesitamos en las celdas de Google Sheet.
=IMPORTJSONAPI("https://test.com/api"; "$..Title"; "@")La forma de instalarla está explicada en GitHub aunque hay algún paso un poco diferente con el nuevo App Scripts, pero sigue siendo tan sencillo como ir a Extensiones > App Scripts y ahí dentro, en Code.gs pegar toda la librería entera desde el sitio de GitHub.
Una vez que hayamos guardado los cambios podemos volver a nuestra hoja de cálculo y comprobar que tenemos la fórmula =IMPORTJSONAPI funcionando.
Obtener el porcentaje de dividendo pagado
Para conseguir la función que envuelva a la orginal y obtenga la cookie, tenemos que añadir un segundo fichero a App Scripts, haciendo click en el símbolo + que hay a la derecha de Files. Dentro de este nuevo fichero, copiaremos el código siguiente:
function IMPORTJSONAPI_YH(url, query, cols) {
let yh_params = yh_cookie();
var headers = {
'cookie': yh_params[0]
}
var params = "headers=" + JSON.stringify(headers);
var stock_data = IMPORTJSONAPI(url +"&crumb=" + yh_params[1],
query,
cols,
params);
return stock_data;
}
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];
}Una cosa interesante es que la URL de arriba puede proporcionar los datos de más de una acción al tiempo, simplemente añadiendo el resto de tickers adicionales de los que queremos obtener datos separados con comas. De esta forma optimizamos el tiempo que le lleva a Google Sheets obtener la información, porque lo hará en una sola descarga y además conectamos menos veces con el servidor de Yahoo Finance, lo cual entiendo que ellos agradecerán también.
Tened en cuenta que este servicio lo están dando de forma gratuita y que además, cuando no visitamos su página no tienen ingresos por publicidad, por lo que creo que es adecuado hacer un uso razonable y no intentar hacer un programa de tiempo real haciendo polling cada segundo. Si este es vuestro objetivo, creo que hay APIs de pago que os pueden dar lo que necesitáis.
Imaginemos que tengo una tabla de radar como la de abajo, en la que listo las acciones que estoy considerando comprar, varios datos que obtengo de Google Finance directamente y quiero añadir el dividendo. En este caso lo primero que tenemos que tener en cuenta es que el ticker en Yahoo Finance y Google Finance no siempre coincide. Esto ocurre con todas las acciones de mercados no americanos, pues la forma de expresar el mercado es distinta, e incluso con alguna del mercado americano, como con Berkshire Hathaway como se puede ver abajo. Para solucionar esto necesitamos una segunda columna con los tickers según Yahoo Finance, que podéis encontrar con su buscador.
Una vez que tenemos esto, la fórmula que uso para obtener una matriz de todos los tickers y los porcentajes de dividendo es:
=IMPORTJSONAPI_YH("https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US®ion=US&corsDomain=finance.yahoo.com&symbols="& JOIN(",";FILTER(ENCODEURL(C3:C);C3:C<>"")); "$.quoteResponse.result.*"; "symbol,trailingAnnualDividendYield,trailingAnnualDividendRate")Vamos paso a paso en la fórmula:
- IMPORTJSONAPI_YH(): es la función wrapper a la librería que hemos cargado antes.
- La URL de descarga de los datos ... pero sin ningún ticker, que añadimos con el símbolo & que concatena las dos cadenas de texto
- ENCODEURL() - lo que hace esta función es codificar cualquier símbolo que haya en el ticker. No sé si siendo tickers hará falta, pues éstos no pueden tener cosas extrañas como la ñ, pero aún así no hace daño.
- FILTER() - esta función nos devuelve una lista del contenido de todas las celdas que hay desde C3 hasta el final (se denomina como C a la columna completa)
- $.quoteResponse.result.* - este argumento dice que tome todos los elementos del árbol después de result. Cada uno de los tickers tiene los datos en un array que empieza por {0}, luego {1}, ...
- symbol,trailingAnnualDividendYield,trailingAnnualDividendRate - aquí tenemos los últimos tres parámetros. Lo que estamos pidiendo aquí es que nos devuelva los datos asociados a estas tres claves.
Una vez que introducimos esta fórmula en una celda de nuestro hoja de cálculo, obtendremos una tabla con la pinta de la imagen de abajo (los encabezados son míos). Estos datos los podemos importar en otra parte de nuestra hoja, o en una pestaña separada, y luego usando la función VLOOKUP traerlos a nuestra tabla de datos.
Cambiando los parámetros de esta función podemos ir obteniendo por columnas todo el resto de datos que nos ofrece esta API para las acciones. Podéis ver la lista si hacéis los pasos que he explicado más arriba para obtener el JSON y luego usar un visor de estos datos de forma estructurada.
Por último, un detalle importante. Las fechas que nos da esta API están un formato conocido como UNIX epoch, es decir, son los segundos transcurridos desde el 1 de enero de 1970. Para transformarlos en una fecha entendible, la fórmula a usar es (si tenemos el valor en la celda A1)
=A1/86400+DATE(1970;1;1)Espero que esto os sirva para mejorar el seguimiento de vuestras carteras. Os recomiendo también que leáis los siguientes artículos en la serie:
