Excel 3.0 para controlar rentabilidades y tu cartera: Nuevas funcionalidades

Os presento la última versión del excel para controlar tu rentabilidad y hacer un seguimiento de la cartera de inversiones. Este excel es la versión 3.0 de una serie de versiones previas (1.0 y 2.0), que poco a poco han ido añadiendo funcionalidades. Actualización: la versión actual, tras algunos retoques y mejoras es la versión 3.5.


Nota: La entrada actual explica las novedades incrementales de las versiones 3.X respecto a la inmediatamente anterior (la 2.0). Por tanto, te recomiendo que antes de leer esta entrada te leas las  dos entradas anteriores explicando las versiones  1.0 y 2.0 (Excel v1 y Excel v2). Así entenderás mejor la entrada actual y la filosofía que hay detrás de esta serie de excels. De hecho, te recomiendo no solo leer las entradas previas, sino también los comentarios que hay en ellas resolviendo dudas.


Dicho esto seguimos:

La principal novedad que tenía pendiente, era poder adquirir automáticamente las cotizaciones de los activos (Acciones, Fondos, ETFs) directamente desde el excel, sin más que apretar un botón. Bien, esta funcionalidad ya está operativa, y así nos evitamos tener una hoja de googlesheet de apoyo. También el excel soporta ahora la inclusión de operaciones con opciones (PUTs y CALLS) para el que lo necesite, e incluso planes de pensiones.

Además ya que la actualización del excel me ha pillado en plena campaña de la renta IRPF, he incluido funcionalidades para facilitarte la recogida de datos a declarar a hacienda (ventas de activos, recuperar doble retención dividendos, etc.). Ya puestos hemos incluido un planificador para ver si te interesa vender algo en pérdidas para pagar menos impuestos en la declaración de la renta del próximo año. Son varias cosas, por tanto si te apetece, te lo voy contando.

Voy al grano, pues asumo que ya conoces las versiones anteriores, y sabes su filosofía de uso.

Antes de ver las novedades individualmente, solo quiero que veas las diferentes pestañas, tal y como se ven desde la página principal de resumen:

Como se ve hay bastantes nuevas, pero algunas de ellas, si no te hacen falta, las puedes borrar directamente (Rendimiento, Rentas, Primas, Aflora, IRPF1, IRPF2) pues no hay ninguna pestaña que dependa de ellas. Las que deben estar sí o sí, son las clásicas (Resumen, Rentabilidad, DatosInversiones, DatosDividendos y Cotizaciones).

Cotizaciones automáticas

En la pestaña de «Cotizaciones», simplemente tienes una tabla dinámica que lista los activos que tienes actualmente en cartera (en DatosInversiones). Para obtener la cotización, debes primero elegir la fecha del día de interés (normalmente el último día de mes), debes escribir las divisas de interés en la columna K, y posteriormente pulsar al botón «Actualizar cotizaciones».

Tras un rato (en el cual el código busca por internet diferentes proveedores de datos), las cotizaciones aparecen junto con la fecha a la que corresponden y la moneda asociada a la cotización. Una marca o tick verde indica que todo ha ido bien. Si sale de color amarillo o rojo, es que la fecha no es exactamente la que deseabas (algunos fondos tardan en actualizar sus cotizaciones un par de días), o que directamente no se ha encontrado (ticker erróneo o no disponible).

Estamos haciendo webscrapping en diferentes sitios web. Para que funcione bien, en la columna C el tipo de activo debe ser uno de estos: ‘Accion’, ‘ETF’, o ‘Fondo’. Y el ticker debe ser el correcto para cada lugar que visitamos para leer la cotización. Los fondos de inversión los leemos de Financialtimes.com , por tanto el ticker debe ser el de este sitio web. Las acciones y los ETF se leen de YahooFinance.com, por tanto usa estos tickers. La información de los tickers, y tipo de activos, como sabes, es algo que se escribe en la pestaña «DatosInversiones», en la columna correspondiente. En la pestaña actual de «Cotizaciones» esta información te debe salir automáticamente, tras dar al botón de «Actualiza cotizaciones».

Espero os funcione bien, y que os resulte interesante no depender de una hoja externa de googlesheets, como era necesario en las versiones anteriores. En cualquier caso, si te gusta más poner las cotizaciones copiando y pegando de googlesheets, podrías seguir haciéndolo así.

IRPF

En la pestaña IRPF1, tienes información fiscal sobre los dividendos que has cobrado. En la columna de la izquierda los dividendos brutos que llegan a España, y las retenciones practicadas. Si tienes broker español, puedes comprobar que lo que te informa hacienda en la información fiscal, coincide con estos datos. Y si tienes un broker extranjero, hacienda no está informada y debes meter estos datos tú en la casilla correspondiente (el total de dividendos y retenciones es suficiente).

En la tabla de la derecha de la misma pestaña IRPF1, tienes la información del exceso de retención que te han hecho en el extranjero (hasta un 15% puedes recuperar). En el total de la columna K tienes el dinero que te tiene que devolver hacienda.

En la pestaña IRPF2 tenemos lo relacionado con las ganancias de patrimonio, por venta de acciones o fondos, o por las primas cobradas si operas con Opciones (PUTs y CALLS). Creo que la imagen lo explica todo mejor:

Planificador para aflorar pérdidas

Esta funcionalidad, de la pestaña «Aflora», es bastante útil pues te permite planificar si puedes pagar menos el año que viene en tu declaración. Si tienes algunas acciones o fondos, que ya no te gustan para tenerlos en cartera, o que ves más potencial a otros activos, y están en pérdidas podrías venderlos. El planificador te dice exactamente cuánto debes vender para pagar lo mínimo posible.

Básicamente calcula el 25% de los rendimientos del capital (intereses, dividendos, retrocesiones) esperados para el año actual, mira si tienes ya alguna ganancia de patrimonio (por vender acciones o fondos con plusvalías), y te sugiere vender activos con pérdidas de este mismo montante (casilla de fondo amarillo). Además en la tabla inferior te ordena tus activos de mayor a menor pérdidas, para que los tengas a mano. En el propio excel se entiende mejor:

Rentabilidad TWR multi-subcartera

En la pestaña de «Rentabilidad» ahora se ha ampliado bastante para que puedas seguir no solo la rentabilidad TWR del total de tu cartera, sino que puedas ver la rentabilidad de cada una de las subcarteras. Por tanto verás muchas tablitas (no te asustes), para acciones, fondos value, fondos indexados, opciones y para liquidez. Cada tablita incluye arriba la fecha inicial en que comienzas a invertir, así eliminamos errores de pasadas versiones (donde había que editar a mano en la fórmula esa fecha). Ahora se hace solo.

Recuerda que en esta pestaña de «Rentabilidad» debes anotar en las columnas con fondo amarillo el valor de cada una de tus subcarteras a fin de mes. Como explico en el excel con un texto de AYUDA: «Debes <Fijar> el valor de tus subcarteras en la fila del fin de mes actual (columnas amarillas: I, P, W, Ak y AN)». Para ayudarte en la tarea (que puedes hacer manualmente), he puesto una macro que se activa si haces doble-click en algún sitio de la fila correspodiente a la fecha de fin de mes deseado, y así se <fijan> los valores (sustituye las fórmulas de abajo de las tablas por un valor fijo). Cuando lo tengas que hacer lo entenderás.

Evolución del Rendimiento

En la nueva pestaña «Rendimiento» se muestra una gráfica donde puedes ver en euros el dinero invertido, el valor de tu cartera, y el rendimiento obtenido. A diferencia de lo que muestra la pestaña «Rentabilidades», que muestra porcentajes de ganancia en TWR, aquí puedes ver los euros ganados o perdidos, que al fin y al cabo es lo más importante para tu bolsillo. Es el complemento ideal a la rentabilidad MWR, que también se enfoca en lo que ganas realmente.

La cartera global incluye la liquidez como parte de los activos. Se tiene en cuenta para su rentabilidad. Cuando ahorras dinero, y decides aumentar tu liquidez para invertir el dinero lo añades en «DatosInversiones» en el activo de liquidez. En este caso, cuando decides invertir realmente, comprando activos con parte de la liquidez, esta operación queda reflejada en el gráfico de la pestaña «Rendimiento» con las barras verdes verticales. Así puedes apreciar cuándo inviertes más, y si tus nuevas compras son periódicas o las aumentas en las caídas de cotización de tu cartera.

Nuevas columnas en hojas de Datos

Para incluir las nuevas funcionalidades para IRPF, Opciones y Cotización captada de diversos sitios, no nos ha quedado más remedio que incluir alguna columna más a las tablas en DatosInversiones y DatosDividendos.

Hay una columna nueva, la F etiquetada «NIF o ISIN (para IRPF)», que como indica es solo información que viene bien para que queden mejor identificadas las operaciones de cara a hacienda, y que podamos contrastar la información fiscal con la que generamos nosotros en las pestañas del IRPF.

La columna E etiquetada «Ticker/ISIN (o compromiso opciones)», contiene el ticker que hay que poner a cada activo para que nuestro buscador de cotizaciones funcione bien. Perdonadme la chapuza, pero he aprovechado, ya que el precio de  las opciones no las buscamos ni usamos, para reutilizar en esta misma columna los euros a los que nos comprometemos cuando vendemos alguna PUT. Necesitaba más campos para el tema de las opciones, que para un activo normal, y por eso esta columna E, tiene uso dual. A los que no uséis opciones este campo lo usais solo para meter tickers y no os molesta nada.

En la columna D etiquetada con «Tipo activo (RV, RF, Mixto)», ya no pongo el porcentaje de RV del activo. Me limito a etiquetar si es RF, RV, o Mixto. Creo que así es suficiente, y me ayuda a automatizar la tabla dinámica de la ficha principal «Resumen».

A los que usen opciones, decir que hay que insertar una fila especial en DatosInversiones (la que veis en la última fila de la imagen anterior) para que de esta forma la liquidez total se vea reducida cuando hay opciones vendidas. es la forma de reducir la liquidez libre para inversiones, cuando se aumenta la exposición a opciones. Si la liquidez para invertir que aparece en la pestaña «Resumen» en algún momento aparece negativa, es que tienes más compromisos que liquidez, y por tanto te estás apalancando.

En «DatosDividensos» se han actualizado la columna de tickers y NIF para IRPF, igual que he explicado arriba. El resto creo que no ha cambiado nada, salvo alguna fórmula interna retocada, etc.

Bueno, se me olvidaba. Hay una funcionalidad extra con macro. Pulsando con doble-click sobre una fila existente de cualquiera de estas tablas, se abre una ventada que te pregunta si quieres insertar una nueva fila, o directamente insertar copiando todos los contenidos de la fila sobre la que haces doble click.

