Получить цены акций, фондов и ОФЗ в Google Sheets

Привет, Хабр!

Изучая информацию по работе с API в таблицах Google/Excel, понял лишь одно: я ничего не понимаю. Разбираться в XPath, в парсинге XML внутри формул — это всё как-то слишком сложно и громоздко. У меня стояла простая задача: разработать что-нибудь для получения текущей цены по конкретному активу с Мосбиржи. И, как мне кажется, у меня это получилось достаточно хорошо, чтобы можно было получать информацию и дальше агрегировать её так, как вам удобно. Также сразу поясню, что функция GOOGLEFINANCE больше не работает, поэтому остаётся искать другие способы решения.

Как работает скрипт

Внутри Гугл-таблицы вам нужно перейти на вкладку «Расширения» → «Apps Script». У вас откроется интерфейс создания и управления скриптами, куда мы и будем вставлять готовые функции, которые я привёл ниже.

Получить рыночную цену по конкретной Акции

function getStockPrice(ticker) {
  // Формируем URL с параметром тикера для получения актуальной цены
  var url = "https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/" + ticker + ".json";
  
  // Получаем данные с сайта
  var response = UrlFetchApp.fetch(url);
  
  // Преобразуем ответ в JSON
  var json = JSON.parse(response.getContentText());
  
  // Проверяем, есть ли данные
  if (json.securities.data.length > 0) {
    // Получаем актуальную цену 
    Logger.log(json.securities.data)
    var lastPrice = json.securities.data[0][3];  // 4-й элемент - это последняя цена акции
    
    Logger.log('Текущая цена акции ' + ticker + ': ' + lastPrice);
    return lastPrice;
  } else {
    Logger.log('Нет доступных данных для акции ' + ticker);
    return null;
  }
}

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

ticker — это уникальный для Мосбиржи код ценной бумаги, который вы можете найти на их сайте.

В интерфейсе Гугл-таблиц это будет выглядеть следующим образом:

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

В целом принцип работы ясен, поэтому ниже приведён блок кода для ОФЗ и ETF, поскольку они хранятся по разным адресам в API Мосбиржи.

Получить цену ETF

function getETFPrice(ticker) {
  // Формируем URL для получения данных по ETF
  var url = "https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities/" + ticker + ".json";
  
  // Получаем данные с сайта
  var response = UrlFetchApp.fetch(url);
  
  // Преобразуем ответ в JSON
  var json = JSON.parse(response.getContentText());
  
  // Проверяем, есть ли данные
  if (json.securities.data.length > 0) {
    // Получаем актуальную цену 
    var lastPrice = json.securities.data[0][3];  
    
    Logger.log('Текущая цена ETF ' + ticker + ': ' + lastPrice);
    return lastPrice;
  } else {
    Logger.log('Нет доступных данных для ETF ' + ticker);
    return null;
  }
}

Получить цену ОФЗ

function getOFZPrice(ticker) {
  // Формируем URL для получения данных по ОФЗ
  var url = "https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities/" + ticker + ".json";
  
  // Получаем данные с сайта
  var response = UrlFetchApp.fetch(url);
  
  // Преобразуем ответ в JSON
  var json = JSON.parse(response.getContentText());
  
  // Проверяем, есть ли данные
  if (json.securities.data.length > 0) {
    // Получаем актуальную цену 
    var lastPrice = json.securities.data[0][3];
    
    Logger.log('Текущая цена ОФЗ ' + ticker + ': ' + lastPrice);
    return lastPrice;
  } else {
    Logger.log('Нет доступных данных для ОФЗ ' + ticker);
    return null;
  }
}