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