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 529 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.
2 thoughts on “100 CryptoCurrency Price – Ticker in Excel – Using VBA”
vеrу nісе wrіtе uр. Fоllоwіng this ѕіmрlе but dеtаіlеd guіdе іѕ a wіѕе ѕtер
tоwаrdѕ ѕtаrtіng a bіtсоіn аnd оthеr ѕuсh 100 сrурtосurrеnсу tісkеrѕ іn Exсеl
wіthоut undеrgоіng muсh ѕtrеѕѕ. Thаnkѕ fоr thе іnfо аnd kеер uр thіѕ gооd wоrk.
I’ve seen some other pieces of work that automatically refresh, is there any way of modifying this so it refreshes every 5 minutes? I want to throw this on one of my spare computer screens and just have it always running throughout my day.