Последнее обновление 24.04.2021 Антон Третьяк
Бывает, что нужно получить данные из внешних источников. Например, курсы валют, точное время, цены и ассортимент товаров. Откуда это получить? Из базы данных, из внешнего файла, из интернета. Давайте посмотрим как получить курсы валют и другую информацию прямо в таблицу Excel или в Google Sheets.
Уже довольно давно придуманы форматы для обмена данными XML и Json. И они как раз подойдут для нашей задачи. Заодно посмотрим как оно работает.
Еще интересное: Для чего буква Е в записи числа в Excel и как получить «нормальное» число
Содержание
Получаем свежие курсы валют в 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. В следующих статьях расскажу о чем-нибудь более полезном.
UPD 10.04.2021
Настройки импорта курса валют, валютных пар из интернета в Excel 2019
Excel вообще продвинулся очень хорошо в последние несколько лет. Мои любимые спарклайны, работа с данными — это вообще шедевр. Но наш любимый Эксель стал напоминать Фотошоп, без инструкции уже никуда. Поэтому продолжу. Сейчас получим пару EUR-USD из интернета. А в следующем посте покажу как рассылать автоматические обновления.
Искать исходные данные курсов валют или другую информацию можно в поисковике вбив что-то вроде xml eur usd или Free XML Currency Exchange Rate.
Запускаем наш Excel (у меня версия 2019, не люблю 365 версию).
Данные -> Из интернета -> Вставляем адрес нашей найденной таблицы в поле URL. Например, http://www.floatrates.com/daily/eur.json
Для другой основной валюты замените eur на другую. Например, на usd.
Картинки кликабельны!
Мы оказались в Power Query и видим таблицу значений. Рекомендую прочитать Учебник по Power Query. На этой же странице масса шаблонов на все случаи жизни.
Выбрали валюту для пары. Справа видим дерево операций. Всегда можно вернуться на любое количество шагов назад.
Удалю лишние строки.
Выполню сортировку относительно выбранной ячейки. Да, это влияет на сортировку.
Все получилось как надо: валюта, дата, курс, обратный курс.
Но это не все. Нужно настроить автоматизацию.
Тут вы вольны выбрать любые настройки получения данных.
Вот, что в итоге получилось.
Данные можно скопировать из ячеек на любой лист или документ. Да, чуть не забыл, в Word это работает точно так же. Очень удобно!
Итак, мы получили получили пару EUR-USD из интернета в наш документ Excel.
Рейтинг статьи:
Подано голосов: 17
Загрузка...
Огонь! Новый офис с нейросетями работает, ещё бы оповещения слать мог — цены бы не было. Получил сам курсы, сам проанализировал, прислал уведомления если покупать надо
Какую формулу ввести, чтобы считалась пара EUR-USD?
Простите, не обратил внимания на ваш комментарий. Всё просто, наверное. Взять две валюты и преобразовывать (поделить друг на друга) их самостоятельно, если это не критично. Сейчас дополню пост для Excel 2019. Там чуть иначе.
Пост дополнил. Надеюсь, что поможет
Как сделать так, что-бы дата в запросе менялась относительны даты в ячейке? В гугл таблицах. К примеру если мне нужно прописать курсы валют на каждый день месяца, который присутствует в таблице? Сейчас приходится менять в каждой ячейке формулу в ручную. Заранее благодарен.
При обновлении страницы будут заново считаны исходные данные. Более интересные штуки можно получить работая с макросами. Например, использовать переменные, циклы