Cryptocurrency Accounting in Excel

No matter how many specialized accounting tools there may exist nowadays, Microsoft Excel is still one of the best choices for keeping track of personal finances. It is easy to start with, incredibly flexible, and scales well to most accounting tasks a layperson might encounter in practice.

If you want to include cryptocurrencies to an Excel spreadsheet of your assets, you might discover a slight inconvenience - the prices of cryptocurrencies float like crazy all the time and updating them manually is a pain.

Fortunately, Excel makes it possible to add a custom function which will conveniently query the current exchange rates for you. Let me show you how to do it:

1. Go to Developer ⇒ Visual Basic in the Ribbon menu (or simply press Alt+F11).
   

   

2. The Visual Basic for Applications window will open. Right-click the "VBAProject", corresponding to your workbook, and choose Insert ⇒ Module:
   

   

3. Paste the following code into the newly opened module window:

   Public Function HttpGet(ByVal requestUrl As String) As String
       Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
       With http
           .Open "GET", requestUrl, True
           .Send ""
           .WaitForResponse
           HttpGet = .ResponseText
       End With
   End Function
   
   Public Function GetCryptoCurrencyPrice(ByVal name As String, Optional ByVal baseCurrency As String = "EUR", Optional ByVal cacheBuster As Object) As Double
       GetCryptoCurrencyPrice = -1#
       json = HttpGet("https://api.coinmarketcap.com/v1/ticker/" & name & "/?convert=" & baseCurrency)
       
       For Each Line In Split(json)
           If found Then
               GetCryptoCurrencyPrice = CDbl(Mid(Line, 2, Len(Line) - 3))
               Exit Function
           End If
           If Line = """price_" & LCase(baseCurrency) & """:" Then
               found = True
           End If
       Next
   End Function
   

4. Close the Visual Basic for Application window (or press Alt+F11) to go back to the spreadsheet.

You are now free to use the GetCryptoCurrencyPrice function in the worksheet, just like any other Excel function. It will query the current prices from the coinmarketcap.com website. The function has three parameters:

• The name of the cryptocurrency, as it is named on Coinmarketcap (e.g. Bitcoin is called bitcoin, Steem is steem and SBD is steem-dollars).
• The name of the target currency ("EUR", "USD", "AUD", "RUB", "NOK", "SEK", etc). "BTC" also works.
• An optional pointer to a cell, changing the value of which will trigger a new query.

Here is how you would use it within a typical table:

Note that the empty cell A1 is referenced in the formula so that we could trigger a query to refresh the price by simply modifying A1 (just writing a random character and removing it, for example).

Now drag the newly created formula to fill the whole column, add the computations for the total value and voilà - your very own cryptocurrency portfolio management tool. Similar to, say, Cryptofolio, but more flexible and not limited to cryptocurrencies (on the downside - it is not web-based and won't work on your phone).