Hello,
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.
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
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:
GetCurrencyFor("bitcoin", 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!