• JavaScript
• Google API
• Блог компании EXANTE

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

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

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

Подготовка

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

Работа с API

Теперь создаем пустую таблицу и идем в редактор скриптов; если кто не знает, попасть туда можно путем вызова Tools → Script 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() (она одна из немногих умеет пересчитываться раз в минуту, при наличии специальной галочки в настройках Юзабилити), но ее значение нельзя передать в пользовательскую функцию, печаль.

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

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

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

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

Но методы обхода этой проблемы я предлагаю найти читателю самостоятельно :) Вероятно, не лучшее, но вполне рабочее решение для однотипных запросов, где мы забираем из 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 лицензией.