# How To Calculate % Alt Changes In Terms Of BTC (not USD) In Google SheetssteemCreated with Sketch.

in steemit •  10 months ago

## One of the things I've found frustrating about Coinmarketcap API is that it gives % change values in USD not BTC

Today I sat down to work out a way to track the relative change in BTC as a percentage. If you don't have Cryptofinance set up in your sheets then go here to learn more.

This is the Google sheet I use in the example

## What do we know already

Cell A1: Bitcoin Price today `=CRYPTOFINANCE("BTC/USD")`
Cell B1: Bitcoin % change (USD) 1 hour `=CRYPTOFINANCE("BTC", "change", "1h")`
Cell C1: Bitcoin % change (USD) 24h `=CRYPTOFINANCE("BTC", "change", "24h")`
Cell D1: Bitcoin % change (USD) 7d `=CRYPTOFINANCE("BTC", "change", "7d")`
Cell E1: Alt price in BTC `=CRYPTOFINANCE("ICN/BTC")` (in this example we will use ICONOMI)
Cell F1: Alt price in USD `=CRYPTOFINANCE("ICN/USD")`

## What else do we need?

We need to work out the price of Bitcoin (USD) 1 hour ago, 24 hour ago, and 7d ago. For this we will use the % changes and apply them to the current price.

Cell B4: Bitcoin Price 1h ago `=(A1/(100+B1))*100`
Cell B5: Bitcoin Price 24h ago `=(A1/(100+C1))*100`
Cell B6: Bitcoin Price 7d ago `=(A1/(100+D1))*100`

## What's next?

Next we take the current alt price, the alt % changes and combine to work out the price 1h, 24h and 7d ago.

Cell B8: ICN % change (USD) 1h `=CRYPTOFINANCE("ICN", "change", "1h")`
Cell B9: ICN % change (USD) 24h `=CRYPTOFINANCE("ICN", "change", "24h")`
Cell B10: ICN % change (USD) 7d `=CRYPTOFINANCE("ICN", "change", "7d")`

Cell D8: ICN price 1h ago `=100*F1/(100+B8)`
Cell D9: ICN price 24h ago `=100*F1/(100+B9)`
Cell D10: ICN price 7d ago `=100*F1/(100+B10)`

## Still with me?

Next we are going to use these newly found ICN prices from 1h/24h/7d ago and divide them by the BTC price from 1h/24h/7d ago to work out what the price of ICN was in terms of BTC 1h/24h/7d ago.

Cell B12: ICN/BTC price 1h ago `=D8/B4`
Cell B13: ICN/BTC price 24h ago `=D9/B5`
Cell B14: ICN/BTC price 7d ago `=D10/B6`

## The last bit...

Finally we will take the price of ICONOMI now (BTC) and compare it with its BTC price 1h, 24h and 7d ago...

Cell B16: `=100*(E1-B12)/B12`
Cell B17: `=100*(E1-B13)/B13`
Cell B18: `=100*(E1-B14)/B14`

## Any questions comment below!

Sort Order:

good job!

Dude, you are a person of unexpected talents!!

This is great, and something I preach. Know the difference in the correct context ie USD or BTC and understand the relationships. Nice work!

This is awesome @thisisbenbrick! Super helpful!

great resource you've provided @thisisbenbrick!