jueves, 21 de enero de 2021

Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF

Un grupo de funciones muy interesantes para interactuar con la www son las funciones Web. Especialmente cuando la web sobre la que trabajamos contiene información en HTML o bien en XML(Extensible Markup Language)... preferiblemente. Ya que para nuestro propósito necesitamos una estrucutra muy clara de lenguaje con etiquetas.
Si estas familiarizado con estos lenguajes entenderás muy rápido el sentido de estas funciones...

Pero vamos por partes.

Lo primero será acceder al contenido de una web. En mi ejemplo a datos del Gobierno de España (información pública y accesible por todo el mundo) a través de su web: datos.gob.es.
En concreto listaremos las provincas españolas. La URL completa que necesitaremos es:
https://datos.gob.es/apidata/nti/territory/Province.xml?_pageSize=100&_page=0&_sort=label

Escribimos entonces en A1 esa dirección, y en A2 la función:
=SERVICIOWEB(A1)
y obtendríamos en nuestra celda el código XML completo de esa dirección:
Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF

Es decir, tenemos información identificada y clasificada por etiquetas... lo cual nos facilitará encontrar el patrón necesario para recuperar ciertos datos... por ejemplo, los nombres de las provincias españolas, las cuales están etiquetadas por el nombre <label>

Y aquí entra la siguiente función: XMLFILTRO
XMLFILTRO(xml, xpath)
que especificamente requiere un código XML (aunque un HTML igualmente lo trabajaría correctamente); y como segundo argumento la 'ruta' o niveles de las etiquetas, cuya información, nos interesa.
Ten presente que en estos tipos de lenguajes las etiquetas se anidan o embeben en distintos niveles unos dentro de otros...

A partir del código anterior, puesto que queremos recuperar las provincias, emplearemos la etiqueta <label> como ya comentamos. En A4 insertamos: =XMLFILTRO(A2;"//label")
Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF

Fíjate en la forma del segundo argumento:
'//label'
donde indicamos busque de forma absoluta cualquier label... Si existieran otras 'label' dentro de otros niveles, necesitaríamos ser más precisos con la secuencia, por ejemplo:
=XMLFILTRO(A2;"//items/item/label")

Otra posibilidad que nos ofrece la función XMLFILTRO es recuperar el atributo de una etiqueta. Muy interesante!.
Supongamos tenemos esta URL en A1:
https://datos.gob.es/apidata/catalog/theme.xml?_sort=notation&_pageSize=100&_page=0
Al aplicar sobre ella la función SERVICIOWEB en A2:
=SERVICIOWEB(A1)
y sobre A2, en la celda A4 la función XMLFILTRO con la siguiente forma:
=XMLFILTRO(A2;"//items/item[1]/prefLabel/item/@lang")
Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF

Con el formato de ruta:
'//items/item[1]/prefLabel/item/@lang'
conseguimos referirnos exclusivamente a la primera etiqueta de <item> dentro de la etiqueta general de <items>
Además para identificar el atributo 'lang' emplearemos la arroba @
@lang
listando por tanto solo los idiomas contenidos en nuestro 'item'.

Por tanto, no solo recuperamos un atributo de etiqueta, si no que personalizamos de qué etiqueta en concreto!!.

Si quisieramos recuperar información de las direcciones web contenidas en nuestro anterior código XML de la URL:
https://datos.gob.es/apidata/catalog/theme.xml?_sort=notation&_pageSize=100&_page=0
nuestra fórmula en A4 podría ser:
=XMLFILTRO(A2;"//items/item/@href")
Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF

Donde comprobamos como el resultado es el listado de los atributos 'href' de las eiquetas de 'item'.

Si solo necesitamos la dirección web del segundo item, de 'Comercio', nuestra fórmula en A4 sería:
=XMLFILTRO(A2;"//items/item[2]/@href")
Funciones Web: SERVICIOWEB, XMLFILTRO y URLCODIF


Y un truco sorprendente para recuperar la última posición es emplear entre corchetes la instrucción [last](). Por ejemplo:
=XMLFILTRO(A2;"//items/item[last()]/@href")
o la penúltima posición:
=XMLFILTRO(A2;"//items/item[last()-1]/@href")

