[Google Sheets] Cotation temps réel des cryptos (Binance / websocket)

Bonjour,

  • pour les cryptomaniaques d'une part
  • pour une démonstration de l'utilisation des websockets,

je vous soumets un projet-démonstration

Avant toute chose,

  • Binance propose une API pour recueillir des informations publiques sur les cryptomonnaies : ce sujet est donc tout à fait légal et ne constitue pas du web scrapping au hacking du terme. Encore que la définition du terme est soft : "Le hacking est initialement la bidouille et l'expérimentation, dont les motivations sont la passion, le jeu, le plaisir, l'échange et le partage".
  • Le websocket est une méthode d'accès en pseudo-continu à des informations, c'est en ce sens une évolution du HTTP et de l'AJAX

Pour ce faire, dans le projet, nous allons trouver 4 parties :

1- la mise en place d'un menu spécifique pour lancer la lecture via websocket (wss)

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('* BINANCE *')
    .addItem('Manage ...', 'showSidebar')
    .addToUi();
}
function showSidebar(){
  var ui = SpreadsheetApp.getUi();
  var html = HtmlService
    .createTemplateFromFile("binance")
    .evaluate()
    .setTitle("web socket");
  ui.showSidebar(html);
} 

2- la transmission des paramètres qui sont ici des "pairs" (colonne B), avec création d'une fonction qui retournera les paramètres à transmettre à l'API Binance

const feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('derCotation');

function param(){
  var valeurs = feuille.getRange('B3:B'+feuille.getLastRow()).getValues().join().split(",")
  var myparams = {"method": "SUBSCRIBE", "params": valeurs, "id": 1}
  return JSON.stringify(myparams) 
}

3- la communication via une page html avec l'API de Binance : la page appellera la fonction précédente par

<? var myparam = param(); ?>

et retournera le résultat

google.script.run.recupererJSON( evt.data );
<!DOCTYPE html>
  <meta charset="utf-8" />
<head>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <style>
    body{margin:12px;}
  </style>
  <script language="javascript" type="text/javascript">

  var wsUri = ("wss://stream.binance.com:9443/ws");
  <? var myparam = param(); ?>
  var nbr=0

  function init()  {
    websocket = new WebSocket(wsUri);
    websocket.onopen = function(evt) { onOpen(evt) };
    websocket.onclose = function(evt) { onClose(evt) };
    websocket.onmessage = function(evt) { onMessage(evt) };
    websocket.onerror = function(evt) { onError(evt) };
    nbr=0;
  }

  function onOpen(evt)  {
    writeLog("Connected to:<br>" + wsUri);
    doSend(<?= myparam ?>);
  }

  function onClose(evt)  {
    writeLog("Disconnected from:<br>" + wsUri);
  }

  function onMessage(evt)  {
    nbr++;
    google.script.run.recupererJSON( evt.data );
    document.getElementById('nbr').innerHTML=('<span style="color: blue;">Response #<b>' + nbr + '</b> transmitted to sheet</span>');
  }

  function onError(evt)  {
    writeLog('<span style="color: red;">ERROR:</span> ' + evt.data);
  }

  function doSend(message)  {
    writeLog("Sent: " + message);
    websocket.send(message);
  }

  function writeLog(message)  {
    var pre = document.createElement("p");
    pre.style.wordWrap = "break-word";
    pre.innerHTML = message;
    document.getElementById("log").appendChild(pre);
  }

  </script>
</head>
<body>
    <div id="nbr"> </div><br>
    <div id="lastButtonBlock" class="block">
    <button onclick="init()" class ="action button actionButton" > Connect </button>
    <button onclick="websocket.close()" class ="action button actionButton" > Disconnect </button>
    <button onclick="google.script.host.close()" class ="create deleteButton actionButton" > Close </button>
    </div><br>
    <div id="log"></div><br>
</body>
</html>

4- la restitution en temps réel des informations dans la feuille

function recupererJSON(jsonstring){
  try{
    var data = JSON.parse(jsonstring);
    var pairs = feuille.getRange('A3:A').getValues().join().split(",");
    var ligne = pairs.indexOf(data.k.s);
    if (ligne != -1){
      var result=[]
      result.push([ data.k.o , data.k.c , data.k.h , data.k.l ])
      feuille.getRange('C' + (ligne+3) + ':F' + (ligne+3)).setValues(result)
      // pour le fun
      feuille.getRange('a' + 2).setValue(data.k.s)
      feuille.getRange('C' + 2 + ':F' + 2).setValues(result)
    }
  }
  catch(e){
  }
}
binance

Bonjour, et merci pour votre excellent travail !

Une question me taraude, puis-je ouvrir plusieurs connexion web sockets , car ce qui m'intéresse serait dans la même feuille que je puisse recupere kline en 1 mn , 5 mn et 15 mn.

J'ai réussi à recréer une page html, et recopier et modifier les fonctions, j'ai bien mon menu mais je ne peux qu'ouvrir un ou l'autre.

Auriez vous un exemple de code pour ouvrir ces trois flux ? J'ai regardé la doc sur binance, c'est possible mais je n'ai pas d'exemple .

