Rankia España
Acceder
¿Nos visitas desde USA? Entra a tu página Rankia.us.
Blog Cambiando el mundo
Discusión de problemas y soluciones de actualidad económica mundial

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

¿Alguna vez has querido programar macros de Excel para evitar trabajo repetitivo y tedioso, donde ocupas que la computadora haga el trabajo por tí?  Aquí he de presentar los fundamentos para que empieces a dar tus primeros pasos en la programación de macros y VBA para Excel.

Tengo miedo a la programación

Muchas personas le tienen miedo a la computadora, porque creen que es un lobo feroz que se come a los usuarios.  Afortunadamente si estás leyendo esto, sabrás que no es así.  De igual forma, un programa es una secuencia de órdenes donde las reglas para escribir son una mezcla de idioma inglés y la forma en que escribías matemática en secundaria.

Programar se parece más a crear recetas de cocina, que sería como darle órdenes a un robot cocinero.  Si tomas una sartén y echas los huevos antes que el aceite de cocina, los huevos se pegarán a la sartén, y no obtienes el resultado deseado. Pero si inviertes el orden, el resultado es distinto.  Así funcionan los errores de programación, pues la máquina hará lo que lo ordenaste, y no necesariamente lo que quieres que haga.

¿Le temes a crear recetas de cocina?  Tampoco deberías temer a la programación.  Al principio te costará un poco acostumbrarte, porque a veces existen procedimientos que ya están inventados y que hacen algunas cosas, y a veces nos lleva trabajo ver la lógica detrás de esos procedimientos.  Pero aparte de eso, no deberías complicarte la existencia.

¿Que es VBA?

En los años 1950 se inventa un lenguaje de alto nivel llamado FORTRAN (IBM Mathematical Formula Translating System), orientado a ingeniería y ciencias, que permitía escribir código de una manera más entendible e intutiva para el ser humano que empezar a escribir en ensamblador que es un lenguaje penosamente arduo para la mayoría de las personas.  Se dice que es de alto nivel porque la forma de expresar las cosas es más entendible para el ser humano, y evidentemente bajo nivel lidia más con aspectos funcionales de la máquina a niveles mucho menos intuitivos.

A partir de este lenguaje se inventa en 1964 el BASIC (Beginner's All-purpose Symbolic Instruction Code) por parte de los húngaros John George Kemeny y Thomas Eugene Kurtz del Dartmouth College en New Hampshire, EUA para personas que no tuvieran profundos conocimientos de computadoras y ciencias. 

Microsoft crea su propia versión con el lenguaje GW-BASIC y un compilador llamado BASCOM para Compaq.  Un compilador sirve para traducir el código que escribe el usuario (código fuente) a lenguaje binario (lenguaje máquina) que es el que las computadoras entienden.  Como el GW-BASIC no compilaba el programa para generar un archivo ejecutable, lo que hacía era "interpretarlo", que equivale a decir que toma las instrucciones de una en una, las compila y luego las ejecuta, un proceso que hace la ejecución mucho más lenta.

Luego viene Visual Basic (VB) que es un lenguaje, tradicionalmente considerado como orientado a eventos, aunque también es capaz de manejar objetos de una forma algo básica, que se considera fácil de trabajar por el manejo de interfaces gráficas y también por lo entendible que resulta el BASIC del cual se deriva.  Fue introducido en 1991 (versión 1), y ha sido objeto de mejoras con el tiempo, pero la versión que se utiliza al programar en macros de Microsoft Office pertenece a la versión 6 de 1998.  Posteriormente se introduce el Visual Basic en su versión .NET que tiene importantes diferencias respecto de versiones anteriores, de modo que el código escrito en versión 5 podría no funcionar en .NET

VBA (Visual Basic for Applications) es una versión recortada de Visual Basic 6, que fue extendida con comandos de macro y objetos específicos de Microsoft Office.  A diferencia del Visual Basic, no puede generar archivos ejecutables, sino que el código se debe echar a andar al estar dentro de una de las aplicaciones de Microsoft Office.

¿Cómo crear una macro?

Una macro es parecida un programa, pero se crea al grabar acciones del usuario que se representan como código.  Dichas macros por sí solas solo podrían realizar secuencias fijas que empulan al pie de la letra las acciones realizadas por un usuario.  Afortunadamente las macros de Officie incorporan la facilidad de usar también elementos de Visual Basic que permiten realizar procedimientos más complejos.

De esta manera puedes grabar algunas acciones (macros) y complementar el resto con instrucciones (VB) que agregas a tu gusto para cambiar todo para que se lleven a cabo los procesos que deseas realizar.

Si deseas crear tus primeras macros, aquí tienes un tutorial: Anthony's VBA page

El tutorial está hecho para Excel 2003.  Excel 2007 acceder a las funciones de grabado y edicion de macros es ligeramente distinto.  Lo demás es prácticamente igual.  Las incompatibilidades entre 2003 y 2007 no las notarás a menos que sea un programador "hardcore" que usa algunas características exóticas de Excel.  La mayoría de estos problemas suelen estar fuera de lo que un usuario normal que programa macros notaría.

  Excel 2003 Excel 2007
Grabar macro Tools > Macro > Record New Macro Vista > Macros > Grabar Macro
Editar macro Tools > Macro > Visual Basic Editor ALT F11

 

Si tienes preguntas sobre el tutorial, puedes aclararlas presentando tus preguntas aquí.

  1. #1

    Alejperez

    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..

  2. #2

    Comstar

    en respuesta a Alejperez
    Ver mensaje de Alejperez

    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.

  3. #3

    Alejperez

    en respuesta a Comstar
    Ver mensaje de Comstar

    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

  4. #4

    Eplanas

    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.

  5. #5

    Comstar

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    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.

  6. #6

    Eplanas

    en respuesta a Comstar
    Ver mensaje de Comstar

    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,

  7. #7

    Comstar

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    ** 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

  8. #8

    Comstar

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    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?

  9. #9

    Eplanas

    en respuesta a Comstar
    Ver mensaje de Comstar

    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

  10. #11

    Comstar

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    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.

  11. #12

    Feinmann

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    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.

  12. #14

    Comstar

    en respuesta a Eplanas
    Ver mensaje de Eplanas

    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.

  13. #15

    Eplanas

    en respuesta a Comstar
    Ver mensaje de Comstar

    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

  14. #16

    X4v13r

    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

Autor del blog


Este sitio web usa cookies para analizar la navegación del usuario. Política de cookies.
Cerrar