Как сделать импорт данных в Google таблицы

Многие сталкивались с тем, что не всегда удобно наблюдать за портфелем в приложении брокера или отчетах. Иногда хочется, например, посчитать комиссии за сделки или за обслуживание за месяц, за год в целом, в разных валютах по разным инструментам. Посчитать какой процент они отъели от портфеля. Насколько вообще были сделки прибыльны и т.д.

На помощь нам приходят таблицы Excel, однако с недавних пор стало популярно пользоваться таблицами Google, инструмент конечно мощный, в чем-то уступающий Excel а в чем-то наоборот его превосходящий, про него и пойдет речь в сегодняшней заметке. Постараюсь максимально кратко!

Многие спрашивают: "Как подтянуть данные по акциям в Google таблицы?".

На самом деле очень просто - для этого нужно использовать функцию: =GOOGLEFINANCE(код; [атрибут]; [дата_начала]; [дата_окончания|количество_дней]; [интервал])

Более подробно про нее можно прочитать тут. На примере будет понятнее: =GOOGLEFINANCE("ADBE", "price") - даст нам текущую цену по акциям Adobe, т.е. первый параметр это "тикет" акции, а второй это что именно нам нужно - цена, остальные параметры не обязательны. Есть особенность, что некоторые тикеты присутствую на разных рынках и торгуются в разной валюте, например Яндекс. Если мы напишем просто =GOOGLEFINANCE("YNDX", "price") - то получим долларовую цену с NASDAQ, чтобы получить рублевую цену с московской биржи - нужно сделать уточнение тикета, добавив вначале префикс биржы "MCX:" =GOOGLEFINANCE("MCX:YNDX", "price").

Следующий вопрос: "С акциями понятно, как сделать тоже самое по облигациям?"

К сожалению я не знаю, где взять информацию по облигациям торгующимся на иностранных биржах. Но я знаю как можно достать информацию по облигациям торгующимся на московской бирже. Есть специальный сайт, где есть нужная нам информация: http://iss.moex.com/iss/engines/stock/markets/bonds/

На первый взгляд ничего не понятно, но если копнуть глубже - то можно дойти до следующего XML: http://iss.moex.com/iss/engines/stock/markets/bonds/boards/EQOB/securities.xml

или

http://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml

Отличие в площадках - EQOB - Т0 Облигации - безадрес. - это наши корпоративные облигации, а TQOB - Т+: Гособлигации - безадрес. - это ОФЗ.

Для того чтобы достать оттуда данные нужно воспользоваться функцией: =IMPORTXML(ссылка; запрос_xpath)

Более подробно про функцию можно почитать тут. В нашем случае функция будет выглядеть так:

=IMPORTXML("http://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml","//row/@MARKETPRICE")

Преимущество функции в том, что она парсит целый столбец данных (все зависит от XPath конечно). Однако, если можно достать целый столбец по ОФЗ - это не удаётся сделать по корпоративным облигациям. По крайней мере мне не удалось, дело в том, что функция имеет ограничение на объем обрабатываемого XML, и если по ОФЗ их всего около 40 штук и файл небольшой, то корпоративных облигаций очень много и функция просто отказывается работать и выдает ошибку о превышении максимально возможного размера данных. В таком случае приходится обращаться к усеченному XML, по каждой конкретной бумаге, вот так:

=IMPORTXML("http://iss.moex.com/iss/engines/stock/markets/bonds/boards/EQOB/securities/RU000A0ZYSS5.xml","//row[@SECID='RU000A0ZYSS5']/@MARKETPRICE")

Популярные режимы торгов TQOD - для долларовых бондов, и TQCB - это Т+: Облигации- безадрес.

Последний вопрос, который мне адресовали: "Как подтянуть данные по ETF/БПИФ, которые обращаются на Московской бирже?"

Честно говоря, я не знал ответа на этот вопрос и не смог сам его найти сразу. Но люди в чате подсказали сначала направление, а затем и верное решение. Получить данные можно почти также, как и по облигациям, просто адрес чуть-чуть другой:

=IMPORTXML("http://iss.moex.com/iss/engines/stock/markets/shares/securities/VTBA.xml", "/document/data[@id=""marketdata""]/rows/row[@BOARDID=""TQTF""]/@LAST")

Прошу обратить внимание на необходимость указания режима торгов "TQTF", если вы хотите получить рублевую цену, "TQTD" если долларовую, и "TQTE" для ETF, торгуемых в евро.

Или вот так, если указать режим торгов заранее:

=IMPORTXML("http://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities/FXAU.xml"; "/document/data[@id=""marketdata""]/rows/row/@LAST")

Новый вопрос: где посмотреть режим торгов, тот самый board по интересующим бумагам?

Легче всего посмотреть на сайте МосБиржи вбиваете в поиск свою бумагу заходите на ее страничку и над последней ценой, указан основной режим торгов этой бумаги.

Как эти режимы закодированы можно посмотреть тут:

http://iss.moex.com/iss/engines/stock/markets/bonds/boards/ - по облигациям

https://iss.moex.com/iss/engines/stock/markets/shares/boards - по акциям

Если остались вопросы, можете задать их мне в телеграм. Я пока не очень хорошо разбираюсь в Google таблицах, поэтому если что будем разбираться вместе.