Excel VBA y macros: Una herramienta muy útil para trabajar hojas de cálculo (1).

Hola, una disculpa,¿el código VBA se puede utilizar en otros ámbitos de desarrollo ?

Este libro que mencionas lo puedes descargar en PDF de este link: http://search.4shared.com/q/1/%22Financial%20Modeling%22%20Simon%20Benninga?view=searchMainField y es la segunda edición.

Saludos.

Con todos los respetos si la parte pesada son los datos, ni excel ni ascess, mejor SQL, claro q la cosa se va complicando, lo digo porque bueno ascess no está mal pero como RDBM no me gusta, prefiero SQL es mejor

S2

Hola Comstar, disculpa no poder contestar antes. Este sería el proceso en una posición corta:
1. Los datos estan distribuidos en 7 columnas: fecha, hora, apertura, cierre, MAX, MIN, volumen.
2. Tomamos la fila anterior del MIN para abrir posición.
3. STOP= Apertura del día
4. Beneficio = (MIN del día anterior - apertura del día) * multiplicador
5. Entrada la posición corta salta el STOP, abrimos posición larga en la apertura, es decir, cuándo salta el STOP automáticamente abrimos posición en sentido contrario.
6. Nuevo STOP: MIN del día anterior.
7. Nuevo beneficio = (Apertura - MIN del día anterior) * MULTIPLICADOR.

Posición larga:
1. Los datos estan distribuidos en 7 columnas: fecha, hora, apertura, cierre, MAX, MIN, volumen.
2. Tomamos la fila del día anterior del MAX para abrir posición.
3. STOP= Apertura del día
4. Beneficio = (MAX del día anterior - apertura del día) * multiplicador
5. Entrada la posición larga salta el STOP, abrimos posición corta en la apertura del día, es decir, cuándo salta el STOP, automáticamente abrimos posición en sentido contrario.
6. Nuevo STOP: MAX del día anterior.
7. Nuevo beneficio = (MAX del día anterior - apertura del día) * MULTIPLICADOR.

Este sería el proceso. Realizado en una tabla excel con los datos diarios es sencillo de ejecutar. Sin embargo, llevar un análisis exhaustivo requiere realizar una prueba historica con datos de 10 segundos o 1 minuto. Probar esta estrategia en el mercado forex que esta abierto 24 horas la operativa de la estrategia nos puede saltar el STOP y abrir operaciones de nuevo 4, 5, 6... veces, esto supone saltar el STOP tantas veces cómop pase. Por tanto, si realizamos una prueba con los datos de cierre del día los resultados no serán fiables. El problema que me encuentro en la tabla excel es que la hoja sea capaz de verme con los datos de 10 segundos o 1 minuto si esta pasando por las entradas, STOP y sea capaz de calcular la cantidad de veces que ha saltado en el día el STOP,es decir, una memoria. Por eso, entiendo que con una programación sería mucho más fácil y fiable.
Cómo lo ves.
Gracias.

Enrique

Cuando aclare las dudas sobre la estrategia, postearé aquí el código VB, y en el siguiente post usaré ese ejemplo donde explicaré paso a paso como se construyó, la lógica seguida y los conceptos de VB asociados.

Un artículo sobre el tema: Using Access or Excel to manage your data

Realmente creo que si va a manejar 20 mil registros en una sola tabla, con Excel es suficiente.

Para clavar clavos, un martillo. Para extraerlos, unas tenazas. Elige la herramienta correcta y tendrás éxito.

Cuando la parte pesada son las formulas, lo adecuado es Excel, pero si la parte pesada son los datos, lo adecuado es Access, al que tambien puedes atacar con VBS.

Si ademas, el proceso es incremental, cada dia solo procesarias los datos del dia, no todos. Ademas, se lo puedes poner muy dificil a un ordenador si te empeñas en una hoja de calculo con cientos de megas, lo puedes poner a paginar a disco como un loco.

Lo veo fácil, pero no creo estar captando los pasos por completo.
Dejame ver si me queda claro. Esto es lo que entiendo.

Tenemos un modo de operar que puede ser corto o largo. Iniciamos con alguno de los dos.

1.Ordenamos los datos por fecha y hora (supongo que los datos de cada fecha y hora están en cada fila horizontal).
2.Buscamos la última fila del día anterior. Tomas el mínimo (corto) o máximo (largo) de allí(Digamos que esa cantidad se llama M).
3.En la siguiente fila de apertura, se toma precio de apertura (digamos que se llama A).
4.Si modo de operar está en corto y A menor ó igual a M, o si modo de operar está en largo y A mayor ó igual a M, entonces:
STOP = ¿cual valor? extraido de la fila de apertura
Beneficio = (STOP - A) * multiplicador
5.Si modo en corto y A menor a M entonces: pasar a modo largo
6.Si modo en largo y A mayor a M, entonces: pasar a modo corto
7.Mostrar el valor del STOP, Beneficio y el modo de operar.

