В последнее время на хабре стали появляться статьи, начинающиеся с рассказов про свободное время на самоизоляции и, как итог, появившихся троллейбусов из буханки. Возможно, администрации стоит задуматься о добавлении нового хаба — Самоизоляция..
Вот и у меня появилось свободное время, которое я посвятил анализу своих сделок в Тинькофф Инвестициях. Есть 2 типа людей: одни прекрасно строят многомерные массивы у себя в голове, пробегаясь по ним for-циклом в IPython Notebook, другим же нравится "щупать" цифры, раскладывая их по полочкам в Excel. Себя я отношу ко второй категории, поэтому все свои сделки аккуратно заносил в Google Sheets.
Под катом я расскажу, как автоматизировал свою рутину при помощи Google Apps Script и API от Тинькофф Инвестиций.
Перед тем как мы перейдём к сути, маленький словарик терминов, которыми я пользуюсь в статье:
- ТИ — Тинькофф Инвестиции
- Инструмент — любая ценная бумага, такая как акция, облигация или ETF.
- Ticker — короткий ID инструмента на бирже. Как правило, участники биржи знают тикеры тех инструментов, которые покупают или продают. По крайней мере я исхожу из этого в своём коде
- Figi — Financial Instrument Global Identifier (Финансовый Глобальный Идентификатор инструмента). Большая часть API-запросов ТИ принимает на вход именно figi.
- Стакан — таблица заявок на покупку и продажу конкретного инструмента.
Задача
У каждого инвестора или трейдера есть свой особый способ вести аналитику сделок. Кому-то достаточно тех инструментов, которые предоставляет брокер — дэшборд в личном кабинете или еженедельные отчёты. Мой способ такой: я веду отдельную таблицу по каждому инструменту, которым торгую. В этих таблицах я рассчитываю прибыль/убыток, определяю стратегию будущих сделок и всячески учусь на своих ошибках.
Какое-то время я заносил сделки вручную, перепечатывая их из мобильного приложения ТИ. Мне захотелось оптимизировать этот процесс. В поисках решения я наткнулся на статью хабраюзера OvkHabr. Из неё я узнал, что брокер предоставляет API, который полностью покрывает мои нужды и принялся за разработку.
Google Apps Script
Всё, что нужно, чтобы расширить возможности документа Google Sheets, это перейти в Tools -> Script editor, задать название проекта и начать писать код на JavaScript.
OpenAPI
Методы взаимодействия с ТИ реализованы с помощью OpenAPI, а сама документация представлена через swagger-ui
Авторизация представляет собой http-заголовок с токеном, который нужно посылать при каждом запросе. Процедура получение токена описана в ещё одной документации.
Для начала нужно набросать простенький клиент для http походов в ТИ.
Какие методы нам понадобятся?
- Получать описание инструмента, из которого будем брать figi
- Получать стакан инструмента, из которого будем брать актуальную цену
- Получать список сделок, отфильтровывая его по времени
Сделаем клиент классом, чтобы потом создать его единственный экземпляр и обращаться во всём верхнеуровневом коде:
class TinkoffClient {
constructor(token) {
this.token = token
this.baseUrl = 'https://api-invest.tinkoff.ru/openapi/'
}
_makeApiCall(methodUrl) {
const url = this.baseUrl + methodUrl
Logger.log(`[API Call] ${url}`)
const params = {'escaping': false, 'headers': {'accept': 'application/json', "Authorization": `Bearer ${this.token}`}}
const response = UrlFetchApp.fetch(url, params)
if (response.getResponseCode() == 200)
return JSON.parse(response.getContentText())
}
getInstrumentByTicker(ticker) {
const url = `market/search/by-ticker?ticker=${ticker}`
const data = this._makeApiCall(url)
return data.payload.instruments[0]
}
getOrderbookByFigi(figi) {
const url = `market/orderbook?depth=1&figi=${figi}`
const data = this._makeApiCall(url)
return data.payload
}
getOperations(from, to, figi) {
// Arguments `from` && `to` should be in ISO 8601 format
const url = `operations?from=${from}&to=${to}&figi=${figi}`
const data = this._makeApiCall(url)
return data.payload.operations
}
}
const tinkoffClient = new TinkoffClient(OPENAPI_TOKEN)Получение цены инструмента
Протестируем получившийся клиент на чём-нибудь простом, чтобы узнать что у нас всё работает. Например, получим цену акции yandex с тикером YNDX.
Custom Functions
Google Sheets предлагает большой выбор встроенных формул, таких как AVERAGE, SUM, или VLOOKUP. Но, когда этого недостаточно, мы всегда можем сделать свою. Всё, что для этого нужно, — обозначить функцию в .gs файле. То, что будет возвращать такая функция, будет вставляться в ячейку, которая вызвала функцию. Причём, если функция возвращает двумерный массив, то данные заполнят область справа и снизу, при условии, что там не будет занятых клеток.
Давайте сделаем функцию getPriceByTicker, которая будет возвращать текущую цену инструмента. Её мы будем использовать в качестве формулы в любой ячейке (=getPriceByTicker("YNDX")).
Для этого нам сначала нужно получить figi инструмента, а потом получить его стакан, из которого мы и вытащим цену:
function _getFigiByTicker(ticker) {
const {figi} = tinkoffClient.getInstrumentByTicker(ticker)
return figi
}
function getPriceByTicker(ticker) {
const figi = _getFigiByTicker(ticker)
const {lastPrice} = tinkoffClient.getOrderbookByFigi(figi)
return lastPrice
}
Здорово! Теперь в нашем распоряжении есть отличный тул, при помощи которого мы можем получать текущую цену акции, и использовать её в расчётах.
Автообновление формулы
Для того, чтобы данные в таблице всегда были актуальными, хочется сделать эту формулу автообновляемой. Прямого способа сделать это нет, но GAS комьюнити придумало вот такой хак:
- Мы резервируем ячейку, в которую при каждом обновлении листа будет складываться случайное число
function onEdit(e) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() sheet.getRange('Z1').setValue(Math.random()) } - Эту ячейку мы будем указывать в качестве аргумента у тех функций, которым необходим периодический пересчёт, например
getPriceByTicker
=getPriceByTicker("YNDX", Z1)
Cache Service
Как мы видим, получая цену мы делаем аж 2 API-вызова. И если в случае похода за стаканом это оправдано, так как цена постоянно меняется, то figi у инструмента является константой. Чтобы сделать нашу формулу чуть быстрее и надёжней, воспользуемся Apps Script Cache Service. Это простое key-value хранилище, которое отлично справится с нашей задачей:
const CACHE = CacheService.getScriptCache()
function _getFigiByTicker(ticker) {
const cached = CACHE.get(ticker)
if (cached != null)
return cached
const {figi} = tinkoffClient.getInstrumentByTicker(ticker)
CACHE.put(ticker, figi)
return figi
}Получение списка сделок
Теперь получим список проведённых сделок по инструменту. Сделаем функцию getTrades, которая будет ходить за операциями по API, формировать двумерный массив с данными и возвращать его.
На вход будем получать тикер, а также временной интервал, по которому нас интересуют сделки. Для интервала сделаем дефолтные значения, чтобы каждый раз не бодаться с форматом ISO 8601, который требует на вход API.
Figi будем получать при помощи _getFigiByTicker, который мы уже реализовали выше.
function getTrades(ticker, from, to) {
const figi = _getFigiByTicker(ticker)
if (!from) {
from = TRADING_START_AT.toISOString()
}
if (!to) {
const now = new Date()
to = new Date(now + MILLIS_PER_DAY)
to = to.toISOString()
}
const operations = tinkoffClient.getOperations(from, to, figi)
...
}Взвешенное среднее
Исходя из документации, объект Operation возвращается нам в виде:
"operation": {
"id": "string",
"status": "Done",
"trades": [{
"tradeId": "string",
"date": "2019-08-19T18:38:33.131642+03:00",
"price": 0,
"quantity": 0
}],
"commission": {
"currency": "RUB",
"value": 0
},
"currency": "RUB",
"payment": 0,
"price": 0,
"quantity": 0,
"figi": "string",
"instrumentType": "Stock",
"isMarginCall": true,
"date": "2019-08-19T18:38:33.131642+03:00",
"operationType": "Buy"
}Некоторые операции купли-продажи являются составными. Это обусловлено законами, по которым работает биржа: продавая 100 акций YNDX по 2500₽, в моменте может быть всего 40 предложений по цене 2500₽, и 60 предложений по цене 2499₽. Поэтому, как и было описано в статье у OvkHabr, часть данных о сделках лежит в биржевых операциях — подмассиве trades.
Меня же интересует сводная информация по конкретной сделке, поэтому для определения цены, по которой она произошла, мы будем пользоваться взвешенным средним.
Для нашего примера с яндексом, взвешенное среднее считается так
А в коде это будет выглядеть так
function _calculateTrades(trades) {
let totalSum = 0
let totalQuantity = 0
for (let j in trades) {
const {quantity, price} = trades[j]
totalQuantity += quantity
totalSum += quantity * price
}
const weigthedPrice = totalSum / totalQuantity
return [totalQuantity, totalSum, weigthedPrice]
}Работа с таблицей
Как было описано выше, если custom функция возвращает двумерный массив, данные займут всё необходимое свободное пространство под ячейкой с формулой. Соответственно, нам необходимо сформировать такой массив.
Мы будем итерироваться по операциям и биржевым сделкам, чтобы этот массив заполнить. Нас не интересуют отменённые операции, а также операции списания комиссии, но интересует само значение комиссии. Помимо этого, мы "на лету" будем присваивать минус операциям покупки (символизируя списание с нашего брокерского счёта) и плюс продажам. Таким образом, нам будет проще понимать текущую стоимость позиции (просто просуммировав столбец)
const values = [
["ID", "Date", "Operation", "Ticker", "Quantity", "Price", "Currency", "SUM", "Commission"],
]
for (let i=operations.length-1; i>=0; i--) {
const {operationType, status, trades, id, date, currency, commission} = operations[i]
if (operationType == "BrokerCommission" || status == "Decline")
continue
let [totalQuantity, totalSum, weigthedPrice] = _calculateTrades(trades) // calculate weighted values
if (operationType == "Buy") { // inverse values in a way, that it will be easier to work with
totalQuantity = -totalQuantity
totalSum = -totalSum
}
values.push([
id, isoToDate(date), operationType, ticker, totalQuantity, weigthedPrice, currency, totalSum, commission.value
])
}Остаётся только вернуть массив values. Итоговый код функции выглядит так:
function isoToDate(dateStr){
// How to format date string so that google scripts recognizes it?
// https://stackoverflow.com/a/17253060
const str = dateStr.replace(/-/,'/').replace(/-/,'/').replace(/T/,' ').replace(/\+/,' \+').replace(/Z/,' +00')
return new Date(str)
}
function _calculateTrades(trades) {
let totalSum = 0
let totalQuantity = 0
for (let j in trades) {
const {quantity, price} = trades[j]
totalQuantity += quantity
totalSum += quantity * price
}
const weigthedPrice = totalSum / totalQuantity
return [totalQuantity, totalSum, weigthedPrice]
}
function getTrades(ticker, from, to) {
const figi = _getFigiByTicker(ticker)
if (!from) {
from = TRADING_START_AT.toISOString()
}
if (!to) {
const now = new Date()
to = new Date(now + MILLIS_PER_DAY)
to = to.toISOString()
}
const operations = tinkoffClient.getOperations(from, to, figi)
const values = [
["ID", "Date", "Operation", "Ticker", "Quantity", "Price", "Currency", "SUM", "Commission"],
]
for (let i=operations.length-1; i>=0; i--) {
const {operationType, status, trades, id, date, currency, commission} = operations[i]
if (operationType == "BrokerCommission" || status == "Decline")
continue
let [totalQuantity, totalSum, weigthedPrice] = _calculateTrades(trades) // calculate weighted values
if (operationType == "Buy") { // inverse values in a way, that it will be easier to work with
totalQuantity = -totalQuantity
totalSum = -totalSum
}
values.push([
id, isoToDate(date), operationType, ticker, totalQuantity, weigthedPrice, currency, totalSum, commission.value
])
}
return values
}Проверяем работу в бою:
Заключение
В рамках статьи мы познакомились с API Тинькофф Инвестиций, возможностями, которые предлагает Google Apps Script, а также решили задачу автоматизации заполнения Google Sheets реальными сделками с брокерского счёта. Надеюсь, вам было интересно)
Весь код и короткий how-to выложен на github
Для тех читателей, кто хочет вступить на дорогу инвестирования, но не знает с чего начать — могу посоветовать бесплатный курс от Тинькофф Журнала https://journal.tinkoff.ru/pro/invest/ — он короткий, информативный и доходчивый.
А при открытии брокерского счёта в ТИ по моей ссылке вы получите акцию стоимостью до 20000 рублей в подарок.
Благодарю за внимание.
