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.

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

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

  • 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! – Jonathan van Clute Feb 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.