Tipos de datos como Cotizaciones

Veremos en el post de hoy una curiosidad de las últimas versiones de Excel: el tipo de dato Cotizaciones.
Sin duda es una herramienta muy concreta del sector financiero, pero muy práctico para recuperar información bursátil sin emplear complicados códigos o macros.
Este tipo de datos Cotizaciones (Stocks en inglés) nos permite trabajar de diferentes formas y en esta explicación veremos algunas de ellas.

Comenzaremos disponiendo en el rango C1:H1 seis valores del IBEX 35 español:
ACCIONA
BANCO BILBAO VIZCAYA
REPSOL
VISCOFAN
AENA
ENDESA
y desde la ficha Datos > grupo Tipos de datos > botón Cotizaciones

Al pasar esas celdas como 'Cotizaciones' podría darse el caso que alguna de ellas no la reconozca.. en mi ejemplo el caso del 'BANCO BILBAO VIZCAYA'

Para corregir el problema presionaremos el símbolo de interrogación (?) que aparece a la izquierda del texto y al abrirse el panel de Selector de datos daremos otros valores para tratar de localizar el valor bursátil deseado.
Por ejemplo escribimos BBVA y Seleccionamos entre las opciones desplegadas el correcto...

Una vez todos los valores identificados correctamente procedemos a obtener información de cada empresa.

La información recuperable de cada valor responde al siguiente listado:
52 week high
52 week low
Beta
CEO
Change
Change (%)
Currency
Description
Employees
Exchange
Exchange abbreviation
Headquarters
High
Industry
Instrument type
Last trade time
Low
Market cap
Name
Open
P/E
Previous close
Price
Shares outstanding
Ticker symbol
Volume
Volume average
Year founded

La primera forma de obtener esa información es referirnos a la celda que contiene ese tipo de dato -Cotización, por ejemplo C1...
Al escribir la referencia
=C1
aparecen desplegadas todas y cada una de las opciones listadas en el párrafo anterior:

Vemos que la forma en que finalmente se ve la 'fórmula' tiene el aspecto:
=C1.[52 week high]
=C1.[52 week low]
=C1.Beta
etcétera

Una vez completada la información deseada de entre las opciones ofrecidas, podremos arrastrar fácilmente hacía la derecha y completar la misma información del resto de empresas.

Otra posibilidad es emplear la función VALORCAMPO o FIELDVALUE.
=FIELDVALUE(valor;campo info)

Si montamos una tabla de referencia con el rango C1:H1 como Cotizaciones (como hemos visto en el caso anterior) y en el rango B2:B29 los conceptos de la información a recuperar...
podremos en C2:H29 añadir la función:
=FIELDVALUE(C$1;$B2)
y disponer de todos los datos necesasrios

La última forma consiste en emplear el asistente...
A partir de una celda almacenada como Cotizaciones aparecerá un botón de adición, que habilita añadir cualquiera de la información listada más arriba.

Esto nos permite fácilmente, sin conocimientos de fórmulas o referencias, insertar datos de las empresas cotizadas.

Finalmente, en cualquiera de los casos descritos, al hacer clic derecho sobre la celda 'cotizada' > Tipo de datos se ofrecerán algunas opciones simples de comprender:
1- Mostrar tarjeta
2- Actualizar
3- Cambiar
4- Convertir en texto

Muy importante la opción de Refrescar/Actualizar para tener siempre el último dato vivo de los valores con los que trabajamos.

OJO!!!: los datos mostrados provienen de proveedores privados y no suelen estar actualizados al minuto, si no que devuelven datos con un retraso temporal de unos quince minutos o más!!!
Recomiendo extraer siempre la info 'Last trade time' para tener una aproximación horaria del precio del valor mostrado... aunque esta info no indica la hora del dato!!!.

Seguir leyendo...

UNIRCADENAS en Excel 365

Una función interesante que nos permite obtener listados en una celda es la función UNIRCADENAS, una función válida para versiones 365 y 2019 (y siguientes...).
Esta es una función interesante que combina el texto de varios rangos o celdas incluyendo un delimitador que especifiquemos entre cada valor de texto que se combinará:
=UNIRCADENAS(delimitador; ignorar_vacío (V/F); Texto1; [Texto2];...)

Comentar una alternativa a la función para el resto de versiones de Excel comentada aquí.

En el ejemplo de hoy necesitaremos recuperar el listado de comerciales asociados a un país del siguiente listado:

En la celda F5 dispondremos el país del que queremos obtener el listado de comerciales y en G5 añadimos la fórmula matricial:
=UNIRCADENAS(", ";VERDADERO;SI(A2:A18=F5;B2:B18;""))
(recuerda validarla presionando Ctrl + Mayusc + Enter en vez de solo Enter).

En este caso la parte matricial
SI(A2:A18=F5;B2:B18;"")
nos devuelve la matriz de valores
{"Pepe";"";"";"";"Isabel";"";"";"";"Raúl";"";"";"";"Irene";"";"";"";"Antonia"}
esto es, solo los comerciales de 'ES'...

A continuación entre en juego la función UNIRCADENAS, donde indicamos el separador deseado (la coma) y como segundo argumento VERDADERO para omitir todos aquellos valores a combinar vacíos o sin dato...

El resultado se ve es el deseado:
Pepe, Isabel, Raúl, Irene, Antonia

Función muy simple pero interesante.

Una alternativa podría ser el empleo de Power Query como vimos en este post

Seguir leyendo...

Reordenar columnas por filas

Hace algunas semanas publiqué un artículo donde explicaba una manera de desapilar columnas (ver aquí).
Era un ejemplo inicialmente pensado para datos distribuidos de manera homogénea o que respondiera a un patrón...
de hecho, a través de un comentario, un lector (Daniel Herce) proponía muy hábilmente una alternativa más simple con la función DESREF (gracias de nuevo por el aporte) ;-)

La ventaja, sin embargo, del método publicado frente a la elegante solución de Daniel, es que éste nos abre el abanico para soluciones no homogéneas o sin patrón.

Siguiendo aquel post daremos solución a un lector que preguntaba:

[...]tengo varios datos en la columna A y deseo pasarlos a una matriz que debe iniciar en fila nueva cada vez que encuentre un numero con guiones (ejemplo 1-1-1), anexo un ejemplo de lo que tengo y como debería quedar la matriz[...]

Nuestra solución formulada se verá así:

El primer paso consiste en insertar dos columnas B2:B22 y C2:C22 con cálculos auxiliares.
En B2 añadimos la siguiente fórmula que arrastraremos hasta B22:
=SI(ESERROR(ENCONTRAR("-";A2));B1+1;1)

El resultado es para cada tramo, diferenciado por el valor inicial (que incluye un guión), un secuencial 1,2,3,4,5,6,...

En C2 añadimos la siguiente fórmula que arrastraremos hasta C22:
=SI(B2=1;C1+1;C1)

Como resultante vemos numeradas, todas las filas de cada bloque, secuencialmente.

Estos dos rangos serán el apoyo para nuestra fórmula final.

Disponemos los encabezados necesarios en el rango E7:K13...
En F7:K7: 1-2-3-4-5-6
En E8:E13: 1-2-3-4-5-6- etc

Y completamos en F8 que luego copiaremos y pegaremos al resto del rango F8:K13:
=SI.ERROR(INDICE($A$2:$A$22;SI(SUMAPRODUCTO(($C$2:$C$22=$E8)*($B$2:$B$22=F$7)*(FILA($A$2:$A$22)))=0;NOD();SUMAPRODUCTO(($C$2:$C$22=$E8)*($B$2:$B$22=F$7)*(FILA($A$2:$A$22)-1))));"")

haz clic en la imagen

En esencia es la misma fórmula expuesta en el post anteriromente comentado (aquí)...
Pero incorpora algunos condicionales para gestionar la no existencia en todos los tramos de todos los elementos...

Con esto se soluciona cualquier casuística de elementos dados por columnas y traspasados a filas.

Seguir leyendo...

VBA: Guardar como .Dialogs(xlDialogSaveAs)

Aprenderemos otro sencillo método para guardar como... nuestros libros de trabajo de manera alternativa al clásico Workbooks.SaveAs filename.

El caso será muy simple, bastará emplear y llamar a las ventanas de diálogos integrados, y en concreto a la de 'Guardar como': .Dialogs(xlDialogSaveAs)

Con esta ventana podemos indicar hasta seis argumentos, que son en parte las opciones estándar de la ventana:

Arg1 (document_text): ruta y nombre del fichero propuesto
Arg2 (type): tipo de formato... serían los empleados por los parámetros de XlFileFormat
Arg3 (password): la contraseña del fichero
Arg4 (backup): TRUE o FALSE si queremos o no guardar una copia de seguridad del fichero
Arg5 (write_password): contraseña de escritura
Arg6 (read_only_rec): TRUE o FALSE para recomendar al abrir que sea solo lectura


Comenzaremos insertando un módulo estándar en nuestro proyecto de VB:

Sub VentanaDialogoGuardarComo()
Dim ruta As String
'ruta y nombre por defecto...
ruta = ThisWorkbook.Path & "\PruebaGuardado.xlsm"

'abrirá una ventana diálogo para cambiar la ubicación donde se guardará.
'el Arg1 será la ruta y nombre del fichero
'y el Arg2 el tipo de fichero a guardar....
Guardando = Application.Dialogs(xlDialogSaveAs).Show(arg1:=ruta, _
                    arg2:=xlOpenXMLWorkbookMacroEnabled)

End Sub

En nuestro ejemplo solo configuramos la ruta y el tipo del fichero, sin considerar contraseñas o copias de seguridad...

Otra ventana diálogo interesante es .Dialogs(xlDialogSendMail) que permite enviar el libro de trabajo adjunto a la dirección y con el asunto deseado empleando Outlook.

Insertando un módulo estándar en nuestro proyecto de VB:

Sub EnvioEmail()
'En el ejemplo de código siguiente se abre un mensaje de correo electrónico en Microsoft Outlook con el libro adjunto actual.
Application.Dialogs(xlDialogSendMail).Show arg1:="excelforo@excelforo.com", _
            arg2:="Asunto del email...", _
            arg3:=True
            
End Sub

En este caso solo tenemos tres argumentos
recipients, subject, return_receipt
Arg1 (recipients): dirección de email del destinatario
Arg2 (subject): Asunto del email
Arg3 (return_receipt): TRUE o FALSE si queremos confirmación de la recepción del email.

Una ventana diálogo mas, también curiosa: .Dialogs(xlDialogProperties) o su equivalente .Dialogs(xlDialogSummaryInfo)

Insertando un módulo estándar en nuestro proyecto de VB:

Sub CambioPropiedadesFichero()
Application.Dialogs(xlDialogProperties).Show Arg1:="Excelforo", _
                        Arg2:="Cambio Propiedades", _
                        Arg3:="Ismael Romero (el autor)", _
                        Arg4:="Palabras claves (Excel, ventanas diálogo..)", _
                        Arg5:="Comentarios sobre la programación..."
'o con la otra posibilidad
'Application.Dialogs(xlDialogSummaryInfo).Show Arg1:="Excelforo", _
                        Arg2:="Cambio Propiedades", _
                        Arg3:="Ismael Romero (el autor)", _
                        Arg4:="Palabras claves (Excel, ventanas diálogo..)", _
                        Arg5:="Comentarios sobre la programación..."
End Sub

Lo malo de estos cuadros diálogo (y del resto) es la limitación de configurar los argumentos definidos...

Seguir leyendo...

VBA: El objeto ArrayList

Aprenderemos algunos aspectos de este curioso objeto ArrayList, que es una estructura de datos muy útil cuando queremos trabajar con rangos dinámicos, pero queremos evitar la molestia de tener que redefinir (Redim) constantemente el tamaño del rango o array.
Los ArrayList no tienen un tamaño fijo, por lo que podemos seguir agregándole elementos constantemente.
A pesar de esta ventaja, en VBA suelo recomendar utilizar la colección -Collection...

Supongamos tenemos una tabla ('TblCiudad') con unas ciudades listadas que deseamos ordenar...

Insertamos un módulo estándar, y en esta ventana de código añadiremos nuestra macro:

Sub Ordenar()
Dim arr As Object   'declaramos nuestra ArrayList
'y la matriz con la que trabajaremos
Dim arrTrabajo() As Variant, arrOrdenada() As Variant

Dim rngCiudades As Range
Set rngCiudades = Hoja1.Range("TblCiudad[Ciudades]")

'creamos el objeto ArrayList
Set arr = CreateObject("System.Collections.ArrayList")

'cargamos la matriz con los valores del rango
arrTrabajo = rngCiudades

'añadimos los elementos del rango al ArrayList
For Each elto In arrTrabajo
    arr.Add elto
Next elto

'ordenamos el objeto en Ascendente
arr.Sort
'podríamos ordenar el objeto en Descendente
'arr.Reverse

'Reconvertimos en una Array 'normal' el objeto ArrayList ya ordenado
'y lo almacenamos en una nueva Array
arrOrdenada = arr.ToArray

'finalmente llevamos a la tabla los valores ya ordenados...
For i = 1 To arr.Count
    rngCiudades.Item(i).Value = arrOrdenada(i - 1)
Next i

'También podríamos haber hecho
'Range("ndCiudades").Value = Application.Transpose(arrOrdenada)
End Sub