Por último decir que si tuviese moneda extranjera como activo (cuenta en dólares o libras), podría ponerlo como un activo más. No lo he hecho pero se podría. El tema de cómo gestionar compras y ventas, etc está fuera del propósito de esta entrada, pero lo han tratado muy bien en entradas recientes del blog amigo «Inviertiendo Poco a Poco» (enlace1enlace2) y también dan detalles de cómo rellenar los apartados de la declaración de la renta.


Actulizarse de versiones anteriores a la 3.0

Para pasar tus datos de la versión 2.0 al 3.0 no hay una herramienta, hay que hacerlo a mano, pero espero que no se tarde mucho. Antes de dar el salto, asegúrate que las nuevas funcionalidades te merecen la pena.

Básicamente lo que hay que hacer es actualizar los tickers de activos (en vez de los tickers de Google finance, ahora se usan los de Yahoo finance y Finantial times para fondos). Además debes pasar todos los datos en DatosInversiones, las columnas de fondo amarillo a las nuevas columnas con igual fondo, dejando las columnas de fondo blanco con las fórmulas sin tocar. Creo que así, no debe haber muchos problemas.


Update (3 Mayo 2019): Versión 3.1 ya disponible que incluye también acceso a las cotizaciones de planes de pensiones. De esta forma opera con 4 tipos de activos: Acciones, Fondos, ETFs y Planes de Pensiones.

Update (9 Junio 2019): Si en la página de cotizaciones, tras pulsar el botón «Actualiza Cotizaciones» os sale un error sobre una línea que pone «Dim html As New HTMLDocument», significa que no tenéis incorporada una librería de Microsoft que permite al programa usar la clase HTMLDocument para leer las páginas web con la cotización. En ese caso la solución es sencilla: Entra en la ventana de Visual Basic donde te sale el error, y pincha en «Herramientas»>»Referencias», y en el desplegable marca con el ratón la librería «Microsoft HTML Object Library». Una vez cargada en tu equipo, debería ya funcionar.

Update (16 Junio 2019): Se ha actualizado a la versión 3.2 que incluye alguna mejora para que la pestaña resumen muestre los activos disponibles a fecha de la cotización elegida. Cambios se describen el en propio excel versión 3.2.

Update (30 Junio 2019): Los cambios de versión 3.2 a la versión 3.3 son:
Se incluye en la pestaña «Rentas» en la parte inferior una tabla extra con seguimiento de los dividendos cobrados por cada empresa en el último año vs. el siguiente esperado, su crecimiento, y el peso de los dividendos en la cartera por cada acción (control de si el dividendo depende de pocas empresas o este está diversificado). Botón que carga el DPA esperado según yahoo finance.
Se ha corregido la fórmula para calcular las comisiones en la tabla superior de la pestaña «resumen», ya que había un error.
Para hacer todo lo anterior se han añadido nuevas funciones principales y auxiliares en el código VBA.

Update (8 Julio 2019): La macro para el botón de carga de DPA, «ActualizaDPA», no estaba insertada en la version 3.3, y por tanto no funcionaba. Cambios disponibles desde la versión 3.3b.

Update (25 Agosto 2019):  Se han corregido errores del código VBasic que provocaba error al leer las cotizaciones de los fondos de inversión en los meses de Agosto y Diciembre. Simplemente se ha cambiado el string ‘Agu’ por ‘Aug’, y ‘Dic’ por ‘Dec’ en la función llamada «CargaPrecioActivo_FinancialTimes» en módulos. Además hemos añadido una mini guía en una nueva pestaña que resume el uso del excel. Cambios disponibles desde la versión 3.4.

Update (Enero 2020):  Se han corregido múltiples pequeños errores en las fórmulas y las macros, para hacer que los cálculos sean más precisos. Se mejora la pestaña de Rentabilidad para que «fijar» valor de la cartera a fin de mes sea más intuitivo, y solo haya que hacer doble-click, sin no tocar nada más. Las fechas de la columna A se calculan solas una vez hayas medido compras en DatosInversiones. En la página de plantillas excel ya está disponible la version 3.5 que lo corrige.


Forma de uso

Antes de terminar por lo importante que es quiero recordar el orden en el que hay que usar el excel. Pasos:

  1. Rellena tus datos de inversiones en «DatosInversiones» y los dividendos cobrados en «DatosDividensos» (recuerda el doble click para copiar o crear nuevas filas). Fondos amarillos indican datos que debes escribir tú, y fondo blanco se calcula solo con una fórmula.
  2. En «Cotizaciones» por una fecha (no más antigua que 30 días), y pulsa el botón.
  3. Revisa en «Resumen» que se vean cifras razonables y sin errores
  4. A fin de mes, en «Rentabilidades» pon abajo en las tablitas de las subcarteras el valor que tiene cada una de las subcarteras (recuerda que tienes una macro para ayudarte).
  5. Usa el resto de pestañas informativas «IRPF» para recoger los datos para hacer la declaración; «Aflora» para analizar si hay que vender algo, etc…
  6. Reflexiona, sobre tu cartera mirando la pestaña resumen ¿Pagas muchas comisiones? ¿Lo hacen mejor los fondos indexados que los activos? ¿Tu cartera de acciones va bien? Te puedes pasar un buen rato viendo cosas y sacando conclusiones.

Despedida

Entrada agotadora. No es de fácil lectura, sino más bien una guía para no perderte usando el excel. Por tanto ya te veo, manejando el excel en una pantalla del ordenador, y esta entrada, u otras entradas pasadas del blog, cerquita a la vista para aclararse con las posibles dudas, errores o cosas que no cuadren bien.

Espero que os resulte interesante este excel que lo puedes descargar en la sección correspondiente de recursos. Si te ha gustado no dudes en compartirlo en tus redes sociales. Y si ves errores o cosas no correctas, encantado de ver si podemos mejorarlo.

