Una de las peticiones recurrentes desde que empecé este proyecto es que publique la hoja que utilizo para el seguimiento¡Hola, cazadividendos! Una de las peticiones recurrentes desde que empecé este proyecto es que publique la hoja que utilizo para el seguimiento, tanto de la cartera como de las empresas que quiero incorporar. Inicialmente era una hoja Excel, pero a raíz del artículo de J sobre Google Finance acabé traduciéndola a una hoja de Google. Desde entonces la he ido puliendo poco a poco y creo que ahora es lo suficientemente versátil como para presentarla a la comunidad. Mi idea, además de publicarla,  es explicar cómo está construida para que los que la encontréis útil podáis adaptarla a vuestras necesidades.

Será un post con mucho número y mucha fórmula… pero creo que puede ser fructífero para varios perfiles de lector. Los que hacen un seguimiento muy sencillo, porque tendrán una herramienta suficientemente versátil para controlar los aspectos relevantes de su cartera. Los que ya tienen una hoja de seguimiento podrán incorporar alguna idea a a su hoja. Los que utilizan Excel podrán valorar si traducirla a hoja Google les aporta algo o no. La principal ventaja de las hojas Google es que están en la nube, son compartidas y se pueden acceder desde cualquier dispositivo, pero a mi me frenaba el hecho de que no sabía si todo lo que hacía con Excel se podía hacer con ellas. En mi caso particular, todo lo que hacía con Excel lo he podido hacer con Google Sheets.

Espero que para mi también sea beneficioso. Seguro que en los comentarios del artículo aparecerán sugerencias interesantes que podré incorporar. Eso sí, antes de empezar ya os aviso de que hoy toca entrada densa. Densa y laaaaaarga. Esa es la razón de que haya tardado tanto en publicarla, pero creo que esta vez tengo una buena excusa 🙂

Mi hoja de seguimiento

Supongo que la mayoría conocéis la página de Valores del blog. Es la página estática más visitada del blog. En ella se muestra mi cartera actual de acciones y fondos de inversión, y las empresas en las que estoy interesado. También hay muchos gráficos donde se ve la cartera desde diferentes perspectivas de diversificación: empresas, sectores, estrategias, divisas, mercados, instrumentos, etc…

pagina_valores

En el fondo, esta página no es más que el resumen en limpio de la hoja de Google que hoy os presentaré que, a su vez, es una versión simplificada de mi hoja de seguimiento. Simplificada, porque he eliminado la mayoría de posiciones, dejando sólo dos empresas españolas, dos estadounidenses, dos británicas y los fondos indexados. También he borrado algunas pestañas que utilizo yo pero que creo que no aportan nada a la mayoría de lectores. De esta manera eliminamos ruido y la explicación será más sencilla.

¿Cómo utilizar la hoja de seguimiento?

La hoja sólo tiene permisos de visualización pero no permite ningún tipo de modificación. Al tratarse de una hoja compartida es mejor que no se puedan hacer cambios para no estropearla. Por lo tanto, si queréis jugar con ella, incluir vuestras empresas y, en definitiva, adaptarla a vuestra situación, sólo tenéis que autenticaros con una cuenta de Google y haceros una copia desde el menú Archivo:

Crear una copia de una hoja de Google

Estructura general de la hoja de seguimiento

La hoja está estructurada en cinco pestañas:

  • En valores se muestran las posiciones actuales de la cartera.
  • En radar tenemos la lista de empresas que seguimos para incorporar a la cartera.
  • En histórico se guardan los pagos de dividendos y scrips por empresa y mes.
  • En operaciones se registran las compras, ventas, cobros de dividendos, scrips, traspasos de fondos, etc…
  • En auxiliar guardo datos que no encajan en las anteriores, como las tasas de cambio dólar-euro y libra-euro razonables. En mi hoja, utilizo esta pestaña para hacer otras manualidades, como calcular el precio de una compra incluyendo las comisiones y aplicando el cambio del momento, o para ajustar el número de acciones necesarias para un determinado importe de compra.

Igual lo siguiente es demasiado básico, pero prefiero decirlo por si alguien no está familiarizado con las hojas excel o de Google: la manera de cambiar la pestaña activa es a través del pestañero de la parte inferior:

seleccionar_pestaña_en_hoja_Google

Anotación de operaciones

Empecemos por lo más sencillo: apuntar las compras, cobros de dividendos, scrips, ventas, etc. de nuestra cartera. Para ellos iremos a la pestaña de operaciones, que tiene el siguiente aspecto:

datos_pestaña_operaciones

Para cada nueva operación, añadiremos una fila con los datos de la misma. Los más importantes son los siguientes:

  • Fecha, a partir de la cual la hora se calcula el día, mes y año, que son necesarios para luego poder obtener la inversión en cada mes.
  • Tipo: Compra, Venta, Ampliación (scrip), Dividendos, Venta-Traspaso / Compra-Traspaso (un traspaso de fondos lo reflejo como una Venta-Traspaso más un Compra-Traspaso) y Alta por Canje (algunas acciones las tengo por conversión de otro producto). Es importante poner los literales exactos porque se utilizarán para calcular la inversión en ese valor: las compras, compras-traspaso y altas por canje suman, y las ventas y ventas-traspaso restan. Lo veremos después. Podría tenerlo preparado para que se elija el tipo de una lista desplegable, pero como Google completa los literales a medida que los vas escribiendo, no me parece necesario.
  • Valor, que debe coincidir con el ticker en Google Finance o el ISIN en el caso de los fondos. Es muy importante que coincida, ya que es el elemento que se utiliza para ligar la información entre las diferentes pestañas y para obtener los ratios de esa empresa.
  • Cambio utilizado por el broker en la operación, necesario para hacer la conversión a euros en el caso de los valores extranjeros.
  • Datos de la operación: títulos, cotización, comisión, ingresos, etc… Los importes se detallan en divisa origen y se convierten automáticamente a euros.

Histórico de dividendos

Los dividendos, tanto los pagados en efectivo como las ampliaciones (scrip), se anotan en la pestaña de histórico.

Pestaña de dividendos, donde se refleja el cobro de dividendos en efectivo y ampliaciones


Cada columna de la B a la G corresponde a un valor, obtenido de la pestaña de valores. Por ejemplo, la primera empresa (celda B2, LON:BLT) corresponde al primer valor de la pestaña de valores (=Valores!A2). Luego revisaremos esta pestaña. Si comprásemos acciones de una nueva empresa, habría que añadir una columna para reflejarlo. El orden de las empresas es irrelevante. Yo prefiero hacerlo por orden alfabético del nombre (BHP va antes que Bolsas y Mercados) y otros preferirán hacerlo por orden alfabético del ticker (BME:BME va antes que LON:BLT). Escoged el que os resulte más natural para encontrar la información rápidamente.

Los dividendos se añaden manualmente en cada valor en los meses de pago. Antes de que llegue la fecha de pago el dividendo se construye dinámicamente. Por ejemplo, para la celda F24 (dividendo de Chevron de septiembre, el dividendo esperado sería:

=1,07*Valores!$H$6*GoogleFinance(“CURRENCY:USDEUR”)*0,995*0,85*0,8

 

Donde:

  • 1,07 es el importe del dividendo en divisa origen (dólares).
  • Valores!$H$6 es el número de títulos obtenido de la pestaña Valores.
  • GoogleFinance(“CURRENCY:USDEUR”) es el cambio de dólares a euros.
  • 0,995 es el factor a aplicar para reflejar la comisión de cambio (0.5%).
  • 0,85 es el factor a aplicar para reflejar la retención en origen (15%).
  • 0,8 es el factor a aplicar para reflejar la retención en destino (20%, aún no lo he actualizado al 19.5% actual).

Cuando llega la fecha de pago, sustituyo esta estimación del dividendo por el importe real cobrado. En general, los cambios respecto a la previsión pueden venir por la tasa de cambio o porque en función del broker aplican diferentes retenciones en origen o en destino. Cuando un dividendo no me corresponde porque he comprado después de la fecha de registro (record date) coloreo la casilla en negro (ejemplo celda G16). Cuando un dividendo se paga en scrip, indico el equivalente en efectivo y la coloreo en amarillo (ejemplo celda E26). Con este código de colores es muy fácil ver lo que cobras este año y hacerte una idea de lo que cobrarás al siguiente, ya que las casillas negras se convertirán en dividendos. También verás de manera muy clara lo que cobras en dividendo de verdad y lo que cobras en scrip. Después veremos que tenemos columnas específicas para saber cuánto cobramos en acciones y cuanto en efectivo cada mes.

En la columna H se muestra la inversión que hemos hecho cada mes, obtenida a partir de la pestaña de operaciones. Por ejemplo, para el mes de marzo (celda H18), la inversión es:

=(SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E;”Compra”) + SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E; “Alta por canje”) + SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E; “Op. Anteriores”) + SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E; “Compra-Traspaso”) – SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E;”Venta”) – SUMIFS(Operaciones!S:S; Operaciones!C:C;MONTH(A18); Operaciones!D:D;YEAR(A18); Operaciones!E:E; “Venta-Traspaso”))

 

Parece muy complicado, pero al final lo único que hacemos es sumar las compras, compras-traspaso, operaciones anteriores y altas por canje del mes/año de la celda H18 y restar las  ventas y ventas-traspaso para ese mismo mes. La inversión acumulada cada mes (columna I) se calcula a partir de la columna de inversión mensual (columna H).

En las columna J y K se indica el dividendo total en efectivo y en acciones para ese mes, sumando de manera manual los dividendos de las columnas. El resto de columnas se calculan a partir de estas: dividendo total (columna L), dividendos acumulados (columna M), rentabilidad (columna N) y rentabilidad anualizada (columna O). No es complicado y clicando en cada celda dos veces se pueden ver las fórmulas utilizadas para estos cálculos. El resto de información de la pestaña son las filas de dividendos anuales por valor y dividendos totales por valor.

Y hasta aquí las manualidades. Vamos ahora con el núcleo de la hoja de seguimiento, que es la pestaña donde se guardan la posiciones de la cartera: la pestaña de valores.

Valores en cartera

La idea general es que la pestaña de valores sea completamente automática, obteniendo el capital invertido y el número de acciones de la pestaña de operaciones, los dividendos por valor de la pestaña de histórico y los ratios de cada empresa de llamadas a la API de GoogleFinance y de algunas tablas de la la página de cada valor en Google Finance. Al contener tantas columnas, es difícil mostrar una imagen que las abarque todas, así que las dividiremos en dos imágenes. Empecemos por la parte izquierda de la tabla:

datos_pestaña_valores_1

Cada fila corresponde a un valor. En la columna A se indica el ticker de la empresa o el ISIN del fondo. Es importante que el ticker sea el de Google Finance para poder obtener la información a través de su API y para poder buscar la información que nos interesa en las pestañas de operaciones e histórico.

En la columna B se indica el nombre de la empresa, a efectos meramente informativos. En el caso de las empresas se puede prescindir de él porque normalmente relacionamos fácilmente el ticker con la empresa. Lo mantengo porque, en el caso de los fondos se utiliza el ISIN y el nombre del fondo es necesario para saber de qué fondo hablamos.

La columna C contiene la estrategia, la D el broker, la E el mercado, la F la divisa y la G el sector. Nos servirán para obtener las gráficas de diversificación por cada uno de estos criterios para controlar que la cartera esté lo más equilibrada posible.

La columna H es el número de títulos para cada valor y se obtiene a partir de la pestaña de operaciones, sumando el número de títulos de cada operación que añade títulos (compra, alta por canje, op. anteriores y compra-traspaso) y restando el número de títulos de las operaciones que reducen el número de títulos (venta y venta-traspaso). Por ejemplo, para BHP Billiton (celda H2) tendría la siguiente fórmula:

