I've created an Excel library that loads all Crypto Currencies from CoinMarketCap into an excel sheet without the user Interface being blocked (async http calls).
all information, code and the Excel file can be found at https://github.com/VinzzB/Async_CoinMarketCap_Excel_Api
a short overview:
How Does It Work
This library loads 1500 coins (by default) from CoinMarketCap.com API (CMC-API) in one request and without blocking the user interface. The amount of coins and requests can be adjusted in the Workbook_Open() method by calling the Setup() method in the Reader class.
Prices are shown in USD by default. You can choose to convert prices to another currency in the Workbook_Open() method by calling the Setup() method in the Reader class. all supported currencies can be found at https://coinmarketcap.com/api/
The refresh timer is also adjustable through the StartTimer() method.
Code:
Private Sub Workbook_Open()
'load all in one request. (could fail if api is under heavy load) and load prices in EURO.
reader.Setup 1500, 0, "EUR"
'Load 1500 coins in three requests
'reader.Setup 500,3, "USD"
'Reload data every 2 minutes.
reader.StartTimer "ReadApi", "00:02:00"
End Sub
'load all in one request. (could fail if api is under heavy load) and load prices in EURO.
reader.Setup 1500, 0, "EUR"
'Load 1500 coins in three requests
'reader.Setup 500,3, "USD"
'Reload data every 2 minutes.
reader.StartTimer "ReadApi", "00:02:00"
End Sub
These function are available in an excel worksheet:
GetCurrencyFor(coinName As String, dummyDateTime As Date) As Double
GetCurrencyForTicker(coinTicker As String, dummyDateTime As Date) As Double
GetCoinOnRank(Rank As Integer, getFieldName As String, dummyDateTime As Date)
GetCoinForName(coinName As String, getFieldName As String, dummyDateTime As Date) as Variant
GetCoinForTicker(coinTicker As String, getFieldName As String, dummyDateTime As Date) as Variant
LastUpdate(dummyDateNow As Date) As Date
NextUpdate(dummyDateNow As Date) As Date
All functions uses a 'dummyDateNow' parameter. Use the NOW() function, this will force Excel to update the cell on every refresh. examples for an excel cell formula:
Code:
GetCurrencyFor("bitcoin", now())
GetCurrencyForTicker("btc", now())
GetCoinForTicker("btc", "24hchange", now())
GetCurrencyForTicker("btc", now())
GetCoinForTicker("btc", "24hchange", now())
GetCoinOnRank, GetCoinForName, and GetCoinForTicker uses the 'getFieldName' argument to get specific data from a coin. These are all valid fieldnames:
id
circulatingsupply or availablesupply
totalsupply
marketcap
name
percentchange1h or 1hchange or change1h
percentchange24h or 24hchange or change24h
percentchange7d or 7dchange or change7d
price
pricebtc or btcprice
rank
ticker
volume24 or volume24h or volume or 24hvolume
You can also attach the ReadApi() method to a custom ribbon or quick toolbar button which allows you to load (or refresh) the CMC data manually. A button is provided in the excel workbook.
To Start, Download the cmc.xlsm file from here. Some examples are included in the workbook. The first 200 coins ranked on CMC are also listed in a separate worksheet called 'CMC'.
Enjoy!