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 (y posiblemente el último salvo pequeñas mejoras ya disponibles en versiones 3.1 y 3.2)  de una serie de versiones previas (1.0 y 2.0), que poco a poco han ido añadiendo funcionalidades. Aquí tienes las entradas explicando las anteriores versiones (Excel v1 y Excel v2) por si te quieres poner al día y entender mejor esta entrada. De hecho, te recomiendo leer estas dos entradas previas (y los comentarios) si esta es tu primera visita a esta trilogía.

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.

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.


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.

59 respuesta 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

  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

Responder a ANGEL Cancelar respuesta