Главная страница » Эксель как менять списком цену акций

Эксель как менять списком цену акций

  • автор:

Автоматизация сравнения акций в Excel

Не так давно начал интересоваться инвестициями и изучать вопрос (В свете последних событий, возможно, пора заканчивать). На всех ресурсах, которые я нашел, на одном листе чаще всего собраны показатели одной компании. Если хочется сравнить показатели разных компаний, то всё усложняется. Поэтому я сделал файл в Excel, который сам собирает данные по компаниям и разносит их в таблицу.

Ссылка на файл: Акции. Файл можно скачать к себе на компьютер и обновлять параметры компаний в часы работы биржи. Файл содержит макрос, поэтому вам при первом открытии нужно разрешить работу макросов. Всё, что вам нужно сделать, нажать кнопку «Обновить данные».

Сразу скажу, что я полный ноль в инвестициях и какие показатели хорошие, какие плохие, еще не понимаю. За основу таблицы брал таблицу Андрея Кононова:

«Когда мы покупаем доли в отличных компаниях с превосходным менеджментом, наш любимый срок инвестирования — навсегда», — говорил Баффет.

Какие показатели считать условно хорошими (в моей таблице выделены зеленым цветом) и условно плохими (красный цвет), я взял в его статье. Поэтому для полного понимания параметров компаний лучше обратиться к той статье. Я просто улучшил сбор данных: у Андрея К. часть информации надо было вбивать руками, у меня всё подтягивается автоматом с сайта Finviz.com. Столбцов с данными получается много, вы можете скрыть не нужные вам. Пока в таблице акции 20 компаний.

Также я добавил расчет «справедливой цены» акций по формуле из комментариев к этой статье:

Как получить котировки акций в Excel

Большинство из нас использует Майкрософт Эксель в нашей повседневной жизни разными способами. Мы используем его для отслеживания задач, которые должны быть выполнены с использованием цветового кодирования, и людей, занимающихся бизнесом, использовать его для отслеживания продуктов, продаваемых и проданных, и каждый использует их каким-то образом. Функции Excel облегчают нашу работу в минимизации задачи, которую мы выполняем. Существует множество встроенных формул, и вы можете даже создавать свои собственные функции для расширения функциональности. Помимо использования его для обычных вещей, вы можете даже вытащить или получить котировки акций в excel. Итак, давайте посмотрим как получить котировки акций в Excel.

Получить котировки акций в Excel

Чтобы получить котировки акций в excel, вам не нужно устанавливать дополнительное дополнение к вашему листу excel. Ты можешь использовать Котировки акций инвестора MSN MoneyCentral, встроенное соединение excel и вытягивание котировок акций. Я проведу вас через шаги, которые необходимо выполнить, чтобы достичь этого.

Откройте лист Excel и нажмите на вкладку «Данные». Затем нажмите «Подключения», который откроет «Подключения к книге» и нажмите кнопку «Добавить».

Выберите «MSN MoneyCentral Investor Stock Quotes» и дважды щелкните по нему. Нажмите «Свойства», чтобы открыть «Свойства подключения». Установите флажок «Включить обновление фона» и любые другие параметры, которые вам нужны. Нажмите «ОК» и «Закрыть».

Нажмите еще раз на вкладке «Данные» и нажмите «Существующие соединения». Выберите соединение, которое вы только что добавили, и нажмите кнопку «Открыть», или вы можете дважды щелкнуть по соединению.

Он открывает всплывающее окно «Импорт данных» и спрашивает, где разместить данные. По умолчанию он отображает $ A $ 1 (Cell ‘A1’) в качестве начальной ячейки. Вы даже можете выбрать диапазон ячеек, просто перетащив мышь по ячейкам и нажмите кнопку «ОК».

Он просит вас ввести значение параметра и добавить котировки акций, разделенные запятой. Проверьте параметры «Использовать это значение / ссылку для будущих ссылок» и «Обновить автоматически при изменении значения ячейки».

Чтобы обновить данные, выберите ячейку с данными и нажмите вкладку «Данные». Выберите «Обновить все», а затем «Обновить».

Он работает с Microsoft Excel 2007 и более высокими версиями, включая Excel 2013. Это простой способ получить котировки акций в Excel с помощью MSN Money. Вы можете даже просматривать и наметить и новости относительно каждой котировки акций. Нет необходимости создавать какие-либо макросы или добавлять сторонние надстройки. Просто добавьте встроенное соединение, и оно просто вытаскивает котировки акций в excel.

Вы знали об этом методе, чтобы вытащить котировки акций в Excel?