Encore MERCI pour votre partage

Bonjour, et bienvenue ... va falloir que je me replonge dans le code !

Excellent, merci

Une question me taraude, puis-je ouvrir plusieurs connexion web sockets , car ce qui m'intéresse serait dans la même feuille que je puisse recupere kline en 1 mn , 5 mn et 15 mn.

J'ai réussi à recréer une page html, et recopier et modifier les fonctions, j'ai bien mon menu mais je ne peux qu'ouvrir un ou l'autre.

Auriez vous un exemple de code pour ouvrir ces trois flux ? J'ai regardé la doc sur binance, c'est possible mais je n'ai pas d'exemple .

Pas besoin d'ouvrir plusieurs websockets !! Les paramètres envoyés sont du type :

["ethusdt@kline_5m","ethbtc@kline_5m","btcusdt@kline_5m","btceur@kline_5m"]

on peut très bien envoyer ceci, un mix entre kline_15m et kline_5m :

["ethusdt@kline_15m","ethbtc@kline_15m","btcusdt@kline_15m","btceur@kline_15m","ethusdt@kline_5m","ethbtc@kline_5m","btcusdt@kline_5m","btceur@kline_5m"]

Les valeurs sont construites aujourd'hui en prenant la colonne B et le retour est basé sur la seule colonne A, il faut juste modifier la façon dont sont fournis les paramètres et récupérées les valeurs ... dois-je le faire ou tu sais adapter le script ?

Merci pour ce retour, oh la la la... la puissance de la chose !

Nom je ne sais pas mais je vais essayer, j'apprends et en découvrant la puissance de ce que tu fais avec ces lignes de codes, et bien c'est tout simplement génial.

En fait dans derCotation ( original) , jai rajouté en J, la différence entre vs low et vs high, ce qui me donne en % la tendance de la kline. si c'est positif j'afficherai une flèche verte, et rouge si c'est négatif

Alors j'ai quelques questions:

1.Pour une question de visibilité:

Au lieu d'avoir 3 lignes par pair, ETCUSDT 1mn, ETCUSDT 5mn et ETCUSDT 15mn, serais il possible par exemple de dire sur une ligne :

ETCUSDT en 1mn on envoies les résultats de D à H, en 5 mn de I à M, et 30 mn de O à R ?

je ne sais pas si je m'xplique bien, alors je te joins une image qui concerne le forex mais est identique dans la démarche.

2.Concernant le code : Je n'arrives pas à comprendre à quoi correspond cette fameuse ligne+3 ?

sh.getRange('D' + (ligne+3) + ':G' + (ligne+3)).setValues(result), je suis un peu bête , mais je préfére ne pas mourir idiot !

3. Si ce n'est pas possible d'avoir une seul paire par ligne, est ce que je peux envisager que derCotation devienne en fait une feuille DATA ou j'irais piocher pour réafficher mes valeurs sélectionnées dans une autre feuille bien mise en forme VIA IMPORTRANGE ? Mais si c'est le cas, alors je pense que les données ne pourrait plus être en live ?

En attendant, un grand merci et si je peux t'offrir un petit quelque chose, ce sera avec grand plaisir.

forex

Ne tiens pas compte des dessins sur la photo c'était déjà dessus ca n'as rien à voir

1.Pour une question de visibilité:

Au lieu d'avoir 3 lignes par pair, ETCUSDT 1mn, ETCUSDT 5mn et ETCUSDT 15mn, serais il possible par exemple de dire sur une ligne :

ETCUSDT en 1mn on envoies les résultats de D à H, en 5 mn de I à M, et 30 mn de O à R ?

intéressant ... je vais le faire d'ici demain dans une autre copie du fichier, sachant qu'ensuite tu pourras modifier à ta guise !

je vais regarder de plus près le forex


2.Concernant le code : Je n'arrives pas à comprendre à quoi correspond cette fameuse ligne+3 ?

sh.getRange('D' + (ligne+3) + ':G' + (ligne+3)).setValues(result), je suis un peu bête , mais je préfére ne pas mourir idiot !

result est un array (tableau) de 4 données o, c, h, l issu du json que tu peux voir s'afficher en bas à droite de la page html en sidebar

      result.push([ data.k.o , data.k.c , data.k.h , data.k.l ])

il est rangé dans la ligne correspondant au flux de données

    var pairs = sh.getRange('C3:C').getValues().join().split(",");
    var ligne = pairs.indexOf(data.k.s.toLowerCase() + '@kline_' + data.k.i);

sachant que je recherche la correspondance du flux entrant avec la liste des pairs que je prends à partir de la ligne 3 (la ligne 1 est dédiée aux titres et la ligne 2 est vierge), et que s est la pair et i la kline

entre les colonnes D et G

Si ce n'est pas assez clair, dis le ...


En attendant, un grand merci et si je peux t'offrir un petit quelque chose, ce sera avec grand plaisir.

juste un retour, ça suffit (et ce n'est pas toujours le cas !)

Qu'est-ce qui détermine les flèches sur l'image ?

  • est-ce juste la progression des valeurs entre 2 flux,
  • ou est-ce un algorithme plus complexe de type tendance sur plusieurs valeurs,
  • ou comparaison avec high et low ?
  • ou comparaison avec ouverture ?

parce que dans le premier et second cas cela nécessite de stocker plusieurs flux et pas seulement le dernier.

Il faudrait optimiser le temps de calcul en retour des données ...

https://docs.google.com/spreadsheets/d/1Is8fMxEdzbX5UWsGZfZGoR1FtFrl_0e4vqYWbCudETw/copy

Tout simplement génial !

Q- Qu'est-ce qui détermine les flèches sur l'image ?

R- Alors pour moi, sur le premier fichier que tu as fait, sur la feuille derCotation, j'avais créé une colonne "J" qui faisait la différence entre "H" vs low et "I" vs high , qui me donne la tendance sur l'unité de temps, et apparemment, cela se verifie trés bien quand j'ouvre le graphique sur binance .

Donc si J est en positif, il y a plus d'acheteurs que de vendeurs et vice-versa.

Qu'entends tu par il faudrait optimiser le temps de calcul en retour des données ... ? J'ai remarqué que si tu mets une trentaine de paires par exemple, oui évidemment ca mouline dans la feuille , mais je ne sais pas si c'est du à mon Pc de base, je vais essayer sur un autre pc lundi.

Concernant mes questions précédemment posées auxquelles tu as répondu, j'ai tout compris! Comme quoi, quand c'est très bien expliqué, on y arrive !

Je vais aller a la pêche aux infos, et je te tiens au courant.

Je me répète mais bon… Merci !!

Qu'entends tu par il faudrait optimiser le temps de calcul en retour des données ... ? J'ai remarqué que si tu mets une trentaine de paires par exemple, oui évidemment ca mouline dans la feuille , mais je ne sais pas si c'est du à mon Pc de base, je vais essayer sur un autre pc lundi.

Quand on stoppe le websockets, on voit qu'il y a encore des données dans le buffer. Il faut donc minimiser le calcul lors du retour des infos et donc ne pas rechercher à chaque fois sur quelle ligne et quelles colonnes je dois reporter les informations.

Mon idée est donc de faire ce calcul une fois pour toutes, c'est-à-dire mémoriser que telle pair est sur telle ligne et que telle kline est sur telles colonnes. Hors, mémoriser ces informations sur GAS n'est pas forcément facile, mais on peut le faire côté html lors du lancement de la sidebar. C'est mon objectif.

Super ! En regardant dans le buffer, je viens seulement de comprendre que l'on reçoit kline complet de "e" à "q", et après on pioche ce qui nous intéresse dans ce cas précis "o,c,h,l" alors que je pensais qu'en fait lors de la requête on demandait "o,c,h,l" ! Je me suis emmêlé les pinceaux, tout s'éclaire !

Pourtant c'est explicite, mais quand on est fatigué, et que c'est pas son métier, forcément, il y a des bugs au niveau des neurones...

En tout cas, c'est un régal !

et ce que je voudrais transférer, c'est seulement o,c,h,l ainsi que la ligne (dépend de s) et la colonne (dépend de i) où ranger les infos reçues ! sachant qu'une fois pour toutes, lors de l'initialisation de la feuille html, j'aurais établi la relation entre s et ligne et i et colonne.

J'ai compris la logique, merci.

Grâce à toi, j'ai réussi à comprendre, et à me recréer une feuille, rapatrier et afficher les infos du ticker sur 24 h pour toutes les cryptos listées dans la colonne A. C'est trop génial !

Du coup, la motivation est à la hausse ! Je vais me faire un modèle design simple et efficace, et essayer de faire en sorte de ranger bon endroit les valeurs, par exemple le "o" en F40, le "c" dans une autre ... Je vais essayer.

MERCI !

ok, tu me partageras ton design ?

de mon côté,

  • en réalité, le temps de traitement était plus perturbé par les couleurs, que j'ai donc enlevées
  • comme Binance renvoie un json en format string, j'ai abandonné l'idée que j'avais commencé à mettre en œuvre d'y adjoindre directement ligne et colonne, en réalité cela faisait trop de calcul car il aurait fallu parser et ensuite "re-stringify-ier"
  • j'ai compléter les pairs en y mettant tout ce que je connaissais
  • je vais voir si je peux une fois pour toutes mettre la relation pair > ligne concernée dans le script, même si c'est contraire à mes principes de mettre les variables dans la feuille pas dans le script

Super, concernant le design, bien évidemment que je te le partagerai ! Et avec grand plaisir Je vais m'y mettre demain car ma fille n'as pas école aujourd'hui et comme je suis tout seul pour m'en occuper, à 7 ans, ca a besoin d'attention . Je t'expliquerai en détails quelques bonus que j'ai fait qui reprennent par exemple pour chaque crypto, un lien qui ouvre une nouvelle fenêtre du navigateur qui ramène directement sur binance sur le graphique de la crypto concernée, idem un lien sur tradingview et idem pour cryptopanic qui affiche la dernière news concernant la crypto. Gain de temps indéniable.

Rechercher des sujets similaires à "google sheets cotation temps reel cryptos binance websocket"