Anexar rangos de celdas con funciones WEB

Un par de entradas atrás hablamos sobre las funciones Web de Excel, y en concreto sobre XMLFILTRO. Una función muy interesante sobre la que hoy daremos una nueva vuelta para conseguir anexar distintos rangos de celdas!!.
Partiremos de tres rangos, convertidos en tablas ('Tbl_2020','Tbl_2021' y 'Tbl_2022') para mayor facilidad

El siguiente ejercicio se basa en el post previo (ver aquí) donde explicábamos como generar en HTML el código de una tabla. Ya que es posible crear una única tabla en código HTML a partir de distintos rangos...
Por ejemplo, en F2 insertamos la fórmula:
="<datos><celda>"&UNIRCADENAS("</celda><celda>";FALSO;Tbl_2020;Tbl_2021;Tbl_2022)&"</celda></datos>"
donde nos hemos inventado las etiquetas 'datos' y 'celda', ya que para nuestro ejercicio no es necesario un código real...
Además ni siquiera necesitamos separar filas de columnas, basta con crear una 'tabla' en HTML o XML de una sola columna... lo que hemos conseguido con la fórmula anterior.
El motivo de esto es que al aplciar en los siguientes pasos la función Web XMLFILTRO en todo caso se generará un vector de elementos!!.
Lo que es fácil de comprobar si en F5 escribimos:
=XMLFILTRO(F2;"//datos/celda")

A partir de esta tabla en HTML o XML, en todo caso, generada con un patrón de etiquetas, podremos crear un rango de las filas y columnas necesarias, para nuestro ejemplo: 13 filas y 3 columnas.
Aprovechándonos de las nuevas fórmulas desbordadas, y en particular de SECUENCIA podremos escribir en H5 la fórmula anteriormente comentada:
=INDICE(XMLFILTRO(F2;"//datos/celda");SECUENCIA(13;3))
Donde INDICE recorre cada fila y columna de nuestra tabla XML creada con las etiquetas datos y celda, devolviendo cada dato de 'celda' y siendo reordenado por SECUENCIA en la posición adecuada!!.

Por supuesto nuevos registros en cualquiera de las tres tablas se anexarán adecuadamente en su posición... siempre y cuando dinamicemos el argumento 'num_filas' de la función SECUENCIA (primer argumento) que debe reflejar siempre el número total de filas de las tres tablas...
Por ejemplo con un sencillo:
FILAS(Tbl_2020)+FILAS(Tbl_2021)+FILAS(Tbl_2022)
Que dejaría nuestra fórmula en H5:
=INDICE(XMLFILTRO(F2;"//datos/celda");SECUENCIA(FILAS(Tbl_2020)+FILAS(Tbl_2021)+FILAS(Tbl_2022);3))
De forma similar para el número de columnas: COLUMNAS(Tbl_2020)
Si bien este caso es algo más delicado, ya que exige que las tres tablas tengan el mismo número de columnas y con igual estructura de campos!!.

Seguir leyendo...

Crear tabla en HTML desde rango de celdas de Excel

Hoy mostraré una forma sencilla de convertir un rango de celdas de Excel a formato de tabla en HTML.
Muy frecuentemente, cuando escribo artículos o post como este, me veo en la necesidad de mostrar información en formato tabla en HTML; lo que suele ser una tarea tediosa...

Veremos como una sencilla fórmula me permite generar el código HTML necesario para poder pasar de Excel a la web o blog en cuestión...
Sin entrar en detalles del lenguaje, es necesario que reconozcamos cuáles son las etiqueta HTML necesarias para crear nuestra tabla:
- En primer lugar la etiqueta <table> es la que define una tabla en HTML.
- Cada fila de una tabla se define por la etiqueta <tr>
- Adicionalmente, si queremos incluir encabezados, emplearemos la etiqueta <th>
- Cada 'celda' o dato de la tabla la incluiremos dentro de una etiqueta <td>

