100 CryptoCurrency Price - Ticker in Excel - Using VBA

Bitcoin Price Ticker in Excel

This is really a fine code that I have come up with accidentally. I was only searching for how to add Cryptocurrency market chart in my website. Then during the Internet research/search, I came across this query. Then, why not build something for Excel on my own. Then I got a source that gives this info in the form of JSON.

Cryptocurrency Price - Ticker in Excel

Bingo. I already have code to convert JSON to Excel. Just integrated both & presenting the full code here. Copy paste this code in your Excel VBA, then you can just see bitcoin & other 100 such cryptocurrency tickers in your Excel worksheet. In case you are not familiar with VBA Macro , then just download the Excel Ticker App by Officetricks. This tool can just fetch the Price details once you click on the command button in the Sheet named "Settings".

- Downloaded Times

Note: Remember to include the steps mentioned in this JSON to Excel article. Otherwise this code will not work.

Sub Start_Bitcoin_Ticker_In_Excel()
    Dim CryptoCurrencyJSON As Object, CryptoCurrencyNode
    Dim WinHttpReq As Object, TickerJSON As String
    Dim tickerSheet As Worksheet, arr(15) As Variant
    
    'Initialize values
    Set tickerSheet = ThisWorkbook.Sheets(1)
    arr(1) = "id"
    arr(2) = "name"
    arr(3) = "symbol"
    arr(4) = "rank"
    arr(5) = "price_usd"
    arr(6) = "price_btc"
    arr(7) = "24h_volume_usd"
    arr(8) = "market_cap_usd"
    arr(9) = "available_supply"
    arr(10) = "total_supply"
    arr(11) = "max_supply"
    arr(12) = "percent_change_1h"
    arr(13) = "percent_change_24h"
    arr(14) = "percent_change_7d"
    arr(15) = "last_updated"
    
    'Query Bitcoin Market Cap Ticker Api
    Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    WinHttpReq.Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False
    WinHttpReq.send
    TickerJSON = WinHttpReq.responseText
    Set WinHttpReq = Nothing
    'TickerJSON = DU("https://api.coinmarketcap.com/v1/ticker/")
    
    'Parse JSON to Dictionary
    Set CryptoCurrencyJSON = ParseJson(TickerJSON)
    
    'Type Coinmarket Headers
    For i = 1 To 15
        tickerSheet.Cells(1, i) = arr(i)
    Next i
    
    'Loop thru Each Cryptocurrency
    trow = 2
    For Each CryptoCurrencyNode In CryptoCurrencyJSON
        For i = 1 To 15
            tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))
        Next
        trow = trow + 1
    Next
    
    'Process Completed
    MsgBox "Process Completed"
End Sub

Just run the code by pressing F5. you might get a reference missing for the datatype "Dictionary". In the Excel VBA project, go to menu, Tools -> References -> Add "Microsoft Scripting Runtime". Then this error will go off. Run again, you will see that it fetch all the Cryptocurrency market details. Where is the Tick Tock - Ticker? In case if you like to execute this code in repeat mode after every minute or some interval, then follow this code. IT explains how to execute this module at a predefined time interval.