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

in steemit •  4 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")

That will look like this



Screen Shot 2018-02-13 at 13.11.58.png

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

That will look like this



Screen Shot 2018-02-13 at 13.12.05.png

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)

That will look like this



Screen Shot 2018-02-13 at 13.22.34.png

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

That will look like this



Screen Shot 2018-02-13 at 13.31.10.png

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

That will look like this



Screen Shot 2018-02-13 at 13.40.33.png

Any questions comment below!

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

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!

Very helpful, thanks!

Excellent post!!

Interesting article! Checkout @cryptobroye for more bitcoin info and analysis. Im sure there's a lot we can learn from each other!

I feel a lot of your posts, the post is that