Curiosa la propiedad .ToArray de este objeto que traslada o transforma los elementos de éste en una array 'normal'.
Otra propiedad muy habitual es .Add que añade elementos al objeto.
También las propiedades .Sort o .Reverse para ordenar en ascendente y descendente, respectivamente, los elementos del objeto.

El resultado final, tras ejecutar la macro, es el esperado...

Seguir leyendo...

VBA: Validación de datos siempre ordenada

Me encontré un cliente que necesitaba tener siempre ordenados los elementos permitidos en una celda con validación de datos tipo lista... la solución que encontré fue emplear un método Range.Sort con un evento de hoja Worksheet_Change.

Veamos en qué consistiría el trabajo.
Tenemos un listado, en modo tabla (que llamaré 'TblCiudad'), que tiene algunas ciudades de España... a la que además hemos asignado un nombre definido:
ndCiudades =TblCiudad[ciudades]

En una celda D2 hemos configurado una validación de datos tipo Lista con origen en el nombre definido anterior 'ndCiudades'

El siguiente paso consistirá en añadir dentro de la ventana de código de la hoja de trabajo (Hoja1 para nuestro ejemplo) el siguiente evento Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
'evento que saltará ante cualquier cambio en la hoja..
Dim rngCiudades As Range
Set rngCiudades = Hoja1.Range("TblCiudad[Ciudades]")

'pero verificamos que únicamente ordenaremos cuando trabajemos en la tabla
If Not Intersect(Target, rngCiudades) Is Nothing Then
'conel méetodo Sort ordenamos el listado...
rngCiudades.Sort _
    Key1:=rngCiudades, _
    order1:=xlAscending, _
    Orientation:=xlSortColumns, _
    Header:=xlYes
End If

End Sub

Una vez insertado el evento podremos comprobar al cambiar algo en la tabla, y solo en la tabla, que se producirá la ordenación deseada... lo que inmediatamente repercutirá en el orden de la celda validada:

Seguir leyendo...

VBA: Modificar Hyperlinks en Excel

Un usuario preguntaba por la forma de modificar el texto mostrado en unas celdas con un listado de vínculos a una URLs.

El objetivo es que el hipervícunlo siga dirigiéndonos a la misma URL, pero el texto mostrado quede reducido al nombre de la página concreta...

Insertamos un módulo estándar, y en esta ventana de código añadiremos nuestra macro:

Sub LimpiarHipervinculos()
'recorremos cada celda del rango seleccionado
For Each celda In Selection
    'determinamos que la celda Sí contiene un vínculo
    If celda.Hyperlinks.Count > 0 Then
        'asignamos el texto del vínculo
        texto = celda.Hyperlinks(1).Name
        'y lo tratamos para quedarnos con un texto final a mostrar
        'con la función Split separamos las partes de la url 
        'y nos quedamos con la tercera con www.xxxxx.xxx
        nuevo = Replace(Split(texto, "/")(3), ".htm", "")
        'finalmente construimos el vínculo en la celda
        'con el mismo vínculo pero con un texto mostrado nuevo
        ActiveSheet.Hyperlinks.Add Anchor:=celda, _
                Address:=texto, _
                TextToDisplay:=nuevo
    End If
Next celda
End Sub

Relevante es la condición que nos permite detectar si la celda tiene un hipervínculo:
If celda.Hyperlinks.Count > 0 Then

y por otra parte la descomposición de la URL aprovechándonos de las barras /
Split(texto, "/")(3)
el tres nos permite recuperar la parte final de la URL...

El resto es simple al recomponer el hipervínculo con los valores tratados.

Seguir leyendo...

VBA: Rellenar un formulario PDF con SendKeys

En la entrada anterior del blog expliqué como completar un formulario pdf desde Excel, empleando Acrobat...
Hoy emplearemos el método SendKeys (ver aquí) para poder hacerlo usando Adobe Reader.

Este método replica las acciones del teclado sobre las diferentes aplicaciones activas en nuestro equipo... por lo que es más lento e impreciso que el expuesto en el post anterior.
A cambio es más abierto y vale para cualquier aplicación.

Tenemos nuestros datos en el rango C2:C6 dispuestos para trasladarlos al formulario PDF.

Insertamos un módulo estándar, y en esta ventana de código añadiremos nuestra macro:

Sub CompletarFormularioPDF()
'Rellenar un formulario PDF con Reader!!

'Declaramos las variables
Dim stRutaPDF As String, nombreForm As String, stRutaCompletaPDF As String
Dim stRutaGuardadoPDF As String

Dim objAcrobatApp As Object, objAcrobatAVDoc As Object, objAcrobatPDDoc As Object
Dim objJSO As Object
    
