Получаем котировки ОФЗ

Подписчики, вы – золото. Ваши вопросы и реакции дают повод для новых постов.
Вот как и сейчас. Когда-то я публиковал несколько материалов про загрузку котировок финансовых инструментов напрямую в Excel через API Мосбиржи.

Если ты еще не видел, то обрати внимание:
— Загружаем курс валюты с сайта ЦБ
— PRO. Получаем данные с MOEX. Часть 1
— PRO. Получаем данные с MOEX. Часть 2
— PRO. Получаем данные с MOEX. Часть 3

🔅🔅🔅

Так вот, я получил вопрос: как загрузить котировки ОФЗ? Представленная в материале формула не загружает котировки.

Проверил, корпоративные облигации загружаются, а ОФЗ нет.
Поковырял 10 минут формулу и решил проблему с получением котировок ОФЗ.

Что я сделал?

🌿 За основу взял прошлую формулу:

=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/BONDS/boards/TQCB/securities.xml?securities="&A2&"&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVPRICE");"//row/@PREVPRICE")

Где А2 – ссылка на ячейку с ISIN облигации.

🌿 Корпоративные облигации отображаются в режиме торгов TQCB, но все ОФЗ находятся в режиме торгов TQOB. Поэтому я поменял TQCB в формуле на TQOB.

🌿 Данные по облигациям загружаются не по ISIN бумаги (хитровыдуманная кодировки), а по коду бумаги. Поэтому в ячейку А2 я подставил код бумаги. Давай на примере покажу, чтобы стало ясно:

Корпоративная облигация
— ISIN: RU000A0JS4J1
— Код бумаги: RU000A0JS4J1

ОФЗ
— ISIN: RU000A1014N4
— Код бумаги: SU26232RMFS7

Посмотри, ISIN и код у корпоративной бумаги совпадает, но формула работает именно по коду бумаги.
Если хочешь сделать универсальный поиск, то работай с кодами бумаги (в примере это SU26232RMFS7).

Финальная формула:

=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/BONDS/boards/TQOB/securities.xml?securities="&A2&"&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVPRICE");"//row/@PREVPRICE")

Спасибо за вопросы, дорогие 💎
Подписывайся на Hello Excel

*данный материал не является индивидуальной инвестиционной рекомендацией

#PRO

❤ Рабочая неделя настигла тебя? Будь быстрее!

Доброе утро, табличные аллигаторы ☕️

Я люблю делиться приемами, которые спасают меня в моих рабочих задачах.
Сегодня речь пойдет об одном из них.

Перед тобой монструозная таблица с кучей объединений, группировок, данные сбоку, сверху, по диагонали.
И шеф такой: надо бы зарплаты в таблице из гросс (НДФЛ) перевести «на руки».

Хочется закрыть ноутбук и выйти.

Этому городу нужен герой.

😼😼😼

Рассказываю как спасти себя на примере с НДФЛ:

1️⃣ В отдельной ячейке прописываю значение, на которое мне нужно умножить значения в таблице. Для примера это 0,87 (вычитаю НДФЛ). Копирую его.

2️⃣ Выделяю значения, которые нужно изменить. Нажимаю ПКМ – Специальная вставка.

3️⃣ Выбираю режим Умножить и нажимаю ОК. Готово, все значения умножены на 0,87.

Таким образом можешь проводить операции с НДФЛ, НДС, отчислениями в ФФОС и что твоей душе угодно. Помимо умножения в спец. вставке есть все математические операции.
Смотри видео и береги свои нервы👇

Вся польза на Hello Excel

🎉 Сюрприз, сюрприз, начало новой недели. Всем заряда!

⚡️⚡️⚡️

Готовьтесь, сегодня вечером будет мощный пост.

Будь быстр как молния

Итак, если бы можно было выбрать самые проверенные способы прокачать свою продуктивность в работе с Excel, то я бы выбрал именно эти.

Встречайте, новое бронебойное и сумасшедший сборник МОЛНИЕНОСНАЯ РАБОТА.

⚡️ 6 замечательных страниц с приемами, хватит 20 минут на изучение.
⚡️ Делаешь как написано, успеваешь сделать больше и лучше своих коллег.
⚡️ Для любого уровня подготовки.
⚡️ Издание в формате PDF в книжном формате (адаптация под экраны телефонов).

Скачивайте издание, это бесплатно, всё для вас.

И помните.
Лучшая благодарность - это подписка на канал 🙏
Если вам понравилось, то поделитесь этим постом с друзьями и коллегами.

Успехов, друзья!

Подписывайтесь на Hello Excel

Разбавим ленту полезными фичами

Шеф смотрит твой файл, все понятно, все классно, говорит: "сохраняй и высылай, но только округли цифры до миллионов".
Отчитываться о выручке в 436 550 675,55 руб. весьма тяжело. Куда проще сказать: 436,6 млн руб.

Это здравый смысл интерпретации данных.
Если вы готовите данные для выступления, презентации, то неплохо подумать об удобстве 👀


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

1️⃣ Выделяй ячейки со «страшными значениями», нажимай на ПКМ – Формат ячеек. Или просто нажми комбинацию Ctrl + 1.

2️⃣ Переходи на вкладку Все форматы. В поле Тип вписывай формат.

