Trabajando con Excel

En el siguiente enlace podéis descargar un fichero Excel que se actualiza con los cierres diarios que ofrece Yahoo. Lo he ido haciendo con Excel 2010 y 2013, pero entiendo que debería de funcionar sin problemas con la versión 2016 y 365.

http://falkys.dyndns.biz/download/MC_IBEX_v2.0.zip

Prácticamente recoge todo el mercado continuo, así que una vez abierto necesita un ratillo para actualizarse…
Dentro del libro Excel hay una hoja por cada CSV y luego una hoja (RESUMEN_MC) que es la que utilizo para realizar los cálculos y resumir la información de cada valor. También hay una hoja que agrupa los valores del Ibex (RESUMEN_IBEX) pero aquí no se realiza ningún calculo, únicamente es una copia de RESUMEN_MC para los valores del Ibex-35.

Resumo brevemente las columnas:
-Nombre, Fecha, Cierre y Volumen negociado: lo de siempre..

-Volumen (% Var. s/media): es la variación del último volumen respecto a la media del último año (256 sesiones).

-Volumen (Valoración): En función de lo anterior, muestra si el volumen ha sido bajo, normal, alto…

-%Variación: las variaciones en distintos periodos.

-Resumen tendencias: indica si la tendencia es alcista o bajista, y si la tendencia mejora o empeora.

-Resistencia y Soporte: se calculan a partir de la “distancia” (desviación típica) sobre la “línea de regresión” (estimación lineal) en función del periodo.

-Proyección Lineal: mediante la función PRONÓSTICO se calcula la prolongación de la línea 1 día, 3 meses y 1 año. De momento es una prueba, el número de sesiones con las que se realiza la estimación lineal no tiene mucho rigor, tengo por ahí otro Excel con un backtest para estimar el número de periodos más adecuado… más adelante si queréis lo explico a fondo.

-Previsión: si el cierre está por debajo o encima de resistencia o soporte, y en función de la tendencia, determina si un valor es alcista o bajista. Por este motivo solo aparece la indicación si se cumplen ambas condiciones. Tengo otros Excels con métodos más sofisticados (que no más fiables), y más adelante iré incorporando nuevas columnas para mostrar las distintas formas de previsión.

También hay unas cuantas columnas ocultas de medias, mínimos, máximos.. y otras columnas auxiliares que he ocultado para intentar aportar algo de claridad.

Por descontado decir que me he podido equivocar con las formulas, y además los datos de Yahoo no son la panacea (volúmenes incorrectos, algún que otro día que se comen y etc.)

Aun me quedan muchísimas cosas por incorporar de otras Excels que he ido haciendo de forma paralela. Si os interesa también colgare otros Excels (gráficos, cálculo de gaps, estadísticas estacionales, numerosas estrategias, análisis de otros índices y etc).

Solo espero que le pueda resultar útil a alguien, y dentro de mis limitaciones estoy abierto a aclarar cualquier duda.

Os agradecería cualquier sugerencia o idea que me permita seguir aprendiendo de vosotros.

P.D.: la contraseña para descomprimir el fichero.zip es: rankia@2016

Hay mucha documentación acerca de obtener datos de la web en Excel, no obstante voy a detallar los pasos por si alguien quiere hacerlo. Yo prefiero enlazar el fichero en formato CSV antes que andar eligiendo tablas desde el navegador de Excel ("Desde Web..")

Una vez se le pilla la mecánica se tarda menos de 1 minuto en hacerlo:

1. En la cinta "DATOS", nos dirigimos al apartado "Obtener datos externos" y pinchamos sobre "Desde texto"

2. En "Nombre de archivo" pegamos la URL en la cual se encuentra el fichero en formato CSV.

Por ej. para descargar el historico del SP500 pondriamos esto:
http://ichart.yahoo.com/table.csv?s=^GSPC&a=0&b=2&c=1900 &d=7&e=6&f=2050&g=d&ignore=.csv
Si deseamos el historico de otro valores solo hay que cambiar ^GSPC por lo que queramos descargar (^IBEX, BBVA.MC, ITX.MC o lo que sea)

3. Dejamos seleccionado "Delimitados" y marcamos el check "Mis datos tienen encabezado"

4. Marcamos el check "Coma" (y desmarcamos el del "Tabulacion")

5. El primer campo como fecha, resto campos dejar tal cual (general). En esa misma ventana vamos a "Avanzadas" y en el "Separador decimal" elegimos el punto (.) y el "Separador de miles" lo dejamos en blanco (tercera opcion del desplegable)

6. Pinchamos en "Finalizar y la ultima ventana entramos en "Propiedades" y desmarcamos "Solicitar nombre del archivo al actualizar" y marcamos "Actualizar al abrir archivo" (si queremos que se actualice nada mas abrir el fichero Excel)

Con esto ya tenemos todo el histórico de una tacada.

Has creado un monstruo... jajajaja

Enhorabuena, un gran trabajo

buen trabajo

aunque es un poco grande y genera mucho gasto de CPU y memoria (abstenerse correrlo en equipos y sistemas operativos antiguos o poco potentes)

Quizás sería apropiado (es una idea) dividirlo en varios archivos, para suplir ese problema.

En cualquier caso repito, muy buen trabajo.

:)

menuda currada!

por cierto, cuando la resistencia o soporte estan en gris, k significa?

Gracias por la sugerencia, es cierto que con tantas hojas hace falta buena maquina para moverlo con soltura..
Es muy sencillo de hacer lo que dices, tan solo eliminar las hojas que no interesen y eliminar también las filas en las hojas de resumen.
Puedo preparar una exclusiva para el Ibex, que con 35 hojas se mueve muy bien.

Tomo nota ya que estoy preparando otro Excel que este si que se me esta yendo de las manos.. Lleva las 100 hojas de este fichero mas otra hoja auxiliar por cada valor.. 95 megas de Excel que literalmente te fríen el PC. Y aun me faltan de incluir los gráficos.. Definitivamente voy a separarlo en distintos ficheros mas manejables.

No es para tanto Kadok..
Las resistencias y soportes se sombrean cuando el cierre se encuentra a un 1%. Es mediante formato condicional.

¡¡¡Impresionante, muchísimas gracias por el curro!!!

A mí se me muere el ordenador, como dice el compañero yotrader. Se me ocurre borrar los valores en los que no esté interesado para hacerlo más ligero...quizás mejore la cosa, no sé.

Una pega que le veo es que, como los datos de Yahoo no "reconocen" los splits, el Excel falsea la trayectoria de una empresa. Por ejemplo, a MCM la pone en rojo cuando su trayectoria es claramente alcista.

Te animo a que cuelgues cuantas hojas de Excel quieras/puedas, me encantan estas historias. :)

GRACIAS

Si, eliminando las hojas que no te interesen y también quitando las filas del resumen, debería agilizarse.

En unos minutos voy a colgar otro Excel, este es mas viejito, es sobre los principales indices y es la hoja que utilizaba para publicar el contenido de mi abandonada web:

http://falkys.dyndns.biz/

Perdón, no había visto tu contestación a yotrader cuando he enviado mi mensaje. Probaré a "personalizar mi Excel" cuando tenga un rato, a ver qué tal chuta el ordenador entonces. :)

¡Muchas gracias de nuevo!

Un saludo

Aquí dejo el otro Excel que te comentaba de los principales índices:

http://falkys.dyndns.biz/download/INDICES_TOP15_v1.6.zip

Falta el índice ruso (RTS) ya que yahoo dejó misteriosamente de darle soporte. Después lo cambie a Qquandl pero también ha dejado de actualizarlo..

Por lo demás es similar al otro. Las tendencias las calculo de otra manera, utilizando una especie de ROC sobre medias, no quiero enrollarme, si a alguien le interesa, lo explico.
La previsión se calcula también de otra manera, pero sigue siendo la misma basura que en el primero que he colgado.

Son solo 14 hojas y otras 14 auxiliares, pero con los graficos y demás puede que se haga bastante pesada..

Muchas gracias!

Iba a pedírtelo en un privado animado por tu comentario en Pulso de Mercado, pero veo que ya lo has compartido. Me lo bajo y el fin de semana me animaré a verlo

Gracias de nuevo!

Tu comentario me motivó a hacerlo :D
De todas formas te recomiendo el del primer enlace de este hilo.

Acabo de colgar otro Excel, este es exclusivamente de gráficos del Ibex-35.
Si alguien quiere el grafico de otro valor, solamente hay que modificar la primera hoja (CSV)e incluir ahí el csv que nos interese.

El resto de hojas son todas auxiliares, únicamente interesan las 2 ultimas: graficos y prev.

En la hoja GRAFICOS hay diferentes periodos, semanal, bisemanal, mensual... 3 meses... varios años e histórico total.

La línea central es la línea de regresión, y los canales superior e inferior son una paralela de esta a la distancia de la desviación típica de este periodo.

En la hoja PREV hay un engendro de experimento, es un grafico de 200 sesiones, similar a los anteriores, además de los 2 canales habituales, hay otros dos también paralelos a la desviación máxima. Y el ultimo tramo son 3 proyecciones de 12 sesiones pero basadas en la estimacion lineal y desviación de los últimos 6 meses (128 sesiones).

Una aclaración, en este Excel la desviación típica se calcula sobre los valores de cierre, pero en los Excels mas actuales lo hago sobre minimos y máximos diarios, ya que creo que es mas preciso.

http://falkys.dyndns.biz/download/IBEX35_GRAFICOS_v1.8.zip