Crear un radar dinámico basado en CAGR dinámico con Google Spreadsheet y Google Finance
Guía paso a paso
Estimados suscriptores:
Antes de empezar, quería pedir disculpas por este gran parón que ha habido en la frecuencia de mis publicaciones. He tenido una situación personal compleja que no me ha permitido seguir con regularidad.
Dicho esto, quiero retomar las publicaciones con afán de poder aportar de vuelta todo aquello que la comunidad me ha ofrecido, y de la que estaré tan agradecido. Si bien hay mucha toxicidad y ego rondando fintwit y resto de foros, como todo en la vida, si uno envía respeto y gratitud, recibirá lo mismo de forma multiplicada.
Una vez soltada esta perla emotiva de azucarillo, hoy os traigo una guía de cómo poder crear un radar dinámico que actualice cotizaciones y margen de seguridad a tiempo real de tus análisis, con otros datos actualizados de utilidad, y esto lo conseguiremos vinculando el radar a cada hoja de análisis que tengamos por cada una de nuestras empresas. Nos basaremos principalmente en dos herramientas: Google Spreadsheet y Google Finance y sus funciones.
Nota importante: si estás en correo electrónico, abre en substack para que no se corte la lectura:
Google Spreadsheet no es más que una versión “online” de Excel, donde puedes vincular hojas entre sí de forma más cómoda, y llamar a cotizaciones en tiempo real.
Las funciones de Google Finance incorpora las fórmulas para llamar a estas cotizaciones, así como otro tipo de métricas de la acción (PER, Mcap, etc)
El paso 0, y con peligro de ofender la inteligencia de alguien, es saber crear una hoja nueva. Para ello vamos a https://docs.google.com/spreadsheets/u/0/ y creamos una nueva hoja en blanco.
Guía paso a paso
La estructura, en mi caso, es la siguiente:
1 documento de Google Spreadsheet por cada empresa al que llamaré ANÁLISIS, que dispondrá de 5 hojas que explicaré más adelante. Vamos a suponer que se trata de ENGHOUSE SYSTEM (aunque en las imágenes aparezcan otras, solo por poner un ejemplo)
1 documento Spreadsheet “RADAR” donde tendremos una fila por cada empresa, y donde captará datos del documento ANÁLISIS por cada una de las empresas, actualizándose en tiempo real y moviéndose con la cotización.
PARTE 1: DOCUMENTO DE ANÁLISIS DE LA EMPRESA
Todo inversor o aspirante debe tener una hoja de cálculo donde exponga sus análisis, hipótesis y conjeturas sobre la futura evolución del negocio. Esto no es parte de este post, así que enseñaré la mía pero de forma puramente ilustrativa (que por cierto, estoy en proceso de renovación prácticamente por completo para implementar una mejora sustancial), para que sirva de apoyo a la explicación pero, como digo, cada cual deberá tener la suya.
¿Y si tengo mi análisis de la empresa en un archivo.xlsx local de excel?
Para pasar tu archivo de Excel a Google Spreadsheet seguiremos estos pasos:
Subir tu archivo excel a Google Drive
Abrirlo desde Google Drive
Convertir a hoja de cálculo de google
Y ya tendríamos nuestra hoja de la empresa “en línea”.
En mi post de Enghouse System (totalmente desactualizado debido a la situación personal comentada) se puede rescatar mi propia hoja de análisis cuantitativo de la empresa, cosa que veremos a continuación.
En mi caso, para el análisis de cada empresa dispongo de 6 pestañas:
DATA
MAIN
VALUATION BASE
VALUATION OPTIMISTA
VALUATION PESIMISTA
GRAPH
DATA: datos brutos extraídos de TIKR, en este caso, que habrá que repasar con la fuente original de los informes en un análisis concienzudo (TIKR puede tener erratas)
MAIN: en esta hoja se calculan todos los ratios, valores, magnitudes, etc. que cada cual consideren importantes para el análisis.
Enlace a mi hoja MAIN para Enghouse System
VALUATION BASE: La tercera de las 5 hojas que componen mi documento de ANALISIS de Google Spreadsheet para la empresa citada, es la de valoración en escenario base.
Enlace a mi hoja VALUATION BASE para Enghouse System
VALUATION BEAR CASE: Cambiaremos los datos para escenario pesimista y obtendremos nuestro retorno.
Enlace a mi hoja VALUATION BEAR CASE para Enghouse System
VALUATION BULL CASE: Cambiaremos de nuevo los datos para escenario optimista y obtendremos un nuevo retorno.
Enlace a mi hoja VALUATION BEAR CASE para Enghouse System
GRAPH: Aquí, grafico la curva para el todos los casos, pero solo para BASE CASE obtengo la curva de la pendiente del valor intrínseco, explicado en mi entrada “La importancia de la gestión de cartera y de la pendiente de la curva de valor intrínseco”, dato que usaré más tarde en el radar
Por ahora, quedémonos con el valor que aparece en la imagen de 66,20% (pendiente de la curva). Te animo a leer el post para saber cómo interpretar este valor
Bien, como digo, estas hojas son puramente ilustrativas de cara a poder elaborar el radar. Asumo que tú tendrás tu propio excel o spreadsheet de análisis, y donde tenemos que converger será en las siguientes celdas:
En mi caso, estos valores se encuentran en cada una de las tres hojas de valoración, pero las capto para referenciarlas desde la hoja de VALUATION BASE.
Por motivos prácticos, en mi caso, lo que hago es llevarme estos valores a la hoja MAIN, y desde ahí las referencio al radar, como luego veremos. La siguiente imagen es de mi hoja MAIN de análisis de Enghouse
Así, mis celdas claves son: B42,B43,B44,C42,C43,C44, además de la celda de la pendiente de la curva de valor intrínseco ( En hoja GRAPH)
En tu caso, tendrás que quedarte con las celdas que incluya cada uno de los CAGR a futuro a 3 y 5 años, o según la proyección que dispongas.
Hasta aquí la mayoría es puramente explicativo. Debes pasar desde excel a google spreadsheet tu hoja de análisis de tu empresa. Mis ejemplos son ilustrativos, así que debes centrarte e identificar dónde están las celdas que arrojan tu TIR o CAGR a futuro y, opcional, la curva de valor intrínseco. Estas serán las únicas celdas que necesitemos referenciar.
PARTE 2: EL RADAR
Nota importante: las TIRs o CAGR y/o cotas técnicas están totalmente desactualizadas y no deben ser para nada razón de compra o venta de ninguna empresa.
El radar, en este caso he usado la categoría de empresas Consumer Staples, visualmente se verá de la siguiente manera:
Tenemos una línea por cada empresa.
A continuación vamos a explicar por qué nos puede ser de amplia utilidad. Te recomiendo abrir el radar destinado a este post con fines de aprendizaje y tenerlo a la vista mientras lees esta entrada. Lo dejaré al final, pero puedes abrirlo aquí también
Voy a explicar cada una de las columnas y cómo fluctúa o cambia su valor. Voy a empezar por la columna final, que es la que le dará sentido a las demás. Sitúate en la fila 4 para seguir el hilo: Tyson foods
Columna AH: Enlace. Aquí vamos a introducir la URL de tu documento de ANALISIS de la empresa en cuestión. De esta forma, nuestro radar ya puede importar valores de celdas de otros libros (aunque habrá que darle permisos como veremos luego), y así poder actualizar a tiempo real nuestras estimaciones
Columna A: nombre de la empresa. Me es útil añadir notas (botón derecho → añadir nota) con peers, enlaces a tesis, Youtube, o cualquier otra información que considere. Tiene un formato condicional que podrás visualizar más adelante, de forma que si la empresa está lo suficientemente barata y en buena situación técnica, se coloreará de negro para resaltarse.
Columna B: separador negro estético
Columna C: versión. Esto me sirve para ver la versión actual de mi libro de análisis de la empresa en cuestión. Al ir modificando la hoja de análisis cada X tiempo con mejoras y otras actualizaciones (que creo que todo el que trastee excel y modelos es consciente de esto), puedo ver si a mi hoja de esa empresa le falta alguna actualización, y cual es. En mi caso, la V.5.4.2 es mi última versión, y ahí puedo ver el histórico de actualizaciones de mil ajustes. Esto es similar a las actualizaciones de softwares o videojuegos, con sus parches. Ayuda a tener una trazabilidad.
Esta está puesto como nota en mi hoja MAIN de mi empresa, y también aparece en el radar.
Columna D: Stake a usar. Esta columna es fruto de mi post La importancia de la gestión de cartera y de la pendiente de la curva de valor intrínseco. En resumidas cuentas, esta columna nos indicará qué stake (cantidad de dinero) tener en cartera de dicha empresa, basándonos en el descuento de su fair value.
Esta columna está referenciada a las columnas M y N (en mi caso solo uso la M, a tres años, aunque quería cambiarlo a 4 y hacer un promedio entre 3 y 5 años), que te adelanto que son el CAGR a 3 y 5 años esperado, referenciadas a su vez a nuestra hoja de análisis de la empresa que ya hemos visto
En mi caso, las orquillas que he usado para saber el stake a tener de dicha empresa en función del CAGR esperado, son las siguientes (tú puedes modificar a tu gusto estas orquillas según tu criterio)
Si el CAGR esperado es menor de 15%, el stake es 0% (no tengo participación, exijo más de 15%)
Si el CAGR esperado es entre 15% y 25%, mi stake es del 100%
Y así, 35% será un 150%, 45% será un 200%, 50% será un 250% y más de 50% será un 300%
¿Qué se considera una unidad (100%) de stake? Para esto tendrás que hacer cálculos sobre gestión de cartera. Ten en cuenta, que según este sistema puedes alcanzar hasta un 300% de stake. Por ejemplo: si te sientes cómo siendo tu máxima exposición a una empresa de un 20% del total de tu cartera de, digamos, 50.000€, entonces:
300% stake es 20% de tu cartera, sobre 50.000€, son 10.000€, por tanto, en este ejemplo, un 100% de stake será 10.000€/3 = 3.333€. Y de aquí deducimos el 150%, 200%, y demás
Estos ajustes deberá hacerlos cada cual.
Ten en cuenta, que es extremadamente importante hacer un estudio exhaustivo debido a los peligros inherentes de promediar a la baja, que es lo que estamos haciendo. Como digo en mi post, esto lo hace de maravilla Turtle Capital, pero si nos equivocamos, estaremos poniendo el riesgo hasta un 300% de nuestro stake (o lo que consideres)
Traducido a fórmula de la columna D es:
=SI(M4<15%;0;SI(M4<25%;100%;SI(M4<35%;150%;SI(M4<45%;200%;SI(M4<50%;250%;SI(Y(M4>49%;ESNUMERO(M4));300%))))))
Columna E: objetivo técnico al alza. En esta columna entra en juego el análisis técnico como punto de entrada en la acción (si te gusta hacerlo así). Una vez hecho el análisis, colocamos el precio por encima de la cotización actual que suponga un punto importante (por ejemplo, cuando llegue a máximos históricos). Esto hará saltar una alarma (formato condicional) que veremos a posteriori.
Columna F: objetivo técnico a la baja. Es igual que la columna anterior, pero colocando un precio objetivo por debajo el precio actual (por ejemplo, allá donde se encuentre un soporte importante)
Columna G: Alamar de trading. En mi caso, no entro en valores que no estén baratos por fundamentales, ya que no suelo hacer trading o swing trading. De este modo, esta alarma está configurada para que aparezca una señal de TRADING! en dicha celda si se cumple lo siguiente:
A) Si el promedio de CAGR a 3 y 5 años es menor que 10%, aparecerá “Muy cara" y no se activara nunca
B) Si A no ocurre, y además el precio de cotización (que está en la columna U, como ya veremos) es MAYOR al de la columna E (objetivo técnico al alza), entonces se activa la alarma y aparece TRADING! en rojo
C) Si A no ocurre, y además el precio de cotización (que está en la columna U, como ya veremos) es MENOR al de la columna F (objetivo técnico a la baja), entonces se activa la alarma y aparece TRADING! en rojo
De lo contrario, aparecerá “No activada”.
Esto es muy útil porque se actualiza cada 15 minutos, y de un día para otro que miremos el radar podemos encontrar que nuestro precio está cerca de la zona técnica deseada. Estas señales de trading se pueden encontrar en ciertas aplicaciones como TradingView pero tienen un número limitado de alarmas, mientras que aquí tenemos las que queramos.
Columna H: separador negro estético
Columna I: Tesis. Esta casilla se puede usar para un enlace a Google Docs con la tésis de inversión, notas, y demás datos de interés sobre la empresa
Columna J: Pendiente curva FV. Si has leído la entrada que ya ha comentado, le encontrarás sentido a esta magnitud. Es un dato que valora la capacidad de crecimiento y de componer, y que influirá en mi criterio también a la hora de entrar o salir con más o menor stake. Tal como comento en el post de la curva de valor intrínseco, es muy peligroso salirse de compounders de alto crecimiento para entrar después, y ese timing es altamente ineficiente.
Columna K: Fair Value, calculado con tu hoja de ANALISIS propia y referenciado aquí, cuya URL ya sabemos que está en la columna AH. La fórmula será:
=SI.ERROR(IMPORTRANGE($AH4;"MAIN!B12");"")
Esto llevará a Google Spreadsheet a dejar en blanco la celda si no encuentra nada, o a importar la celda B12, de la hoja MAIN del documento cuya URL está situada en AH3 (ANALISIS) (para este ejemplo, estamos en la fila 4 del radar)
Columna L: Upside hoy (base case). Es esta columna aparecerá la diferencia, en tanto por cien, entre el valor actual y teórico (fair value) calculado en nuestra hoja de análisis. La fórmula es, en mi caso:
=SI.ERROR(-IMPORTRANGE($AH4;"MAIN!B17");""),
que quiere decir importar la celda B17, de la hoja MAIN del libro cuya URL está situada en AH4
Si me voy a mi hoja MAIN de mi documento ANALISIS y llego a B17, veo que tengo un Upside del 16%.
y que a su vez está referenciado a la hoja =VALUATION!C97
Que a su vez, en la hoja VALUATION, tiene la fórmula =(C96-C95)/C95, que es la diferencia entre el precio de cotización actual y el precio, y el precio a Fair Value, calculado como promedio de 2023 (actual)
=PROMEDIO.SI(J76:J80,"<>0"). Escoge los valores de la cuadrícula de arriba en rojo, excepto los que son cero, y hace el premedio.
Espero que todo este galimatías no sea muy abrumador. Con este ejemplo, he realizado el seguimiento completo del valor para que se vea cual es su trazabilidad, pero la realidad es que una vez que has hecho alguna referencia entre hojas, puedes hacer maravillas.
Columna M: 3Y. Este es CAGR promedio a 3Y caso base de nuestra hoja de análisis. De forma análoga a lo anterior, y sin entrar en la trazabilidad, se importa de nuestro libro de análisis a su celda correspondiente de la hoja de valoración
=SI.ERROR(IMPORTRANGE($AH4;"MAIN!B42");"")
Columna N: 5Y. CAGR a 5Y, caso base
Columna O, P, Q y R: respectivamnte 3Y y 5Y de caso optimista (trébol) y caso pesimista peligro)
Nota: las columnas de la M a la R tienen un formato condicional para poder ver visualmente sus rendimientos esperados: Más de 10% de CAGR, verde, más de 3% hasta 10%, marrón. Menos de 3%, nada.
Columna S: Last update. Esta columna es interesante. Su fórmula es:
=SI.ERROR(IMPORTRANGE($AH4;"MAIN!B4");""), donde en la hoja MAIN del libro de análisis tengo en B4 la fecha de la última actualización (como un informe de ganancias, un update de earnings, o cualquier otra cosa que implique tocar datos que modifiquen mi estimación a futuro.
Tiene un formato condicional, de forma que si han pasado 90 días desde la fecha del último update y hoy, se colorea de amarillo y negro. De esta forma, con un vistazo rápido puedo ver las empresas que llevo más de 90 días sin actualizar su documento de análisis, y debería ponerme al día con ella.
Columna T: Ticker. Para Coca-Cola, será NYSE:KO. Se puede encontrar en Google
Columna U: Cotización. Esta es la clave de todo. Se actualiza cada 15 minutos y eso nos brinda que todo el radar se mueva a tiempo real. En mi caso la tengo referenciada a la hoja MAIN de la empresa donde también tengo la cotización en la celda B18, cuya fórmula es =GOOGLEFINANCE($B$6, "Price"). También se puede referencia al ticker del propio radar, cuya fórmula sería =GOOGLEFINANCE(T4, "Price")
Columna V: cambio 24H. Es el cambio diario de cotización. Fórmula =GOOGLEFINANCE(T4;"changepct")/100
Columna W. Volumen relativo. columna interesante que nos mostrará el tanto por ciento de volumen por encima de la media. Un volumen de 150% nos indica que está un 50% por encima de su volumen medio. Un volumen del 500% puede indicar alguna noticia de especial relevancia. También tiene formato condicional. Su fórmula es =Y4/X4, que veremos ahora
Columna X e Y. (las mantengo ocultas normalmente) Son respectivamente el promedio de volumen diaro, cuya fórmula es =GOOGLEFINANCE(T4;"volumeavg"), y el volumen diario, cuya fórmula es =GOOGLEFINANCE(T4;"volume"). Su división, nos da el valor de la columna W, volumen relativo
Columna Z y AA y AB. Son respectivamente el cambio que ha sufrido la cotización del valor, en tanto por ciento, en 7 días y 30 días y en lo que va de año YTD respectivamente.
Sus fórmulas son algo más complejas. Estas columnas son prescindibles. Únicamente nos aporta una visión de lo que ha hecho la cotización en estos periodos. Recomiendo copiarlas directamente para su uso, referenciando correctamente al resto de columnas del radar
Columna Z: Cambio en 7D:
=(GOOGLEFINANCE(T4; "price")-INDICE(GOOGLEFINANCE(T4; "price"; AD4);2;2))/INDICE(GOOGLEFINANCE(T4; "price"; AD4);2;2)
Columna AA: Cambio en 30D:
=(GOOGLEFINANCE(T4; "price")-INDICE(GOOGLEFINANCE(T4; "price"; AC4);2;2))/INDICE(GOOGLEFINANCE(T4; "price"; AC4);2;2)
Donde AC y AD son auxiliares (las mantengo ocultas normalmente)
Columna AC: =HOY()-30
Columna AD: =HOY()-7
Columna AE: Permiso. Esta columna es una columna auxiliar. Su fórmula carece de importancia, pero la usaremos para pasar el ratón por encima y darle permiso a una nueva línea del radar, para que pueda acceder a los datos de dicha Google Spreadsheet. Cada vez que creemos una hoja nueva y la coloquemos en el radar, debemos de darte estos permisos. Al pasar el ratón por encima, luce así:
Columna AF: Dividend Yield. Referenciada a la hoja de análisis donde tenamos este dato. Puramente informativo. En mi caso, este YIELD ya está sumado en el cálculo del CAGR (no olvidarnos)
Columna AG. Versión actual. Es de nuevo la columna de versión, que la tengo por duplicado para alcanzar a verla por el ancho horizontal del radar.
Columna AH (última columna). Enlace, que ya lo vimos
PARTE 3: INTERPRETACIÓN
Una vez llegado al final, repasemos cual es la clave del funcionamiento:
Hacemos nuestro análisis en una Spreadsheet → arroja un espectro de varias cotizaciones esperadas futuras a 3 y 5 años, en base a 3 escenarios con la elección de sus correspondientes múltiplos (modificar al gusto. Aquí puedes decidir hacer solo 1 escenario o 5) → En este ejemplo, obtenemos un total de 6 valores CAGR: 3 escenarios para 3 y 5 años → Al cambiar la cotización en tiempo real por la función de GoogleFinance, el CAGR es dinámico y cambia cada 15 minutos, lo que afecta a las celdas correspondientes del documento de análisis, que a su vez repercute en cambios para la mayoría de columnas de radar, activando alarmas, cambiando formatos condicionales, etc. → Con un vistazo cada día, podemos identificar cambios tales como:
En la columna A: si la celda de colorea negro con texto amarillo negrita, esa acción tiene un margen de seguridad algo con respecto a su Fair Value y está en una situación de trading interesante
En la columna D: un cambio nos indica el stake a incrementar/reducir en base a una gestión de cartera adecuada por pesos
En la columna G: si la celda de colorea rojo con texto amarillo negrita, la acción ha alcanzado un punto de trading interesante
En la columna L: un valor negativo y en negro indicará descuento actual respecto a su FV
En las columnas MNOPQR: un valor en verde o naranja indicarán CAGRs a 3 y 5 años de mayor o menor porcentaje
En la columna S: un cambio a negro indicará que llevamos más de 90 días sin actualizar nuestras datos de la hoja de análisis
En la columna V: un cambio a verde o rojo indica caídas superiores a +-5%
En la columna W: un cambio a negro indica volumen usualmente elevado de más del 100% del volumen diario
En columnas Z,AA,AB: un cambio de color indica ganancias o pérdidas elevadas en el periodo indicado
En la columna AF: un cambio a naranja indica un dividend yield mayor al 3%
Consideraciones y comentarios finales
Cada vez que queramos añadir una nueva línea, debemos de copiar la fila 2 y pegarla en la siguiente fila en blanco del radar. Esto es importante porque contiene todas las fórmulas en cada una de las columnas para hacer que funcione correctamente
Todos los números, imágenes y demás expuestos aquí no siguen unos cálculos correctos. No intentes calcular, por ejemplo, el promedio del CAGR de las imágenes porque los valores están desactualizados y puede que no sean correctos, tal como he comentado. Las capturas están hechas de diversas hojas de diversas empresas por ser más representativas
El radar que descargarás tendrá números inventados orientados a ilustrar las distintas funcionalidades del radar (alarmas, formatos condicionales, etc.) por tanto no se deben considerar como criterio para inversión.
El trabajo para mantener el radar actualizado es extremadamente alto, ya que cuantas más empresas se tenga en observación, más análisis exhaustivos se deberían hacer ( y digo, deberían). Es responsabilidad de cada cual realizar los estudios necesarios para llegar a estimaciones lo más certeras posibles
A continuación, voy a dejar el enlace al radar que lo abriré a la comunidad. Al estar en modo lectura para que no sea manipulado, la forma correcta de hacerte con una copia es ir a Archivo - Hacer una copia.
Aquí el acceso al Radar:
ENLACE A RADAR
En este momento te aparecerán la mayoría de las columnas en blanco, ya que casi todo está referenciado a la columna AH (enlace) y aún no estoy preparado todavía para compartirlo porque es un trabajo de cientos de horas por mi parte. Aquí deberás colocar tu URL. Es tan sencillo como configurar tu propia hoja de análisis y vincularla al radar con su enlace y los permisos adecuados. Aplica lo aprendido aquí para crear una herramienta muy potente, pero que requiere de un mantenimiento muy dedicado, tanto como empresas tengas en el radar y cuyos análisis deben ser exhaustivos para estimar su valoración futura en base a lo que te avisará el radar
Llegando al fin, espero que te haya resultado de utilidad, al menos algunas pinceladas o truquitos para usar con Spreadsheet en tu día a día o tu propio radar.
Como trabajo gratuito, lo único que te pido a cambio es compartirlo. Te dejo mi twit con ello por si te nace un breve like + retwit.
¡Gracias!
Estaré encantado de contestar a comentarios conforme vaya pudiendo (que hay muchos estados contables que estudiar :) ) y, como siempre, cualquier propuesta de mejora será más que bienvenida
¡Buena inversión!
Bibliografía, fuentes de información usadas y lecturas recomendadas
Elaboraciones propias
Curso Avanzado Arte de Invertir de Alejandro Estebaranz
Descargo de responsabilidad: Toda la información ofrecida en el presente documento tiene un carácter meramente formativo y no representa una recomendación de compra (artículo 63 de la Ley 24/1988, de 28 de julio, del Mercado de Valores, y en el artículo 5.1 del Real Decreto 217/2008, de 15 de febrero). ManuMB no se responsabiliza del uso que se haga de esta información. Antes de invertir en una cuenta real, es necesario tener toda la formación adecuada o delegar la tarea en un profesional debidamente autorizado para ello.
Exposición: Escribí este artículo yo mismo basándome en la bibliografía aportada y en mi propia investigación, y expresa mis propias opiniones subjetivas. No estoy recibiendo compensación por ello. No tengo ninguna relación comercial con ninguna empresa cuyas acciones se mencionan en este artículo.