How much is a Bitcoin price?

Create a VBA routine in Microsoft Excel to read the price of a Bitcoin via an API and display it on a Dashboard in Microsoft PowerBI

Reading Time: 10 minutes

Post published on 23/01/2021 by Fabrizio Cesarini on site https://www.fabriziocesarini.com and released with licenza CC BY-NC-ND 3.0 IT (Creative Common – Attribuzione – Non commerciale – Non opere derivate 3.0 Italia)

Title Image credits and copyrights by Pawel Janiak on Unsplash https://unsplash.com/@pawelj

Article Images credits and copyrights by Fabrizio Cesarini https://www.fabriziocesarini.com

A few days ago I was asked to visualize the value of bitcoins within a Microsoft Excel spreadsheet.

Today many people are talking about bitcoin considering that this cryptocurrency has recently made headlines following some events that have brought its value to levels never reached before.

It is undeniable that this sudden rise is arousing a lot of interest from the media and people who see, in this digital currency, a potential form of investment and / or speculation but especially because it offers an interesting alternative to the current market. Surely it is a phenomenon that deserves to be observed and maybe even understood.

Warning

I’m not going to explain, in this article, what a Bitcoin is and especially I’m not going to address the issue of whether or not it’s appropriate to invest in it. For the moment I can only say that it is a very complex topic and requires a lot of training before you can venture into any form of investment or speculation. Above all, it must be taken into account that it is a very volatile instrument whose trend depends on an incredibly high number of factors which makes the possibility of making any prediction on the future trend even more complex. This difficulty is given by the fact that it is subject to frequent and sudden changes of direction, thus creating potential risks that are very high in case of a wrong prediction and in case the appropriate measures are not taken.

Objectives

What has just been said, however, does not concern the fact that instead it is useful to know the value and perhaps use it as a comparison or, much more simply, as a simple monitoring of the phenomenon.

To do this there are an infinite number of solutions more or less complex. My primary goal is to do this in the simplest and most useful way possible. Thinking that this may be useful to many I decided to make it public and share the result with my readers. I hope you will appreciate it!

The purpose of this article is purely didactic although it can really be used in your applications.

Work scheme

We need to perform the following tasks:

• Find a service (possibly free) that provides the value of a Bitcoin.
• Create a function that reads this value and puts it inside a spreadsheet
• Convert the value in the currency that interests us (Dollars, Euros or other)
• Display the result in a Dashboard

We must then look for a free service that provides an API that, through simple parameters, returns the value of a Bitcoin. This value we must then find a way to read it (Request and obtain it) and to do this we will use a routine written in Visual Basic For Application (VBA) within a Microsoft Excel spreadsheet. Once we get the result we will display it in a simple Dashboard made with the excellent Microsoft PowerBI also available for free.

Requirements

We need to have our trusty Microsoft Excel available in a fairly recent version (2010 onwards). For the examples I will use 2016. The important thing is that the Visual Basic Editor (VBE) development environment is available with which we are going to develop our routines in Visual Basic for Application (VBA). In case it is not available you must provide to activate it. For those who need to know the activation procedure of the development environment I suggest you to consult my second book or to rely on one of the many guides available online.

If you want to try your hand at creating the Dashboard, you need the Microsoft PowerBI software in the Desktop version. You can download it directly from this address

https://www.microsoft.com/en-us/download/details.aspx?id=58494

Microsoft PowerBI is a business analysis platform available in various versions. It provides interactive visualization tools for business intelligence purposes through a simple and intuitive visual interface with which you can create Dashboards and Reports to better present your data. The version we are interested in in this article is the Desktop version which is available for free after registering for an account.

If you are interested in learning more about this topic I invite you to visit the page https://powerbi.microsoft.com/en-us/ where you will find a lot of material.

Let’s get to work

The first thing we need to do is find a web service provided as an API that can return the current value of Bitcoin. Usually these services are paid or free but with some limitations. Almost all of them require a specific identification key for each user.

Scouting the net I found a service that doesn’t require any of that. We can make a direct call to their API without any identification key with the only flaw that the price is deferred and therefore not in real time. This means that the price that will be returned will be several minutes behind the current price. For normal purposes this limitation usually doesn’t represent a problem but above all it is fine for our educational purposes. If you need to get the price in real time, you can switch to a service that does it while maintaining the same code base seen in this article.

Let’s go to the Blockchain.com website at https://www.blockchain.com/

NOTE: I would like to emphasize that I have no connection with this site nor any business relationship. I chose this site solely and exclusively because it provides the service I needed. I could have used any other site instead of this one.

This site provides users with a large amount of services related to cryptocurrencies, blockchain, wallets, exchanges, APIs and much more. We, in this article, are only interested in their API for providing Bitcoin pricing.

I remind you that there are so many Cryptocurrencies and Bitcoin is just one of them and definitely the most famous. Just to name a few there are Ethereum, Litecoin, XRP, Cardano and many others. Everything we will see in this article is also applicable to other cryptocurrencies by making some simple modifications.

The API

Doing a search in their rich documentation you will find the API we are interested in which is as follows:

https://blockchain.info/tobtc?currency=USD&value=1

Typing this address in our browser will result in a number that indicates the value in Bitcoin (whose financial symbol is BTC) of 1 dollar (USD).

This API is composed as follows:

• https://blockchain.info/ is the base address (endpoint)
• tobtc is the function that converts the parameters passed to it into BTC (Bitcoin)
• ? is used to pass parameters
• currency=USD indicates the currency to convert from. In this case Dollar (USD) or Euro (EUR) or other.
• & is used to pass a second parameter
• value=1 indicates how much currency we want to convert. In this case 1 dollar

