Hi,
If it can help some of you I have prepared an Excel spreadsheet to get cryptos live quotes from various Crypto’s exchanges.
I have prepared this spreadsheet thanks to tricks found here and there and I tried to put them together to make it easy for your guys to use them and develop your own spreadsheets.
For informations, my spreadsheet is available in my little forum. I keep it in my little forum to make it easier to share and update my tools (all GiG content is completely free, you just need to register to be available to download the file). Link at the bottom of the post.
In GiG forum you can also find my Excel Spreadsheet with my Crypto Portfolio tracker which is using data and queries from CoinMarketCap.
Single crypto live quote
==========================================================Basically, you can get the live quotes for individual cryptos, here a basic example:
Screenshot:
https://image.ibb.co/mHLT96/S1.pngI have included the live quotes for:
- Bittrex
- Bitfinex
- Binance
- CryptoCompare (thanks to cryptocompare you can get the quotes from a huge list of exchanges, but if I am correct, quotes are updated every 10 minutes). But it can be useful to have it available ;-)
For the technical part here are the links used to get the quotes:
- Bittrex:
https://bittrex.com/api/v1.1/public/getticker?market=- Bitfinex:
https://api.bitfinex.com/v2/ticker/t- Binance:
https://api.binance.com/api/v1/ticker/price?symbol=- CryptoCompare:
https://min-api.cryptocompare.com/data/price?fsym=To work correctly, you have to allow macros in Excel (the source code of those macros is visible and I tried to made it clear so as for you to adapt it).
For those individual pairs live quotes, I have coded the macro as a public function which can be used as a formula directly in Excel without having to modify the code. The formula looks like this:
=MyJSON( Base, Quote, Exchange, Volatile)
- Base is the base part of your instrument (for BTCUSD it would be BTC)
- Quote is the second part of your instrument (for BTCUSD it would be USD)
- Exchange is the exchange you want to use to get your quotes (for CryptoCompare you have to add the exchange used by cryptocompare)
- Volatile is just a reference to the cell A1. Explanation below.
In Excel, public functions are not volatile (not updated live or when a cell is modified) and if you set them as volatile, each time you modify a cell, the spreadsheet is recalculated which can be tricky in term of resources usage (CPU…). So, to make it easier to refresh the quotes, I have included a button which refresh the quotes when you click on it. Off course with further development you can program the macro to refresh the quotes every xx minutes…
So to get updates, just click the button and just let A1 as the volatile reference.
List and tickers of Cryptos from Exchanges
==========================================================I have also included some macros to get the tickers from 3 exchanges:
- Bittrex
- Bitfinex
- Binance
I have associated the macro with some buttons, and so onclick the cryptos list are updated, here the example with Bittrex:
Screenshot:
https://image.ibb.co/dprDbm/S2.pngFor the technical part here are the links used to get the quotes:
- Bittrex:
https://bittrex.com/api/v1.1/public/getmarketsummaries- Bitfinex:
https://api.bitfinex.com/v1/symbolshttps://api.bitfinex.com/v2/tickers?symbols=- Binance:
https://api.binance.com/api/v1/ticker/allPricesJson
==========================================================The data are collected from the various Exchange’s API in a Json format which is not readable as it in Excel. So, we have to format the data with the macros to get something readable with Excel.
Some of those macros are formatting the “Json” directly, and some of them are using a library to do the job. The library used is the one which you can find at this link:
https://github.com/VBA-tools/VBA-JSONThe library needs to use a reference in Excel VBA which you can activate: Microsoft scripting runtime. (Tools > references > select)
Key reference for Json: JsonConverter.ParseJson, Parse Json,
You can use those examples to extend the quotes to other exchanges.
Link:
https://www.greed-is-good.com/forum/viewtopic.php?p=1855#p1855PS: all macros codes are completely readable for review.
Regards,
G
