Plantilla seguimiento fondos

[Elab]

Dejo por aquí la plantilla de seguimiento de fondos que había compartido en el grupo @bogleheads de Telegram, con el objetivo de colaborar en el mantenimiento y mejora de la misma.

EjemploCartera Inversión v1.5

Plantilla Cartera Inversión v1.5

Errores detectados y mejoras sugeridas:

• V1.0
  • Los reembolsos no funcionan, la plantilla los trata como suscripciones. Corregido en v1.0
    Las operaciones de reembolso se indican introduciendo el importe y el nº de participaciones en negativo.

• V1.1
  • El cálculo del MWR que hace la función XIRR es directamente el tipo anualizado, sin embargo en la plantilla se incluye en la columna de Total (acumulado), en lugar de la de Anual. Corregido en v1.1
  • El cálculo de MRW de la cartera sería más correcto si se obtuviera a partir de todos los movimientos de la cartera, en lugar de como media ponderada. Corregido en v1.1

• V1.2
  • El cálculo de la TWR de un solo fondo lo más adecuado es hacerlo en base a su VL (sin tener en cuenta nuestras aportaciones/reembolsos) Corregido en v1.2. Gracias a dullinvestor
  • Corregido un error que hacia que en el gráfico "Histórico de la Cartera" sólo se mostrasen los datos de los primeros 30-40 operacionesCorregido en v1.2

...

• V1.5
  
  
  Os dejo los enlaces de la v1.5.
  
  EjemploCartera Inversión v1.5
  
  Plantilla Cartera Inversión v1.5
  
  • Se ha ampliado la plantilla para que se puede llevar el seguimiento de hasta 11 fondos (antes sólo se podían 7) Corregido en v1.5
  • Corregido el error que hacía que no se mostrase el histórico de los fondos. Se ha cambiado la fuente de datos de Morningstar a FT. Corregido en v1.5

Pendiente:

• He notado que a veces da algún que otro error por mal formato de alguna celda al hacer copy-paste en Operaciones, por eso he pensado añadir una nueva pestaña para meter todas las operaciones de compra/venta sin ningún formato, y dejar las pestañas de Cartera y Operaciones solo a modo de lectura, así no se perdería ningún formato. Lo haría retrocompatible con las versiones anteriores, para no tener que meter las operaciones desde 0. En cuanto lo tenga lo comparto por aquí.

• Meter todas las operaciones sin separar por fondos y permitir elegir el fondo con cual operar a través de un combo.

• El cálculo de TWR de la cartera como media ponderada sólo se aproxima a la TWR verdadera en caso de que el 'Asset Allocation' sea muy estable en el tiempo. En caso contrario, el resultado puede ser bastante inexacto.

• Añadir un Valor fijo a la cartera que sería el de Efectivo o Cash.

• Adaptar la plantilla para ETFs.

Puedes ayudar reportando algún error, sugiriendo alguna mejora o solucionando un error ya identificado.

Stay the course.

[brunomalone]

Lo primero agradecerte el trabajo porque la hoja es muy buena y seguro que te ha llevado mucho tiempo. He intentado meter un etf (isin: IE00B4L5Y983) y no me da el NAV, supongo que es porque saca la información de la parte de fondos de FT, y ahí no hay ETFs.
Yo no sé prácticamente nada de hojas de cálculo, pero supongo que no sería muy complicado añadirlo porque google ya te da el dato a través de =googlefinance("AMS:IWDA", "price").

[Pepeco]

¡Gracias por compartir! Muy buena.

[inves76]

Yo estaba usando esta hoja de calculo

https://www.oldschoolvalue.com/blog/inv ... readsheet/
Para usar los valores espanoles hay que poner BME:TEF por ejemplo.
Tu hoja esta muy bien. Quizas puedas tomar ideas de este ejemplo

[dullinvestor]

El cálculo de la rentabilidad TWR de cada fondo realmente la forma más sencilla y exacta de hacerlo sería a partir de la evolución de su Valor Liquidativo, sin tener para nada en cuenta los importes en él invertidos/reembolsados. Es decir, se calcularía a partir del VL liquidativo actual y del VL de la primera compra, sin más.
La fórmula que muestras en las instrucciones se utilizaría para calcular el TWR de toda la cartera, concretamente para cada uno de los periodos entre una aportación/reembolso y la siguiente (a cualquier fondo). Ésto requiere conocer el VL de todos los fondos de la cartera el día que se hace una aportación o reembolso a cualquiera de los fondos. Como no tenemos esa información, no es posible calcular el TWR de la cartera. La opción de hacer la media ponderada según la distribución inicial de la cartera creo que puede dar lugar a resultados muy alejados de la realidad, por lo que yo no la utilizaría.

