Plantilla seguimiento fondos

Para debatir todo tipo de cuestiones, noticias y teoría de inversión Bogleheads® (no personales).
Grobi
Mensajes: 5
Registrado: Mié Ago 07, 2019 17:46

Re: Plantilla seguimiento fondos

Mensaje por Grobi » Jue Ene 09, 2020 17:12

Tienes la "solución" en este mismo hilo, un par de respuestas mas atrás.

En la celda Datos!AM20 tienes la formula para calcular el TWR, que es; =IFERROR(Cartera!D10/Operaciones!E9-1)
Pero si tu primera operación la introduces en la celda Operaciones!E08 o en la celda Operaciones!E10 la formula da error.
Para subsanarlo, debes modificar la formula indicando la primera celda con un importe para cada uno de los fondos.

MigL
Boglehead
Mensajes: 359
Registrado: Mié Ene 09, 2019 23:24

Re: Plantilla seguimiento fondos

Mensaje por MigL » Jue Ene 09, 2020 17:28

Si te pones en la casilla que no te muestra valor, puedes ver que datos d la pestaña datos está usando.
Vete a la pestaña datos y revisa las celdas aludidas
En relación con su fondo.
73s. % fondos

Avatar de Usuario
Alex M
Mensajes: 30
Registrado: Vie Ago 09, 2019 18:10

Re: Plantilla seguimiento fondos

Mensaje por Alex M » Vie Ene 10, 2020 14:52

Gracias arreglado

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Mié Feb 05, 2020 22:11

He hecho otro ajuste a mi plantilla y lo comparto:
- Insertar en la última fila de la hoja "Operaciones" una entrada que simule una aportación simbólica extremadamente pequeña a cada uno de los fondos y con fecha del día actual insertando en la celda de la fecha la función =hoy()

De esta manera, en el gráfico de la hoja de seguimiento siempre existe un último punto con fecha corriente marcando valor aportado y valor de la cartera, independientemente de cuándo haya sido la última aportación.

Ejemplo:
Captura de pantalla 2020-02-05 22.00.13.png
Captura de pantalla 2020-02-05 22.00.13.png (53 KiB) Visto 14845 veces
Hay que insertar en cada fondo:
- Fecha: función =hoy()
- Importe: un número muy pequeño (0,000001)
- NAV: un número muy pequeño
- Participaciones: un número muy pequeño

Opcionalmente puede colorear fondo y letra del mismo color para que desaparezca (lo he puesto en blanco para que se vea).
Captura de pantalla 2020-02-05 22.08.55.png
Captura de pantalla 2020-02-05 22.08.55.png (189.71 KiB) Visto 14845 veces
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

serter
Mensajes: 7
Registrado: Jue Jul 18, 2019 17:05

Re: Plantilla seguimiento fondos

Mensaje por serter » Mié Feb 05, 2020 23:21

Muchas gracias por el aporte @c@crisau.

¿Habría alguna forma de hacer un gráfico pero de evolución diaria? (en vez de que te salgan "puntos" solo cuando aportas) Creo que de esa forma se vería mejor el histórico

MigL
Boglehead
Mensajes: 359
Registrado: Mié Ene 09, 2019 23:24

Re: Plantilla seguimiento fondos

Mensaje por MigL » Mié Feb 05, 2020 23:57

Para eso tendrías que almacenar de algún modo el histórico de los valores (VL), y luego calcular el importe como número de participaciones en una fecha por el VL de esa fecha...
73s. % fondos

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Vie Feb 07, 2020 09:18

Supongo que lo más sencillo sería sacar periódicamente un registro de los valores liquidativos de alguna web y ponderarlos según qué proporción tengas de cada fondo. También se podría hacer una macro para que haga un registro con los valores de cada día, pero no se hacerlo y no estoy seguro de que vaya a dar buenos resultados ya que a veces no se obtienen bien los valores de la web de FT y además no se si se ejecutaría la macro automáticamente sin entrar en el archivo.
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

Avatar de Usuario
dullinvestor
Boglehead
Mensajes: 231
Registrado: Mié Ene 09, 2019 16:37

Re: Plantilla seguimiento fondos

Mensaje por dullinvestor » Lun Feb 10, 2020 20:36

crisau escribió:
Vie Feb 07, 2020 09:18
También se podría hacer una macro para que haga un registro con los valores de cada día, pero no sé hacerlo y no estoy seguro de que vaya a dar buenos resultados ya que a veces no se obtienen bien los valores de la web de FT y además no se si se ejecutaría la macro automáticamente sin entrar en el archivo.
En su día hice una copia de la hoja original y le añadí precisamente eso, la grabación diaria del valor de la cartera (se programa la ejecución de la macro, sin necesidad de abrir la hoja), e inserté un gráfico que recogía dicha evolución. Como puedes ver, funciona muy bien, teniendo en cuenta que no le hago ni caso, y lleva grabándose desde hace un año. Échale un vistazo, a ver si te sirve de inspiración. La grabación de la macro la hice con la utilidad que tiene la propia hoja, que captura tus acciones y las convierte en código. No hace falta saber programar.
"Investing should be dull. It shouldn't be exciting. Investing should be more like watching paint dry or grass grow. If you want excitement, take $800 and go to Las Vegas..." Paul Samuelson
Listado fondos indexados

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Vie Feb 28, 2020 22:50

dullinvestor escribió:
Lun Feb 10, 2020 20:36
En su día hice una copia de la hoja original y le añadí precisamente eso, la grabación diaria del valor de la cartera (se programa la ejecución de la macro, sin necesidad de abrir la hoja), e inserté un gráfico que recogía dicha evolución. Como puedes ver, funciona muy bien, teniendo en cuenta que no le hago ni caso, y lleva grabándose desde hace un año. Échale un vistazo, a ver si te sirve de inspiración. La grabación de la macro la hice con la utilidad que tiene la propia hoja, que captura tus acciones y las convierte en código. No hace falta saber programar.
Genial, ya lo he hecho. A ver si registra correctamente en los siguientes días!

¿Qué haces si el valor que registra es erróneo ya que no estaba obteniendo correctamente los VL de la web de FT? ¿Los eliminas con la misma macro? Sería interesante poder echar un vistazo al código por si has introducido algo adicional a lo grabado. Gracias!!!
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

MigL
Boglehead
Mensajes: 359
Registrado: Mié Ene 09, 2019 23:24

Re: Plantilla seguimiento fondos

Mensaje por MigL » Vie Feb 28, 2020 23:12

Puedes ver el código en Herramientas > Macros > Gestionar macros > tres puntos > editar secuencia de comandos.

Yo en esos casos, si es solo un día o dos, no toco nada. Si fuera varias semanas de datos erróneos, pues lo puedes corregir manualmente en la pestaña datos...
73s. % fondos

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Sab Feb 29, 2020 13:53

MigL escribió:
Vie Feb 28, 2020 23:12
Puedes ver el código en Herramientas > Macros > Gestionar macros > tres puntos > editar secuencia de comandos.

Yo en esos casos, si es solo un día o dos, no toco nada. Si fuera varias semanas de datos erróneos, pues lo puedes corregir manualmente en la pestaña datos...
El problema es que lo tiene compartido en solo lectura y no se puede acceder a su macro. Yo ya tuve que pasar por ahí para hacer la programación de la ejecución... Gracias igualmente.
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

MigL
Boglehead
Mensajes: 359
Registrado: Mié Ene 09, 2019 23:24

Re: Plantilla seguimiento fondos

Mensaje por MigL » Sab Feb 29, 2020 15:51

Yo hice una copia y luego de ahí saqué el código...

Espera, que lo busco y lo pego aquí...

EDITO: aquí lo tienes, es lo que tengo aplicado yo en mi hoja:

Código: Seleccionar todo

/** @OnlyCurrentDoc */

function GrabarValorCartera() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Datos'), true);
  spreadsheet.getRange('BN20:BO20').activate();
  spreadsheet.getRange('BN20:BO20').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('BN20').activate();
  spreadsheet.getRange('BN18:BO18').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};
73s. % fondos

Avatar de Usuario
dullinvestor
Boglehead
Mensajes: 231
Registrado: Mié Ene 09, 2019 16:37

Re: Plantilla seguimiento fondos

Mensaje por dullinvestor » Mar Mar 03, 2020 09:27

crisau escribió:
Vie Feb 28, 2020 22:50
¿Qué haces si el valor que registra es erróneo ya que no estaba obteniendo correctamente los VL de la web de FT? ¿Los eliminas con la misma macro? Sería interesante poder echar un vistazo al código por si has introducido algo adicional a lo grabado. Gracias!!!
Pues automáticamente no hago nada. Tal como ves en el código que han pegado, únicamente inserto las celdas, y pego el saldo de la cartera. Si alguna vez entro en la hoja y veo algo raro en el gráfico, simplemente borro manualmente esa fila. La verdad es que el número de errores es sorprendentemente bajo. No sé si tendrá que ver que estas automatizaciones siempre las programo para que se ejecuten de madrugada.

Inicialmente dejé la hoja abierta, modificable por cualquiera, pero entró alguien y la "rompió" un poco (sin mala intención), así que decidí cerrar el acceso, para que se mantuviera en funcionamiento. Como siempre hay posibilidad de que cada uno se haga su copia, entiendo que no hay problema.
"Investing should be dull. It shouldn't be exciting. Investing should be more like watching paint dry or grass grow. If you want excitement, take $800 and go to Las Vegas..." Paul Samuelson
Listado fondos indexados

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Mar Mar 03, 2020 10:42

Ah no sabía que se podía acceder al código previa copia! Al final llegué al mismo resultado que tú, simplemente he hecho una hoja a parte para no andar insertando filas en la de Datos. Parece que la macros me están dando este error, extrañamente solo cuando se ejecutan de manera programada:

Código: Seleccionar todo

Exception: No se puede cortar ni pegar contenido de un intervalo que cruce parcialmente unas celdas combinadas. Separa las celdas o selecciona un intervalo mayor que incluya todas las combinadas.
No le veo mucho sentido ya que es una hoja sin formato y sí funciona si la ejecuto manualmente. Intentaré sustituir mi código por el tuyo y cambiando la referencia de celdas, a ver si así funciona. Gracias a los dos @MigL y @dullinvestor
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

juannicus
Mensajes: 16
Registrado: Mié Mar 04, 2020 19:22

Re: Plantilla seguimiento fondos

Mensaje por juannicus » Mié Mar 04, 2020 19:28

Hola a todos,

he probado la plantilla y va genial, felicidades!!!

os quería preguntar un par de cosas

- Por qué no hay una multiplicación directa de participaciones por coste de participación para saber el importe final? es pesado de introducir manualmente y teniendo todo tan automatizado me sorprende, seguro que se me escapa algo, no es ese el precio real en ocasiones?
- Por qué no puedo poner más de 5 fondos? cuando he puesto 6 la pestaña operaciones se vuelve completamente loca.

Saludos y gracias

crisau
Boglehead
Mensajes: 291
Registrado: Dom Ago 25, 2019 17:31

Re: Plantilla seguimiento fondos

Mensaje por crisau » Jue Mar 05, 2020 01:34

juannicus escribió:
Mié Mar 04, 2020 19:28
Hola a todos,

he probado la plantilla y va genial, felicidades!!!

os quería preguntar un par de cosas

- Por qué no hay una multiplicación directa de participaciones por coste de participación para saber el importe final? es pesado de introducir manualmente y teniendo todo tan automatizado me sorprende, seguro que se me escapa algo, no es ese el precio real en ocasiones?
- Por qué no puedo poner más de 5 fondos? cuando he puesto 6 la pestaña operaciones se vuelve completamente loca.

Saludos y gracias

Sobre el segundo punto: si te fijas, existen columnas creadas en el lado derecho. Tienes que ampliarlas de ancho para que "quepa" el texto porque si no se descuadra.
Puedes mostrar tu agradecimiento aquí: https://ko-fi.com/crisau

Avatar de Usuario
Alex M
Mensajes: 30
Registrado: Vie Ago 09, 2019 18:10

Re: Plantilla seguimiento fondos

Mensaje por Alex M » Mié Mar 11, 2020 12:42

