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”.

Download 100 Cryptocurrency Price List in Excel – Downloaded 622 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.