Recently the price of Bitcoin has skyrocketed again and many people keep refreshing their phones and browsers to be on top of current price fluctuations. However, why not use Excel – and for that purpose, how to get Bitcoin in Excel using a formula? There is no ready formula in Excel but it turns out it is not so hard. With only a little mix of Power Query or VBA/Web Scraping you can easily extract the current Bitcoin price and reuse it in your Excel spreadsheet calculations. You can use the methods shown below easily to track the price of other cryptocurrencies – provided you can found some online API or Web Page.

In the first part of this article, for beginners, I will show you how to use the Power Query – Data from Web feature in Excel (only newer versions of Excel) to extract the price of Bitcoin into your spreadsheet. Power Query is useful when you are processing well structured data such as XML, JSON or a single Web Page HMTL.
In the second part, for more advanced users, I will show you how you can use VBA (Visual Basic for Applications) and a simple Web Scraping macro code to extract the information from the API or Web page. In some more complex cases when trying to extract data from Web Pages it might be too hard to understand how to get a particular part of the page albeit we see where the data is located on the source code of the web page. That is when VBA with some Regular Expressions will be better suited.

Bitcoin Price with Power Query

The price of Bitcoin can be taken from many online sources, however, it is easiest to use Power Query for well structured source data. ThankfullyCoindesk being one of the more regarded sources posts a free API which you can use to get their noted Bitcoin price. The API result is a well structured JSON response:

CoinDesk current price information is location under the link below:

https://api.coindesk.com/v1/bpi/currentprice.json

The API returns a simple JSON response like this:

Step by Step – Extracting the Bitcoin Price using Power Query

Follow the below steps to run Power Query Wizard to setup a query in Excel and to download Bitcoin Price data into your

Open Data ribbon and click From Web

Open your Data and click From Web. This will open in then next step a window asking you for the URL details of the API or Web Page where the data is being provided.

Provide the URL of the source page

As we are using the CoinDesk URL provided above let us put this in the Wizard window, next click OK.

Navigate the JSON structure to get the Bitcoin price

Now Power Query has processed the API response and identified the data structure as JSON. Instead of showing us the source code Power Query shows us a simple table and asks us to navigate it to find the information we need. First click on Record near the bpi column name like below.

This will extract the bpi array and you will see the underlying array. Now you must decide if you want to get the Bitcoin price in USD, GBP or EUR. Select the Record button next to the name of the desired currency.

Finally you will see a similar table below which contains the price we were looking for. To transfer the table to Excel click Into Table.

This is the result:

Refreshing the new Query

You can refresh your query by right-clicking the table and hitting Refresh. If you want, however, the Query to automatically refresh periodically do the following. Go to Query ribbon and hit Properties.

Next mark the Refresh every check box and provide the refresh rate in minutes below. Unfortunately the Query From Web feature does not allow you to refresh data more often which can be a problem if you want to track the live price of the cryptocurrency. If you want to refresh more often then proceed below to see how you can use VBA/Web Scraping to extract the Bitcoin price more often.

Bitcoin Price with VBA / Web Scraping

Bitcoin Price Source data

Although there are many available sources I will use the simplest web source which is the Google search URL. When you type a search query in Google notice that the URL will change. If you type bitcoin price usd notice that your browser URL will change to something like:

https://www.google.com/search?q=bitcoin+price+usd

The part of this string following https://www.google.com/search i.e. this bit ?q=bitcoin+price+usd are called HTML GET parameters. These parameters are variables that are sent to Google servers telling them to send the search result for these keywords: bitcoin, price and usd.

This is the web page results for this query above:

Web Scraping Bitcoin Price using VBA

To get the Bitcoin price from this web page we will need to first obtain the HTML source code of the server response. To do this we will need to first open the Excel Visual Basic project VBE. Next we will create a simple VBA Function and use the XMLHTTP object to get the server page response:

Public Function BITCOINPRICE()
    'Get HTML of Google page search results
    Dim XMLHTTP As Object, HTMLresponse As String
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 
    Call XMLHTTP.Open("GET", "https://www.google.com/search?q=bitcoin+price+usd", False)
    XMLHTTP.send

    HTMLresponse = XMLHTTP.ResponseText

    '...extract the bitcoin price...
End Function

As you might notice HTMLresponse gets us the underlying sourcecode for the Google page. What we are interest in however is only tiny piece of information below:

Now what is left is to somehow extract this value from the entire HTML DOM of the page source code.

Extracting Bitcoin Price in Excel

In above section I explained how we can get the result of the Google Bitcoin price query result page source code. The document HTML DOM is long and we need to extract just the tiny bit of information.
To be more precise this is the string we are really interested in as it provides the most accurate value of the current Bitcoin price:

data-value="57091.600000000006"

In Excel most of you are probably used to using MID, LEFT, MID or FIND functions. This will not be practical. In VBA, however, thankfully we can also use so called VBA Regular Expressions i.e. a pattern matching language. So let us use the VBScript Regex object to extract this:

Dim Regex As Object, matches as Variant
Set Regex = CreateObject("VBScript.RegExp")
Regex.Pattern = "data-value=""(.+?)"""
Regex.Global = False
Set matches = Regex.Execute(HTMLresponse)
BITCOINPRICE = matches(matchIndex).Submatches(subMatchIndex)

Excel Bitcoin Price function

The full Excel Bitcoin function now should look like this:

Public Function BITCOINPRICE() as Double
    'Get HTML of Google page search results
    Dim XMLHTTP As Object, HTMLresponse As String
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    Call XMLHTTP.Open("GET", "https://www.google.com/search?q=bitcoin+price+usd", False)
    XMLHTTP.send
    HTMLresponse = XMLHTTP.ResponseText
    
    'Extract Bitcoin Price
    Dim Regex As Object
    Dim matches As Variant
    Set Regex = CreateObject("VBScript.RegExp")
    With Regex
        .Pattern = "data-value=""(.+?)"""
        .Global = False
    End With
    Set matches = Regex.Execute(HTMLresponse)
    BITCOINPRICE = matches(matchIndex).Submatches(subMatchIndex)
End Function

The Function is Public for a reason as by adding this statement it can be considered an UDF (User Defined Function) and hence accessible as any Excel function like so:

Download Example

You can download the example with the full code using below link:
Bitcoin Excel Example.zip

Checkout my Web Scraping Toolkit to scrape data to Excel even more easily. Scraping the Bitcoin Price in Excel can be done in just 2 lines:

Public Function BITCOIN PRICE() As Double
    Dim htmlDom As String
    htmlDom = GetHTTPResult("https://www.google.com/search?q=bitcoin+price+usd")
    GetBitcoinPrice = Regex.RegexExecuteGet(htmlDom, "data-value=""(.+?)""", 0, 0)
End Function

Get Bitcoin Price in Excel from CoinDesk

The benefit of the code above is its versatility as it can be used to obtain other interesting data from Google search results. The price of Bitcoin however is different across different sources with Coindesk being one of the more regarded sources. What is more they also post a free API which you can use to get their noted Bitcoin price. See below code you can use to replace the one above to get the price from their website (and avoid getting banned).

The current price information is location under the link below:

https://api.coindesk.com/v1/bpi/currentprice.json

The API returns a simple JSON response like this:

In the example just replace these 2 items to get the Bitcoin Price in Excel from CoinDesk instead of Google:

'Instead of this
https://www.google.com/search?q=bitcoin+price+usd
'Use this
https://api.coindesk.com/v1/bpi/currentprice.json

'Instead of this
"data-value=""(.+?)"""
'Use this
"USD.+?""rate"":""(.+?)"""

The above switch will get you the price in USD. But you can change the Regular Expression to get it in GBP or EUR.

Live Bitcoin Price in Excel

Bitcoin is volatile and you might want to keep close track of it’s price changes. As a bonus I want to show how you can use the VBA Application OnTime procedure to auto refresh your Bitcoin Price in Excel.

First we need to add from the Developer ribbon tab a Button Form Control. To do this go to Developer->Controls->Insert and select Button from the Form Control section. Name it to Button 1.

Next what we will do is to create the code function that will do the recalculation of our Excel cell.

Dim onFlag As Boolean 'IMPORTANT: PUT THIS AT THE BEGINNING OF THE MODULE!
'...
Sub RefreshOnOff()
    If onFlag = Empty Then
        onFlag = True
    Else
        onFlag = Not (onFlag)
    End If
    If onFlag Then
        RefreshBitcoinCell
        Worksheets("Sheet1").Buttons("Button 1").Text = "Turn Off"
    Else
        Worksheets("Sheet1").Buttons("Button 1").Text = "Turn On"
    End If
End Sub
Sub RefreshBitcoinCell()
    If onFlag Then
        Range("Sheet1!C2:C3").Calculate
        Dim timeout As Date
        timeout = Now + TimeValue("00:01:00")
        Application.OnTime timeout, "RefreshBitcoinCell"
    End If
End Sub

To break it down:

• RefreshOnOff – basically just sets our global onFlag variable to True or False. It also changes the Button text (see image below) to tell us if the refresh is On or Off. The Button name is assumed to be Button 1
• RefreshBitcoinCell – is again a simple procedure that works recursively i.e. it refreshes the cells C2:C3 which contain our Bitcoin function as well as a simple timestamp (=NOW() excel function). The timeout is set to 1 minute (00:01:00) but you can adjust as needed. Once the refresh is done the function calls the Application.OnTime procedure to run itself

Below you can see how the end result looks like.

Hope you have fun! I recommend not setting it to anything lower than a minute as Google might ban you. However, experiment at your own risk!

Pros and Cons – Power Query vs VBA

I hope you found this useful and interesting and will help you keep get Bitcoin Price in Excel. Query From Web as well as VBA/Web Scraping methods can easily be reused to provide you price data of other cryptocurrencies and data. Provide my perspective here are the Pros and Cons of both:

Power Query (From Web):
Pros:

• Quick and easy to setup
• Can be refreshed reliably without any macros
• Works for XML, JSON and simple HTML data sources

Cons:

• Does not support complex Web Pages (as you cannot specify/customizable XPATH)
• Does not allow you to refresh more often than 1 minute
• Does not support proxy

VBA / Web Scraping:
Pros:

• Allows very complex data extraction from API/Web Pages
• Can be refreshed even every second or less
• Allows proxy setup – so you can even switch proxies every query (see my Web Scraping Toolkit)

Cons:

• Is more complex and requires knowledge for Regular Expressions (or XML processing, there is no JSON processing object in VBA)
• Refreshing data via macro prevents redo/undo in Excel