
Описание
В этом уроке мы научимся получать прямо в 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