How to get AUD price of bitcoin at a particular date

Question

I am trying to find out how to get the price of bitcoin, ethereum, litecoin and bitcoin cash in AUD at a particular date.

I have a table as follows

+------------+-------+
|    Date    | Price |
+------------+-------+
| 16/03/2016 |       |
| 19/04/2016 |       |
| 03/12/2017 |       |
+------------+-------+

I have tried entering using =IMPORTXML("http://coinmarketcap.com/currencies/bitcoin/","//span[@id='quote_price']") in the price column but it doesn't seem to work.

Can you provide the sample result situation you expect? I cannot understand about the relationship between your formula and your table. I apologize for this. — Tanaike, Jan 16 '21 at 02:42
I would not expect it to work since `IMPORTXML` is not an Excel function. — Ron Rosenfeld, Jan 16 '21 at 12:51

jarumski · Answer 1 · 2021-01-23T02:23:43.680

I would use: https://min-api.cryptocompare.com/documentation?key=Historical&cat=dataPriceHistorical

In order to use this service you need an api key and register at their website:https://www.cryptocompare.com/cryptopian/api-keys

You also need to convert your date to unix timestamp:
https://www.unixtimestamp.com/index.php
e.g. 16/03/2016 => 1458086400
so ts=1458086400

The complete api call would resemble:
https://minapi.cryptocompare.com/data/pricehistoricalfsym=BTC&tsyms=AUD&ts=%201458086400&api_key="your-key"

executing this call gives = AUD: 582.52

The value in 'Kolom1' is de resulting value in AUD. This value can be retrieved by executing the url in 'URL' field : =WEBSERVICE([@URL])

Remark: to convert your dates 16/03/16 see post: Excel date to Unix timestamp

How to get AUD price of bitcoin at a particular date

1 Answers1