Cotizaciones históricas de acciones con STOCKHISTORY

Digamos que queremos recuperar cotizaciones bursátiles históricas para un valor específico en Excel. Como se trata de Excel, existen, por supuesto, varias opciones. Antes de la función STOCKHISTORY, podíamos extraer datos de páginas web relevantes usando Power Query (sobre el cual he escrito antes aquí ). Pero, las versiones más actuales de Excel ahora cuentan con otra opción: la función HISTORIAL DE EXISTENCIAS. Vamos a ver.

Objetivo

Antes de llegar demasiado lejos, primero comprendamos nuestro objetivo aquí. Queremos poder ingresar un símbolo de cotización, una fecha de inicio y una fecha de finalización en algunas celdas, como esta:

Luego, nos gustaría que Excel recupere automáticamente los valores de cierre, así:

Finalmente, nos gustaría que todos esos datos se incluyan en un gráfico, como este:

Y, cada vez que escribimos un nuevo Ticker, Fecha de inicio o Fecha de finalización, nos gustaría que todo se actualice automáticamente

Nota: Al momento de escribir este artículo, Microsoft está en el proceso de implementar la función HISTORIAL DE EXISTENCIAS para los suscriptores de Excel 365 (Windows, Mac y Web). Dependiendo de tu canal de actualización 365, es posible que tengas o no acceso a él todavía.

Video

Detalles

Recorreremos el proceso siguiendo estos pasos:

• Tipo de datos de acciones
• HISTORIAL DE STOCK
• Grafico

Bueno, ¡hagamos esto!

Tipo de datos de acciones

Lo primero que queremos hacer es convertir nuestra celda de entrada de teletipo a un tipo de datos de acciones. ¿Qué es eso? He escrito sobre el tipo de datos de acciones antes, aquí , pero en resumen, le decimos a Excel que almacene ese símbolo de cotización como una acción en lugar de una cadena de texto. Una vez que lo hagamos, Excel recuperará datos enriquecidos sobre ese ticker a través de un proveedor de datos externo, Refinitiv.

Para hacer esto, seleccionamos la celda de entrada del ticker:

A continuación hacemos clic en el comando Datos Acciones . Excel convierte el valor de texto MSFT en Stock. Puede reconocerlo por el ícono y cómo Excel muestra automáticamente el nombre de la empresa correspondiente, así:

Nuestro siguiente paso es recuperar los precios de cierre históricos con la función STOCKHISTORY.

HISTORIAL DE STOCK

En general, cuando proporcionamos un ticker y una fecha de inicio a la función STOCKHISTORY, esta recuperará los precios de cierre del proveedor de datos externo Refinitiv. La función tiene muchas opciones y argumentos, por lo que querrás explorarlos aquí . Aquí están los argumentos básicos:

=HISTORIAL DE EXISTENCIAS(existencias, fecha_inicio, fecha_finalización, intervalo, encabezados, propiedades...)

Dónde:

• la acción es el ticker
• la fecha de inicio es la primera fecha para recuperar
• end_date es la última fecha límite para recuperar
• El intervalo determina mensual, semanal o diario.
• Los encabezados determinan si queremos recuperar etiquetas de datos.
• Las propiedades son los puntos de datos que podemos recuperar, incluida la fecha, el cierre, la apertura, el máximo, el mínimo y el volumen.

Escribiremos la siguiente fórmula en la celda B10 para recuperar datos de las acciones que ingresamos en B4 , la fecha de inicio que ingresamos en B5 , la fecha de finalización que ingresamos en B6 , intervalo 0 para diario y 0 para ocultar encabezados:

=HISTORIAL DE STOCK(B4, B5, B6, 0, 0)

Cuando presionamos Enter, bam:

La fórmula recupera las fechas de cierre y los precios y los resultados salen de la celda B10 de la fórmula según sea necesario.

¡Misión cumplida! Ahora podemos escribir un nuevo ticker, fecha de inicio o fecha de finalización y los resultados de la fórmula se actualizan automáticamente.

Como beneficio adicional, podemos colocar estos datos en un gráfico si lo deseamos.

Grafico

Para crear el gráfico inicial, podemos seleccionar una celda en el rango de resultados y hacer clic en Insertar Gráfico de líneas (u otro tipo de gráfico según se desee). Cuando lo hagamos, bam:

Si solo necesitamos crear este gráfico, podemos actualizar manualmente el título del gráfico y listo.

Sin embargo, si queremos poder actualizar este gráfico dinámicamente según el ticker actual y el rango de datos en expansión, debemos realizar un par de ajustes.

Primero, obtengamos un título de gráfico dinámico. Podemos hacer esto haciendo clic en el Título del gráfico, ingresando un signo igual = en la barra de fórmulas y seleccionando nuestra celda de entrada B4 . El título del gráfico ahora se actualiza según el valor en B4 :

Nos gustaría estar seguros de que si cambiamos las fechas de inicio o finalización, el gráfico muestre todos los resultados.

Para lograr esto, crearemos dos nuevos nombres definidos. Uno hará referencia a la columna de fecha de cierre y el otro hará referencia a la columna de precio de cierre. Haga clic en Fórmulas Administrador de nombres . En el cuadro de diálogo Administrador de nombres resultante, haga clic en Nuevo.

Ingresamos un nombre descriptivo como chart_labels para hacer referencia a la columna de fecha y lo establecemos igual a la siguiente fórmula:

=ÍNDICE($B$10#,0,1)

Dónde:

• $B$10# es una referencia absoluta a la celda de fórmula… y debemos asegurarnos de ingresar el operador de referencia de derrame # al final
• 0 significa que queremos incluir todas las filas
• 1 significa que queremos incluir la primera columna (fechas en este caso)

Creamos otro nombre chart_data para hacer referencia a la columna de precio de cierre y lo igualamos a:

=ÍNDICE($B$10#,0,2)

Ahora necesitamos actualizar el gráfico para hacer referencia a nuestros nuevos nombres definidos. Hacemos esto seleccionando el gráfico y haciendo clic en Diseño de gráfico Seleccionar datos .

Seleccionamos la Entrada de Leyenda Serie 1 y hacemos clic en Editar. Reemplazamos la referencia de rango de estilo tradicional A1 con nuestro nombre definido chart_data de esta manera:

Nota: debemos mantener el nombre de la hoja (HISTORIAL DE EXISTENCIAS en este caso) y el signo de exclamación en la fórmula.

Para las etiquetas del eje de categorías horizontales, hacemos clic en Editar y reemplazamos la referencia del rango de estilo A1 con nuestro nombre definido chart_labels de esta manera:

¡Deberíamos estar listos para partir! Podemos ingresar un nuevo ticker o nombre de empresa, fecha de inicio o fecha de finalización, y el gráfico se actualiza dinámicamente:

Además, dado que nuestros nombres definidos se basan en un rango de derrame, es posible que recibamos un error inesperado si ingresamos un símbolo o nombre de empresa que STOCKHISTORY no admite. Cuando lo hacemos, vemos este error:

Esto ocurre porque STOCKHISTORY devuelve un error y, por lo tanto, no hay un rango de derrame. Una forma de manejar este problema y evitar el error es envolviendo la función IFERROR alrededor de la función STOCKHISTORY en B10 de esta manera:

=SIERROR(HISTORIAL DE STOCK(B4,B5,B6,0,0),{0,0})

Esto hará que la fórmula devuelva dos ceros y un rango de derrame para que nuestros nombres definidos no encuentren un error.

Conclusión

La función STOCKHISTORY proporciona una manera conveniente de recuperar cotizaciones históricas de acciones en Excel. Si su versión de Excel aún no es compatible con STOCKHISOTRY, no dude en consultar una publicación anterior donde demuestro cómo usar Power Query.

Archivo de muestra

HISTORIAL DE STOCKDescargar