Cryptocurrency Accounting in ExcelsteemCreated with Sketch.

in #howto7 years ago (edited)

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

    ribbon.png

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

    module.png

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

Sort:  

This is a really useful!

I am pleased to announce that your post has been featured in Max Curation Edition No.6 published on Steemit.

You can take a look at it HERE.

Congratulations! Upvoted and resteemed.

Thanks for the appreciation!

You're welcome!

This is a very valuable tutorial to anyone with a few types of cryptocurrency. I'm resteeming so my husband can read - he's our account manager and definitely better at Excel than I am, though I'm very intrigued to discover you can add commands to Excel this way.

Amazing post, thanks a ton.

Nice article, I've posted a link to it on https://Fresherblock.com (let me know if you'd like an invite)

Is it possible to update automatically at certain intervals?
For example, how do I apply a module like OnTime Now + TimeValue ("00:00:30"), "AddCell"?

Loading...

Nice post man! do you think I can apply this to openoffice? I dont know too much about visualbasic :S Thanks a lot! very usefull post!!

Loading...

Useful and instructive, thanks!