Saber además que el texto de los encabezados, dentro de <th> aparecerán centrados y en negrita
Y que, de otro lado, el texto de cada celda o dato dentro de <td> aparecerá alineada a la izquierda y sin formato de fuente.
Por ejemplo, un código sencillo para una tabla en HTML sería:

    

  

    
Nombre
    
Apellido
    
Edad
  
  

    
Juan
    
González
    
25
  
  

    
Isabel
    
Romero
    
19
  

Y se vería:

Nombre	Apellido	Edad
Juan	González	25
Isabel	Romero	19

Adicionalmente se podrían añadir atributos a cada etiqueta; por ejemplo, incluir un borde a la tabla empleando el atributo 'border':

    

  

    
Nombre
    
Apellido
    
Edad
  
  

    
Juan
    
González
    
25
  

Nombre	Apellido	Edad
Juan	González	25

Pues este es nuestro objetivo... generar ese código HTML con fórmulas a partir de nuestro rango de celdas...
Veamos el ejemplo a partir de nuestro rango de celdas B2:D5:

En la celda B8 insertamos la fórmula:
="<table>"& UNIRCADENAS("";FALSO;ELEGIR({1\2\3\4\5\6\7};"<tr><th>";B2;"</th><th>";C2;"</th><th>";D2;"</th></tr>"))& UNIRCADENAS("";FALSO;ELEGIR({1\2\3\4\5\6\7};"<tr><td>";TEXTO(B3:B5;"dd/mm/aaaa");"</td><td>";C3:C5;"</td><td>";D3:D5;"</td></tr>"))& "</table>"
Quizá algo larga, pero es porque he decidido separar Encabezados y Datos, de ahí las dos líneas de la función UNIRCADENAS.
La primera línea:
UNIRCADENAS("";FALSO;ELEGIR({1\2\3\4\5\6\7};"<tr><th>";B2;"</th><th>";C2;"</th><th>";D2;"</th></tr>"))
concatena la celdas del encabezado B2,C2,D2 alternando las etiquetas necesarias tr - th.
Esta primera se puede simplificar con un simple CONCAT, ya que el encabezado son solo tres celdas...
La segunda función UNIRCADENAS permite generar el 'cuerpo' de la tabla independientemente de cuantas filas existan!!
UNIRCADENAS("";FALSO;ELEGIR({1\2\3\4\5\6\7};"<tr><td>";TEXTO(B3:B5;"dd/mm/aaaa");"</td><td>";C3:C5;"</td><td>";D3:D5;"</td></tr>"))
Esta fórmula nos permite concatenar en el orden adecuado las etiquetas de filas y datos tr - td cada una de las filas de nuestro rango!!.
La matriz de constantes {1\2\3\4\5\6\7} sirve para 'unir' los siete tramos de textos involucrados en la fórmula..

Finalmente completamos la cadena de código HTML entre el inicio y fin de la etiqueta table.
La función ELEGIR se encarga de gestionar adecuadamente el orden de unión de las distintas columnas de nuestro rango.

Si copiamos y pegamos el resultado de nuestra fórmula en nuestro blog o web veríamos:

Fecha Nacimiento	Nombre	Nota Media
11/04/1971	Daniel Dominguez	8
02/04/1978	Ana Alonso	9
11/06/1997	Beatriz Bermudez	7

Listo... Recuerda el atributo 'border' si quieres añadir bordes a las 'celdas'.
Espero te sea útil!!

Seguir leyendo...

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:

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

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

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

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

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.

Seguir leyendo...

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.

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?

Seguir leyendo...

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.

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:

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:

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.

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...

Seguir leyendo...

Power Query: Trabajar con Records

Deberíamos empezar por definir, lo mejor posible este concepto tan amplio y vago, un Record es un conjunto de valores... pero es mucho más.
Incluso podríamos categorizarlos en dos grandes conjuntos:
- originales: esto es, sin ningún tratamiento, datos puros (aaa, 123, 12/1/21, FALSE, etc)
- elaborados: construidos a partir de estos originales, o incluso otros previamente elaborados
Lo más importante es que cualquier Record se debe escribir entre corchetes [ ... ] (si revisas la entrada donde explcamos cómo crear tablas verás varios ejemplos de Records).
Por ejemplo: [País="ES", Importe=400]

¿Por qué es importante aprender algo más de un Record?.. bueno, básicamente por dos motivos:
1- acceder o trabajar con filas de nuestras tablas
2- para pasarlo como parámetro a una función M
y muchos más, claro :-)

Yendo al 'meollo' del asunto.. ¿cómo podemos crear Records?
Como mencioné antes, siempre detallando las partes del registro entre corchetes [...].
Un ejemplo más elaborado que el previo sería el siguiente, donde podemos ver que un registro/Record admite diferentes formatos de dato. Escribimos en nuestro editor avanzado dentro de Power Query:

[Nombre= "Ismael",
Ciudad_País="Madrid - España",
Antigüedad=10,
Activo= true,
Ranking dos años={2,5},
Ventas=[a20=1200,a19=1100,a18=900],
Comerciales=#table({"Región", "Comercial"},{{"ES","Juan"},{"PT","Ana"}}),
Cálculo= (IPC)=>1+IPC]

Vemos el aspecto de nuestro registro/Record

Comprobamos como cada parte de nuestro registro contiene un tipo de dato distinto: Table, Function, List, texto, número, fecha...

Otra forma de crear registros/Records es empleando funciones M. Dos de las más relevantes:
- Record.FromList(list as list, fields as any) as record
que crea un record a partir de una lista de valores y una lista de nombres. Incluso podemos definir el tipo de dato asociado a cada campo:
Record.FromList({1, "ES", 123}, type [ID = number, País = text, Ventas = number])
- Record.FromTable(table as table) as record
de forma similar, a partir de una tabla, construimos un registro.

Existe un mundo de funciones M nativas vinculadas con el uso de los registros; de hecho tienen su propia categoría.

Finalmente, un uso interesante de los registros es obtener un Record de la fila de una tabla
, sencillamente refiriéndonos a una de las filas de nuestra tabla (recuerda que empleamos las llaves {} para esto - y que la numeración tiene base cero).
Table{número fila en base 0}
Por ejemplo

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
        [País="ES",Importe=20],
        [País="PT",Importe=300],
        [País="PT",Importe=4],
        [País="FR",Importe=50]},
        type table[País = Text.Type, Importe = Currency.Type]){1} //obtenemos la segunda fila de la Tabla!
in
    Origen

Dato interesante!!. Para referirnos a la fila corriente, al crear una columna personalizada, empleamos el guión bajo '_', que equivale de alguna manera al registro correspondiente.
Por ejemplo, para sumar datos de dos columnas empleando este truco, podríamos hacer:

let
    Origen = #table({"Año 2020","Año 2019"},{{100,200},{110,190},{150,150}}),
    Add_Suma = Table.AddColumn(Origen, "Total Ventas", each List.Sum(Record.ToList(_)))
in
    Add_Suma

En este caso nos hemos referido a todos los elementos de la fila corriente, esto es, del registro... el cual, con Record.ToList convertimos en una 'lista' para luego poder operar sobre todos sus elementos con List.Sum.

Un último apunte que nos servirá para recuperar un elemento de nuestro registro.
Para obtener un dato concreto bastará referirnos a la columna deseada, indicándola entre corchetes []. Por ejemplo, del registro:
[País="ES", UDS=100, Ventas=2568]

para recuperar las Unidades, nos referiremos a ese campo, como:
[País="ES", UDS=100, Ventas=2568][UDS]

Alternativamente podríamos hacer uso de la función M:
Record.SelectFields(record as record, fields as any, optional missingField as nullable number) as record
que nos devuelve un registro solo con el/los campos indicados!.
Por ejemplo

let
    MiRegistro=[País="ES", UDS=100, Ventas=2568],
    Calc=Record.SelectFields(MiRegistro,{"UDS","Ventas"})
in
    Calc

O también para recuperar el registro completo...

let
    MiRegistro=[País="ES", UDS=100, Ventas=2568],
    // o haciendo uso de Record.FieldNames para todos los campos
    Calc=Record.SelectFields(MiRegistro,Record.FieldNames(MiRegistro))
in
    Calc

Y esta podría ser una introducción al fascinante mundo de los registros en Power Query ;-)

Seguir leyendo...

Power Query: Recuperando filas o columnas de una tabla

Muy frecuentemente se hace necesario tener que trabajar con elementos concretos de una tabla:
- una columna concreta
- una fila en particular
- o un dato (celda) única
Esto es posible conociendo dos sencillos caracteres:
- corchetes [ ]
- llaves { }

Debememos saber que en general los corchetes [ ] sirven para identificar columnas de una tabla (o Lista), y el resultado se recuperará en modo Lista!!.
Deberemos referirnos en modo: Nombre_Tabla[Nombre_columna]
De forma similar respecto a la forma de recuperar una fila de una tabla, que usaremos las llaves { }, retornándonos un 'Record' (registro!).
Sabiendo que la numeración u ordenación en Powew Query se basa en un sistema de base 0, esto es, el primer elemento será siempre el cero, podemos obtener una fila en modo: Nombre_Tabla{número índice de la fila}.
Igualmente, empleando los dos conceptos anteriores, podemos recuperar un dato (una celda concreta) de una tabla, en modo: Nombre_Tabla[Nombre_columna]{número índice de la fila}

Veamos los siguientes ejemplos...
Para obtener los valores de una columna:

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
                    [País="ES",Importe=20],
                    [País="PT",Importe=300],
                    [País="PT",Importe=4],
                    [País="FR",Importe=50]},
                    type table[País = Text.Type, Importe = Currency.Type]),
    //obtenemos una Lista de los elementos de la columna País
    Personalizado1 = Origen[País]
in
    Personalizado1

recuperamos una 'Lista' de los elementos de la columna 'País'

Igualmente efectivo (y recomendado) y obvio con igual resultado podríamos haber escrito:

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
        [País="ES",Importe=20],
        [País="PT",Importe=300],
        [País="PT",Importe=4],
        [País="FR",Importe=50]},
        type table[País = Text.Type, Importe = Currency.Type])[País] //obtenemos una Lista de los elementos de la columna País
in
    Origen

Para recuperar una fila (un Record de la tabla), podríamos escribir:

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
        [País="ES",Importe=20],
        [País="PT",Importe=300],
        [País="PT",Importe=4],
        [País="FR",Importe=50]},
        type table[País = Text.Type, Importe = Currency.Type]){1} //obtenemos la segunda fila de la Tabla!
in
    Origen

Y finalmente para conseguir el dato concreto a una columna y una fila aplicaremos:

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
        [País="ES",Importe=20],
        [País="PT",Importe=300],
        [País="PT",Importe=4],
        [País="FR",Importe=50]},
        type table[País = Text.Type, Importe = Currency.Type])[País]{1}
in
    Origen

En este caso, como curiosidad, comprobamos como la consulta cambia el icono de Tabla a 'Valor'.

Estos ejemplos nos sirven si queremos trabajar sobre una única columna o fila... en caso que queramos operar sobre distintas columnas o filas deberemos emplear funciones M ya vistas en el blog:
Table.SelectRows(table as table, condition as function) as table
devuelve una tabla con las filas que cumplan la condición marcada...
Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table
con la que obtenemos una tabla solo con las columnas indicadas (en modo lista!)

Seguir leyendo...

Power Query: Creando tablas en el editor de consultas

A estas alturas todos conocemos diferentes alternativas para importar datos a nuestro editor de Power Query y crear las consultas oportunas:
- desde un libro
- desde unan carpeta
- desde un pdf
- desde un csv o txt
- desde una web
- ...
- desde una tabla o rango de nuestro libro de trabajo