So we asked to tell us how many bitcoins 1 dollar corresponds to.

We get as result (in this case) 0.00003072

So 1 dollar corresponds to 0.00003072 Bitcoin. We can also write 1 USD = 0.00003072 BTC

Obviously the value that will be returned will be almost certainly different from this

If we wanted to know the same information but relatively to the euro would be enough to replace the symbol USD with EUR to the parameter “currency” and so on with other currencies

https://blockchain.info/tobtc?currency=EUR&value=1

If we wanted to know how much 500 dollars in bitcoin correspond to, we would just change the parameter “value” writing 500 instead of 1

https://blockchain.info/tobtc?currency=USD&value=500

You can do all the tests you want. We have understood the working mechanism of this API.

In figure 4 you can see an example of the result on Google Chrome browser (any browser is fine)

Now we need to figure out how to get this value inside our Excel sheet.

The Excel sheet

Let’s open Microsoft Excel and create a new empty file.

Before doing anything we save the file to make our life easier for the next steps. As a file name we choose “BitcoinPrice” or any other name you want, but for the extension we have to make a small variation. Since we have to write source code in the file we can’t store it in the classic Excel format or with .xlsx extension but we have to use an extension that supports the presence of VBA source code. So, in this case we choose the extension .xlsm which is suitable for the purpose. For the sake of argument, the .xlsb extension would also be fine but we prefer to use the former.

So our file will be BitcoinPrice.xlsm

Now we go to the “Developer” panel and click on the first icon on the left “Visual Basic” as shown in figure 5.

In the section “Project – VBAProject” where is shown the structure of the current project and any open ones click with the right mouse button over the item “VBAProject (BitcoinPrice.xlsm)” which is our file / project. In the menu that will appear we select the item “Insert” which in turn will bring up a new submenu. In this sub-menu we select the item “Module” so as to create a new source code module that will host the routines that we are going to insert.

Once this is done we will be presented with a panel as shown in figure 7.

Now we need to create a routine that makes the call to the API we saw earlier and returns the result.

Let’s go ahead and write the following code:

Public Function USD2BTC() As Variant
    Dim V As Variant
    Dim S As String
    
    V = 0
    S = ""
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://blockchain.info/tobtc?currency=USD&value=1", False
        .Send
        S = .responseText
        S = Replace(S, ".", ",")
        V = CVar(S)
    End With
    
    USD2BTC = V

End Function

The function, whose name is USD2BTC, is responsible for asking the service via API the value in Bitcoin of 1 Dollar and return it in the form of a numerical value of type Variant.

A brief and useful explanation of the function.

Two support variables V of type Variant and S of type String are declared to contain the temporary values of the function.

The heart of the routine is the request to the API which is made through the creation of an object of type MSXML2.XMLHTTP which deals with the interface via http with API and Web Services.

Through the command “.Open” we open a communication channel for a GET request to the API address passing it the parameters we have seen previously.

The command “.Send” makes the request and the result that is returned is captured through the property “.responseText” assigning it to the variable S of type string since the result is a text.

The result is provided as a decimal using the point (.) as a symbol of division of the decimal as is customary in the American format. Since I am interested in having it in European format I need to convert the point (.) in a comma (,) and I do it with the Replace instruction. This is because in order to convert it to a number I need to use the correct symbol for decimals. If you don’t have this need just delete this line.

Finally, convert the string containing the number into a number in variant format using the CVar function.

The result will be returned as the result of the function.

That’s all!

To get the same result but in Euro (EUR) we will create a similar routine giving it the name EUR2BTC and replacing USD with EUR in the API call.

I do not report here the routine for obvious reasons of synthesis but you can find it in the attached file and you can see it in Figure 8.

Let’s use the created functions

These two routines are now present within our spreadsheet and we can use them anywhere.

Let’s go back into our spreadsheet and within the grid we are going to insert the functions. We can do this in any cell we want. Let’s choose cell D5 to enter the value of Bitcoin in Dollars and cell E5 for the value of Bitcoin in Euros. You could choose any other cell but we will refer to these two.

Inside cell D5 we insert the function =USD2BTC()

This will report the value exactly as we download it from the web and not in dollars. To do this we just need to make a simple modification to the function. If we divide 1 by the returned value we get the equivalent in dollars of Bitcoin. So inside cell D5 the correct formula is:

=1/USD2BTC()

Let’s do the same thing with cell E5 regarding the euro. The formula we will enter will be as follows:

=1/EUR2BTC()

Applying the formatting of the cells with the format of the currency with Dollars and Euros we will obtain the result shown in figure 9.

We got exactly what we wanted. Now our spreadsheet contains the value of Bitcoin in Euros and in Dollars.

Now we need to enter these two values into our Dashboard created with Microsoft PowerBI to get the result visible in Figure 10.

Conclusion

For the moment we stop here. In the next days I’ll publish a new article where I’ll explain step by step how to realize the dashboard you saw in fig. 10 using Microsoft PowerBI.

We have seen how with few instructions it is possible to obtain a very useful result. There are several improvements that we could make to the code.

• Possible errors have not been considered. They should be handled and taken into consideration.
• No update logic has been provided to refresh the values.
• The routines could be optimized by passing them as values the currency and the value of the currency to be converted.

I didn’t do these things to keep the code as simple as possible staying in the educational purposes. If you are interested in using this code in real environment it would be appropriate to take into account these improvements.

How to get the example file

If you want the get BitcoinPrice.xlsm file containing the example and source code connect with me on Linkedin and send me a request via a message. I will be happy to share it with you

Let me know if you enjoyed this article and, more importantly, if you are interested in the second part of this article. Stay in touch