Application.ScreenUpdating = False
'la ruta del formulario a completar
stRutaPDF = "E:\excelforo\"
nombreForm = "FormWord_PDF.pdf"
stRutaCompletaPDF = stRutaPDF & nombreForm

'asignamos valor, desde las celdas, a las variables
'que servirán para completar el formularaio PDF
Dim nombre As String, NumExp As String, ciudad As String, registro As Date, email As String
NumExp = CStr(Hoja1.Range("C2").Value)
nombre = CStr(Hoja1.Range("C3").Value)
ciudad = CStr(Hoja1.Range("C4").Value)
registro = Format(CStr(Hoja1.Range("C5").Value), "dd/mm/yyyy")
email = CStr(Hoja1.Range("C6").Value)

'Generamos la ruta de los pdf guardados
stRutaGuardadoPDF = stRutaPDF & nombre & ".pdf"
'comprobamos si existe el fichero destino... y si existe lo eliminamos!!
If Dir(stRutaGuardadoPDF) <> Empty Then Kill (stRutaGuardadoPDF)

'abrimos el formulario PDF
ThisWorkbook.FollowHyperlink stRutaCompletaPDF

'y comenzamos a movermos empleando las teclas y métodos abreviados
Application.SendKeys "{Tab}", True      'nos desplazamos por el formulario TABulando
Application.SendKeys nombre, True       'rellenamos el nombre en el primer campo de Nombre
Application.Wait Now + TimeSerial(0, 0, 1) 'damos tiempo para rellenar el campo

Application.SendKeys "{Tab}", True      'nos desplazamos por el formulario TABulando
Application.SendKeys email, True       'rellenamos el nombre en el campo de Email
Application.Wait Now + TimeSerial(0, 0, 1) 'damos tiempo para rellenar el campo

Application.SendKeys "{Tab}", True      'nos desplazamos por el formulario TABulando
Application.SendKeys registro, True       'rellenamos el nombre en el campo de Fecha registro
Application.Wait Now + TimeSerial(0, 0, 1) 'damos tiempo para rellenar el campo

Application.SendKeys "{Tab}", True      'nos desplazamos por el formulario TABulando
Application.SendKeys NumExp, True       'rellenamos el nombre en el campo de Núm expediente
Application.Wait Now + TimeSerial(0, 0, 1) 'damos tiempo para rellenar el campo

Application.SendKeys "{Tab}", True      'nos desplazamos por el formulario TABulando
Application.SendKeys ciudad, True       'rellenamos el nombre en el campo de Ciudad origen
Application.Wait Now + TimeSerial(0, 0, 1) 'damos tiempo para rellenar el campo

'una vez completado el formulario lo guardaremos....
'pero como se ha abierto desde Reader usaremos la opción de 'guardar como'
'desde la opción de imprimir (Ctrl+P)
Application.SendKeys "^(p)", True   'presionamos Ctrl+P
Application.Wait Now + TimeSerial(0, 0, 3) 'damos tiempo para abrir la ventana diálogo

'en las opciones de elección de impresora
Application.SendKeys "{UP}", True   'presionamos flecha arriba
Application.SendKeys "{UP}", True   'presionamos flecha arriba.. hasta llegar a impresión 'AdobePDF'

Application.SendKeys "{Enter}", True
Application.Wait Now + TimeSerial(0, 0, 2) 'damos tiempo para abrir la ventana diálogo

Application.SendKeys "%(m)", True   'presionamos Alt+M para escribir el nombre del ficheor
Application.Wait Now + TimeSerial(0, 0, 2) 'damos tiempo para abrir la ventana diálogo
Application.SendKeys stRutaGuardadoPDF      'añadimos la ruta y nombre del fichero nuevo...
Application.Wait Now + TimeSerial(0, 0, 2)

Application.SendKeys "%(g)", True   'presionamos Alt+G para guardar
Application.Wait Now + TimeSerial(0, 0, 2)

'y acabamos cerrado el pdf y la aplicación
Application.SendKeys "^(q)", True   'presionamos Ctrl+Q para salir
'
Application.SendKeys "%(n)", True   'presionamos Alt+N para NO guardar cambios

'y restauramos el bloqueo numérico (por razones poco claras se desactiva...)
Application.SendKeys "{NUMLOCK}", True

'mensaje final
MsgBox "Proceso finalizado"
End Sub

Si ejecutamos la macro veremos paso a paso como se van rellenando los campos, abriendo las ventanas diálogo y ejecutando las diferentes acciones...

Seguir leyendo...