Buenas,

Acerca de Historico.

He observado que el Historico no corresponde con el de Morningstar. El Historico de la hoja es en base a MWR quizas, en lugar de en base a TMR?
Slds

Pablo Type R
Mensajes: 58
Registrado: Lun Jun 10, 2019 20:00

Re: Plantilla seguimiento fondos

Mensaje por Pablo Type R » Mié Mar 11, 2020 13:15

Hola,

Enhorabuena por la plantilla, está muy bien. Yo tengo un par de sugerencias, siendo consciente de que es muy fácil pedir y más complicado hacer los cambios, yo lamentablemente de Excel voy muy justo:

- Poder introducir el asset allocation deseado para cada fondo, y con eso calcular la desviación y cual sería el rebalanceo necesario. Lo tengo en otra hoja de Excel que encontré por ahí y me parece muy útil.
- Cálculo del drawdown de la cartera.

Un saludo.

Pablo Type R
Mensajes: 58
Registrado: Lun Jun 10, 2019 20:00

Re: Plantilla seguimiento fondos

Mensaje por Pablo Type R » Jue Mar 19, 2020 10:35

Pablo Type R escribió:
Mié Mar 11, 2020 13:15
Hola,

Enhorabuena por la plantilla, está muy bien. Yo tengo un par de sugerencias, siendo consciente de que es muy fácil pedir y más complicado hacer los cambios, yo lamentablemente de Excel voy muy justo:

- Poder introducir el asset allocation deseado para cada fondo, y con eso calcular la desviación y cual sería el rebalanceo necesario. Lo tengo en otra hoja de Excel que encontré por ahí y me parece muy útil.
- Cálculo del drawdown de la cartera.

Un saludo.
Aprovechando el encierro he implementado mi primera petición, es una tontería pero a mí me resulta últil. En Asset Allocation he agregado 4 campos:

Objetivo %: es el único que hay que rellenar, indicando el porcentaje de la cartera deseado para ese fondo. Por ejemplo 20%

Los demás campos se rellenan solos:

Objetivo €: es el objetivo anterior traducido a €, teniendo en cuenta el tamaño actual de la cartera.
Desviación: % de desviación actual de ese fondo respecto al objetivo.
Rebalanceo: rebalanceo necesario para alcanzar el objetivo.

Comparto un enlace con datos de ejemplo:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Slds.
Última edición por Pablo Type R el Jue Abr 15, 2021 09:44, editado 2 veces en total.

Gadrik
Mensajes: 4
Registrado: Mar Ene 22, 2019 17:18

Re: Plantilla seguimiento fondos

Mensaje por Gadrik » Dom Mar 22, 2020 17:06

Pablo Type R escribió:
Jue Mar 19, 2020 10:35
Pablo Type R escribió:
Mié Mar 11, 2020 13:15
Hola,

Enhorabuena por la plantilla, está muy bien. Yo tengo un par de sugerencias, siendo consciente de que es muy fácil pedir y más complicado hacer los cambios, yo lamentablemente de Excel voy muy justo:

- Poder introducir el asset allocation deseado para cada fondo, y con eso calcular la desviación y cual sería el rebalanceo necesario. Lo tengo en otra hoja de Excel que encontré por ahí y me parece muy útil.
- Cálculo del drawdown de la cartera.

Un saludo.
Aprovechando el encierro he implementado mi primera petición, es una tontería pero a mí me resulta últil. En Asset Allocation he agregado 4 campos:

Objetivo %: es el único que hay que rellenar, indicando el porcentaje de la cartera deseado para ese fondo. Por ejemplo 20%

Los demás campos se rellenan solos:

Objetivo €: es el objetivo anterior traducido a €, teniendo en cuenta el tamaño actual de la cartera.
Desviación: % de desviación actual de ese fondo respecto al objetivo.
Rebalanceo: rebalanceo necesario para alcanzar el objetivo.

Comparto un enlace con datos de ejemplo:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Slds.
Muchas gracias compi! Me gusta esta versión aun más, le hago copia

Responder
  • Similar Topics
    Respuestas
    Vistas
    Último mensaje