Como tener el precio histórico de las criptomonedas en un archivo de Excel (últimos 6 meses) - I

Que tal:

  Gracias por seguirnos visitando en este blog y dando sus opiniones, en este ocasión voy a presentar un artículo en dos partes (puede extenderse a más, no lo se de momento) esperando les sea de utilidad. Ya en otras ocasiones he hablado de las criptmonedas en este blog, pros y contras. Ahora veremos como tener el precio histórico en un gráfico de Microsoft Excel y más adelante haré lo posible por mostrar como hacer lo mismo pero en PHP para incluir esos gráficos en un sitio web.

 Recientemente publiqué un artículo sobre la criptomoneda "Chaucha" y sigo las novedades de la misma en su grupo oficial de Facebook, pues bien, gracias este grupo me enteré que otro desarrollador ha publicado una manera de tener el precio de la moneda en Microsoft Excel. Esto permite en tiempo real estar monitoreando el precio de Chaucha sin tener que visitar algún servicio web, lo que agiliza bastante el proceso.

Publicidad

 Me pareció interesante la propuesta, sin embargo le veo algunos detalles: Primero, se necesita Microsoft Excel 2016 y segundo, solo se muestra el precio de compra y venta, pero lo que yo quiero es ver el precio histórico de la moneda.

  En la red existen múltiples servicios web para ver los precios históricos de las criptomonedas y que este pueda descargarse en un archivo CSV para con ello generar una gráfica (no he encontrado uno por el momento para Chaucha lamentablemente). Tomando esto como base haremos uso de los archivos entregados por CoinGecko.

  CoinGecko es un portal web dedicado a las criptomonedas, podemos encontrar en el los detalles de diversas criptomonedas, los precios históricos, gráficos de su comportamiento, entre otros datos. Bien, la parte que nos interesa es el precio histórico, pero que pueda descargarse en algún formato para luego con ello generar una gráfica desde Microsoft Excel.

  ¿Porqué desde Microsoft Excel?, porque Excel es una herramienta ampliamente usada, además no hay necesidad de abrir ningún navegador web y teclear la dirección de CoinGecko y luego buscar la moneda. Microsoft Excel mediante macros puede hacer todo eso por nosotros y con simplemente abrir el libro ya se descargarían los datos necesarios y se generaría la gráfica, veamos como es esto.

  Lo primero que necesitamos es tener la ruta del archivo CSV para la moneda que nos interesa, en este caso vamos a ver el ejemplo para Bitcoin (es aplicable para cualquier moneda listada en CoinGecko). Abriremos nuestro navegador, teclearemos la dirección https://www.coingecko.com/es; ahora abriremos la lista de monedas disponibles, se encuentra del lado derecho de la página web.

  Ingresaremos el nombre de la moneda a consultar, Bitcoin (BTC) y haremos un clic sobre el nombre de la moneda.

  Nos desplazaremos en la página web hasta llegar a la pestaña "Tabla de precios", la presionaremos y entonces veremos el histórico de los precios del Bitcoin (el precio se encuentra en dólares - USD). Podemos filtrar el rango del tiempo que queramos analizar, bajo ese gráfico veremos un enlace que dice "Exportar como" y esperaremos a que se abra un menú como el siguiente:

  Pasaremos con el puntero del mouse por encima del enlace, presionaremos con el botón secundario y elegiremos la opción "Copiar dirección del enlace".

  Ya está, con esto ya tenemos en el portapapeles la dirección web del archivo CSV generado por CoinGecko (este archivo se actualiza a diario), ahora veamos como podemos generar nuestra gráfica en Excel.

  Antes que nada debemos tener habilitada la opción de ejecución de Macros en Microsoft Excel, de lo contrario no se ejecutará el siguiente script. También, hay que ir a la pestaña programador de nuestro Excel ya funcionando (estos scripts están probados en Microsoft Excel 2007, en teoría deben trabajar en versiones superiores, el formato es XLSM y para versiones de Excel 2003 e inferiores se distribuye el mismo script pero en formato XLS, hasta el momento esto no funciona en LibreOffice). Si no saben como activar la pestaña programador visiten por favor el siguiente enlace.

  Una vez habilitada la opción para la pestaña programador, la presionaremos y haremos clic en la opción "Visual Basic", esto nos permitirá ir al editor de scripts de Excel.

  Dentro de nuestro editor haremos doble clic sobre la opción "ThisWookbook" e introduciremos el siguiente código fuente:

'#### Purpose: Get crytocoin data from server at book open
'#### Created date: 19/02/2018
'#### Created by username: Juan Manuel Mar Hdz.
'#### Last modified date: 20/02/2018
'#### Last modified username: Juan Manuel Mar Hdz.
'#### Thanks to https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-open-event-excel
'#### Thanks to https://www.mrexcel.com/forum/excel-questions/686700-import-csv-file-web-vba.html
'#### Thanks to https://stackoverflow.com/questions/29967411/quickest-way-to-clear-all-sheet-contents-vba
'#### Thanks to https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-activate-method-excel
'#### Thanks to http://www.excel-easy.com/vba/examples/statusbar.html
'#### Thanks to https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
Private Sub Workbook_Open()

  On Error Resume Next
  
    Err.Clear
  
    Dim URL As String, destCell As Range
    Dim FirstRow As Long, LastRow As Long, daystofilter As Long
    
    daystofilter = 180
    URL = "http://www.coingecko.com/price_charts/export/bitcoin/usd.csv"
    Application.StatusBar = "Preparando área de trabajo..."
    
    Worksheets("Grafico").Activate
    
    Sheets("Datos").UsedRange.ClearContents
    Sheets("tmp").UsedRange.ClearContents
    
    Set destCell = Worksheets("tmp").Range("A1")
    
    Application.StatusBar = "Descargando datos, espere por favor..."
    
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & Trim(URL), Destination:=destCell)
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    Application.StatusBar = "Filtrando datos..."
    
    LastRow = Worksheets("tmp").Range("A1").CurrentRegion.Rows.Count
    Worksheets("tmp").Range("A:A").Replace What:="UTC", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Worksheets("tmp").Range("A:A").NumberFormat = "dd/mm/yyyy"
    
    If LastRow <= 0 Then
      MsgBox "No se encontraron datos, al parecer no fue posible descargarlos desde la ruta '" + Trim(URL) + "'...", vbExclamation, "Generar gráfico"
    Else
    
      If LastRow - daystofilter <= 1 Then
        FirstRow = 2
      Else
      
        FirstRow = LastRow - daystofilter
        FirstRow = FirstRow + 1
        
      End If
      
      Sheets("tmp").Select
      Range("A" & FirstRow & ":B" & LastRow).Select
      Selection.Copy
      Sheets("Datos").Select
      Range("A1").Select
      ActiveSheet.Paste
      
    End If
    
    Application.StatusBar = "Preparando gráfico..."
    
    destCell.Parent.QueryTables(1).Delete
    Worksheets("Grafico").Activate
    
    Application.StatusBar = False
    Set destCell = Nothing
    
    If Err.Number <> 0 Then
      MsgBox "Ocurrió el error '" + Err.Description + "' al actualizar los datos...", vbCritical, "Generar gráfico"
    End If
    
End Sub

  El script hace lo siguiente: Recibe una dirección web desde la cual descargar el archivo CSV y con ello generar la gráfica.

URL = "http://www.coingecko.com/price_charts/export/bitcoin/usd.csv"

  Importante: usen el protocolo HTTP y no HTTPS de la ruta copiada en los pasos anteriores, de lo contrario aparecerá un cuadro de diálogo para introducir un usuario y contraseña (presionaremos cancelar para cerrarlo y continuar), luego con ese dato se procede a descargarlo e insertar todo el histórico en la pestaña "tmp" de nuestro libro de Excel.

