Excel completo

[asoma]

Hola compañeros.

Ya estoy terminando este fabuloso excel. Y está quedando genial jejeje! Que gran TRABAJO!!

Tengo una duda en la pestaña "VENTAS".

Es un problema de calculo que nose como resolverlo:

Vendí:
- Ferrovial 215acc y me dieron 1992.91€
- BME 80acc y me dieron 1639.92€

Este dinero es NETO con las comisiones de ING (5€+0,25%) + el canon (2,45 € + 0,024%). Fueron vendidas en el 2011 nose si el canon era distinto .

SOLUCIÓN: Quiero saber como desgloso las comisiones, ver el importe BRUTO y ver cuanto euros me ha quitado hacienda (19%).

Saludos espero que no sea muy complicado. GRACIAS!!

[Raftal]

No entiendo muy bien a que te refieres. En la pestaña ventas ya te sale todo lo que pides, el importe bruto y las comisiones y lo de hacienda, todo desglosado?
Si lo que quieres es ver que importe bruto tuviste en la venta entra en ING y en el desglose de la operación te saldrá. Si no es esto quizá no te he entendido bien, si es relacionado con el excel cuelga algún imprimir pantalla para verlo mejor saludos, me alegro de que te sea útil.

[asoma]

Hola..

Lo siento por no explicarlo bien .

Lo voy a intentar otra vez jejeje.

En el extracto de ING me aparece solo el importe que me han "ingresado". Pero no me aparecen las comisiones de ING ni de CANON, ni Hacienda.. (esto solo me pasa con las ventas) . Lo que necesito es saber es que comisiones me han cobrado tanto en ING como en Canon y hacienda!

En compras me aparece todo detallado!

Gracias!!! Salud

[Raftal]

No estoy seguro de que pueda hacerse, pero creo recordar que en ING al lado de cada movimiento tienes un círculo blanco, si le das click se pone azul (es como que lo has marcado) y luego le das a detalles y te tiene que salir.
Están obligados a darte las operaciones desglosadas, tranki que lo conseguirás si o si, o bien por la web o llamando y piediendolo (te lo tendrían que dar en soporte duradero, papel o internet).
Pero vamos, yo creo recordar que lo que te comento se puede hacer, mira bien la web de ING, en movimientos y marcar uno y dándole a detalles. Saludos.

[Meñique]

En el extracto de ING me aparece solo el importe que me han "ingresado". Pero no me aparecen las comisiones de ING ni de CANON, ni Hacienda.. (esto solo me pasa con las ventas) . Lo que necesito es saber es que comisiones me han cobrado tanto en ING como en Canon y hacienda!

Buenas asoma...

El precio al que se te ejecutó la venta tiene que aparecer en alguna parte sí o sí. Mira el detalle de las operaciones desde la banca online de ING, quizás lo tengas ahí (no te puedo decir que opero con otro broker). Una vez que sepas eso el resto se puede deducir fácilmente... ING 5+0,25% del importe bruto y BME se lleva el cánon y liquidación como en una orden de compra; Hacienda... ya te dará el palo en tu próxima declaración (si tuviste plusvalías) pero en la venta no te retienen nada.

Espero haberte aclarado algo. Un saludo.

[charlio]

Hola a todos/as!
Me he pasado por este hilo y me he quedado gratamente sorprendido por tratar de un tema que conozco de primera mano, ya que he dedicado horas y horas a buscar por internet algun EXcel donde poder anotar todas las operaciones realizadas, y que hiciera unos gráficos super chulos y esas cosas... Existir existen, pero unos son de pago y otros están tan bloqueados que es casi imposible modificarlos al gusto propio, y molesta un poco la verdad.

En fin que quisiera testear la versión beta e intentar ayudar a mejorarlo!

Alguien podría pasar por MP la contraseña?

Muchas gracias!

[asoma]

Hola a todos/as!


Alguien podría pasar por MP la contraseña?

Muchas gracias!

Mandale un privado a raftal

saludos!

[asoma]

