Bitcoin Price in Excel – How to get Bitcoin price in Excel? – Analyst Cave

recently the price of Bitcoin has skyrocketed again and many people keep refreshing their phones and browsers to be on top of stream price fluctuations. however, why not use Excel – and for that determination, 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 early cryptocurrencies – provided you can found some on-line 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 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 :
bitcoin price Coindesk

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 :
bitcoin search results
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 :
excel bitcoin function

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 :
bitcoin price Coindesk
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.
Live Bitcoin Price in Excel
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
source : https://ontopwiki.com
Category : Finance

Post navigation

Leave a Comment

Trả lời

Email của bạn sẽ không được hiển thị công khai.