Excel 2.0 para controlar tu rentabilidad y cartera: Novedades y Uso

En la pasada entrada (link) te mostraba un Excel para llevar el control de tu rentabilidad, poder compararte con índices, y de paso para tener un mínimo seguimiento de tu cartera de inversión. Este Excel ha tenido una muy buena acogida, especialmente para gente que está empezando, o más veteranos pero que no estaban llevando hasta ahora el control de la rentabilidad (ni con TWR ni MWR). He recibido varias sugerencias de mejora o dudas en los comentarios del blog, por twiter y directamente por email, y esto me  motiva a revisar el Excel, ampliar algunos aspectos, y a tratar de explicar mejor o con algo más de detalle cómo se usa este Excel.

En primer lugar presentaré las novedades de este Excel 2.0 vs la versión original (1.0) y luego daré una pequeña guía de uso paso a paso para que no te sientas perdido.

¿Me acompañas  en este viaje por el Excel?

Novedades del Excel en la versión 2.0

En esta semana he hecho una serie de mejoras (eso espero), no muy profundas, pero que dan más funcionalidad a aquellos que quieren usar este Excel no solo para analizar rentabilidades sino como plantilla para llevar el control total de la cartera. Estos son los principales cambios:

1-Posibilidad de tener subcarteras. Además de la visión global de tu cartera, podrás categorizar parte de los activos de tu cartera como pertenecientes a una subcartera específica que te interesa tener desagragada para ver cómo funcionan diferentes estrategias. Por ejemplo, puedes tener subcarteras de Acciones, Inversión indexada, Gestión Value, etc.. lo que se te ocurra. Así podrás ver si tu estrategia implementada con la subcartera de acciones, lo hace mejor que tu subcartera de fondos indexados, o qué tal respecto a tus fondos de gestores value. Es algo que uso yo, y a largo plazo quiero saber, cuál de estas tres estrategias (me gustan todas) lo hace finalmente mejor.

 

Esta posibilidad se consigue añadiendo el nombre de tus subcarteras en la nueva columna «C» de la pestaña «DatosInversiones». Aquí abajo puedes verlo:

La distribución entre subcarteras la tienes en el primer gráfico tipo tarta de la hoja «Resumen». Si no te gusta esta funcionalidad, puedes no usarla, que el resto del excel seguirá funcionando igual.

2-Distribución Renta variable (RV) vs Renta fija (RF). Es muy importante saber cómo distribuyes tu cartera entre este tipo de activos RV (acciones, fondos de RV, etc), y RF (Bonos, fondos de RF, depósitos, etc). Para ello en la pestaña de «DatosInversiones» en la columna «D», se anota el porcentaje de RV de cada activo. Si son acciones pones un 100%, si es un fondo de renta fija pones un 0%, y si es un fondo mixto del 60% en RV y 40% en RF, pones un 60%. El resultado de tu cartera distribuida en RV vs RF lo tienes en el tercer gráfico tipo tarta de la hoja «Resumen».

3-Control de Comisiones. Cuando invertimos es muy importante que no nos frían a comisiones de compra/venta cuando compras directamente acciones o ETF, ni tener muchos gastos de gestión en los fondos que utilizamos. Si compramos acciones en paquetes de unos 1000€, y nos cobran 15€ de comisión de compra, de entrada estamos pagando un 1,5%, y si vendemos y compramos con frecuencia (algo nada recomendable), o si compramos acciones en pequeños paquetes, el porcentaje aumentará. Si hacemos buy&hold puro con acciones, estas comisiones de compra con el paso de los años irán desapareciendo. En el caso de los fondos, no ves un flujo de dinero que te quiten, pero internamente el fondo te va restando sus comisiones internas. Todo esto es muy importante, y puede ser el motivo del mal rendimiento en tus inversiones. Por ello he incluido estas comisiones anualizadas, en la ficha «Resumen» (F7 y G7), para que lo tengas muy presente.

4-Detalles cartera (número acciones, precio medio compra, …). Junto con el listado de los activos en cartera y su gráfico de barras, hemos anotado información importante de interés. Saber, además del valor de cada inversión, el número de acciones o participaciones, el precio medio de compra, el valor actual de ese activo, y si lo tienes en verde o rojo (con ganancias o pérdidas), es necesario.

