Power Query Веб-запросы №2. HTML, получение данных по облигациям

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

Мы хотим получить данные по всем облигациям со страницы https://bonds.finam.ru/trades/today/rqdate7E30713/default.asp?order=4&resultsType=1&close=on&bid=&avg=&ask=&tradesOnly=0&page=0&resultsOnPage=&sectorId=2&subFieldId=»

Решение

Для решения мы воспользуемся функциями:

• Web.Contains
• Web.Page
• Text.Contains
• Table.FirstN
• Table.Skip
• List.FindText

Сначала мы создадим пользовательскую функцию, которая извлекает данные с одной страницы. Далее мы применим эту же функцию ко всем интересующим нас страницам.

Примененные функции

• Web.Page
• Web.Contents
• Table.SelectColumns
• Table.TransformColumnTypes
• Int64.Type
• Table.RemoveRowsWithErrors
• Table.AddColumn
• List.FindText
• Table.FromColumns
• Lines.FromBinary
• Table.AddIndexColumn
• Text.Contains
• Table.SelectRows
• Table.FirstN
• Table.Skip
• Table.TransformColumns
• Text.BetweenDelimiters
• Table.ReplaceValue
• Replacer.ReplaceText
• Replacer.ReplaceValue
• Table.ExpandTableColumn
• Text.AfterDelimiter
• Text.Trim
• Text.Remove

Код

Функция для обработки одной страницы:

(Ссылка) =>
   let
//      source = Table.Buffer(Web.Page(Web.Contents("https://bonds.finam.ru/issue/details01CD900002/default.asp"))),
      source = Table.Buffer(Web.Page(Web.Contents(Ссылка))), 
      Data = source{[ClassName = "light"]}[Data], 
      cols_select = Table.SelectColumns(
         Data, 
         {
            "Купоны №", 
            "Купоны Дата", 
            "Купоны Размер   (ден)", 
            "Погашение Размер   (ден)"
         }
      ), 
      col_type = Table.TransformColumnTypes(
         cols_select, 
         {{"Купоны №", Int64.Type}}
      ), 
      col_remove_errs = Table.RemoveRowsWithErrors(col_type, {"Купоны №"}), 
      tab_add_col_1 = Table.AddColumn(
         col_remove_errs, 
         "ISIN", 
         each List.FindText(
            source{[ClassName = "emissiondetailstbl"]}[Data][Column1], 
            "ISIN"
         ){0}
      ), 
      tab_add_col_2 = Table.AddColumn(
         tab_add_col_1, 
         "Название облигации", 
         each source
            {[ClassName = null, Source = "Service"]}
            [Data]
            [Children]
            {0}
            {[Name = "HEAD"]}
            [Children]
            {[Name = "TITLE"]}
            [Children]
            [Text]
            {0}
      )
   in
      tab_add_col_2

Применяем функцию ко всем интересующим нас ссылкам:

