Community post

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:

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

![ribbon.png](https://steemitimages.com/DQmY8SEQixrGpxE6mRE6XiDxdr64Uq126P2zvitSti3DMZd/image.png)

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

![module.png](https://steemitimages.com/DQmVU7LrbbA6y8zhBFuqcx8eKUCVGUZXneJhyDzU5GNBrap/image.png)

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

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:

![](https://steemitimages.com/DQmR8bh7hV4hXwthb71XjnXCPTs3Mv8imUBkvi3vWNuSMuY/image.png)

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

![](https://steemitimages.com/DQmTXsnNxj2Mo4VDbceEHwUoffu3HUVniq5zzbYm95t1ZZX/image.png)

Replies (11)

Of course you can also add logic to recalculate the sheet automatically at regular intervals. One way to do this could be to add a new module with, for example, the following code:

Private Running As Boolean

Public Sub StartTimer()
    Running = True
    Call TimerEvent
End Sub

Public Sub StopTimer()
    Running = False
End Sub

Public Sub TimerEvent()
    Workbooks("Book1").Worksheets(1).Calculate
    If Running Then
        Application.OnTime Now + TimeValue("00:00:10"), "TimerEvent"
    End If
End Sub

(Note that you'd need to replace "Book1" with the appropriate name, or simply use ActiveWorkbook in place of Workbooks("Book1"), although then the timer will be recalculating whatever workbook you have open at the moment).

You can start the timer by running the StartTimer macro (using the "Run" button) and stop it using StopTimer.

It might make sense to also add something like this to the code of ThisWorkbook module, so that the timer will start and stop automatically:

Private Sub Workbook_Open()
    Call StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

Yes, you can do the same in OpenOffice as well.

Go to Tools ⇒ Macros ⇒ Organize Macros ⇒ OpenOffice Basic..., create a new module under your current document, and paste the following, slightly modified, version of the code in the newly opened editor 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 10
        HttpGet = .ResponseText
    End With
End Function

Public Function GetCryptoCurrencyPrice(ByVal name As String, ByVal baseCurrency As String, Optional cacheBuster As Variant) 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