Power Query Веб-запросы №4. JSON, Котировки Yahoo Finance 2

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

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

Все уроки курса Веб-запросы в Power Query находятся на этой странице и в этом плейлисте.

Описание

Вы хотите получить котировки с сайта Yahoo Finance. Как обработать формат JSON?

Мы разберем еще 1 способ получения котировок с сайта Yahoo Finance при помощи Excel. Этот способ будет намного быстрее предыдущего.

Решение

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

• Text.From
• Number.From
• Date.From
• DateTimeZone.LocalNow
• Table.FromColumns
• Table.ReplaceValue

Сначала мы вычислим сегодняшнюю дату в формате UNIX TIME с помощью формулы:

Text.From((Number.From(Date.From(DateTimeZone.LocalNow()))-Number.From(Date.From("01.01.1970"))) * 86400)

После этого мы запросим данные с помощью API:

Json.Document(
      Web.Contents(
         "https://query1.finance.yahoo.com/v8/finance/chart/MCD?interval=1d&period1=1&period2="
            & today
            & ""
      )
   )

Дальше останется только привести данные к надлежащему виду.

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

• Json.Document
• Web.Contents
• Record.ToTable
• Table.Transpose
• Table.ExpandListColumn
• Table.Skip
• Table.ExpandRecordColumn
• Table.AddColumn
• Table.FromColumns
• Table.ReplaceValue
• Date.From
• Int64.From
• Replacer.ReplaceValue
• Text.From
• DateTimeZone.LocalNow
• Number.From

Код

let
   query = Json.Document(
      Web.Contents(
         "https://query1.finance.yahoo.com/v8/finance/chart/MCD?interval=1d&period1=1&period2="
            & today
            & ""
      )
   ), 
   chart = query[chart], 
   result = chart[result], 
   result1 = result{0}, 
   tab_from_record = Record.ToTable(result1), 
   get_value = tab_from_record{2}[Value], 
   get_tab_1 = Record.ToTable(get_value), 
   tab_transpose = Table.Transpose(
      Table.ExpandListColumn(get_tab_1, "Value")
   ), 
   rows_skip = Table.Skip(tab_transpose, 1), 
   col_expand_1 = Table.ExpandRecordColumn(
      rows_skip, 
      "Column1", 
      {"open", "close", "volume", "low", "high"}, 
      {"open", "close", "volume", "low", "high"}
   ), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand_1, 
      "Column2", 
      {"adjclose"}, 
      {"adjclose"}
   ), 
   tab_add_col_1 = Table.AddColumn(
      col_expand_2, 
      "Пользовательская", 
      each Table.FromColumns(
         {
            tab_from_record{[Name = "timestamp"]}[Value], 
            [open], 
            [high], 
            [low], 
            [close], 
            [adjclose], 
            [volume]
         }, 
         {"Date", "O", "H", "L", "C", "Adj C", "Vol"}
      )
   ), 
   get_tab_2 = tab_add_col_1{0}[Пользовательская], 
   col_replace_value = Table.ReplaceValue(
      get_tab_2, 
      each [Date], 
      each Date.From("1.1.1970")
         + #duration(Int64.From([Date] / 60 / 60 / 24) - 1, 0, 0, 0), 
      Replacer.ReplaceValue, 
      {"Date"}
   )
in
   col_replace_value

Этот урок входит в курс Веб-запросы в 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, чтобы извлечь список всех веб-страниц сайта.