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:
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
Provide the URL of the source page
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:
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.
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:
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
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:
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
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):
- Quick and easy to setup
- Can be refreshed reliably without any macros
- Works for XML, JSON and simple HTML data sources
- 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:
- 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)
- 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