Por último hablaremos brevemente de la función URLCODIF que retornará cadenas de texto codificada como exigen los navegadores, convirtiendo todo tipo de caracteres no válidos a caracteres legibles y listos para usar por las URL.
Esto nos permite dinamizar algunas partes de nuestras URL.
Por ejemplo en A1 escribimos un número. Y en A2 escribimos:
=SERVICIOWEB("https://datos.gob.es/apidata/catalog/theme.xml?_sort=notation&_pageSize="&URLCODIF(A1)&"&_page=0")
Esto permite 'parametrizar' nuestra búsqueda sin peligro de error al usar caracteres no válidos.
Es por esto que recomiendo usar la función URLCODIF junto con la función SERVICIOWEB, lo que garantiza el uso de un URL válido en el momento de acceder cualquier servicio web.

martes, 19 de enero de 2021

Búsqueda a la izquierda

Una cuestión recurrente a lo largo de los años es cómo aplicar un BUSCARV a la inversa, i.e., recuperando datos a la izquierda de la columna donde buscamos.
Veremos algunas alternativas interesantes con empleo de funciones en Excel.
Búsqueda a la izquierda


Apliquemos algunas alternativas sobre la tabla de la imagen anterior...
Sabiendo que a F3 le hemos asignado el nombre definido 'vBuscado', y que la Tabla ('TblUDS') tiene tres campos: uds Ventas pais.

Las dos primeras opciones aplicarán si el dato a recuperar es numérico.
Así en G3 escribiríamos:
=SUMAPRODUCTO((TblUDS[pais]=vBuscado)*(TblUDS[uds]))
O también en G4:
=SUMAR.SI(TblUDS[pais];vBuscado;TblUDS[uds])
donde, en ambos casos, condicionamos la suma del campo UDS a la coincidencia del valor buscado en el campo PAIS.
Rápido y simple.. con la limitación comentada. El dato a recuperar debe ser numérico, i.e., algo que se pueda 'sumar/acumular'.

Otras opciones validas para cualquier dato (texto, número o fecha.
En G6 insertamos una clásica búsqueda con INDICE y COINCIDIR:
=INDICE(TblUDS[uds];COINCIDIR(vBuscado;TblUDS[pais];0))
O en G7 donde añadimos un comportamiento matricial interesante (leer algo más aquí)
=BUSCARV(vBuscado;SI({1\0};TblUDS[pais];TblUDS[uds]);2;0)
O de manera similar en G8:
=BUSCARV(vBuscado;ELEGIR({1\2};TblUDS[pais];TblUDS[uds]);2;0)
En estos dos últimos casos, vemos como empleando BUSCARV, apoyándonos en las constantes matriciales {1\2} o {1\0}, reconstruimos una matriz de búsqueda reordenada por columnas, para poder aplicar el orden requerido por BUSCARV!!.

Para usuarios de versiones 'modernas' que disponga de la función BUSCARX y fórmulas desbordadas tenemos estos casos.
En G9 insertamos:
=FILTRAR(TblUDS[uds];TblUDS[pais]=vBuscado)
válido si existe una única coincidencia para el valor buscado...
O en G10:
=BUSCARX(vBuscado;TblUDS[pais];TblUDS[uds])
donde BUSCARX, en su forma más simple, permite localizar coincidencias... sin requisitos de ordenación ni de columnas ni de filas.

Dejo para el final el uso de la función BUSCAR. Un función clásica.
En G12 podríamos añadir:
=BUSCAR(vBuscado;TblUDS[pais];TblUDS[uds])
OJO, ya que esta función exigiría que el vector de PAIS estuviera ordenado en modo ASCendente!!!.

¿Conoces alguna alternativa a las vistas?

jueves, 14 de enero de 2021

Nueva función HISTORIALCOTIZACIONES

Recientemente se ha lanzado para todo el mundo (usuarios de Microsoft 365, claro) una nueva función desbordada: HISTORIALCOTIZACIONES:
=HISTORIALCOTIZACIONES (cotizaciones, fecha_inicial, [fecha_final], [intervalo], [encabezados], [propiedad0], [propiedad1], [propiedad2], [propiedad3], [propiedad4], [propiedad5])
Esta nueva función es capaz de devolvernos información financiera de algunos de los principales mercados bursátiles, así como de tipos de cambio entre distintas monedas...
OJO!! toda la información suministrada de la empresa (datos históricos, los precios en tiempo real y retrasados, el resumen de fondos y los datos de rendimiento) son proporcionados por Refinitiv, según un contrato de suministro de información firmado con Microsoft!!!.
De acuerdo a ese contrato, los datos mostrados no son para uso profesional de la industria financiera ni uso por otros profesionales de empresas no financieras (incluidas las entidades gubernamentales) que desempeñan funciones, entre las que se incluyen: finanzas, comercio, inversión, cobertura, cumplimiento, gestión de riesgos, fusiones y adquisiciones, tesorería, investigación económica y financiera, estrategia y planificación financiera y económica, consultoría financiera y de gestión, procesamiento de pagos, leasing y financiamiento.

Algo obvio de esta función es que se necesita una constante conexión a Internet.
Esta función está relacionada en cuanto a su comportamiento con el tipo de dato enriquecido de 'Cotizaciones' (ver aquí).
Otro punto importante antes de menternos en faena sería mostrar qué mercados están entre la lista de mercados bursátiles disponibles:
País Código de país Código de identificación de mercado Nombre de la bolsa de valores Zona horaria Desviación UTC Retraso (minutos)
Argentina AR BCBA Bolsa de comercio de Buenos Aires ART -3 30
Australia AU XASX Mercado de valores de Australia AEST 10 20
Austria AT XWBO Bolsa de valores de Viena CET 1 15
Bélgica BE XBRU Euronext Bruselas CET 1 15
Brasil BR BVMF Bolsa de valores de Sao Paulo BRT -3 30
Canadá CA XCNQ Bolsa de valores de Canadá EST -5 15
Canadá CA XTSE Bolsa de valores de Toronto EST -5 20
Canadá CA XTSX Bolsa de valores Venture Exchange EST -5 20
Chile CL XSGO Bolsa de valores de Santiago CLT -4 20
China CN XSHE Bolsa de Shenzhen CST 8 30
Dinamarca DK XCSE Nasdaq Copenhague CET 1 15
Estonia EE XTAL Nasdaq Tallin EET 2 15
Finlandia FI XHEL Nasdaq Helsinki EET 2 15
Francia FR XPAR Euronext París CET 1 15
Alemania DE XFRA Deutsche Boerse CET 1 15
Alemania DE XETR Xetra CET 1 15
Hong Kong HK XHKG Bolsa de valores de Hong Kong HKT 8 15
Islandia FT XICE Nasdaq Islandia GMT 0 15
India IN XBOM Bolsa de Bombay IST 5,5 15
India IN XNSE Bolsa nacional de India IST 5,5 5
Indonesia ID XIDX Bolsa de Indonesia WIB 7 15
Irlanda IE XDUB Euronext Dublin WET 0 15
Italia IT XMIL Bolsa de Italia CET 1 15
Corea del Sur KR XKRX Bolsa de valores de Korea KST 9 20
Letonia LV XRIS Nasdaq Riga EET 2 15
Lituania LT XLIT Nasdaq Vilnius EET 2 15
México MX XMEX Bolsa de México CST -6 20
Nueva Zelanda NZ XNZE New Zealand Exchange Ltd NZST 12 20
Noruega NO XOSL Bolsa de Oslo CET 1 15
Filipinas PH XPHS Bolsa de valores Filipinas PHT 8 15
Polonia PL XWAR Bolsa de valores Varsovia CET 1 Final del día
Portugal PT XLIS Euronext Lisboa WET 0 15
Rumania RO XBSE Bolsa de valores Bucarest EET 2 15
Rusia RU MISX Bolsa de Moscú MSK 3 15
Sudáfrica ZA XJSE Bolsa de Johannesburgo  SAST 2 30
España ES BMEX Bolsas y Mercados Españoles CET 1 15
Suecia SE XSTO Nasdaq Estocolmo CET 1 15
Suiza CH XSWX Bolsa de Suiza CET 1 15
Taiwán TW XTAI Bolsa de Taiwán CST 8 20
Países Bajos NL XAMS Euronext Ámsterdam CET 1 15
Ucrania UA XUAX Bolsa de valores de Ucrania EET 2 Final del día
Reino Unido GB XLON Bolsa de Londres WET 0 15
Estados Unidos de América EE.UU. BATS Cboe BZX Exchange EST -5 0
Estados Unidos de América EE.UU. XNASB Nasdaq última venta EST -5 0
Estados Unidos de América EE.UU. XNAS Mercado de acciones Nasdaq EST -5 15
Estados Unidos de América EE.UU. XNYS Bolsa de Nueva York EST -5 15
Estados Unidos de América EE.UU. ARCX NYSE Arca EST -5 15
Estados Unidos de América EE.UU. OTCM Mercado extrabursatil EST -5 15
Vietnam VN XSTC Bolsa de valores de Ho Chi Minh ICT 7 15
Como se observa los retrasos en los datos mostrados respecto a las cotizaciones en tiempo real oscilan entre los 5 y 30 minutos, excepto datos NASDAQ en los que no existe retraso alguno...
Especialmente importante de la tabla anterior es el campo 'Código de identificación de mercado', con el que podremos ajustar el mercado del cual recuperar información sobre una empresa cotizada.

Comentados los puntos anteriores, fundamentales para un correcto uso, veamos algo más de la función HISTORIALCOTIZACIONES.
Como se veía al inicio del post, esta función tiene bastantes argumentos.. pero todos muy simples:
=HISTORIALCOTIZACIONES (cotizaciones, fecha_inicial, [fecha_final], [intervalo], [encabezados], [propiedad0], [propiedad1], [propiedad2], [propiedad3], [propiedad4], [propiedad5])
- argumento 'cotizaciones': Será el valor del cual se devuelve datos históricos de precios del instrumento financiero. Recomendado detallarlo con un código de identificación de mercado (MIC) ISO de 4 caracteres (ver tabla previa!), seguido de dos puntos, seguido del código bursátil (por ejemplo, "XNAS:MSFT").
- argumentos 'fecha_inicial' y 'fecha_final': La primera y última fecha a partir de la cual se recuperan los datos.
- argumento 'intervalo': indicamos el intervalo que representa cada valor de datos. Con opciones: 0 = diario, 1 = semanal, 2 = mensual.
- argumento 'encabezados': Especifica si se muestran los encabezados de la siguiente forma: 0 = sin encabezados, 1 = mostrar encabezados, 2 = mostrar identificador del instrumento y encabezados.
- argumentos 'propiedades' seís opciones: 0 = Fecha, 1 = Cierre, 2 = Apertura, 3 = Máximo, 4 = Mínimo y 5 = Volumen.
Entendiendo cada propiedad según lo siguiente:
Fecha: Primer día hábil bursátil del período
Cierre: Precio de cierre del último día hábil bursátil del período
Apertura: Precio de apertura del último día hábil bursátil del período
Máximo: Mayor precio del mayor de los máximos diarios del período
Mínimo: Menor precio del menor de los mínimos diarios del período
Volumen: Volumen negociado durante el periodo

Ya estamos en disposición de plantear un primer y sencillo ejemplo.
Supongamos queremos disponer del histórico de cotizaciones del Banco Santander en la Bolsa de Madrid en todo el 2020, dispondremos en A1 el código: BMEX:SAN (comprueba que BMEX responde a las bolsas españolas, y SAN es el código bursatil del banco).
En B1 y C1 indicamos las fechas de inicio y fin del detalle deseado.
Así pues en A3 escribiríamos:
=HISTORIALCOTIZACIONES(A1;B1;C1;0;2;0;1;2;3;4;5)
donde indicamos que del valor BMEX:SAN entre las fechas del 1/1/2020 y 31/12/2020 queremos ver con un intervalo diario, mostrando el identificador del valor y los encabezados, así como todas las propiedades habilitadas.
Nueva función HISTORIALCOTIZACIONES

Vemos que al tratarse de una función desbordada, automáticamente se completan todas las filas y columnas necesarias. Seguramente, durante un instante, en la celdas formuladas haya aparecido un mensaje de '#OCUPADO!' que indica que se está actualizando la conexión...
Para construir algo más aplicable al día a día podemos aprovecharnos de otras funciones (clásicas y/o desbordadas). Por ejemplo, si necesito el dato de los últimos dos meses desde la fecha corriente, en C1 añadiríamos la función =HOY() y en B1:=FECHA.MES(C1;-2), manteniendo en A3 la anterior función =HISTORIALCOTIZACIONES(A1;B1;C1;0;2;0;1;2;3;4;5)
Sobre ese rango desbordado podríamos incluir o insertar algún gráfico, por ejemplo de 'líneas', o 'gráficos y bigotes' o incluso el especifico gráfico del tipo 'cotizaciones'... o siendo imaginativos un 'minigráfico' ;-)

A parte de simples listados podemos montar informes donde se refleje información de una cartera de inversión.
Por ejemplo, supongamos tenemos una cartera de cinco valores:
1- Bankinter (BMEX:BKT)- banco
2- Acciona (BMEX:ANA) - constructora
3- AENA (BMEX:AENA) - gestión aeropuertos
4- Repsol (BMEX:REP) - petrolera
5- Pharma Mar (BMEX:PHM) - farmacéutica
De todos ellos queremos conocer su situación a cierre del año: 31/12/2020... Entonces tendríamos:
Nueva función HISTORIALCOTIZACIONES

Vemos que en C3 hemos añadido la fórmula: =HISTORIALCOTIZACIONES($B3:$B7;B1;;0;0;{1\2\3\4\5})

Donde indicamos que de los valores indicados en B3:B7 mostrando los datos del día indicado en B1, sin mostrar encabezados y mostrando las propiedades de Cierre, Apertura, Alto, Bajo, Volumen , lo que conseguimos con la constante matricial: {1\2\3\4\5}
Otro ejemplo, sobre nuestra cartera de valores, podría ser la siguiente:
Nueva función HISTORIALCOTIZACIONES

En este caso con la fórmula en C3
=SI.ERROR(HISTORIALCOTIZACIONES(C2:G2;B3#;;0;0;1);"-")
recuperamos los datos diarios de cierre de las distintas fechas listadas en B3:B9 (con la función: =SECUENCIA(7;1;HOY();-1)) sin encabezados.
He anidado en SI.ERROR ya que en los días sin cotización (festivos y fines de semana) se obtiene un error de #¡VALOR!.

Como se puede comprobar la función es muy versatil, y permite trabajar en diferentes dimensiones...
Pero no solo nos permite 'atacar' información bursatil tal cual, otro aspecto importante es el de la cotización del tipo de cambio entre monedas.
En el siguiente ejemplo veremos los tipos de cambios cruzados entre GBP, USD y EUR de los últimos quince días.
Nueva función HISTORIALCOTIZACIONES

Aquí en C2 añadimos la función desbordada:
=SECUENCIA(1;15;HOY();-1)
con la que conseguimos los últimos quince días desde la fecha actual.
En B3:B8 escribimos los cruces (directos e inversos) de las tres monedas a analizar:
USD/GBP
GBP/USD
USD/EUR
EUR/USD
EUR/GBP
GBP/EUR
y finalmente en C3 añadimos nuestra fórmula:
=SI.ERROR(HISTORIALCOTIZACIONES($B$3:$B$8;C2#;;0;0;1);"-")
donde pedimos que para cada tipo de cambio de las celdas B3:B8 y cada fecha de C2:Q2 muestre el dato del cierre del día para dicho tipo de cambio.
Al igual que en el caso anterior anidamos con SI.ERROR para evitar el fallo en los días no hábiles.

Incluir un sencillo minigráfico de tipo línea al informe aportará alg omás de valor a éste.

Comentábamos al inicio del post que esta función HISTORIALCOTIZACIONES es similar al tipo de dato enriquecido de 'Cotizaciones', si bien las opciones que ofrece el tipo enriquecido son bastantes más, al menos en cuanto a número...