В эпоху повальной автоматизации пользователям хочется «нажать на кнопку и получить ответ». Ну или дополнительно немного подвигать мышкой. Автоматизация же отчетов и других штук, которые удобно представить в виде таблички, часто строится в 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 лицензией.
ссылка на оригинал статьи https://habrahabr.ru/post/326906/
Добавить комментарий