## Get simple BTC value to insert it in excel sheet

14

3

I'm trying to so some maths calculations with Excel but I need to get the current btc value from any website that can provide it in any format but simple as possible.

Do you have any idea to get the current btc value (usd) and update it in real time in an excel sheet? Thanks.

2

Try to use Web queries in excel, you can adjust which information you want to retrieve. references - http://office.microsoft.com/en-ca/excel-help/get-external-data-from-a-web-page-HA010218472.aspx

– Marek – 2014-03-16T01:06:58.350

@fredsbend mistake from me, sorry. – zeflex – 2017-06-19T12:43:27.123

You can use excel add-in http://cryptoexcel.net/

– Oleg – 2017-10-23T05:58:00.290

10

This more of a question about excel than it is actually about Bitcoin. But the answer is actually pretty easy.

The first thing is that the data that you want to import from the web may not be easily isolated, so you might have to import much more data than you want into a second excel sheet, then link the cell with the data you are interested to a cell in your actual sheet.

1. Find a site that has the dynamic data you are looking for. Let's use Bitstamp.
2. Copy the data that you want and paste it into excel. You can't miss the current price right at the top.
3. After you paste it into excel the little paste options icon will popup next to it. Click it then select Refreshable Web Query.
4. A new window will popup that will load the whole page. You might get some script errors; you can ignore those.
5. The window directs you to click the arrow icon next to the table that you want to import. Unfortunately, with BitStamp, the only option is the whole page. Click the arrow then click import.
6. Excel will now import a load of data from the webpage. The second cell is the one we are looking for; that is the current Bitcoin price in USD. This is where have a separate sheet for this import data is convenient.
7. Go to the sheet where you want to use that data and link the cell you want to the other by first clicking = then clicking the cell in the sheet with all the data from bitstamp. You will see something like =Sheet1!A2 in the formula bar.

That's all there really is to it. Microsoft has an extensive article on the topic complete with all the technical details.

1Couldn't agree more. Über-gut! – Joe Pineda – 2014-03-16T14:20:55.903

So basically the idea is to "import" the data (probably more than you actually need), and then link to the appropriate imported data, right? – Tom Au – 2014-04-25T13:38:55.920

@TomAu Yes. Import the table with the data points that you want into a sheet, then link the cells of another sheet (where you are doing your work) to the data points that you want to use. – frеdsbend – 2014-05-20T21:27:29.997

3

=IMPORTXML("http://coinmarketcap.com/currencies/bitcoin/","//span[@id='quote_price']")


NOT WORKING ANYMORE! I used this formula in google docs and it worked for a long time but now it is not working anymore. Do I need to change something? – user2756695 – 2020-03-24T13:37:35.963

1What is the minimum Excel version that supports IMPORTXML? – Dan Dascalescu – 2017-03-05T05:45:23.183

It works but when I try to work with it for example divide or multiply the price I get #VALUE! error the price is shown as text instead of a number. Any solutions for this? Thanks – Tim GVK – 2017-06-25T06:43:01.253

This also works with google docs – guribe94 – 2017-09-01T01:06:04.227

use value() to convert the text to a number in Google sheets and Excel – vinnief – 2017-12-07T07:49:10.310

This works really great. Any chance on improving to get the value only in text-large2 – snh_nl – 2018-01-05T12:45:55.687

0

While it is a stock market add-in, it works with Yahoo Finanace and since Yahoo now lists Bitcoin (Symbol: BTCUSD=X) Yes, that is the symbol you use to get the price, it will update your spreadsheet every time there is a change made to the spreadsheet or when you force the sheet to recalculate.

This fits the bill for what you need.

0

Here's a simplified macro that works as far back as Excel 2007, original credit Samir Khan:

Sub GetBTCUSD()

Range("BTCUSD").Select
Selection.ClearContents

"URL;https://api.bitcoinaverage.com/ticker/USD/last", Destination:=Range("BTCUSD"))
.Name = "BTCUSD field that will pollute your Name Manager but I don't know how to get rid of"
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SaveData = False
.RefreshPeriod = 0
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub


To use the macro:

1. Name a column in your spreadsheet "BTCUSD" (just click in the Name Box in the top left and type "BTCUSD"
2. Add a button to the sheet (you can name it "Get BTCUSD")
3. Create a new Macro and paste the code above
4. Right click the button, choose Assign macro, and pick the GetBTCUSD macro.

0

I tried this with Excel 2016:

Cell J2 = https://query.yahooapis.com/v1/public/yql?q=
Cell J3 = select * from html where url='https://coinmarketcap.com/currencies/bitcoin/' and xpath='//span[@id="quote_price"]'
Cell J4 = &diagnostics=true&env=store://datatables.org/alltableswithkeys


Insert the following into the Cell where you want the actual price:

=XMLFILTERN(WEBDIENST($J$2&URLCODIEREN($J$3)&$J$4);"//span")