[inves76]

dullinvestor, el listado de fondos que tienes en tu firma esta muy bien para localizar los ETF en euros. Si no lo esta, se podria publicar en las paginas iniciales porque es una informacion muy util. Que te parece?
No sabia que Lyxor tenia un world ETF en euros con una comision tan baja.

[ClarkBo]

[mention]dullinvestor[/mention] te dejo los honores? Cuándo lo tengas le ponemos una chincheta

[CrazyBone]

El cálculo de la rentabilidad TWR de cada fondo realmente la forma más sencilla y exacta de hacerlo sería a partir de la evolución de su Valor Liquidativo, sin tener para nada en cuenta los importes en él invertidos/reembolsados. Es decir, se calcularía a partir del VL liquidativo actual y del VL de la primera compra, sin más.
La fórmula que muestras en las instrucciones se utilizaría para calcular el TWR de toda la cartera, concretamente para cada uno de los periodos entre una aportación/reembolso y la siguiente (a cualquier fondo). Ésto requiere conocer el VL de todos los fondos de la cartera el día que se hace una aportación o reembolso a cualquiera de los fondos. Como no tenemos esa información, no es posible calcular el TWR de la cartera. La opción de hacer la media ponderada según la distribución inicial de la cartera creo que puede dar lugar a resultados muy alejados de la realidad, por lo que yo no la utilizaría.

Efectivamente, iba a comentar lo mismo. Una forma mas sencilla de implementarlo, aunque al final es lo mismo que comentas, es en una pestaña nueva cada vez que se haga una aportación/reembolso anotar una fila con Fecha | Valor Total de la Cartera | Aportación o Reembolso. Y a partir de ahí calcular el TWR como comentas a partir de los periodos.

Quizás con un botón de Actualizar Datos o algo así se podría hacer esta inserción automáticamente eh la google sheet.

[CrazyBone]

Dejo por aquí la plantilla de seguimiento de fondos que había compartido en el grupo @bogleheads de Telegram, con el objetivo de colaborar en el mantenimiento y mejora de la misma.

Plantilla Cartera Inversión

Ejemplo Cartera Inversión


Errores detectados y mejoras sugeridas:

• Los reembolsos no funcionan, la plantilla los trata como suscripciones. Corregido en v1.0
  Las operaciones de reembolso se indican introduciendo el importe y el nº de participaciones en negativo.

• Ampliar la cartera a 10 posibles valores (actualmente se permite un máximo de 7 fondos).

• Meter todas las operaciones sin separar por fondos y permitir elegir el fondo con cual operar a través de un combo.

• El cálculo del MWR que hace la función XIRR es directamente el tipo anualizado, sin embargo en la plantilla se incluye en la columna de Total (acumulado), en lugar de la de Anual

• El cálculo de MRW de la cartera sería más correcto si se obtuviera a partir de todos los movimientos de la cartera, en lugar de como media ponderada.

• El cálculo de TWR de la cartera como media ponderada sólo se aproxima a la TWR verdadera en caso de que el 'Asset Allocation' sea muy estable en el tiempo. En caso contrario, el resultado puede ser bastante inexacto.

Puedes ayudar reportando algún error, sugiriendo alguna mejora o solucionando un error ya identificado.

Stay the course.

Añadiría un Valor fijo a la cartera que sería el de Efectivo o Cash.

[Elab]

Lo primero agradecerte el trabajo porque la hoja es muy buena y seguro que te ha llevado mucho tiempo. He intentado meter un etf (isin: IE00B4L5Y983) y no me da el NAV, supongo que es porque saca la información de la parte de fondos de FT, y ahí no hay ETFs.
Yo no sé prácticamente nada de hojas de cálculo, pero supongo que no sería muy complicado añadirlo porque google ya te da el dato a través de =googlefinance("AMS:IWDA", "price").

La plantilla es sólo para fondos de momento, no la he probado con ETFs, supongo que no funciona.
Tengo pensado montarme una cartera de ETFs y seguramente adapte la plantilla para incluir seguimiento de estos fondos e incluir las comisiones de compra/venta y custodia..

[Elab]

