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 utilitarian 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 childlike Web Scraping macro code to extract the information from the API or Web foliate. In some more complex cases when trying to extract data from Web Pages it might be besides 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 world wide web page. That is when VBA with some regular Expressions will be adept suited .
Bitcoin Price with Power Query
The monetary value of Bitcoin can be taken from many on-line sources, however, it is easy to use Power Query for well structured source data. thankfully Coindesk being one of the more regard sources posts a free API which you can use to get their note Bitcoin price. The API consequence is a well integrated JSON reception :
CoinDesk current price data is location under the link below :
https://api.coindesk.com/v1/bpi/currentprice.json
The API returns a simpleton JSON reception like this :
Step by Step – Extracting the Bitcoin Price using Power Query
Follow the downstairs steps to run Power Query Wizard to setup a question 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 following gradation 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 lashkar-e-taiba us put this in the Wizard window, following snap OK.
Navigate the JSON structure to get the Bitcoin price
now Power Query has processed the API reply and identified the data social organization as JSON. rather of showing us the reference code Power Query shows us a elementary table and asks us to navigate it to find the information we need. First click on read near the bits per inch column name like below.
This will extract the bits per inch range and you will see the underlie align. now you must decide if you want to get the Bitcoin price in USD, GBP or EUR. Select the Record button next to the identify of the coveted currency.
Finally you will see a similar board below which contains the price we were looking for. To transfer the board to Excel click Into Table.
This is the result :
Refreshing the new Query
You can refresh your question by right-clicking the table and hitting Refresh. If you want, however, the Query to automatically refresh sporadically do the following. Go to Query ribbon and collision Properties.
Next crisscross the Refresh every check corner and provide the refresh rate in minutes below. unfortunately the question From Web sport does not allow you to refresh data more much which can be a trouble 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 much.
Bitcoin Price with VBA / Web Scraping
Bitcoin Price Source data
Although there are many available sources I will use the simplest web generator which is the Google search URL. When you type a search question in Google notification 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 region of this string following hypertext transfer protocol : //www.google.com/search i.e. this act ? 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 resultant role for these keywords : bitcoin, price and usd .
This is the web page results for this question above :
You can use other web site pages, however, remember that overusing the lapp URL may get your IP banned if you refresh excessively much
Web Scraping Bitcoin Price using VBA
To get the Bitcoin price from this network page we will need to first obtain the HTML source code of the waiter response. To do this we will need to first open the Excel Visual Basic project VBE. Next we will create a dim-witted 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 fundamental sourcecode for the Google page. What we are interest in however is only bantam part of information below :
now what is left is to somehow extract this value from the stallion HTML DOM of the page source code .
Read this if you want to learn more about Web Scraping in Excel
Extracting Bitcoin Price in Excel
In above part I explained how we can get the resultant role of the Google Bitcoin price question consequence page source code. The document HTML DOM is long and we need to extract precisely the bantam bite of information.
To be more accurate this is the string we are very concern in as it provides the most accurate value of the stream 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 hardheaded. In VBA, however, thankfully we can besides use thus call VBA Regular Expressions i.e. a radiation pattern match 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 entire Excel Bitcoin affair 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 therefore accessible as any Excel serve like so :
Read more: Events Timeline
Download Example
You can download the exemplar with the full code using below yoke :
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 regard sources. What is more they besides post a free API which you can use to get their note Bitcoin price. See below code you can use to replace the one above to get the monetary value from their web site ( 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 reaction like this :
In the exercise just replace these 2 items to get the Bitcoin Price in Excel from CoinDesk alternatively 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 ’ mho price changes. As a bonus I want to show how you can use the VBA Application OnTime routine to car refresh your Bitcoin Price in Excel .
first we need to add from the Developer ribbon yellow journalism a Button Form Control. To do this go to Developer- > Controls- > Insert and blue-ribbon Button from the Form Control section. identify 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 moment as Google might ban you. however, experiment at your own gamble !
Pros and Cons – Power Query volt VBA
I hope you found this utilitarian and matter to and will help you keep get Bitcoin Price in Excel. question From Web deoxyadenosine monophosphate 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:
Read more: Events Timeline
- 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
Leave a Comment