Worksheets("Grafico").Activate
    
    Sheets("Datos").UsedRange.ClearContents
    Sheets("tmp").UsedRange.ClearContents
    
    Set destCell = Worksheets("tmp").Range("A1")
    
    Application.StatusBar = "Descargando datos, espere por favor..."
    
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & Trim(URL), Destination:=destCell)
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With

  Como podemos ver, lo primero que hacemos es activar la pestaña "Grafico" (para tener de fondo las operaciones), luego borrar todo el contenido de las pestañas "Datos" y "tmp" y copiaremos el contenido descargado a la primer celda de la pestaña tmp.

  Ahora bien, un detalle: Aunque en la página web de CoinGecko podemos filtrar el gráfico por diversos días, cuando descarguemos el archivo CSV se viene absolutamente todo, por lo que debemos filtrar esos datos en nuestra macro ya que en lo personal solo quiero ver los últimos 6 meses de comportamiento. Esto se hace de la siguiente manera:

Application.StatusBar = "Filtrando datos..."
    
    LastRow = Worksheets("tmp").Range("A1").CurrentRegion.Rows.Count
    Worksheets("tmp").Range("A:A").Replace What:="UTC", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Worksheets("tmp").Range("A:A").NumberFormat = "dd/mm/yyyy"

  Lo que estamos haciendo es preparar la primer columna de nuestros datos descargados a formato fecha, ahora haremos el filtro.

If LastRow <= 0 Then
      MsgBox "No se encontraron datos, al parecer no fue posible descargarlos desde la ruta '" + Trim(URL) + "'...", vbExclamation, "Generar gráfico"
    Else
    
      If LastRow - daystofilter <= 1 Then
        FirstRow = 2
      Else
      
        FirstRow = LastRow - daystofilter
        FirstRow = FirstRow + 1
        
      End If
      
      Sheets("tmp").Select
      Range("A" & FirstRow & ":B" & LastRow).Select
      Selection.Copy
      Sheets("Datos").Select
      Range("A1").Select
      ActiveSheet.Paste
      
    End If

  La variable LastRow contendrá la posición del último dato ingresado (o descargado mejor dicho). Si hay datos seleccionaremos el rango de los últimos 180, que equivale a 6 meses de historia. Si hubo éxito entonces ese rango es copiado a la pestaña "Datos" y los datos en esa pestaña actualizarán el gráfico en la pestaña "Gráfico".

Ya solo queda cerrar las conexiones y mostrar un mensaje en caso de error.

Application.StatusBar = "Preparando gráfico..."
    
    destCell.Parent.QueryTables(1).Delete
    Worksheets("Grafico").Activate
    
    Application.StatusBar = False
    Set destCell = Nothing
    
    If Err.Number <> 0 Then
      MsgBox "Ocurrió el error '" + Err.Description + "' al actualizar los datos...", vbCritical, "Generar gráfico"
    End If

Este es el resultado:

  Como podemos ver, ahora tenemos el histórico del Bitcoin en nuestro excel. Tan pronto abramos nuestro libro se disparará la función Private Sub Workbook_Open() y ella se encargará de hacer todo el trabajo que hemos mencionado.

  Ahora bien, un detalle. Si abrimos el archivo XLS y comparamos el gráfico con el del archivo XLSM vemos que es diferente. La razón es que este gráfico no muestra correctamente las fechas en el archivo XLS (supongo que el formato), es la única diferencia, los códigos trabajan exactamente igual.


  Los invito a descargar el siguiente archivo, el cual permite generar las gráficas de los precios históricos para las monedas Bitcoin, Bitcoin Cash, Dash, Doge Coin, Ethereum, Lite Coin, Monero y Ripple. En una segunda entrega veremos como hacer esto mismo pero para otras monedas, esperamos nos puedan acompañar.

Actualización:  Se volvió a subir el archivo zip con los códigos de las criptomonedas para incluir la función de limpiar el portapapeles al terminar de copiar los rangos de datos.

¿Te gustó este post?, entonces si lo deseas puedes apoyarnos para continuar con nuestra labor, gracias.

---