Страницы: 1
RSS
Возможность подгрузки курсов валют с сайта ЦБ для Excel на дату платежа
 
Доброго времени суток!

Столкнулся с задачкой на работе. Пример в таблице

У меня есть платежные поручения определенной даты и мне необходимо проверить верно ли вбит курс валют. Очень сложно каждый раз обращаться на сайт ЦБ и вбивать там нужный курс, а с учетом того, что строк бывает и под 5 тыс., то все это превращается в очень утомительное мероприятие.

Существует ли возможность автоматической подгрузки курсов соседний столбец?

Буду очень благодарен за помощь!!!
 
Цитата
Hjq написал:
Существует ли возможность
существует
Соблюдение правил форума не освобождает от модераторского произвола
 
Какой вопрос ,такой ответ
А можно получить практическое решение этой проблемы?
 
Ещё в "Приемах" есть статья.
 
Цитата
Hjq написал:
А можно получить практическое решение этой проблемы?  
А по ссылке, которую Вам дал buchlotnik,  ходили?
 
Цитата
Hjq написал:
практическое решение
согласен, функция на PQ будет практичнее:
Скрытый текст
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, по что вы так сайт ЦБР не взлюбили? ТС со своими 5к строк за ДДОСит же их. Да и долго придется ответа ждать. ИМХО проще и быстрее так:
Код
let
    GetCur = (d1,d2)=> Table.PromoteHeaders( Table.Skip( 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=R01239&UniDbQuery.FromDate=" & d1 & "&UniDbQuery.ToDate=" & d2 ) ){0}[Data], 1 ) ),
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    RemovedCurCol = Table.RemoveColumns(Source,{"Курс с сайта ЦБ"}),
    AddDate = Table.AddColumn(RemovedCurCol, "дата", each Date.From( Text.AfterDelimiter([#"Платежное поручение "], "от ") ), type date),
    minD = List.Min(AddDate[дата]),
    maxD = List.Max(AddDate[дата]),
    CurrencyTab = Table.Buffer( Table.TransformColumnTypes( GetCur( Text.From( minD ), Text.From( maxD ) ),{{"Дата", type date}, {"Курс", type number}}) ),
    Dates = Table.FromColumns( {List.Dates( minD, Number.From(maxD - minD) + 1, Duration.From(1) )}, type table [дата=date]),
    MergedQueries = Table.Join( Dates, {"дата"}, CurrencyTab, {"Дата"}, JoinKind.LeftOuter ),
    FilledDown = Table.FillDown( Table.Sort(MergedQueries,{{"дата", Order.Ascending}}), {"Курс"} ),
    MergedCur = Table.Join(AddDate, {"дата"}, FilledDown, {"дата"}),
    RemovedColumns = Table.RemoveColumns(MergedCur,{"дата", "Дата", "Единиц"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Курс", "Курс с сайта ЦБ"}})
in
    RenamedColumns

Всего один запрос к сайту.
Вот горшок пустой, он предмет простой...
 
Очевидно я совсем не шарю.

А можно где-то мануал найти о том куда это все надо вставлять и как пользоваться
 
Hjq, в табличку вставляете данные как в вашем примере, потом жмете по ней ПКМ и выбираете пункт "обновить". Можете прямо в файле, который я выложил нажать обновить - увидете результат, если у вас установлен 2016+ Эксель, либо 2010/2013 с установленной надстройкой Power Query.
Вот горшок пустой, он предмет простой...
 
В запросе "Таблица1" произошла ошибка. Expression.Error: Импорт Text.AfterDelimiter не соответствует ни одному из экспортов. Не пропущена ли ссылка на модуль?

Что-то не выходит у меня(((

Я могу в принципе делить ячейки по столбцам и вообще только дату оставить от платежного поручения, но так тоже не выходит  
Изменено: Hjq - 18.11.2019 10:25:47
 
Это у вас PQ старый, новые функции не распознает. Щас переделаю.
Пробуйте.
Изменено: PooHkrd - 18.11.2019 10:48:04
Вот горшок пустой, он предмет простой...
 
М-да...
Очевидно, что у меня все не очень с актуализацией Excel((((

Теперь новая ошибка, но я вообще не понимаю почему? там же всего одна строчка
Formula.Firewall: Запрос "Таблица1" (шаг "RenamedColumns") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных.

Наверное что-то с безопасностью на моем компе
 
Какой у вас Эксель?
Вот горшок пустой, он предмет простой...
 
Prof + 2016, но PQ наверное старый  
 
Цитата
Hjq написал:
PQ наверное старый
способ 3
 
Надо выставить настройки как на картинках. После этого желательно Эксель закрыть, потом файлик заново открыть и обновить табличку.

Изменено: PooHkrd - 18.11.2019 12:19:04
Вот горшок пустой, он предмет простой...
 
Это просто космос!

PooHkrd - спасибо тебе большое!  
 
Цитата
Hjq написал:
с учетом того, что строк бывает и под 5 тыс
- я в UDF встраивал словарь, где хранил уже скачанные за сеанс курсы (на 3 валюты, срок примерно пару месяцев, но тянул не с Банка России). Т.к. по задаче валют точно меньше (ни одной :) ), то запросов при таком способе думаю будет не особенно много, можно и по старинке UDF юзать :)
 
PooHkrd, а можно сделать так, чтобы он не ранжировал от большего к меньшему?
 
Hjq, в смысле? Надо сохранить изначальный порядок строк что ли? Если да, то надо в запросе сразу после загрузки источника создать столбец с индексами, а после погрузки к таблице курсов, надо по этому столбцу таблицу отсортировать и столбец удалить.
3 дня буду без компа, так что либо сами, либо ждите, может заглянет кто ещё поможет мой запрос подправить.
Вот горшок пустой, он предмет простой...
 
PooHkrd, то есть вот как я их вставил, чтобы в том порядке так и стояли. Есть такая возможность?
 
Цитата
Hjq написал: чтобы в том порядке так и стояли
Цитата
PooHkrd написал: надо в запросе сразу после загрузки источника создать столбец с индексами, а после погрузки к таблице курсов, надо по этому столбцу таблицу отсортировать и столбец удалить
 
Цитата
Hjq написал:
Есть такая возможность?
Вроде ж написал выше, что возможность есть и даже как это сделать расписал.
Вот горшок пустой, он предмет простой...
 
PooHkrd, в общем давно не пользовался запросом и выскочило это...

Подскажите, как исправить
 
ЦБ с тех пор переделал структуру сайта. Ссылки нужно поправить. Завтра, если найду время, помогу.
Вот, подправил чутка. Вроде фурычит.
Изменено: PooHkrd - 29.06.2020 09:23:08
Вот горшок пустой, он предмет простой...
 
PooHkrd, Фурычит на 100%. Знал бы ты, как я тебе благодарен)))
 
Цитата
Hjq написал:
как я тебе благодарен)))
Да какие вопросы, засылай! Сразу узнаю!  :D
Вот горшок пустой, он предмет простой...
 
PooHkrd, привет)

у меня опять со "столбцом дата" проблема((( ЦБ походу все время свой сайт меняет. Что нужно поправлять чтобы была возможность самому корректировать?
Страницы: 1
Наверх