165 respuestas a «Excel 3.0 para controlar rentabilidades y tu cartera: Nuevas funcionalidades»

  1. Interesantísimo. Una maravilla. Es compatible con Google Drive. Al tener Mac el archivo no es compatible. Realmente una lástima. Sugerencia de algún programa que me adapte el archivo a Mac? Un saludo y gracias

    1. Kike,
      Me alegro que te guste.
      No es compatible con google drive, Está hecho con microsoft office (excel), pero si lo quieres usar en Mac, deberías instalar microsoft Office para Mac, que veo que existe, aunque yo uso Windows y no se que tal funcionará en Mac.

    2. Hola Koke. Recordarte que en un Mac se puede también tener Windows aparte de IOS. Yo comparto los dos sistemas operativos en mi Mac.

  2. Muy interesante. Lastima que no sea compatible con Google drive.
    Tengo Mac, sabes si hay al programa que me convierta el archivo Zip o alguno compatible con Max? Muchas gracias y perdona las molestias

  3. El archivo está comprimido en zip, pero debes descomprimirlo y encontrarás un xlsm que es un formato de Excel (Microsoft Office). Necesitas un programa Excel para abrirlo y usarlo. Google drive no lo importa bien, y no funciona.

  4. Antonio, primero darte la enhorabuena porque es un currazo que flipas, y segundo que no se si se puede, yo el dividendo de VOD, GSK y BATS lo pillo en acciones, lo he puesto en la pestaña de dividendos, pero no se suman a los datos ya metidos en la pestaña de compra. Como se hace?? Muchas gracias y felicitarte otra vez por el trabajo

  5. Que gran trabajo, gracias por compartir. A modo de sugerencia, sobretodo para carteras con acciones, pondría también una columna en la que figure el sector de cada acción.

    Un saludo

  6. Lo primero gracias, es un archivo magnífico. Tengo una duda, he empezado añadiendo una de mis acciones, en este caso Red Electrica, pero no aparece en la tabla dinámica de Cotizaciones, ni se actualiza el precio en la pestaña datos de inversión. ¿cómo hago para filtrar los valores de la tabla dinámica? No puedo filtrar…..selecciono todas pero me siguen apareciendo las que tienes de ejemplo. Si elimino una fila de las que vienen de ejemplo, sí desaparece de la tabla dinámica, pero si añado filas no aparecen los nuevos valores

        1. Pues si no os importa mandadme el excel tal y como lo teneis y reviso y arreglo lo que no funcione y por qué. Me gustaría ver el problema, pues yo uso la misma version 3.0 para mi propia cartera y no tengo ese problema. Mandadmelo a mi email y lo estudio.

          1. Ya me funciona. Comento por aquí lo que hice de forma diferente por si fuese de ayuda.

            La primera vez que lo probé, había añadido varias filas en la pestaña DatosInversiones haciendo doble click sobre la anterior, y seleccionando «Si» en la ventana emergente (creando filas vacías). Después completé una de esas filas.

            En esta ocasión, añadí una sola fila, seleccionando «No» en la ventana emergente (copiando la anterior), y fui cambiando las celdas amarillas de la fila copiada. Y esta vez la tabla ya se actualizó correctamente en la pestaña Cotizaciones.

            Gracias.

    1. Daniel,
      Ya he mirado tu excel. Para conseguir que funcionase he tenido que tocarte estas cosas:

      1) Poner en «Formulas» > «Opciones para el cálcuco» > automático (estaba en manual)

      2) Te he insertardo de nuevo la compra de REE, ya que tenias copiada mal la fila y no contenía todas las fórmulas. Para insertar la nueva fila no insertes en la última fila, hazlo sobre cualquier otra, y mejor con el doble click copiando una anterior del mismo mercado (luego sobre la nueva creada escribes tus datos particulares de la empresa), asi te aseguras crear una fila totalmente correcta.

      3) Los tickers de las acciones españolas deben tener «.MC» de coletilla. Te he puesto REE.MC en vez de solo REE como ticker. Si te va a dar un error cuando des al botón calcular cotizaciones.

      Con estas modificaciones ya lo tienes operativo (te devuelvo tu excel «arreglado»). Siento que no sea todo tan directo. Procuraré el finde retocar alguna cosilla para que os de menos problemas de este tipo y sea todo más directo (yo como ya tengo mecanizado su uso, no veía estos problemas).

      Espero que ahora puedas seguir importando valores sin problemas. Si no es así, insiste y quéjate, que así me sirve para dejarlo más robusto.
      Saludos
      Antonio

  7. Felicidades por este gran trabajo, es justo lo que estaba buscando.

    Mi duda es la siguiente: en Self Bank tenemos una comisión por cambio de divisa (0,30%). ¿Cómo podemos reflejar dicha comisión en las operaciones de compra/venta en la tabla? ¿Sería correcto hacerlo de la siguiente forma?:

    En la columna “Cambio divisa (Moneda/EUR) en compra” introducimos el tipo de cambio aplicado por Self Bank (incluye la comisión).
    En la columna “Comisión compra (euros)” le sumamos el valor [0.003*Coste bruto total de la compra (euros)] a la comisión.

    Muchas gracias y un saludo.

  8. BUenos días. Sigo rellenando este magnífico archivo, la verdad es que da gusto.

    Una pregunta concreta: por ejemplo compré 1200 acciones del Santander, y años más tarde vendí solo 650 acciones. De cara a reflejarlo en la tabla de datos de inversión, la fila en la que anoté la compra de las 1200 acciones, en la columnas de venta se refiere a las 1200 acciones.

    La solución sería desdoblar las 1200 acciones en 2 compras, una de 650 acciones que después vendí, y otra entrada de 550 acciones que aun conservo y que tendría todo igual en las columnas de compra.

    Supongo que es así, verdad? gracias

      1. Hola,

        Estaba buscando en comentarios a ver si estaba mi duda y esto es lo más parecido que he encontrado así que la inserto aquí. Si la situación es del tipo 10 compras sucesivas de 100 y luego una venta/traspaso de, pongamos, 250, ¿habría que crear una línea de compra a 0 para poder meter la venta/traspaso?

        1. Para vender 250 en este caso, vendes las 2 primeras compras completas. Luego la tercera de 100€ la divides en dos lineas de 50€, y vendes una de estas. Al final tienes 3 lineas vendidas y 8 lineas vivas por valor de 750€.
          Espero haberte resuelto la duda.

          1. Gracias por tu pronta respuesta.

            Entiendo que donde pones euros estás pensando en acciones o la unidad que sea, por que el valor de venta es lo que se actualiza.

            Por otro lado, en caso de traspaso de fondos… he visto en otra pregunta que recomendabas cambiar el comercializador de las compras en vez de hacer una venta para el caso de que se «traspase» acciones, aunque se pierde la trazabilidad ¿Sería recomendable hacer lo mismo en caso de traspaso de participaciones de fondos para no perder la «contabilidad fiscal» de esas participaciones?

            No sé si me explico…

          2. Correcto, asi es.
            Para traspaso de acciones tal y comlo dices, pues no hay ventas. En el caso de traspaso de los fondos, sí deberias marcar que es una venta por traspaso y guardar de alguna forma el fondo destino nuevo comprado, pero esta parte no está resuelta en el excel, como indiqué en un comentario pasado. Si se os ocurre algo para hacer la trazabilidad de fondos será estupendo.
            Saludos

  9. Muchas gracias Antonio, has hecho una labor enorme. Lo he descargado pero todavía no lo he mirado en profundidad, sigo en la versión 2.0. En cuanto pueda sacar un ratillo lo estudio más en detalle..

  10. Hola, tengo una duda muy concreta:
    Para compras, en la columna «Coste bruto total de la compra (euros)» incluye la comisión no? es el total pagado..aunque después volvamos a poner las comisiones.

    Para calcular el coste total en USD, tengo que dividir el coste total incluyendo comisiones en euros entre el tipo de cambio de ese día, y dividiendo eso entre el número de acciones….¿estoy equivocado?

    Gracias

    1. Hola Daniel,
      No incluye comisiones, ya que el coste se calcula con la fórmula multiplicando el número de participaciones, el precio de cada una y el tipo de cambio. Multiplico columnas J*k*L. Es cierto que el titulo «Coste bruto total de la compra (euros)» induce a error. Las comisiones las debes poner a parte en la columna siguiente (esta comisión ya depende de tu broker, y deberían desglosartela).
      Para pasar ese coste total sin comisiones a USD simplemente divídelo por el tipo de cambio (columna K).
      Saludos
      Antonio

    1. Sí, correcto. La terminología de bruto o neto, es respecto a hacienda. Bruto es sin comisiones, que tras sumar todas las comisiones que van aparte, se convierte en neto (tu plusvalia o pérdida real de cara a hacienda).

  11. Hola, GRACIAS POR TU TRABAJO DESINTERESADO. te quisiera pedir un favor, no podrías dejarla en blanco para ir metiendo los datos de cada uno. No sé que pasa pero cada vez que cambio algo se estropea otra cosa, es “demasiado completa” para personas con pocos conocimientos de Excel como yo. Me he guardado la original para ver todas las posibilidades de la hoja. Me encanta la actualización de la cotización. Gracias otra vez

    1. Manuel,
      Lo mejor es que añadas tus nuevas compras en DatosInversiones sin quitar las de ejemplo de la plantilla. Al añadir tus compras, mejor «copia» la fila, no crees una en blanco, te ayudará. Haciendolo asi no deberias tener demasiados problemas. No creo que te ayude una plantilla vacia.
      Si tras meter tus datos asi, sigues teniendo problemas, dime qué casilla en concreto te falla. Y si aun asi no te fuese, me mandas tu excel y te lo intento arreglar.
      Saludos
      Antonio

      1. Gracias por tu respuesta. Estoy leyendo tu libro con el Kindle de Amazon y me está gustando mucho, sobre todo para las personas que, como yo, to tenemos experiencia en éstos temas. Se lo recomiendo a todo el mundo. Lo de la plantilla vacía sólo lo decía para las personas que no tengan tantos tipos de inversión, pero olvídalo, creo que tienes razón. Gracias otra vez

        1. Manuel,
          Me alegro te esté gustando el libro.
          Puedes usar la v3.1 que mejora ahora algo el proceso de insertar nuevos datos, pues fallaba si insertabas en la ultima fila.
          Aunque tu cartera sea más sencilla, puedes usarlo igualmente. Solo pon lo que tengas, no pasa nada por no tener opciones o acciones, y solo tener fondos por ejemplo.
          Saludos

  12. Buenas de nuevo. Ya tengo toda mi inversión pasada a este excel, y me reitero en que es es un gran documento, gracias otra vez.

    Estoy pensando en hacer un traspaso de un fondo de inversión de gestión activa a otro fondo, y no se muy bien cómo ponerlo. Se me ocurre en las filas de las suscripciones que he hecho del fondo «venderlo», con comisión cero, y abrir nueva compra para el nuevo fondo, también con comisión de compra cero…..¿sería ok?

    1. Correcto, un traspaso es una venta seguido de otra compra, y sin comisiones. Anota en la coluna traspaso del que vendes un SI, asi no aparecerá como vendido de cara a hacienda en la pestaña IRPF.
      Al margen de esto, si tu fondo origen tiene pérdidas actualmente, es mejor que lo vendas, y no lo traspases. Si tienes plusvalias, traspasalo.

    1. Hola Oriol,
      En opciones no uso el ticker, ya que no busco su cotización de mercado. En esa columna ticker la aprovecho para en opciones, meter el compromiso al que me expongo si vendo una opción.
      Yo así lo uso, y va bien, pero seguramente se podría haber hecho mejor (la idea era no poner demasiadas columnas extra con las particularidades de las opciones).
      Saludos
      Antonio

  13. Hola,

    Grandísimo trabajo, currada alucinante!

    Yo de momento he empezado poniendo la cartera actual, y a partir de aquí iré añadiendo las operaciones. Unas dudas/problemas que me han surgido:

    1. Como comentas hay brokers que puedes acumular moneda extranjera. Es imprescindible poner el tipo de cambio de la compra o venta? Viendo como está formulado entiendo que si, pero al no cambiar cada vez que compro/vendo no tengo esta información. No se si ha surgido esta cuestión antes, sino tendré que mirar alguna forma de solventarlo.

    2. He visto que en la pestaña cotizaciones solo salen los valores que están en la cartera de acciones, se puede cambiar esto? Entiendo que es un filtro pero no lo veo. He creado una nueva cartera que es renta fija y no me aparecen los valores marcados como tal. Si un valor lo tienes en dos bolsas distintas solo aparece una vez, pero esto manualmente se soluciona fácil.

    3. Si se hace una aportación extra de liquidez, hay alguna forma de apuntarlo para que no te cuente como rentabilidad?

    4. Cuando vendes una call cubierta, como esta planteado te resta liquidez no? No debería poner el strike en Ticker/ISIN? O poner un 1 para que las formulas sigan funcionando?

    Saludos!

    1. Hola Oriol,

      Gracias por tus palabras. Me alegro que ya hayas metido tu cartera actual en el excel y confíes en él para llevar el control de tus inversiones.

      Entiendo que trabajas con IB y que manejas divisas como USD para poder comprar acciones USA sin andar cambiando moneda en cada operación. Yo opero con brokers españoles y por tanto la gestión de las divisas extranjeras y su seguimiento como si fueran otros activos más (y su correspondiente declaración a hacienda por plusvalias en divisas) no lo tengo que hacer. Para los que si tenéis broker multidivisa, efectivamente hay que anotarlo todo y se complica un poco más.
      En el excel, he asumido que cuando compras una acción en USA por ejemplo, en ese instante de compra haces el cambio de EUR a USD, y por eso hay esa casilla (columna K) para anotar el tipo de cambio del día de compra. Si tienes broker multidivisa con USD en efectivo (cambiados días atrás), el día de tu compra de la acción USA no cambias moneda y por tanto no la tienes que anotar. Sin embargo, si utilizamos este excel, para que funciones bien tal como está diseñado, lo que debes hacer en estos casos es que en la casilla K coloques lo mismo que en la casilla R (tipo de cambio actual o tras la venta). Esto te permite que se generen bien las plusvalias si en algún momento vendes tus acciones. De cara a hacienda si vendes tendrás las plusvalias en USD multiplicadas por el tipo de cambio en día de la venta (aunque no cambies moneda de esas plusvalias en el IRPF tienes que meter una cifra en euros, y por tanto tienes que anotar un tipo de cambio, el de la venta). Como el excel usa el tipo de cambio de la compra (y este no existió) por eso debes poner el tipo de cambio de la compra igual al del día de la venta en el excel y así la plusvalía se generará bien.
      Al margen de esto, debes mantener el seguimiento de tus USD que compres o vendas o compres tras cobrar dividendos, o tras vender acciones con plusvalias. Todo esto es un poco/bastante lioso y te recomiendo que leas estas entradas donde lo explican muy bien, paso a paso: https://www.invirtiendopocoapoco.com/fiscalidad-en-cuenta-multidivisa/ y https://www.invirtiendopocoapoco.com/como-declarar-las-operaciones-con-divisas/
      Como ves son normales las dudas, esto no es nada evidente, y es fácil liarse.
      Te respondo ahora a las otras preguntas

    2. Oriol,
      Respuesta a tu pregunta 2:
      En la pestaña de cotizaciones debería aparecer todos los activos (salvo los derivados y liquidez, en los que no busco su cotizacion). En la columna B (tipo de Tipo Instrumento) deben aparecer estas etiquetas: ‘Accion’, ‘ETF’, ‘Fondo’ o ‘PlanPensiones’, para que busque la cotización.
      Subcarteras, como la que dices de «Renta fija» se pueden crear sin problemas, indicandolo en la columna C, pero los activos cotizados son solo esos indicados: ‘Accion’, ‘ETF’, ‘Fondo’ o ‘PlanPensiones’.

      Si no te aparecen en la tabla los fondos o ETFs, es posible que el filtro de la tabladinámica en la pestaña de cotizaciones se haya modificado al ir borrando y metiendo tus activos en DatosCotizaciones. En ese caso deberías, reasignar los filtros de la tabla en Cotizaciones. No se si me explico.
      Si tienes problemas con eso, me pasas el excel, y no me importa arreglaros las cosas que se trastocan (ya he retocado varios, y es normal a veces no saber dónde tocar al no haberlo creado vosotros). Esto de hacerlo más «robusto» es algo pendiente (pues en mi uso diario no lo necesito), pero que llevaría trabajo duro de depurar para contemplar los diversos problemas que veo os suelen surgir. Por el momento, os los soluciono a mano.

    3. Oriol,
      Respuesta a pregunta 3:
      No se si te entiendo. Si no quieres que la liquidez se contemple como una inversión y por tanto te reste rentabilidad, simplemente no la incluyas en DatosInversiones, así eliminas la subcartera de liquidez. No se si te refieres a eso.

      Respuesta a pregunta 4:
      Cuando vender call cubierta, yo por ejemplo lo anoto así:
      CALL ITX 26,64 21-dic-18 Derivado Opciones CALL cub 0 EUR Renta4 14-jun-18 -1,000000 1,0000 102,0100000 -102,01 3,04

      como ves en la columna E (quinta) del ticker o compromiso, pongo un cero, indicando que no comprometo ninguna liquidez, ya que está cubierta por acciones.

      Y si compro calls (solo lo hago tras venta de una accion por minusvalias para aflorar en el IRPF, pero que quiero mantener en cartera, entonces me creo un futuro sintético equivalente a comprar lo que he vendido, comprando 1 call y vendiendo 1 put, para resolver el problema de los 2 meses en barbecho), lo hago igualmente poniendo cero en compromiso (columana E o quinta):
      CALL ITX101 23,49 18-dic-19 Derivado Opciones CALL 0 EUR Renta4 15-ene-19 1,000000 1,0000 103,5000000 103,50

      Oriol, espero haberte aclarado casi todas las dudas, y espero las de otros lectores.
      Saludos
      Antonio

      1. Muchas gracias!

        Punto 1 y 4 aclaradisimos!

        Te mando un mail por si te puedes mirar lo del punto 2 y te intento explicar mejor a lo que me refería del punto 3.

        Saludos

  14. Hola Antonio,

    un trabajo genial, GENIAL!!!

    me ha gustado mucho. Buen trabajo, pero me encuentro con unos desajustes cuando realizas traspasos de broker, que realizas compra y venta, y vuelves hacer una compra con el otro broker a precio 0, entonces no se reflejan en el resumen, a menos que tengas nuevas compras de ese valor con ese broker, Si no tienes, no calcula el % rentabilidad (diferencias de precios) y si tienes compras el precio medio de compra, se desviertua.

    ¿sabes como lo debo hacer para que salgan correctos?

    Gracias si puedes ayudarme 🙂

    PD: he visto que en datos dividendos, si pones importe en «Comisión venta derechos/ cobro dividendos (euros)», no se suman a «Dividendo neto (euros)»

    1. Hola Miguel Ángel,
      El excel no contempla traspasos de tus activos de un broker a otro. Pero en caso de que hayas pasado todas tus acciones de un broker a otro, lo que puedes hacer es simplemente cambiar el nombre del broker en la columna H de DatosInversiones, y además en todos tus cobros de dividendos pasados en columna G de DatosDividendos. Así no hace falta ni insertar nuevas filas, ni añadir compras a cero o similar. Es una solución que funciona (aunque no optima ya que se pierde la traza de dónde compraste la primera vez). Si se os ocurre alguna idea para gestionar los traspasos, encantado de oírla.

      Respecto a lo de que «Comisión venta derechos/ cobro dividendos (euros)» no se suma a «Dividendo neto (euros)», es cierto. He dejado esa comisión al margen del dividendo neto, ya que en el IRPF ese «gasto» va en otro apartado de gastos generales de mantenimiento de tu cartera.

      Espero haberte resuelto las dudas.

      Antonio

  15. Buenos días, un gran trabajo. Poco a poco voy metiendo toda la información.

    Tengo un problema en la Pestaña Resumen > Columna G39 («Precio Actual Moneda») no me marca ningún precio actual. El resto de datos de la hoja están bien y la formula también está bien. Las cotizaciones están actualizadas.

    Sabrías decirme cual podría ser el problema?

    Un saludo

    1. Hola Asoma,
      Me alegro que vaya casi todo bien con el excel. Pues de ese fallo no te se decir qué podria ser. Si me das alguna pista más, un pantallazo donde se vea el error y la fórmula, creo que te podria decir algo.
      Ya me dices por email.
      Saludos
      Antonio

  16. He incluido en la entrada este aviso ya que algún usuario ha experimentado este error:
    Update (9 Junio 2019): Si en la página de cotizaciones, tras pulsar el botón «Actualiza Cotizaciones» os sale un error sobre una línea que pone «Dim html As New HTMLDocument», significa que no tenéis incorporada una librería de Microsoft que permite al programa usar la clase HTMLDocument para leer las páginas web con la cotización. En ese caso la solución es sencilla: Entra en la ventana de Visual Basic donde te sale el error, y pincha en «Herramientas»>»Referencias», y en el desplegable marca con el ratón la librería «Microsoft HTML Object Library». Una vez cargada en tu equipo, debería ya funcionar.

  17. Hola colegas,

    Antonio te confirmo que en Mac no se puede cargar la librería «Microsoft HTML Object Library», parece ser debido a directrices de seguridad de Apple.
    Así que para poder actualizar las cotizaciones hay que o bien usar Windows o un emulador como Parallels para poder tener windows en Mac.

    Saludos

  18. Me acabo de dar cuenta que en la pestaña resumen, en el número de acciones por activo (en la parte inferior) aparecen las compradas hasta el presente, ¿no deberían salir las compradas hasta la fecha de cotización que hayamos seleccionado?.
    Lo digo porque hoy he ampliado una posición y aunque ponga 31/05/2019 para el cálculo, me tiene en cuenta las acciones compradas hoy 14/06/2019.
    ¿Qué variable habría que añadir a la fórmula?

    Buen finde.

    1. Hola Ángel,
      Pues tienes toda la razón, creo que es más coherente que la hoja resumen solo muestre el estado del valor de tus acciones hasta la fecha que eliges para las cotizaciones, y no hasta el día de hoy.
      He modificado el excel (ahora en versión 3.2 ya descargable en el blog) para poder hacerlo como dices. Los cambios los he explicado en el propio excel 3.2, y lo repito aqui:

      Cambios versión 3.2 vs. 3.1:
      En la versión 3.2 la hoja Resumen muestra los valores de las inversiones y los activos disponibles en la fecha de la cotización elegida en «Cotizaciones» (antes en 3.1 mostraba el estado a día de hoy, sin importar la fecha de cotización elegida en «Cotizaciones» y mostrada en Resumen!I2).
      Para ello se han cambiado:
      – las fórmulas en casillas de Resumen: I2 y rango C8:D12, colocando la coletilla: «;1*(TablaDatosInversiones[Fecha Compra]<=I2)" - en DatosInversiones se ha añadido otra nueva columna "Comprada antes fecha cotizacion" para saber si esa acción existía ya antes de la fecha de cotización elegida. - en la tabla dinámica que muestra la lista de activos en Resumen, se ha añadido otro campo filtro, para elegir los activos que cotizan actualmente y que además están comprados antes de la fecha de cotización (casilla Resumen!I2) Espero que ahora se vea tal y como esperabas. Un saludo Antonio

      1. Si, perdona,no me he explicado bien, es al introducir datos inversiones . En la columna l precio pagado por unidad . Incluyo hasta 7 decimales y me redondea al número entero

        1. Emilio, pues debe ser algo de tu configuración de excel. Yo en mis excels en esa columna veo todos los decimales.
          Prueba a en el menu del excel ir a Inicio > Numero > Aumentar decimales.
          Así se debería solucionar.
          Suerte

  19. Os informo a los suscritos a los comentarios que hay una actualización del excel descargable. Los cambios de versión 3.2 a la nueva versión 3.3 son estos:

    Se incluye en la pestaña «Rentas» en la parte inferior una tabla extra con seguimiento de los dividendos cobrados por cada empresa en el último año vs. el siguiente esperado, su crecimiento, y el peso de los dividendos en la cartera por cada acción (control de si el dividendo depende de pocas empresas o este está diversificado). Botón que carga el DPA esperado según yahoo finance.

    Se ha corregido la fórmula para calcular las comisiones en la tabla superior de la pestaña «resumen», ya que había un error.

    Para hacer todo lo anterior se han añadido nuevas funciones principales y auxiliares en el código VBA.

    Espero que os sea de utilidad

    Antonio

  20. Menudo trabajo hay detrás de ese excel, hay muchas horas metidas.
    Gracias por compartir.
    Haber si saco tiempo para rellenarla y probar por que eso es otra .
    Un saludo

    1. Hola Karlos,
      Sí es cierto, hay muchas horas de pegarse con el excel. Todo sea por dejarlo lo más útil posible, aunque esto es ya muy personal, y a muchos les gustará más completo o al contrario más simple, o enfocado solo en dividendos. Yo lo he ido creando, adaptando, rehaciendo de nuevo y dando forma según iba invirtiendo y me surgían nuevas necesidades. Al final ha convergido en esto que veis, que cumple bastante bien lo que necesito para el día a día. Ante todo, la idea es saber lo que gano en bolsa (medir con bastante precisión la rentabilidad que obtengo), y al mismo tiempo, tener todos los datos a mano para cumplir con hacienda, y poder tomar decisiones en base a datos objetivos.
      Entiendo que para muchos quizás pueda desbordar la primera vez que se ve (aunque el ejemplo es con pocos pero variados datos), pero dedicándole un tiempo a tratar de entender la filosofía de uso y el motivo de cada campo, y a ir metiendo tus propios datos, creo que poco a poco os vais a sentir cada vez más cómodos. Los errores o fallos que vais detectando, son útiles para mi también, pues me permite mejorarlo poco a poco.
      Saludos

  21. Buena baje a opción la del DPA.gracias
    Antonio, el botón del DPA no funciona.Parece que no tiene enlazada una función ¿como se llama para poderla enlazar manualmente?me refiere a la v3.3
    Gracias

    1. Maorr,
      Pues tienes toda la razón. La macro «ActualizaDPA» no estaba insertada en esa version y el botón que la debía llamar no funcionaba.
      En la página de plantillas excel ya está disponible la version 3.3b que lo corrige.
      Gracias por avisar.
      Antonio

  22. Hola Antonio
    Estoy intentando actualizar las cotizaciones a 1 de agosto pero no actualiza ninguna. Las divisas sí que las actualiza pero los fondos no…
    Por cierto, enhorabuena y gracias por tu excelente trabajo.
    (versión 3.3b)

    1. Hola Sinalek,
      Es raro que unas cosas sí las actualice y otras no. Entiendo que el problema pueda ser que en los fondos no hayas puesto el ticker adecuado, su ISIN. Si quieres mandame una captura por email y te digo.
      Saludos

      1. Es que llevo utilizándolo varios días sin problema hasta hoy, y no he modificado nada. De hecho, si pongo fecha 31/7/2019, funciona perfectamente… no sé si solo me está pasando a mí.

        1. Ah, es que el valor liquidativo de los fondos va con un dia o 2 de retraso, con lo cual es posible que ese sea el problema. El lunes deberias poder cargar bien las cotizaciones del jueves 1 de agosto….En cualquier caso lo miro

  23. Buenas,

    Estoy metiendo los datos en tu excel a ver si termino de enterarme como funciona todo. Me he fijado que ahora también funciona con planes de pensiones, pero para que te encuentre las cotizaciones veo que pones algo específico en la columna de ISIN que no estoy seguro de como encontrarlo para otros planes. ¿Simplemente el nombre con guiones? En un caso veo que lo acabas con «PP» y en otro no, por lo que no sé exactamente cómo funciona el buscador que has programado.

    Gracias de nuevo y un saludo.

  24. hola Antonio, al intentar actualizar los precios con fecha posterior al 31/7/2019, o sea de Agosto, obtengo únicamente precios hasta el 31/7/2019. He utilizado la version 3.3 y anteriores y el mismo resultado. Sabes o tienes alguna información si los proveedores de precios puedan estar bloqueando el servicio. Favor comparte si a ti te sucede lo mismo. Gracias y a disfrutar del resto del verano. un saludo.

    1. Hola German, y todos
      Efectivamente estoy detectando cosas raras. Los fondos no se actualizan en agosto, y se quedan con fechas de 31 de Julio. Sin embargo la web «FinancialTimes» donde coge los datos el excel si que tiene el valor liquidativo de agosto de los fondos.
      Por ejemplo aqui: https://markets.ft.com/data/funds/tearsheet/historical?s=ES0124037005:EUR
      Lo siento, habrá que depurarlo pero ahora en vacaciones curiosamente no tengo tiempo. Es un sin parar de ir de aquí allá. Si encuentro un rato lo miraré, pues esto no puede estar así… Antes del 25 de Agosto prometo arreglarlo.
      Antonio

        1. Hola,
          Yo no lo he podido ver, pero un lector, Jesús, ha analizado el código y ha encontrado una errata. Me dice esto:
          «Me he dado cuenta de que el macro de Actualizar cotizaciones no está funcionando bien para los fondos. He recorrido el código ejecutando despacio y he detectado una errata que hace que la actualización de precios de fondos se quede en el 31/7 y no haga bien los de agosto. Resulta que en el código pones «Agu» en lugar de «Aug».»
          Por tanto podeis arreglarlo directamente entrando en el codigo visualbasic, en pestaña programador, modulos, y sustituir «Agu» por «Aug».
          Espero que asi funcione ya.
          Gracias Jesús.
          Saludos
          Antonio

  25. Antonio, muchas gracias nuevamente por tu ayuda y dedicación continua con esto. La corrección de código ha funcionado a la perfección. Ahora si a disfrutar del verano, que pases super. Gracias German

    1. Hola German, gracias por confirmar que haciendo esos cambios ya funciona bien.
      A todos: En cuanto pueda ya subiré de nuevo las versiones de los excel con las erratas corregidas. Por el momento, para los que ya estais usando el excel lo mejor es hacer lo de German.
      Buen verano a todos. 😉
      Saludos

  26. Antonio, mil gracias por compartir estas plantillas, me parece muy generoso por tu parte no solo crearlas sino luego atender todas las peticiones y ayudar a cada uno de forma individual. Por mi parte, tengo un Mac y me quedo con la versión 2.0 que ya me parece estupenda y aunque no es tan completa me parece una maravilla absoluta. Y nada más, reiterarme en mi agradecimiento total a tu trabajo con estas herramientas y con el blog en general, que me parece instructivo, interesante y de grandísma utilidad para todos los que empezamos en este mundo de la inversión y el ahorro y además tenemos niños pequeños.

    Un saludo.

    Pd. Voy ahora mismo a ‘invitarte’ a un café porque me parece lo mínimo que se merece tanta dedicación desinteresada.

    1. Hola Mario,
      Gracias por tus comentarios de agradecimiento. Son realmente estos comentarios positivos y sinceros los que dan una recompensa al trabajo, además de vuestras ideas de mejora sobre la plantilla. El soporte y mantenimiento no es tanto, una vez decidido el enfoque y diseño de las funcionalidades (que fue la parte dura), ya que yo mismo al ser usuario propio de la plantilla excel, lo hago de forma continua mientras detecto cosas mejorables. Va evolucionando. Me parecía buena y útil, y quizás un desperdicio usarlo solo yo, sabiendo que cualquier inversor nuevo necesita algo así, por eso me alegro que haya gente que le sea también de utilidad.
      Gracias también por el café, que compartiremos con Paypal, que se lleva un buen pellizco siempre. Voy a tener que mirarme esa empresa para invertir, que tiene una posición importante y gran efecto red, aunque competidores nunca faltan…. y seguro que está cara…
      Suerte con el excel, a ver que tal te manejas…
      Saludos
      Antonio

  27. Excelente trabajo! Lo he encontrado en la web y me ha gustado mucho.
    Aún no lo he puesto en práctica pero me surgió la siguiente duda, para el caso de un Uruguayo que usamos el peso uruguayo, como piensas es la mejor manera de usar la planilla?

    1. Hola Grabiel
      Creo que basta con buscar en todo el libro excel la palabra EUR y sustituirla por la palabra UYU , con eso ya tendrias tu cartera denominada en pesos uruguayos. Prueba a ver, y si tienes problemas me avisas y te lo miro en tu excel.
      A ver si asi lo puedes usar.
      Un saludo

  28. Hola Antonio, hace algunos meses que uso tu Excel. Muchas gracias por tu trabajo.
    Desde este mes, los fondos no se actualizan y se quedan con fecha 29/11. No veo el motivo

    Un saludo

    1. José, ¿qué versión estas usando? Había un bug en una de las versiones que no dejaba trabajar en agosto y diciembre. Ya quedó resuelto eso gracias a un lector que lo encontró. Está explicado en comentarios de esta u otras entradas. O entras en el código y lo retocas, es fácil, o te actualizar a otra versión más moderna. Ya me dices si lo resuelves.
      Antonio

  29. Estoy intentando manejar la gran plantilla de nuestro amigo Antonio y como dicen algunos es para muy iniciados, pero estoy encabezonado en aprender y hacerme con ella. Me da un error con los macros, me dice que está deshabilitados y sobre todo no me deja actualizar las cotizaciones por dicho motivo, como podría resolverlo?,,, otra cuestión es si es necesario el Excell office o con el OpenOffice o wps office también funciona el Excell?

    1. Hola Antonio, no te se decir exactamente. En principio debes habilitar todo, para que las macros se ejecuten. Creo que solo funciona en excel de microsoft, y no en open office, ni mac. Si te sigue sin funcionar mandame un email y vemos.

      1. me respondo yo mismo, efectivamente el excel solo funciona con el programa office de microsoft,si no lo tienen ni lo intenten que se volveran locos para hacerlo manejable,tampoco funciona en la nube de onedrive.Lo mejor es cerrar todos los programas por cuesta que vaya fluido si no tienen un pc medio decente.y ahora que tenemos los mimbres vamos a ver si puedo hacer la silla

  30. Hola!

    Por entenderlo, en la pestaña de rentabilidad dónde se calcula el TWR no acabo de entender al fórmula:

    La fórmula del TWR es:

    TWR: (Valor Final-Valor Inicial+Reembolsos-Aportaciones)/Valor inicial

    En el excel veo:

    TWR:[(Valor Final + DIvidendos) -Valor Inicial – Aportaciones+Reembolsos]/Valor Inicial +Aportaciones-Reembolsos.

    Por un lado no entiendo el denominador ya que se tiene en cuenta los reembolsos y las aportaciones y por otro lado para calcular el valor inicial en el numerador no tengo claro pq se suman los dividendos. Es porque el dividendo al darlo se resta del valor de cotización?

    Un saludo!

  31. Hola Alex, Correcto lo que dices del dividendo.
    Cualquiera de las dos fórmulas son una aproximación de tipo mensual al TWR exacto que es el diario. Ambas, aunque distintas, son razonables. Una de ellas asume que aportas a final de mes y otra al principio, cuando en realidad habrás aportado cualquier dia del mes. Las discrepancias serán pequeñas si las aportaciones son pequeñas comparadas con el capital ya invertido.
    Espero ya lo veas más claro.
    Saludos
    Antonio

  32. Muy buenas Antonio,

    Un par de consultas, acabo de cerrar el año 2019 y me he dado cuenta de que cometí un error y en lugar de identificar ciertas inversiones como «Indexado Pasivo» lo llamé «Gestión Pasiva» en la tabla «Datos Inversiones» y me acabo de dar cuenta que al haber ido fijando meses en la tabla «Rentabilidad» no me ha cogido esas inversiones.
    ¿Cómo puedo desfijar las filas de esos meses en concreto parar que se recalcule y reasignen esos importes?

    Y por otra parte, en su día vendí una Put de Telefónica, he visto en el blog y en el excel un ejemplo de como anotarlo al abrir la posición, pero no he localizado cómo anotar el cierre. ¿Puedes ponerme un ejemplo?

    Muchas gracias,
    Angel.

    1. Me autorespondo a lo del indexado, lo que he hecho ha sido sustituir las celdas fijadas por «=Resumen!$D$10» , recalcular en la fecha de cierre que quería corregir y volver a fijar.

      1. Es posible que así te haya funcionado. Si es así estupendo.
        En cualquier caso para el futuro, me gustaría implementar algo para evitar tener que ir «fijando» el valor de tu cartera mes a mes, y que el historial de cotizaciones mensual de todos los activos de la cartera estén siempre disponibles automáticamente. Pero, me llevará tiempo hacerlo. Ahora está en la lista de cosas pendientes.
        Saludos

    2. Hola,
      Respecto a lo de las Puts, en el excel version 3.5 que hay en la web, puedes ver un par de ejemplos para estas columnas:

      Nombre Tipo instrumento (Acciones, Fondo, ETF,…) Subcarteras (diferentes estrategias inversión) Tipo activo (RV, RF, Mixto) Ticker/ISIN (o compromiso opciones) NIF o ISIN (para IRPF) Moneda (EUR, USD, GBP,…) Broker / Comer-cializador Fecha Compra nº unidades compradas (acciones o participaciones) Cambio divisa (Moneda/EUR) en compra Precio pagado por unidad (moneda) Coste bruto total de la compra (euros) Comisión compra (euros) Fecha de Venta o traspaso Año de Venta o traspaso Traspaso sin tributar Cambio divisa actual (o tras venta) Precio actual, o tras venta, por unidad (moneda) Valor actual bruto o reembolso o compromiso opciones abiertas (euros) Comisión venta (euros) Operación abierta (cotizando) Comisión anual media (acciones automatico) Rendimiento mercado neto (euros) Rendimiento dividendos netos (euros) Rendimiento total neto (euros) Rentabilidad acumulada (porcentaje) Rentabilidad TAE (porcentaje) Años en cartera

      PUT BAY 60 20-sept-19 Derivado Opciones PUT 6000 EUR Renta4 13-nov.-18 -1,000000 1,0000 495,0000000 -495,00 4,00 5-dic.-18 2018 No 1,0000 241,000 -241,00 4,00 FALSO 28,97% 246,00 0,00 246,00 49,7% 89,2% 0,063

      PUT ITX101 23,49 18-dic-19 Derivado Opciones PUT 4744,98 EUR Renta4 15-ene.-19 -2,000000 1,0000 249,9000000 -499,80 4,10 28-ene.-20 2020 No 1,0000 249,900 4744,98 VERDADERO 0,79% -4,10 0,00 -4,10 -0,8% -0,8% 1,038

      Básicamente, en la zona roja, pones a mano la fecha de venta (que en realidad es la de cierre o recompra), y anotas el precio pagado por cerrarla (0 si expiró, o la prima que pagaste al recomprar la put de nuevo).

      Espero que se entienda
      saludos

        1. Leyendo los comentarios y respuestas e arriba creo que no tengo algo muy claro, el tema del precio de compra bruto y las comisiones,,,,,, en mi caso broker ing, creo que me da en los resúmenes de movimientos el precio con los gastos incluidos, entonces en la tabla debería descontar los gastos, que sería coste bruto y luego en la columna de gastos especificarlo? Es así como se haría en el caso concreto de bróker ing?

          1. Pero entonces no entiendo una cuestión, al poner en la columna el precio pagado por acción y por ejemplo pones 10 por la compra de 100,se genera automáticamente en la siguiente columna un precio, (1000€),en el que están las comisiones incluidas y según tu me dices luego hay que volver a ponerlas,,,, Es así?
            Pongo un ejemplo real de ing:
            Compra: precio por acción 3,48€
            Comisiones 9.22€
            Importe:27849.22€
            Efectivo:27840€
            ¿Que precio debe de salir en la columna de precio bruto? ¿El importe o el efectivo?

          2. Si has comprado 8000 acciones de Santander por ejemplo a 3,48€/accion, lo multiplicas y te da 27.840€ que es lo que va en la columna de precio bruto. Los 9,22€ van separados en la de comisiones.

  33. Sigo con la cuestión anterior….. , si es como creo que es, el precio por acción del ejemplo anterior no se pondría 3,48.,sino 3,47xxxxxx que seria la división del precio efectivo por el n° de acciones compradas
    Y como todos decimos gracias por tu Excel y por tus ayudas

    1. Me respondo para borrar mi último comentario, y para dar un consejo a quien tenga como broker ing y quiera rellenar el Excel, no mirar los datos del certificado de operaciones, mirar mejor las operaciones en detalle que se ve más claro lo que hay que rellenar tanto en compra como en venta, que sería el precio por acción en Columna L, que daría el efectivo automaticamente en Columba M +comisiones a rellenar en columna N

  34. Hola Antonio,
    Primero, muchas gracias por el Excel!
    Como podría añadir una subcartera y que aparezca en la hoja resumen, en la tabla ”Rentabilidad de la cartera (y subcarteras)”? Es a mano?
    Y tambien se tendria que añadir una tablita en la hoja Rentabilidad no?

    1. Hola Marc,
      Asumo que tienes la versión 3.5. Para poner las subcarteras con el nombre que le hayas dado en DatosInversiones (columna C), vas a la hoja Resumen y en la columna B (de fila 8 a fila 12) pones a mano esos mismo nombres. Y acto seguido, automáticamente en la pestaña Rentabilidad ya te lo coge.
      Espero que te ayuden estas indicaciones.
      Antonio

        1. Marc, perdona. Si quieres meter una quinta subcartera, entonces en Resumen debes insertar una nueva fila y fijarse que las formulas esten bien. Y claro en Rentabilidad añadir otra tabla. Si controlas excel, lo puedes hacer pero antes debes entender las formulas para asegurarte que quede bien. Si no puedes me pides ayuda.
          Antonio

    1. Hola Jordi,
      No hay fórmula en excel. Yo he creado un script que te lo busca en finacialtimes. En el excel tienes ejemplos de cómo hacerlo, es solo poner el isin y dar al botón en la pestaña de cotizaciones.
      Saludos
      Antonio

  35. Hola, tremendo Excel, ¿una pregunta como anotas los cambios de divisa? ejemplo compro dolares el 1 de enero (compro varias empresas con ellos) y luego en año (cuando ya no tengo las acciones) los vendo y tengo plusvalia/deterioro.

    1. Hola Joan,
      En principio, si compras USD con EUR por tener cuenta multidivisa, deberías anotar una nueva fila en DatosInversiones, donde USD sería tu activo. Cuando lo vendas, lo tratas igual que un fondo o acción, aplicas el tipo de cambio, y se generarán las plusvalías de cara a hacienda.
      En otra respuesta arriba a Oriol (11 MAYO, 2019 A LAS 7:39 PM) puedes ver más detalles, y unos enlaces útiles.
      Espero haberte ayudado.
      Un saludo

  36. Hola Antonio,

    tiene muy buena pinta tu excel, lo estoy trasteando.

    He probado de usarlo en excel mac, pero da errores, pero ahora lo he continuado en excel windows y parece que todo va bien, hasta que me he encontrado un error que no se arreglar, te lo comento por si lo quieres tener en cuenta.

    A la hora de «actualizar» en la pestaña «resumen», me sale:

    Se ha producido el error ‘1004’ en tiempo de ejecucion.
    No se puede obtener la propiedad de PivotTables de la clase worksheet.

    Con lo que no se actualiza bien la pestaña y veo posibles errores de calculo.

    Es para que lo tengas en cuenta, y si crees que hay una solución rapida, la puedas plasmar en el blog, para probarlo y poder ayudar a otros que también les pase.

    Un abarazo, y reitero, BUEN TRABAJO

  37. Hola Miguel Angel,
    Gracias por reportarlo. Si me mandas el excel tal y como lo tienes con el error, lo puedo mirar a ver si lo se reproducir, e identificar, y así solucionarlo.
    Gracias
    Antonio

  38. Hola Antonio,

    En primer lugar, quiero felicitarte por varios motivos:

    1. Por tus entradas en el blog tan didácticas
    2. Por las horas de trabajo que le has puesto a la plantilla de Excel, me quito el sombrero
    3. Por compartirla con todos nosotros

    Yo me la he bajado hace unos días (versión 3.5) y aunque he tratado de seguir las instrucciones que has ido poniendo en las sucesivas entradas del blog, la verdad es que no sé muy bien como usarla…
    He puesto todas las compras y ventas de acciones que he hecho desde el año 2010, cuando empecé. Pero ahora no sé muy bien si tengo que seguir con los dividendos (los tengo anotados en un excel muy sencillo desde esa fecha) o si con la macro cargar DPA de la pestaña “rentas” es suficiente…

    También me lío con lo de pasar de las cotizaciones… Una vez que las tengo, como paso de ahí a Resumen, Rentabilidad…

    Sé que es mucho pedir, pero si cuando tengas algo de tiempo pudieras hacer un nuevo guía-burros de como rellenar la plantilla en la versión 3.5, sería de gran ayuda.

    Gracias de cualquier modo, y suerte con las inversiones!!
    José

    1. Hola Jose,
      Gracias por tus palabras de agradecimiento. La idea es intentar ayudar en lo que se pueda. Se que no hay una guía detallada de cómo proceder, pero esperaba que se entendiese más o menos bien. Salvo la pestaña rentabilidad, creía que el resto era bastante entendible. Me gustaría hacer una guía más detallada en algún momento, o incluso mejor un vídeo o serie de videos, para contarlo paso a paso. Si hay gente interesada en un vídeo, que lo diga, y así aumenta la motivación para ello.
      Por el momento, intento recordar el orden de hacer las cosas tal y como pongo en la pestaña «Ayuda» del propio excel 3.5, pero que ahora actualizo un poco.

      Pasos:
      0) Entorno de trabajo: Este Excel solo funciona en Windows, con software de Microsoft (No funciona con Apple, Mac, OpenOffice, Google sheets, etc…, solo Excel de Microsoft).
      1) Rellena tus datos de inversiones en «DatosInversiones» y si quieres, o tienes, los dividendos cobrados en «DatosDividendos» (recuerda el doble click para copiar o crear nuevas filas). Fondos amarillos indican datos que debes escribir tú, y fondo blanco se calcula solo con una fórmula.
      2) En «Cotizaciones» por una fecha (la de hoy, por ejemplo), y pulsa el botón “Actualiza cotizaciones”. Si antes de cargar ninguna cotización (se debe ver una a una cómo cargan) sale un error es posible que te falta activar una librería de Microsoft. En ese caso la solución es sencilla: Entra en la ventana de Visual Basic donde te sale el error, y pincha en «Herramientas»>»Referencias», y en el desplegable marca con el ratón la librería «Microsoft HTML Object Library». Una vez cargada en tu equipo, debería ya funcionar.
      3) Revisa en «Resumen» que se vean cifras razonables y sin errores. Para ello asegurate de poner en «Resumen» en las casillas B8 a B12 a mano el nombre de las subcarteras, exactamente con el mismo nombre que has usado en la columna C (subcarteras) de «DatosInversiones».
      4) La pestaña «Rentabilidades» te calcula la rentabilidad TWR que luego se ve en la hoja resumen. Para mantener esta estimación bien calculada, debes inicialmente rellenar el valor histórico estimado de tus inversiones al final de cada mes (si no lo sabes por el momento ponlo a ojo en las columnas amarillas mirando tus aportaciones/reembolsos mensuales). Luego ya es solo ir «fijando» el valor de tu cartera a fin de mes, a mano o pulsando (doble-click para ejecutar una macro que lo hace por ti) en la pestaña «Rentabilidades» sobre la fila con fecha actual en verde.
      5) Usa el resto de pestañas informativas, «Rentas», «Rendimiento» para tener más detalles de tus inversiones, y la de «IRPF» para recoger los datos para hacer la declaración; «Aflora» para analizar si hay que vender algo, etc… Todas estas pestañas, para no saturarte no hace falta que las uses ahora, ya que son opcionales. Las únicas pestañas obligatorias para tener el excel funcionando son «DatosInversiones», «Resumen», «Cotizaciones», y si cobras dividendos «DatosDividendos». Si quieres tener en “Resumen” la rentabilidad TWR bien calculada también sería interesante rellenar «Rentabilidad», pero esta es la hoja que os cuesta más rellenar.
      6) Reflexiona, sobre tu cartera mirando la pestaña resumen ¿Pagas muchas comisiones? ¿Lo hacen mejor los fondos indexados que los activos? ¿Tu cartera de acciones va bien? Te puedes pasar un buen rato viendo cosas y sacando conclusiones.

      Espero, Jose, que ahora se entienda mejor. Si no siempre tienes la opción de pasarme el Excel para que te retoque a mano lo que vea que te falta para que en “Resumen” se vea todo bien.
      Un saludo
      Antonio

  39. Hola Antonio.

    Muchísimas gracias por la explicación. Ahora ya me aclaro mejor con el funcionamiento y con como ir introduciendo los datos y hacer correr la plantilla.
    Respecto al vídeo, pues la verdad es que sería genial. Si puedes explicar en el orden correcto y como rellenar los diferentes campos de las diferentes pestañas, y de como hacer para hacer correr las macros, sería muy beneficioso. Ya sabes, una imagen vale más que mil palabras!

    A pesar de haber seguido los pasos que pones arriba, aún no consigo que mi pestaña «rentabilidad» muestre los valores correctos.

    Si no te molesta, te mando un mail privado y te explico lo que creo que pasa…

    Un saludo y de nuevo muchas gracias por tu generosidad.

    Jose

  40. Una preguntilla: para 2019 yo empezaba el año con una cartera pequeña de acciones que tenia en Inglaterra compradas en 2018 (cuando era residente fiscal en inglaterra) pero 2019 ya fui residente fiscal en España. Alguna idea de como tengo que declarar esa “posicion inicial” o como contabilizarla en el excel?

    1. Hola Andrés,
      En el excel no tienes que hacer nada especial, ya que no gestiona la fiscalidad, solo ayuda a saber lo que has vendido y por tanto tributar por ello, o si te convine aflorar pérdidas.
      En tu caso, si no has vendido no tienes que hacer nada. En todo caso, al estar esas acciones fuera de España y ser residente en España, deberás rellenar el modelo D6 (descargandote el programa Aforix del ministerio) que se hace en el mes de Enero. Espero haberte aclarado algo.
      Un saludo
      Antonio

  41. Hola Antonio,

    Ya he terminado de meter por fin todos los datos, incluyendo los dividendos, y estoy chocho con el Excel! Feliz de la vida! Muchas gracias por compartir la plantilla y echarme una mano con las dudas.
    Ahora que ya estoy usándola para analizar mis inversiones, me surgen algunas dudas más que te pregunto a continuación, si no es mucha molestia:

    – Cómo sugieres hacer para incluir las comisiones de custodia que (al menos en mi caso) me cobra alguno de mis brokers?

    – Cómo sugieres hacer para incluir las comisiones por suscripción a ampliación de capital que (al menos en mi caso) me cobra alguno de mis brokers?

    – Cómo sugieres hacer para incluir las comisiones por pago de dividendos que (al menos en mi caso) me cobra alguno de mis brokers? Veo que hay una casilla que parece estar para eso, la columna «M» de DatosDividendos, pero al menos en mi versión del Excel esta columna no se usa en ninguna fórmula…

    – Hay alguna manera de seleccionar un valor cualquiera de mi cartera y ver de un plumazo el dinero que he gastado en él (compras, comisiones varias, etc), el valor que me ha dado (incluyendo dividendos) y por tanto el beneficio o pérdida total y el interés? En la hoja Resumen se puede ver la diferencia entre compra y venta, pero creo que no incluye comisiones u otros gastos y dividendos, cierto?

    Muchísimas gracias por adelantado. Un abrazo y cuídate.

    José Ramón

    1. Hola José Ramón,
      Todo lo que preguntas lo tengo pendiente de hacer. Pero ya estoy en ello, trabajando sobre la versión 4.0, que tendrá bastantes novedades para ajustar estos detalles y otros como facilitar anotar compras, ventas parciales/totales o spits. Y todo ello creando unos formularios que tras rellenarlos y pulsar ok te rellenan y tocan la tabla DatoInversiones sin que tengas que hacer nada a mano (aunque a mano podrás seguir editando si lo prefieres). Además quiero poder importar las tablas de la version 3.5 a la 4.0 de forma automatizada. Pero lleva tiempo…

      Las comisiones de custodia, son un poco liosas pues algunas son proporcionales al patrimonio, otros te cobran si cumples condiciones, o a un porcentaje de la cartera, otros tienen un mínimo fijo más un porcentaje,… con lo cual igual hay demasiadas casuisticas, y no se si al final es mejor forzar al usuario a anotar en una fila extra los gastos anuales de cada broker (custodias, ivas, operativa mercados, mínimos mensuales, semestrales, etc..) aprovechando la info fiscal que generan todos en su informe anual, y meter estas comisiones en una tabla extra de gastos.

      El último punto que muestras es muy bueno, y lo implementaré. Buena idea.
      Paciencia pues esta semana santa estoy trabajando en el excel, pero voy lento escribiendo el código VBA de las macro.
      Un saludo
      Antonio

  42. Buenas Antonio,

    Me podrías decir cómo debo registrar el un traspaso de un fondo a otro de inversión?
    Y como se registra una venta de varias participaciones de un fondo?

    1. Alejandro,
      El traspaso del fondo, ponlo como una venta, marcando que es un traspaso en la columna correpondiente. Abre una nueva linea de compra para el fondo destino que quedará cotizando.
      La venta de algunas participaciones del fondo, no el total, lo haces duplicando la fila, y una de ellas la vendes, ajustando las participaciones que quedan vivas y las que no. Todo eso será muy fácil hacerlo en la versión 4.0. Disponible próximamente.
      Un saludo
      Antonio

  43. Buenas tardes Antonio,

    Ante todo felicitarte por tu gran trabajo, llevo unos dias trasteando la version 3.5 de tu excel pero no consigo que me funcione correctamente, mi gran duda son las aportaciones que he hecho en el pasado y las que hago menualmente, no se donde ponerlas, con esas aportaciones compro acciones, por lo que en algun sitio tendre de meterlas, o no?

    Las comisiones de mantenimiento de la cuenta donde podria ponerlas?

    1. Hola Miguel, todas las compras van cada paquete en una nueva linea en DatosInversiones. Las comisiones de mantenimiento estarán en la nueva version 4.0 que estoy preparando. Podrás importar lo que metas ahora en la 3.5 a la 4.0, o si prefieres no metas nada y espérate a que salga. Saludos

  44. Gracias por tu respuesta, tengo alguna pregunta mas, al introducir el valor de mi cartera de acciones en la pestaña Rentabilidad celda I36, ,me surge otra duda:
    debo introducir unicamente el valor de las acciones que tenga «compradas» en fecha 31 de enero, es decir, que si el 15 de enero realizo una compra de 100 BBVA y las vendo el dia 20, en dicha celda el importe a colocar seria= 0? ya que a dia 31 no dispongo de ninguna accion, o debo poner el importe resultante que disponga despues de la venta?
    Esquematico
    Dia 15 compro 100 BBVA por 10€ x accion = 1000€
    Dia 20 vendo 100 BBVA por 11€ x accion = 1100€
    3 opciones para colocar en celda I36.
    – Opcion 1: 0€ (valor acciones en cartera)
    – Opcion 2: 1100€ (valor obtenido de la venta)
    – Opcion 3: 100€ (beneficio obtenido de la venta)
    Y si la venta se realiza justamente el dia 31? influye en algo o la respuesta es la mismas?

    Si tu respuesta es la opcion 1, entonces tengo otra pregunta, desde el 30 de junio del 2012 al 31 de enero del 2016 no realizo operaciones, en la celda n33 me pone esa ultima fecha «31 de enero del 2016 como fecha de inicio» cuando realmente tengo operaciones realizadas en 2011 y 2012 —> puede ser por el +36 que pone en la formula, para q sirve ese +36?

    Por otro lado en la pestaña Resumen celda I8, en la formula escribes:
    =Rentabilidad_acumulada_fechas(«1/1/2014»;HOY();….. –> en mi caso que tengo movimientos desde 2011 deberia de cambiar eso a 2011 no?

    Perdona por tanta pregunta pero tengo muchas operaciones y se me hace complicado porque no acabo de entender las formulas.

    Gracias de antemano!

    1. Hola Miguel,
      Respecto la primera pregunta, es la opción 1=0€, el precio de tu cartera de acciones a fin de mes. Si vendes justo el último día del mes, ese día todavía tus acciones aparecerán y debes poner su valor (en realidad aparecerá solo, y basta con fijarlo con doble-click).
      Respecto a la celda n33, la fórmula tiene sus limitaciones, ya que busca en la columna «I» el último valor no cero, y la fecha de ese mes la toma como la fecha de comienzo de tus inversiones. Veo dos limitaciones, la primera si haces operaciones intra-mes (abres y cierras dentro del mes) esa columna tendrá muchos ceros, y no te cogerá la fecha de inicio bien. Además busca en 120 filas, habría que poner más si llevas muchos años invirtiendo (más de 10 años). El +36 es porque los datos empiezan en la fila 36 y la búsqueda anterior es relativa a esa fila. Habrá muchas formas de resolver estos bugs, pero una similar es mirando en la columna de aportaciones «J»:
      En vez de : INDICE($A:$A;COINCIDIR(0;Rentabilidad!$I$36:$I$120;1)+36;1)
      Pon: INDICE($A:$A;COINCIDIR(0;Rentabilidad!$J$36:$J$360;1)+36;1)
      pero antes inserta fijas abajo en la zona de fórmulas para llegar a los 30 años (360 meses).
      Gracias por reportar estos fallos.
      Saludos
      Antonio

  45. Bueno creo que lo dejo por imposible, no soy capaz, me da datos absurtos, creo que tiene que ver con que intente poner tantos años, desde 2011, al ir tan atras tengo de ampliar celdas y por ahi se me debe estropear la hoja (aunque siempre intento hacerlo añadiendo en medio de las tuyas…
    Esperare a ver la version 4 a ver si tengo mas suerte.
    Gracias igualmente.

  46. Buenas tardes Antonio,

    Mi mensaje no es para plantearte ninguna duda, sino únicamente para felicitarte por el enorme trabajo que haces. Después de estudiarlo a fondo, empecé a utilizar esta última versión del Excel para el seguimiento de mi cartera, y de verdad que me parece un Ferrari. A mí me encantan las hojas de cálculo y tengo muchas montadas para varios fines, pero nada tan sofisticado como esto.

    Eres un auténtico crack, y estoy esperando la salida de la versión 4.00 como si del último estreno de Hollywood se tratase.

    Un saludo,
    David

    1. Hola David, pues muchas gracias por pasarte por aquí para contar cosas buenas. Me alegro que te guste. La versión 4.0 tiene tantos cambios (no de estructura, tranquilo), que me va a llevar tiempo sacarla, de uno o dos meses no baja….
      Paciencia os pido, que lleva mucho trabajo, y me están pidiendo bastantes mejoras muy razonables en twitter.
      Un saludo
      Antonio

  47. Buenos días,

    Ante todo felicitarte por el magnifico trabajo que has desarrollado con las plantillas, eres un verdadero crack.

    Solo quería comentarte una duda que he intentado pero no consigo que funcione…

    En la hoja DatosInversiones, hay que poner el ticker de yahoo finance pero, en mi caso, tengo comprados btc desde coinbase pro e investing es la que me puede proporcionar el precio, en yahoo finance no encuentro ticker de ese mercado especifico, ¿hay alguna forma de que en la subcartera Acciones se pueda ir a buscar ese ticker en concreto?

    Espero haberme explicado correctamente

    Un saludo

    1. Hola!

      Grandisima hoja excel para llevar las cuentas, es una maravilla. Pero tengo una pregunta:

      Como has hecho para hacer la tabla dinámica de las cotizaciones y que se pueda actualizar desde Excel los precios y demas? Es alguna versión especial de Excel o hay unos pasos para hacerlo?

      Si puedes contestarme por email te lo agradecería. Un saludo!

      1. Hola Tony,

        La tabla dinámica es algo propio de Excel. Pero para lograr que los precios se actualicen dando un botón, hay unas macros (programitas) que se ejecutan tras presionarlo (por eso el excel tiene extensión xlsm y no simplemente xls ; la «m» es de macro). Estas macros buscan el precio por páginas web públicas de inversión en internet (como Yahoo, Financial Times, o Investing) y te lo insertan donde lo ves para que lo disfrutes.
        Me alegro te guste.
        Un saludo

        Antonio

  48. Hola Antonio,

    Muchas gracias por compartir el excel. Me es muy útil para llevar un control y para el tema de la declaración de la renta. Estoy empezando a invertir el bolsa y como te digo esto me viene de maravilla. Pero tengo una duda que me ha surgido conforme estoy pasando mis datos a la plantilla. Yo de momento solo estoy operando con acciones. He llegado a un activo en el que hice varias compras de varios paquetes en diferentes fechas para hacer promedio en el precio. Todos ellos los he puesto desglosados, pero el problema viene cuando hago una venta de una parte de las acciones y tengo que ponerlo en la sección de ventas. Es fácil poner una compra de por ejemplo 500 acciones y luego poner esas 500 en la parte de venta porque todo coincide, el problema viene cuando tienes varias compras de 100 un día, al día siguiente 300, al otro otras 600 y llega el día que vendes por ejemplo 450 y no se donde encajarla. No se si me he explicado claramente.

    Un saludo y muchas gracias por todo

    1. Hola Alberto,
      En ese caso que pones de ejemplo de vender 450 con múltiples compras, venderias completamente la fila de 100 y de 300, pero la fila de 600, la desglosas en dos iguales, pero dejando en una 50 y en otra 550. Vendes completamente la de 50, y así ya has conseguido vender las 450 acciones.
      Espero se entienda
      Saludos

      1. Hola Antonio,
        Antes de nada darte las gracias por contestar tan rápido.
        Tu explicación la he entendido, pero el resultado no se si lo daría bien, ya que en cada paquete están en un precio. Si lo hiciese de esa forma en alguno de los paquetes, por ejemplo el de 100 por decir alguno, me daría el resultado en negativo cuando en realidad en el promedio de todas sería positivo. Me parece que no sería el resultado real aunque probablemente esté equivocado.

        Te voy a poner el ejemplo:
        100 acciones a 3€ (300€)
        300 acciones a 2€ (600€)
        600 acciones a 1€ (600€)
        Esto sería una media de 1,5€ por acción.

        Si vendiera las 450 acciones por 2€ estaría ganándole 0,50€ por acción. Los resultados de las cuentas serían positivos (225€ total), en cambio si lo hacemos por paquetes como dices, quedaría:
        100 acciones a 3€ que compré las vendo a 2€ le estaría perdiendo 1€ por acción (-100€)
        300 acciones a 2€ que compré las vendo a 2€ le estaría sacando 0€ por acción (0€)
        50 acciones a 1€ que compré las vendo a 2 le estaría sacando 1€ por acción (50€)
        El resultado total sería de -50€ en vez de ser 225€

        Un saludo!

        1. Alberto, el cálculo lo has hecho perfecto. Son -50€ efectivamente, pero no 225€ que es equivocado, ya que asumes que ganas 0,5€ por accion y es falso, ya que incluyes 550 acciones a 1€ no vendidas. Espero que lo veas.
          Antonio

          1. Lo siento Antonio, ahora estoy mas confundido… Te voy a explicar el caso como veo yo, el cual dices que estoy equivocado.

            100 acciones a 3€ (300€)
            300 acciones a 2€ (600€)
            600 acciones a 1€ (600€)

            Son 1000 acciones que tengo a un coste de 1500€ en total. Hasta día de hoy yo saco la media del precio por acción de esta forma: 1500€ / 1000 acc = 1,5€ por acción es lo que tengo en mi cartera.

            Ahora de esas 1000 acciones yo vendo 450 acciones a un precio de 2€. Estas 450 acciones las tengo a un precio medio que hemos calculado antes de 1,5€ en mi cartera, siendo 450 acciones x 1,5€ = 675€ me han costado y las vendo por 2€. 450 acciones por 2€ = 900€. Ahora para sacar el beneficio hago esta operación 900€ que me han dado menos los 675€ que me costaron = 225€ de beneficio. y me siguen quedando en mi cartera 550 acciones que no he vendido y que siguen a una media de 1,5€.

            Si eres tan amable de explicarme en que estoy fallando, te lo agradecería.

            Un saludo Antonio y perdona las molestias. Llevo muy poco tiempo…

          2. El precio medio global es 1,5€ por acción, sí. Pero en cuanto vendes una parte, eso ya no es así. Tienes un precio medio de lo vendido inferior a 2€, y las 550 acciones remanentes tienen un precio medio de compra de 1€, que es a lo que las compraste. Piénsalo, a ver si lo ves ya, y es lo que hay que declarar a hacienda.

  49. A ver si lo he entendido porque llevo un cacao…
    Se calcula el global cuando vendes todo de golpe. Así se haría el calculo este último que te he puesto…
    Si vendo parte tengo que calcular el precio al que voy a vender respecto al primer paquete que compré, siguiendo por el segundo paquete que compré después y así sucesivamente. Es decir, me voy deshaciendo de las acciones mas viejas primero porque así lo hace el broker. Estoy en lo cierto? Es así como se calcula para la declaración de la renta y como se calculan las ganancias de mi cartera?

    1. Correcto, eso es. Siempre que vendas, vendes lo más viejo primero de cara a hacienda. Y tal y como has calculado en esos tre paquetes. Lo que hacias mal era asumir un precio medio de compra que era el global de todo. Y el precio de compra es simplemente el de cada paquete vendido. Unos con minusvalia y otros con plusvalia. Anótalo tal y como te dije, y tu luego detallaste, con una perdida de -50€ en todo lo vendido, y así está bien.

  50. Hola Antonio,

    Estoy pasando todas mis cuentas a tu plantilla y es una pasada, pero he detectado lo que creo que tiene un pequeño fallo. En la casilla de operación abierta que sale como verdadero o falso, llega una fecha que ya siempre por defecto es en falso. Si pongo fecha de compra en abril de este año por defecto sale en verdadero al estar la posición abierta, esto es lo correcto, pero a partir de mayo ya sale falso por defecto. No se si será cosa de las fórmula de excel porque yo no tengo ni idea de excel. Échale un ojo cuando puedas por si quisieras corregirlo en futuras versiones.

    Un saludo!

  51. Alberto,
    Supongo que eso te pasa porque en la pestaña «cotizaciones» tienes puesta una fecha de mayo. Esa fecha se usa para cargar la cotización, pero también para indicarle la fecha virtual actual de tu cartera, y saber qué es lo que tenías abierto cotizando en el pasado. Debe ser solo eso, la fecha de «Cotizaciones».
    Saludos
    Antonio

    1. Antonio,
      Si te refieres a la fecha que hay a la izquierda del boton de «Actualiza Cotizaciones» tengo la fecha de hoy puesta (15/6/2020). Lo he estado toqueteando y no cambia nada.
      Saludos!

      1. Alberto, Sí, a eso me refería.
        Si no es ese el motivo pues no te se decir así a distancia.
        Si quieres, y si no te importa, lo único que puedo hacer es revisar sobre tu excel qué ha podido irte mal (alguna fórmula estropeada al añadir nuevas filas posiblemente). En ese caso me lo mandas por email y lo miro.
        Saludos
        Antonio

        1. Muchas gracias. Voy a probar a realizarlo de nuevo desde el principio por si hubiese eliminado alguna fórmula y si no lo consigo te lo mando.
          Un saludo!

  52. Buenas, he estado usando el excel hasta ahora y estoy encantado. Un currazo el que has hecho con todo esto. Sólo le encuentro 2 «pegas» y es que con intradias y ventas parciales es un poco pesado si lo mezclas con otras inversiones a largo plazo. Y bueno, los avisos exagerados cada vez que intentas abrir el excel sin conexión, que te salen demasiados mensajes.

    Tengo una duda y es cómo incluir de alguna forma un préstamo (y sus intereses) en las inversiones. Sería una forma interesante de valorar el usar apalancamiento e invertir y cuando no, además de ver la evolución (y posible momento para amortizar). Metiendo la comisión anual a mano no sirve, pues el valor no viene ya contado en el prestamo. Meter el valor total a pagar tras el tiempo estimado da una valoración irreal de la rentabilidad de la cartera, pues directamente ya te descuenta intereses que aún no estás pagando… no encuentro forma para hacerlo que funcione y que tenga sentido en cuanto a al valor cotizado, actual y rendimiento real.

    1. Hola Mario,
      La verdad es que el excel no está pensado para intradía, ni tampoco para operar apalancado y con prestamos (solo tiene de picante las opciones). Con intradía, tendrías que anotar una locura de movimientos, y ya bastantes tenemos con los dividendos de una cartera de largo plazo, o algunas ventas de puts. Siento no poder ayudarte por ahí, pues el excel lo ajusto y mejoro según la operativa que uso, que ya es muy amplia, pero no justo en lo que dices.
      Respecto a los avisos al abrir sin conexión a internet, lo miraré a ver si se puede suavizar. El excel comprueba de vez en cuando que hay conexion por temas de depuración de errores, pero si se pueden reducir lo intentaré.
      Un saludo
      Antonio

Responder a Miguel Angel Cancelar respuesta