Приветствую всех любителей Excel и аналитиков! Сегодня мы поговорим о том, как автоматически обновлять курс валюты прямо из базы данных Центробанка Российской Федерации (ЦБ РФ) с помощью инструмента Power Query.

Если вы работаете с финансовыми данными или ведете учет доходов/расходов в иностранной валюте, то актуальный курс валют — это очень важный показатель. Но постоянно проверять сайт ЦБ вручную может быть утомительно и неудобно. Именно здесь на помощь приходит замечательный инструмент Power Query! Благодаря Power Query можно настроить автоматическое обновление курсов валют каждый день, что значительно упрощает работу.

Шаг 1: Подготовка данных

Для начала подготовим наш Excel файл, ниже краткий список необходимых действий.

• Создание справочника с буквенными кодами валют.
• Использование формулы ДВССЫЛ для создания выпадающего списка с буквенными кодами валют.

• Преобразование в умные таблицы тех таблиц, которые будем загружать в Power Query.
• Использование формулы РАБДЕНЬ для расчета дат аванса и баланса (плановых).

• Использование формулы ЕСЛИ для выбора фактической или плановой даты аванса.

Далее переходим в Power Query, загружаем наши таблички для работы и творим волшебство )

Шаг 1: Загрузка основных данных в Power Query

1. Откройте свой файл Excel → Перейдите во вкладку «Данные» → Нажмите кнопку «Получить данные».
2. Выберите пункт «Из Интернета». В открывшемся окне вставьте URL страницы ЦБ РФ с интересующим вас типом данных (например, официальные курсы иностранных валют).
3. После загрузки данных появится окно предварительного просмотра таблицы. Нужно выбрать подходящую таблицу.
4. Закончите загрузку нажатием кнопки «Загрузить» → «Загрузить в», после чего ваши данные появятся в редакторе Power Query.

Шаг 2: Получение данных о курсе валют

• Настройка запроса из интернета для получения курсов валют.
• Получение данных о курсе валют с сайта ЦБ РФ.

Шаг 3: Создание функции для курса валют

• Создание функции для расчета курса валют в расширенном редакторе
• Использование аргументов ДатаКурса и ВалютаКурса.
• Преобразование даты в текстовый формат.

Шаг 4: Объединение запросов

• Объединение трех таблиц в новый запрос.
• Заполнение вверх столбцов, относящихся ко второму и третьему запросу.
• Удаление пустых строк (фильтр по первому столбцу, который всегда заполнен) с корректировкой формулы.

Шаг 4: Настройка курса на дату аванса

• Создание формулы расчета итогового Курса валюты аванса с использованием аргументов if, then, else и нашей пользовательской формулы.
• Добавляем столбец для курса на дату аванса.
• Проверяем, равен ли курс на дату оплаты аванса нулю.
• Если курс равен нулю, вызываем функцию курса валют с аргументами дата курса и валюта курса.

Итог:

Теперь вы можете обновить данные в любое время одним кликом мыши или настроив регулярное автоматическое обновление через меню «Сведения о подключении»

Заключение

Таким образом, с помощью простого запроса Power Query вы всегда будете иметь под рукой самые свежие курсы валют без необходимости постоянного ручного обновления информации.

Надеюсь, этот урок был полезен и упростит вашу ежедневную работу! Буду рад вашим комментариям и вопросам ниже 🌟