Pero a veces no nos interesa o simplemente no podemos recuperar la información de ningún sitio y optaremos por crear una tabla dentro del editor de Power Query.
En otras ocasiones se hace necesario componer tablas o listas dentro de nuestro código M para facilitar el proceso de nuestra consulta... como veremos más adelante.

Hay muchas maneras de crear una Tabla desde la nada y poder disponer de ella posteriormete...
Una primera sería emplear el botón de Espeficicar datos dentro del menú Inicio > grupo Nueva consulta del editor de consultas.

Esto nos abrirá una ventana donde o bien escribir nuestros datos o bien pegar de algún otro lugar

Al finalizar nos genera una línea de código algo rara y difícil de leer :D

A partir de este punto podremos continuar y hacer exactamente lo mismo que con cualquier otra tabla cargada de la forma 'habitual'...

Una segunda manera sería recurrir al lenguaje M y emplear alguna de las funciones dentro de Power Query que permiten generar tablas:
- Table.FromColumns()
- Table.FromValue()
- Table.FromRows()
- Record.ToTable()
y sobre todo
- Table.FromList()
- Table.FromRecords()
En concreto la más usada:
Table.FromRecords(records as list, optional columns as any, optional missingField as nullable number) as table
nos permite completar una tabla con las columnas y filas que necesitemos, incluos definir el tipo de dato para cada columna...

let
    Origen = Table.FromRecords({[País="ES",Importe=1],
                    [País="ES",Importe=20],
                    [País="PT",Importe=300],
                    [País="PT",Importe=4],
                    [País="FR",Importe=50]},
                    type table[País = Text.Type, Importe = Currency.Type])
in
    Origen

Con esta función podemos incluir tantas filas y columnas como necesitemos con una estructura muy clara, entre corchetes cada fila nueva, y en cada fila identificando 'Nombre columna' y 'valor'.

Una tercera alternativa (y última de la que hablaremos hoy, es emplear la función #table(...), con la cual podemos generar tablas de una manera algo más rápida que en los casos anteriores...
Por ejemplo, podríamos escribir:

let
    Origen =  #table(type table [País=text,Dato=number],{{"ES",2},{"PT",40},{"ES",100},{"ES",50},{"PT",20}})
in
    Origen

Con esta forma de trabajar podremos montar nuestra tabla con filas y columnas, e incluso poder definir el tipo de dato de esas columnas!!.
Una variante, sin necesidad de definir el tipo de dato (más sencilla):

let
    Origen =  #table({"País","Dato"},{{"ES",2},{"PT",40},{"ES",100},{"ES",50},{"PT",20}})
in
    Origen

Fíjate en las columnas y los tipos de datos generales asociados.

Una última variante de esta función M sería generar la tabla sin nombres de columnas, esto es, con nombres genéricos Column1, Column2, etc...

let
    Origen =  #table(2,{{"ES",2},{"PT",40},{"ES",100},{"ES",50},{"PT",20}})
in
    Origen

Con el primer argumento: 2 , indicamos el número de columnas a insertar.

Estas son mis tres formas de crear tablas de la nada... aunque insisto hay algunas más.

Pero no quiero cerrar este artículo sin mencionar un operador que me permite combinar tablas existentes, es el operador & (nuestro ampersand de siempre...el operador de unión).
Obviamente equivaldría a la función Table.Combine.
Veamos un ejemplo...

let
    Origen =   #table({"País","Budget 2020"}, {{"ES",2},{"DE",3}}) & #table({"País","Actual 2020"}, {{"ES",4},{"DE",5}})
in
    Origen

Igualmente podríamos haber escrito:

let
    Origen =    Table.Combine({#table({"País","Budget 2020"}, {{"ES",2},{"DE",3}}) , #table({"País","Actual 2020"}, {{"ES",4},{"DE",5}})})
in
    Origen

OJO con las llaves al inicio y final {} !!.

Seguir leyendo...