Теперь посмотрите, как можно добавить калькулятор Windows в панель быстрого доступа Excel.

Как вести учёт сделок в Excel

Рис. 1 Учёт денежных средств.

Откройте счёт с тарифом «Всё включено» за 5 минут, не посещая офис.

проект «Открытие Инвестиции»

Открыть брокерский счёт

Тренировка на учебном счёте

Об «Открытие Инвестиции»

Москва, ул. Летниковская,
д. 2, стр. 4

8 800 500 99 66

Согласие на обработку персональных данных

Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+

АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 г. (без ограничения срока действия).

ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.

Как я слежу за акциями в гугл-таблице: три простых способа

Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.

Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.

Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.

Что за АПИ

В большинстве случаев данные с бирж передаются через программный интерфейс, называемый API — application programming interface. Грубо говоря, это инструмент, благодаря которому одна программа начинает говорить на одном языке с другой. В нашем случае мы используем API финансовых бирж для «дружбы» с гугл-таблицами.

API Московской биржи

Для чего. Получить информацию о российских акциях и облигациях.

Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл-таблиц. Например, можно получать имена инструментов, цены закрытий, даты оферт и другие параметры.

Как упростить жизнь с помощью «Экселя»

Работа этой функции полностью зависит от сервера Московской биржи. Если биржа поменяет формат выдачи, организует какие-то другие режимы торгов или изменит что-то в существующей системе, то для корректной работы формул потребуется их доработка.

Такая выборочная информация с биржи пригодится для разных целей. Можно создать гугл-таблицу , чтобы покупать облигации с хорошей ликвидностью, — об этом я уже рассказывал. А можно следить за параметрами бумаг в собственном портфеле — например, за датами оферт. Это поможет не пропустить важную дату, если хотите принять участие в досрочном погашении.

Еще таблица пригодится, если вас не устраивают существующие сервисы учета — Intelinvest и Investing.com.

В таблице-примере к этой статье я привел несколько полезных параметров ценных бумаг. Вот что она умеет:

  • подтягивать название бумаги по идентификатору — столбец «Названия акций и облигаций»;
  • показывать текущие курсы — столбец «Цены акций и облигаций»;
  • получать даты выплат облигаций — столбец «Даты купона и значения для облигаций»;
  • получать дивиденды — столбец «Даты и значения дивиденда для акций»;
  • видеть возможность досрочного погашения облигаций — столбец «Даты оферт».

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

Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк — чтобы каждый смог воспроизвести эту конструкцию в своей таблице.

Googlefinance внутри гугл-таблиц

Для чего. Получить информацию об американских акциях, которые торгуются на Санкт-Петербургской бирже.

Как это работает. Googlefinance — это встроенная функция гугл-таблиц. Она помогает получить текущие или архивные данные о ценных бумагах из сервиса «Гугл-финансы».

Российские акции в сервисе тоже есть. Чтобы их увидеть, надо указывать тикер — то есть код из нескольких букв, который принадлежит определенному финансовому инструменту, — с приставкой «MCX:» так, чтобы получилось «MCX:SBER».

Эта функция позволяет получать название, текущую цену акций и еще 18 параметров — например, максимальную и минимальную цену за 52 недели, количество акций в обращении. Все это есть в моем шаблоне.

Главное преимущество в том, что формулы для получения этих параметров предельно просты: например, имя бумаги — name, самая высокая цена на текущий день — high. Описание всех формул и атрибутов есть в инструкции от Гугла.

В первом столбце я оставил идентификаторы двух акций — Сбербанка (MCX;SBER) и «Нетфликс» (NFLX) — и фонда Van Eck (RSX). При изменении тикеров во втором столбце будет подтягиваться название акций и актуальные цены.

А чтобы получить подробную информацию об акции или фонде, используйте ячейки В13 и В35: в них нужно занести тикер и в таблицах ниже сразу же обновится информация.

Например, вы хотите отслеживать акции американского производителя сетевого оборудования Ubiquiti Inc (UI) и высокодивидендного фонда Highland Small-Cap Equity Y (HSZYX). В ячейку В13 заносите тикер акции UI, а в ячейку В35 — тикер фонда HSZYX. Получите 19 параметров каждой бумаги.

Смотрите, как это работает:

«Яху-финанс»

Для чего. Отслеживать любые финансовые данные со всего мира.

