siguenos en Twitter

lunes, 8 de febrero de 2016

SEGUIMIENTO DE VALOR DE CARTERA EN TIEMPO REAL

En esta entrada les quiero enseñar de forma muy sencilla como llevo a cabo el seguimiento del valor de cartera en tiempo real.
Para dicho seguimiento utilizo Google Drive, insertando funciones desde Google Finance


Para quien no este muy puesto en el tema Google Drive es simplemente la aplicación de Google que permite crear, compartir y guardar todos tus archivos en la nube, es decir, es como el Excel de toda la vida pero de Google, y Google Finance, no es más que otra aplicación de Google con datos financieros de empresas, valores de bolsa…que nos va a proporcionar diferentes funciones, que insertaremos en el Excel creado en Google Drive, para que toda nuestra cartera se actualice en tiempo real.

Que ventajas tenemos sobre un Excel normal:
  • Nos permite tener la cartera actualizada con valor real de la misma cada 5 minutos.
  • Podemos asociar a un valor de cartera: high52 , low52, PER, payout…de forma automática.
  • Podemos tener también el valor del cambio de divisa en tiempo real, para asociarlo a nuestros valores extranjeros.
  • Al estar en la nube lo podemos visualizar donde queramos.
Resumiendo, para mi es la caña, comparado con el excel normal.
Os voy a explicar los pasos a seguir para crear la tabla, que yo tengo para seguimiento:
  1. Crear una cuenta de Google: primer paso e imprescindible, sencillo y rápido, en cinco minutos lo tienes hecho.
  2. Dentro de todas las aplicaciones que tiene Google debemos de irnos a la aplicación de Google Drive.
  3. Una vez dentro de Google Drive si se controla Excel, es muy fácil e intuitivo crear tu tabla de seguimiento.
Lo más importante es insertar en la tabla que creemos las funciones de Google Finance de forma correcta, para que nuestra cartera se actualice de forma automática. 

Vamos a crear una tabla a modo de ejemplo: 


Como pueden ver en la imagen es una tabla sencilla para el seguimiento del valor de cartera en tiempo real.

¿Cómo creamos la tabla?
  • Columna A: nombre de la empresa.
  • Columna B: peso en cartera, % sobre importe total invertido.
  • Columna C: sector.
  • Columna D: aquí viene lo importante y la diferencia con un excel normal, tenemos que poner un código o ticket de cada empresa en cartera que nos proporciona Google Finance. Como sabemos el código? es muy sencillo te metes en Google Finance y en el buscador añades la empresa, dándote el código que debes insertar en la columna D. En nuestro ejemplo, el código que debemos insertar en la celda es BME:MAP, como vemos en la siguiente imagen:

  • Columna E: número de acciones.
  • Columna F: PM.
  • Columna G: inversión, que seria el número de acciones * el precio medio, mas las comisiones si las tienes en cuenta.
  • Columna H: otro punto clave, aquí insertamos una función de Google Financie, como se ve en la foto la función para cotización en tiempo real es =GOOGLEFINANCE (celda D), (la celda con el código que nos da Google Finance), por esta razón es tan importante introducir la columna D de forma correcta para no tener ERROR.
  • Columna I: valor, que sería en este caso la columna anterior por número de acciones.
  • Columna J: valor-inversión, tendríamos en esta celda la plusvalía o minusvalía.
  • Columna K: % de la columna J.
Tendríamos que hacer lo mismo para cada valor de cartera, hecho uno es fácil hacer el resto, sumando todas las filas tendríamos el valor de cartera en tiempo real.


Para cada valor podrán añadir mas columnas con datos como PER, high52, PayOut…, las funciones serán siempre =GOOGLEFINANCE (celda D; valor que quieres obtener), en el caso del ejemplo ”high52”, para mínimo de 52 semanas “low52”, para PER “pe”....


Saludos a todos.

39 comentarios:

Dividendo Veloz dijo...

Buena guia para principiantes, yo poco a poco voy puliendo mi documento de gestión de cartera, la verdad es una herramienta tremenda y facilta mucho el trabajo esto del drive.
Como observación y corrigeme si me equivoco, la función payout no la devuelve googlefinance, yo por lo menos no le he conseguido, y por tanto lo realizo con un calculo sobre el PE y EPS (es decir (dpa/bpa) x100 )
Saludos.

Dividendo Veloz dijo...

Y otra cosa que veo en googlefinance y no entiendo, es por que por ejemplo a abertis les da un BPA (EPS) negativo?

Dividendo Veloz dijo...

Y otra cosa que veo en googlefinance y no entiendo, es por que por ejemplo a abertis les da un BPA (EPS) negativo?

oNLiMiT dijo...

Hola !!!

En la columna "B" pones peso en cartera con respecto al capital total invertido. Yo sin embargo pongo el peso en cartera con respecto a lo que vale actualmente, osea con respecto a la columna "I". Ya que si invierto 1000 y baja de valor a 900 creo que refleja mejor el peso en cartera del valor "actual", no sé si me explico. Quizás se me escape algo y me esté equivocando.

Un saludo.

El Farmacéutico Activo dijo...

Sí correcto, he hecho una primera entrada para seguimiento de valor.
Voy a prepara una más avanzada.
Saludos.

El Farmacéutico Activo dijo...

ABE, tienes razón algo pasa con este valor. Creo que debe de haber un error.
Saludos.

El Farmacéutico Activo dijo...

Buenas oNLiMit, la verdad es que es un poco por elección de cada una, a mi personalmente el peso me gusta más respecto a dinero invertido, pero como te digo es más bien personal. Me parece buena idea añadir también otra columna como tu dices.
Saludos.

Pobre Pecador dijo...

Me ha servido para actualizar mi excel ...Muchas gracias amigo

Anónimo dijo...

Hola, buenos días: Agradecerte el trabajo de explicación. Una pregunta como le insertas el cambio libra/euro o dolar/euro?
Gracias por la contestacion

El Farmacéutico Activo dijo...

Buenas Pobre Pecador, no son tiempos para tenerla actualizada.....pero bueno paciencia ya llegaran las subidas.
Un saludo.

El Farmacéutico Activo dijo...

La función para el cambio es =GOOGLEFINACE ("EUROUSD").
En una celda pondrías un 1, y en la de al lado llamada cambio por ejemplo, pondrías =GOOGLEFINACE (celda del 1; "EURODÓLAR"), el resultado de esa casilla lo tienes que dividir entra el valor de cada empresa usa (PM*cotizacion/cambio. Así tienes valor real de cartera cada 5 mina con cambio incluido.
Saludos.

El Farmacéutico Activo dijo...

EURODÓLAR NO perdón EUROUSD

Xoan Ferreira dijo...

Muchas gracias.

Unknown dijo...

Muy bueno, voy a probar ese método, muchas gracias por compartir. ;)

El Farmacéutico Activo dijo...

Buenas Jorge.
De nada, un saludo.

Unknown dijo...

Excelente aportación. Estoy actualizando mi excel básico y ya parece un poco "más profesional" jeje.
Aunque tienes razón y no es que sean los mejores tiempos para actualizarla pero...vendrán tiempos mejores, sin duda ;)
Una consulta, he intentado incluir unas columnas para ver la rentabilidad por dividendo y los últimos dividendos en efectivo pero...no hay manera, ¿me podrías ayudar con esto?
Mil gracias.

El Farmacéutico Activo dijo...

Buenas, el RDP yo lo pongue a mano, no encuentro forma directa con función de GOOGLEFINANCE.
Respecto al los últimos dividendos tampoco, estaría muy bien poder saberlo de forma automática para añadir al Radar.
No son los mejores momento no....
Saludos.

Anónimo dijo...

Muchas gracias.
Es que he visto que hay fórmulas de "incomedividend" que, en teoría, te indican el último dividendo pagado en efectivo, otra para la rentabilidad por dividendo pero al intentar incluir en el excel no dan ningún valor. Pensé que era yo que metía mal la fórmula....de ahi mi pregunta.
Muchas gracias.!!!!

Unknown dijo...

Eestoy a probarlo y si que es una pasada. Muchas gracias de verdad. Ahora tienes un seguidor fiel.

El Farmacéutico Activo dijo...

Buenas Jorge, la verdad es que es la caña, yo cuando lo "desubrí" pensé, como no lo había encontrado antes, tanto Excel metiendo valores cuando esto lo hace de forma automática¡¡¡¡
Voy a preparar una entrada sobre gráficos que también les hay muy chulos.
Saludos.

Marchamalo dijo...

Hay una formula para obtener el RDP de forma automática:

=index(split(index(substitute(importhtml(concatenate("http://www.google.com/finance?q=";C5);"table";3);".";",");0;2);"/";TRUE);0;2)

En este caso el Ticket (ejemplo BME:BME) lo tengo situado en la columna C.

Un matiz importante es que si metes muchas empresas y tu hoja Google Drive tiene muchas fórmulas este función devuelve un error. Supongo que será por tener alguna limitación.