Hola Ya he encontrado lo que estaba buscando (comisiones de venta).

Estan en "broker"-->"mi cartera"-->"historico de operaciones". (ING)

Muchas gracias por la ayuda

[ctry]

Muchas gracias por el TRABAJO, y por compartirlo de forma desinteresada. Qué bien quedan mis numeros rojos en la pestaña Cartera .
Un saludo
Ctry

[Raftal]

La pestaña cartera creo que está en color rojo en la mayoria de excels xDDD
Por cierto, veo que has cargado tus datos, si tienes alguna duda pregunta ya que tiene bastantes funciones. A la que veas que una fórmula te casca o cualquier cosa postealo por aquí y lo solucionamos. Espero que te sea útil, saludos.

[p00kylikesfoxies]

Buenos días Raftal,

Al volver a ser fin de semana, puedo seguir jugando con la hoja excel de carteras mientras esté aqui en el despacho pero shhh

Una duda que me salió recientemente fue el incorporar los dividendos flexibles que otorgan los bancos e Iberdrola. Como los reflejariamos en excel? Los añadaríamos como Operaciones a precio 0€ o de alguna manera en la pestaña de Dividendos?

Gracias de antemano por la respuesta

Un saludo

PD: He notado que si incorporo una empresa en mi cartera, sigo comprando otras empresas etc, y luego vendo esa misma empresa, se me queda una línea en blanco en la pestaña cartera donde estaba esa empresa reflejada, en vez de suprimirse esa línea

[Raftal]

En el caso de dividendo flexible no está automatizado porque no es un dividendo, si te quedas las acciones tienes que modificar la pestaña de cartera, coger el dinero disminuye tu precio de compra con lo que también tienes que hacer modificaciones.

Con lo de la línia en blanco es fácil, simplemente selecciona todo lo que queda debajo de la línia y te quedará un cuadrado, ahora lo arrastras una línia arriba y ya lo tienes, saludos

[Stiga]

En el caso de dividendo flexible no está automatizado porque no es un dividendo, si te quedas las acciones tienes que modificar la pestaña de cartera, coger el dinero disminuye tu precio de compra con lo que también tienes que hacer modificaciones.

Con lo de la línia en blanco es fácil, simplemente selecciona todo lo que queda debajo de la línia y te quedará un cuadrado, ahora lo arrastras una línia arriba y ya lo tienes, saludos

Hola Raftal,

En mi Excel yo sí tengo automatizado el dividendo flexible y puedo incorporar automáticamente la modalidad que quiera de las 3. También las ampliaciones no liberadas, como la de MAP. No tengo problema en pegarme el curro de darte la explicación de SMC, si quieres.

Un saludo!

[Raftal]

Adelante, si lo cuentas perfecto bájate si quieres la hoja para ver como se podría mejorar, gracias Stiga

[Stiga]

Adelante, si lo cuentas perfecto bájate si quieres la hoja para ver como se podría mejorar, gracias Stiga

A ver, he estado echándole un vistazo a la tuya y la verdad, no sé cómo podríamos hacer para encajarlo todo. El caso es que la tuya está pensada también para vender acciones y para operar con opciones, y la mía para acumular, sin ventas ni opciones. Así que cuando efectúo una venta me toca echar números (si he cobrado dividendos o si con la venta he ganado dinero) para reflejarlo todo correctamente. Por contra, mi hoja está totalmente automatizada y sólo tengo que representar el movimiento que haya (compra, dividendo o ampliación) y la Excel rellena el resto.

Voy a intentar representarlo todo (lo importante) aunque ya está en el hilo de SMC. Quien vaya a efectuar modificaciones basándose en la explicación que adjuntaré, no le vendría mal tampoco buscar la información que digo, para compaginar porque seguramente me deje algo en el tintero.

Un saludo!

[SMC]

Voy a intentar representarlo todo (lo importante) aunque ya está en el hilo de SMC.

Jo, parece que sigue vigente lo que escribí en su día. Aunque no tengo ni remota idea de lo que estais hablando...

[Stiga]

Excel de la Cartera

Buenas noches,

El archivo Excel que voy a detallar, sacado íntegramente del explicado por SMC en su hilo salvo algunos pequeños matices míos, está formado por 7 hojas:

1. Resumen.
2. Compras.
3. Dividendos.
4. Ampliaciones y derechos
5. Rentabilidad
6. Cotizaciones
7. ElEconomista

No requiere grandes dotes en el manejo de las hojas de cálculo, nada más que lo básico (operaciones entre celdas, fijar celdas, arrastrar, importar datos desde otra hoja, etc) y un poco de lógica. Yo no tenía ni idea de hojas más allá de operaciones básicas de suma, resta, multiplicación y división entre celdas y pude hacerla, hace falta echarle ganas.

Antes de comenzar diré que voy a explicar únicamente las 4 primeras hojas. Las últimas tres son fáciles de hacer, la de Rentabilidad es únicamente para conocer la RPD de toda la cartera, es decir, dividendos reales (no ampliaciones) dividido entre el total invertido. Cotizaciones y ElEconomista son dos hojas de Datos, la primera está sacada de Invertia (http://www.invertia.com/mercados/bol...l=IB011CONTINU) ya que tiene las acciones representadas por el TICKER y la de ElEconomista, sacada del periódico del mismo nombre (http://www.eleconomista.es/mercados/mercado-continuo) para el valor del Ibex35.

Hay que decir que el TICKER es sumamente importante ya que toda la automatización de la página gira a través de él, tanto para sacar los precios de la hoja “Cotizaciones” como para llevar los datos de las hojas 2/3/4 a la hoja 1 (“Resumen”), y todos esos movimientos se llevan a cabo a través de la fórmula SUMA.SI, pero eso lo explicaré después.

Explicaré primero las hojas 2, 3 y 4 que son más fáciles de explicar y acabaremos por la 1, que es la más difícil ya que es la automatizada.

[CONTINUA EN EL SIGUIENTE MENSAJE]

[Stiga]

Hojas: "Compras" y "Dividendos"

A) Hoja: “Compras”



Fácil, que pregunte quien tenga dudas. Se rellenan las casillas: “Ticker”; “Fecha de compra”; “Precio Unitario”; y “Gastos de Bolsa”. El resto de casillas están automatizadas mediante operaciones entre las casillas rellenadas.

Puntos a remarcar: “Días en Bolsa” y “Días*Acciones”.

Bien, como habréis visto “Días en Bolsa” es de las casillas automatizadas, esta se calcula automáticamente restando a la fecha de Hoy, que estará en la hoja resumen, el día de la fecha de compra. Es decir, si compramos una acción el día 1/04/2012, la Excel hará la siguiente resta: “8/04/2012 – 1/04/2012” y el resultado será 7.

“Días*acciones” sirve para cuando tenemos más de una compra en un valor, sacar la media ponderada de los días, dividiendo por el número total de acciones. Es decir, si tenemos 2 compras, una con “10días*100acciones” y una segunda compra “5días*200acciones”, la media ponderada será 2000/300 igual a 6’67 días. Sigamos.

B) Hoja: “Dividendos”



Se rellenan todas menos las que pueden ser automatizadas, en este caso “Dividendo bruto”; “Retención” y “Dividendo Neto” que pueden extraerse fácilmente por operaciones entre celdas, multiplicando la primera, restando la última y la de “Retención” aplicando un 21% (excepto las operaciones del año pasado grabadas que véis, que están al 19% pero ahora con el cambio de tipo el 21%).

Creo que esta es también fácil de entender y de hacer.

[CONTINUA EN EL SIGUIENTE MENSAJE]

[Stiga]

Hoja: "Ampliaciones y dividendos"

C) Hoja: “Ampliaciones y derechos”

La hoja es demasiado larga como para sacarla entendible en una sóla imagen. La tengo partida.

Parte 1:



Parte 2:



Explico la hoja. Como vemos hay 4 grandes apartados relacionados con los derechos de ampliación y un quinto aparte, relacionado con las suscripción real o no de estos derechos y, si hay que desembolsar capital.

Estos apartados son: GENERA, COMPRA, BAJA, VENTA. Creo que es deducible a qué se debe cada uno, pero por si las moscas. “Genera” y “Baja” son ajenos a nosotros y no tienen utilidad práctica en la hoja, es decir, se producen por la propia ampliación si poseemos acciones y suscribimos los derechos. Si poseemos acciones de SAN y da comienzo una ampliación, los derechos que nos otorguen se rellenan en la columna: “Genera” y cuando nuestras acciones se transforman en acciones se rellena las columnas “Baja”. No tienen utilidad real porque lo que realmente se transporta a la hoja “Resumen” en una ampliación de capital es la venta de los derechos o la aceptación de nuevas acciones, y como vemos estas dos columnas no hacen ninguna de las dos cosas

“Compra” y “Venta”, estas son las operaciones que pueden hacerse, voluntariamente, en una ampliación: comprar y/o vender. Como en la hoja “Compras” rellenaremos las fechas, “Número de títulos”, “Precio” y “Gastos de Bolsa”, si bien esta última es una cantidad fija para la cuantía de mis operaciones. El resto de columnas puede hacerse mediante operaciones entre celdas y fórmulas, por ejemplo, las comisiones de ING, un 0’20%. Aprovecho para recordar la fiscalidad de la venta de los derechos que se traduce en una minoración del precio de compra, así pues adelanto, que de venderse, la cuantía de la venta se llevará automáticamente a la hoja resumen. Después lo veremos en la fórmula de la respectiva casilla.

Por último, como había dicho, la quinta y última columna, es “Suscripción Acciones” donde nuestros derechos se transforman realmente en acciones, una cosa muy importante. Y nada, 5 columnas algunas de las cuáles ya hemos visto:

“Derechos por acción” marca la razón de la ampliación, es decir, cuantos derechos hacen falta para que nos brinden una acción nueva; “Acciones” que podemos escribirlo nosotros o automatizarlo llevando los derechos desde las columnas “Genera” o “Baja” y dividirlos por la celda anterior “Derechos por acción”. “Aportación Ampl. No liberada” e “Importe Suscripción” dependen, como dice su nombre, de si la ampliación requiere de un desembolso, es decir que no sea liberada, como la de MAP. Si hay que desembolsar dinero hay que introducir la cuantía por acción en la primera y la segunda, automatizada, multiplicara esta por “Acciones”. Por último volvemos a ver “Días*acciones” que, como expliqué antes, servirá para sacar la media y se llevará a la hoja principal “Resumen”.

[CONTINUA EN EL SIGUIENTE MENSAJE]

[Stiga]

Hoja: "Resumen"

Bien, llegamos al último punto y más importante, la hoja principal:

D) “Resumen”

También larga, dos imágenes:

Parte 1:



Parte 2:



Quedaos con la primera, es la más importante, ahí está el cotarro de la cuestión.

Recordemos el uso de la fórmula SUMA.SI, que es el eje sobre el cuál gira la recogida de información. Estas son las 5 columnas que la utilizan para atraer toda la información de las hojas explicadas arriba:

“Días en Bolsa”. Fórmula para ACS, el primer valor detallado en la imagen:

=REDONDEAR((SUMAR.SI(Compras!$B$3:$B$24;$B8;Compra s!$M$3:$M$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$AI$4:$AI$24))/D8;0)

Esto básicamente dice: Redondea a 0 decimales el resultado de sumar todos los días en bolsa de las filas de B8 (que como vemos en la imagen encaja con el ticker ACS), en las hojas: “Compras!” y “Ampliaciones y derechos!” y dividirlo por D8 (Número Acciones). La Excel tiene que buscar B8 (ACS) en la columna de B3 a B24 en la hoja Compras y si encaja, copiar el valor de la columna M (que es en la hoja “Compras”: “Días*Acciones” es decir, lo que buscamos. Todos los que encuentre los sumará. Las fórmulas están fijas ($) para que no se muevan al arrastrar.

Así funciona el SUMA.SI, espero que se haya entendido. Quien se haya perdido que pregunte.

“Número de Acciones”. Fórmula para ACS:

=SUMAR.SI(Compras!$B$3:$B$24;$B8;Compras!$E$3:$E$2 4)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$AE$4:$AE$24)

Hará lo mismo que antes pero en lugar de buscar y traer “Días*Acciones” la Excel recopilará todas las acciones adquiridas, en las únicas hojas en que pueden generarse, esto es “Compras” y “Ampliaciones y Derechos”.

“Importe Bruto”. Fórmula para ACS:

=SUMAR.SI(Compras!$B$3:$B$24;$B8;Compras!$G$3:$G$2 4)-SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$Y$4:$Y$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$N$4:$N$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$AG$4:$AG$24)

Es la más larga, tiene que sumar todas las cantidades pagadas en compras y ampliaciones pero también restar la venta de derechos, si la hubiese.

“Comisión Broker”. Fórmula para ACS:

=REDONDEAR(SUMAR.SI(Compras!$B$3:$B$24;$B8;Compras !$I$3:$I$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$P$4:$P$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$AA$4:$AA$24);2)

“Gastos Bolsa”. Fórmula para ACS:

=SUMAR.SI(Compras!$B$3:$B$24;$B8;Compras!$J$3:$J$2 4)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$Q$4:$Q$24)+SUMAR.SI('Ampliaciones y derechos'!$B$4:$B$24;$B8;'Ampliaciones y derechos'!$AB$4:$AB$24)


En la segunda imagen, también hay que utilizar el SUMA.SI en dividendos, para atraer todos los cobrados desde la correspondiente hoja de “Dividendos”. Así pues, para ACS:

=SUMAR.SI(Dividendos!$B$3:$B$24;$B8;Dividendos!$H$ 3:$H$24)

El resto de celdas se rellenan automáticamente por operaciones entre celdas, sobre todo la segunda imagen en que todo son variaciones porcentuales del valor, TAE, “Importe Neto” (es igual a la suma del “Importe Bruto” + “Comisiones Broker” + “Gasto Bolsa”), etc.

Puntualizaciones. Lo de arriba del todo son todo cacahuás, excepto la fecha del día de hoy, que es importante para sacar la fecha media de la cartera, así como los días que tenemos un valor (recordemos en la hoja “Compras” que sirve para restar a esta fecha, la fecha de compra, lo cuál da los “Días de Compra”). El día de hoy en Excel se consigue escribiendo: =HOY() .

La parte inferior de la tabla son sumas de las columnas excepto los “Día de Bolsa” y los porcentajes de cambio en valor. La media ponderada de los “Días de Bolsa” se calcula, aunque ya lo he explicado, dividendo la celda V14 (Días*Acciones) entre la D4 (Número de Acciones) de la misma hoja Resumen.

Por último, en "Precios Hoy" tenemos el precio actualizado, que proviene de la hoja de "Cotizaciones" de los datos de Invertia ya que nos basamos en el ticker para llevar datos de un sitio a otro, y esta página los utiliza. Aquí utilizamos la fórmula BUSCARV, para ACS:

=BUSCARV(B8;Cotizaciones!$A:$M;2;0)

Que significa: Busca B8 (ACS) en la hoja Cotizaciones en la matriz que forman las columnas A a M. El 2 es el indicador de la columna en el que está el valor que buscamos (porque lo tengo en la columna B, pero si alguien lo tiene en la D, por ejemplo, al importar datos, debería poner 4) y 0 hace que el valor que busque deba ser exacto.

Y creo que después de una hora escribiendo no me dejo nada en el tintero, aunque posiblemente sí. Preguntad si tenéis alguna duda, intentaré explicarlo pero no seáis perezosos e intentad pensar antes de lanzaros a preguntar.

A por el sobre!!!!

Bona nit.