➖ # ##0 “млн руб.” – если хочешь округлить до целых миллионов. После нуля 2 пробела.
➖ # ##0 “тыс. руб.” – если округляешь до целых тысяч. После нуля 1 пробел.

Если тебе нужны знаки после запятой, то добавляй их после нуля: # ##0,00 “млн руб.”
Такой формат превратит 436 550 675,55 в 436,55 млн руб.

Смотри видео 👇

Подписывайся на Hello Excel

⏰ Доброе утро!
Всем бодрости и добрости на этой неделе)

Понедельник - день полезных штук

Иногда возникают ситуации, когда нужно показать где сохранен файл или как он называется. Вот натурально чтобы было значение в ячейке.
Ниже ты найдешь формулы, они помогут показать эти сведения.

🌿 Узнаем путь, имя файла и имя активного листа. Просто вставляй в любую ячейку формулу:
=ЯЧЕЙКА("filename")

🌿Нужно только имя файла? Тогда вставляй формулу:
=ПСТР(ЯЧЕЙКА("filename");ПОИСК("[";ЯЧЕЙКА("filename"))+1; ПОИСК("]";ЯЧЕЙКА("filename"))-ПОИСК("[";ЯЧЕЙКА("filename"))-1)


Ты не подписался на Hello Excel? Так чего ты ждешь!?

🎤 Интервью - дело тонкое

Часто приходится обрабатывать беспорядочные данные, пришедшие с опроса клиентов, опроса жителей района и много чего еще.

Один написал в адресе «улица», другой – «ул», следующий поставил точку и начал с заглавной – «Ул.».
И всё это нужно как-то подсчитать. Вручную можно обработать список из нескольких десятков ответов. А если там сотни или тысячи?

Давай посмотрим на пример опроса пользователей сайта, где заявку на ремонт можно подать разными видами. Мне как организатору опроса интересно, а сколько пользователей пользуются именно формой подачи заявки в виде файла Excel?

Задача: посчитать ответы, связанные с Excel.
Нюанс: каждый писал как хотел - документ Excel, заявка excel, кто-то word написал. Хаос.


📌 Делай раз. Вспоминаем замечательную функцию =СЧЁТЕСЛИ (диапазон; критерий).
В первом аргументе укажи ответы пользователей, во втором аргументе укажи, что хочешь найти – “Excel”. Нажимаешь Enter, а выходит 0. Как так?

Функция считает строго значения Excel. Ни слова слева, ни слова справа, ни других знаков.
Нужно исправить ситуацию.


📌 Делай два. Вспоминаем про маски. Так вот если ты заменишь критерий “Excel” на “*Excel*”, то ты найдешь все ответы, содержащие слово excel, при чем без учета регистра букв.

Звездочка или знак умножения – это обозначение неопределенного количества символов. Так ты подразумеваешь, что Excel может быть не просто словом, а еще и частью предложения, где будут символы и справа, и слева.

Смотри видео и пользуйся этим замечательным приемом 👇

Один раз Hello Excel? Всегда Hello Excel.

🙈 Всего 6 дней, крепитесь друзья!
Всем бодрости🔥

В один темный-темный день, в одной страшной шестидневной рабочей неделе, в одном стеклянном и холодном офисе, один менеджер по имени Витя копошился в своей огроменной табличке Excel.
Уже второй час он руками переносил строки из ячеек одного столбца в другой, потому что не мог автоматизировать эту рутину.

И вдруг позади менеджера появилась фигура, которая приближалась все ближе и ближе, пока он не услышал: «Витя ёпрст, не страдай, попробуй Текст по столбцам и пошли праздновать Тыквенный спас»

Бу 👻
Занавес.

Во-первых всех с хэллоуином, а может ..... с тыквенным спасом 😁

Во-вторых, если у тебя множество ячеек с текстом в 2-3 и больше строчек, то ты легко можешь извлечь информацию по строкам.

🎃 Выделяй столбец с данными. Заходи на вкладку ленты Данные – Текст по столбцам.

🎃 Выбирай формат «С разделителями», Далее.

🎃 Выбирай Другой символ разделитель, встань в пустое поле справа и нажми комбинацию Ctrl + J (перенос строки). Нажимай Готово.

Не будь как Витя, читай Hello Excel.

Огненный поиск

Ох как я люблю, когда название одного подрядчика в разных корпоративных системах / таблицах пишут по-разному.
В одной таблице это ООО «Огненные таблички», а в другой просто «Таблички» или «Огненные таблички».

Беда в невозможности применения ВПР и других поисковых функций.
Однако не все так печально. Делаем огненный поиск.

🔥🔥🔥

Делаем на примере простого ВПР:

1️⃣Вспомни, что символ подстановки «звездочка» обозначает любое количество знаков до / после.

2️⃣ В таблице, куда будем подтягивать должны быть неполные наименования подрядчиков или части наименования. Прописывай туда такую конструкцию:

= ВПР ("*"&D2&"*";A:B;2;0)

Если у нас в ячейке D2 написано Таблички, то мы говорим системе найди *Таблички*. То есть любое значение, где есть это слово.
Подтянется результат из таблицы с ООО «Огненные таблички».

Смотри видео 👇

Подписывайся на Hello Excel

Просыпаемся, заливаем топливо и врываемся в эту неделю 😼
Всем бодрости, табличные бандиты 😉