Además esta tabla es ahora dinámica y contiene la posibilidad de filtrarla, en un desplegable, con lo cual puedes ver la cartera global por defecto, pero si así lo eliges puedes ver solo tu cartera de acciones, o fondos value, o fondos indexados.

Nota: La tabla dinámica solo está implementada, parcialmente ya que parte de las columnas son estáticas. Soy nuevo en tablas dinámicas, y no he sabido cómo incluir los campos precio medio y actual con cálculo preciso en la tabla dinámica. Por ello en esa parte uso celdas estáticas que van bien. Si hay voluntarios se agradecerá, si no así es aceptablemente funcional también.

5-Liquidez. Para aquellos que incluis la liquidez como parte de la cartera global (aquella reservada para invertir cuando haya oportunidades de mercado), decir que es posible gestionarlo. Normalmente esa liquidez la tenéis en una cuenta corriente con algo de rentabilidad (p.ej. Coinc al 0,3%, Pibank 0,5%, etc.). Se consigue gestionarlo en este excel simplemente anotando el dinero en la cuenta como una inversión en la pestaña «DatosInversiones». Hay que usar de ticker «EUR» y de precio unitario «1€», así si tienes 5000€ pones que tienes 5000 participaciones. Si la cuenta da intereses, pues lo anotas como un dividendo en la pestaña «DatosDividendos» con ticker «EUR» y tantas acciones/participaciones como euros en la cuenta. Esos intereses los puedes anotar mensualmente, o para que no sea pesado, creo que vale con una vez al año.

6-Pestaña de rentas por dividendos. Como hay gente que le encantan los dividendos y otros que no los quieren por considerarlos poco eficientes, he dejado la parte de los dividendos en una nueva ficha separada llamada «Rentas». De esta forma también queda más limpia las ficha de «DatosDividendos» conteniendo solo los datos crudos y sin gráficas.

7-Otros. En la ficha de rentabilidades he quitado la fila de rentabilidad MWR ya que esta rentabilidad, al no tener que usar las cotizaciones de tu cartera en el tiempo mes a mes, no es apropiada para medir intervalos tan pequeños. Es una rentabilidad muy buena, pero creo que con verla solo en la ficha «Resumen» es suficiente. Así, la pestaña «Rentabilidad»  se centra exclusivamente en rentabilidad TWR.

Guía de uso paso a paso

Bien, ya tienes el Excel en tus manos, lo has echado un vistazo, entiendes un poco por encima su funcionalidad, pero ha llegado la hora de empezar a rellenarlo, quitando los datos del ejemplo y poniendo tus datos de inversión. ¿Por dónde empiezo?, te preguntarás. Yo lo haría siguiendo estos pasos:

1) Modifica las pestañas de Datos (DatosInversiones y DatosDividendos): El sitio natural para empezar a añadir tus datos es en la pestaña de DatosInversiones. Te recomiendo que insertes nuevas filas, y por el momento no borres las del ejemplo. Para añadir tus compras históricas pasadas, o las nuevas que hagas en el futuro, solo tienes que ir insertando nueva filas en blanco a continuación de la última rellena. Tras insertar la fila, deberías concentrarte en anotar las celdas de las columnas con fondo amarillo. El resto de las columnas contienen fórmulas, y por tanto se rellenan solas una vez que añadas tus datos en las celdas amarillas.

El mismo proceso lo repites en la pestaña de Datosdividendos. Si  en algún momento alguna fórmula no se replicase al insertar una nueva fila, hazlo tú manualmente (copiando y pegando o mejor arrastrando la esquina derecha inferior de la celda con la fórmula hacia abajo donde están las nuevas filas creadas). Y eso es todo al meter tus datos. Una vez tienes tu fuente de datos en estas dos pestañas ya tienes la mayor parte del trabajo duro hecho.

2) Actualiza las cotizaciones de tus inversiones (una vez a fin de mes). A fin de mes debes ir a la página de googledrive y copiar la columna de cotizaciones y luego pegarla en la columna «E» de la pestaña de «Cotizaciones» de tu Excel. De esta forma actualizarás el valor de tu cartera en «Resumen» (C7). Dale con el ratón en «Datos>Actualizar todo» para asegurarte que las fórmulas se refrescan.