=(SUMIFS(Operaciones!K:K;Operaciones!G:G;A2; Operaciones!E:E; “Compra”) + SUMIFS(Operaciones!K:K; Operaciones!G:G; A2;Operaciones!E:E; “Alta por canje”SUMIFS(Operaciones!K:K; Operaciones!G:G;A2; Operaciones!E:E; “Op. Anteriores”) + SUMIFS(Operaciones!K:K; Operaciones!G:G;A2; Operaciones!E:E; “Compra-Traspaso”) + SUMIFS(Operaciones!K:K; Operaciones!G:G;A2; Operaciones!E:E; “Ampliación”) SUMIFS(Operaciones!K:K; Operaciones!G:G;A2; Operaciones!E:E; “Venta”SUMIFS(Operaciones!K:K; Operaciones!G:G;A2; Operaciones!E:E; “Venta-Traspaso”))

 

La columna I es el porcentaje de cartera que representa cada valor y se calcula dividiendo la valoración actual de ese valor entre la valoración actual de la cartera total. Siguiendo con el ejemplo de BHP sería:

=Y2/Y$14


Más adelante explicaremos cómo se calcula la columna Y. Vamos con las columnas J y K, que muestran la RPD a cotización actual y el importe del dividendo para cada valor. La RPD se obtiene a partir de la tabla 3 de la hoja de Google Finance de cada valor (http://www.google.com/finance?q=TICKER). Reutilizo el ejemplo de General Electric que pusimos en el artículo sobre Google Finance:

google_finance_ge1

 

A partir de esta tabla, hacemos un tratamiento un poco rocambolesco, que consiste en sustituir los “.” por “,” (notación USA -> notación española), quedarnos con el segundo elemento, que contiene el dividendo y la RPD (en el ejemplo 0.23/3.38) , lo partimos por “/” para separarlos (0.23 y 3.38) y nos quedamos con el segundo elemento (3.38), que es la RPD. J lo explicaba muy bien en el artículo sobre Google Finance. Esta es la fórmula:

=index( split( index( substitute( importhtml( concatenate( “http://www.google.com/finance?q=”; A2); “table”; 3); “.”; “,”); 1; 2);“/”;TRUE); 1; 2)


El dividendo lo calcularemos multiplicando la RPD por la valoración actual. Previamente le quitamos los “*” que, a veces, aparecen en el texto de la RPD:

=SUBSTITUTE(J2;“*”;“”)*Q2/100


En el caso de BHP Billiton, por ejemplo, pone “*” para indicar que el dividendo es en libras:

astericos_en_rpd

Las siguientes seis columnas obtienen sus valores de la funcion GoogleFinance:

  • Columna L – BPA=GOOGLEFINANCE(A2;“EPS”)
  • Columna M – PER: =GOOGLEFINANCE(A2;“PE”)
  • Columna N – mínimo de 52 semanas: =GOOGLEFINANCE(A2;“low52”)/IF(F2=“GBP”;100;1)
  • Columna O – máximo de 52 semanas: =GOOGLEFINANCE(A2;“high52”)/IF(F2=“GBP”;100;1)
  • Columna P – hora: =GOOGLEFINANCE(A2;“price”)/IF(F2=“GBP”;100;1)
  • Columna Q – cotización: =GOOGLEFINANCE(A2;“price”)/IF(F2=“GBP”;100;1)

Los importes se dividen por 100 si la divisa es GBP, porque en GoogleFinance se utilizan peniques en vez de libras.

Para el caso de los fondos, obtenemos la información que tiene sentido, el precio y la fecha, de quefondos.com:

  • Columna P – hora: =MID( importXml( CONCATENATE( “http://www.quefondos.com/es/fondos/ficha/index.html?isin=”; A8); “(//span[@class=’floatright’])[8]”); 1; 50)
  • Columna Q – cotización: =SPLIT( MID( importXml( CONCATENATE( “http://www.quefondos.com/es/fondos/ficha/index.html?isin=”; A8); “(//span[@class=’floatright’])[6]”); 1; 50); “EUR”)

La siguiente columna muestra el precio medio de compra para ese valor, calculado, una vez más, a partir de la pestaña de operaciones, según esta fórmula:

=(SUMIFS(Operaciones!R:R; Operaciones!G:G; A2; Operaciones!E:E; “Compra”) + SUMIFS(Operaciones!R:R; Operaciones!G:G; A2; Operaciones!E:E; “Alta por canje”) + SUMIFS(Operaciones!R:R; Operaciones!G:G; A2; Operaciones!E:E; “Op. Anteriores”)) / (SUMIFS(Operaciones!K:K; Operaciones!G:G; A2;Operaciones!E:E; “Compra”) + SUMIFS(Operaciones!K:K; Operaciones!G:G; A2; Operaciones!E:E; “Alta por canje”) + SUMIFS(Operaciones!K:K; Operaciones!G:G; A2; Operaciones!E:E; “Op. Anteriores”))


De momento, esta fórmula sólo tiene en cuenta las adquisiciones y no las ventas. Para aquellos que incluyáis las ventas como parte de vuestra operativa tendríais que incorporarlas a esta fórmula restando los importes de adquisición correspondientes a las acciones vendidas. En el futuro, supongo que tendré que hacerlo pero, hasta el momento, no he tenido la necesidad.

Las columnas T y U tienen el precio al que ampliaría el valor en la divisa origen y en euros. El precio en divisa origen se introduce manualmente y el precio en euros se calcula multiplicando el anterior por una tasa de cambio que considero razonable y que se obtiene de la pestaña auxiliar. Esta es la fórmula:

=T2/IF(F2=“EUR”1IF(F2=“USD”Auxiliar!$B$2IF(F2=“GBP”Auxiliar!$B$3“ERROR”)))


Auxiliar!$B$2 contiene la tasa de cambio USDEUR razonable (1.14) y la Auxiliar $B$3 la GBPEUR (0,76). Con estos dos precios de compra, en divisa origen y en euros, podremos garantizar que no hacemos grandes barbaridades. Si el cambio es muy perjudicial, seremos más exigentes con el precio. Si el cambio es muy beneficioso, podemos permitirnos ser menos exigentes con el precio. Lo veremos a continuación. Vamos con la parte derecha de la pestaña de valores. Esta es la imagen:

datos_pestaña_valores_2


Las columnas V y W son medidas de lo caro que está el valor respecto al precio de compra que me había marcado y respecto a su equivalente razonable en euros. Por ejemplo, para BHP Billiton (fila 2):

V2  = (Q2T2)/Q2, es decir, la diferencia en % entre la cotización actual y mi precio de compra.
W2 = (R2U2)/R2, es decir la diferencia en % entre la cotización actual en euros y mi precio de compra a un cambio razonable.


La idea es plantearse comprar sólo si ambos indicadores están en negativo o cero, es decir, si la acción no está cara ni en divisa origen ni en euros.

La columna X muestra el total invertido a partir de la pestaña de operaciones:

=(SUMIFS(Operaciones!S:S; Operaciones!G:G;A2; Operaciones!E:E; “Compra”) + SUMIFS( Operaciones!S:S; Operaciones!G:G; A2; Operaciones!E:E; “Alta por canje”) + SUMIFS( Operaciones!S:S; Operaciones!G:G; A2; Operaciones!E:E; “Op. Anteriores”) + SUMIFS(Operaciones!S:S; Operaciones!G:G; A2; Operaciones!E:E; “Compra-Traspaso”) SUMIFS(Operaciones!S:S; Operaciones!G:G; A2; Operaciones!E:E; “Venta”SUMIFS(Operaciones!S:S; Operaciones!G:G; A2; Operaciones!E:E; “Venta-Traspaso”))


La columna Y muestra la valoración actual:

=Q2*H2*IF(F2=“EUR”; 1; GoogleFinance(concatenate(“CURRENCY:”; F2; “EUR”)))


La columna Z calcula el total en dividendos para ese valor a partir de la pestaña de histórico:

=HLOOKUP($A2; Historico!$B$2:$G$29; 28; FALSE)


y la columna AD, los dividendos del año en curso ese valor, también a partir de la pestaña de histórico:

=HLOOKUP($A2; Historico!$B$2:$G$29; 27; FALSE)


El resto de columnas son cálculos de rentabilidades utilizando todos los valores anteriores salvo las columnas AI y BL, que contiene enlaces a artículos de la comunidad sobre ese valor. Este punto es para mi muy importante. Voy guardando aquellos análisis que me parecen más completos y que me ofrecen perspectivas diferentes sobre el valor. Guardo todos los que me parecen interesantes, aunque sean de empresas que no tengo en seguimiento. Siempre va bien tener información y análisis de varias fuentes para tener una visión lo más completa posible, y cuando empiezo a seguir una empresa es mejor partir de varios análisis recogidos en los últimos meses que partir de cero.

Finalmente, la parte de abajo, en azul, hace cálculos sobre la cartera para obtener el total invertido, la valoración actual de la cartera, el total de dividendos, la rentabilidad calculada de diferentes maneras y la renta mensual esperada.

ratios_generales_cartera

No entraré en detalle porque son cálculos sencillos que podéis revisar simplemente haciendo doble-click en las celdas.

Un punto para mi muy importante es generar automáticamente las gráficas de diversificación de la cartera, porque me ayuda a valorar si mi inversión está equilibrada de un vistazo. Este es un uso bastante más avanzado que el expuesto hasta el momento, pero los que ya usáis Google Finance, podréis construirlas insertando tablas dinámicas (menú Datos -> Tabla dinámica), seleccionando las Filas, Columnas, Valores y Filtros adecuados para quedarnos con la información que no interesa y dibujar las gráficas a partir de ellos. En los siguientes ejemplos, las tablas dinámicas y las gráficas asociadas muestran la inversión por estrategia, divisa, broker y mercado.

datos_pestaña_valores_3


Valores en seguimiento

La pestaña de radar no tiene mucho misterio: no es más que una versión reducida de la pestaña de valores, eliminando toda la información relativa a la posición de cada valor en cartera (número de títulos, porcentaje, rentabilidad, etc…), dado que aún no forman parte de ella.

datos_pestaña_radar


Pestaña auxiliar

La utilizo para muchas tareas adicionales y es una especie de espacio de trabajo para hacer mis cálculos. He dejado los dos que me parecen más útiles.

pestaña_auxiliar

El primero es una tabla con la tasa de cambio actual y la tasa mínima razonable para comprar. Con estos datos se construyen las columnas U y W de la pestaña de  valores, con las que decidimos el precio de compra en euros, cosa que nos “protege” contra compras baratas en divisa origen que son caras en euros.

El segundo es una calculadora de rentas mensuales que se nutre del valor acumulado de cartera (Valores!Y14), la RPD actual (Valores!AG15) y de los valores de la tabla (ahorro mensual y fecha en que empezaremos a disfrutar de la renta mensual)

Conclusión

Tener una buena hoja de seguimiento es fundamental para controlar como vamos formando la cartera, detectar los problemas e intentar corregirlos. También es muy útil para identificar los valores que llegan a precios de compra sin esfuerzo. Para ello es muy recomendable utilizar el formato condicional (menú Formato -> Formato condicional) y aplicar códigos de colores a las celdas que nos servirán para tomar decisiones. Yo utilizo verde, ámbar y rojo para para valores negativos, cero y positivos, respectivamente, o escalas de color de verde a rojo, en función del valor que quiero controlar, pero cada uno debe utilizar el código que le resulte más cómodo para el seguimiento.

Supongo que leyendo el artículo entenderéis por qué me he retrasado… Tocaba publicación hace un par de días, pero he tenido que dedicar mucho más tiempo del habitual, porque explicar con un cierto nivel de detalle la hoja de seguimiento se ha traducido en un artículo mucho más denso y largo de lo habitual. Aún así, he preferido presentarla en un único artículo para que, el que quiera probar la hoja, tenga la explicación entera y no tenga que esperar a futuras entregas.

Llega vuestro turno. Creo que sería muy productivo que  explicaseis cómo hacéis el seguimiento de vuestras carteras y que hagáis propuestas de mejora sobre la hoja que os he presentado. Seguro que tenéis funcionalidades muy interesantes que no están presentes en ella.

Si os ha gustado el artículo, como siempre, os pido que lo compartáis en las redes sociales. Es muy sencillo, sólo tenéis que pulsar en uno de los iconos que tenéis bajo estas líneas y seguir las instrucciones, y con ello ayudáis a que la comunidad siga creciendo.  También podéis seguir los artículos más interesantes de los bloggers de la comunidad en Los mejores artículos de inversión en dividendos. ¡Qué tengáis buena caza!