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 65 Times
Note: Remember to include the steps mentioned in this JSON to Excel article. Otherwise this code will not work.Citcoin Price Ticker in ExcelVisual Basic123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051Sub Start_Bitcoin_Ticker_In_Excel()Dim CryptoCurrencyJSON As Object, CryptoCurrencyNodeDim WinHttpReq As Object, TickerJSON As StringDim tickerSheet As Worksheet, arr(15) As Variant'Initialize valuesSet 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 ApiSet WinHttpReq = CreateObject("MSXML2.XMLHTTP")WinHttpReq.Open "GET", "https://api.coinmarketcap.com/v1/ticker/", FalseWinHttpReq.sendTickerJSON = WinHttpReq.responseTextSet WinHttpReq = Nothing'TickerJSON = DU("https://api.coinmarketcap.com/v1/ticker/")'Parse JSON to DictionarySet CryptoCurrencyJSON = ParseJson(TickerJSON)'Type Coinmarket HeadersFor i = 1 To 15tickerSheet.Cells(1, i) = arr(i)Next i'Loop thru Each Cryptocurrencytrow = 2For Each CryptoCurrencyNode In CryptoCurrencyJSONFor i = 1 To 15tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))Nexttrow = trow + 1Next'Process CompletedMsgBox "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.
100 CryptoCurrency Price – Ticker in Excel – Using VBA