Чтобы подружить «Яху» и таблицы, можно пойти двумя путями: написать скрипт, который будет импортировать выдачу API в специальном текстовом формате JSON в гугл-таблицу , или воспользоваться формулой IMPORTHTML — еще есть вариант с IMPORTXML, — которая импортирует таблицы и списки с сайтов.

Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.

Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.

Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования:

  • для российских акций после тикера нужно добавить приставку «.ME», чтобы получилось LKOH.ME;
  • для бумаг, торгующихся на немецкой бирже, нужно добавить «.DE». Например, чтобы посмотреть подробности по Bayerische Motoren Werke Aktiengesellschaft (BMW), надо написать BMW.DE;
  • для итальянской фондовой биржи Borsa Italiana S.p. A., ISE, расположенной в Милане, после тикера надо дописывать приставку «.MI», чтобы получилось UBI.MI.

Запомнить

  1. API Московской биржи в гугл-таблицах — практически самый быстрый способ получить данные с Мосбиржи.
  2. Встроенная функция Googlefinance внутри гугл-таблиц пригодится, если нет времени разбираться с формулами, но хочется видеть курсы акций.
  3. Любые финансовые данные со всего мира получайте в API «Яху-финанс» с помощью простой функции IMPORTHTML.
  4. Если вы только начинаете инвестировать, пройдите бесплатный курс от Т—Ж: на сайте или в приложении для iOS.

Загрузка

Пользователи Тинькофф Инвестиций могут следить за состоянием своего портфеля и через API заносить информацию в Google-таблицы. Тут написал об этом подробнее: https://habr.com/ru/post/516210

Dmitriy, А api тинькофф инвестиций уже научились в readonly токены? А то стрёмно

Георгий, вот в тему вопрос. Занялся сборкой своей таблицы чтобы видеть сколько дивов и когда получу и нет нормальных инструментов для этого. Какие то кривые приложения где нужно руками вбивать свои покупки. А токен страшно как то в гугл табицы вставлять свой)

Иван, я сделал связку гугл таблицы и сервис Доход.

Михаил Шардин

Святослав, Каким образом связали?

Михаил, формулами. В основном меня интересовали дивиденды на определенные компании.

Через IMPORTHTML И "TABLE".

Интересовали акции, которые больше 1 раза в год выплачивают дивиденды.

Спасибо за статьи, очень помогают.

Dmitriy, посмотрела публикацию на Хабре, вставила в таблицу токен. Курс доллара не подгрузился, портфель тоже. Бедапечаль(

Михаил Шардин

Лиза, а какой токен? Они же бесплатно

Dmitriy, что делать если после вставки токена в ячейку B1 в листе «Настройки», в ячейке B3 не появляется текущий курс доллара? Опробованы все возможные для получения в личном кабинете токены

Dmitriy, посмотрела статью на хабре, вставила токен в таблицу, курс доллара не появился, портфель не подгружается. Бедапечаль

Dmitriy, а как там посмотреть "Избранное"?

Михаил Шардин

Dmitriy, вы автор?

Михаил Шардин

Dmitriy, хорошая статья, спасибо ��

Функции импорта, если их хотя бы пара десятков на листе работают из Google Sheets крайне медленно.
Сам использую API мосбиржи, но лист с портфелем открывается с неизменной ошибкой с предложением снизить количество IMPORTXML и подобных.

Сергей, если импортить при помощи gscript то такой проблемы не будет

Михаил Шардин

Пончик, ну с ними с наскока не разобраться. Хотя это JavaScript.

Пончик, спасибо, попробую!

Михаил Шардин

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

Изначально делал колхоз на c++/php/html (локальный веб-сервер), портфель держу в csv, учитывая не только сами позиции, но и дивиденды/купоны, а также доходности по разным инструментам, группам и т.п.

Сейчас пишу приложение под андроид с тем же функционалом. Готовые не понравились 🙂

Михаил Шардин

Михаил, я ещё веду учёт поступлений по каждой покупке внутри позиции, т.е. в любой момент могу глянуть доходность, цену и дату покупки, поступления по сути каждого лота (точнее, их группы). Так же по FIFO уменьшаю начисленные дивиденды, если продаю часть прежней покупки.

Всё в виде веб-сайта, от таблиц я отказался 🙂

Михаил Шардин

Денис, вот мой опыт: https://habr.com/ru/post/477920/

Михаил Шардин

Денис, у вас сложная структура получается наверное.

Михаил, немного замороченно, да 🙂

Денис, О вот это интересно! Очень интересно, расскажите подробней плиз!

Иван, и вторая картинка.

Иван, сложно как-то подробно расписать 🙂 Есть сайт, он кушает csv — в них и портфель, и котировки. Котировки грузит отдельная программа с разных бирж через api, если не удалять файлы, можно смотреть состояние портфеля за прошлые даты. Также есть графики эквити + бенчмарки (таблицы с данными тоже в csv, собираются отдельно). В самом портфеле каждая покупка отдельно, в т.ч. и приходящиеся на неё дивиденды, это удобнее средней цены в терминале. Если нажать на тикер в общем списке (главная страница), то видны как раз покупки внутри одного тикера.

Для наглядности пара скринов не моего портфеля низкого риска (там нет ПД, так что ничего вроде не раскрываю :)).

