PowerQuery. Загрузка данных из интернета

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » PowerQuery. Загрузка данных из интернета (Формулы/Formulas)

Viper25

Дата: Четверг, 21.07.2016, 12:56 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 93

Репутация: 4 ±

Замечаний: 20% ±

Excel 2007

На http://minfin.com.ua/currency/nbu/ курс НБУ представлен не в виде таблицы, а по конкретному дню.
Т.е. чтобы узнать курс, приходится выбирать интересующий день.
Все проще с курсом межбанка.
Делаешь через PowerQuery запрос на http://minfin.com.ua/currency/mb/archive/usd/ и получаешь готовую таблицу с датами и курсами.
Вопрос: "Как в Excel загрузить инфо с курсом НБУ в табличной форме за месяц"?
Например, используя PowerQuery.

Ответить

Nic70y

Дата: Четверг, 21.07.2016, 19:54 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8759

Репутация: 2273 ±

Замечаний: 0% ±

Excel 2010

вариант: рекордер и куча формул

К сообщению приложен файл: 9855773.xlsm (25.1 Kb)

ЮMoney 41001841029809

Ответить

SLAVICK

Дата: Четверг, 21.07.2016, 23:18 | Сообщение № 3

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

а я юдф-ку пользую:
[vba]

Код

Function курс(Optional CurrencyName As String = "usd", Optional ByVal RateDate As Date) As Double
'xml v6
    Dim n&, s, myRequest As Object, D As Double
    On Error Resume Next
    If CurrencyName = "" Then CurrencyName = "usd"
    If RateDate = 0 Then RateDate = Date
    If Len(CurrencyName) <> 3 Then Exit Function Else CurrencyName = UCase(CurrencyName)
    Set myRequest = New XMLHTTP60
     myRequest.Open "GET", "http://tables.finance.ua/ru/currency/official/~/1/" & Format(RateDate, "YYYY\/MM\/DD"), False
        myRequest.send
       s = myRequest.responseText
'       Debug.Print s
       n = InStr(1, s, "{""" & CurrencyName & """:")
       If n = 0 Then Exit Function
       s = Split(s, "{""" & CurrencyName & """:", 2)(1)
       s = Split(s, "}")(0)
       D = Val(s)
       курс = D
End Function

[/vba]
только она не с минфина берет данные.
В приложении пример на этот месяц - дальше можно просто тянуть формулу вниз
еще тут была похожая тема

К сообщению приложен файл: kurs.xlsm (16.6 Kb)

Иногда все проще чем кажется с первого взгляда.

Ответить

krosav4ig

Дата: Пятница, 22.07.2016, 03:55 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Цитата Viper25, 21.07.2016 в 12:56, в сообщении № 1

Например, используя PowerQuery.

Цитата Текст запроса PQ

let
aa = Web.Page(Web.Contents("http://minfin.com.ua/currency/nbu/"&DateTime.ToText(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Column1],"yyyy-MM-dd")&"/")),
bb = Table.RemoveRowsWithErrors(Table.AddColumn(aa, "Custom", each [Data][Дата]), {"Custom"}),
cc = Table.AddColumn(Table.RemoveColumns(bb,{"Custom"}), "Custom", each Table.ColumnNames([Data])){0}[Custom],
dd = Table.SelectColumns(Table.ExpandTableColumn(bb, "Data", cc),cc),
ee = Table.ReplaceValue(Table.ReplaceValue(dd,".",Text.Range(Text.From(1/2),1,1),Replacer.ReplaceText,cc),"+ ","+",Replacer.ReplaceText,cc),
ff = List.Range(cc,1,1){0},
gg = Table.RenameColumns(Table.TransformColumns(Table.SplitColumn(ee,ff,Splitter.SplitTextByDelimiter("#(lf)"),{ff,"Динамика"}),{},Text.Trim),{{ff, "Курс НБУ"}}),
hh = Table.TransformColumnTypes(gg,{{"Курс НБУ", type number}, {"Динамика", type number}, {"За неделю", type number}, {"Дата", type date}, {"Обновляется", type text}, {"КУРС К ГРИВНЕ", type text}})
in
hh

К сообщению приложен файл: PQ.xlsx (68.3 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Пятница, 22.07.2016, 19:16

Ответить

Viper25

Дата: Пятница, 22.07.2016, 12:32 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 93

Репутация: 4 ±

Замечаний: 20% ±

Excel 2007

krosav4ig, к сожалению, не могу разобраться.
Как Ваш файлик использовать?

Ответить

krosav4ig

Дата: Пятница, 22.07.2016, 20:19 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Упс, заменил файл
добавил в нем еще 1 запрос вот от сюда

Цитата Текст запроса

let
FDate=(date) => DateTime.ToText(date,"yyyy-MM-dd"),
tbl = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content]{0},
aa = Web.Page(Web.Contents("http://kurs.com.ua/arhiv/tablicy/"&tbl[Column1]&"/"&tbl[Column2]&"/"&FDate(tbl[Column3])&"/"&FDate(tbl[Column4])&"/nbu")),
bb = Table.ExpandTableColumn(Table.SelectRows(aa, each ([ClassName] = "archiveTable")), "Data",{"Дата", "Курс"}),
cc = Table.ReplaceValue(Table.SplitColumn(bb,"Дата",Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)),".",",",Replacer.ReplaceText,{"Курс"}),
dd = Table.TransformColumnTypes(Table.RenameColumns(Table.SelectColumns(cc,{"Дата.2", "Курс"}),{{"Дата.2", "Дата"}}),{{"Дата", type date}, {"Курс", type number}})
in
dd

данные для запросов берутся из таблиц (закрасил желтым)