3) Anota el valor de tu cartera («Resumen», C7) en la última fecha de la hoja de «Rentabilidad». Una vez refrescadas las cotizaciones, ya tienes la inversión de tu cartera actualizada. Por tanto a fin de mes, debes coger el valor de tus inversiones : casilla C7 en pestaña «Resumen», y pegas esos euros en la casilla B81, o siguientes, de la pestaña «Rentabilidad».  Esta es la única forma de que tengas un historial del valor de tu cartera y puedas calcular la rentabilidad TWR.

Debes hacer lo mismo en las columnas I, M y Q de «Rentabilidad», para poner la cotización de los indices Ibex, S&P500, y MSCI World, que puedes obtener siguiendo los enlaces en esa misma pestaña. Así te podrás comparar, en términos de TWR, con estos índices (u otros que quieras usar). Es como lo hacen los profesionales.

Nota: Si llevas muchos años invertido, y no dispones de este historial del valor de la cartera, no podrás calcular el TWR pasado en el excel, salvo que los regeneres tirando de precios históricos en internet de cada activo que tengas (tarea muy dura). Sin embargo, si tienes los datos completos en DatosInversiones y Datos Dividendos, estás de suerte, ya que podrás calcular la rentabilidad MWR pasada y futura (la que se muestra en la pestaña «Resumen» en H7 e I7).

4) Visualiza tu cartera (verifica y analiza). Una vez has hecho lo anterior, deberás comprobar que no hay errores en fórmulas, si surgiesen deberías revisar que las fórmulas están en los campos adecuados y que no te faltan datos.

Si todo se ve bien, ahora es el momento de analizar cómo vas, cómo es tu diversificación, cómo van tus dividendos crecientes, analizar las comisiones anuales que pagas y si puedes mejorar, ver tus rentabilidades históricas (no importa mucho a corto plazo), pero tras 4 o 5 años invirtiendo ya debes poder sacar conclusiones de si tu cartera lo hace bien o si los índices te baten. Disfruta de este momento, una vez al mes, y de forma un poco más intensa tras finalizar un año.

 

El enlace al Excel nuevo (2.0) lo tienes en la en la zona de recursos. Y dejo la versión anterior (1.0) por si a alguien le gustaba más la otra.

Update (1/Mayo/2019): Os aviso que hay una nueva versión 3.0 disponible en la sección de recursos. Aquí tenéis la entrada (excel 3.0) que explica los cambios.


Bueno hasta aquí la entrada de hoy, espero que te hayan gustado las novedades. Si te parece útil el Excel, tienes dudas o quieres colaborar en mejorarlo, solo tienes que decirlo.

45 respuesta a “Excel 2.0 para controlar tu rentabilidad y cartera: Novedades y Uso”

    1. Hola Damian, las aportaciones periódicas debes incluirlas una a una en una nueva fila tras la compra. No queda mas remedio pues tienes que anotar el precio y numero de participaciones que finalmente te diga tu broker.
      Antonio

  1. Buenas!! Voy a animarme a usar este magnifico excell para hacer un seguimiento de mis inversiones. Quiero rellenarla desde el principios (finales de 2015) y sera una tarea muy pesada por la cantidad de movimientos de compraventa que hice, sobre todo al principio… Mi duda es que si en el futuro decides actualizar el excell con nuevos apartados y funcionalidades, habría alguna forma de exportar todos los datos de golpe del excell antiguo al excell actualizado?
    Un saludo y gracias por todo!!

    1. Hola Fran,
      Será pesado hacerlo si tienes muchos movimientos, pero si este excel representa mejor el seguimiento respecto a lo que ya tenías merecerá la pena.
      Si en el futuro se modificase el excel, solo te afectaría si los cambios son por incluir una nueva columna en la pestaña de datosInversiones o datosDividendos, y eso no sería mucho problema, ya que bastaría con que tu insertes esa nueva columna en tu excel y pongas los datos nuevos necesarios (que igual son solo una fórmula). Pero creo que ya está todo o casi todo recogido actualmente y poco cambio hace falta. Si el cambio es en las pestañas resumen o dividendos, puedes copiar y pegarlas esas nuevas pestañas en tu excel actual, o hacer la actualización a la inversa: coger los datos de tu excel actual y pegarlos al excel nuevo. Parece lioso, pero no es gran dificultad.
      Saludos

  2. Hola,

    Gracias por tu gran aportación. Tengo una duda al respecto de cómo modificamos aquellos valores (Tikets) que ahora mismo no están en tu lista y por otro lado si conoces la posibilidad de obtener los datos sin que tengamos que entrar el último día del mes para recoger la información de las últimas cotizaciones del mes.

    Muchas gracias.

    1. Pedro, Las empresas nuevas se insertan añadiendo una nueva línea en la tabla de googlesheets, y ahí poner el ticker nuevo y resto de campos, y te aseguras que la fórmula de precio esté puesta en la fila, y ya está.
      Lo de poder coger las cotizaciones del final de mes, sin hacerlo ese mismo día lo estudiaré y ya os digo.
      Antonio

    1. Pedro,

      He actualizado el googlesheets «Cotizacionescompleta» (https://docs.google.com/spreadsheets/d/1pSs2mC38gaKbqUYBkmMv7s1sbFWaVJmVJS7GvdXpyDk/edit?usp=sharing)
      para que se pueda poner la fecha que quieras (por ejemplo la fecha de fin de un mes pasado), y así saber la cotizacion al cierre de ese día. Era una de tus sugerencias, que realmente es muy útil.

      Funciona para acciones, pero para fondos y ETFs, aun no se exactamente cómo hacerlo, pues hay que hacer webscrapping y elegir los valores de precio deseados de una lista no muy regular y dispersa (el primer intento de FT no me ha funcionado). Es más complejo.

      Espero que con solo las acciones ya te ayude bastante
      Antonio

    1. Laertes,

      Estupendo, cuando puedas, seguro que aportas ideas. Y abuso un poco de ti y te pido más:

      Si sabes de webscrapping, estaría bien para el googlesheets poderlo actualizar a cualquier fecha pasada, como le digo a Pedro arriba (en acciones ya va, pero fondos no).

      O si sabes como coger las cotizaciones directamente desde Excel, sin requerir el uso del googlesheets, estupendo. yo en su época lo hacía así desde excel, pero no se conectaba bien el excel a las fuentes de datos (web externas), o tardaba mucho, y no me gustaba el resultado. Desde entonces uso la solucion de googlesheets, que va algo mejor, aunque sea manual en parte con el copy pega, y aparezca el «loading» muchas veces. Cualquier cosa que mejore esto será bienvenido.
      No se si el plugging de power BI en excel mejora esto de importar datos de cotizaciones y fondos.

      A ver si se te ocurre algo. Muchas gracias.
      Antonio

      1. Ya he mirado más en profundidad la hoja y me parece una mejora apreciable respecto a la primera versión. En cuanto a tus preguntas ya lo siento pero me temo que mis conocimientos de Excel no llegan a tanto, al menos de momento.

        Cambiando de tema, ¿tu libro a partir de qué edad es recomendable? Estoy pensando en regalar uno pero no sé si será apropiado.

        1. Hola,
          Bueno, gracias. Ahora estoy auto aprendiendo a hacer webscrapping en excel y espero de sus frutos pronto.
          Respecto al libro, a partir de 13 años lo podrian leer, ya depende del niño. A mi me lo revisó una niña de 11 años y pudo con el, pero era especialmente lista. Lo puedes regalar a sobrinos, que luego a los padres si son abiertos al leer cosas tabú de dinero, les suele encantar también .
          Gracias por todo

  3. Enhorabuena por el excel, un gran trabajo que me está siendo muy útil, llevaba tiempo buscando algo similar.

    Una pena que no se actualicen los precios de los fondos, espero que entre todos podamos corregirlo, porque es realmente útil.

    Una duda que tengo, cuando en lugar de cobrar dividendos he cobrado acciones (programa dividendo elección del Santander), supongo que hay que insertar una nueva fila en adquisiciones, no en dividendos, a coste cero, ¿no es así? Es que veo que has puesto el scrip dividendo como dividendo, pero esto solo es en el caso de cobrar derechos, no de recibir acciones, ¿no es así?

    Muchísimas gracias, da gusto leer tu blog, para mi el número uno de la comunidad, pocas entradas pero todas de muy alta calidad

  4. Hola Daniel,

    Me alegra saber que te gustan los contenidos y que el excel encaje con lo que buscabas. todos hemos pasado por esa fase. Los precios en la hoja de google sheets se actualizan en el día, y los de días pasados también en acciones. Pero falta, como dices, que se actualice en fondos (tarea en stand-by). El motivo, es que he pensado que esa parte de los precios en googlesheets es posible que ya no nos haga falta, pues estoy trabajando en la version 3.0 para poder leer los precios directamente desde excel, sin página de google de apoyo. Ya leo acciones, fondos y ETFs, del día actual y de días pasados. Cuando lo tenga listo ya lo publico, pero hay que depurarlo, pues hay mucho código vba, y debo ver posibles problemas prácticos. Pero si yo logro que funcione todo, será una gozada.

    Lo otro que dices de las acciones recibidas en scriptdividend, correcto, si vendes los derechos (tanto a mercado como a la empresa) lo pones en DatosDicidendos, y si coges las acciones lo pones en DatosAcciones en una nueva fila a precio de compra cero.

    Si ves alguna funcionalidad que falte, no dudes en decirlo. Vuestros comentarios ayudan, y me dan ideas de mejoras.

    Saludos

  5. Buenas!
    Pues nada, llevo unos días trasteando con el excel y tengo algunas dudas, a ver si me podéis ayudar…
    En la hoja Resumen, como añado y/o modifico las subcarteras disponibles? no consigo hacerlo no se porque.
    En la hoja Rentas, el gráfico de evolución de dividendos por mes, que pasos tendría que seguir para añadir mas años?
    Mis conocimientos de excel son básicos, de ahí estas dudas.
    Un saludo y gracias por todo!

  6. Hola Fran,
    Para las subcarteras, en la hoja Resumen, no deberías hacer nada, salvo quizás dar a la tecla F5 para actualizar (o con botón derecho raton sobre la tabla dinamica de activos). Los datos de las subcarpetas se actualizarán una vez que los pongas el nombre d elas subcarpetas que deses en la columna correspondiente de la hoja «Datos Inversiones».
    En la hoja de Rentas, simplemente copia y pega una fila de la tabla de meses (desde enero a media mensual) en otra fila libre, y revisa las fórmulas para asegurarte que están bien.
    Espero te sirva

  7. Buenos días,
    Perfecto, muchas gracias por contestar.
    Seguiré operando con ella para ponerla al día con todos mis movimientos.
    Un saludo y gracias!

  8. Buenos días,

    Muchas gracias por poner estos recursos a disposición.

    Mi conocimiento de Excel es básico, pero al empezar y rellenar algunas líneas, no consigo que lo blanco se complete solo.

    Tengo un Mac y al abrir el archivo me sale un mensaje y le doy a “Habilitar macros”. Pero no se si este puede ser el problema o en Mac funciona en principio perfectamente.

  9. Tom,
    Supongo que estás insertando filas en las tablas en DatosInversiones o DatosDividendos. En este caso si tras seleccionar la fila completa, le das a insertar fila, debe aparecer una fila nueva con las fórmulas ya copiadas. Si por lo que sea no se han copiado las fórmulas, hazlo manualmente arrastrando la fórmula de arriba (por ejemplo como hacen aqui: https://www.youtube.com/watch?v=Z2t_VWF8JVE)
    No debería haber problema con el Mac, si usas microsoft excel (si es otra versión de mac compatible con excel no lo se).
    Si no dominas excel como para poder insertar, pegar filas y columnas, y entender lo básico de las fórmulas, te recomiendo que veas antes algún tutorial por internet, hasta que cojas un poco de soltura.
    Saludos, Antonio

  10. Hola Antonio, muchas gracias por el Excel, está super completo. Voy a coger ideas para mejorar el spreadsheet que tengo para el seguimiento de cartera. Comentarte 2 cosas que te puede interesar:
    – Cotización de fondos y ETFs: lo más cómodo es usar el add-on µFunds que te permite actualizar la cotización a partir del ISIN: https://www.mufunds.com/
    – Data Studio para dashboard, uso esta herramienta gratuita de Google para montar el dashboard a partir de la información del Excel/Spreadsheet. Quizás es un poco más de lío porque hay que “normalizar” los datos para poder sacar el jugo a la herramienta. Pero bueno es una idea.
    Saludos!

  11. Hola Pasape,

    Muchas gracias por tus sugerencias de mejora. No las conocía, pero veo que son muy buenas opciones.

    Lo del Data Studio, tiene buena pinta, parece seguir un enfoque como el de Power BI de Microsoft, que también me habéis sugerido alguno. Por el momento, no los uso, pues serían una capa final de integración de fuentes de datos y visualización online en un dashboard.

    La de mufunds, lo acabo de probar, y ya lo usado en el googlesheets que comparto, pero no veo que se puedan coger cotizaciones del pasado. Esta funcionalidad de precios pasados es la última incorporación del googlesheets que comparto, que ya funciona para acciones y ETFs, pero para fondos no se todavía hacerlo, y mufunds no lo soporta. He dejado un aviso en el github de mufunds para ver si incluyen esas funcionalidad. Si lo ampliasen estupendo.

    En cualquier caso, en la version 3.0 del excel ya tengo resuelto el acceso a acciones, fondos, ETS, y divisas, y todo descargable pulsando un botón desde el excel (sin usar googlesheets de apoyo), tanto para precios actuales como pasados. Al final con el excel y código VBA hago lo mismo que muFunds, conectarme a web externas (Yahoo, FT, o investing) y coger los precios de las web, por web scrapping (precio actuales y históricos).

    Si alguién tiene más ideas, o formas de mejorar la forma de llevar el control de las acciones, bienvenidas sean. Un aspecto importante, y que no he resuelto, es llevar el control de plusvalias fiscales y de mercado (algo que se necesita si se traspasan fondos).
    Un saludo
    Antonio

    1. Genial, esperaremos con ganas la versión 3.0!
      Otra idea que estaría bien es crear una pestaña donde se «definen» los productos (acciones, fondos o etf) con los campos: nombre, ticker, % RV, tipo, subcartera, moneda, mercado y cotización actual. Con esto, conseguimos no tener que repetir todo esto en los datos de inversiones, ya que simplemente con VLookup se podría coger automáticamente.
      Gran trabajo!

      1. Pasape, es buena sugerencia esa, lo estuve pensando poner, pero al final pensé que mejor no poner más pestañas. Al fin y al cabo repetirlo es fácil, pues es solo copiar lo que ya tienes en una compra anterior. Ya veré si me decido a hacerlo.
        Gracias

  12. Hola, muchas gracias por compartir tu trabajo! Estoy intentando adaptar la excel a mi cartera modificando alguna cosilla (por ejemplo, la rentabilidad la he puesto semanalmente, 1 mes me parece mucho). De momento me ha surgido una duda con “liquidez” en Datosdeinversiones porq no se exactamente como funciona. Hay que poner la liquidez que tienes al final todos los periodos?? (se me hace engorroso). Seria mas interesante reflejar el ingreso y a partir de ahi, que la excel te indique la liquidez gracias al resto de transacciones.
    Gracias de nuevo, con lo que vaya viendo lo compartire aqui.

    1. Edu,
      Te deseo que te quede muy bien el excel adaptado, y con TWR por semanas.

      Lo de la liquidez, en principio en DatosInversiones, si tienes una liquidez constante, solo tienes que anotarlo una vez, como una compra de liquidez. Los intereses de esa liquidez irían en Datosdividendos (que puedes agrupar en un solo pago a fin de año). Si tu liquidez cambia, y pasas de tener 5.000 a 8.000 euros, tendrías que anotar otra nueva compra de liquidez de 3.000€. Y si baja, tendrías que vender parte. Creo que hacer todo esto es a lo indicas que te parece un lío.¿no? Pero, no hay que hacerlo todas las semanas, solo cuando cambie tu liquidez.

      Si se te ocurre otra forma de hacerlo, en tu proceso de adaptación de la plantilla, ya nos cuentas…

      Saludos
      Antonio

      1. Humm…., si, cuando la tenga hecha la dejo por aqui. La verdad es q esta muy bien y no te creas q voy a innovar mucho, pero claro, he de adaptarlo. Por ejemplo, yo tengo otros brokers y las comisiones las calculo automaticamente segun las tarifas de mi broker. A mi no me dicen el coste bruto para sacar la comision, me dicen las comisiones para calcular el coste bruto. Lo de la liquidez es un poco extraño, para mi eso serian ingresos. Por ejemplo, si yo abro una cuenta y meto 20000€, tengo 20000€ de liquidez, pero segun vaya comprando o vendiendo acciones, la liquidez va variando y tal como esta concebida la hoja, creo que no te calcula la liquidez de cada momento. por eso digo, que lo que tu indicas como «liquidez», para mi serian ingresos. Cuando la termine la cuelgo o la paso.
        Aunque aun tendre que hacerte alguna pregunta,çGracias

  13. Edu,
    Ya te entiendo. Tú quieres que la liquidez sea el resultado de restar un dinero ingresado de lo que vayas invirtiendo, y que al invertir, esta liquidez se vaya modificando sola. Para ti la liquidez = colchon – aportaciones + reembolsos. La liquidez es para ti lo que aun te queda por invertir. Lo veo bien.

    Yo sin embargo lo planteo como que la liquidez es otro activo más, es decir, un dinero que tengo invertido (en un deposito a corto, o en una cuenta remunerada, o en un fondo monetario) y que me da ciertos intereses. Cierto, que tiene la desventaja, que si compro acciones/fondos, entonces debo vender liquidez (una doble operación).

    Quedo a la espera de ver como lo implementas. Seguro que queda bien tal y como dices…

    gracias
    antonio

    1. Bueno, de momento ya tengo todos mis datos metidos, pero me encuentro con un problema estupido q me impide continuar. A ver si alguien me da una idea. En la hoja rentabilidad, pretendo q la columna B me la de automaticamente mediante la formula «=SUMAR.SI(TablaDatosInversiones[Fecha Compra];»<@Fecha]";TablaDatosInversiones[Rendimiento mercado neto (euros)])", pero me da resultado 0. Es curioso, porque si no fuera tabla y pusiera en vez de "<@Fecha]" una fecha concreta, por ejemplo "<12-12-2018", si que me da el resultado correcto.

  14. Edu,
    Si lo que quieres es rellenar la columna B completa «Valor de la inversión» (para todos los meses de cada fila) siento decirte que no es posible, ya que para poder hacer esto necesitas tener el historial de las cotizaciones de todos los productos mes a mes, y así obtener el «Valor de la inversion» de tu cartera en cada uno de los meses.
    Los datos que tienes en la TablaDatosInversiones son solo para las cotizaciones del día que hayas cargado en la pestaña de cotizaciones. Si no tienes el historial de cotizaciones pasadas, deberás regenerarlas, e irlas poniendo una a una en las casillas de la columna B de «Rentabilidad». Si son acciones, con GoogleSheets se puede hacer relativamente rápido.
    ¿Es eso lo que querías hacer?
    Antonio

  15. Buenas tardes, un gran trabajo. Yo he realizado unos cambios por ejemplo en “Resumen” el precio medio de compra he cambiado la formula porque la que estaba no estoy seguro de si está bien o mal.

    Yo creo que el precio medio de compra de cada Valor es la de dividir todas las acciones entre el total de compras de ese valor.

    Podrías facilitarme la formula de googlefinance para añadir una columna de “capitalización”

    Un saludo

  16. Asoma,
    Gracias por tu comentario. El precio medio de compra es la suma del coste de todas tus compras dividido por el numero total de acciones que posees (moneda/acciones)

    En el excel lo calculo asi, con numerador igual al coste total, y denominador el numero de acciones totales:

    =SI.ERROR(SUMAPRODUCTO((TablaDatosInversiones[Nombre]=B43)*1;TablaDatosInversiones[nº unidades compradas (acciones o participaciones)];TablaDatosInversiones[Precio pagado por unidad (moneda)];(TablaDatosInversiones[Operación abierta (cotizando)]=VERDADERO)*1)/SUMAPRODUCTO((TablaDatosInversiones[Nombre]=B43)*1;TablaDatosInversiones[nº unidades compradas (acciones o participaciones)];(TablaDatosInversiones[Operación abierta (cotizando)]=VERDADERO)*1);»»)

    Creo que está bien. No le veo el fallo.

    Respecto a la capitalizacion de googlefinance se consigue poniendo «marketcap», tal y como se explica aqui:
    https://support.google.com/docs/answer/3093281

    Saludos

  17. Hola buenas tardes. Lei en algún comentario tuyo que estabas preparando una tercera versión mejorada del excel

    Para cuándo prevees que pueda estar listo? Muchas gracias de antemano, un saludo

  18. Hola Daniel,
    La versión 3.0 está casi lista, de hecho me gustó tanto la simplicidad de la 2.0 que preparé para vosotros, que estos días he estado pasando mi propia hoja excel, que había crecido demasiado y de una forma más desorganizada, a la nueva versión. Ahora mismo mi propia cartera al completo ya la estoy siguiendo con la versión 3.0. La estoy refinando, incorporando alguna funcionalidad extra que tenía en la antigua, y cuando la deje un poco más a mi gusto, y depure posibles errores que vea en estas semanas próximas, ya la podré medio vaciar de mis operaciones personales y compartir con vosotros.
    Espero que os merezca la pena la espera.
    Un saludo
    Antonio

  19. Hola, felicidades por la hoja excel.

    En cuento a los script y pago de dividendos mediante acciones, si coges acciones está claro que sería como una compra a precio 0.

    Pero si vendes los derechos, en realidad estas bajando el precio de la acciones por lo menos hasta el año 2017 que cambio la tributación. A partir de ese año serían como dividendos.

    Yo creo que debería ser recogido en la hoja de DatosInversiones, de forma que el precio medio del valor contemple el valor de los derechos vendidos.

    Un saludo
    Álvaro

    1. Hola Álvaro,
      Cierto todo lo que dices. Si vendes los derechos, y fue antes del 2017, depende de si lo vendías en el mercado o a la empresa. Si la venta era a la empresa, los consideras como dividendos, pero si los vendías al mercado habría que rebajar el precio medio de compra. En un caso lo pones en DatosDividendos, y en el otro en DatosInversiones, sí, cierto como lo dices. Sin embargo ahora (desde el 2017 en adelante), ya la venta de derechos siempre tributa como un dividendos y te retienen el 19%, por tanto va a DatosDividendos.
      Para los que necesiten verlo más despacio, aqui lo explica en detalle: https://www.bolsamania.com/declaracion-impuestos-renta/fiscalidad-de-los-dividendos-en-forma-de-acciones/
      Un saludo, y gracias por contribuir.
      Antonio

  20. Hola, lo primero felicitarte por tu gran trabajo y darte las gracias por compartir un Excel que me está ayudando mucho…

    Voy a ver si soy capaz de explicarme… cuando añades en la pestaña DatosDividendos un ingreso por Scrip y este es parcial (es decir has vendido una parte de los derechos y otra los has convertido en acciones), tal y como tienes la fórmula de sumaproducto de la columna W de la pestaña DatosInversiones el dato obtenido es incorrecto y superior al real. La función es correcta si has vendido todos los derechos pero no lo es si solamente has vendido una parte de ellos.

    Creo que llevo razón así que quizás los ingresos por scrip dividend habría que calcularlos y sumarlos en otra columna, o modificar la fórmula, … no estoy muy seguro, si puedes compruébalo.

    Un saludo.

    1. Hola Jose Antonio,

      Gracias por el aviso. Es posible que tengas razón. A ver si lo miro en otro momento con más calma. Si se te ocurre una forma de solventarlo, encantado de oir propuestas.

      Saludos y gracias por compartir.

      Antonio

  21. David555,
    Haz la venta parcial duplicando la fila. Tendrás una fila con 180 acciones, por tanto al mes cuando vendas, solo tienes que copiar una nueva fila debajo (dando doble-click) y poner en la primera fila 90 acciones (las que no vendes), y en la fila de abajo las otras 90 (las vendidas), y en este último caso, le pones la fecha de venta. Y ya está, has anotado tu venta parcial.
    Antonio

Deja un comentario