Excel таблица с замещающими облигациями с подтягиванием данных с ISS Московской биржи

Подготовил для вас таблицу Excel в которой данные подтягиваются автоматически.

Сама таблица находится тут: t.me/filippovich_money/1116

В таблице сейчас 114 выпусков и по ним автоматом рассчитываются следующие параметры:

1. Название
2. Купон, %
3. Валюта
4. Купон в валюте
5. НКД
6. Цена бумаги в %
7. Номинал бумаги
8. Цена бумаги в валюте
9. Цена в рублях
10. Дата погашения
11. Дата оферты
12. YTM
13. Дюрация
14. Количество купонных выплат в год
15. Объём торгов

Про то, как работает подтягивание формул с ISS Мосбиржи писали много раз, но много у кого вызывает вопрос того как подтягиваются автоматически курсы валют. Они находятся на отдельном листе и подтягиваются с официального сайта ЦБ: cbr.ru/currency_base/daily/
 

Расскажу более подробно про подтягивание курса валют с сайта ЦБ в Excel

1. Переходим в «Данные»
2. «Создать запрос» -> «Из других источников» -> «Из интернета»
3. В окно вставляем ссылку следующего вида: cbr.ru/currency_base/daily/
4. После нажатия «ОК» вы увидите следующее окно:
5. В «HTML-таблицы[2]» выбираем «Таблица 2» и нажимаем загрузить. Получите следующую таблицу на новом листе:

Данная таблица будет автоматически обновляться при каждом входе в Excel.


Теперь расскажу про то, как использовать эти курсы валют в нашей таблице Excel



У нас есть столбец «Валюта» и столбец «Цена бумаги в валюте»

Столбец «Валюта» имеет формулу вида:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=FACEUNIT");"//document//data//rows//row/@FACEUNIT")

Классическое подтягивание валюты валютной облигации. Чаще всего он будет давать ответ USD/EUR/CNY

Более интересная ситуация со столбцом «Цена в рублях»
Вот формула: 
=ЕСЛИ([@Валюта]=«USD»;[@[Цена бумаги в валюте]]*'Курс валют'!$E$14; ЕСЛИ([@Валюта]=«CNY»;[@[Цена бумаги в валюте]]*'Курс валют'!$E$44;[@[Цена бумаги в валюте]]*'Курс валют'!$E$16))

То есть тут стандартная формула с условиями, что если в поле «Валюта» указана определённая валюта, то цену бумаги в валюте умножаем на курс валюты с листа, где подтягиваются валюты с сайта ЦБ.