Herramienta para crear carteras
Buenas tardes,
Hoy os traigo una herramienta con la que llevo trabajando unas semanas. Consiste en una hoja de Excel (analisis cuantitativo) vinculada a una hoja de Google Sheets (actualización de datos) que nos permitirá a través de unos simples pasos crear una cartera optima para un nivel de riesgo establecido. Os explico como funciona primero y luego os enseño como configurarla.
En primer lugar vemos que tenemos varias hojas, en la primera explicare brevemente las instrucciones de la hoja, de todos modos a continuación la explicare con mayor detalle. La segunda hoja contiene información relativa a productos que tenemos en seguimiento, la idea de esta hoja es introducir todos aquellos activos que nos interesa e ir probando carteras hasta dar con la idonea para nosotros. En mi caso, son todo fondos excepto 2 ETF´s y bueno toda esa información la he sacado de Morningstar (como veis la "ficha" esta hecha para fondos ya que principalmente es donde voy a invertir). Es importante que no toqueis las columnas que hay aqui, en especial las 2 últimas ya que estan vinculadas a otras celdas muy importantes, si aun asi queréis modificarla os aconsejo que arrastreis esas celdas al principio de la tabla y ya podais modificar lo que querais. Debajo de la tabla veis que pone "Productos actualizados", esto significa que todos los productos de la tabla estan vinculados al historico de datos, es decir, que están todos lo productos bien.
La siguiente hoja es de las más importantes, ahi será donde surgirá la magia y donde se crearán las carteras por lo que es importante que no toqueis mucho. No obstante, esta hoja tiene sus limitaciones y es que solo se pueden crear carteras de 7 activos como mucho. Esto se debe a que para el analisis cuantitativo hacen falta unas matrices, y no se como hacer para que cuando cambio el número de productos se cambie tambien el tamaño de la matriz (por lo que si alguien sabe como solucionar esto sería genial). Lo único que habría que cambiar aquí sería la tabla de valores liquidativos o precios de cierre en caso de acciones y ETF´s (el nombre lo pone arriba), habría que cambiar los activos por los que queréis que se cree la cartera e, importante cambiar la fecha por la de la última fecha que aparezca en la hoja histórico de datos de los activos seleccionados.
Por ejemplo, en mi caso he elegido los activos que aparecen ahi, pues tendríamos que ir a la pestaña Histórico de datos y seleccionar la última fecha que encontremos, en este caso 21 porque el Seilner es su última fecha (no se si me he explicado bien, espero que si). Copiariamos desde el 21/8/19 hasta abajo seleccionando 283 fechas y la pegariamos en la columna Fecha de la hoja anterior.
Una vez pegados automaticamente se cambiaran los valores liquidativos correspondientes a esa fecha. A la derecha veremos un apartado donde aparecen las matrices de las que hablaba antes y una tabla de covarianzas (aunque no le hagais mucho caso ya que luego hay una herramienta que nos servirá mejor para analizar la cartera antes de comprarla). Cuando ya tengamos los valores liquidativos tendremos que ir a Datos --> Solver y se nos abrirá una ventana.
En esta ventana donde pone Establecer objetivo tenemos dos opciones, o ponemos la varianza o ponemos la rentabilidad. Si ponemos la varianza lo ideal sería darle a min (es lo que tengo yo) y nos creará una cartera que minimice la varianza, para hacer esto es importante que en varianza objetivo no pongamos nada, si no, ese será nuestro mínimo (a menos que sea inferior a la varianza de la cartera de mínima varianza, es igual, quitarla y ya está). La otra opción será poner una varianza objetivo, en Establecer objetivo pondríamos la rentabilidad y en Para: pondriamos Máx.
Esto es lo que veremos en la parte derecha de la hoja, una vez activemos la función Solver nos dará unas ponderaciones, simplemente tendremos que cambiar donde pone 32.000,00 y poner nuestro patrimonio a invertir (es aconsejable ponerlo antes ya que una de las restricciones de la función Solver es que la cantidad invertida sea al menos igual a la inversión mínima).
Posteriormente, en la siguiente hoja llamada Resumen introduciremos la fecha del dia con los últimos valores liquidativos (la primera fecha que aparece en la tabla de antes), los productos que hemos elegido, las ponderaciones y ya tendremos la cartera creada.
Finalmente, tenemos la herramienta mas útil (a mi parecer) de toda la hoja, consiste en un análisis super completo de la que sería nuestra cartera. Esta herramienta ha sido cortesía de @Triath , quien dejo en un post su herramienta y que os dejo por aquí para que le deís las gracias. Para obtener este análisis tenemos que copiar y pegar en la primera columna los activos en los que vamos a invertir, copiar y pegar los códigos de Morningstar que para quien no sepa lo que es, es el código que aparece al final de un enlace al entrar en un fondo a través de Morningstar, por ejemplo: Si queremos el código del fondo Vontobel Mtx Sust Emmkts, lo buscamos en Morningstar (lo mas fácil es buscar el ISIN y pegarlo en el buscador) y una vez estemos en su ficha nos vamos al enlace: http://www.morningstar.es/es/funds/snapshot/snapshot.aspx?id=F00000MOJF y aquí tenemos el código Morningstar: F00000MOJF (lo ideal es copiarlo y pegarlo en la ficha en la segunda pestaña). Y por último copiamos y pegamos el importe o el porcentaje que hayamos invertido, es indiferente. Eso si, IMPORTANTE el autor de la herramienta recalca mucho la importancia de solo poner codigos en la segunda columna y solo poner números en la tercera columna. Al final quedaría algo así:
Y ahora solo quedaría hacer lo que pone en la celda gris oscuro, dar click --> Cntrl+C --> Ir al navegador y pegarlo ahí como si fuera un link.
Finalmente, las dos últimas pestañas son las que recaban todos los datos automáticamente (o deberían ya que lo cierto es que es lo que más problemas me ha dado). Es importante que sepáis como funcionan estas hojas ya que son MUY IMPORTANTES (y las que más trabajo me han llevado), la hoja de Actualización de datos está conectada a una hoja de Google Sheets donde se descargan los datos diarios de Morningstar gracias a una herramienta que me enseñó @crossbower (y que creo que creó él) y cuyo enlace a su página os dejo aquí (es en inglés, eso si). Para vosotros poder tener actualizadas vuestras hojas teneís también que modificar esta hoja, la cual os dejó aquí. Para editarla tenéis que haceros una copia y guardarosla en vuestra unidad. Una vez en vuestra unidad, es muy sencillo, cambiais los fondos (como el formato es el mismo podéis copiar y pegar lo del excel) y activais la macro "Actualizar datos" y luego la macro "Pegar solo valores" y listo. Ahora para vincular la hoja de Google Sheets a la de Excel tenéis que seguir los pasos que aparecen en esta web (os redirijo para que quien ya sepa hacerlo no se trague todo el rollo de la explicación). Estos datos podéis copiarlos en la pestaña "Actualización de datos" o crear una nueva y borrar esa, como queráis.
Y finalmente, una vez tengáis los datos en vuestro excel simplemente tendréis que pasaros esos datos a la pestaña "Histórico de datos" y ya estaría todo.
Ahora que ya conocéis la herramienta, no os asustéis, el trabajo hay que hacerlo ahora, luego es todo muchisimo más fácil y automátizado. Para configurar la hoja lo primero que tenéis que hacer es obtener los datos históricos, los podeis encontrar en Investing facilmente (yo en mi caso los descargue de Renta4) ya que Google Sheets solo nos dará el último dato, el resto tenemos que ponerlo nosotros. Una vez tengamos los productos elegidos, sus datos históricos preparados y su ficha rellenada, estaremos preparados para disfrutar de esta herramienta.
Espero que haya quedado todo claro, cualquier cosa no dudéis en preguntarlo.
Herramienta para crear carteras
Un saludo,
Kevin