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.7.
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» (enlace1 y enlace2) 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.
Update (Enero 2021): Se ha mejorado la pestaña «Aflora» que tenía algún error. Se han habilitado más palabras clave de activos para buscar precios en Yahoo finance. Ahora se puede poner «MateriasPrimas» con el ticker de yahoo-finance. Si por ejemplo quieres poner un activo de «oro físico». En la página de plantillas excel ya está disponible la version 3.5 que se llama igual que la anterior, pero internamente es la 3.52. Ejemplo de anotación de oro físico y de bitcoin:
Update (Diciembre 2021): En la versión 3.6 se ha incluido una pestaña «D-6/720» que permite rellenar de forma automática el fichero en el formato necesario para presentar los modelos D-6 y 720, algo necesario cuando tienes inversiones depositadas en brokers extranjeros (como DeGiro, interactive Brokers o Revolut). Con un solo click te crea el fichero. Esta funcionalidad está disponible para usuarios Premium (con solo donar ya lo puedes ser). Aquí tienes más información de qué es el 720 y el D-6 y cómo se rellena.
Update (Marzo 2022): En la versión 3.7 se ha añadido la pestaña «Config» que permite predefinir los tipos de activos que se van a comprar o vender en DatosInversiones. Ahora en DatosInversiones hay más columnas en blanco, que se rellenan solas gracias a los datos de «config». En «Cotizaciones» al pulsar botón ya no se busca la moneda en yahoo, pues directamente se usa la moneda que se declare en «Config». La búsqueda de cotizaciones es más rápida. Se han incluido en «config» descripciones del activo como estilo, región o sector (útil si se quieren personalizar los quesitos de la hoja resumen). También se puede elegir la moneda base (por defecto es EUR). En resumen se ven los rendimiento realizados y no realizados también.
Forma de uso
Antes de terminar por lo importante que es quiero recordar el orden en el que hay que usar el excel. Pasos:
- Prepara la pestaña «Config» con el tipo de subcarteras, brokeres, monedas (moneda base), y listado de todos los activos (y sus detalles: Nombre, ticker, ISIN, moneda de cotización, sector, país, etc..)
- Rellena tus datos de inversiones en «DatosInversiones» añadiendo tus compras en nuevas filas, y rellena 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.
- En «Cotizaciones» escribe una fecha deseada (día-mes-año), y pulsa el botón para que el excel busque por internet las cotizaciones de los activos.
- Revisa en «Resumen» que se vean cifras razonables y sin errores (si falla algo es posible que no hayas rellenado bien todos los campos en DatosInversiones).
- Puedes ajustar los quesitos de la pestaña «Resumen» ajustando los filtros de las tablas que hay ocultas tras los quesitos. Desde la versión 3.7 puedes ver también tu distribución por países, sectores, etc.
- 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 con doble-click sobre la fila que tiene la fecha con fondo verde).
- 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, «720 y D-6» para completar estos modelos, etc…
- 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.
Otra promoción especial para ti:
Si quieres invertir con una gran selección de fondos indexados en MyInvestors, pues simplemente ¡Descarga MyInvestor! siguiendo este link: https://myinvestor.page.link/4gHyQz3dcv4TH2CXA compra tus carteras indexadas y disfrutarás de un año sin comisión de gestión y de un importe de 20€ extra tanto para ti como para mi.
Buenas, por fin pase ya toda mi cartera!! Tengo 2 dudillas, como aqui no puedo poner graficos, te lo pregunto en twitter
Estupendo,
Las dudas con gráficos, mejor por email a olvidatedetucerdito@gmail.com
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
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.
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.
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
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.
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
Eric, Pon las acciones nuevas en DatosInversion, con precio de compra cero.
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
Cierto lo que dices, en acciones tiene todo el sentido ponerlo
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
Daniel, prueba sin mas a dar al boton de cargar cotizaciones, y deberia actualizarse y verse red electrica
A mi me ocurre lo mismo. Y tras darle al botón de actualizar cotizaciones, no aparece el nuevo valor que he introducido.
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.
Muchas gracias, en cuanto pueda te lo envío.
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.
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
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.
Angel yo lo hago segun dices en tu primera opción, creo que es mas sencillo, e igualmente queda recogido ese coste.
Me alegro que se ajuste a lo que buscabas.
Saludos
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
Efectivamente, es asi como hay que hacerlo
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?
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.
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…
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
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..
Estupendo, muchas gracias a ti por usarlo y analizarlo.
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
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
Ok entendido, gracias. Y con las ventas supongo que lo mismo, ponemos de precio de venta el precio bruto y las comisiones aparte
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).
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
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
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
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
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?
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.
ok gracias, sí, en este caso tiene plusvalías.
Hola,
Una duda, como ticker de las opciones pones lo que has cobrado?
Muy muy interesante el excel!
Saludos
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
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!
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
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.
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
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
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)»
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
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
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
Usando la hoja con mis posiciones.una maravilla. Cuando el dividendo se reparte a modo de acciones , como se puede plantear con la esta hoja de cálculo?gracias
Me alegro Maorr. Pon las acciones nuevas en DatosInversiones con precio de compra cero.
Suerte
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.
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
Vaya, lo siento. Gracias por informar
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.
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
Hola, me redondea el precio de compra de la acción, porque puede ser?
Gracias por tu trabajo
Emilio, Si no me das algún dato más no te puedo ayudar. En la hoja resumen los precios medios tienen 2 decimales, no se a qué te refieres exactamente.
Saludos
Antonio
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
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
También me lo hace en el número de unidades compradas
He vuelto a copiar las fórmulas y solucionado
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
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
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
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
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
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)
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
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í.
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
Ok, te he enviado un pantallazo al correo. Gracias.
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.
Hola Gabriel, para planes de pensiones debes poner la ultima parte de la url a la web de investing cargando en fondo que quieres.
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.
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
No te preocupes Antonio, disfruta de las vacaciones. Mientras tanto se pueden poner a mano, no es tanto esfuerzo.
Un saludo.
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
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
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
y por cierto, creo que también habría que cambiar «Dic» por «Dec»
Yo también lo he corregido y funciona a la perfeción. Gracias a todos.
Estupendo. Gracias por compartirlo.
Ya está la versión 3.4, descargable en recursos, con el error de carga de cotizaciones de fondos en Agosto y Diciembre arreglado. Gracias por la espera….
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.
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
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?
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
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
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
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?
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.
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
Gracias por confirmar que solo te funciona con el excel de Microsoft en Pc con Windows. Ni Mac, ni Open office, ni Google sheets.
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!
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
Genial! Muy claro. Sería un acercamiento mejor calcular las dos y hacer una media?
Precisamente porque ante cantidades elevadas el % cambia bastante.
Bien, es otra posibilidad, incluso mejor, pero sigue siendo una aproximación. Saludos
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.
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.
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
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
Perfecto Antonio,
lo que me faltaba era cerrarla a 0. Ahora todo cuadra.
Muchas gracias y un saludo.
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?
Sí, efectivamente así es, pero ING tambien dispone del dato del coste sin gastos incluido en la zona de detalles. Un saludo
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?
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.
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
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
Gracias por el consejo para los usuarios de ING
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?
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
Hola,
La pregunta era si quiero añadir una subcartera nueva, a parte de las 4 ya existentes 🙂
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
Gracias!
Buenas! muy buena web! no la conocía!!
Cual es la formula o como se hace para ver el valor liquidativo de un fondo en excel?
gracias!
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
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.
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
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
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
Gracias Antonio, ahora te lo reporto
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é
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
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
Jose,
Me alegro que se entienda mejor ahora.
Mándame un email privado a olvidatedetucerdito@gmail.com sin problemas, y cuando tenga un rato lo miro..
Un saludo
Antonio
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?
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
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
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
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?
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
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?
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
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!
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
Miguel, creo que esa otra fórmula no lo hace bien tampoco. Lo más sencillo, en tu caso puedes poner en n33 a mano la fecha de inicio y ya está solucionado.
Saludos
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.
Miguel,
Ya lo siento. Si no está muy estropeado te lo puedo arreglar si me lo mandas. Si no, se intenta otra vez con la versión 4.0. Saludos
Antonio
te lo he pasado si lo ves facil de arreglar perfecto, si no me espero y pruebo la 4.
Arreglado Miguel, te lo he enviado
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
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
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
Hola Rubén,
Muchas gracias, me alegro que te guste.
Para Bitcoin pon este ticker: BTC-EUR
y en tipo de instrumento: Crypto
Así funciona fijo, pues yo lo tengo puesto, y lo busca en Yahoo: https://es.finance.yahoo.com/quote/BTC-EUR
Un saludo
antonio
Muchísimas gracias!!
Un gran trabajoo!!
Un saludo
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!
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
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
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
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!
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
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…
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.
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?
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.
Vale, pues ahora a modificar todas las cuentas que tenía realizadas, ya que las tengo mal. Muchas gracias por la lección! Un saludo!
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!
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
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!
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
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!
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.
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
Hola, queria saber si es posible que adquiera o importe automaticamente las ordenes que exporto del mi broker (DEGIRO), es un Excel con la información. Evitaria picar a mano los datos y sería genial. Gracias!
Hola Antonio. ¿Sigues trabajando en la versión 4.0?
Muchas gracias por tu trabajo
Hola Javier, La verdad es que lo tengo aparcado por el momento, pues ahora mismo estoy liado con otras cosas. Podéis seguir usando la 3.5 tranquilamente si os resulta útil, pues sobre la otra no tengo fecha prevista ya que tengo que encontrar tiempo y las ganas para poder hacerlo bien. Siento la demora
Perfecto. La 3.5 va perfecta. Muchas gracias!
Hola Antonio.
Lo primero agradecerte tu trabajo y ayuda. Estoy encantado con lo práctica que es la Excel.
Tengo un pequeño problema que no he conseguido solucionar. cuando «he metido» en la hoja Datosinversiones o he alcanzado los 65 valores, (acciones y fondos), a partir del producto 65, no me actualiza el valor de las cotizaciones. Es como si la macro estuviese limitada a ese valor y a partir de ahí ya no actualiza las cotizaciones.
He probado a comenzar nuevamente desde ceo e introducir todos los valores en «Datosinversiones» y me ocurre lo mismo.
Mil gracias por todo.
Carlos Eguizábal
Hola Carlos,
He repasado el código de las macros que busca en la pestaña de «Cotizaciones» el número de activos distintos que hay en cartera, y lo hace mirando la columna «C» de esa pestaña. Mira lo que hay desde la fila 10 hacia abajo. No mira la columna A, mira solo la C para hacer el barrido.
Este es el código:
‘ Actualiza el precio de todos los activos deseados en columna C (de fila 10 para abajo)
fila_ini = 10 ‘ fila primer activo en columna C
fila_fin = ActiveSheet.Cells(Rows.Count, «C»).End(xlUp).Row
num_filas = fila_fin – fila_ini + 1 ‘ numero de activos
y num_filas es el número de activos distintos que luego recorre en un bucle, desde la fila_ini hasta las fila_ini+num_filas. Por tanto se debería adaptar a cualquier número de activos sean 60, 100 o 200…
Lo que si podría estar pasando es que sobre la fila 65, o donde surja el problema de no bajar más, tengas una casilla vacía en la columna «C» en vez del tipo de activo (Fondo, accion, etc..). Si ocurre eso el código si podría interpretar que no hay más activos debajo, y por tanto calcular mal el número de filas (o activos) a recorrer.
Si nadie más se ha quejado de este tema (yo tampoco, que tengo una lista también muy amplia), es posible que sea por eso (alguna casilla vacía en la columna C). A ver si es ese el problema que tienes. Si no lo es, lo único que podría hacer es mirar tu excel en concreto y ver qué pasa.
Gracias por avisar
Antonio
Muchas gracias por tu rápida y detallada respuesta Antonio.
Te he enviado un email con unos pantallazos, porque una imagen explica mejor que mil palabras, y la hoja Excel.
Por si también le puede ayudar la explicación a Javier, copio el texto del correo enviado.
Saludos.
Carlos
«Como una imagen val más que mil palabras, copio-pego pantallazo del momento en el que las cotizaciones de los productos dejan de actualizarse. Verá que en la fila 71, la actualiza pero no pone el “confirmado en verde”, a partir de ahí las 5 siguiente filas no las colorea en amarillo, y la siguiente fila ya no actualiza los precios. Lo curioso es que si elimino productos de la hoja “Datosinversiones” comprados al principio , y reduce el número de valores, sí actualiza bien los últimos valores.
Copio pantallazo También de cuando reduzco el número de productos.»
Carlos,
Acabo de ver la imagen que te falla. Si te fijas bien verás en la celda de la fila 75, y columna de los ticks, una pequeña marquita abajo-derecha de la celda. Basta con agarrar esa marquita con el ratón y arrastrar hacia abajo a lo largo de esa columna. Tras hacer eso, has ampliado una tabla que se te ha reducido por alguna eliminacion que has hecho, ya que originalmente tenía unas 170 filas, y ahora solo 75.
Espero que asi se resuelva.
El color amarillo no se pondrá pero eso no importa y lo puedes añadir tú a mano.
Ya nos cuentas
Saludos
Antonio
Solucionado, que maravilla, de diez.
Además Antonio me ha revisado y arreglado alguna otra cosilla de mi tabla.
Un millón de gracias por tu ayuda y dedicación.
De nada Carlos. Me gusta que ya que lo usáis, y habéis confiado en el excel para llevar el control de vuestras inversiones, que se vean bien esos detalles
Saludos
Antonio
Hola Antonio. Yo tengo el mismo problema que Carlos. En la versión 3.5 a partir de la fila 38 del Excel no me cargan las cotizaciones. ¿Alguna idea de cuál puede ser el problema?. Muchas gracias por tu trabajo.
Saludos
Javier
Javier,
He contestado a Carlos, arriba en los comentarios. A ver si es lo que digo lo que te puede ocurrir también.
Saludos
Hola de nuevo Antonio,
Gracias por contestar tan rápido. Los valores en la columna C están bien. A mi el problema me ocurre a partir de la línea 35, pero sólo no da la fecha y el valor numérico del precio, en cambio, si da el tipo de moneda para esas acciones en concreto a partir de dicha línea 35. Además una vez acabado el barrido la ventana emergente dice que están correctos el 100% de los valores.
Si te parece bien y te interesa mirarlo te puedo mandar la excel para ver si das con el error.
Saludos y muchas gracias
Javier
Curioso lo que dices, Javier. Sí, mándamelo y depuro a ver cuál es el motivo de ese comportamiento raro.
Saludos
Antonio
Hola Javier,
Ya he analizado tu excel. El problema tuyo no era el mismo que tenía Carlos (el que comentó en el blog junto a ti). Él solo tenía que arrastrar con el ratón de borde de abajo-derecha de la tabla de cotizaciones, y usaba una versión 3.5 anterior a la tuya.
En tu caso, usas la última versión disponible en la web (es la 3.5 pero internamente es la 3.513) y parece que tenía problemas esporádicos con las cookies que pide yahoo para acceder a la info. En varios accesos (normalmente al final de la lista de cotizaciones) la web de yahoo respondía cookie incorrecta, y por eso a partir de ahí no podía acceder a los precios, pero sin embargo si a la moneda. Es un caso un poco rato, pues los fallos no siempre se producen. Estos mismos fallos ya los había sufrido yo en algún momento. Curiosamente el excel de Carlos no sufre de estos fallos por cookies (al menos mientras lo he probado), con lo cual he mirado las diferencias entre ambas versiones y he cambiado un par de líneas de código en la gestión de nuevas cookies.
Parece que ya funciona tras los cambios, pero como son fallos esporádicos no canto victoria. La versión que te he devuelto corregida sería la 3.514, cuya versión a compartir aquí la subiré en breve en cuanto me confirmes si te ha solucionado el problema (por si sirve para resolver ese mismo problema en otras personas).
Espero que vaya bien.
Saludos
Antonio
Antonio, a mi me está dando problemas al actualizar los valores de las Acciones, el valor de los fondos lo actualiza bien.
Aparece una ventana emergente que dice: “Error en getCookieCrum. Error: 9 Linea: 0”
¿se trata del mismo problema que describes para Javier?
Emilo,
Me temo que sí, es el mismo problema con las cookies al acceder a yahoo finance. Con los fondos no hay problema pues los coge de financialtimes.
En ese caso, asumiendo que haya quedado resuelto el problema, espero que si, tienes dos opciones. Una bajarte la plantilla nueva 3.5 en la web que subí anoche, y pasarte los datos de las inversiones copiando y pegando. Y la otra opción es que sobre tu excel, te cambio las líneas de código que supuestamente provocaban ese fallo. Ya me dices que prefieres.
Saludos
Antonio
Antonio,
la nueva versión corrige el problema, al menos el que a mi me daba.
Mil gracias por la ayuda, por responder tan rápido y sobre todo por la gran contribución que haces.
Un saludo.
Emilio, de nada. Estupendo oir que lo corrige. Saludos
Hola Antonio,
Enhorabuena por el excel, me encanta, menudo currazo. Hasta que descubri tu web tenia un excel hecho por mi que bueno… decir que era rudimentario comparado con el tuyo es poco…
Me gustaría añadir cryptomonedas al excel, como lo harias?, se puede actualizar la cotización?
Gracias
Hola Alejandro,
Para añadir cryptomonedas lo haces de forma habitual buscando el ticker the yahoo finance, y ya está. Aquí tienes el ticker a usar para bitcoin: «BTC-EUR». Y aquí la web de donde se saca la cotización: https://es.finance.yahoo.com/quote/BTC-EUR?p=BTC-EUR&.tsrc=fin-srch
Recordad que cada tipo de activo tiene una fuente de datos distinto de donde tenéis que sacar el ticker:
– Ticker de fondos: Usar el ISIN de Financialtimes.com
– Ticker de acciones, ETF, Bono y Crypto: Usar el de Yahoo Finance.com
– Ticker de PlanPensiones: Usar la cadena final del URL de Investing
Saludos
Muchas gracias Antonio!
Ya las he metido y funciona perfectamente.
Aunque ahora me surgen otras dos cuestiones:
1. Como puedo añadir las aportaciones periodicas al plan de pensiones? una por una?
2. Tengo un problema q no logro resolver. Es en el resumen, en la tabla dinámica que aparece al final junto a la cartera de inversiones. El precio actual y la diferencia de precio no me aparece. He comprobado que la formula de la función sigue existiendo y no se ha modificado, y la tabla esta conectada a los datos de inversión por que el resto de datos si me aparecen (valor en euros, precio medio de compra) … por más vueltas que le doy no se por que ocurre eso.
Alejandro te respondo:
1. Sí, una a una. No hay otra forma. Cada aportación tiene su propio precio de compra
2. Eso pasa solo si en DatosInversiones tienes alguna casilla con un error, aunque solo sea una. Suele ser en columnas que leen precios o en fechas.
Saludos
Gracias, Antonio. Problemas resueltos.
Siento ser pesado, me ha surgido otra duda.
Podría añadir la cotización de hong kong Dolar (KHD) debajo de USD, EUR y GBP?
Tengo acciones de bolsa de HK y me gustaria q tambien se me actualizaran.
Un saludo y gracias
Alejandro, Sí, por supuesto, puedes poner las monedas que necesites. Simplemente escribe KHD debajo de las otras, o encima de alguna otra, si no necesitas los otros cambios, y ya está.
Hola Antonio, me ha ocurrido algo extraño.
Cuando he abierto hoy me excel resulta que en la hoja de datos de inversiones me han desaparecido la mayoria de las inversiones, tan sólo permanecen las posiciones cerradas. En cambio en el resumen si aparece todo… perdona pero no consigo solucionarlo. Sabes que puede ser?
Hola Alejandro,
No se pueden haber borrado sin más los datos. Lo más seguro es que en alguna de las columnas de DatosInversiones hayas activado un filtro (pequeño triangulito a la derecha de las celdas de cabecera). Si clicas ahí, podrás ver si has seleccionado un filtro que te deja ver solo los activos que ya no cotizan. Pon ver todos.
Espero que sea solo eso.
Saludos
Antonio
Gracias Antonio, siento molestarte por cosas tan tontas, pero no daba con la tecla y sabía que debía ser algo así. Efectivamente había una que la había marcado sin querer… ufff que susto.
Hola, como todos los que pululamos por aquí y aprendemos de ti, no me queda otra introducción que agradecerte enormemente el trabajo que realizas para ayudarnos a entender mejor este mundillo y a convertirnos poco a poco en unos amos del control numérico!!
Tengo varias dudas, ojalá me puedas ayudar con ellas:
1. En la pestaña IRPF1, no me aparecen debidamente colocadas las acciones extranjeras y las cobradas en euros en las columnas que les corresponden, están mezcladas. Cómo puedo solucionar esto? De qué depende que aparezcan en uno u otro sitio? Quizá estoy metiendo algún dato mal en DatosDividendos
2. Los dividendos que cobro en moneda extranjera a través de ING me proporcionan los datos adecuados para introducirlos en las columnas de DatosDividendos, ya que están todos en EUR. Sin embargo en DEGIRO no aparece el tipo de cambio. Siguiendo el link que pusiste en el tema de cuentas multidivisas de este post, me he generado una pestaña nueva que he llamado “diviDEGIRO” , para que de forma automatizada, con los datos que proporciona, saber el tipo de cambio que se me ha aplicado. Acerca de esto, ejemplificando en $(USA) la retención del 15% (origen) se realiza automáticamente en DEGIRO y con el restante ($) se “compra” la divisa euro para ingresarme el dividendo en mi cuenta. Es con esta cantidad ingresada en mi cuenta (me aparecen el neto en $ y se transforman en €) que deduzco el tipo de cambio que se me ha aplicado (que no corresponde siempre con el tipo de cambio al final del dia según el BCE)
Bien, mi duda es si ese orden de operaciones que llevo a cabo para sacar los datos a introducir en DatosDividendos, es adecuado o algo se me escapa. Porque luego con estas empresas, la pestaña IRPF1 me señala que hay un exceso de retención en origen en cada cobro de dividendo, y eso me despista! La retención la realiza el fisco estadounidense y será el 15% fijo. Si hay descuadre…será porque el tipo de cambio aplicado beneficia al broker y me salen las cuentas como exceso de retención? Estoy perdido en este punto.
3. Me ha llevado un tiempo entender el excel, por ello no he ido fijando bien las filas de “Rentabilidad”. ¿Podría ir fijándolas una a una tras poner en “Cotizaciones” las fechas del ultimo día del mes de este año (comencé a invertir en 2020) de forma sucesiva? En este caso, tener introducidos por ejemplo datos de julio mientras hago esta operación para abril, tiene alguna consecuencia? Algo me dice que podría hacerlo porque en este ejemplo, en “Resumen” aparecen solo las que tenía en cartera en Abril y no las adquiridas posteriormente.
4. En la pestaña rentabilidad, me aparecen fechas futuras hasta 2028, en las que desaparecen las columnas amarillas y en su lugar son blancas y tienen números generados con alguna formula…lo que modifica la gráfica. Se pueden eliminar esas filas? O cuál sería la solución?
Perdón por la chapa, espero pacientemente tus comentarios.
De nuevo mil gracias.
Hola RattusInvestor,
Te intento contestas a tus preguntas.
1. No te se decir pues el fallo que has visto se puede deber a varias cosas. Una imagen ayudaría. Lo mejor es tener el propio excel que si quieres podrías mandarme por email.
2. El excel como sabes, hasta esta versión 3.5 no está pensado para cuentas multidivisa, aunque sí para Degiro cuando tienes activo el cambio automático de divisa. Pero no para IBKR o Revolut. Quiero hacer ya una versión 3.6 que permita gestionar bien las multidivisas. Creo que tu pestaña “diviDEGIRO” va en esta dirección. Si quieres te lo reviso, y así de paso me sirve de impulso para crear esa versión 3.6 nueva con multidivisa. No es algo evidente, pues cuando compras divisa tienes anotar por método FIFO todas las nuevas compras de divisa (cambios eUR/USD, cobro de dividendos, y ventas de acciones con plusvalías), y restar por FIFO las ventas o gastos de USD, y las ventas de acciones con minusvalías. Yo pensaba hacerlo con una macro, pues si no, tienes que duplicar todo lo que hagas (dobles anotaciones de dividendos y operaciones con acciones), y es un lío.
3.Si son acciones, sí. Puedes ir metiendo fecha de fin de mes e ir regenerando el valor de tu cartera en fechas mensuales pasadas.
4.Se pueden eliminar y modificar, siempre que entiendas que es lo que modificas.
El excel es de ayuda, pero como dices tienes que hacer un esfuerzo por entender su filosofía y fórmulas usadas, ya que si no te dará miedo tocar cosas, o si te surge un problema no sabrás cómo arreglarlo. Mientras pueda, os estoy ayudando a recomponerlos o arreglarlos, sin compromiso alguno.
Espero haberte aclarado buena parte de las dudas.
Saludos
Antonio
Hola, de nuevo, gracias por tu respuesta, y tan rápido!
Te envío el excel al correo. Gracias de antemano.
Efectivamente el cambio de divisa es automático en DEGIRO.
Cuando tenga luz verde si no he desconfigurado el excel, haré lo de la rentabilidad, puesto que lo que me interesa es el tema de las acciones.
Un saludo
Hola Antonio
El fichero está súper bien la verdad, muy muy currado, ojalá puedas retomar las versión 4.0, creo que estamos todos ansiosos porque salga 🙂
He estado rellenando los datos y no consigo cuadrar la pestaña de rentabilidades, la TAE de 1 año, el histórico de años en 2020 y la YTD me salen como -100%. -100% y N/D. Sin embargo, las rentabilidades desde el Inicio y desde el Inicio parece que salen correctamente…
Te he mandado un correo con un par de capturas por si ves algún error, cuando puedas ok?
mil gracias! y feliz año 2020
hola, muy buen trabajo
Hola Antonio, gracias por compartir este recurso esta increible, es lo mejor que he encontrado en internet la verdad! Queria hacerte una pregunta: existe alguna forma de hacer el tracking de la compra/venta de oro fisico? No se si hay algun ticker que pueda usar que refleje el precio del oro en spot por gramos u onzas o si hay alguna forma mejor de hacer este seguimiento. Gracias!
Hola JaviB,
Pues puedes hacer el tracking de oro físico usando este ticker: «GC=F» ya que busca el precio de los futuros del oro (la onza) en USD en yahoo finance en este sitio: https://es.finance.yahoo.com/quote/GC%3DF?p=GC%3DF
En tipoInstrumento puedes poner «Crypto», aunque ya se que no es una criptomoneda, pero hay que poner alguna de las palabras clave (Accion, Bono, ETF, Crypto,…) para que lo busque.
Para eliminar esta incoherencia de nombres en la nueva versión refinada de la version 3.5 del excel (ya descargable desde hoy), ya se puede usar como palabra clave en tipoInstrumento: «MateriaPrima» (en vez de Crypto) y luego poner el mismo ticker «GC=F»
En la nueva versión, por tanto, se pueden poner las materias primas así en DatosInversiones:
Nombre: «Oro físico (onzas)» por ejemplo
Tipo instrumento (Acciones, Fondo, ETF,…): «MateriaPrima»
Ticker/ISIN (o compromiso opciones): «GC=F»
Moneda (EUR, USD, GBP,…): «USD»
Espero que te sirva, Suerte
Hola, Me encantaria utilizar tu excel y evidentemente al igual que hago con otras plataformas hacer un donativo, pero estoy encontrando muchos problemas para activar el Excel. Tengo un Macbook y la versión de excel la 16.16.27.
Mi problema esta que cuando voy a actualizar las divisas, incluso cuando hago todo lo que esta en el PDF, me da error en las macros.
Alguna sugerencia para arreglarlo ??
Hola Javito,
Este excel con Mac no funciona, solo en entorno Windows y con Microsoft office.
Lo siento
Saludos
Hola Antonio buenas noches:
Quería felicitarte por el trabajo realizado en este excel, es una gran ayuda para controlar nuestras inversiones. Lo estoy empezando a utilizar pero algunas veces al actualizar los precios me da Error al cargar moneda/precios en Yahoo Finance F error: 91 linea: 7. En el medio de la actualización, luego tarda un rato y finaliza.
Al parecer es el ticker de Ford (F) en el que da error. A veces finaliza el proceso cambiando bien todos los valores menos Ford pero a veces solo cambia aproximadamente la mitad de los datos.
Muchísimas gracias. Un abrazo
Hola eferlav, Hay veces que esos errores de cotización ocurren, pues yahoo no entrega los datos. Pero misteriosamente se arreglan de un día para otro. ¿la versión que usas es reciente descargada hace pocos meses?
Hola buenos días de nuevo, estuve usando una versión del año pasado pero recientemente he instalado la última, la versión 3.52 me parece que es.
Ok, muchas gracias, de todas formas no hay problema, le meto los datos a mano cuando no los encuentre, jeje.
Otra consultita si no es mucha molestia, en la pestaña IRPF2, al vender acciones, en la columna «Precio venta tras gastos» aparece #NAME? en toda la columna, es normal, o estoy rellenando algo mal?
Ya me estoy preparando para el IRPF, este año empecé a invertir y me da algo de miedo cubrir bien los datos, con tantas operaciones y teniendo Revolut, firstrade y De giro, no se si me volveré medio loco.
Por cierto el saldo en $ lo voy calculando por cada broker, no se si es correcto o no.
Bueno, un saludo fuerte.
Hola Eferlav,
En IRPF2 si te sale eso «#NAME?» en toda la columna pues no debería aparecer. Es posible que hayas metido en algún precio una letra en vez de números y por eso salga eso. Mira también el filtro de la tabla dinámica asociada en IRPF2.
Si tienes 3 brokers o más es cuando te será de más utilidad el excel, al proporcionarte una visión integrada de todo.
Lo que dices del saldo en $ no lo entiendo. Es posible que tenga que ver con el tratamiento de divisas, y este excel no maneja por el momento posiciones abiertas en divisas. En principio al menos para llevar tu rentabilidad bien en DatosInversiones tienes que anotar tus compras en la divisa que sea, y poner el tipo de cambio cuando compras, con lo cual al final tienes una visión integrada de tus plusvalías en euros que es tu moneda base.
Saludos
Buenos días. Antes de nada, agradecerle su labor al compartir semejante maravilla con el mundo inversor.
Escribo porque me asaltan dudas. Ya sé que las rentabilidades se deben copiar mes a mes para realizar un seguimiento, así que antes de ponerme a ello quiero cerciorarme: ¿cómo borro los datos previos de «Rentabilidad»? ¿O se rellenan automáticamente al ir completando «DatosInversiones»?
Por otra parte, me gustaría hacer una tabla de rebalanceo a partir de la hoja «Resumen». Yo voy a dividir mis subcarteras en Liquidez, RF, Acciones, Indexación y Gestión Activa. Quiero darle un peso objetivo específico a cada inversión para que me calcule automáticamente la redistribución de activos, para hacer un rebalanceo anual. Me parece súper automatizado lo de que «Subcarteras» pueda ser editado en «Datosinversiones» y «Resumen» para filtrar las aportaciones y es lo que más o menos quiero exportar para calcular los pesos en mi cartera. He estado cotilleando de dónde vienen los datos de las gráficas y demás, y enlazan a tablas dinámicas (que no sé muy bien lo que son) que no puedo editar. ¿Dónde puedo encontrar instrucciones para realizar esta tarea? Y si quisiera hacer rebalanceos dentro de cada cartera (en gestión pasiva compro fondos regionales), ¿podría añadir una subcolumna en «DatosInversiones»?
Espero no haberme alargado mucho.
Otra vez: muchísimas gracias.
Rodrigo, Tanta duda me desborda un tanto. Te puedo indicar alguna cosa concreta, pero en tanta casuística no puedo entrar.
Te invito a que aprendas excel avanzado por tu cuenta (incluyendo tablas dinámicas), y después intentes entender cómo está hecho este excel, para que de esa forma tú puedas hacer las modificaciones que veas oportunas.
Saludos
¡Gracias! Intentaré hacer eso. Sólo la duda principal: ¿cómo puedo borrar los datos previos de «Rentabilidad»?
Rodrigo, todo lo que tenga fondo amarillento, es editable y lo puedes borrar. Las casillas en fondo blanco contienen fórmulas y no hay que tocarlas (salvo que sepas lo que quieres hacer). Te aviso que la pestaña Rentabilidades es la más difícil de manejar de todas. Saludos
Buenos días,
Ante todo muchísimas gracias por todo lo que haces por la comunidad, Antonio.
Tengo una duda respecto al TWR que no se decir si es matemático-financiera o de la propia hoja de Excel.
En la pestaña «Rentabilidad» me aparece:
– TWR acumulado desde inicio del global de la cartera: negativo
– TWR acumulado desde inicio de cada una de las subcarteras: positivo
¿Es posible que todas las subcarteras tengan TWR positivo desde inicio y sin embargo el global de la cartera sea negativo?
Es cierto que no empecé todas las subcarteras en el mismo instante, pero me sigue pareciendo contraintuitivo.
Gracias de antemano.
Hola José Carlos,
Eso que comentas no es normal. Seguro que es alguna fecha o dato mal metido. Mira a ver si lo descubres indagando en el excel. Si no das con ello te lo puedo mirar.
Saludos
Antonio
Hola Antonio,
Gracias por tu respuesta.
He estado “bicheando” en el Excel y haciendo pruebas… y todo parece estar bien. He comprobado la consistencia de los datos de con las aportaciones incluidas en la pestaña “Datos Inversiones” y de nuevo, todo parece correcto.
Mi único razonamiento es que al tener cada subcartera fechas de comienzo distintas, no puedo inferir que porque todas sean positivas a día de hoy la de la cartera global también lo sea. La realidad es que el TWR desde inicio, me arroja ese resultado.
Me sigue pareciendo raro en cualquier caso.
Gracias,
Hola, muchas gracias por compartir la plantilla. Voy a empezar a rellenarla a ver si me ayuda a llevar un mejor control de mi cartera.
Tengo una pregunta quizás algo «tonta», pero después de leer los artículos y comentarios no encuentro respuesta.
Según comentas, únicamente hay que rellenar las celdas en amarillo y las blancas no tocarlas ya que tienen fórmulas. Entonces, en DatosInversiones, las celdas de las columnas de Venta están en blanco, ¿cómo las rellenamos?
Si compramos y X tiempo después vendemos, ¿reflejamos ambas operaciones en la misma línea? Me ha parecido leer que sí, pero no estoy seguro.
Muchas gracias!!
Hola chusti,
Pues tienes toda la razón. La venta se anota en la misma fila, sí. Y por tanto es raro que esté en blanco (mientras no lo vendas hay una fórmula sí, pero al venderlo quitas la fórmula al anotar fecha y precio y pones el fondo amarillo).
A ver si próximamente arreglo estas incoherencias, y preparo la venta para que sea más intuitiva de hacer, y no surjan estas dudas lógicas (nada de tontas).
Gracias por comentar.
Antonio
Hola Antonio,
Muchas gracias por tu respuesta, de verdad. Hoy he empezado a introducir las operaciones y ha sido llegar a la parte de dividendos y volverme loco, y eso que solo estoy con el primer valor!! (ACS).
Yo los dividendos cuando son flexibles, suelo recibir las acciones que me corresponden a coste 0 y el resto de derechos que no cuadran los vendo. Pues bien:
Me meto en los extractos/certificados del banco (ING) y en la parte de Eventos Financieros tengo un lío que no me entero de nada y no se qué introducir en la plantilla.
Hay operaciones varias como: Baja por canje, Alta por canje, Baja derechos, Suscripción, Genera derechos… Unos coinciden en fecha y otros no. El valor es el mismo (ACS) pero justo al lado aparece una fecha que entiendo que es algo relacionado con el dividendo. El importe es cero todos los casos y los títulos varían. Abro el concepto para obtener más información como he leído que hace otro usuario, pero me quedo igual.
Para un novato como yo en el mundo de las inversiones tanto número y/o concepto puede abrumar un poco, pero aún así soy terco y me propongo tener una buena y actualizada plantilla.
PD.: Parece que he resuelto el tema de los colores.
Muchas gracias de nuevo.
Hola Antonio. Maravilloso trabajo con el Excel que has realizado. Quería preguntarte una duda de principiante. ¿Me podrías decir los diferentes posibles tipos de subcarteras para los distintos tipos de instrumento financiero que hay? Por ejemplo, para Acciones, la subcartera de acciones, para Fondos de inversión, la subcartera de gestión activa e indexado pasivo. A partir de ahí me pierdo.
Muchas gracias por tu tiempo y tu trabajo.
Un saludo.
Hola Quique, el campo subcartera es libre, por tanto puedes poner el nombre que quieras, o mejor defina tu estilo de inversión en cada subcartera. Saludos
Hola Antonio,
Estoy intentando incorporar la venta de PUTs a la hoja de calculo y tengo el siguiente problema:
– Relleno el TICKER con el compromiso de pago. Por ejemplo, para esta opcion PUT REE 14,50 17SEP21 entiendo que el compromiso es 1450 EUR (100 acciones de REE a 14,50 EUR/accion). Es asi?
– Que tengo que poner en numero de unidades compradas? -1,00 si es una venta de PUT? +1,00 si es una compra?
– Que tengo que poner en precio pagado por unidad?
– En el coste bruto total de la compra se pone el mismo valor que el ticker?
– La fecha de venta o traspaso se pone la fecha en la que expira la opcion?
– En que campo se mete la prima cobrada?
Gracias por tu trabajo y un saludo
Hola José Luis,
Perdona la demora de esta ocasión…Te contesto:
-Sí, efectivamente para el ejemplo que pones: PUT REE 14,50 17SEP21 el compromiso es 1450 EUR (100 acciones de REE a 14,50 EUR/acción.
-Efectivamente, en número de unidades compradas pones -1,00 si es una venta de PUT, y +1,00 si es una compra
-En precio pagado por unidad pones la prima cobrada al vender la PUT
-El coste bruto total de la compra, tiene una fórmula que te multiplica las unidades compradas por el compromiso. En tu caso te debe aparece 1450 EUR.
-La fecha de venta o traspaso se pone la fecha actual, y cuando expire pones la de expiración, o la fecha en la que recompres la PUT.
-La prima cobrada va en «precio pagado por unidad» (en apartado de compra)
Se que está un poco lioso, pero es que al vender primero las ventas aparecen como compras con signo negativo, y las recompras o expiraciones van en la zona de venta.
Mira los dos ejemplos de ventas de PUT que hay en la plantillla para Bayer y Inditex
Espero te haya ayudado
Saludos
Antonio
Gracias Antonio,
Si que me ha ayudado. Lo que veo es que entonces la prima no aparece en la pestaña de primas. Aparece cuando expire la opcion? Igual con la pestaña de rentabilidad (columna AG).
Correcto, cuando cierres o expire la opción tendrás la prima realizada anotada en primas.
Hola Antonio,
He terminado de pasar los datos de mi cartera y no puedo mas que felicitarte. Increible el trabajo que has hecho y quehayas decidido compartirlo con todos nosotros. He empezado a seguirte desde hace poco y siempre estoy al pendiente de una nueva publicacion o modificacion a la versión.
Tengo una duda que comentas en un post anterior y es en el cambio de la Subcartera. Si modifico el valor en “Datos de Inversion” de mis Cryptos para no manejarla dentro de “Indexado Pasivo” hace que me desaparezca mis cryptos de la “Cotizacion” y de las gráficas de la prestaña de “Resumen”.
Estoy en Mexico y mucha de la terminología de algunas pestañas aún no se como adaptarlas a mi país pero estoy tratando de sacarle provecho al 100%.
Saludos, Cancún Mexico
Problema resuelto. Cuestión de activar el filtro en la pestaña Resumén.
Buenos días Antonio, llevo usando tu plantilla ya bastante tiempo y te estoy muy agradecido, me es muy util, ahora me ha surgido una duda… tengo acciones de bankia y ahora con la fusión no tengo claro como debo proceder en el excel para que todo quede ok, sabrías decirme como hacerlo? gracias por todo. Un saludo
Hola Roberto, Me alegro que te resulte útil. En ese caso como solo es un cambio de intermediario sin venta de acciones, simplemente cambia el nombre del broker en cada fila de datosinversiones. Saludos
no me refiero a un cambio de broker sino a la fusión de bankia con caixa bank, lo que he hecho es en «datos inversiones» (en las dos filas de compras que tengo de bankia), he cambiado los datos de bankia por caixa bank y he calculado el nuevo numero de acciones y el precio de cada compra de acciones… es decir, como si hubiese hecho las compras de caixa bank en vez de bankia….no se me ocurre otra forma…no se si es lo mas correcto… ni siquiera se si me explico…jejje
Ah, vale, que eras accionista de Banki, no cliente. Pues sí, creo que esa es una buena forma de anotarlo. Saludos
perfecto, gracias por todo
Pena que no calcule la ganacia o perdida por divisa cuando vendes una accion usa en cuenta multidivisa de forma automatica.
La veo compleja de usar, imagino que no habra otra manera mas facil de hacerlo.
Hola Antonio,
Ante todo agradecerte que pongas a nuestra disposición esta herramienta de gran utilidad, y tu dedicación en contestar nuestras dudas y consultas.
Yo tengo 2 consultas y un problemilla técnico (al parecer).
1. Respecto a la comisión del 0,30% que aplica Selfbank en las compras y ventas de acciones en divisa distinta del euro, en una consulta que te hicieron el 24.04.2019 decías que quedaba recogido ese coste si introducimos en la columna “Cambio divisa (Moneda/EUR) en compra” el tipo de cambio aplicado por Selfbank, que incluye la comisión.
Entiendo que sólo se contempla de cara a la declaración del IRPF en caso de que se produzca una venta de ese activo y se produzca una plusvalía, en cuyo caso se resta a la compra la comisión de compra (la comisión de cambio divisa ya viene recogida en el propio precio), y a este valor se le resta el valor de la venta menos la comisión de venta, viniendo también incluida la comisión de cambio divisa el precio de la venta. No obstante, no veo que ese 0,30% quede contemplado en ningún sitio en caso de no producirse una venta, es decir, a los efectos de calcular la suma total de las comisiones anuales qu enos cobran los brokers. ¿Es esto correcto, o hay algo que se me escapa?
2. He leído en alguna consulta cómo tratar la venta de derechos, pero no he encontrado nada sobre la compra de derechos.
En concreto, compré 4 derechos de REPSOL, que sumados a 576 derechos correspondientes a 576 acciones, sumaban 580 derechos, que me daban derecho a 20 acciones (1 acción por cada 29 derechos), es decir, que compré 4 derechos para completar 25 derechos sobrantes y conseguir una acción más. Mi duda es cómo contemplar esto en el excel. La opción que veo sería ponerlo en datos inversiones como una compra de 1 acción al precio de compra de esos 4 derechos, más la comisión de compra en su celda correspondiente. ¿Estoy en lo cierto?
Por último, me sucede como a otros compañeros, que al cargar las cotizaciones con la fecha que sea, empieza a cargar correctamente y, cuando llega a la acción nº x, ya no la carga, saliendo un signo de exclamación desde esa acción hasta todas las que quedan hasta el final, sean 5 ó 10, ya que no siempre sucede con la misma empresa. Parece un problema técnico. ¿Sabes si se puede solucionar?
Muchas gracias por todo y gracias por anticipado
Hola Narro,
Respecto a la primera pregunta la comisión de divisa queda ahí oculta en un tipo de cambio menos favorable, se tiene en cuenta para calcular la rentabilidad, pero no te aparecerá explicitamente como un gasto ni en el excel ni en los informes fiscales del banco. No se si te respondo.
Antonio
Sí sí, tema aclarado, muchas gracias
Narro, respecto los derechos de repsol, si al final has comprado 20 acciones, quizás sea más correcto anotar esa compra de 20 acciones con el precio total incluyendo lo que te costaron esos 4 derechos extra. Nunca he tenido ese caso. Si alguién lo tiene claro, a ver si lo comenta. gracias
Hola Antonio, no compré 20 acciones, sino 4 derechos, que sumados a los 576 ya existentes me daban derecho a 20 acciones (script dividend). Si no hubiera comprado los 4 derechos me habrían tocado 19 acciones y me habrían sobrado 25 derechos.
En todo caso, en tanto no haya una sugerencia distinta, lo imputaré como comentaba en la pregunta inicial.
Muchas gracias por tu tiempo.
Narro, el fallo en la carga de precios hace tiempo que ya no lo veo aparecer. Hace varios meses se actualizó el excel y parecía más estable en ese sentido. Pero es posible que a algunos os ocurra de vez en cuando… es un tema de yahoo finance y el tratamiento de cookies, y en algún momento salta una protección de ellos y su web y no entrega más datos. Si pruebas con una plantilla reciente descargada puedes probar si se te resuelve, y si eso haces una migración.
Suerte con ello
Antonio
Perfecto, probaré como dices, gracias.
Sin duda, esta plantilla es una genialidad!
Tengo una duda, no me coge la divisa DKK (Coronas Danesas) en los datos inversiones, no la reconoce como divisa en el resumen. ¿Como pueblo añadirla?
Me parece una genialidad de plantilla!! Gracias por compartirla.
Una consulta: No me reconoce las DKK como divisa (Coronas Danesas) y no se aplican al resumen como divisa. ¿Puedo añadirla de alguna forma?
Gracias de antemano
Miguel,
Las nuevas monedas, como DDK las debes poner en cotizaciones y darle a actualizar precios.
Si tras ello no se ve en las tartas de la pestaña Resumen, es posible que algún filtro la oculte. Mueve los gráficos y en la tabla dinámica de debajo, ajusta el filtro o dale actualizar con el botón derecho.
Espero te sirva.
Antonio
Hola Antonio, estoy empezando a trabajar con esta hoja que a primera impresión me parece brutal, dicho esto al abrir y actualizar cotizaciones, me sale un mismo error siempre que dice: “Error al gestionar actividad.Error: -2147024891 Línea código: 21”. Y no sé si tiene que ver pero en la pestanña Cotizaciones, al devolverme el precio, me dice “1” y evidentemente ese no es el precio, además en el cuadro del check, me sale un aspa roja. Me puedes ayudar con este errror.
Gracias
Hola a todos, en cuanto vuelva de vacaciones en 2 semanas me pongo con ello. Necesitaba desconectar bien. Os pido un poquito de paciencia, a ver si damos con el fallo. Saludos
Hola a todos,
He revisado el error del excel con este mensaje: «Error al gestionar actividad. Error: -2147024891 Línea código: 21». A mi también me aparecía, lo he visto hoy al actualizar mis inversiones.
Parece que ya está identificado. He probado con mi versión, y una nueva que he puesto en la web (la 3.53), en la zona de descargas excel, y parece que ya no da problemas la nueva plantilla 3.53.
Si alguien quiere probar dicha nueva plantilla y verificar que ya se ha resuelto en su ordenador, encantado de saberlo.
Disculpad el retraso en ponerme con ello.
Saludos
Antonio
Buenas tardes Antonio,
Coon la última versión sigo obteniendo el error de conexión a internet con el mismo código -2147024891. ¿Sabes el origen o la solución?
Gracias.
Saludos.
Adrián, Creo que no debería aparecer ya, salvo que tengas a veces un fallo real de conexión. Ya me dices si el error es sistemático. Al resto de usuarios que lo han actualizado, que yo sepa, me han dicho que les va bien ya.
Saludos
Antonio
Buenas noches, Antonio.
Lo primero, agradecerte el currazo que te tuviste que pegar para crear esta maravilla y compartirla con todos. Llevo un par de meses metiéndolo todo en la hoja y me da una seguridad tremenda. Es la primera vez que voy a tener que hacer la declaración y esas pestañas de IRPF creo que me van a ayudar mucho (al menos para estar más seguro de que todo va bien).
Quería comentarte una dudilla: He leído en algún comentario que tenías pensado hacer una version 3.6 multidivisa pero que no lo veías nada claro. A mí no me afectaba en nada hasta ahora (tengo algo en Revolut de cuando empecé, pero poca cosa y quiero ir cerrando posiciones), pero con la cerdada del cambio de tarifa en Degiro, he decidido abrir cuenta en IBKR e ir pasando allí las acciones USA. Para ello el trato de la multidivisa (al menos USD) se me hacía indispensable, por lo que he creado una pestaña adicional «Forex» basándome en Datosinversión y llevando allí la contabilidad de las compras y ventas de USD, y poniendo el cambio EUR-USD de las acciones en compra de Revolut igual al de venta o actual.
A priori no me ha parecido demasiado complicado y eso me ha llevado a pensar que me estoy olvidando de algo y no es tan fácil. La otra opción que veo (ojalá) es que al operar solo con 1 moneda extranjera y sin mirar las pestañas de resumen, rentabilidad, etc… no es tan difícil.
Por eso quería consultarte qué dudas o problemas veías para hacer este excel multidivisa.
Gracias de nuevo y un saludo.
Hola Chus,
El tema de las versiones de los excel lo tengo parado. Sigo esperando a que hacienda me responda a una consulta sobre la declaración en cuentas multidivisa. Ya van 11 meses de espera, y me han avisado que está tramitado pero a espera de firmas. Esperaremos más… paciencia. La idea es que en función de la respuesta implementar la versión definitiva y a su gusto, para luego evitar problemas por declarar mal.
En principio, lo que parece el modo aceptado es llevar la contabilidad a parte tal y como dices de compras y ventas de divisas, en una pestaña DatosForex, pero es más complejo pues todas las ventas con plusvalía/minusvalías que hagas o dividendos que cobres en USD, también aportan anotaciones para forex que además van con método FIFO. Si hacienda me dice que se puede hacer de otra forma más sencilla que les propuse, pues facilitaría las cosas. De todas formas si tú ya lo has estudiado y lo tienes claro, encantado de escuchar tu propuesta.
Un saludo
Antonio
Está claro que es un engorro. A mí no se me hizo tan pesado porque lo suelo llevar al día y tampoco tenía muchas operaciones/dividendos con Revolut (y tampoco creo que con las que vaya a hacer en IBKR me resulte tan complicado), pero no he probado a vincular la pestaña con el resto (por lo que resumen, rentabilidad, IRPF… quedan incompletas).
Se me había ocurrido que el «rendimiento mercado» de las operaciones cerradas de los brokers multidivisa (no sé cómo de complicado puede ser esto, pero supongo que como mucho añadiría una columna en la que marcar un 1 o 0 según queramos que esa operación pase a Forex o no), fueran a DatosForex como una compra con fecha y tipo de cambio de la venta. Ídem con los dividendos; y solo sería necesario meter a mano las compras y ventas de moneda (estas ventas sí que me parecen muy pesadas por el tema de coger varios ingresos juntos, dividir el último en 2 trozos para que cuadre con la cantidad de venta….). Pero de tablas dinámicas no sé nada, así que todo esto no sé si es factible o no. Supongo que no es nada fácil si tú no lo vista nada claro.
Por otro lado, yo ya había aceptado tener que declararlo así (por la consulta vinculante V2422-20, que supongo que es a la que te refieres con el modo aceptado por Hacienda), pero la verdad que estaría bien tener una forma más simple de regularizar todo esto. Llevo desde que Degiro sacó la nueva tarifa comiéndome el coco hasta el punto que casi no sé ni como les va esta semana a las bolsas… A ver si te responden de una vez y podemos hacer la declaración de una forma más fácil.
Un saludo y gracias por tu tiempo.
Hola:
En la pestaña de datos inversiones la suma del total de la columna «Rendimiento dividendos netos (euros)» debe de ser igual a la suma de los dividendos cobrados?
Porque en mi caso no es así.
Gracias por esta maravilla……
Hola, Paco. Debería ser igual, pero he visto que no coincide exactamente. Me debo haber equivocado en alguna fórmula. Sí logras averiguarlo, comparteló porfa, que hoy estoy agotado…para mirarlo yo.
Gracias
Antonio
Hola, Antonio. Siempre he tenido una pequeña diferencia entre ambos valores, pero actualmente (no me he dado cuenta hasta hoy) después de más de 100 apuntes la diferencia entre ambos valores es de unos 1200€, he estado revisando hasta donde yo se y no encuentro que error he cometido o si es problema de formulación.
Gracias.
Saludos.
Hola.
Me han regalado un Mac y no soy capaz de hacer funcionar el Excel de inversiones.
Creo que hay gente a la que si le funciona, pero no encuentro la respuesta, ni aquí ni en twitter.
He habilitado las macros en Excel, pero sigue sin funcionar.
¿Alguien me puede echar una mano?
Gracias.
Luismi,
Con Mac creo que no funciona, al menos la lectura de cotizaciones y otras macros. Si alguien sabe cómo hacerlo funcionar que diga algo… O que me regale un Mac y lo depuro..jeje
Saludos
Antonio
Hola Antonio,
Desde hace unos días no se me actualizan los precios de las acciones (yahoo) aunque si los de planes de pensiones y cambio de monedas.
Estoy usando la versión 3.5. ¿sabes si es error mio, de yahoo o de la hoja?.
Gracias por atenderme
Saludos
Javier
Hola a todos,
Me han avisado que desde ayer vuelve a fallar la lectura de la moneda de acciones en yahoo finance.
Indagando he visto que yahoo ha cambiado algunos string en el código html de sus página web y por eso no leía la moneda de las acciones generando un error en la línea 7.
He subido una nueva versión 3.6 a la web. Esto debería resolver el problema a todos (al menos en mi PC funciona).
Siento los problemas que tiene el web scrapping… cuando algo cambia en las web externas hay que andar retocando el código.
Saludos
Antonio
Muchas gracias por responder tan rápido.
Miraremos. La nueva 3.6
Buenos dias de nuevo,
Probado en la nueva versión y sigue dando el mismo error.
Saludos
Javier
Hola,
La nueva versión es de ayer jueves por la tarde. Esa misma versión la acabo de probar y va bien en mi caso.
Para simplificar la carga desde yahoo, en breve voy a preparar el excel para que no lea moneda de Yahoo, y solo lea la cotización, que esa parte es más estable y no está cambiando en la web de yahoo. Con ese cambio nos evitamos problemas y además la lectura de cotizaciones será más rápida.
La nueva versión se llamará 3.7.
Los que os dé pereza volver a pasar los datos a la nueva 3.7 que salga, como en otras ocasiones os podría ayudar a reactualizar vuestro excel actual a la nueva versión…
Saludos
Antonio
Muchas gracias Antonio,
Esperare a la 3.7
Saludos
Javier
Muchas gracias por la respuesta, a ver alguien encuentra algo.
Mientras sigo usando mi viejo portátil solo para seguir mi cartera.
Buenas noches Antonio,
Voy introduciendo los datos en la nueva versión 3.7 y me parece que está genial. Los precios cargan super rápido.
Un fallo que he encontrado es que en la pestaña RENTAS no aparece la columna del coste en euros, dando lugar a error en el YOC. ¿Si arreglas ese error es posible copiar sólo esa hoja en mi libro que ya tengo pasados todos los datos?
Gracias por tu tiempo y esfuerzo.
Saludos
Javier
Hola Javier,
El fallo del YOC está resuelto ahora desde hoy en versión 3.7 retocada en la web.
Copiar podrías copiar solo esa hoja, pero faltaría la macro. Aunque estoy saturado, podría intentar encontrar un hueco para retocarte eso en tu excel (por privado), si quieres.
Antonio
Hola Antonio.
Solo comentarte que al bajar la version 3.7 ,me salta el antivirus de Windows indicandome que se ha detectado el virus
Win32/Uwamson.A!ml. en el archivo. No se si es solo cosa mia o es general.
Por lo demas felicitarte por la magnifica excel que has hecho y darte las gracias por tu esfuerzo.
Un saludo
Hola Rogelio,
Eres el tercero que me lo dice, gracias por avisar. Lo he mirado ya, pero no he detectado nada raro. No sé si será simplemente un falso positivo del antivirus. Solo sé que la copia descargable de la web es la misma que uso localmente en mi PC y yo no veo nada raro, ni ningún aviso. Lo estudiaré más especialmente si hay más casos.
Saludos
Antonio
Benas tardes Antonio, llevaba como un mes sin actualizar mis cotizaciones en mi hoja excel (version 3.4) y hoy al actualizarlas me aparece un error (error ticker) esto aparece dentro de la pestaña cotizaciones, en la columna moneda, sabes a que puede deberse? como puedo solucionarlo? gracias por la ayuda.
Hola, mismo problema de virus con Antivirus Defender
Pues una pena que os salga ese aviso. Ya lo siento.
Hola Antonio, veo que has escrito y ando un poco desesperado… te puse esto mas arriba… te lo vuelvo a poner por si me puedes ayudar. «Buenas tardes Antonio, llevaba como un mes sin actualizar mis cotizaciones en mi hoja excel (version 3.4) y hoy al actualizarlas me aparece un error (error ticker) esto aparece dentro de la pestaña cotizaciones, en la columna moneda, sabes a que puede deberse? como puedo solucionarlo? gracias por la ayuda.»
Hola Roberto,
Se me pasaría responder, perdona. Hay una versión nueva, la 3.7. Prueba a descargarla y ver si ya no te da el problema. Si es así, tendrías que pasar los datos de la 3.4 a la nueva. Espero que vaya todo bien por lo demás.
Saludos
Antonio
Hola Antonio:
Ayer intenté actualizar las cotizaciones y aparece el siguiente error…
«Error en get coockie crumb»
Sabes a que es debido?
Gracias
Buenos días, me sale el mismo error, simplemente de descargué la ultima versión y la ejecuté a ver que tal y me sale esto: error en getCookieCrumb. Error: -2147012746 Línea: 5. Sabes a qué puede deberse? gracias por la ayuda
AVISO: Ya está disponible para descarga la nueva versión 3.7 (internamente la 3.72) que resuelve el problema del error en cotizaciones que apareció el 28 de abril (motivo: Yahoo cambió algo sobre cookies en su web). Podéis probar a ver que tal va esta nueva versión, y si dura el arreglo en el tiempo.
Saludos
Antonio
Buenas tardes Antonio, estoy usando la plantilla 3.74 y me funciona muy bien, ahora he añadido mi primera acción china, he creado la moneda correspondiente (HKD), pero en la pestaña «Cotizaciones» no me hace el cambio para hkd, no me aparece, en su día añadí USD y GBP y sin problemas…sabes a que puede deberse? gracias por la ayuda
Hola Roberto,
En la pestaña de cotizaciones, pones en la columna K el ticker de tu moneda nueva: HKD, y con eso tras pulsar el botón de «Actualiza Cotizaciones» te debe coger la cotización, actualmente a 8,2702. Lo acabo de probar en la plantilla y va perfectamente. Ya nos dices.
Antonio
ah ok!! muchas gracias, pensé que con crearlo en la pestaña «configuración» era suficiente, no recordaba ese paso. Muchas Gracias!!
Hola,
Estaba buscando porque me gustaría tener algo de orden en mis inversiones y algo que me calculase la rentabilidad y me encontré con tu plantilla que me tiene una pintaza! Enhorabuena y muchas gracias por compartir esta herramienta con los demás y por contestar a todas las preguntas que se te hacen que veo que pocas no son.
Estuve leyendo los pasos para empezar con el Excel y también unos cuantos comentarios a ver si encontraba la respuesta a mis dudas pero no la he visto (no digo que no esté), a lo mejor porque es muy obvio, pero es que a mí se me hace muy raro tener una plantilla con todos esos datos que hay supongo que a modo de ejemplo.
Hay que empezar por la pestaña de Config, ok, ahí se borra lo que no se utilice? Yo añadí al final de la lista de valores/fondos/etc los que tengo en cartera, pero no sé si borrar los otros que están ahí porque si no son los de mi cartera ¿para qué los quiero?
En la pestaña de Datos Inversiones es donde anotaría las compras y ventas:
– ¿Borro todo lo que hay de ejemplo?
– ¿Escribo encima de los valores que están de ejemplo?
– ¿No borro nada y añado filas que son las que completo?
Una vez que sepa como es esto, ¿se cambiarán los datos de las demás pestañas: Rendimiento, Rentabilidad, etc.?
Perdón si todo esto es más simple de lo que me parece, pero quiero hacer las cosas bien sin dar palos de ciego.
Gracias de nuevo y un saludo.
Hola David, Efectivamente todo lo que hay es a modo de ejemplo. Te recomiendo que vayas añadiendo tus datos particulares, y cuando veas que te funciona y entiendas cómo funciona, te pongas a borrar las filas de los ejemplos. Al final es un Excel y debes entender lo que hace cada pestaña y celda, para poder asegurarte que funcione e incluso puedas adaptarlo a tu gusto. Un saludo
Antonio
Hola, muchas gracias por el excel. Estoy teniendo problemas con para que lea la cotización del PP de indexa de «Más rentabilidad bonos» . He probado de todo, con el ticker de yahoo 0P0001971N, con el cif V86516903 (N5137) y con «indexa-mas-rentabilidad-bonos» y no hay manera. Te agradecería si me pudieras ayudar.
Hola Alberto, Si es un plan de pensiones de memoria te digo que creo que debería ser este ticket de investing: «indexa-mas-rentabilidad-bonos-pp»
Un saludo
Antonio
Gracias Antonio, funciona como dijiste, me faltaba poner el «-pp». Otra duda: ¿Los datos del SP TR con el que comparamos la cartera deben ir en euros o en dólares si estamos en España? Me extraña que el link que pones de investing para el SP vaya en dólares y para el MSCI World en euros. Gracias!
Hola Alberto,
Me alegro que te funcione. En esos campos de comparativa puedes poner el índice que mejor se ajuste a tu cartera. Es solo un ejemplo, para que lo adaptes a tu gusto.
un saludo
Antonio
Buenas tardes Antonio, tengo un problema con tu excel a ver si me puedes ayudar. Tengo compras y dividendos apuntados desde el 2014 hasta día de hoy, sin embargo en la pestaña «Rentas», no me aparece información del año 2014, del 2015 en adelante sí, pero los dividendos cobrados en 2014 no me aparecen, sabes a que puede deberse? gracias de nuevo por la ayuda
Hola Roberto, No te sé decir. Deberías poder mirar las fórmulas, entender lo que hacen y con ello podrás averiguar que está pasando y arreglarlo. A ver si lo averiguas. Suerte. Antonio
Hola Antonio, me han gustado las anteriores versiones de tu programa, pero con la V3.7, al abrir por primera vez el fichero me salta el error «Error de compilación en el módulo oculto: Modulo1_Cotizaciones.
He de aclarar que estoy con Excel 2007.
¿Sabrías decir que ocurre?
UN saludo.
Hola Antonino, me gustaron tus anteriores versiones del excel, pero con esta nueva versión, la 3.7, tengo problemas.
Al abrirle la primera vez me salta el error en el módulo oculto Módulo1_Cotizaciones.
¿Tienes idea de que puede ser? Trabajo con Excel 2007.
Un saludo.
Hola Javier, la verdad es que no sé a qué se deberá, pero si es error de compilación igual es por la versión del Excel que sea algo antigua. Sí Puedes prueba en otro ordenador por si cambia algo y en el otro funciona. Saludos. Antonio
Hola Antonio. En primer lugar felicitarte por tu increíble trabajo, estoy empezando con las inversiones y con tu hoja excel es una manera de ver como van las inversiones muy clara. Me e descargado todas la versiones de tus tablas pero e empezado por la primera versión para coger soltura y ver como funciona.
E ojeado todas las versiones y no se como introducir las letras del tesoro, también tengo un fondo de renta fija con el banco pero con el ISIN no consigo encontrarlo por ningun lado para que me actualice los datos y por ultimo el alquiler de un trastero y una plaza de garaje no se como meterlo ya que forma parte de los ingresos que recibo cada mes.
Gracias por tu tiempo y un saludo.
Las ventas de acciones donde se introducen, en compra pero negativa? en el bloque venta son todo formulas
Juanjo, En la zona de venta quitas las fórmulas y pones los datos de tu venta.
Saludos
Buenas tardes Antonio, de repente no me carga el cambio de divisa en la pestaña «cotizaciones». Al darle a actualizar me pone todos los valores de divisa a valor «1». Sabes como puedo solucionar esto? he descargado de nuevo la plantilla, pensando que he podido tocar algo, pero incluso en la plantilla recién descargada tengo el mismo problema, he probado también en varios ordenadores y tengo el mismo problema. alguna idea? gracias de antemano. un saludo
Hola Roberto,
Es problema de Investing que cambia su web y por tanto tengo que actualizar las macros para que vuelva a leerlo otra vez. En cuanto pueda (estoy liado cerrando cosas del trabajo antes de coger vacaciones) subo otra versión a la web, o a los que quieran les actualizo las macros de su excel. Ya siento los inconvenientes.
Antonio
Siii, te lo agradecería, te dejo un café pagado. Mil gracias, me es muy útil tu excel
Hola Antonio, estoy intentando añadir una acción Polaca pero no me cuadra la información. Lo que hice fue poner en la pestaña de configuración la moneda PLN y la acción.
Posteriormente en datos inversiones tengo nº de unidades 20, tipo de cambio 4,4197 y el precio pagado es 31,3145 y me sale en el coste total bruto 692 (pero esto es la moneda polaca) no en euros.
¿Qué estoy haciendo mal?
Muchas gracias!