Plantilla seguimiento fondos

Para debatir todo tipo de cuestiones, noticias y teoría de inversión Bogleheads® (no personales).
Elab
Mensajes: 25
Registrado: Lun Feb 04, 2019 19:48

Plantilla seguimiento fondos

Mensaje por Elab » Mar Feb 05, 2019 23:57

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.
Última edición por Elab el Mar Ago 29, 2023 22:28, editado 6 veces en total.

Avatar de Usuario
brunomalone
Mensajes: 79
Registrado: Mié Ene 09, 2019 16:22

Re: Plantilla seguimiento fondos

Mensaje por brunomalone » Mié Feb 06, 2019 10:16

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
Boglehead
Mensajes: 175
Registrado: Vie Ene 04, 2019 17:12

Re: Plantilla seguimiento fondos

Mensaje por Pepeco » Mié Feb 06, 2019 12:21

¡Gracias por compartir! Muy buena.
"There seems to be some perverse human characteristic that likes to make easy things difficult." - Warren Buffet. Hazlo simple.

inves76
Boglehead
Mensajes: 176
Registrado: Lun Feb 04, 2019 19:00

Re: Plantilla seguimiento fondos

Mensaje por inves76 » Mié Feb 06, 2019 14:54

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

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

Re: Plantilla seguimiento fondos

Mensaje por dullinvestor » Mié Feb 06, 2019 19:03

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.
"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

inves76
Boglehead
Mensajes: 176
Registrado: Lun Feb 04, 2019 19:00

Re: Plantilla seguimiento fondos

Mensaje por inves76 » Mié Feb 06, 2019 22:50

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.

Avatar de Usuario
ClarkBo
Boglehead
Mensajes: 1226
Registrado: Vie Ene 04, 2019 16:40

Re: Plantilla seguimiento fondos

Mensaje por ClarkBo » Mié Feb 06, 2019 23:11

@dullinvestor te dejo los honores? Cuándo lo tengas le ponemos una chincheta ;-)

CrazyBone
Mensajes: 8
Registrado: Lun Ene 07, 2019 19:19

Re: Plantilla seguimiento fondos

Mensaje por CrazyBone » Jue Feb 07, 2019 07:58

dullinvestor escribió:
Mié Feb 06, 2019 19:03
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
Mensajes: 8
Registrado: Lun Ene 07, 2019 19:19

Re: Plantilla seguimiento fondos

Mensaje por CrazyBone » Jue Feb 07, 2019 07:59

Elab escribió:
Mar Feb 05, 2019 23:57
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
Mensajes: 25
Registrado: Lun Feb 04, 2019 19:48

Re: Plantilla seguimiento fondos

Mensaje por Elab » Lun Feb 11, 2019 00:20

brunomalone escribió:
Mié Feb 06, 2019 10:16
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
Mensajes: 25
Registrado: Lun Feb 04, 2019 19:48

Re: Plantilla seguimiento fondos

Mensaje por Elab » Lun Feb 11, 2019 00:41

dullinvestor escribió:
Mié Feb 06, 2019 19:03
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..
Última edición por Elab el Lun Feb 11, 2019 08:05, editado 2 veces en total.

Elab
Mensajes: 25
Registrado: Lun Feb 04, 2019 19:48

Re: Plantilla seguimiento fondos

Mensaje por Elab » Lun Feb 11, 2019 00:47

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
Mensajes: 8
Registrado: Lun Ene 07, 2019 19:19

Re: Plantilla seguimiento fondos

Mensaje por CrazyBone » Lun Feb 11, 2019 08:47

Elab escribió:
Lun Feb 11, 2019 00:41
dullinvestor escribió:
Mié Feb 06, 2019 19:03
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.

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

Re: Plantilla seguimiento fondos

Mensaje por dullinvestor » Mar Feb 12, 2019 20:38

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.
"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

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

Re: Plantilla seguimiento fondos

Mensaje por MigL » Mar Feb 12, 2019 22:27

Hola
Elab escribió:
Lun Feb 11, 2019 00:20
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... :arrow: ejemplo
73s. % fondos

ferdb
Mensajes: 3
Registrado: Mié Feb 13, 2019 21:38

Re: Plantilla seguimiento fondos

Mensaje por ferdb » Mié Feb 13, 2019 21:43

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!

Avatar de Usuario
ClarkBo
Boglehead
Mensajes: 1226
Registrado: Vie Ene 04, 2019 16:40

Re: Plantilla seguimiento fondos

Mensaje por ClarkBo » Mié Feb 13, 2019 21:49

ferdb escribió:
Mié Feb 13, 2019 21:43
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 @Elab.

Tienes un template de la tuya que puedas compartir?

ferdb
Mensajes: 3
Registrado: Mié Feb 13, 2019 21:38

Re: Plantilla seguimiento fondos

Mensaje por ferdb » Jue Feb 14, 2019 17:48

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!

Avatar de Usuario
ClarkBo
Boglehead
Mensajes: 1226
Registrado: Vie Ene 04, 2019 16:40

Re: Plantilla seguimiento fondos

Mensaje por ClarkBo » Sab Feb 16, 2019 12:08

@ferdb, 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
Mensajes: 1
Registrado: Sab Feb 16, 2019 13:01

Re: Plantilla seguimiento fondos

Mensaje por JepGambardella » Sab Feb 16, 2019 13:08

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.

Responder
  • Similar Topics
    Respuestas
    Vistas
    Último mensaje