Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Справочник курсов валют (Power Query)
 
#1
20.12.2019 18:27:20
Не получается создать справочник курсов валют.
Создаю список дат.
Создаю функцию, которая обращается к сайту Нацбанка и тянет оттуда дату.
При проверке функции все Ок, т.е. функция возвращает курс на указанную в соответствующем поле дату.
Далее создаю календарь, в который подтягиваю список дат. Добавляю столбец, который обращается к дате в конкретной строке и на этом все заканчивается.
Почему - непонятно.
При этом меня терзают смутные сомненья относительно того, что такое количество запросов сайт перенесет с трудом. Ведь обращение происходит по каждой дате в списке.
Подскажите, пожалуйста, куда копать и как оптимизировать запрос.
Ссылка на файл - https://1drv.ms/x/s!Amn-IZ0k3g6o1DK2dtHfTwELqNf1?e=tighNu
Изменено: vector1 - 20.12.2019 18:27:57
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#2
20.12.2019 19:24:59
Цитата
vector1 написал:
на этом все заканчивается
загрузка же идет. сделайте список дней на десять - загрузит же.
попробуйте так еще. не быстро. да и пустых дней столько
Код
let
    Источник = ListDates(#date(2019, 9, 20), 60, #duration(1, 0, 0, 0)),
    #"Преобразовано в таблицу" = Table.FromList(Источник, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Переименованные столбцы" = Table.RenameColumns(#"Преобразовано в таблицу",{{"Column1", "Дата"}}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Дата", type date}}),
    #"Added Custom" = Table.AddColumn(#"Измененный тип", "a", each let
    Source = Table.FromRecords(Json.Document(Web.Contents("https://bank.gov.ua/tables/exchangerates?date="&Text.From([Дата])&"&period=daily"))[data]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([cc] = "USD"))[rate]{0}
in
    #"Filtered Rows")
in
    #"Added Custom"
 
 
 
#3
21.12.2019 10:56:20
Есть большая таблица с продажами с 2000 года в функциональной (т.е. национальной валюте) с максимальной детализацией до продукта.
Нужен справочник валют, чтобы заджойнить таблицы и получить продажи в долларах.
Поэтому нужен весь справочник валют. За весь период.
Несколько дней назад еще можно было снимать курсы с сайта за период, а сейчас только на конкретную дату.
Я, конечно, направил запрос в нацбанк. Но ответят ли - большой вопрос :)
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#4
21.12.2019 13:38:36
Цитата
vector1 написал:
ZBook15G5|Xeon E-2186M|32GbRam
вы че за него четыре штуки баксов отвалили?
 
 
 
#5
21.12.2019 14:16:09
vector1, не хотите рассказать что за валюта и что за банк?
А если говорить вообще - то всегда можно получить курс валют на любой день например от европейского банка, и естественно нет необходимости запрашивать курс повторно - он не меняется задним числом...
 
 
 
#6
21.12.2019 19:32:36
Цитата
artyrH написал:
вы че за него четыре штуки баксов отвалили?
Да. Это был эксперимент, опыт. Хотел посмотреть на сколько быстрее будут отрабатываться запросы в Power Query, и как быстро будут открываться файлы Экселевские тяжелые. Результат - быстрее процентов на 50% (по сравнению с моим прошлым ноутом, там процессор тоже был неплохой, ССД и оперативки 24 Гбт).
Вывод - не стоит того. И мне подсказали, что там видеокарта - половина стоимости.
Цитата
Hugo написал:
не хотите рассказать что за валюта и что за банк?
Доллар и евро. Национальный банк Украины.
Они на днях обновили интерфейс (https://bank.gov.ua/markets/exchangerate-chart)
Раньше можно было выгрузить таблицу с курсами за период и стянуть ее с сайта.
Сейчас есть период, но выводится график (типа визуализация), а снизу ссылки на файл с данными за период.
Пока не понимаю как их забирать автоматом. Не хватает квалификации, к сожалению. Хоть вручную делай.
Николай Павлов лет восемь назад научил, как запрос написать. Я и сделал (см.вложенный файл). Хотел запрос в ПаверКвери переделать, но не успел, к сожалению.
Такой справочник удобен (по крайней мере, для меня). Из 1С данные выгружаются в национальной валюте за любой период, я сделал запрос в PQ, подцепил курсы из файла и сую их, куда нужно (склад, дебиторка, продажи, денежные потоки...). Красота… была.
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#7
21.12.2019 19:49:07
Цитата
Hugo написал:
нет необходимости запрашивать курс повторно - он не меняется задним числом
раз уже есть таблица с курсами почему бы при надобности не брать курс из этих таблиц, а не из интернета
 
 
 
#8
21.12.2019 20:16:39
Цитата
artyrH написал:
курс из этих таблиц, а не из интернета
Так оттуда и беру, но там-то они должны как-то появиться, в свою очередь.
А так как интерфейс на сайте банка изменился, я подумал, что можно использовать функцию в ПаверКвери, которая будет обращаться к ячейке с датой.
И вот тут-то у меня и произошел облом. Такое количество записей ПаверКвери не может отработать нормально.
Т.е. сейчас есть два варианта: все-таки пробовать как-то запрос наваять или вручную набивать/копипастить.

Вы можете спросить, зачем брать курс именно национального банка. Поясню.
Для исчисления курсовых разниц и признания долгов в бухучете должен применяться единый для всех индикативный курс, который и дает национальный банк. Помимо этого курса есть, конечно, и реальный межбанковский, и курс на черном рынке... Но важен именно индикатив, используя который я всегда получу корректную цифру продаж, например, по контрагенту-нерезиденту в валюте, имея в таблице только выгрузку в гривне (т.е. в национальной валюте).
Изменено: vector1 - 21.12.2019 20:24:58
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#9
21.12.2019 21:08:26
.
Код
let
    Источник = List.Dates(#date(2019, 1, 1), 350, #duration(1, 0, 0, 0)),
    #"Преобразовано в таблицу" = Table.FromList(Источник, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Преобразовано в таблицу", {{"Column1", type date}}, "sv-SE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1", "Column2", "Column3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Column3", type text}, {"Column2", type text}, {"Column1", type text}}, "ru-RU"),{"Column3", "Column2", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Сведено"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "a", each let

    Source = Xml.Tables(Web.Contents("https://old.bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=EUR&date="&[Сведено]))[[Table]],
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Table", {"rate"}){0}[rate]
in
    #"Expanded {0}"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Сведено", type date}})
in
    #"Changed Type1"
 
 
 
#10
22.12.2019 15:26:34
Попробую. Спасибо за идею!
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
Страницы: 1
Наверх