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

in #steemit2 years ago (edited)

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!

Sort:  

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