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


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.


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


  • 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 Feb 19 '19 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 Feb 19 '19 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 Feb 19 '19 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 Feb 20 '19 at 4:47
  • it's not like I didn't include this option in the last link (workarounds) of my solution :) – user0 Feb 20 '19 at 8:56

this concludes historical Bitcoin value relative to today's worth

 "{.+:(.+)}")) * D8/100


  • 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 Feb 19 '19 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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