Si vives en EEUU o Latinomérica

compra aquí


Busca todo lo que quieras en Invertirenbolsa.info
con este buscador personalizado de Google
:

Búsqueda personalizada


Libro La Economía funcionará cuando TÚ la entiendas de Gregorio Hernández Jiménez (invertirenbolsa.info)

Si vives en EEUU o Latinomérica

compra aquí
Busca todo lo que quieras en Invertirenbolsa.info con este buscador personalizado de Google:
Búsqueda personalizada

Anuncio

Colapsar
No hay anuncio todavía.

Hoja de cálculo para largo plazo y opciones

Colapsar
X
 
  • Filtrar
  • Tiempo
  • Mostrar
Limpiar Todo
nuevos mensajes

  • Hoja de cálculo para largo plazo y opciones


    Consulta aquí qué empresas tienen los inversores en sus carteras en las estadísticas de Invertirenbolsa.info



    Abro este hilo para poneros el excel que me he creado para llevar mis carteras, por si a alguien le pueden ser de utilidad.

    Bueno, excel, está hecho con Openoffice.

    Tiene dos partes, una para una cartera clásica de comprar y mantener y otra para la estrategia de venta sistemática de puts y calls que se está tratando en este otro hilo:

    http://www.invertirenbolsa.info/foro...e-opciones-put

    He intentado automatizarlo lo más posible de manera que solo haya que meter los datos imprescindibles. Permite conocer en todo momento la situación de nuestra cartera actualizada conforme a la cotización del momento.

    Paso a explicarlo:

    El excel cuenta con tres hojas:

    1.- Largo Plazo.
    2.- Put -Call
    3.- Cotizaciones

    Esta última está vinculada a la web de invertia y nos da las cotizaciones del Ibex en tiempo real.

    Si alguno quisiese contar con otras del mercado continuo o del extranjero, le remito a la página de Crazybone donde explica como hacerlo. Es muy sencillito. Muchas gracias a él por su aportación:

    http://www.invertirenbolsa.info/foro...nline-en-Excel

    Bien, empecemos por el principio:


    1.- Largo Plazo.


    Esta hoja está dividida en tres secciones: Compra, cartera actualizada y desglose de dividendos.

    En la primera, tendréis que rellenar 4 celdas por cada valor que añadáis a vuestra cartera. De arriba a abajo y empezando por la celda de color lila: Nombre de la empresa, fecha de compra, precio por acción y número de acciones.

    Los distintos valores se van reflejando de izquierda a derecha empezando por la columna D. La parte más a la izquierda de la hoja, donde aparecen los distintos conceptos y el total de la cartera están fijos, de manera que aunque vuestra cartera sea amplia y os desplacéis mucho hacia la derecha, siempre estén visibles.

    El resto va automático. A saber:

    -Comisiones: Están calculadas para la comisión normal de ING y unas comisiones de bolsa para cuantías entre 300 y 3.000 euros. Si queréis cambiarlas, basta con hacerlo en una de las celdas y copiarla a las demás.

    Esta es la fórmula que aparece:

    =SI(D7="";"";5+(D6*D7*0,25/100)+2,45+(D6*D7*0,024/100)+0,04)

    D6 y D7 son las celdas donde se reflejan el precio y el número de acciones. Si por ejemplo disfrutáis del 0,20 en ING tendrías que eliminar el 5 inicial y cambiar el 0,25/100 por el 0,20.

    Si hacéis operaciones con grandes cantidades, pues adaptar los cánones de bolsa, que son los que aparecen a continuación.

    - Importe de compra: Lo que os cuesta la operación en su conjunto.
    - Porcentaje cartera: Pues eso, que parte de vuestra cartera supone ese valor en concreto.

    Después aparecen otros tres conceptos: Dividendo/acción, Dividendo Total y RPD a precio de compra.

    Para esto tenemos que bajar a la última parte de la hoja, el desglose de dividendos.

    Al poner el nombre del valor en la casilla correspondiente en la sección de compra, veréis que se refleja también en las otras secciones. En la de dividendos simplemente tenéis una tabla en la que poner la fecha y el dividendo por acción que recibís en cada ocasión.

    El excel directamente reflejará el total que lleváis percibido en la parte de arriba.

    La sección del medio es la de la cartera actualizada en tiempo real. Aquí solo tenéis que reflejar un dato, que es el del precio de la acción en las casillas amarillas.

    La forma de hacerlo es la siguiente:

    Os colocáis en la celda correspondiente y escribir la siguiente fórmula:

    =Cotizaciones.X

    Donde X es la celda de la hoja llamada Cotizaciones en la que aparece la cotización actual de ese valor.

    Por ejemplo, tenemos Telefónica, pues pondríamos =Cotizaciones.B37

    Por cierto, la cotización se actualiza cada 20 minutos (podéis cambiarlo si queréis) y cada vez que se abre el documento.

    Y eso es todo lo que tenéis que hacer, el resto de datos saldrán automáticos:
    - Número de acciones: las mismas que comprasteis.
    - Comisión que os cobrarían si cerraseis ahora vuestra posición.
    - Valor actual: Esto es el dinero que realmente ingresaríais si vendieseis al precio de cotización, una vez descontadas comisiones.
    - Porcentaje que supone de vuestra cartera.
    - Ganancia latente: Lo que estáis ganando o perdiendo ahora mismo.
    - Ganancia %: Lo mismo en términos porcentuales.

    Y ahora lo mismo otra vez pero sumando dividendos:

    - Valor cartera + dividendos.
    - Rendimiento: Si vendiese ahora al precio de cotización cuanto sacaría contando la ganancia/perdida que supone vender y los dividendos que he ido cobrando.
    -Rendimiento %.

    Espero que esta parte os sea útil. Ahora sigo con la hoja dedicada a la operativa con opciones.

    Ah, por cierto, si por lo que sea vendéis algún valor, simplemente podéis poner en la casilla del precio actualizado el precio al que vendisteis y si queréis reflejar que se trata de una operación cerrada de algún modo, o abrir una nueva hoja igual a esta donde lleveis un histórico de operaciones ya cerradas, por ejemplo. A gusto de cada cual.

    Decir también que todas las casillas que reflejan ganancias/pérdidas están condicionadas de manera que si el resultado es favorable a nuestros intereses se volverán de color verde y en caso contrario se tornarán rojas.Excel inversion.zip
    Editado por última vez por Tepic; https://invertirenbolsa.info/foro-inversiones/member/1771-tepic en 30-12-2010, 03:41 PM.

  • #2
    Bien, la tercera hoja, la de cotizaciones, ya hemos explicado también para que sirve. En ella podeis ver además los típico datos que os ofrece cualquier página tipo invertia, eleconomista, etc.

    Vamos a la segunda, aunque esta interesará a menos gente:

    2.- PUT - CALL

    La parte de arriba de esta hoja la ocupa una tabla para reflejar la c/v de opciones.

    Hay que introducir la fecha en que hacemos la operación, si se trata de una put o call (hay que ponerlo y bien escrito, porque hay formulas condicionadas a lo que diga esta celda), el valor (subyacente), fecha de vencimiento, número de títulos/contratos, el strike y la prima.

    El excel os calculará las comisiones (siempre según Ibanesto), el beneficio que sacais (prima * 100 - comisiones), la TAE, el capital inmovilizado y el número de días que dura la operación.

    Tres advertencias:

    - En ocasiones , por temas de ajustes, el número de acciones por contrato es distinto de 100, así que en ese caso tendríamos que tocar las fórmulas cambiando ese número por el que corresponda.

    - Capital inmovilizado: Siguiendo la idea de Husky, se trata del dinero que tendríamos que gastar si nos ejecutan. No descuento el valor de la prima, para evitar equivocarnos.

    Esta celda es importante para no caer en descubiertos, y si marcase por ejemplo 800 euros pero en realidad el coste fuese de esos 800 + 20 de la prima, puede que nos descuidasemos y dejasemos en la cuenta solo 800 y al ejecutarnos nos quedásemos en rojo.

    En el caso de las calls, el inmovilizado sería simplemente la comisión de Ibanesto (reitero la advertencia que hice antes respecto a las comisiones de ING), a lo que habría que sumar las acciones, claro).

    - La TAE sin embargo sí está calculada sobre el capital invertido. Es decir, el inmovilizado menos el beneficio obtenido con la prima. En el caso de calls, el inmovilizado incluiría el valor de las acciones al precio de ejecución.

    Advierto, cuidado con la columna M, porque aunque aparece en blanco tiene fórmulas para ayudar con los cálculos.

    Bien, si vais hacia abajo vereis que el resto de la hoja es similar a la anterior, solo que con tres partes: Compra, venta y posiciones abiertas.

    La primera y la última son iguales que las de compra y cartera actualizada de la primera hoja. La segunda funciona también de manera similar, simplemente teneis que poner el precio al que habeis vendido, y si por lo que sea no habeis vendido todas las acciones que teníais (por ejemplo, os ejecutan dos puts y luego solo vendeis una call), cambiar el número de acciones manualmente (por defecto al poner el precio os aparecerá el mismo número que comprasteis.

    Así llevaréis por separado las operaciones abiertas y cerradas para una mejor gestión de la cartera.

    La verdad es que podía haber hecho lo mismo en la otra hoja, pero como la idea era que no hubiese ventas salvo rara excepción lo dejé pasar. Aquí sin embargo las ventas deberían ser mucho más habituales.

    Finalmente teneis de nuevo el desglose de dividendos.

    Y para terminar, volvermos a la parte superior de la hoja, donde tenéis, a la derecha, un cuadro resumen.

    Solo necesitais rellenar las dos primeras celdas, liquidez inicial y aportaciones, que están pensadas por si se decide destinar para los que decidamos destinar una cantidad concreta a esta operativa y luego podamos y ir añadiendo más, aunque eventualmente ese dinero esté en una cuenta remunerada mientras no esté inmovilizado (podéis incluso añadir los intereses que os genere en el apartado de aportaciones).

    El resto de campos se completan automáticamente. Total ingresado por primas, dividendos totales cobrados y ganancia/perdida en las operaciones cerradas permiten establecer finalmente la liquidez de la que disponemos en ese momento y la variación respecto al capital que hemos destinado.

    Las posiciones abiertas se corresponden con el valor de las acciones que mantenemos en nuestro poder a precio actual. La suma de la liquidez y las posiciones abiertas dan el total de la cartera.

    Y finalmente, muy importante, el total inmovilizado, que nos avisará además si resulta que estamos comprometiendo más capital del que tenemos disponible.

    Para evitar que las puts y calls que ya hayan vencido se sumen a ese total, cuando la opción expire simplemente borrad el contenido de la celda en la que se refleja su inmovilizado, o sustituídlo por un "cerrada", "ejecutada" o lo que cada cual prefiera.

    Esto es todo.

    Espero que sea de utilidad.Excel inversion.zip
    Editado por última vez por Tepic; https://invertirenbolsa.info/foro-inversiones/member/1771-tepic en 30-12-2010, 03:43 PM.

    Comentario


    • #3
      Gracias por el aporte Tepic, cuando lo mire con más calma si tengo algún comentario o duda te digo.

      Saludos.
      "Aut viam inveniam aut faciam"

      Comentario


      • #4
        Gracias Tepic por compartirlo con nosotros, yo tb le echare un vistazo con mas calma y te cuento mis dudas o sugerencias.

        Comentario


        • #5
          Hay alguna forma de pasar el open al excel 2003, o mejor me lo bajo?

          Salu2

          Comentario


          • #6
            Originalmente publicado por Tepic Ver Mensaje
            Bueno, excel, está hecho con Openoffice.
            Simplemente espectacular. Y encima con herramientas libres como el Openoffice.

            Si hay gente que esta dudando en darle la bota de oro al Xavi, al Iniesta o al Messi...

            Yo le doy mi Euro de oro a Tepic.

            Gracias por compartir la hoja de calculo, me se están saltando las lágrimas

            Originalmente publicado por Rip Ver Mensaje
            Hay alguna forma de pasar el open al excel 2003, o mejor me lo bajo?
            Mejor te lo bajas, que es gratis y libre. Free as in free beer and also free as in free speech.
            Leña al mono hasta que se aprenda el catecismo.
            Las prisas nunca fueron buenas consejeras.
            Vísteme despacio, que tengo prisa.
            Los experimentos, con gaseosa.
            Lo que se consigue cuando se adquiere la independencia financiera, es ser dueño de tu propio tiempo

            Comentario


            • #7
              Buenas Noches;

              Estos días me veo la Excel de Tepic. Por lo que decís, debe de estar de cine. Yo uso una (made in mine) que lo calcula todo pero es bastante compleja en el sentido de que hay que andar copiando y pegando, añadiendo filas, etc. para que te haga los cálculos. De manera que o sabes un poco de Excel o no puedes usarla.

              Unir en un sólo documento acciones, derivados, etc. sería ideal. Pero en abstracto y con carácter general, no se me ocurre cómo hacerlo. Así que si alguien ha hecho algo que lo reúna, habrá que ponerle un 10.

              Nos hablamos,

              Un Saludo.
              Editado por última vez por Husky; https://invertirenbolsa.info/foro-inversiones/member/756-husky en 30-12-2010, 02:00 AM.
              Tratando de no arruinarme vendiendo opciones (forewarned is forearmed: it's better to be safe than sorry).

              Comentario


              • #8
                Acabo de ver otro tema en el foro en el que se habla de las comisiones de ING y he descubierto que mis conocimientos estaban desactualizados.

                Contaba con que el canon de liquidación tenía un mínimo de 0,04 €, pero resulta que ha subido a 0,1 €.

                Ya está corregido en la hoja de cálculo.

                Seguramente alguno con más conocimientos que yo podría diseñar una fórmula que sirva para toda clase de inversiones, y no solo para las que están en la franja entre 300 y 3.000 € como he hecho yo.

                Así podríamos ir mejorando la hoja entre todos.

                Comentario


                • #9
                  HOla Tepic, gran trabajo el tuyo, sin embargo me surgen varias dudas en la parte del Largo Plazo.

                  Hay que poner una hoja por empresa?? o es para todos los valores??? Lo digo porque si es para todos lo valores no lo veo claro, parece que se mezclan todas las compras y a mi entender es un poco lioso.

                  En mi opinión pondría un hoja aparte con todas las compras de cada valor, una tabla como la tuya de arriba pero para cada valor, digamos 20 tablas para 20 valores en una hoja.
                  Otra hoja idem para los dividendos.

                  Y luego en la que tu tienes poner una solo columna para cada valor recogiendo los sumatorios de las hojas anteriores.

                  No se si me explico, pero es mas o menos como lo tengo yo aunque mas cutre la verdad. Me gusta mucho mas la tuya visualmente.

                  Es decir, tres hojas, HojaCompras, HojaDividendos, HojaValores.

                  Quizás añadiría otra Hoja, que se llamara HojaSeguimiento, o incluiria esa tabla en HojaValores donde tendria otra tabla con los PER, RPD y demás en función de las cotizaciónes actuales.

                  Que opinas?¿

                  Comentario


                  • #10
                    Feliz año antes de nada!

                    La hoja que pongo es para todos los valores. Un valor por columna. Por ejemplo, en la D Santander, en la E Telefónica, en la F BME, etc., etc.

                    Si vas haciendo sucesivas compras de un mismo valor si puede ser interesante tener un desglose. En ese caso puedes añadir hojas y copiar el contenido de la que yo he puesto, o solo parte si en esa hoja solo quieres tener las compras, por ejemplo. Eso a gusto del consumidor.

                    A mí por ejemplo me gusta tenerlo todo a golpe de vista, sin necesidad de ir pasando hojas. Pero ya digo que depende también de tu cartera, supongo que cuando la mía vaya creciendo, vaya promediando en determinados valores, etc. querré tener también distintas hojas en las que ver unos datos u otros. De momento, con una columna por valor me resulta suficiente.

                    También puedes tener una hoja resumen de la cartera y luego otra específica para con los desgloses por valor. O por ejemplo, crear una hoja nueva para cada año, para poder ver resultados históricos y luego otra con la evolución total de la cartera durante toda su vida... Lo que se te ocurra. Se trata simplemente de cortar y pegar tantas veces como quieras.

                    En cuanto a lo del PER y el RPD a precio de cotización, en la hoja llamada Cotizaciones tienes esos datos, que se actualizan también según la haga invertia.com (he comprobado unos cuantos valores y los RPD que ofrecen son fiables, al contrario de lo que me ha pasado con otras páginas como eleconomista.es, que tiene pinta de que no lo llevan tan al día). Puedes simplemente consultarlo ahí o crear una celda en la hoja que tu quieras y utilizar la misma formula que para los precios actualizados (=Cotizaciones.Celda en la que aparece el dato).

                    Saludos.

                    Comentario


                    • #11
                      Gracias Tepic, Feliz año igualmente.

                      Según lo que comentas ya me cuadra mejor todo. El tema de la hoja alternativa para las compras viene muy bien cuando haces mas compras del mismo valor.

                      Como bien dices, a gusto del consumidor y te agradezco mucho tu aportación. Estoy confeccionado una nueva hoja excel, mezcla tuya y mezcla mia con las tres hojas que te comentaba de forma que cuando se introduzca cualquier compra o cualquier dividendo se actualice automaticamente en la hoja resumen de la cartera.

                      Si algún dia de estos la acabo y alguien le interesa la colgaré también.

                      Saludos.

                      Comentario


                      • #12
                        barbaro el excel!
                        felicidades y gracias tepic

                        crazybone, yo ya te pido ese excel...ojala supiera de estas cosas

                        Comentario


                        • #13
                          Hola,

                          Muchas gracias, tepic.

                          Y feliz 2011.


                          Gestionar dinero es algo que absolutamente todo el mundo, sin excepción, va a tener que hacer a lo largo de su vida. No solamente es imposible vivir sin gestionar dinero, sino que la forma en que se gestione es una de las cosas que más determinan la vida de una persona, para bien o para mal, en todos los sentidos. Libros publicados:

                          "Conoce la Bolsa y deja de tenerle miedo (Todo el mundo puede aprender a invertir en Bolsa)" (Nota: Este libro es la "Guía para principiantes")
                          "Educación financiera partiendo de cero (Aprenda a gestionar su dinero para transformar su vida)"
                          "Cómo invertir en Bolsa a largo plazo partiendo de cero (Consiga la jubilación que se merece)"
                          "Más cosas sobre la Bolsa (Aprende más para invertir mejor)"
                          "Análisis técnico y velas japonesas para inversores de medio y largo plazo partiendo de cero (Es mucho más fácil de lo que crees)"
                          "Opciones y futuros partiendo de cero (También es mucho más fácil de lo que crees)"
                          "Pensiones públicas: La esclavitud de nuestra época (Así empobrecen a la población para controlarla y manipularla)"
                          "¡Haz que el dinero sea tu amigo! (Te acompañará toda la vida)"
                          Novela: "Proyecto Sal (¿Podrás cambiar el Mundo?)"
                          "La Economía funcionará cuando TÚ la entiendas (Se trata de TU vida, de verdad)"
                          "Novela: Tú a la UCO y yo a la UDEF (Firmes ante el Poder)"


                          Gregorio Hernández Jiménez en Facebook
                          Gregorio Hernández Jiménez en Instagram
                          Gregorio Hernández Jiménez en Youtube

                          Bájate aquí la aplicación Android de Invertirenbolsa.info

                          Bájate aquí la aplicación Apple de Invertirenbolsa.info

                          Comentario


                          • #14
                            Hola,
                            He adaptado el excel de Tepic añadiendole una hoja de compras que he extraido y adaptado del excel de Rip que se comenta en otro post.

                            Por si a alguien le sirve...

                            PD: Hay que ver lo que se aprende analizando excels

                            PD2: Hay un error a la hora de calcular el porcentaje de cartera en el excel de Tepic. supongo que crazybone no puso el $ y al copiar/pegar salio el error.

                            ale, un saludo!
                            Archivos Adjuntos
                            Editado por última vez por bolsero; https://invertirenbolsa.info/foro-inversiones/member/1758-bolsero en 04-01-2011, 12:05 PM. Razón: correccion de menciones

                            Comentario


                            • #15
                              HOla bolseador, te agradezco la mención pero el mérito no es mío, la hoja excel es la de Tepic.

                              Muy buen excel, el desglose de compras me mola!

                              Comentario


                              • #16
                                uy uy uy, es verdad, lo edito

                                Comentario


                                • #17
                                  Buen regalo de Año Nuevo el de Tepic y Bolseador. Andaba intentando confeccionar algo así, pero con el excel no me aclaraba. Muchas gracias a los dos.

                                  Saludos

                                  Comentario


                                  • #18
                                    Originalmente publicado por Carme Ver Mensaje
                                    Buen regalo de Año Nuevo el de Tepic y Bolseador. Andaba intentando confeccionar algo así, pero con el excel no me aclaraba. Muchas gracias a los dos.

                                    Saludos
                                    Y siendo rigurosos, mi página de compras no es más que una pequeña adaptación de la de IEB.

                                    Eso entre todos a montar el Superexcel o el SuperOpen

                                    Salu2

                                    Comentario


                                    • #19
                                      bueno, le damos gracias al inventor de las hojas de calculo y listos

                                      Comentario


                                      • #20
                                        Libros de Gregorio Hernández Jiménez (invertirenbolsa.info)
                                        Gracias al señor Guillermo Puertas

                                        Salu2

                                        Comentario



                                        Trabajando...
                                        X