El cálculo de la rentabilidad TWR de cada fondo realmente la forma más sencilla y exacta de hacerlo sería a partir de la evolución de su Valor Liquidativo, sin tener para nada en cuenta los importes en él invertidos/reembolsados. Es decir, se calcularía a partir del VL liquidativo actual y del VL de la primera compra, sin más.
La fórmula que muestras en las instrucciones se utilizaría para calcular el TWR de toda la cartera, concretamente para cada uno de los periodos entre una aportación/reembolso y la siguiente (a cualquier fondo). Ésto requiere conocer el VL de todos los fondos de la cartera el día que se hace una aportación o reembolso a cualquiera de los fondos. Como no tenemos esa información, no es posible calcular el TWR de la cartera. La opción de hacer la media ponderada según la distribución inicial de la cartera creo que puede dar lugar a resultados muy alejados de la realidad, por lo que yo no la utilizaría.

dullinvestor, gracias por reportar el error del MWR. Ya está corregido para que se calcule el MRW de la cartera a partir del conjunto de movimientos de la cartera, como sugerías, y no como la media ponderada de la rentabilidad de los fondos que la componen.

En cuanto al TWR de la cartera, tengo una duda. Cuando hacemos aportaciones a la cartera, normalmente hacemos suscripciones automáticas a todos los fondos que componen la cartera el mismo día (al menos yo), por lo que ya tendríamos todos sus NAV. Por lo que entiendo que si calculo TWR de la cartera sobre todos los movimientos de la cartera en vez de como media ponderada, debería dar una resultado más exacto, ¿cierto?
La única pega es unas suscripciones se hacen efectivas antes que otras, aún habiéndolas el mismo día, por lo que podría haber una diferencia de 1-3 días entre ellas. No sé si afectaría mucho al resultado..

[Elab]

Nueva versíón con las siguientes correcciones:

• V1.1
  • El cálculo del MWR que hace la función XIRR es directamente el tipo anualizado, sin embargo en la plantilla se incluye en la columna de Total (acumulado), en lugar de la de Anual. Corregido en v1.1
  • El cálculo de MRW de la cartera sería más correcto si se obtuviera a partir de todos los movimientos de la cartera, en lugar de como media ponderada. Corregido en v1.1

[CrazyBone]

El cálculo de la rentabilidad TWR de cada fondo realmente la forma más sencilla y exacta de hacerlo sería a partir de la evolución de su Valor Liquidativo, sin tener para nada en cuenta los importes en él invertidos/reembolsados. Es decir, se calcularía a partir del VL liquidativo actual y del VL de la primera compra, sin más.
La fórmula que muestras en las instrucciones se utilizaría para calcular el TWR de toda la cartera, concretamente para cada uno de los periodos entre una aportación/reembolso y la siguiente (a cualquier fondo). Ésto requiere conocer el VL de todos los fondos de la cartera el día que se hace una aportación o reembolso a cualquiera de los fondos. Como no tenemos esa información, no es posible calcular el TWR de la cartera. La opción de hacer la media ponderada según la distribución inicial de la cartera creo que puede dar lugar a resultados muy alejados de la realidad, por lo que yo no la utilizaría.

dullinvestor, gracias por reportar el error del MWR. Ya está corregido para que se calcule el MRW de la cartera a partir del conjunto de movimientos de la cartera, como sugerías, y no como la media ponderada de la rentabilidad de los fondos que la componen.

En cuanto al TWR de la cartera, tengo una duda. Cuando hacemos aportaciones a la cartera, normalmente hacemos suscripciones automáticas a todos los fondos que componen la cartera el mismo día (al menos yo), por lo que ya tendríamos todos sus NAV. Por lo que entiendo que si calculo TWR de la cartera sobre todos los movimientos de la cartera en vez de como media ponderada, debería dar una resultado más exacto, ¿cierto?
La única pega es unas suscripciones se hacen efectivas antes que otras, aún habiéndolas el mismo día, por lo que podría haber una diferencia de 1-3 días entre ellas. No sé si afectaría mucho al resultado..

Aquí lo explican bastante bien https://www.gestionpasiva.com/time-weig ... ed-return/ , probablemente la forma mas exacta de calcularlo.

Si se tuviera un asset de cash o efectivo no tendrías ningún problema porque la aportación o reembolso a cash es instantánea. Sino pues cuando se haga efectiva suscripción es cuando lo anotas como aportación.

[dullinvestor]