Денис, можете поделиться этой таблицей или сайтом?

Михаил Шардин

Денис, своя система учета у вас 🙂

Очень познавательно! Обязательно буду использовать для своего портфеля. Не знала про api мосбиржи, использовала для этого скрипты на lua из quik

В заголовке статьи Эксель, а в тексте про него ни слова. Поправьте, пожалуйста.

Михаил Шардин

Alexander, да, спасибо. Исправили. Но про эксель я тоже писал: Free API Мосбиржи в формулах Microsoft Excel [ https://habr.com/ru/post/498268/ ].

Михаил, 365 — это не веб-версия, это просто версия, которую сейчас выдают с подпиской. Data Types в Excel 2019 не вошли, насколько я знаю (но «релизные» версии это сейчас скорее корпоративная история, а у реально работающих с рыночными данными людей должны быть расширения от Bloomberg или Refinitiv).

В веб-версии есть data types, но механизмы расширения Экселя (VBA, VSTO, JS add-ins, Office Scripts, что там еще) на каждой платформе свои. Это беда, конечно.

Михаил Шардин

rinat, используй ; как разделитель. Это зависит от локальных настроек таблицы. В данном случае попробуй =IMPORTxml("https://iss.moex.com/iss/securities/"&A31&".xml?iss.meta=off&iss.only=boards&boards.columns=secid,is_primary,boardid"; "//document//data//rows//row[@is_primary=1]/@boardid")

Pavel, можно конечно, но в таблице можно какие-то дополнительные вычисления производить, балансировку портфеля например, или строить собственные графики, а вносить в нее данные вручную утомительно и нудно

Binotto, рекомендуем остановиться на одном таком комментарии, а то придется вас забанить за флуд

Михаил, а как вытащить с мосбиржи gldrub_tom ?

Михаил, добрый день! (точнее, я пишу это ночью, потому что второй час пытаюсь воспроизвести функцию в гугл таблицах).
Прежде всего огромное спасибо за статьи и таблицы — каждая из них — просто кладезь полезной информации.
У меня есть таблица для ведения и расчета параметров структуры портфеля. Сейчас все тяну встроенными функциями googlefinance. Но данных по бпифам и облигациям сильно не хватает. Решила воспользоваться функциями из п.1. Не получается, выдается синтаксическая ошибка. Уже даже специально вставила в гугл таблице в те же ячейки — все равно ошибка. В вашей табличке при этом все работает.
Подскажите, пожалуйста, в чем может быть загвоздка?

Олег Мелихов

Михаил, добрый день!
Нужен совет профессионала!
В секции http://iss.moex.com/iss/securities/"&A3&"/dividends.xml есть данные о дивидендах по ряду акций (SBER, LOKH, NLMK и др.).

Однако нет данных о дивидендах таких акций как AVAN, SGHZ, RZSB, LSNGP, HIMCP. Могли бы подсказать в какой секции их искать? Все перерыл. Не могу найти.

Заранее спасибо за ответ! )))

Подскажите, как подтягивать в таблицу цены облигаций по ISIN? USG*******89

Михаил, пытаюсь подставить ISIN иностранных облигаций, и ничего не подтягивается.

Написал для получения последней цены, странно что автор в примере приводит цену предидущего дня. боится что мы перегрузим сервак московской биржи))

API Московской биржи в гугл-таблицах перестали работать в последние дни.
Кто-то знает что случилось, это временно или надолго?

Михаил Шардин

Павел, всё работает. Проверяйте на своей стороне.

Вот пример: https://docs.google.com/spreadsheets/d/1MI-FfjjOhPCYb0H2E9—bEWdsbu4HfOKly3ySySN-0k/

Михаил, похоже отвалился механизм передачи от московской в Таблицы 🙁 Можете обновить код?

Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу — показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) — пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй — нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы.

Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу — показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) — пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй — нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы.

Почему-то последние недели цена закрытия перестала подтягиваться. Не подскажете как это исправить?

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *