Файлы к уроку:
Все уроки курса Веб-запросы в Power Query находятся на этой странице и в этом плейлисте.
Описание
В этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи.
Решение
Московская биржа предоставляет возможность скачивать котировки и другую информацию по ценным бумагам при помощи специальных ссылок.
Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.
Вот ссылка на сайте Московской биржи https://iss.moex.com/iss/reference/ На этой странице вы можете увидеть какую именно информацию вы можете получить с сайта Московской биржи. Котировками дело не ограничивается.
Все ссылки конструируются примерно по одному шаблону. В этом примере мы разберем получение котировок при помощи Power Query, но если вам нужна какая-то другая информация, то вы по аналогии сможете создать похожую ссылку.
Наша ссылка будет выглядеть так — https://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml
В том месте, где написано SBER нужно вставить тикер интересующей вас ценной бумаги. Далее, развернув таблицу, вы получите таблицу с котировками, но только 100 строк. Это максимальное количество, которое позволено выгружать. Нам нужно что-то придумать, чтобы выгружать больше строк.
Что делать, чтобы получить много строк? Нужно использовать параметр start. Мы создадим столбец с числами с инкрементом 100 и в каждой строке будем запрашивать информация по ссылке.
Примененные функции
- Xml.Tables
- Web.Contents
- List.Numbers
- Table.FromList
- Splitter.SplitByNothing
- ExtraValues.Error
- Table.AddColumn
- Text.From
- Table.SelectColumns
- Table.ExpandTableColumn
- Table.TransformColumnNames
- Text.AfterDelimiter
Код
Пример получения 100 строк:
let
source = Xml.Tables(
Web.Contents(
"http://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml?start=300"
)
){0}[Table]{0}[rows]{0}[row]
in
sourceПример получения 4000 строк:
let
source = List.Numbers(1, 40, 100),
to_table = Table.FromList(
source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
table_add_col = Table.AddColumn(
to_table,
"Пользовательская",
each Xml.Tables(
Web.Contents(
"http://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml?start="
& Text.From([Column1])
)
){0}[Table]{0}[rows]{0}[row]
),
cols_select = Table.SelectColumns(table_add_col, {"Пользовательская"}),
col_expand_table = Table.ExpandTableColumn(
cols_select,
"Пользовательская",
{
"Attribute:BOARDID",
"Attribute:TRADEDATE",
"Attribute:SHORTNAME",
"Attribute:SECID",
"Attribute:NUMTRADES",
"Attribute:VALUE",
"Attribute:OPEN",
"Attribute:LOW",
"Attribute:HIGH",
"Attribute:LEGALCLOSEPRICE",
"Attribute:WAPRICE",
"Attribute:CLOSE",
"Attribute:VOLUME",
"Attribute:MARKETPRICE2",
"Attribute:MARKETPRICE3",
"Attribute:ADMITTEDQUOTE",
"Attribute:MP2VALTRD",
"Attribute:MARKETPRICE3TRADESVALUE",
"Attribute:ADMITTEDVALUE",
"Attribute:WAVAL"
},
{
"Attribute:BOARDID",
"Attribute:TRADEDATE",
"Attribute:SHORTNAME",
"Attribute:SECID",
"Attribute:NUMTRADES",
"Attribute:VALUE",
"Attribute:OPEN",
"Attribute:LOW",
"Attribute:HIGH",
"Attribute:LEGALCLOSEPRICE",
"Attribute:WAPRICE",
"Attribute:CLOSE",
"Attribute:VOLUME",
"Attribute:MARKETPRICE2",
"Attribute:MARKETPRICE3",
"Attribute:ADMITTEDQUOTE",
"Attribute:MP2VALTRD",
"Attribute:MARKETPRICE3TRADESVALUE",
"Attribute:ADMITTEDVALUE",
"Attribute:WAVAL"
}
),
types = Table.TransformColumnNames(
col_expand_table,
each Text.AfterDelimiter(_, ":")
)
in
typesЭтот урок входит в курс Веб-запросы в 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, чтобы извлечь список всех веб-страниц сайта. |