Para los inversores que, como tú, aportan a todos los fondos a la vez, sí podrías obtener un TWR de la cartera bastante bueno. Se trataría de calcular el valor de la cartera en ese momento de la aportación (no importaría mucho que los VLs fueran de 2 ó 3 días distintos cercanos), e ir calculando los TWR de cada periodo entre aportaciones. El problema sería para los que, como yo, aportamos únicamente al fondo o fondos que se van quedando rezagados. En este caso no podemos calcular un valor de la cartera, y no se puede obtener el TWR de la misma.
No veo ninguna solución sencilla para todas las casuísticas. La opción de mantener un asset de efectivo que sugiere @CrazyBone es buena, y no sería difícil de implementar en la hoja, pero requiere que todo el mundo sepa cómo utilizarla.
En otro rato intentaré explicar por aquí cómo se puede automatizar la grabación diaria en la hoja del valor de la cartera, que podría ser una forma de resolverlo.

[MigL]

Hola

La plantilla es sólo para fondos de momento, no la he probado con ETFs, supongo que no funciona.
Tengo pensado montarme una cartera de ETFs y seguramente adapte la plantilla para incluir seguimiento de estos fondos e incluir las comisiones de compra/venta y custodia..

Yo he probado a poner como ISIN "PHAU:AEX" (ETFS Physical Gold) y creo que no cambié mucho más, y me recupera los datos (NAV), aunque no la fecha (no me importa), puesto que con ese "ISIN" desde ft sí que recupera datos, pero la fecha se saca de morningstar por lo que veo, y claro, no es un ISIN válido... Lo mismo pasa con los datos del histórico, que también se obtienen de morningstar...

Lo he probado en la v1.0

PS: tuneando un poco la fórmula de la pestaña Datos, columna Q y J para la fila del ETF, se pueden conseguir los datos... ejemplo

[ferdb]

Hola,

He llegado a este hilo a través del grupo de Telegram.

Solo quería comentar que soy programador informático y que tengo un Google Sheets muy personalizado para mi cartera de acciones en el que he hecho funciones personalizadas, scripts, web app en html vinculada a la hoja para poder extraer/introducir datos, etc.

Como leo en el hilo que hay algo que no sabéis del todo bien cómo hacerlo, yo ofrezco mis conocimientos técnicos para poder aportar/ayudar en lo que sea.

Un saludo!

[ClarkBo]

Hola,

He llegado a este hilo a través del grupo de Telegram.

Solo quería comentar que soy programador informático y que tengo un Google Sheets muy personalizado para mi cartera de acciones en el que he hecho funciones personalizadas, scripts, web app en html vinculada a la hoja para poder extraer/introducir datos, etc.

Como leo en el hilo que hay algo que no sabéis del todo bien cómo hacerlo, yo ofrezco mis conocimientos técnicos para poder aportar/ayudar en lo que sea.

Un saludo!

Qué buena pinta lo que comentas! Seguro que puedes colaborar con la de [mention]Elab[/mention].

Tienes un template de la tuya que puedas compartir?

[ferdb]

Pues está muy en fase Beta aún, y además muy pensada para mi estrategia (B&H con dividendos). Si a alguien le sirve esta estrategia, cuando la tenga más pulida la comparto!

[ClarkBo]

[mention]ferdb[/mention], podrías compartir el código que usas para:

web app en html vinculada a la hoja para poder extraer/introducir datos, etc.

Me interesa mucho esa parte, gracias.

[JepGambardella]

Saludos a todos.

Aquí uno que viene referenciado a través del genial blog de Willyfog (Los Revisionistas). En mi caso soy comparto gran parte de vuestra filosofía Bogle, pero soy más de Harry Browne y su Cartera Permanente. De hecho monté el foro con su nombre.

Dicho esto, escribo para ver si sería posible gestionar desde esta genial plantilla un ISIN correspondiente a Bonos Alemanes, en concreto este: DE0001102341

Para empezar habría que buscar otra fuente ya que FT no lo muestra. Yo lo sacaba en mi hoja de aquí usando la función siguiente:

Código: Seleccionar todo

substitute (importXml ("http://www.boerse-berlin.com/index.php/Bonds?isin=DE0001102341";"(//span[@class='nc id_70466154_last'])"); "."; ",")/100000

Sin embargo me está costando adaptarlo a la hoja de Datos ya que los bonos, como sabéis, se expresan en el porcentaje de variación respecto de su valor de salida.

Lo dicho, si pudieséis incorporar la forma para gestionar Bonos sería genial.

Un saludo y mil millones de gracias por el buen trabajo de esta hoja.