Uso de Excel para el seguimiento de su cartera de valores
En este artículo, veremos cómo usar Excel para realizar un seguimiento del rendimiento de su cuenta. Esta guía básica está pensada para quienes tienen poca o ninguna experiencia con Excel.
Uso de Excel para el seguimiento de su cartera de valores: Obtención de datos
Antes de usar Excel, necesitamos obtener datos. La información que se utiliza en Excel se llama “Datos”. Algunos datos se pueden anotar, otros se pueden copiar y pegar, y otros se pueden descargar directamente como un archivo de Excel.
Obtener el valor histórico de su cartera (Ingresar números en la hoja de cálculo)
Puede encontrar el valor histórico de su cartera en su panel de control, justo encima del gráfico de valores de su cartera:
Esto descargará una hoja de cálculo con los valores de su cartera, el número de operaciones y su clasificación para cada día que estuvo en su concurso actual.
Obtención de precios históricos de acciones (Copiar y pegar datos en una hoja de cálculo)
En este ejemplo, queremos obtener los precios históricos de una acción para ver su evolución a lo largo del tiempo. Primero, crearemos una hoja de cálculo en blanco en Excel.
Usaremos la acción de Sprint (símbolo: S). Vaya a la página de cotizaciones y busque S:
A continuación, haga clic en la pestaña “Histórico” en la parte superior derecha de la cita:
A continuación, cambie las fechas de “Inicio” y “Fin” a la hora que desee consultar. Para este ejemplo, usaremos las mismas fechas que guardamos para los valores de nuestra cartera: del 11 al 15 de enero de 2016.
Una vez cargados los precios históricos, resalte todo desde la “Fecha” hasta el último número en “Cierre ajustado” (debería verse así):
Ahora copie los datos, seleccione la celda A1 en su hoja de cálculo de Excel en blanco y péguela.
¡Felicitaciones! Ya hemos importado algunos datos a Excel. Tenga en cuenta que los encabezados de columna ya se han detectado; esto será importante más adelante.
A partir de ahí, hay algunos cambios que nos gustaría realizar.
Cambiar el orden de los datos
Primero, estos datos están en orden inverso al de los valores de nuestra cartera. Para que estén en el mismo orden, queremos ordenar esta tabla por fecha, del más antiguo al más reciente. En el menú superior, haga clic en “Datos” y luego en “Ordenar”.
Ahora puede elegir por qué quiere ordenar y cómo hacerlo. Si hace clic en el menú desplegable “Ordenar por”, Excel muestra todos los encabezados de columna que detecta (seleccione “Fecha”). A continuación, en “Ordenar”, deseamos “De más antiguo a más reciente”:
Ahora sus datos deberían estar en el mismo orden que los valores de su cartera de antes.
Cambiar el ancho de columna
A continuación, verá que “Volumen” aparece simplemente como “########”. Esto no se debe a un error, sino a que el número es demasiado grande para el ancho de la celda. Para solucionarlo, podemos aumentar o disminuir el ancho de las celdas arrastrando los límites entre las filas y las columnas:
Consejo: Si hace doble clic en estos bordes, la celda de la izquierda ajustará automáticamente su ancho para que quepan los datos.
Si desea ajustar automáticamente todas las celdas a la vez, en el menú superior, haga clic en “Formato” y luego en “Ajustar automáticamente el ancho de columna”.
¡Una vez que hayas ajustado la columna de volumen, todo debería estar visible!
Eliminación de columnas innecesarias
Creo que solo usaremos el precio de cierre ajustado en los cálculos que realizaremos más adelante (el precio de cierre ajustado es el precio de cierre ajustado por cualquier división o dividendo ocurrido desde ese día). Esto significa que quiero conservar las columnas “Fecha” y “Cierre ajustado”, pero eliminar el resto.
Si intentas seleccionar solo los datos y eliminarlos, quedará un gran espacio vacío:
En su lugar, haga clic en “B” y arrastre hasta “H” para seleccionar las columnas completas:
Ahora, haz clic derecho y selecciona “Eliminar”. Las filas desaparecerán. El cierre de ajuste será la nueva columna B, sin espacio en blanco. Ya tienes tus datos históricos de precios, así que guarda este archivo de Excel para que podamos revisarlo más adelante.
Obtenga su historial de transacciones y posiciones abiertas
Al igual que el valor histórico de su cartera, lo facilitamos: junto a sus posiciones abiertas y su historial de transacciones, encontrará más botones de exportación a Excel:
Independientemente del rango de fechas que se muestre en la página del historial de transacciones, la exportación extraerá todo su historial de transacciones para este concurso.
Uso de Excel para el seguimiento de su cartera de acciones – Gráficos
Ahora que tenemos algunos datos, ¡creemos gráficos con ellos! Veremos cómo crear gráficos de líneas del valor diario de su cartera y su variación porcentual, además de un gráfico de barras que muestra sus posiciones abiertas. Esta suele ser la parte más divertida de usar Excel para el seguimiento de su cartera de acciones.
Gráfico de líneas: Valor diario de su cartera
Primero, queremos crear un gráfico de líneas que muestre el valor diario de nuestra cartera. Abra la hoja de cálculo que contiene los valores diarios de su cartera:
A continuación, resalte sus datos y haga clic en “Insertar” en la pestaña superior:
Aquí, en la sección “Gráficos”, haga clic en el que tiene líneas y elija el primer “Gráfico de líneas 2d”:
¡Listo! Tu nuevo gráfico está listo para mostrar. Incluso puedes copiarlo y pegarlo en Microsoft Word para integrarlo en un documento, o pegarlo en un editor de imágenes para guardarlo como imagen.
Gráfico de líneas – Cambios porcentuales de la cartera
A continuación, queremos crear un gráfico que muestre cuánto ha cambiado nuestra cartera cada día. Para ello, primero debemos calcularlo.
Cálculos en Excel
En la siguiente columna, calcularemos el cambio porcentual diario de nuestra cartera. Primero, en la siguiente columna, agregue el encabezado “% Cambio”.
Ahora necesitamos hacer nuestro cálculo. Para calcular el cambio porcentual diario, restamos el valor del día más reciente al del día anterior y lo dividimos por el valor del día anterior:
Cambio porcentual = (Valor del día 2 – Valor del día 1) / Valor del día 1
Para ello, en la celda C3 podemos realizar algunas operaciones para calcular el cambio porcentual. Para introducir una fórmula, comience escribiendo “=”. Puede usar los mismos símbolos que usa al escribir en papel para escribir sus fórmulas, pero en lugar de escribir cada número, simplemente seleccione las celdas.
Para calcular el cambio porcentual que observamos entre el día 1 y el día 2, use la fórmula anterior en la celda C3. Debería verse así:
Ahora haga clic en la esquina inferior derecha de esa celda y arrástrela a su última fila con datos, Excel copiará automáticamente la fórmula para cada celda:
¡Ya tienes tus porcentajes! Si quieres que se muestren como porcentajes en lugar de números enteros, haz clic en “C” para seleccionar toda la columna y luego haz clic en el pequeño símbolo de porcentaje en las herramientas de la parte superior de la página.
Creando un gráfico con solo ciertas columnas
Ahora queremos crear un gráfico que muestre cómo ha evolucionado nuestra cartera cada día, pero si intentamos hacer lo mismo que antes (seleccionar todos los datos e insertar un gráfico de líneas), el gráfico no nos dice mucho:
Esto se debe a que intenta mostrar simultáneamente el valor total de la cartera y el cambio porcentual, pero están en una escala completamente diferente.
Para corregir esto, necesitamos cambiar los datos que se muestran. Haga clic derecho en el gráfico y seleccione “Seleccionar datos”.
Así es como decidimos qué datos se muestran en el gráfico. Los elementos del lado izquierdo formarán nuestras líneas, mientras que los elementos del lado derecho conformarán los elementos que aparecen en el eje X (en este caso, nuestras fechas).
Desmarque la opción “Valor de la cartera” y haga clic en Aceptar para actualizar el gráfico.
Esto está más cerca, pero ahora queremos mover las fechas de nuevo a la parte inferior del gráfico (aquí están en el punto “0” del eje Y).
Para ello, haz clic derecho en las fechas y selecciona “Formato de eje”:
Aparecerá un nuevo menú a la derecha de la pantalla. Haga clic en “Etiquetas” y configure la posición de la etiqueta en “Baja”.
¡Felicitaciones, tu gráfico está listo! Ahora puedes ver fácilmente qué días tu cartera tuvo un buen rendimiento y cuáles tuvo pérdidas.
Gráfico de barras: Visualización de sus posiciones abiertas
A continuación, crearemos un gráfico de barras que muestre la proporción de nuestras posiciones abiertas actuales en cada acción, ETF o fondo mutuo.
Primero, abra la hoja de cálculo con sus posiciones abiertas. Debería verse así:
Como queremos crear un gráfico de barras, solo podemos tener dos columnas de datos. Una muestra el símbolo y la otra su valor. La columna “Costo total” representa el valor de mercado actual de estas acciones, así que es la que queremos conservar. Sin embargo, no queremos eliminar la cantidad ni el precio, ya que podríamos necesitarlos más adelante. En su lugar, seleccione las columnas que no desee y haga clic derecho en sus letras (A y C en este caso). Luego, seleccione “Ocultar”.
Ahora las columnas que no queremos en nuestro gráfico están ocultas. Siempre podemos recuperarlas más tarde yendo a “Formato” -> “Visibilidad” -> “Mostrar columnas”. Ahora seleccione sus datos e inserte un “Gráfico de barras” en lugar de un “Gráfico de líneas”:
Antes de terminar, su gráfico mostrará “Costo total”. Puede cambiarlo haciendo clic en “Costo total” y editándolo para que indique lo que desee (por ejemplo, “Asignación de cartera”).
Este gráfico ya está terminado, pero también puedes intentar cambiar el tipo de gráfico para intentar obtener un gráfico circular. Primero, haz clic derecho en el gráfico y selecciona “Cambiar tipo de gráfico”.
A continuación, busque los gráficos “circulares” y elija el que más le guste.
Por último, ahora no sabemos qué parte del gráfico representa cada acción. Para agregar esta información, haz clic en tu gráfico circular y, en la parte superior de la página, haz clic en “Diseño”. Después, selecciona cualquiera de las opciones para cambiar el aspecto de tu gráfico circular.
¡Felicitaciones! ¡Has convertido tu gráfico de barras en un gráfico circular! Este debería verse casi igual al que tienes a la derecha de tu página de Puestos Vacantes.
Uso de Excel para el seguimiento de su cartera de acciones: cálculo de las ganancias y pérdidas de sus operaciones
La razón principal por la que debería usar Excel para el seguimiento de su cartera de acciones es para calcular las ganancias y pérdidas de cada operación. Para ello, abra la hoja de cálculo con su historial de transacciones. Debería verse así:
Consejo: Si no ha comprado y vendido una acción, no podrá calcular la ganancia obtenida en la operación.
Primero, queremos cambiar la clasificación de los datos para agrupar todas las operaciones del mismo símbolo. Utilice la herramienta “Ordenar” para ordenar primero por “Ticker” y luego por “Fecha” (de la más antigua a la más reciente).
En el caso de DWTI y SPY, nunca hemos cerrado nuestras posiciones (vendiendo una acción que compró ni cubriendo una acción que vendió en corto), por lo que no podemos calcular ganancias ni pérdidas. Por ahora, oculte esas filas.
¡Ya estamos listos para calcular! Empecemos con la operación de S. Esta es fácil porque las acciones que vendí son iguales a las que compré. Esto significa que si simplemente sumamos el “Monto Total”, nos indicará la ganancia o pérdida exacta que obtuvimos en la operación.
Esto no funciona con UWTI, ya que vendí una cantidad de acciones diferente a la que compré. Esto significa que primero debo calcular el costo total de las acciones vendidas para luego usar ese valor para determinar mi ganancia.
Primero: multiplique el precio de compra por la cantidad de acciones vendidas:
Segundo: agregue este número al “Monto Total” de cuando vendió sus acciones.
Ahora tiene su ganancia o pérdida para esta operación. Nota: Este método se aplica si compró más acciones de las que vendió. Si compró acciones a diferentes precios y luego las vendió, deberá calcular su costo promedio para usarlo en su cálculo.