Про получение курса валют функцией пользователя(UDF) при помощи VBA я уже писал в статье Получить курс валют от ЦБР. Но такой подход требует обязательного внедрения проекта VBA в файл, что не всегда удобно. Т.к. Power Query набирает популярность и доступна для всех последних версий Excel, то мне было интересно сделать с её помощью запрос получения курса валют как на одну отдельную заданную дату, так и за целый период.
Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Получение курса указанной валюты на заданную дату
Такое решение хорошо подойдет в случаях, если необходимо ежедневно обновлять курс для заданной валюты.
Самое простое - это обратиться к сервису ЦБР, который на указанную дату генерирует таблицу курсов всех валют в формате XML:
http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017
на выходе получается такая таблица:

<ValCurs Date="23.05.2017" name="Foreign Currency Market">
<Valute ID="R01010">
<NumCode>036</NumCode>
<CharCode>AUD</CharCode>
<Nominal>1</Nominal>
<Name>Австралийский доллар</Name>
<Value>42,0973</Value>
</Valute>
<Valute ID="R01020A">
<NumCode>944</NumCode>
<CharCode>AZN</CharCode>
<Nominal>1</Nominal>
<Name>Азербайджанский манат</Name>
<Value>33,1897</Value>
</Valute>
<Valute ID="R01035">
<NumCode>826</NumCode>
<CharCode>GBP</CharCode>
<Nominal>1</Nominal>
<Name>Фунт стерлингов Соединенного королевства</Name>
<Value>73,3467</Value>
</Valute>
<Valute ID="R01060">
<NumCode>051</NumCode>
<CharCode>AMD</CharCode>
<Nominal>100</Nominal>
<Name>Армянских драмов</Name>
<Value>11,7120</Value>
</Valute>
<Valute ID="R01090B">
<NumCode>933</NumCode>
<CharCode>BYN</CharCode>
<Nominal>1</Nominal>
<Name>Белорусский рубль</Name>
<Value>30,5069</Value>
</Valute>
<Valute ID="R01100">
<NumCode>975</NumCode>
<CharCode>BGN</CharCode>
<Nominal>1</Nominal>
<Name>Болгарский лев</Name>
<Value>32,2574</Value>
</Valute>
<Valute ID="R01115">
<NumCode>986</NumCode>
<CharCode>BRL</CharCode>
<Nominal>1</Nominal>
<Name>Бразильский реал</Name>
<Value>17,3666</Value>
</Valute>
<Valute ID="R01135">
<NumCode>348</NumCode>
<CharCode>HUF</CharCode>
<Nominal>100</Nominal>
<Name>Венгерских форинтов</Name>
<Value>20,4558</Value>
</Valute>
<Valute ID="R01200">
<NumCode>344</NumCode>
<CharCode>HKD</CharCode>
<Nominal>10</Nominal>
<Name>Гонконгских долларов</Name>
<Value>72,5693</Value>
</Valute>
<Valute ID="R01215">
<NumCode>208</NumCode>
<CharCode>DKK</CharCode>
<Nominal>10</Nominal>
<Name>Датских крон</Name>
<Value>84,7758</Value>
</Valute>
<Valute ID="R01235">
<NumCode>840</NumCode>
<CharCode>USD</CharCode>
<Nominal>1</Nominal>
<Name>Доллар США</Name>
<Value>56,4988</Value>
</Valute>
<Valute ID="R01239">
<NumCode>978</NumCode>
<CharCode>EUR</CharCode>
<Nominal>1</Nominal>
<Name>Евро</Name>
<Value>63,1713</Value>
</Valute>
<Valute ID="R01270">
<NumCode>356</NumCode>
<CharCode>INR</CharCode>
<Nominal>100</Nominal>
<Name>Индийских рупий</Name>
<Value>87,4933</Value>
</Valute>
<Valute ID="R01335">
<NumCode>398</NumCode>
<CharCode>KZT</CharCode>
<Nominal>100</Nominal>
<Name>Казахстанских тенге</Name>
<Value>18,1902</Value>
</Valute>
<Valute ID="R01350">
<NumCode>124</NumCode>
<CharCode>CAD</CharCode>
<Nominal>1</Nominal>
<Name>Канадский доллар</Name>
<Value>41,7891</Value>
</Valute>
<Valute ID="R01370">
<NumCode>417</NumCode>
<CharCode>KGS</CharCode>
<Nominal>100</Nominal>
<Name>Киргизских сомов</Name>
<Value>83,3685</Value>
</Valute>
<Valute ID="R01375">
<NumCode>156</NumCode>
<CharCode>CNY</CharCode>
<Nominal>10</Nominal>
<Name>Китайских юаней</Name>
<Value>81,9738</Value>
</Valute>
<Valute ID="R01500">
<NumCode>498</NumCode>
<CharCode>MDL</CharCode>
<Nominal>10</Nominal>
<Name>Молдавских леев</Name>
<Value>30,8147</Value>
</Valute>
<Valute ID="R01535">
<NumCode>578</NumCode>
<CharCode>NOK</CharCode>
<Nominal>10</Nominal>
<Name>Норвежских крон</Name>
<Value>67,3118</Value>
</Valute>
<Valute ID="R01565">
<NumCode>985</NumCode>
<CharCode>PLN</CharCode>
<Nominal>1</Nominal>
<Name>Польский злотый</Name>
<Value>15,0459</Value>
</Valute>
<Valute ID="R01585F">
<NumCode>946</NumCode>
<CharCode>RON</CharCode>
<Nominal>1</Nominal>
<Name>Румынский лей</Name>
<Value>13,8349</Value>
</Valute>
<Valute ID="R01589">
<NumCode>960</NumCode>
<CharCode>XDR</CharCode>
<Nominal>1</Nominal>
<Name>СДР (специальные права заимствования)</Name>
<Value>78,0022</Value>
</Valute>
<Valute ID="R01625">
<NumCode>702</NumCode>
<CharCode>SGD</CharCode>
<Nominal>1</Nominal>
<Name>Сингапурский доллар</Name>
<Value>40,6993</Value>
</Valute>
<Valute ID="R01670">
<NumCode>972</NumCode>
<CharCode>TJS</CharCode>
<Nominal>10</Nominal>
<Name>Таджикских сомони</Name>
<Value>64,0939</Value>
</Valute>
<Valute ID="R01700J">
<NumCode>949</NumCode>
<CharCode>TRY</CharCode>
<Nominal>1</Nominal>
<Name>Турецкая лира</Name>
<Value>15,8260</Value>
</Valute>
<Valute ID="R01710A">
<NumCode>934</NumCode>
<CharCode>TMT</CharCode>
<Nominal>1</Nominal>
<Name>Новый туркменский манат</Name>
<Value>16,1679</Value>
</Valute>
<Valute ID="R01717">
<NumCode>860</NumCode>
<CharCode>UZS</CharCode>
<Nominal>1000</Nominal>
<Name>Узбекских сумов</Name>
<Value>14,9073</Value>
</Valute>
<Valute ID="R01720">
<NumCode>980</NumCode>
<CharCode>UAH</CharCode>
<Nominal>10</Nominal>
<Name>Украинских гривен</Name>
<Value>21,4467</Value>
</Valute>
<Valute ID="R01760">
<NumCode>203</NumCode>
<CharCode>CZK</CharCode>
<Nominal>10</Nominal>
<Name>Чешских крон</Name>
<Value>23,8261</Value>
</Valute>
<Valute ID="R01770">
<NumCode>752</NumCode>
<CharCode>SEK</CharCode>
<Nominal>10</Nominal>
<Name>Шведских крон</Name>
<Value>64,7217</Value>
</Valute>
<Valute ID="R01775">
<NumCode>756</NumCode>
<CharCode>CHF</CharCode>
<Nominal>1</Nominal>
<Name>Швейцарский франк</Name>
<Value>57,9594</Value>
</Valute>
<Valute ID="R01810">
<NumCode>710</NumCode>
<CharCode>ZAR</CharCode>
<Nominal>10</Nominal>
<Name>Южноафриканских рэндов</Name>
<Value>42,7154</Value>
</Valute>
<Valute ID="R01815">
<NumCode>410</NumCode>
<CharCode>KRW</CharCode>
<Nominal>1000</Nominal>
<Name>Вон Республики Корея</Name>
<Value>50,5277</Value>
</Valute>
<Valute ID="R01820">
<NumCode>392</NumCode>
<CharCode>JPY</CharCode>
<Nominal>100</Nominal>
<Name>Японских иен</Name>
<Value>50,7193</Value>
</Valute>
</ValCurs>

Останется только правильно получить эту таблицу. Идем на вкладку Данные(Data) или Power Query -Получить данные(Get Data) -Из других источников() -Из интернета(From Web):
Получить данные из интернета PowerQuery
в появившемся окне в поле URL-адрес вписываем текст: http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017
URL-адрес
нажимаем ОК. Появится окно предпросмотра, в котором пока ничего интересного нет. В этом окне жмем внизу кнопку Изменить(Edit). Появится окно редактора запроса, в котором будет три столбца: Valute, Attribute:Date, Attribute:name. В заголовке столбца Valute жмем кнопку с двумя развернутыми стрелками, в выпадающем списке снимаем галочку Использовать исходное имя столбца как префикс(Use original column name as prefix) и подтверждаем нажатием Ок:
Раскрыть столбец
в результате PowerQuery развернет таблицу всех курсов валют на указанную дату:
Таблица курсов валют

Тут два неприятных момента:

  1. названия валют(столбец Name) у нас отображаются квадратиками(могут быть и ромбики и другие символы) вместо нормального "Евро", "Доллар США" и т.п. Все дело в кодировке. Power Query очень неохотно определяет кодировку запросов к файлам, в которых присутствуют русские символы. На своей практике ни разу не видел, чтобы определение было правильным :)
  2. для изменения даты необходимо будет либо каждый раз создавать новый запрос либо изменять вручную данные текущего через расширенный редактор

Но оба этих нюанса мы сейчас исправим. Удобнее всего сделать это через расширенный редактор: вкладка Главная -Расширенный редактор:
Расширенный редактор PowerQuery
Там будет следующий текст:
let
Источник = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017")),
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Attribute:Date", type date}, {"Attribute:name", type text}}),
#"Развернутый элемент Valute" = Table.ExpandTableColumn(#"Измененный тип", "Valute", {"NumCode", "CharCode", "Nominal", "Name", "Value", "Attribute:ID"}, {"NumCode", "CharCode", "Nominal", "Name", "Value", "Attribute:ID"})
in
#"Развернутый элемент Valute"

  • Чтобы русский текст отображался корректно надо изменить кодировку на 1251.Это самая распространенная кодировка с поддержкой кириллицы для интернета и файлов XML, поэтому в большинстве случаев можно смело указывать её.
    Для этого в тексте расширенного редактора ищем строку
    Источник = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017")),
    перед последней скобкой добавляем текст: , null, 1251
    Источник = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017"), null, 1251),
    и нажимаем Готово. Сразу увидим, что русский текст теперь отображается правильно.
  • Теперь сделаем запрос динамическим, чтобы каждый день подставлялась текущая дата. Для этого в строке расширенного редактора:
    Источник = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017"), null, 1251),
    заменяем непосредственно дату на такой текст:
    = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_daily.asp?date_req="&DateTime.ToText(DateTime.LocalNow(), "dd.MM.yyyy")), null, 1251),
    DateTime.LocalNow() - функция языка М, которая получает текущую локальную дату в формате даты-времени
    DateTime.ToText - функция языка М, которая преобразует дату в текстовое представление в указанном формате. В нашем случае нам нужен формат "dd.MM.yyyy". Его и указываем.

Все. Теперь список валют будет обновляться каждый раз на текущую дату. Остается дело за малым: установить фильтр на нужные типы валют(удобнее всего это делать через столбец CharCode) и удалить лишние столбцы.

Скачать файл с готовым запросом:

  Курс валют на дату.xlsx (44,1 КиБ, 2 256 скачиваний)

Курс валют за период с изменяемыми параметрами
Приведенный выше метод хорош, если курс надо получить на одну дату. А если надо получать курс валют за период дат? Например, чтобы потом использовать в таблице продаж для конвертации валюты в рубли. Плодить запросы не вариант. Однако можно использовать подключение к сайту ЦБР с параметрами. Для этого идем на вкладку Данные(Data) или Power Query -Получить данные(Get Data) -Из других источников() -Из интернета(From Web):
Получить данные из интернета PowerQuery
в появившемся окне в поле URL-адрес вписываем текст:
https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017
раньше использовался этот запрос:
http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01235&date_req1=01.01.2017&date_req2=23.05.2017&rt=1&mode=1
но после того, как ЦБ поменял структуру сайтов и запросов - теперь эта строка подключения не актуальна - будет ошибка
где
R01235 - код валюты для получения курса. R01235 - Доллар США.
01.01.2017 - начальная дата периода для получения курса
23.05.2017 - конечная дата периода для получения курса
После нажатия Ок PowerQuery может задуматься на несколько секунд, а то и на минуту-другую, все зависит от загруженности сайта и текущего соединения. После этого появится окно навигатора следующего содержания:
Навигатор PowerQuery
Нам нужна последняя таблица - С 01.01.2017 по 23.05.2017 Динамика курса валюты Доллар США. Выделяем её в окне и нажимаем Загрузить(Load), если все устраивает и Правка(Edit), если хотим что-то изменить. Т.к. я хочу чуть больше автоматизировать процесс, то нажимаю Правка(Edit).
Сперва неплохо бы научить этот запрос выдавать таблицу курсов с указанной даты и до текущей. Для это мы можем использовать тот же подход, что и выше для одной даты. Переходим в расширенный редактор(вкладка Главная -Расширенный редактор) и в строке
Источник = Web.Page(Web.Contents("https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017")),
вместо конечной даты записываем нужные функции:
https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To="&DateTime.ToText(DateTime.LocalNow(), "dd.MM.yyyy")
Однако такой подход не всегда удобен. Во-первых, не всегда нужен курс именно до текущей даты. Во-вторых, не всегда с однажды указанной. И в-третьих, очень хочется видеть курс не только для доллара США. Все это не очень сложно править руками прямо в запросе. Но ведь куда удобнее, когда можно задать даты и код валюты прямо на листе и оттуда же управлять таблицей курсов. Для этого применим способ, описанный мной в статье Относительный путь к данным PowerQuery - Вариант 2 - с применением умной таблицы на листе книги.

  • создаем таблицу с именем params, в которую и будем записывать начальную дату, конечную дату и код валюты (на основании этих данных будем получать таблицу курсов валют):
    Создание умной таблицы
  • переходим в наш запрос и меняем строку
    Источник = Web.Page(Web.Contents("https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017")),

    на такую:
    Источник = Web.Page(Web.Contents("https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ="&Excel.CurrentWorkbook(){[Name="params"]}[Content]{0}[Код валюты]&"&UniDbQuery.From="&Excel.CurrentWorkbook(){[Name="params"]}[Content]{0}[Д1]&"&UniDbQuery.To="&Excel.CurrentWorkbook(){[Name="params"]}[Content]{0}[Д2])),

Это позволит передавать в запрос данные из созданной на листе таблицы(params):

  • Код валюты - указывается код валюты. Все коды можно получить при помощи запроса, который рассматривается в самом начале статьи: там на выходе получается таблица всех имеющихся валют
  • Д1 - начальная дата. Начиная с этой даты будут браться курсы валют
  • Д2 - конечная дата. Последняя дата, для которой будут браться курсы валют

подробно принцип передачи данных в запрос из таблиц книги я описывал в статье Относительный путь к данным PowerQuery, здесь же на примере кода валюты кратко изложу суть:

  • Excel.CurrentWorkbook() - функция получения данных обо всех умных таблицах внутри книги Excel, в которой создан этот запрос(CurrentWorkbook - текущая книга)
  • {[Name="params"]} - ссылка на таблицу с именем "params"
  • [Content] - все содержимое таблицы "params"
  • {0} - номер строки в указанной таблице("params"), начиная с 0, без учета заголовков
  • [Код валюты] - имя столбца, из которого надо получить данные

Два главных момента:

  1. даты в таблицу на листе необходимо записывать в текстовом формате, а не в формате даты. Иначе есть вероятность, что сайт не поймет значение как дату. Лучше всего использовать формат "ДД.ММ.ГГГГ". Можно перед записью даты просто поставить апостроф или назначить ячейкам формат текстовый. Для автоматической записи текущей даты в качестве Д2 можно использовать такую формулу:
    =ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")
    =TEXT(TODAY(),"dd.MM.yyyy")
  2. таблица должна находиться в книге с запросом курсов валют
  3. чтобы получить курс только на одну дату надо поставить одинаковые даты в столбцы Д1 и Д2

Теперь все, что останется сделать, это изменять параметры на свое усмотрение. После каждого изменения значения в таблице params запрос не обновится автоматом - его надо обновить принудительно. Для этого необходимо перейти на лист, с выгруженной результирующей таблицей, выделить любую ячейку в ней, перейти на вкладку Запрос(Query) и нажать Обновить(Refresh). Так же это можно сделать с вкладки Данные(Data) -Обновить все(Refresh all). Но в этом случае будут обновлены все запросы и сводные таблицы, что не всегда нужно, особенно если запросов много.

И один бонус: чтобы в этом варианте можно было получить курс за период не на одну валюту, а на несколько указанных и для каждой указать свой период, можно перейти в расширенный редактор и заменить весь текст в нем на такой:

let
    params = Excel.CurrentWorkbook(){[Name="params"]}[Content],
    rrows = Table.RowCount(params),
       GetWebContent = (lr as number) as table =>
        let
            surl = "https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ="&params{lr}[Код валюты]&"&UniDbQuery.From="&params{lr}[Д1]&"&UniDbQuery.To="&params{lr}[Д2]
        in
            Web.Page(Web.Contents(surl)){0}[Data],
    webContent = List.Transform({0..rrows-1}, each GetWebContent(_)),
    webContent2 = Table.Combine(webContent),
    PromHeaders1 = Table.PromoteHeaders(webContent2, [PromoteAllScalars=true]),
    PromHeaders2 = Table.PromoteHeaders(PromHeaders1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(PromHeaders2,{{"Дата▼", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Changed Type", "Курс на единицу", each [Курс]/[Единиц])
in
    #"Добавлен пользовательский объект"

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

let
    params = Excel.CurrentWorkbook(){[Name="params"]}[Content],
    rrows = Table.RowCount(params),
       GetWebContent = (lr as number) as table =>
        let
            surl = "https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ="&params{lr}[Код валюты]&"&UniDbQuery.From="&params{lr}[Д1]&"&UniDbQuery.To="&params{lr}[Д2]
        in
            Table.AddColumn(Web.Page(Web.Contents(surl)){0}[Data], "Код валюты", each params{lr}[Код валюты]),
    webContent = List.Transform({0..rrows-1}, each GetWebContent(_)),
    webContent2 = Table.Combine(webContent),
    PromHeaders1 = Table.PromoteHeaders(webContent2, [PromoteAllScalars=true]),
    PromHeaders2 = Table.PromoteHeaders(PromHeaders1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(PromHeaders2,{{"Дата▼", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Changed Type", "Курс на единицу", each [Курс]/[Единиц])
in
    #"Добавлен пользовательский объект"

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

Скачать файл с готовым запросом:

  Курс валют на дату.xlsx (44,1 КиБ, 2 256 скачиваний)

Единственное, на что я еще хотел бы обратить внимание и что важно в обоих описанных случаях: ЦБР возвращает ставку не одним полем, а двумя связанными: Курс и Единиц. Это означает, что значение в столбце Курс не есть окончательный верный курс. Для некоторых валют этот курс выводится из расчета не на одну денежную единицу указанной валюты, а на 10 или даже 100. Например, для Армянских драмов или Датских крон. Чтобы не попасть в неприятную ситуацию я советую добавлять в запросы еще один столбец, в котором просто делить столбец Курс на Единиц. Идем на вкладку Добавить столбец(Add Column) -Пользовательский столбец(Custom Column)
в появившемся окне указываем имя столбца(я его назвал Курс на единицу), а в поле Пользовательская формула столбца(Custom column formula) записываем следующую формулу:
=[Курс]/[Единиц]
Пользовательский столбец
в случае с методом получением курса на одну дату, описанном в начале статьи, это будет формула:
=[Value]/[Nominal]

Так же см.:
Получить курс валют от ЦБР
Power Query - что такое и почему её необходимо использовать в работе?
Получить данные из файлов XML при помощи Power Query
План-фактный анализ в Excel при помощи Power Query
Относительный путь к данным PowerQuery

Loading

20 комментариев

    1. Спасибо большое за статью. Очень мне помогла. Насчет настраиваемых дат с вводом вообще супер. Не поверите уже 10 дней ищу нечто подобное. Особенно правильно отметили насчет единиц валют. В некоторых валютах действительно идет по 100.

  2. Очень полезная статья, спасибо.
    Подскажите, пожалуйста, как имея таблицу с курсами валют на определенную дату перевести (конвертировать) с помощью Power Query значения с одной валюты в другую?
    Например, имеем транзакции в рублях, нужно показать в USD , с учетом курса RUB/USD на эту дату.

  3. ЦБРФ поменял линк на курсы валют и теперь этот способ не работает.
    Источник = Web.Page(Web.Contents("http://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2020&UniDbQuery.To=02.12.2020"))

    Подскажите как теперь видоизменить запрос по Вашему примеру?

      1. Спасибо! Но почему то конструкция &DateTime.ToText(DateTime.LocalNow(), "dd.MM.yyyy")& в Excel 2016 не работает. Выходит ошибка "Ожидался токен Comma".
        А приложенный к статье пример не открывается с указанием "....Эти запросы были созданы с помощью более новой версии Power Query и могут не работать в текущей версии". В итоге не отрабатывает с ошибкой [Expression.error] Аргументы 2 были переданы функции, которая ожидает 1. Неужели так сильно отличаются версии?

        1. Руслан, описанная ошибка относится к ошибке синтаксиса. Ожидалась запятая там, где Вы забыли её проставить. Внимательно посмотрите как именно чего и куда вставили.
          А про версии: да, все так сложно. Если Ваша версия устаревшая, то некоторые вещи могут не работать. Судя по сообщению - в какой-то функции сейчас два аргумента, вместо 1 в более ранней версии.

          1. Дмитрий, запрос такой
            let
            Источник = Web.Page(Web.Contents("https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=08.12.2020")),
            Data2 = Источник{2}[Data],
            #"Измененный тип" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
            #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
            #"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные верхние строки",{"Column2"}),
            #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные столбцы"),
            #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Курс", Currency.Type}})
            in
            #"Измененный тип1"

            При попытке внесения изменений по примеру (в источник - текущая дата, либо создание таблицы с указанием периода дат) выходит ошибка синтаксиса "Ожидался токен Comma". Ума не приложу где он возникает.

          2. Руслан, ну честное слово, Вы какую помощь ждете? Приведенный запрос никаких ошибок не выдает. Вы предлагаете гадать что, как и куда вставляете и как изменяете запрос? У меня нет на это ни времени, ни желания. В таком виде это просто засорение комментариев. Еще раз: хотите пообсуждать и погадать - обратитесь на форум, пообсуждаем.

  4. Все работает на ура, спасибо.
    Есть один момент, курс ЦБ, устаноленный на пятницу, также является курсом ЦБ на понедельник, однако, при выгрузке с сайта по скрипту, даты, являющиеся понедельниками, попадать в выгрузку не будут. Можно ли как-то доработать запрос, чтобы в выгрузке были все дни года? Спасибо

    1. Артем, самое простое - создание отдельной таблицы(списка) календаря за заданный период и потом просто слияние с ним. Например, создаете отдельный запрос(на примере дат из статьи):

      let
    d1 = Date.FromText(Excel.CurrentWorkbook(){[Name="params"]}[Content]{0}[Д1]),
    d2 = Date.FromText(Excel.CurrentWorkbook(){[Name="params"]}[Content]{0}[Д2]),
    Source = List.Dates(d1, Duration.Days(Duration.From(d2-d1)), #duration(1, 0, 0, 0))
in
    Source

      потом преобразуете этот список в таблицу и просто делаете слияние этой таблицы с таблицей курса валют. Сортируете по дате и заполняете пустые строки вышестоящими. Но это подойдет только если в запросе только одна валюта. С несколькими в одной таблице подход нужен другой.

  6. Здравствуйте. При помощи данной статьи сделал запрос курсов валют с сайта нацбанка РБ. Появился вопрос, как можно сделать чтобы после ввода даты в ячейку на листе и нажатия Enter автоматически запускался процесс обновления запроса? Сейчас приходится после ввода даты нажимать "Обновить все".

    1. Максим, это возможно только макросом. На событие листа, в котором ячейка с датой, надо записать код обновления. Т.е. жмете на ярлыке листа правую кнопку мыши -Посмотреть код(или Исходный текст). Вставляете код:

      Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        ThisWorkbook.RefreshAll
    End If
End Sub

      данный код будет обновлять все запросы при каждом изменении в ячейке В3. Если надо это делать по изменению другой ячейки(или диапазона) - просто замените В3 на нужный адрес.

  8. Здравствуйте! Запрос стал выдавать ошибку "Элементов в перечислении было недостаточно для выполнения операции", не подскажите, что нужно в нем подкорректировать?

    1. Да, Вы правы. Центробанк немного изменил структуру страницы и теперь появляется ошибка. Но изменить надо самую малость :) В этой строке:
      Web.Page(Web.Contents(surl)){2}[Data]
      заменить {2} на {0}.
      Код в статье и в файле я уже заменил.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.