[Geko]

Excel Crypto / API / Json / Live quote…

You can find in this thread a spreadsheet that I have prepared 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 (that you are free to share here).

Single crypto live quote
==========================================================
Basically, you can get the live quotes for individual cryptos, here a basic example:

Single.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:

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

Feel free to share your developments here

[celobacara]

Congratulations, I never seen some so complex explained so simply.
Thanks.
Celobacara

[Geko]

Many thanks for your kind words

So many people are making things so difficult when they could be so simple, it is especially true in the financial world.

Having worked in this world during years, when you see the vocabulary used

[Ciezar]

How to get LTCBTC order book ? Is it possible ?

[Ciezar]

How to get bid and ask BTCLTC from Biance ?

[Geko]

Hi And welcome to GiG Ciezar,

Here your answer:

2018-01-17_0857.png

[cryptophan]

thank you very much for this useful tools..
it's running great on bittrex and bitfinex when requesting live quotes
but for binance got error run time error

[Geko]

Hi cryptophan and welcome to GiG,

Thanks, I am happy that you find this tool useful

I have just tested the tool with Binance and it is working fine from here.

Have you tried to open directly the URL in a browser to see if it is working fine?

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

[fourwinds]

GEKO, I have tried to use your spreadsheet but it won't open in Excel for MAC 2011 v 14.0.0 properly. I am getting the error
"compile error cant find project..."

I followed your instructions and downloaded the updated Json file. The import I believe came in fine. I haven't been able to follow your instructions to add the new dictionary that I think would be necessary to run on my MAC.

I am not a programmer so my knowledge of VBA is almost nil.

Is your program too esoteric for me to run?

Thanks,
Nancy

[Geko]

Hi Nancy,

I am not sure about the compatibility of the file with a MAC as I have not tested it.

I have just checked the latest version of Excel available and it seems that we are at 16.xxx. Here I am using the latest updated version on Windows.

So, first thing, I would recommend you to update your Excel version.

Second, concerning the macro security, you have to make sure that you enable macros when you open the file in order to be able to modify the file and have access to the macro code.

Third, concerning the library, the one used is a Microsoft lib, so I am not surprised that it is not available on MAC…

I have just checked how to solve this issue and I found those links in case they can help:

https://stackoverflow.com/questions/198 ... ary-on-mac

https://sysmod.wordpress.com/2011/11/02 ... ictionary/

As I have not a MAC version here I cannot test those advises.

The code to get updates from exchanges with the list of all cryptos is not using the lib and should work fine.

I anybody read this message and is a MAC user maybe she/he can help us…

Hope it helps,
Best,
G