Develop Customized Cryptocurrency Portfolio Trackers with Excel VBA

Step 1: Set up your Excel spreadsheet

1. Prepare the Excel Worksheet:
   • Open a new Excel workbook.
   • In column A, list the names or symbols of the cryptocurrencies you want to track (e.g., Bitcoin, Ethereum, etc.).
   • In column B, enter the amount of each cryptocurrency you hold.
   • In column C, the current market price for each cryptocurrency will be fetched via VBA.
   • In column D, calculate the value of your holdings (Amount * Price).

Example structure:

Step 2: Accessing cryptocurrency prices

You will use an external API to fetch the latest prices for cryptocurrencies. One of the most commonly used APIs is CoinGecko or CoinMarketCap. In this example, we’ll use the CoinGecko API because it is free and easy to use.

API Endpoint Example for CoinGecko:

https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum&vs_currencies=usd

This will return the current price of Bitcoin and Ethereum in USD.

Step 3: Implementing VBA Code

1. Enable Developer Tab:
   • Go to the « Developer » tab in Excel and click « Visual Basic » to open the VBA editor.
2. Add a Module:
   • In the VBA editor, go to Insert > Module to create a new module.
3. VBA Code to Fetch Cryptocurrency Prices:

Sub GetCryptoPrices()
    Dim http As Object
    Dim JSON As Object
    Dim url As String
    Dim cryptoName As String
    Dim cell As Range
    Dim cryptoData As Object
    Dim price As Double
    Dim portfolioValue As Double
    ' Create HTTP object
    Set http = CreateObject("MSXML2.XMLHTTP"
    ' URL to get cryptocurrency data (CoinGecko API)
    url = "https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum&vs_currencies=usd"
    ' Open HTTP request
    http.Open "GET", url, False
    http.Send
    ' Parse the JSON response
    Set JSON = JsonConverter.ParseJson(http.responseText)
    ' Loop through the list of cryptocurrencies
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A2:A10") ' Adjust range as needed
        cryptoName = LCase(cell.Value) ' Get the cryptocurrency name (in lowercase)
        ' Check if the API contains the data for the cryptocurrency
        If Not JSON.Exists(cryptoName) Then
            MsgBox "Cryptocurrency " & cell.Value & " not found!", vbExclamation
        Else
            ' Get the price from the JSON response
            price = JSON(cryptoName)("usd")
                 ' Update the price in column C
            cell.Offset(0, 2).Value = price   
            ' Calculate and update the portfolio value in column D
            portfolioValue = cell.Offset(0, 1).Value * price
            cell.Offset(0, 3).Value = portfolioValue
        End If
    Next cell
End Sub

1. JsonConverter Module: You need to download and add a JSON parser to your VBA project. You can get the VBA-JSON parser from here: VBA-JSON GitHub Repository.
   • Download the JsonConverter.bas file from the repository and import it into your project via File > Import File.
2. Explanation of the Code:
   • The GetCryptoPrices subroutine makes a GET request to the CoinGecko API to retrieve cryptocurrency prices in USD.
   • It loops through each cryptocurrency in column A and updates column C with the latest price.
   • It calculates the total value of your portfolio by multiplying the amount of cryptocurrency (column B) by the price (column C) and outputs the result in column D.

Step 4: Run the Code

1. Running the VBA Code:
   • Close the VBA editor and return to Excel.
   • Press Alt + F8, select the GetCryptoPrices macro, and click Run.
2. Output:
   • The code will fill in column C with the current prices of your cryptocurrencies and update the portfolio value in column D.

Summary:

This tracker fetches live cryptocurrency prices from CoinGecko and calculates the total value of your holdings based on your portfolio data. You can customize this by adding more cryptos, using different APIs, or adding features like historical price tracking.