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.

02 Sep

Macro para crear un diagrama Gantt en Excel y exportar a html y/o MS Project

En ocasiones debemos crear diagramas Gantt para el control de proyectos y resulta mas sencillo manipular los datos desde Excel que desde Project. En otras ocasiones, simplemente no se dispone de una licencia de Project para el contro del projecto.

Este post es sobre una herramienta en Excel que he diseñado en la que se puede crear un proyecto en blanco y a partir de la cual se pueden exportar los datos bien sea hacia un servidor para visualizar el proyecto en formato Gantt, o simplemente hacia MS Project.

 

gantt1

Nota técnica: Para que la solución funcione correctamente, es necesario tener instalado MS Excel y Project (opcional) 2010, asi como un servidor local, yo particularmente utilizo XAMPP pero está en cada usuario como desea aplicarlo. Finalmente, es necesario tener JSGantt que es un desarrollo en javascript que permite crear diagramas Gantt en web.

Los archivos pueden ser descargados en el siguiente vinculo.

Read More

21 Aug

Modeloff 2015 - Campeonato mundial de Excel

ModelOff-201511

Este año se celebra la cuarta versión del campeonato mundial de modelación financiera en Excel Modeloff. El campeonato cuenta cada vez con más adeptos, solo en su versión del año 2014 participaron cerca de 4,000 concursantes provenientes de 110 países vinculados a las disciplinas de finanzas, banca, contabilidad, seguros, actuaría, ingeniería y consultoría.

Al igual que el año anterior (ver post) el concurso se realizará en tres fases, las dos primeras en línea (sábado octubre 17 y sábado octubre 31) cada una con duración máxima de 2 horas. Los 16 mejores resultados llegarán a la final que se realizará en la ciudad de Londres (UK) los días 3 y 4 de diciembre de este año.

El siguiente video hace parte de la campaña promocional del concurso. Si usted es un apasionado por las finanzas aplicadas en Excel, este es concurso es recomendado.

 

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:

25 Feb

Macro para incrustar archivos PDF en Excel

Para incrustar un archivo PDF en Excel es necesario tener instalado un visor PDF cualquiera en su computador, en este caso utilizamos como referencia Acrobat Reader pues es tal vez el más popular y agregar a la lista de referencias el controlador Adobe Acrobat Browser Control Type Library 1.0 (ver imagen)

pdfForm

A continuacion es necesario crear un formulario suficientemente grande para contener una página de lectura del archivo PDF. Llamaremos a este formulario PdfForm

Luego creamos una función para llamar el formulario con el archivo PDF ya cargado. Llamaremos a esta función LoadPDF. El código se encuentra a continuación:

Lo demás es sencillo, requiere crear una macro que llame la función LoadPDF con el parámetro  correcto (ruta del archivo a abrir).

Un ejemplo de como se incorporan estas funciones se puede descargar en el siguiente link.

21 Oct

Macro Excel para reproducir un video

De esas cosas curiosas que uno se encuentra en la web. Un analista en una empresa se sentía muy monitoreado y restringido en su trabajo pues no tenía acceso a las aplicaciones comunes de su computador como el Windows media player y las aplicaciones de internet. Incluso su rendimiento era monitoreado según la utilización de recursos de su computador, principalmente Excel. Así que decidió hacer algo bastante inusual, creó una macro que permite insertar videos en Excel y reproducir de forma continua una lista determinada.

VMqYo5b

 

El link para descargar el archivo y las instrucciones se encuentra aquí

Como dato extra, la aplicación es además util para casos de monitoreo pues toda la utilización de recursos del computador queda concentrada en Excel.

13 Oct

Cómo hacer un gráfico dinámico en Excel

Un caso muy frecuente cuando se trabajan series en hojas de cálculo es la necesidad de crear gráficos y operaciones sobre toda la serie, que pueden ser dinámicos. Uno puede siempre que abra el libro editar la gráfica para arrastrar la fuente a la totalidad, sin embargo, existe un método más ingenioso para hacer que el gráfico y las fórmulas se actualicen automáticamente.

Supongamos que usted tiene una serie de precios mensuales como se muestra en la siguiente imagen

img1

Read More

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:

 

06 Oct

Tabla de equivalencias de funciones en Excel

En este blog hemos destinado varias entradas a trucos y herramientas elaboradas en Excel. La version de Excel que utilizo es Excel 2010 Profesional de 32Bits en Inglés.

Normalmente, si usted abre un archivo de Excel con fórmulas en otro idioma al de su computador, Excel hará la conversión.

Si usted requiere convertir una fórmula particular, usted puede buscar la función en la siguiente página (link).

Usted también puede utilizar un traductor de fórmulas, como el creado por Excel-translator.

09 Sep

ModelOff 2014 | The financial modeling world champioship

modeloff

Si a usted le apasionan las finanzas y la modelación en Excel, este concurso es para usted, se trata del campeonato mundial de modelación financiera ModelOff 2014. Este año es su tercera versión, la primera ronda será el 25 de Octubre y la segunda el 8 de Noviembre, ambas en línea. Los ganadores pasarán a la tercera ronda que será presencial en la ciudad de Nueva York el 6 y 7 de Diciembre. El siguiente video es un abreboca del concurso...

En el sitio web se encuentran las preguntas y los casos de las dos versiones anteriores.

Las inscripciones aún están abiertas, yo ya me inscribí!

%d bloggers like this: