Topic: Excel Spreadsheet Cryptos live quote JSON / API… (Read 391 times)

Hello Greg,

There is an error about other website register and login. I try it again and again.
Is it possible that could you send the excel file to my personal e-mail.
I would be grateful.

Thank you very very much.

Regards.

Hi Greg,
sorry to bother you but I'd love to try out your spreadsheet but I'm unable to register on the GIG website - https://www.greed-is-good.com/forum/viewtopic.php?f=85&t=16

I've tried 3 times to register but failed and I don't know why - I used different email addresses and verification was not sent to any of the email addreses.

Can you give me access to it via some other method ?

many thanks

B

Hi,
You are free to help to improve the spreadsheet, I tried to comment/explain the process as much as possible to make the macro easy to use and to duplicate to add other exchanges.

Not that I don’t want to do it, but there are so many exchanges that I cannot answer to every single request.

Hope you like it ;-)
Best,
G

It would be good if you are including few more exchanges and cryptos and showing their comparisons in pie charts.

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.png


I 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.png


For 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/symbols
https://api.bitfinex.com/v2/tickers?symbols=

- Binance:
https://api.binance.com/api/v1/ticker/allPrices


Json
==========================================================
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-JSON
The 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#p1855

PS: all macros codes are completely readable for review.
Regards,
G