Что нам стоит автоматизацию построить. Использование HTTP API в Google Sheets

    В эпоху повальной автоматизации пользователям хочется «нажать на кнопку и получить ответ». Ну или дополнительно немного подвигать мышкой. Автоматизация же отчетов и других штук, которые удобно представить в виде таблички, часто строится в Excel с использованием своих макросов или же просто встроенных формул. Плагинами к Excel нынче никого уже не удивишь, кстати, у нас такой тоже есть, но это предмет отдельной статьи. А как насчет Google Sheets? Ранее мой коллега рассказывал, как можно прикрутить наше API к Telegram, я же попробую рассказать, как использовать его в гуглотаблицах.


    image


    Под катом чуть-чуть кода и много костылей.


    Работать мы будем, очевидно, в браузере. Для написания своих функций будем использовать Google Apps Script, который по синтаксису подозрительно похож на урезанный javascript. Исходим из принципа, что кодить мы не умеем, а читать документацию не хотим, зато активно используем подходы, изложенные в технике Stackoverflow Driven Development.


    Если вы начинающий трейер, то предлагаем вам почитать тут.


    Подготовка


    Для начала получаем доступ к API. Бесплатно (если только аккаунт-менеджеры не замучают звонками) и без смс, но с регистрацией. Документацию читать не будем (все равно там картинок нет), а токен для доступа мы сгенерируем руками через jwt.io. Почему руками? Потому что токен, генерируемый нашим сайтом, истекает через час. Это полезно, например, для использования на вебсайте, но для нормальной работы в Sheets мы хотим, чтобы он жил дольше, допустим, год. Подробнее о процедуре создания токена можно почитать здесь.


    Работа с API


    Теперь создаем пустую таблицу и идем в редактор скриптов; если кто не знает, попасть туда можно путем вызова ToolsScript editor. В редакторе объявим несколько глобальных переменных:


    var BASE_URL_API = "/md/1.0";
    var BASE_URL_HOST = "https://api-demo.exante.eu";
    var BASE_URL = BASE_URL_HOST + BASE_URL_API;
    var TOKEN = "your-token-from-jwt-io";

    Также зададим функции для работы с запросами:


    function _payload() {
      return {
        "method": "get",
        "headers": {
          "Authorization": "Bearer " + TOKEN
        }
      };
    }
    
    function _parse(url) {
      var response = UrlFetchApp.fetch(url, _payload());
      var code = result.getResponseCode();
      if (code != 200)
        throw new Error(response.message);
      return JSON.parse(response.getContentText());
    }

    Подробнее про UrlFetchApp и его аргументы можно почитать здесь. Дополнительно мы вылавливаем коды, отличные от 200, и показываем пользователю «человекочитаемую» ошибку из запроса.


    Статическая информация из API


    Сейчас попробуем прикрутить несколько вызовов нашего API. Здесь все-таки пришлось открыть документацию и убедиться, что красивых картинок там действительно нет.


    Для начала напишем метод, реализующий запрос финансовых инструментов. Как мне подсказывают, для экономии трафика информацию об инструментах разделили в два конца — /symbols/:symbolId и /symbols/:symbolId/specification:


    var SYMBOL_SPEC_FIELDS = ["leverage", "lotSize", "contractMultiplier", "priceUnit", "units"];
    
    function EXANTESYMBOL(symbol, field) {
      var url = BASE_URL + "/symbols/" + encodeURIComponent(symbol);
      if (field in SYMBOL_SPEC_FIELDS)
        url += "/specification";
      return _parse(url)[field];
    }

    Здесь и далее имя финансового инструмента (symbol) должно кодироваться, хотя бы потому что может содержать странные символы, например, /.


    Затем создадим аналогичные методы для работы с опционами и фьючерсами.


    function EXANTEGROUP(group, field) {
      var url = BASE_URL + "/groups/" + group;
      return _parse(url)[field];
    }
    
    function EXANTEGROUPNEAREST(group, field) {
      var url = BASE_URL + "/groups/" + group + "/nearest";
      return _parse(url)[field];
    }

    Котировки и «свечки»


    Свечки — это такой специальный индикатор на финансовых графиках. Для понимания того, что мы делаем, достаточно знать, что одна «свечка» представлена четырьмя значениями — [цена_на_начало_интервала, максимальная_цена_в_интервал, минимальная_цена_в_интервал, цена_на_конец_интервала]. Интервал у нас задается в секундах, в общем виде функция будет выглядеть так:


    function EXANTEOHLC(symbol, duration, what) {
      var url = BASE_URL + "/ohlc/" + encodeURIComponent(symbol) + "/" + duration + "?size=1";
      return _parse(url)[0][what];
    }

    Тогда запрос наподобие EXANTEOHLC("EUR/USD.E.FX", 60, "high") вернет нам максимальную цену за последнюю минуту.


    С котировками чуть сложнее. На момент написания статьи единственное API для получения котировок — это стрим, который неудобно использовать в Apps Script. (Кстати, обещают добавить новое API для единичной котировки в будущих релизах). Поэтому пришлось накостылить решение из имеющихся средств. По построению, close незакрытой свечки (то есть за текущие минуту/час/день) — это среднее между последними пришедшими ценами покупки и продажи, поэтому:


    function EXANTEMID(symbol) {
      return EXANTEOHLC(symbol, 60, "close");
    }

    Для полного счастья можно еще сделать функцию конвертации из одной валюты в другую:


    function EXANTECROSSRATES(from, to) {
      var url = BASE_URL + "/crossrates/" + from + "/" + to;
      return _parse(url)["rate"];
    }

    Использование


    Теперь мы попробуем использовать наши функции как обычные методы в Excel. Первая же проблема, с которой мы столкнемся — это обновление значений. Дело в том, что Google считает, что нет нужды часто пересчитывать пользовательскую функцию, если параметры не изменились. В случае котировок, которые предполагаются как «live», это немного критично. Для обхода данной проблемы добавим еще один «изменчивый» (а на самом деле нет), но не используемый аргумент в наши функции EXANTEOHLC, EXANTECROSSRATES и EXANTEMID и назовем его timestamp:


    function EXANTECROSSRATES(from, to, timestamp) {
      var url = BASE_URL + "/crossrates/" + from + "/" + to;
      return _parse(url)["rate"];
    }
    
    function EXANTEOHLC(symbol, duration, what, timestamp) {
      var url = BASE_URL + "/ohlc/" + encodeURIComponent(symbol) + "/" + duration + "?size=1";
      return _parse(url)[0][what];
    }
    
    function EXANTEMID(symbol, timestamp) {
      return EXANTEOHLC(symbol, 60, "close", timestamp);
    }

    Теперь реализуем функцию, которая будет генерировать этот timestamp.


    function EXANTEUPDATE() {
      SpreadsheetApp.getActiveSheet().getRange('A1').setValue(new Date().toTimeString())
      SpreadsheetApp.flush();
    }

    Обратите внимание, что мы нагло приватизировали ячейку A1, а заодно и потребовали дополнительных прав на модификацию листа. Для повышения безопасности гугл рекомендует вставить @OnlyCurrentDoc, чтобы скрипт не просил права сразу на все документы:


    /**
     * @OnlyCurrentDoc
     */

    Кстати, на первый взгляд, можно было бы просто использовать функцию NOW() (она одна из немногих умеет пересчитываться раз в минуту, при наличии специальной галочки в настройках Юзабилити), но ее значение нельзя передать в пользовательскую функцию, печаль.


    Для автоматического обновления данных раз в минуту можно создать триггер для написанной функции в EditCurrent project's triggers:


    image


    Для полного пользовательского счастья дополнительно можно добавить кнопку (делается в нашей табличке через InsertDrawing...) и связать ее с функцией EXANTEUPDATE.


    О, кажется, теперь с этим можно работать. Давайте попробуем взять ближайший фьючерсный контракт на FORTS:Si (который USD/RUB) и посмотреть на его свечки:


    image


    Но мы же говорим об автоматизации, почему бы нам не сделать такую табличку для 100 инструментов сразу? Ой...


    image


    Но методы обхода этой проблемы я предлагаю найти читателю самостоятельно :) Вероятно, не лучшее, но вполне рабочее решение для однотипных запросов, где мы забираем из JSON только одно поле (например, EXANTEOHLC) — использовать кэш в глобальных переменных. Более правильное решение — в одном запросе (например, для свечек) посылать списки из нескольких финансовых инструментов, разделенных запятой.


    Документация


    Опциональный пункт, который я упустил в ходе повествования. Можно оформить комментарии к функциям в соответствие с JSDoc и дополнительно добавить @customfunction, например:


    /**
     * mid (average between bid and ask) value
     * @param {string} symbol
     * symbol ID
     * @param {string} [timestamp]
     * dummy parameter for update feature
     * @returns {number} mid value for specified symbol
     * @customfunction
     */

    В таком случае пользователь увидит красивую справочку о том, как правильно использовать данную функцию, какие аргументы она требует и что возвращает. Следует отметить, что парсит гугл докстринг по своему усмотрению, но в целом очень похоже на JSDoc.




    На этом все. Кажется, теперь можно пользоваться и опубликовать. Только токен вырежьте :) Исходный код этого «скрипта» можно найти на гитхабе под MIT лицензией.

    EXANTE
    36,00
    Инвестиционная компания нового поколения
    Поделиться публикацией

    Похожие публикации

    Комментарии 2

      0
      Есть отличная возможность опубликовать этот скрипт как библиотеку, которую любой разработчик сможет быстро подключить к своему скрипту: https://developers.google.com/apps-script/guide_libraries#creating-a-library
        0
        не то чтобы я детально разбирался в этом вопросе… Но может быть не совсем удобно в связи с необходимостью авторизации в нашем сервисе и передаче/хранении токена, который, кстати, довольно большой, чтобы передавать его аргументов в функциях и не лишиться читаемости. Но за замечание спасибо

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое