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:
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...
=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 |
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...
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
