Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выгрузка таблиц/файлов с сайта с помощью power query
 
#1
21.11.2019 14:51:58
Добрый день.
Мало работал с power query с источником Web, поэтому ищу помощи и подсказки.
Подскажите, пожалуйста, возможно ли выгрузить файлы и/или таблицы через power query на примере следующих сайтов.

1) https://www.ishares.com/ch/individual/en/products/264107/ishares-spi-ch-fund
на сайте выше имеется кнопка выгрузки в Excel

Возможно ли указать источником ссылку на данный файл?


2) https://www.six-group.com/exchanges/funds/security_info_en.html?id=CH0237935652CHF4
по этой ссылке есть два варианта выгрузки через файл, как в примере выше.
А также есть обычные таблицы на странице сайта. Выделил красным оба варианта на скрине ниже
 
Изменено: reactortyz - 21.11.2019 14:58:23
 
 
 
#2
21.11.2019 15:40:33
По первой ссылке все можно сделать вот таким запросом
Код
Excel.Workbook(Web.Contents("https://www.ishares.com/ch/individual/en/products/264107/ishares-spi-ch-fund/1535604580403.ajax?fileType=xls&fileName=iShares-Core-SPI-ETF-CH_fund&dataType=fund"), null, true)

Но есть один нюанс, там на скачивании файл формата xls и PQ не распознает его формат.
По второй ссылке
Код
Csv.Document( Web.Contents("https://www.six-group.com/exchanges/funds/info_market_data_download.csv"),[Delimiter=",", Columns=12, Encoding=28591, QuoteStyle=QuoteStyle.None] )

Но там тоже вместо csv тянется код страницы.
Так что не судьба.
Вот горшок пустой, он предмет простой...
 
 
 
#3
22.11.2019 10:24:34
PooHkrd, жаль, конечно, но спасибо, что уделили время.
 
 
 
#4
22.11.2019 12:19:06
reactortyz, какие то данные можно получить
а вот как с html выцепить класс "icon-xls-export" и ссылку - не знаю. к тому же ссылка, как я понял, формируется скриптом.
Код
let
    Source = Csv.Document(Web.Contents("https://www.ishares.com/ch/individual/en/products/264107/ishares-spi-ch-fund/1535604580403.ajax?fileType=xls&fileName=iShares-Core-SPI-ETF-CH_fund&dataType=fund"),null,""">",null,1250),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Пользовательская", each if [Column1] = "</ss:Table>" then [Индекс] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Пользовательская"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Пользовательская"}, {{"Количество", each _, type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Количество"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Other Columns", "Индекс", 1, 1),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Index1", "Количество", {"Column1"}, {"Column1"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded {0}", {{"Column1", each Text.BetweenDelimiters(_, ">", "</ss:Data>"), type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Extracted Text Between Delimiters", each ([Column1] <> ""))
in
    #"Filtered Rows1"
Изменено: artyrH - 22.11.2019 12:22:31
 
 
 
#5
26.11.2019 15:32:08
artyrH, спасибо!
Если поиграться, то можно получить нужную информацию)

Задам попутно еще вопрос, можно ли как-то более рационально получить структурированную таблицу и как?
На шаге Filtered rows1 видно, информация идет в одинаковой очередности, но одном столбце.
Я сделал через индексы, но как-то по ощущениями очень коряво.
Прикладываю код и новый файл
Код
let
    Source = Csv.Document(Web.Contents("https://www.ishares.com/ch/individual/en/products/264107/ishares-spi-ch-fund/1535604580403.ajax?fileType=xls&fileName=iShares-Core-SPI-ETF-CH_fund&dataType=fund"),null,""">",null,1250),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Пользовательская", each if [Column1] = "</ss:Table>" then [Индекс] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Пользовательская"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Пользовательская"}, {{"Количество", each _, type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Количество"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Other Columns", "Индекс", 1, 1),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Index1", "Количество", {"Column1"}, {"Column1"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded {0}", {{"Column1", each Text.BetweenDelimiters(_, ">", "</ss:Data>"), type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Extracted Text Between Delimiters", each ([Column1] <> "") and ([Индекс] = 3)),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Filtered Rows1", "Индекс.1", 0, 1),
    #"Условный столбец добавлен" = Table.AddColumn(#"Добавлен индекс", "Пользовательская", each if Text.Contains([Column1], "/") then [Индекс.1] else null),
    #"Заполнение вниз" = Table.FillDown(#"Условный столбец добавлен",{"Пользовательская"}),
    #"Замененное значение" = Table.ReplaceValue(#"Заполнение вниз",null,0,Replacer.ReplaceValue,{"Пользовательская"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение", "Пользовательская.1", each [Индекс.1]-[Пользовательская]),
    #"Сгруппированные строки" = Table.Group(#"Добавлен пользовательский объект", {"Пользовательская.1"}, {{"Количество", each _, type table}}),
    
    Дата = Table.AddIndexColumn(
    Table.ExpandTableColumn(
    Table.SelectRows(#"Сгруппированные строки", each ([Пользовательская.1] = 0)),
    "Количество", {"Column1"}, {"Column1"}),
    "Индекс", 0, 1),
    
    NAV = Table.AddIndexColumn(
    Table.ExpandTableColumn(
    Table.SelectRows(#"Сгруппированные строки", each ([Пользовательская.1] = 2)),
    "Количество", {"Column1"}, {"Column1"}),
    "Индекс", 0, 1),
    #"Объединенные запросы" = Table.NestedJoin(NAV,{"Индекс"},Дата,{"Индекс"},"Дата",JoinKind.LeftOuter),
    #"Развернутый элемент Дата" = Table.ExpandTableColumn(#"Объединенные запросы", "Дата", {"Column1"}, {"Column1.1"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент Дата",{"Column1", "Column1.1"})
    in
    #"Другие удаленные столбцы"
Изменено: reactortyz - 26.11.2019 15:35:12
 
 
 
Страницы: 1
Наверх