0

I have an assortment of cryptocurrency transactions over time. Various currency pairs at various prices. I need to figure out what the price of those currencies was, in Bitcoin, at the time that I purchased them.

The manual solution is to look up a historical price chart of each pair (ETH/BTC, LTC/BTC, XRP/BTC, etc.) at the moment of purchase and record that. But needless to say, this is an extremely time consuming and tedious process.

I have the date & time of each purchase, along with the currency purchased and the amount, in a spreadsheet already. I would like to be able to fetch the final data I need from an API of some sort directly in my sheet.

I've gotten something similar using 

https://blockchain.info/frombtc?value=100000000&currency=USD&time=1468858800000&textual=false&nosavecurrency=true

but it only returns the value of BTC in USD. I need to get the value of other currencies in BTC.

I have made some dummy data available here.

Improve this question
1

2 Answers 2

Reset to default
0

this concludes historical Bitcoin value relative to history records

  • if you want to go full-pro-mode try CRYPTOFINANCE

=CRYPTOFINANCE(C8&"/BTC", "price", B8) * D8

0

Improve this answer
5
  • Thanks, I did look at the pro version of cryptofinance but I don't know how often I'll be doing this so don't want to incur any costs at this time.
    – JVC
    Commented Feb 19, 2019 at 15:36
  • if it's just for occasional purposes then just sacrifice a few days to get your historical data with 10 calls per day
    – user0
    Commented Feb 19, 2019 at 16:58
  • Yeah but right now, it would be more than a few days and was hoping to have the work done this past weekend. Then I found out it was much harder to come by the data than anticipated. I have to look up quite a lot of data from the past right now, but don't know how often I'll be doing it in the future.
    – JVC
    Commented Feb 19, 2019 at 23:02
  • 1
    I had forgotten that you can bypass the coinmarketcap data by specifying the exchange you want to use in the formula, in my case BINANCE. Doing so allowed me to get all the data I needed. Though I had already explored this route, I'll accept this as the answer since it is ultimately what worked, just with a slight tweak.
    – JVC
    Commented Feb 20, 2019 at 4:47
  • it's not like I didn't include this option in the last link (workarounds) of my solution :)
    – user0
    Commented Feb 20, 2019 at 8:56
0

this concludes historical Bitcoin value relative to today's worth

=IMPORTDATA("https://blockchain.info/frombtc?value=10000000&currency=USD&time="&
 B8 * 1000) * VALUE(REGEXEXTRACT(
 IMPORTDATA("https://min-api.cryptocompare.com/data/price?fsym=USD&tsyms="&C8), 
 "{.+:(.+)}")) * D8/100

0

Improve this answer
1
  • 1
    I like the use of cryptocompare in the formula, but since it's only current price data I can't use it. Nice effort though!
    – JVC
    Commented Feb 19, 2019 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.