Si entiendo bien, esto se hace una vez al día, de modo que no ocuparías un proceso recurrente para hacer cambios de posiciones. ¿Cierto? ¿O hay que hacer análisis que acumulen varios días?

Cuando se hace un programa, los pasos se ordenan así como lo he mostrado, usando pasos que una persona normal podría seguir para hacer las cosas manualmente. La computadora lo que hace es automatizar esos pasos simples.

Indícame si debo hacer algún cambio en los pasos descritos y necesitaría que me indiques qué valor se usa para el STOP. Mi fortaleza es analizar la economía, pues yo fui empresario y analizaba en función de decisiones de gestión y no como inversionista en bolsa, y por ende no manejo tanto los temas específicos de cálculos de bolsa.

Quiero decir el precipicio, claro esta.

Hola Comstar, intentare "correr" porque donde yo vivo es apropiado llevar este ritmo. Siempre viendo el precipio de lejos, claro esta.
Cómo veo la facilidad que tienes con la programación te pedire si me puedes ayudar en el análisis que llevo con una estrategia muy sencilla. Llevo alrededor de 1 mes y medio desarrollando un análisis en excel y no soy capaz de terminarla. Te explico la estrategia:
Bien, cojo el último o mínimo o máximo del cierre del día anterior eligiendo si voy en posicionas cortas o largas, si elijo posición corta entrará mi posición cuándo toque el mínimo anterior, el STOP estará en la apertura y el beneficio será un factor multiplicador del diferencial entre el STOP y la apertura. Es decir si la diferencia entre el stop y la apertura son 50 pips, la recogida de beneficios será 50 pips * el multiplicador. Ahora bien si nos ha entrado la operación y se nos da la vuelta saltandonos el STOP abriremos una posición en sentido contrario (larga) con la misma operación de stop y take proffit.
La inversa sería en posicionas largas.
¿Cómo lo ves?
Gracias.
Un saludo,
Enrique

Me han explicado que "correr" es una mala palabra en España. La palabra correcta es "ejecutar". Es que donde yo vivo la palabra "run" se traduce como "correr". ¿Es inapropiado usar esa palabra?

** Nota: este post no pretende ser el siguiente paso del tutorial, porque antes de que sea entendible por un novato, tengo que explicar algunos conceptos, que trataré en forma fácil y entendible en futuros posts, pero no aquí.

Eplanas:
Dejame ver, los datos de entrada que me indicas son una colección de registros donde cada registro contiene:

1.Fecha y hora (En VB se pueden manejar como un solo dato)
3.Precio de apertura
4.Precio de cierre
5.Precio máximo
6.Precio mínimo

Podemos decir que lo que tienes es una "estructura" llamada registro, compuesta por una colección de datos. Cabe notar que no me estás indicando cómo procesar los datos o cómo mostrar la salida. Si lo explicaras podría hacer el código más detallado.

En este caso no pretendo que entiendas el código todavía, si fueses u principiante, sino que voy a presentar el código para que tengas una idea general de cómo se vería. Cabe agregar que ese código igual sirve para procesar 100 registros que para procesar 20 mil. El único problema sería el tiempo de corrida, para lo cual sería bueno ponerle algún despliegue al usuario para avisarle el estado de avance del proceso.

De una forma genérica el código debería verse como se muestra a continuación.

-----------------------------------
'Declaramos una constante que indicará la cantidad de líneas o registros a procesar
Const CantidadDeLineas = 100

'Declaramos una estructura llamada TipoRegistro que contendrá los datos de un registro
Private Type TipoRegistro
Fecha As Date
Apertura As Double
Cierre As Double
Maximo As Double
Minimo As Double
End Type

'Declaramos una variable llamada registro que contendrá una cantidad de elementos dada por CantidadDeLineas, y cada elemento contendrá un registro.
Dim Registro(CantidadDeLineas) As TipoRegistro

Sub Main()
'Pasar los datos de celdas de Excel en formato Cells(fila,columna) a la estructura Registro(i) en memoria
'i es una variable indice que varía desde un valor 1 hasta CantidadDeLineas
For i = 1 To CantidadDeLineas
'Nótese que i describe el número de lemento de la estructura, así como la fila de la que se lee.
Registro(i).Fecha = Cells(i, 1)
'También puede notarse que cada dato de la estructura corresponde a una columna diferente en la hoja de cálculo.
Registro(i).Apertura = Cells(i, 2)
Registro(i).Cierre = Cells(i, 3)
Registro(i).Maximo = Cells(i, 4)
Registro(i).Minimo = Cells(i, 5)
Next i

