Author

Topic: Load All CoinMarketCap coins in Excel (non blocking lib) + Auto Refresh (Read 151 times)

newbie
Activity: 6
Merit: 0
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.

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

    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())

    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!

Jump to: