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):