'Procesamiento de los datos
For i = 1 To CantidadDeLineas
If condicion Then
'Pasos a realizar si se cumple la condicion
Else
'Pasos a realizar si no se cumple la condición
End If
Next i

'Pasos a seguir para mostrar los resultados

End Sub


Hola Comstar, las hojas de cálculo las utilizo para realizar pruebas históricas de un instrumento. Por ejemplo, ahora estoy intentando realizar la prueba con el indice SP 500 de Fecara. Pongo el link: https://www.rankia.com/blog/ferran/544936-mis-pequenos-tesoros-iv-quinto-elemento
Mi metodo de trabajo en estos casos es descargarme los históricos de fecha, hora, apertura, cierre, max y min. por tick o 10 segundos. En este caso no conozco ninguna página para descargarme los datos de este indice si conoces alguna podrías darmela?. Tras descargarme los datos comienzo a traves de formulas de excel, sobre todo condicionantes, si(...) o(....) hasta conseguir realizar el análisis. Cómo puedes imaginarte si realizo una prueba de 1 año en una hoja, puede haber 20.000 filas cada una con sus formulas, esto supone tener libros con 30 o 40 Megas, una barbaridad.
¿cómo comenzarías a realizarlo con macros?
Muchas gracias.
Un saludo,

Cuando tenga tiempo, en los siguientes posts trataré de elaborar pequeños programas.
Si tienes problemas específicos, podemos analizar cómo resolverlos aquí.
Toma el más sencillo de todos, porque quiero ir de lo más simple a lo más dificil.
Yo podría hacer ejemplos meramente didácticos, pero también quisiera mostrar ejemplos reales.

Hola, estoy interesado en seguir tus siguientes artículos. Cómo usuario de excel no he trabajado con macros, siempre he creado libros con cientos de formulas repetitivas en la hoja que llegabán a ocuparme hasta 60 Megas, es decir, un disparate. Por tanto, animo con esta etiqueta porque espero aprender con tus articulos.
Gracias.

Gracias, este tema lo tengo controlado, (soy del gremio software), sólo lo comentaba con una "molesta" a nivel usuario que se da según el idioma del Excel. He tenido que trabajar con usuarios / idiomas de distintos países y cada vez, para lo básico, parezco un aprendiz por el tema de los nombres de las funciones. Obviamente VBA es más estandarizado.

Saludos

La solución al problema de la inconsistencia de funciones por idioma, es crear un módulo de clase por cada idioma, que automatice las funciones de Excel en ese idioma, de modo que todo el código que escribas para Excel esté encapsulado en un objeto, y lo único que harás será escribir código en Visual Basic que es estándar, y hacer un llamado a una propiedad o método de un objeto. Entonces para seleccionar el objeto adecuado lo que cambias es tan sólo una línea de código, donde se crea el objeto, para que use el módulo de clase apropiado. Si no sabes cómo crear objetos en Excel, me avisas y te enseño como efectuar programación orientada a objetos.

Y para aquellos que no saben VBA no se preocupen por entender lo que acabo de decir porque lo dicho es programación de Visual Basic avanzada. A menos que tengas un proyecto muy largo y complicado, no vas a necesitar conceptos como los que he dicho. Así como para muchos novatos el concepto de inflación es muy avanzado, así lo que acabo de decir, dicho en cristiano no tiene nada de complejo.

Para mí el Excel es la aplicación estrella de Microsoft, y para la que peor le he encontrado reemplazo..., de hecho muchos usuarios "avanzados", cuando les intentas vender una aplicación de gestión, stocks, etc.., lo que te piden es que básicamente haga lo que hace excel, tablas dinámicas, pivots, etc.., aunque los datos vengan de un Oracle o DB2.., y tú difícilmente puedes dárselo, aunque el "comercial" lo venda.. :D.

Lo que menos me gusta de programar en Excel es el lío con el nombre de las funciones predefinidas según idioma (VAN, IRR, etc...)...

También comentar que hace un tiempo compré el libro "Financial Modeling" de Simon Benninga. Éste cuesta unas 60$ (Amazon UK), pero me lo compré de los "used" por unas 20, la segunda edición. Ahora hay una tercera. Comentar que para introducirse a nivel avanzado en Excel como herramienta de cálculo financiero es una joya, además de cálculos financieros (rendimientos, opciones, eficiencia de portfolio, etc...), al final lleva más de 120 páginas de tutorial de VBA..