Excel VBA API call returning specific data only

Question

I would like to return data to Excel from a website (CoinMarketCap.com) similar to the example below:

VBA - API call displayed in Excel

...except I need to limit the amount of returned data to only specific currencies (not the entire Ticker).

The website requires an API Key (which I have) for these types of calls, but I don't understand how to format the url. The website provides this info:

Using Your API Key You may use any server side programming language that can make HTTP requests to target the Professional API. All requests should target domain https://pro-api.coinmarketcap.com.

You can supply your API Key in REST API calls in one of two ways:

Preferred method: Via a custom header named X-CMC_PRO_API_KEY

Convenience method: Via a query string parameter named CMC_PRO_API_KEY

But I can't make heads or tails from that. Ultimately I would like to have the url return (for instance) 3 currencies, such as BTC, ADA, DASH along with the API Key (which for example purposes is "abc-xyz".

Once I have the structure of the url I can work from there (in VBA), but I'm at a total loss as to how to format the url so that it will return only that specific data.

Answer 1

Public API

You might find it easier to start with the public API; That has nice easy syntax. You will need to use JSONConverter to parse the JSON response. After downloading and adding to your project you will need to go VBE > Tools > References and add a reference to Microsoft Scripting Runtime.

The following then shows you how to implement a very bare bones class, clsHTTP, to hold the XMLHTTPRequest object and expose a method GetJSON for retrieving the JSON string.

I then give some examples of parsing the JSON response for the specified cryptocurrency and USD.

It is very basic, and you would want to develop this, but I know the documentation for some of these APIs can be difficult to get a handle on.

Note: this is client side. I think the documentation you were referencing is possibly for web application development based on the server side.

---

Class clsHTTP

Option Explicit
Private http As Object
Private Sub Class_Initialize()
    Set http = CreateObject("MSXML2.XMLHTTP")
End Sub

Public Function GetJSON(ByVal URL As String) As String
    With http
        .Open "GET", URL, False
        .send
        GetJSON = StrConv(.responseBody, vbUnicode)
        'Debug.Print GetJSON
    End With
End Function

Standard module:

Option Explicit
'Public API:  https://coinmarketcap.com/api/
Public Sub GetTickerInfo()
    Const BASE_URL As String = "https://api.coinmarketcap.com/v2/ticker/1/?convert="
    Dim tickers(), i As Long, http As clsHTTP, jsonString As String, json As Object

    tickers = Array("BTC", "ADA", "DASH")
    Set http = New clsHTTP

    For i = LBound(tickers) To UBound(tickers)
        jsonString = http.GetJSON(BASE_URL & tickers(i))
        Set json = JsonConverter.ParseJson(jsonString)("data")("quotes")
        Debug.Print tickers(i) & " : " & json(tickers(i))("price")
        Debug.Print "USD" & " : " & json("USD")("price")
        Set json = Nothing
    Next
End Sub

---

Pro-API

①Specify multi-conversion in string:

The "starter plan" only allows me to specify one conversion option (so can't easily see how to bundle currencies). You might be able to pass the following, if you have an upgraded account, instead of the loop, in my code below.

jsonString = http.GetJSON("https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest?convert=BTC,ADA,DASH")

② Get all latest and parse out required symbols:

WARNING: This is expensive in terms of your credits. You want to try and bundle a call if possible. starter account has 200 credits per day.

You can parse out of the JSON, using JSONConverter, what you need as follows:

Class clsHTPP:

Option Explicit
Private http As Object
Private Sub Class_Initialize()
    Set http = CreateObject("MSXML2.XMLHTTP")
End Sub

Public Function GetJSON(ByVal URL As String) As String
    With http
        .Open "GET", URL, False
        .setRequestHeader "X-CMC_PRO_API_KEY", "yourAPIkey"
        .setRequestHeader "Accept-Encoding", "gzip"
        .send
        GetJSON = StrConv(.responseBody, vbUnicode)
        'Debug.Print GetJSON
    End With
End Function

Standard module 1:

Option Explicit
Public Sub GetTickerInfo()
    Dim http As clsHTTP, jsonString As String, json As Object, crypto As Object

    Set http = New clsHTTP
    jsonString = http.GetJSON("https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1&limit=5000&convert=USD")
    Set json = JsonConverter.ParseJson(jsonString)("data") 'collection

    For Each crypto In json  'dictionaries within collection
        On Error Resume Next
        Select Case crypto("symbol")
         Case "BTC", "ADA", "DASH"
           EmptyDictionary crypto
        End Select
        On Error GoTo 0
    Next
End Sub
Public Sub EmptyDictionary(ByVal dict As Object)
    Dim key As Variant
    For Each key In dict.keys
        Debug.Print key & " : " & dict(key)
    Next
    Debug.Print vbNewLine
End Sub

---

Using the class with a different API:

The following API has the kind of syntax I think you are after:

https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=ADA,USD,DASH,BTC

You can thus use the class clsHTTP as follows, note that I have dropped the use of JSONConverter, using Split to get the info you want instead. You can easily still use JSONConverter if you wish.

Option Explicit
Private http As Object

Private Sub Class_Initialize()
    Set http = CreateObject("MSXML2.XMLHTTP")
End Sub

Public Function GetJSON(ByVal URL As String) As String
    With http
        .Open "GET", URL, False
        .send
        GetJSON = StrConv(.responseBody, vbUnicode)
    End With
End Function

And the standard module as follows:

Option Explicit
Public Sub GetTickerInfo()
    Const BASE_URL As String = "https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms="
    Dim tickers(), http As clsHTTP, jsonString As String, i As Long
    tickers = Array("BTC", "ADA", "DASH", "USD")
    Set http = New clsHTTP
    jsonString = http.GetJSON(BASE_URL & Join$(tickers, ","))

    For i = LBound(tickers) To UBound(tickers)
       Debug.Print tickers(i) & ":" & Replace$(Split(Split(jsonString, Chr$(34) & tickers(i) & Chr$(34) & ":")(1), ",")(0), "}", vbNullString)
    Next
End Sub

Output in immediate window (Ctrl+G):