Бывает, что нужно получить данные из внешних источников. Например, курсы валют, точное время, цены и ассортимент товаров. Откуда это получить? Из базы данных, из внешнего файла, из интернета. Давайте посмотрим как получить курсы валют и другую информацию прямо в таблицу Excel или в Google Sheets.
Уже довольно давно придуманы форматы для обмена данными XML и Json. И они как раз подойдут для нашей задачи. Заодно посмотрим как оно работает.
Получаем свежие курсы валют в Excel
Запускаем таблицу Excel. Создаём новую книгу. Далее я буду делать всё на Листе 3, а полученные данные транслировать на Лист 1. Данные будем получать с https://www.cbr-xml-daily.ru/ .
Переходим на вкладку Данные и в блоке Получение данных выбираем Из Веба. Можно выбрать Из других источников и вставить ссылку, приведённую ниже, но тогда данные нельзя будет настроить для обновления.
Скопируйте эту ссылку — https://www.cbr-xml-daily.ru/daily_utf8.xml
В строке Адрес окна Создание веб-запросов вводим ссылку, которую привел выше.
Нажимаем Пуск и когда страница загрузится нажимаем Импорт. Далее нужно будет выбрать лист и ячейки. По умолчанию это текущий лист и выделенная ячейка.
Вот что вы получите. Теперь вы можете копировать необходимые ячейки на нужный лист.
Внимание! Если вы удалите часть таблицы, по после обновления она появится вновь.
Теперь нужно настроить обновление. Снова переходим на вкладку Данные и в блоке Подключения нажимаем на Подключения. Среди списка текущих подключений выбираем нужное и нажимаем Свойства. В окне Свойства подключения выбираем период обновления. Жмём ОК.
Теперь мы можем на нужном листе в ячейках присвоить значения из полученной таблицы.
Котировки акций и биржевые индексы можно получить выбрав Существующие подключения. Они будут под заголовком Файлы подключений на этом компьютере.
Получаем свежие курсы валют в Google Sheets
Для начала переходим в вашу таблицу. В выбранную ячейку для отображения курса доллара США пишем код:
=IMPORTXML(«http://www.cbr.ru/scripts/XML_daily.asp»; «//ValCurs/Valute[CharCode=»»USD«»]/Value»)
Этот код, который на скриншоте выше, добавляет курс доллара США. Код Евро добавляется так:
=IMPORTXML(«http://www.cbr.ru/scripts/XML_daily.asp»; «//ValCurs/Valute[CharCode=»»EUR«»]/Value»)
Если вы хотите добавить другие валюты, то вам вместо EUR нужно написать название другой валюты. Вот полный список — http://www.cbr.ru/scripts/XML_daily.asp . Таким образом вместо EUR в примере для белорусского рубля пишем короткий код BYN.
Это минимум того, как можно использовать файлы форматов XML и Json. В следующих статьях расскажу о чем-нибудь более полезном.
Как сделать так, что-бы дата в запросе менялась относительны даты в ячейке? В гугл таблицах. К примеру если мне нужно прописать курсы валют на каждый день месяца, который присутствует в таблице? Сейчас приходится менять в каждой ячейке формулу в ручную. Заранее благодарен.
При обновлении страницы будут заново считаны исходные данные. Более интересные штуки можно получить работая с макросами. Например, использовать переменные, циклы