18 May

Macro para transferir archivos a un sitio FTP

Recientemente tuve un caso donde luego de realizar unos cálulos automatizados en Excel, el resultado debía transferirse a un sitio FTP.  El desafío entonces es si es posible crear una manera automatizada de transferir estos archivos al sitio remoto.

Encontré varios recursos en línea y gracias a ellos desarrollé la siguiente solución

En síntesis es una macro que crea un archivo de texto con las instrucciones en sintaxis FTP, luego crea un archivo Batch que transfiere instrucciones MS-DOS siguiendo cada una de las  líneas del archivo de texto. Al finalizar, crea un archivo con extensión .out donde se confirma la transferencia.

Nótese que es necesario cambiar el código para agregar el usuario y password correcto. En ocasiones el sitio FTP no requiere password pues la conexión es anónima. Si este es el caso, simplemente remueva esas dos líneas del código.

El siguiente video muestra el programa en ejecución.

13 Aug

Macro para obtener historico de precios de una accion en Excel

[ File # csp9666250, License # 2222627 ] Licensed through http://www.canstockphoto.com in accordance with the End User License Agreement (http://www.canstockphoto.com/legal.php) (c) Can Stock Photo Inc. / bbbar

[ File # csp9666250, License # 2222627 ]
Licensed through http://www.canstockphoto.com in accordance with the End User License Agreement (http://www.canstockphoto.com/legal.php)
(c) Can Stock Photo Inc. / bbbar

Este programa permite descargar cualquier serie de precios disponible Yahoo Finanzas directamente a Excel con solo conocer el símbolo de la acción. Antes de comenzar es importante adicionar la referencia Microsoft XML en el módulo VBA accediendo con el método abreviado Alt+F11, luego haciendo click en Tools + Reference y buscar la referencia (ver imagen) la cuál permite importar y manipular archivos en formato XML.

ReferencesYahooFinances

La macro central de este programa se compone de varios elementos. El primero es la subrutina que genera la consulta llamada GetYahooFinanceTable (ver código a continuación).

La macro obtiene la información utilizando APIs de Yahoo Finanzas (lea aquí el wiki de los APIs de Yahoo finanzas). Un API que traducido al español significa Interfaz de programación de aplicaciones, es un conjunto de subrutinas predefinidas que puede ser utilizado por otro software de manera abreviada (Wikipedia API) en este caso para descargar datos de Yahoo (lea aquí un blog sobre como utilizar el API de Yahoo finanzas para archivos CSV).

Para utilizar el API simplemente construimos el query con los parámetros deseados, en este caso solamente necesitamos el código de la acción almacenado en la celda J2 (2, 10). Utilizemos como ejemplo la acción de 3M (MMM). El comando requerido por el API será http://ichart.finance.yahoo.com/table.txt?s=mmm. Si usted introduce este código en su explorador podrá descargar un archivo de texto con el histórico de precios.

GetYahooFinanceTable ejecuta a su vez la función GetHTTPResult y la subrutina UpdatePriceData. La función permite crear la consulta (query) en formato XMLHTTP y lanzarla a la web.

La subrutina UpdatePriceData prepara y llena los vectores con el resultado de la consulta. Para ello llama a la función GetQuoteXmlFromWeb y la subrutina GetQuoteFromXml.

La función GetQuoteXmlFromWeb es la que descarga la información en archivo XML y lo traduce. Un archivo XML es una estructura anidada de datos (padre e hijo) lo que permite manipular múltiples dimensiones y gran cantidad de información. Desafortunadamente no es tan simple descifrar la estructura por lo que utilizamos una función para descifrar la relación jerárquica de datos (ver función FindChildNodeName al final).

 

Finalmente, la función GetQuoteFromXml es una adición que bien puede ser opcional. Su utilidad es lanzar una nueva consulta que permite conocer los datos básicos de la acción, como el nombre, el último volumen transado, el mayor y menor valor de transacción del día, el último valor de transacción y la última hora de actualización. Si bien una buena parte de la información se encuentra en la serie de precios, esta función es útil cuando se tiene el símbolo y no se conoce el nombre exacto, o cuando se lanza la consulta y los mercados están aún abiertos por tanto no se conoce el precio de cierre de la acción. La siguiente es la función:

Lo demás es estético y a gusto del usuario, una vez la serie se carga en la hoja de cálculo se pueden crear gráficos, estadísticas y demás. Usted puede descargar una versión comprimida de esta macro con algunas adiciones en el siguiente link. Una vez funcional la macro usted tendrá un reporte como el presentado a continuación.

quote

 

Comentarios finales:

06 Oct

Excel Sumo: Otro concurso para expertos en Excel

Hay un sitio web llamado Excel Sumo que organiza el concurso mundial de hojas de cálculo.
El video a continuación describe el objetivo del concurso y sus reglas.
Básicamente los aspirantes deben inscribirse en el sitio y presentar su solución que puede estar en una de las cuatro categorías: Diversión, Productividad, Estilo de vida y Técnico (ver imagen)

draw[1]

La solución debe estar sustentada en una presentación de 90 segundos donde se explique cuál es la problemática (el porqué), la solución y cómo funciona.

El comité seleccionará las 16 mejores soluciones y las enfrentará en un primer round que tendrá lugar el 10 de noviembre de 2014. Entre estos 16 aspirantes se elegirán las 8 mejores y finalmente los 4 ganadores. El plazo máximo para aplicar es el 3 de noviembre.

El premio mayor es un Microsoft surface Pro 3, el segundo premio es un Samsung phone y el tercero y cuarto reciben una formación completa y gratuita en power pivot (Chandoo.org)

El siguiente video explica el concurso:

 

13 Dec

Macro Excel para desproteger una hoja de cálculo

Les ha pasado alguna vez que reciben una hoja de excel protegida y no saben como modificarla? Existe una manera de desbloquearla con una macro sencilla que se basea en el método de fuerza bruta. Solo necesita ingresar al editor de visual basic (Alt + F11) e insertar el siguiente código:

Finalmente debe llamar (ejecutar) la macro Unprotect desde la hoja que quiere desproteger y listo.

Atención: Este método es efectivo, sin embargo tiene un inconveniente y es que si bien permite desbloquear la hoja, no permite conocer el código para luego bloquearla en su estado original. Vale la pena saber esto antes de ejecutarlo.

%d bloggers like this: