Usando Excel Para Rastrear Tu Portafolio de Acciones
En este artículo veremos cómo puedes usar Excel para hacer un seguimiento del rendimiento de tu cuenta. Esto está destinado como una guía básica para personas que tienen poca o ninguna experiencia con Excel.
Usando Excel Para Rastrear Tu Portafolio de Acciones – Obteniendo Algunos Datos
¡Antes de que podamos hacer algo con Excel, necesitamos obtener algunos números! La información que usas en Excel se llama “Datos”. Algunos de ellos necesitaremos anotarlos, algunos pueden ser copiados y pegados, y algunos podemos descargarlos directamente como un archivo de Excel.
Obteniendo Tus Valores Históricos del Portafolio (Escribiendo Números en la Hoja de Cálculo)
Puedes encontrar tus Valores Históricos del Portafolio en la página de tu Tablero, justo encima de tu gráfico de valor del portafolio:
Esto descargará una hoja de cálculo con los valores de tu portafolio, número de operaciones y tu rango para cada día que estuviste en tu concurso actual.
Obteniendo Precios Históricos de Acciones (Copiando y Pegando Datos en una Hoja de Cálculo)
Para este ejemplo, queremos obtener los precios históricos de una acción para que podamos ver cómo ha estado moviéndose el precio a lo largo del tiempo. Primero, abre una nueva hoja de cálculo en blanco en Excel.
Usaremos la acción de Sprint (símbolo: S). Ve a la página de cotizaciones y busca S:
A continuación, haz clic en la pestaña “Histórico” en la parte superior derecha de la cotización:
A continuación, cambia las fechas de “Inicio” y “Fin” al período que deseas revisar. Para este ejemplo, usaremos las mismas fechas que guardamos para nuestros valores de portafolio, del 11 de enero al 15 de enero de 2016.
Una vez que cargues los precios históricos, resalta todo desde “Fecha” hasta el último número bajo “Cierre Ajustado” (debería verse así):
Ahora copia los datos, selecciona la celda A1 en tu hoja de cálculo de Excel en blanco y pega.
¡Felicidades, ahora hemos importado algunos datos a Excel! Nota que los encabezados de tus columnas ya están detectados – esto será importante más adelante.
A partir de ahí, hay algunas cosas que nos gustaría cambiar.
Cambiando El Orden De Tus Datos
Primero, estos datos están en el orden opuesto a nuestros valores de portafolio. Para ponerlo en el mismo orden, queremos ordenar esta tabla por fecha, de más antigua a más nueva. En el menú superior, haz clic en “Datos“, luego haz clic en “Ordenar“:
Ahora puedes elegir por qué queremos ordenar y cómo hacerlo. Si haces clic en el menú desplegable bajo “Ordenar Por”, Excel lista todos los encabezados de columna que detecta (selecciona “Fecha“). A continuación, bajo “Orden”, queremos “Más Antiguo a Más Nuevo“:
Ahora tus datos deberían estar en el mismo orden que los valores de tu portafolio de antes.
Cambiando El Ancho De Las Columnas
A continuación, notarás que “Volumen” aparece como “########”. Esto no es porque haya un error, el número es simplemente demasiado grande para caber en el ancho de nuestra celda. Para solucionar esto, podemos aumentar y disminuir los anchos de nuestras celdas arrastrando los límites entre las filas y columnas:
Consejo: si haces doble clic en estos bordes, la celda a la izquierda ajustará automáticamente su ancho para caber los datos en ella.
Si deseas ajustar automáticamente todas tus celdas a la vez, en el menú superior haz clic en “Formato”, y “Ajustar Ancho de Columna Automáticamente”:
Una vez que hayas ajustado tu columna de volumen, ¡todo debería ser visible!
Eliminando Columnas Que No Necesitas
Creo que solo queremos usar el precio de Cierre Ajustado en los cálculos que haremos más adelante (el precio de “Cierre Ajustado” es el precio de cierre ajustado por cualquier división o dividendos que ocurrieron desde ese día). Esto significa que quiero mantener las columnas de “Fecha” y “Cierre Ajustado”, pero eliminar el resto.
Si intentas simplemente seleccionar los datos y eliminarlos, terminarás con un gran espacio vacío:
En su lugar, haz clic en “B” y arrastra hasta “H” para seleccionar las columnas completas:
Ahora haz clic derecho y selecciona “Eliminar”, y las filas completas desaparecerán. Ahora el Adj. Close será tu nueva columna B, sin más espacio vacío. Ahora tienes tus datos históricos de precios, así que guarda este archivo de excel para que podamos volver a él más tarde.
Obteniendo Tu Historial de Transacciones y Posiciones Abiertas
Al igual que tu valor histórico de cartera, hacemos esto fácil: al lado de tus Posiciones Abiertas y tu Historial de Transacciones, encontrarás más botones de exportación de Excel:
Independientemente del rango de fechas que se muestre en la página del historial de transacciones, la exportación extraerá todo tu historial de transacciones para este concurso.
Usando Excel Para Rastrear Tu Cartera de Acciones – Graficando
Ahora que tenemos algunos datos, ¡hagamos algunos gráficos con ellos! Vamos a repasar cómo hacer gráficos de líneas de tu valor diario de cartera y tu cambio porcentual de cartera, además de un gráfico de barras que muestre tus posiciones abiertas. Esta es generalmente la parte más divertida de usar excel para rastrear tu cartera de acciones.
Gráfico de Líneas – Tu Valor Diario de Cartera
Primero, queremos hacer un gráfico de líneas que muestre nuestro valor diario de cartera. Primero, abre tu hoja de cálculo que tiene tus valores diarios de cartera:
A continuación, resalta tus datos y haz clic en “Insertar” en la pestaña superior:
Aquí, en la sección “Gráficos“, haz clic en el que tiene líneas y elige el primer “Gráfico de Líneas 2D“:
¡Y eso es todo! Tu nuevo gráfico está listo para mostrarse. Incluso puedes copiar el gráfico y pegarlo en Microsoft Word para que sea parte de un documento, o pegarlo en un editor de imágenes para guardarlo como una imagen.
Gráfico de Líneas – Cambios Porcentuales de la Cartera
A continuación, queremos hacer un gráfico que muestre cuánto ha cambiado nuestra cartera cada día. Para hacer esto, primero necesitamos calcularlo.
Realizando cálculos en Excel
En la siguiente columna calcularemos nuestro cambio porcentual diario de cartera. Primero, en la siguiente columna, agrega el encabezado “% Cambio”
Ahora necesitamos hacer nuestro cálculo. Para calcular el cambio porcentual cada día, queremos tomar la diferencia entre el valor del día más reciente menos el día anterior, luego dividir eso 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 hacer esto, en la celda C3 podemos realizar algunas operaciones para hacer el cálculo del cambio porcentual. Para ingresar una fórmula, comienza escribiendo “=”. Puedes usar los mismos símbolos que usas al escribir en papel para escribir tus fórmulas, pero en lugar de escribir cada número, puedes simplemente seleccionar las celdas.
Para calcular el cambio porcentual que vimos entre el día 1 y el día 2, usa la fórmula anterior en la celda C3. Debería verse así:
Ahora haz clic en la esquina inferior derecha de esa celda y arrástrala hasta tu última fila con datos, Excel copiará automáticamente la fórmula para cada celda:
¡Ahora 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, luego haz clic en el pequeño signo de porcentaje en las herramientas en la parte superior de la página:
Haciendo Tu Gráfico Con Solo Ciertas Columnas
Ahora queremos hacer un gráfico que muestre cómo estaba cambiando nuestra cartera cada día, pero si intentamos hacer lo mismo que antes (seleccionando todos los datos e insertando un “Gráfico de Líneas”, el gráfico no nos dice mucho:
Esto se debe a que está tratando de mostrar tanto el valor total de la cartera como el cambio porcentual al mismo tiempo, ¡pero están en una escala completamente diferente!
Para corregir esto, necesitamos cambiar qué datos se están mostrando. Haz clic derecho en tu gráfico y selecciona “Seleccionar Datos”:
Así es como decidimos qué datos se muestran en el gráfico. Los elementos en el lado izquierdo formarán nuestras líneas, los elementos en el lado derecho formarán los elementos que aparecen en el eje X (en este caso, nuestras Fechas).
Desmarca “Valor del Portafolio”, luego haz clic en Aceptar para actualizar tu gráfico:
Esto está más cerca, pero ahora queremos mover las fechas de vuelta a la parte inferior del gráfico (aquí están a lo largo del punto “0” del eje Y).
Para hacer esto, haz clic derecho en las fechas y selecciona “Formato de Eje”:
Un nuevo menú aparecerá en el lado derecho de la pantalla. Aquí, haz clic en “Etiquetas”, luego establece la Posición de Etiqueta en “Baja”.
¡Felicidades, tu gráfico ya está terminado! Ahora puedes ver fácilmente qué días tu portafolio tuvo un buen rendimiento y qué días tuviste pérdidas.
Gráfico de Barras – Ver tus Posiciones Abiertas
A continuación, nos gustaría hacer un gráfico de barras que muestre cuánto de nuestras posiciones abiertas actuales está en cada acción, ETF o Fondo Mutuo.
Primero, abre tu hoja de cálculo con tus Posiciones Abiertas. Debería verse algo así:
Dado que queremos hacer un gráfico de barras, solo podemos tener dos columnas de datos. Queremos una columna que muestre el símbolo y una segunda columna que muestre cuánto vale. La columna “Costo Total” es el valor de mercado actual de estas acciones, así que esa es la que queremos mantener. Sin embargo, no queremos eliminar la cantidad y el precio, ya que podríamos necesitarlos más tarde. En su lugar, selecciona las columnas que no deseas y haz clic derecho en su letra (A y C en este caso). Luego, selecciona “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 selecciona tus datos e inserta un “Gráfico de Barras” en lugar de un “Gráfico de Líneas”:
Antes de terminar, tu gráfico dirá “Costo Total”. Puedes cambiar esto haciendo clic en “Costo Total” y editando para que diga lo que desees (como “Asignación de Portafolio”):
Este gráfico ya está terminado, pero también puedes intentar cambiar el Tipo de Gráfico para intentar obtener un Gráfico de Pastel. Primero, haz clic derecho en tu gráfico y selecciona “Cambiar Tipo de Gráfico”:
A continuación, encuentra los gráficos de “Pastel” y elige el que más te guste.
Por último, ahora no sabemos qué parte del pastel representa qué acción. Para agregar esta información, haz clic en tu gráfico de pastel, luego en la parte superior de la página haz clic en “Diseño”. Luego selecciona cualquiera de las opciones para cambiar cómo se ve tu gráfico de pastel.
¡Felicidades, has convertido tu gráfico de barras en un gráfico de pastel! Este debería verse casi igual que el que tienes en el lado derecho de tu página de Posiciones Abiertas.
Usando Excel para Rastrear tu Portafolio de Acciones – Calculando las Ganancias y Pérdidas de tus Operaciones
La razón más importante por la que querrías usar Excel para rastrear tu portafolio de acciones es intentar calcular tus ganancias y pérdidas de cada operación. Para hacer esto, abre la hoja de cálculo con tu historial de transacciones. Debería verse algo así:
Consejo: Si no has comprado y luego vendido una acción, no puedes calcular cuánto has ganado en la operación.
Primero, queremos cambiar cómo se ordenan los datos para que podamos agrupar todas las operaciones del mismo símbolo juntas. Usa la herramienta “Ordenar” para ordenar primero por “Ticker”, luego por “Fecha” (de más antiguo a más nuevo).
Para DWTI y SPY, nunca hemos “cerrado” nuestras posiciones (vender una acción que compraste, o cubrir una acción que vendiste en corto), así que no podemos calcular una ganancia o pérdida. Por ahora, oculta esas filas.
¡Ahora estamos listos para calcular! Comencemos con la operación de S. Esta es fácil porque las acciones que vendí son iguales a las acciones que compré. Esto significa que si simplemente sumamos el “Monto Total”, nos dirá la ganancia o pérdida exacta que obtuvimos en la operación.
Esto no funciona para UWTI, porque vendí un número diferente de acciones al que compré. Esto significa que primero necesito calcular el costo total de las acciones que vendí, luego puedo usar eso para determinar mi ganancia.
Primero: multiplica tu precio de compra por el número de acciones que vendiste:
Segundo: añade este número al “Monto Total” de cuando vendiste tus acciones.
Ahora tienes tu ganancia o pérdida por esta operación. Nota: este es el método para si compraste más acciones de las que vendiste; si compraste acciones a diferentes precios y luego las vendes, necesitarás calcular tu Costo Promedio para usar en tu cálculo.