Descargas automatizadas de históricos de cotizaciones

angelsdoz · el 16 de febrero

Bueanas tardes. El otro día vi un woutube https://www.youtube.com/watch?v=qLieIhUFMAk donde esplicaban como descargar el histórico de unas cotizaciones. Funciona muy bien pero lo que quiero ahora es generar una macro que barra una tabla de valores y decargar sus históricos.

La solucion que propone para pocos valores es editar la consulta y cambiar el tiker anterior "BTCUSDT" por el nuevo, pero creo que eso no se puede hacer automaticamente. Si se pudiera hacer sería un forma perfecta pues es mucha mnás rápida y solo requiere el cambio de unas pocas letras.
La otra solución sería relanzar la macro para cada uno de los valores. generando para cada valor el nobre, la fórmula y varios puntos en los que aparece el valor..
Me surgen dudas antes de ponerme a ello.
1.- Funcionará cambiando eos valores, fórmuals.... con otros en los que el valor "BCTUSDT" se cambie por el nuevo?
2.- El nombre no es gran problema pero la fórmula está plagada de """" ¿Habría alguna forma de generar la fórmula de una forme más facil?
3.- en la líne "With ActiveSheet.... entiendo que "Location" también se podría generar xon una variable
4.- ¿Que hacer con .CommandText= Array(.... en la que también aparece el valor
5.- por último en la 5ª líne por el final aparece denuevo el valor

Gracias de antemano

Adjunto el macro generado en el que entre la línea 3 y 11 se encuentra el nobre y la formula

Sub Macro7()
' Macro7 Macro
    ActiveWorkbook.Queries.Add Name:= _
        "klines?symbol=BTCUSDT&interval=5m&limit=1000&startTime=1609459200000&endTime=164" _
        , Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Origen = Json.Document(Web.Contents(""https://api.binance.com/api/v3/klines?symbol=BTCUSDT&interval=5m&limit=1000&startTime=1609459200000&endTime=1644969600000""))," & Chr(13) & "" & Chr(10) & "    #""Convertida en tabla"" = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Valores extraídos"" = Table.TransformColumns(#""Convertida en tabla"", {" & _
        """Column1"", each Text.Combine(List.Transform(_, Text.From), "";""), type text})," & Chr(13) & "" & Chr(10) & "    #""Valor reemplazado"" = Table.ReplaceValue(#""Valores extraídos"",""."","","",Replacer.ReplaceText,{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Dividir columna por delimitador"" = Table.SplitColumn(#""Valor reemplazado"", ""Column1"", Splitter.SplitTextByDelimiter("";"", QuoteStyle.Csv), {""Column1.1" & _
        """, ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6"", ""Column1.7"", ""Column1.8"", ""Column1.9"", ""Column1.10"", ""Column1.11"", ""Column1.12""})," & Chr(13) & "" & Chr(10) & "    #""Tipo cambiado"" = Table.TransformColumnTypes(#""Dividir columna por delimitador"",{{""Column1.1"", Int64.Type}, {""Column1.2"", type number}, {""Column1.3"", type number}, {""Column1.4""" & _
        ", type number}, {""Column1.5"", type number}, {""Column1.6"", type number}, {""Column1.7"", Int64.Type}, {""Column1.8"", type number}, {""Column1.9"", Int64.Type}, {""Column1.10"", type number}, {""Column1.11"", type number}, {""Column1.12"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Columnas con nombre cambiado"" = Table.RenameColumns(#""Tipo cambiado"",{{""Column1.1"", ""ts""}})," & Chr(13) & "" & Chr(10) & " " & _
        "   #""Personalizada agregada"" = Table.AddColumn(#""Columnas con nombre cambiado"", ""Fechas"", each [ts]/ 86400000+ 25569)," & Chr(13) & "" & Chr(10) & "    #""Columnas quitadas"" = Table.RemoveColumns(#""Personalizada agregada"",{""Column1.7"", ""Column1.8"", ""Column1.9"", ""Column1.10"", ""Column1.11"", ""Column1.12""})," & Chr(13) & "" & Chr(10) & "    #""Columnas reordenadas"" = Table.ReorderColumns(#""Columnas qui" & _
        "tadas"",{""Fechas"", ""ts"", ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6""})," & Chr(13) & "" & Chr(10) & "    #""Tipo cambiado1"" = Table.TransformColumnTypes(#""Columnas reordenadas"",{{""Fechas"", type datetime}})," & Chr(13) & "" & Chr(10) & "    #""Columnas quitadas1"" = Table.RemoveColumns(#""Tipo cambiado1"",{""ts""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Columnas quitadas1"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""klines?symbol=BTCUSDT&interval=5m&limit=1000&startTime=160945" _
        , "9200000&endTime=164"";Extended Properties="""""), Destination:=Range("$A$1" _
        )).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [klines?symbol=BTCUSDT&interval=5m&limit=1000&startTime=1609459200000&endTime=164]" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "klines_symbol_BTCUSDT_interval_5m_limit_1000_startTime_1609459200000_endTime_164"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Frank2021 · el 17 de febrero

Hola,

te envío mi propuesta

Instrucciones:

Baja los 2 ficheros adjuntos y ponlos juntos en la misma carpeta

Abre el excel y cambia las casillas amarillas según convega

Pulsa el botón "Bajar datos".

Saludos

Cripto.xlsm qry.txt

angelsdoz · el 18 de febrero

Antes de nada gracias por tu rçapida respiesta.

Como me indicas he puesto los dos ficheros en un mismo directorio y me da un "error definido porla aplicacion o el objeto" en la última línea: wbq.Refresh..

Gracias de antemano

Frank2021 · el 19 de febrero

Hola,

para acotar el problema necesito que compruebes lo siguente:

1. Que la versión de Excel que usas es 2013 o 2016 o 2019 o 365.

2. Que tienes instalado Power Query, Add-In gratuito de Microsoft. Si es que no, bajatelo e instalalo. Power Query

3. La versión que te adjunto tiene una pequeña modificación que mostrará un mensaje en pantalla con la descripción del error en caso de haberlo. Pruebala.

4. Enviar una captura de pantalla mostrando ventanas para que yo vea lo que está pasando del Excel y del Explorardor de Archivos donde se vea la trayectoría y los archivos guardados que hay en el directorio. Te envío las mías.

Gracias

Cripto.xlsm qry.txt

angelsdoz · el 20 de febrero

Gracias por tu ayuda pero sigo igual-.

En relsción con tus preguntas:

1.- La versión de Office que tengo es la 365

2.- La versión de Power Query que me descargo del enlace que me mandas es para 32bits y no me deja instalarla, por lo que veo por internet solo hablan de esa versión y para Office distinta de la 365.

3.- He probado la nueva versión que me mandas pero me da el mismo error: "Error definido porla aplicacion o el objeto

4.- Te adjunto los pantallazos que me cpides, em el de Excel no aparece ni la pantalla de la consulta ni la editable.

Gracias

Frank2021 · el 21 de febrero

Hola,

gracias por la información.

Como tienes Excel 365 no es necesario instalar Power Query, ya te comentaba en los pasos anteriores que primero era ver si lo tenías instalado y en caso de que NO era cuando se necesita instalar.

Yo también tengo Excel 365, lo que es buena noticia pues nos deja igualados.

Ahora comprobemos que puedes editar la query en Power Query, para ello:

Ve al menú Datos -> Consultas y conexiones, aparecerá un panel a la dereccha "Consultas y conexiones".

Después botón derecho sobre Q1 y del menú elegir la opción Editar.

Estos pasos se resumen graficamente en la siguiente captura.

Ahora aparecerá el editor de Power Query

Comprueba si te sale igual que mi pantalla, en caso de que NO, capturas la pantalla y me la envías, en caso de que SÍ, pulsa Actaulizar todo a ver que pasa

Ya me cuentas

Saludos

angelsdoz · el 21 de febrero

·He realizado los pasos que comentas u sí me salen las páginas que incluyes, al pulsar en "Actualizar Todo" aparecen unos puntos en la línea superior a "Table:.RemoveCplum......." como si se estuviera actualizando pero aparece lo mismo.

He cambiado el valor a descargar para ver si lo actualiza pero permanece igual.

angelsdoz · el 17 de marzo

Fran2021: Muchas gracias por tu ayuda.

He estado unas semanas fuera y por eso no he seguido requiriendo tu ayuda.

Si no quieres seguir, no puedes o por cualquier otra causa, lo entiendo y gracias por todo, pero si puedes y quieres, por favor podrías seguir con este tema, creo que pudiera ser interesante para muchos otros adaptándolo a otras circunstancias y páginas de donde descargar información-