let
   source = Table.Buffer(
      Table.FromColumns(
         {
            Lines.FromBinary(
               Web.Contents(
                  "https://bonds.finam.ru/trades/today/rqdate7E30713/default.asp?order=4&resultsType=1&close=on&bid=&avg=&ask=&tradesOnly=0&page=0&resultsOnPage=&sectorId=2&subFieldId="
               )
            )
         }
      )
   ), 
   tab_add_index = Table.AddIndexColumn(source, "Индекс", 0, 1), 
   tab_add_col_1 = Table.AddColumn(
      tab_add_index, 
      "Пользовательская", 
      each 
         if Text.Contains([Column1], "<table")
            and Text.Contains([Column1], "light")
         then
            "Начало"
         else if Text.Contains([Column1], "</table") then
            "Конец"
         else
            null
   ), 
   rows_select = Table.SelectRows(
      tab_add_col_1, 
      each ([Пользовательская] <> null)
   ), 
   get_value_1 = rows_select{[Пользовательская = "Начало"]}[Индекс], 
   get_value_2 = Table.SelectRows(rows_select, each [Индекс] > get_value_1)[
      Индекс
   ]{0}, 
   rows_first_n = Table.FirstN(source, get_value_2), 
   rows_skip = Table.Skip(rows_first_n, get_value_1), 
   rows_select_2 = Table.SelectRows(
      rows_skip, 
      each Text.Contains([Column1], "http://")
   ), 
   col_transform = Table.TransformColumns(
      rows_select_2, 
      {
         {
            "Column1", 
            each Text.BetweenDelimiters(_, "http://", "'"""), 
            type text
         }
      }
   ), 
   col_replace_value = Table.ReplaceValue(
         col_transform, 
         "8/", 
         "2/", 
         Replacer.ReplaceText, 
         {"Column1"}
      ), 
   tab_add_col_2 = Table.AddColumn(
      col_replace_value, 
      "Пользовательская", 
      each Ф_Страница([Column1])
   ), 
   col_expand = Table.ExpandTableColumn(
      tab_add_col_2, 
      "Пользовательская", 
      {
         "Название облигации", 
         "ISIN", 
         "Купоны №", 
         "Купоны Дата", 
         "Купоны Размер   (ден)", 
         "Погашение Размер   (ден)"
      }
   ), 
   cols_transform = Table.TransformColumns(
      col_expand, 
      {
         {
            "Название облигации", 
            each Text.AfterDelimiter(_, ": "), 
            type text
         }, 
         {"ISIN", each Text.Trim(Text.AfterDelimiter(_, ":")), type text}, 
         {
            "Погашение Размер   (ден)", 
            each Text.Trim(Text.Remove(_, {"A" .. "Z"})), 
            type text
         }, 
         {
            "Купоны Размер   (ден)", 
            each Text.Trim(Text.Remove(_, {"A" .. "Z"})), 
            type text
         }
      }
   ), 
   cols_types = Table.TransformColumnTypes(
      cols_transform, 
      {
         {"Купоны №", Int64.Type}, 
         {"Купоны Дата", type date}, 
         {"Купоны Размер   (ден)", type number}, 
         {"Погашение Размер   (ден)", type number}
      }
   ), 
   col_repalce_value_2 = Table.ReplaceValue(
      cols_types, 
      null, 
      0, 
      Replacer.ReplaceValue, 
      {"Погашение Размер   (ден)"}
   )
in
   col_repalce_value_2

Этот урок входит в курс Веб-запросы в Power Query

Номер урока	Урок	Описание
1	Котировки Yahoo Finance	Вы хотите получить историю котировок акций MCD с 2000 года по текущий день с сайта Yahoo Finance. Вы копируете ссылку на раздел с историческими данными, вставляете ссылку в Power Query и получаете только 100 строк.
2	HTML, получение данных по облигациям	Мы хотим получить данные по всем облигациям со страницы bonds.finam.ru
3	Многостраничное извлечение	На разных страницах находятся сайта находятся котировки золота на каждый год. Нужно соединить все данные в одну таблицу.
4	JSON, Котировки Yahoo Finance 2	В этом уроке мы разберем еще один способ скачивания котировок с Yahoo Finance. Этот способ намного удобнее и быстрее. Здесь мы научимся обрабатывать формат JSON.
5	Котировки Московской биржи	В этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи. Московская биржа предоставляет возможность скачивать котировки и другую информацию по ценным бумагам при помощи специальных ссылок. Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.
6	Неразмеченный текст	Вы делаете запрос к Web-странице и сталкиваетесь с неразмеченным текстом. Вы хотите при помощи Power Query этот неразмеченный текст преобразовать в нормальную красивую таблицу.
7	Текстовый документ, веб-страница, List.Zip	В этом запросе мы научимся получать нужную информацию из текста веб-страницы.
8	Youtube Data API	В этом уроке мы научимся получать данные из Youtube Data API. Узнаем как получить данные о Youtube канале: список видео и плейлистов, названия, описания, длительность, тэги, количество лайков, дизлайков, просмотров и комментариев.
9	Личный OneDrive — подключаемся напрямую к файлам и папкам	В этом уроке мы разберем как напрямую подключиться к файлам и папкам на личном OneDrive.
10	Подключение Google Spreadsheets, Google Drive (Excel.Workbook, Web.Contents)	В этом уроке мы научимся подключаться к xlsx файлам, которые находятся в вашем Google Drive.
11	Получить все станции метро	Получим таблицу с перечнем станций метрополитена даже с координатами широты и долготы.
12	Получить ссылки с веб-страницы (Html.Table)	Получить URL с веб-страницы. Сделать запрос к каждому URL, получить таблицы и объединить их всех по вертикали.
13	Получить все URL с веб-страницы	Получим нужные URL с веб-страницы в Excel. Сделать запрос к каждому URL, получить таблицы и объединить их по вертикали.
14	Ищем работу через API Head Hunter (hh.ru)	Получим таблицу с перечнем вакансий с сайте HH. Получим вакансии, в названии или описании которых встречаются слова SQL, Pandas, Power Query, Power Pivot, Power BI.
15	Запрос к XML Sitemap	Сделаем запрос к sitemap.xml, чтобы извлечь список всех веб-страниц сайта.