Google Sheets – Get Cryptocurrency Prices at Specific Times

bitcoinformulasgoogle sheetsgoogle-finance

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.

Best Answer

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

installation: https://cryptofinance.ai/how-to-install
test out: https://chrome.google.com/cryptofinance
limitations: maximum of 10 historical calls per day
price: $39/month, $374/year | 34.51€/month, 330.91€/year
workarounds: https://medium.com/cryptofinance-ai

Related Solutions

GOOGLEFINANCE – Return Only Historical Price in Google Sheets

No, it is not possible. You can however just reference the lower right cell in another cell.

so if you got back from Google:

A1: "Date"      B1: "Close"
A2: "3/3/2000"  B2: "55.22"
A3: "Date"      B3: "Close"
A4: "3/4/2000"  B4: "58.44"

Set cell C1 =B2 and cell C2 =B4

and the results will be:

C1: 55.22
C2: 58.44

you could also put the query in one google doc and then reference it from another google doc if you really wanted to separete the data.

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Best Answer

this concludes historical Bitcoin value relative to history records

Related Solutions

GOOGLEFINANCE – Return Only Historical Price in Google Sheets

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

Related Topic