A partir del RDP automático, puedes calcular el Dividendo en términos absolutos.

El Farmacéutico Activo dijo...

Muchas gracias por la aportación Marchamalo!!!!!!!

Héctor dijo...

Estupendo que des a conocer las posibilidades de esta herramienta. A mí me es muy útil para tener una hoja de seguimiento de las acciones que me interesan y poder saber cuáles están baratas, caras (botón derecho "conditional formatting" y puedes poner colores cuando se cumplan las condiciones que tú consideres: per, rpd, %respecto a tu precio de compra...).

Buscando por ahí encontré cómo calcular medias móviles:

De 50 sesiones: =average(query(sort(GoogleFinance(C6,"price", TODAY()-320, TODAY()),1,0),"select Col2 limit 50"))

De 1000 sesiones (200 semanas): =average(query(sort(GoogleFinance(C6,"price", TODAY()-1200, TODAY()),1,0),"select Col2 limit 1000"))

Y también lo uso para fondos:

=VALUE(SUBSTITUTE(MID(importXml("http://www.morningstar.es/es/snapshot/snapshot.aspx?id=F0GBR04PSV","(//td[@class='line text'])[1]"),5,8),",","."))

Un saludo

El Farmacéutico Activo dijo...

Buenas,
Héctor, muchas gracias por la aportación me lo apunto y lo añado a mi cartera.
Lo de los Fondos es un puntazo también.
Saludos.

Gus dijo...

Hola FA,

magnifica entrada. La verdad es que es algo que todos necesitamos para hacer un seguimiento más òptimo y menos tedioso.
Però no os da dudas sobre su seguridad de que cualquiera tenga acceso a la información de vuestra cartera?
Sabeis alguna web donde encontrar los dividendos pagados (por ejercicios o años) de la Ibex o del mercado continuo?

Muchas gracias!

Saludos

Unknown dijo...

Gus, la web de invertia esta bastante bien para consultar dividendos historicos de ibex y mercado continuo.
Para valores extranjeros echa un ojo a dividata.com, a ver que te parece
Saludos

El Farmacéutico Activo dijo...

Muchas Gracias por la aportación Diego.
Saludos.

Florence May dijo...

Hola!! Me han pasado tu enlace desde el foro de Gregorio pero soy muy torpe con esto. ¿Cual es la fórmula para que me promedie el precio de compra? Gracias por este pedazo de blog!!!

El Farmacéutico Activo dijo...

Buenas,
El precio medio lo calcularía como suma de importes invertidos con comisión dividido entre número de acciones totales.
Saludos

Florence May dijo...

Muchísimas gracias!!! Ya lo he conseguido gracias a tu consejo!! Y la fórmula para los porcentajes de plusvalía/minusvalía? Consigo sacar la diferencia en porcentaje, pero no que me salga positivo o negativo

El Farmacéutico Activo dijo...

Buenas, de nada, debes de poner:
100-(casilla plusvalía/munusvalias*100/importe invertido).
Saludos.

Florence May dijo...

No lo consigo amigo!! Perdona por el follón que te estoy dando!!
Si los datos son:

Minusvalía en euros: -40 (casilla D2 por ejemplo)
Importe Invertido: +200 (casilla C2 por ejemplo)

sería:
100-(D2*100/C2)

Esto sería:
100-(-40*100/200)
Pues no me sale!!! ¿Qué hago mal?

El Farmacéutico Activo dijo...

Buenas, con las prisas te lo he dicho mal:
Sería: ((valor*100)/inversion)-100
En tu ejemplo: ((D2*100)/C2)-100
Muy importante los paréntesis.
Saludos.

Juan dijo...

Muy instructiva la entrada. Unas preguntas,el payout lo introduces a mano?, que criterio tienes para la compra fuerte?. Un saludo.

El Farmacéutico Activo dijo...

Buenas, para Payout utilizó la fórmula: dividendo estimado/ GOOGLEFINANCE (celda ticket; "eps").
El dividiendo estimado a mano.
Respecto a compra fuerte utilizo un poco de todo, soportes, RDP... Quiero actualizarlo un poco para adaptarlo a los tiempos actuales......

Juan dijo...

Gracias.

Unknown dijo...
Este comentario ha sido eliminado por el autor.
Unknown dijo...

Hola, me da error al usar las fornukas de la media movil. Aparte de sustituir la celda C6 del ejemplo por la que tenga el Ticker de nuestra hoja, hay que hacer algo mas?

Muchas gracias

Paulshirley dijo...
Este comentario ha sido eliminado por el autor.