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

Title Image credits and copyrights by Pawel Janiak onUnsplashhttps://unsplash.com/@pawelj

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

A few days ago I was asked to visualizethe value of bitcoinswithin aMicrosoft Excel spreadsheet.

Today many people are talking aboutbitcoinconsidering that thiscryptocurrencyhas 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 afree servicethat provides anAPIthat, through simple parameters, returns thevalue 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 aroutine written in Visual Basic For Application (VBA) within a Microsoft Excel spreadsheet. Once we get the result we will display it ina simple Dashboard made with the excellent Microsoft PowerBIalso available forfree.

Requirements

We need to have our trustyMicrosoft Excelavailable in a fairly recent version (2010 onwards). For the examples I will use 2016. The important thing is that theVisual Basic Editor (VBE) development environment is availablewith which we are going to develop our routines inVisual 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 consultmy second bookor to rely on one of the many guides available online.

If you want to try your hand at creating theDashboard, you need theMicrosoft PowerBIsoftware in theDesktop 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 platformavailable in various versions. It providesinteractive visualization tools for business intelligencepurposes through a simple andintuitive visual interfacewith which you can createDashboardsandReportsto better present your data. The version we are interested in in this article is theDesktop versionwhichis available for free after registering for an account.

If you are interested in learning more about this topic I invite you to visit the pagehttps://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 finda 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 theBlockchain.comwebsite athttps://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 tocryptocurrencies,blockchain,wallets,exchanges,APIsand much more. We, in this article, are only interested in their API for providing Bitcoin pricing.

I remind you that there are so manyCryptocurrenciesandBitcoinis just one of them and definitely the most famous. Just to name a few there areEthereum,Litecoin,XRP,Cardanoand 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 inBitcoin(whose financial symbol isBTC) of 1dollar(USD).

ThisAPIis composed as follows:

• https://blockchain.info/is the base address (endpoint)
• tobtcis the function that converts the parameters passed to it into BTC (Bitcoin)
• ?is used to pass parameters
• currency=USDindicates the currency to convert from. In this case Dollar (USD) or Euro (EUR) or other.
• &is used to pass a second parameter
• value=1indicates 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 openMicrosoft Exceland create anew 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 extensionbut we have to use an extension that supports the presence of VBA source code. So, in this case we choose theextension .xlsmwhich is suitable for the purpose. For the sake of argument, the.xlsb extensionwould also be fine but we prefer to use the former.

So our file will beBitcoinPrice.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 isUSD2BTC, 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 theAPIwhich is made through the creation of an object of typeMSXML2.XMLHTTPwhich deals with the interface via http withAPIandWeb Services.

Through the command “.Open” we open a communication channel for aGETrequest 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 theReplaceinstruction. 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 theCVarfunction.

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 nameEUR2BTCand 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 cellD5to 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 cellD5the correct formula is:

=1/USD2BTC()

Let’s do the same thing with cellE5regarding 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 ourDashboardcreated withMicrosoft PowerBIto 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 examplefile

If you want the getBitcoinPrice.xlsmfile containing the example and source codeconnect with me on Linkedinand 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

Read more insights …