Track your crypto portfolio in Excel using VBA Scripting and CoinGecko price feed

Lately, I've been playing with ways that I can track the yield of my crypto assets and I've tried to use different portfolio trackers. But one thing that I've observed is that if I want to have the information that I want I would need to subscribe to a paid service. Thus I've searched how I could grab data myself and track my portfolio in Excel using the CoinGecko API price feed. Even if it is a little bit more technical, at least I can get all the data that I want and track different dimensions in which I am interested.

In order to get the crypto price feed from CoinGecko into Excel, there are some addons and steps that need to be performed. Here is what I did in order to achieve this and be able to refresh the price data in Excel by clicking a button.

Create a new blank Excel document and save it as 'Excel Macro-Enabled Workbook. This will ensure that we will be able to use some macros and addons in order to run VBA Script that will connect to Coingecko API and refresh the price of specific given crypto assets.

Enable 'Microsoft Scripting Runtime'. You can do this from the Microsoft Visual Basic For Applications Editor (Press Alt+F11 in Excel), and Go To Tools -> References, and select the 'Microsoft Scripting Runtime' checkbox from Available References.

Add JSON to Excel addon that will be used to fetch and parse data from CoinGecko API. You can download the latest version of VBA Excel JSON add-on directly from VBA JSON GitHub. To import it you simply need to enter in Excel the Microsoft Visual Basic For Applications Editor -> File -> Import -> JsonConverter.bas.

Insert VBA Script code that has 2 functions: one to Get Crypto Prices from CoinGecko and one to Extract from the JSON return message the needed data and put it on the Excel sheet. In order to run the script you can do that by pushing the Run button from within the Microsoft Visual Basic For Applications Editor or from within the Excel -> Sheet by going to View -> Macros -> View Macros -> Sheet1.GetCryptoPrices and hit the Run button.

VBA Scripting source code:

Sub GetCryptoPrices()
    Dim json As Object, cryptoList As String, cryptoPrices As Object, i As Integer
    cryptoList = "hive,hive_dollar"
    Set json = GetJson("https://api.coingecko.com/api/v3/simple/price?ids=" & cryptoList & "&vs_currencies=usd")
    Set cryptoPrices = json
    i = 2
    For Each crypto In cryptoPrices
        Worksheets("Sheet1").Cells(i, 1) = crypto
        Worksheets("Sheet1").Cells(i, 2) = cryptoPrices(crypto)("usd")
        i = i + 1
    Next crypto
End Sub

Function GetJson(url As String) As Object
    Dim req As Object
    Set req = CreateObject("MSXML2.XMLHTTP")
    req.Open "GET", url, False
    req.Send
    Set GetJson = JsonConverter.ParseJson(req.responseText)
End Function

And by following the above steps the magic happens and you'll be able to get fresh data from CoinGecko on the crypto assets prices that you've chosen. You can extend the list of assets by adding them to the cryptoList = "hive,hive_dollar" from the VBA Script. That is all from me on this subject and I hope this will help you to track easily your portfolio assets. And this can be extended in the same way with multiple assets and multiple APIs from where one would want to get the price feed.
 

Posted Using LeoFinance ^Beta