Создать таблицу продажи акций брокерской фирмы произвести расчеты по заданию в excel
Обновлено: 07.07.2024
Основное преимущество интернет-сервисов по учёту инвестиций в том, что в любой момент вы можете посмотреть состояние дел в вашем портфеле из любой точки мира. Но что делать, если вы не хотите тратить лишние деньги или не доверяете Интернету? В таком случае можно вести учёт сделок самостоятельно в Excel. Минус этого подхода в том, что вам необходим именно тот компьютер, на котором хранится отчётность. Хотя с применением облачных технологий этот недостаток легко устраняется. Неудобством является то, что вы не сможете оценить стоимость портфеля в режиме онлайн, как это доступно в интернет-сервисах, но и эта проблема решается, если вы аккуратный инвестор и регулярно проверяете свой портфель.
Далее на примере показано, как можно вести отчётность. Подразумевается, что все разделы отчётности — листы одной книги Excel, т.е. физически всё хранится в одном файле.
1. Движение денежных средств по счёту
Простая таблица из трёх столбцов: «Дата», «Зачисление», «Отзыв». Желательно, чтобы была ещё и сводная статистика по зачисленным и отозванным денежным средствам за всё время инвестирования.
Для определения «Зачислил, Всего» вам нужно суммировать все значения в столбце «Зачисление». Для получения значения «Отозвал, Всего» — суммируете значения в столбце «Отзыв». «Сальдо» определяется как разница между «Отозвал, Всего» и «Зачислил, Всего». В том, что сальдо отрицательное, нет ничего страшного, если вы вносите на счёт намного большие суммы, чем отзываете. Важнее другое: сумма сальдо и текущего баланса счёта должна быть положительной. Это означает, что в случае, если вы закроете счёт и выведете все деньги, то окажетесь с прибылью. В нашем примере 31.12.18 года на счёте было 820 тысяч рублей, сальдо равнялось − 606 тысяч. Если бы вы закрыли счёт и вывели все деньги в последний день 2018 года, то ваша прибыль составила бы 214 тысяч рублей. Это накопленный итог за всё время инвестирования.
Обратите внимание, что в таблице присутствуют даты 31.12.18 и 01.01.19, но в эти дни не производилось ни зачисление, ни отзыв денег. Они необходимы для определения доходности вложений в течение года.
Одна полезная функция, встроенная в Excel — возможность фиксировать строки, чтобы они не прокручивались. Обратите внимание, что на рисунке после третьей строки идёт 121-я. Фиксация строк делает работу с длинными таблицами намного удобнее.
2. Состав портфеля
Следующий лист в книге — «Состав портфеля на отчётную дату». В нашем примере отчётными датами выступают последние дни кварталов.
На рисунке биржевые тикеры акций используются для краткости. Значения для количества акций и стоимости соответствующей позиции можно посмотреть в терминале. «Доля в портфеле» определяется как отношение стоимости соответствующей позиции к общей стоимости портфеля. Диаграмма приведена для наглядности.
Лист с составом портфеля позволяет отслеживать динамику акций. Если вы собираетесь проводить ребалансировку, то столбец с долями акций в портфеле подскажет, как её осуществить.
3. Динамика портфеля по годам
Состав портфеля на определённую отчётную дату может быть весьма информативен, но если нужно подсчитать доходность вложений за длительный период, то необходимы дополнительные данные. Помимо ежеквартальной отчётности желательно вести годовую.
«Результат управления» рассчитывается как разница между стоимостью активов в конце года и начале, с поправкой на сальдо ввода и вывода денежных средств. «Прирост капитала» рассчитывается как отношение результата управления к стоимости активов на конец года.
«Чистая доходность» — это внутренняя норма доходности ваших вложений, учитывающая ввод и вывод денежных средств со счёта. Смысл внутренней нормы доходности поясним на примере.
На рисунке вы видите, что по итогам 2016 года «Чистая доходность» составила 25,52%. Представьте, что вы нашли депозит в банке со ставкой 25,52% и совершили следующие действия:
- внесли в начале года 341 246 рублей;
- в течение года внесли суммарно 212 600 рублей, такими же частями и в те же даты, что и на брокерский счёт;
- в течение года вывели 8 360 рублей, такими же частями и в те же даты, что и с брокерского счёта.
В этом случае в конце года на вашем депозите было бы 646 751 рубль.
При отслеживании динамики портфеля по годам желательно сравнивать «Чистую доходность» портфеля с индексом Мосбиржи. Об этом читайте в статье « Почему стоит ориентироваться на индекс при покупке акций ».
4. Учёт дивидендов и купонов
Если ваш инвестиционный подход ориентирован на получение дивидендов и купонов, то важно отслеживать суммарные дивидендные и купонные выплаты по итогам того или иного года.
Цель занятия. Закрепление и проверка навыков создания комплексных текстовых документов со встроенными расчетными таблицами и графиками
(время выполнения –1час 20 мин.).
Инструментарий. ПЭВМ IBM PC, программа MS Excel.
Литература. Практикум по информатике: учебное пособие-практикум / Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
Применяя все известные вам приемы создания и форматирования текстовых и табличных документов, выполните задания по образцу, стараясь создать по внешнему виду документ как можно ближе к оригиналу задания.
Выполняйте каждое задание на новом листе электронной книги «Расчеты».
Периодически выполняйте текущее сохранение файла.
Задание 1. Расчет заработной платы за квартал.
Используя таблицу «Расчет заработной платы за месяц» файла «Расчеты», создайте комплекс таблиц расчета заработной платы за квартал (рис. 1).
Рис.1. Исходные данные для Задания 1
Порядок работы.
Напечатайте таблицу «Расчет заработной платы за месяц» на новый лист электронной книги.
Применяя таблицы расчета заработной платы за январь, создайте таблицы расчета за февраль и март, изменяя формулы расчета Премии:
премия в январе = 20%;
в феврале - 27%;
в марте - 35%.
Рассчитайте среднее значение зарплаты за каждый месяц.
Проведите форматирование средних значений – курсив 12 пт. желтая заливка ячейки.
Проведите форматирование заголовка – объедините ячейки и разместите по центру таблицы, шрифт - полужирный курсив 14 пт. зеленого цвета.
Постройте гистограмму заработной платы сотрудников за март.
Ваши навыки и умения оцениваются «Удовлетворительно»
Ниже таблицы расчета заработной платы за март, создайте новую таблицу и рассчитайте квартальную зарплату каждого сотрудника как сумму ежемесячных заработных плат.
Применяя функции МАКС и МИН, выделите сотрудников с максимальной и минимальной квартальной заработной платой.
Ваши навыки и умения оцениваются «Хорошо»
Проведите уловное форматирование таблицы расчета зарплаты за февраль:
Премия (27%) меньше3000 руб. – синим цветом;
Премия (27%) больше 3000 руб. – малиновым цветом.
Проведите сортировку окладов сотрудников за февраль в порядке возрастания.
Постройте круговую диаграмму квартальной заработной платы сотрудников.
Ваши навыки и умения оцениваются «Отлично»
Дополнительные задания
Задание 2. Создать таблицу продажи акций брокерской фирмы.
Произвести все расчеты по заданию. Построить диаграмму выручки по отделениям фирмы и по видам акций.
Текущую дату задайте в ячейке А3 функцией СЕГОДНЯ.
Исходные данные представлены на рис.2.
Рис.2. Исходные данные для Задания 2
Формулы для расчета:
% от общей выручки = Выручка подразделения / Итого всей выручки
(результат расчета - в процентном формате).
Задание 3. Создать таблицу доходов/расходов сотрудника брокерской фирмы.
Произвести все расчеты по заданию. Построить график доходов и расходов.
Порядок работы.
Исходные данные представлены на рис.3.
Рис.3. Исходные данные для Задания 3
Формулы для расчета:
Сальдо = Доходы всего – Расходы всего
Задание 4. Создать таблицу анализа результатов опроса.
Произвести все расчеты по заданию. Построить круговую диаграмму количества опрошенных в возрасте свыше 41 года по видам увлечений.
Порядок работы.
Исходные данные представлены на рис.4.
Рис.4. Исходные данные для Задания 4
Задание 5. Создать комплексный документ (Word+Excel).
Текстовую часть документа набрать в программе Word (рис.5).
Создать таблицу в программе Excel (рис.6).
Произвести расчеты в таблице.
Построить диаграмму по данным за 2004 г и отформатировать по образцу (рис.7).
Скопировать из Excel в текстовый документ Word:
таблицу - после третьего абзаца текста;
диаграмму - после четвертого абзаца текста.
В ячейке A3 задайте текущую дату функцией СЕГОДНЯ.
Формула для расчета:
% от общей выручки = Выручка подразделения/Итого всей выручки (результат расчета — в процентном формате).
Задание 2. Создать таблицу анализа результатов опроса. Произвести все расчеты по заданию. Построить круговую диаграмму числа опрошенных в возрасте свыше 41 года по видам увлечений.
Задание 3. Создать таблицу «Расчет удельного веса документально проверенных организаций» и построить круговую диаграмму по результатам расчетов.
Примечание. При вводе текстовых данных, начинающихся с тире или другого математического знака, сначала нажмите клавишу Пробел - признак текстовых данных, а затем тире и текст (- государственных, - муниципальных и т.д.).
Рис. 1. Исходные данные для задания
Произведите расчеты в таблице. Формула для расчета
Удельный вес = Число проверенных организаций / Общее число плательщиков*100
Постройте диаграмму (круговую) по результатам расчетов с использованием мастера диаграмм.
Для этого выделите интервал ячеек Е7:Е11 с данными расчета результатов и выберите команду Вставка/Диаграмма или вызовите мастер диаграмм с Панели «Стандартная».
На первом шаге работы с мастером диаграмм выберите тип диаграммы - ^ Круговая (Объемный вариант разрезанной круговой диаграммы)
На втором шаге на вкладке Ряд в окошке Подписи категорий укажите интервал ячеек В7:В11 (рис. 5.3).
Третий шаг мастера диаграмм. Введите название диаграммы на вкладке Заголовки; укажите подписи значений на вкладке Подписи данных.
Четвертый шаг мастера диаграмм. Поместите диаграмму на имеющемся листе Конечный вид диаграммы приведен на рис.
Задание 4. Форматирование диаграммы «Расчет удельного веса документально проверенных организаций».
Порядок работы
1. Скопируйте полученную диаграмму и вставьте рядом.
2. Сделайте диаграмму активной щелчком мыши по ней, при этом появятся маркеры по углам диаграммы и серединам сторон.
3. Мышью переместите диаграмму под таблицу, измените размеры диаграммы (мышью за маркеры).
4. Выполните заливку фона диаграммы. Для этого выполните двойной щелчок мыши по области диаграммы. В открывшемся окне Формат области диаграммы выберите желтый цвет заливки и нажмите на кнопку Способы заливки.
4. В открывшемся окне Способы заливки на вкладке Градиентная бегунком выберите степень затемнения и укажите тип штриховки Вертикальная, после чего дважды нажмите кнопку ОК.
5. Отформатируйте легенду диаграммы (окошко в правой части диаграммы). Щелчком мыши сделайте область легенды активной, двойным щелчком вызовите окно Формат легенды. В открывшемся диалоговом окне Способы заливки выберите вкладку Рисунок или текстура, укажите вид текстуры Пергамент и нажмите кнопку ОК.
6. Заштрихуйте один сектор (дольку) круговой диаграммы. Для этого выделите одну дольку (выполните на дольке диаграммы два одинарных щелчка, при этом маркеры должны переместиться на дольку). Двойным щелчком по выделенной дольке вызовите диалоговое окно ^ Формат точки данных, выберите Заливку. В открывшемся окне Способы заливки на вкладке Узор выберите диагональную штриховку и дважды нажмите кнопку ОК.
7. Проведите форматирование подписей данных (значений 34%, 8% и т.д.). Для этого выполните двойной щелчок мыши по одному из численных значений подписей данных и в открывшемся окне Формат подписей данных на вкладке Шрифт установите: полужирный курсив — 11 пт., гарнитура шрифта - Anal Cyr.
8. Скопируйте созданную диаграмму (после выделения диаграммы используйте команды Правка/Копировать, Правка/Вставить).
9. Измените вид диаграммы на гистограмму. Для этого сделайте диаграмму активной щелчком мыши, далее щелчком правой кнопкой мыши по области диаграммы вызовите Свойства диаграммы, выберите команду Тип диаграммы и укажите тип - Гистограмма. Обратите на произошедшие изменения в диаграмме.
Основное преимущество интернет-сервисов по учёту инвестиций в том, что в любой момент вы можете посмотреть состояние дел в вашем портфеле из любой точки мира. Но что делать, если вы не хотите тратить лишние деньги или не доверяете Интернету? В таком случае можно вести учёт сделок самостоятельно в Excel. Минус этого подхода в том, что вам необходим именно тот компьютер, на котором хранится отчётность. Хотя с применением облачных технологий этот недостаток легко устраняется. Неудобством является то, что вы не сможете оценить стоимость портфеля в режиме онлайн, как это доступно в интернет-сервисах, но и эта проблема решается, если вы аккуратный инвестор и регулярно проверяете свой портфель.
Далее на примере показано, как можно вести отчётность. Подразумевается, что все разделы отчётности — листы одной книги Excel, т.е. физически всё хранится в одном файле.
1. Движение денежных средств по счёту
Простая таблица из трёх столбцов: «Дата», «Зачисление», «Отзыв». Желательно, чтобы была ещё и сводная статистика по зачисленным и отозванным денежным средствам за всё время инвестирования.
Для определения «Зачислил, Всего» вам нужно суммировать все значения в столбце «Зачисление». Для получения значения «Отозвал, Всего» — суммируете значения в столбце «Отзыв». «Сальдо» определяется как разница между «Отозвал, Всего» и «Зачислил, Всего». В том, что сальдо отрицательное, нет ничего страшного, если вы вносите на счёт намного большие суммы, чем отзываете. Важнее другое: сумма сальдо и текущего баланса счёта должна быть положительной. Это означает, что в случае, если вы закроете счёт и выведете все деньги, то окажетесь с прибылью. В нашем примере 31.12.18 года на счёте было 820 тысяч рублей, сальдо равнялось − 606 тысяч. Если бы вы закрыли счёт и вывели все деньги в последний день 2018 года, то ваша прибыль составила бы 214 тысяч рублей. Это накопленный итог за всё время инвестирования.
Обратите внимание, что в таблице присутствуют даты 31.12.18 и 01.01.19, но в эти дни не производилось ни зачисление, ни отзыв денег. Они необходимы для определения доходности вложений в течение года.
Одна полезная функция, встроенная в Excel — возможность фиксировать строки, чтобы они не прокручивались. Обратите внимание, что на рисунке после третьей строки идёт 121-я. Фиксация строк делает работу с длинными таблицами намного удобнее.
2. Состав портфеля
Следующий лист в книге — «Состав портфеля на отчётную дату». В нашем примере отчётными датами выступают последние дни кварталов.
На рисунке биржевые тикеры акций используются для краткости. Значения для количества акций и стоимости соответствующей позиции можно посмотреть в терминале. «Доля в портфеле» определяется как отношение стоимости соответствующей позиции к общей стоимости портфеля. Диаграмма приведена для наглядности.
Лист с составом портфеля позволяет отслеживать динамику акций. Если вы собираетесь проводить ребалансировку, то столбец с долями акций в портфеле подскажет, как её осуществить.
3. Динамика портфеля по годам
Состав портфеля на определённую отчётную дату может быть весьма информативен, но если нужно подсчитать доходность вложений за длительный период, то необходимы дополнительные данные. Помимо ежеквартальной отчётности желательно вести годовую.
«Результат управления» рассчитывается как разница между стоимостью активов в конце года и начале, с поправкой на сальдо ввода и вывода денежных средств. «Прирост капитала» рассчитывается как отношение результата управления к стоимости активов на конец года.
«Чистая доходность» — это внутренняя норма доходности ваших вложений, учитывающая ввод и вывод денежных средств со счёта. Смысл внутренней нормы доходности поясним на примере.
На рисунке вы видите, что по итогам 2016 года «Чистая доходность» составила 25,52%. Представьте, что вы нашли депозит в банке со ставкой 25,52% и совершили следующие действия:
- внесли в начале года 341 246 рублей;
- в течение года внесли суммарно 212 600 рублей, такими же частями и в те же даты, что и на брокерский счёт;
- в течение года вывели 8 360 рублей, такими же частями и в те же даты, что и с брокерского счёта.
В этом случае в конце года на вашем депозите было бы 646 751 рубль.
При отслеживании динамики портфеля по годам желательно сравнивать «Чистую доходность» портфеля с Индексом Мосбиржи. Об этом читайте в статье «Почему стоит ориентироваться на индекс при покупке акций».
4. Учёт дивидендов и купонов
Если ваш инвестиционный подход ориентирован на получение дивидендов и купонов, то важно отслеживать суммарные дивидендные и купонные выплаты по итогам того или иного года.
Биржевые тикеры акций и обозначения 3М, 6M, 9M, Y используются для краткости. Если дивиденды пока не известны, например, по акциям «Газпрома», в таблице стоят нули.
5. Сделки с ценными бумагами
Учёт сделок показан на примере акций «ФосАгро». «Сумма покупки» и «Сумма продажи» учитывают комиссии брокера. «Сальдо» определяется как разница между суммой продажи ценных бумаг и суммой покупки. В нашем примере ни одна акция не продавалась, поэтому сальдо отрицательное. Если текущая стоимость акций будет выше 98 тысяч рублей, то при продаже акций и закрытии позиции будет прибыль. Если в финансовом результате учитывать полученные дивиденды, то для получения прибыли нужно продать акции дороже, чем за 94 325 рублей. «Цена безубытка» определяется как отношение сальдо, взятого со знаком «—», к количеству акций в портфеле. «Цена безубытка с учётом» дивидендов определяется аналогично, только в расчётах используется «Сальдо с учётом дивидендов». «Количество ц\б» рассчитывается автоматически на основании содержимого столбцов «Количество» в разделах «Покупка» и «Продажа».
Для облигаций учёт будет аналогичным, но с учётом НКД и номинала при покупке и продаже.
На каждую позицию в вашем портфеле следует завести соответствующий лист в книге Excel. Поскольку позиций в вашем портфеле может быть много, то будет лучше вынести листы со сделками в отдельную книгу. Финансовые результаты сделок можно вести на отдельном листе.
Для большей информативности удобно завести в таблице с финансовыми результатами отдельный столбец, в котором содержится тип дохода: дивиденды, купоны, закрытие позиции. Он будет полезен при составлении сводной таблицы для группировки финансовых результатов, т.к. доступна сумма по каждой категории.
В нашем примере ведётся учёт финансового результата без разделения на типы доходов, поэтому группировка выполнена по годам.
Напоминаем, что любые вопросы, предложения и пожелания можно и нужно озвучивать нам через форму обратной связи. Нам важно ваше мнение – вместе мы сделаем «Открытый журнал» ещё лучше!
Способы дополнительного заработка на своем портфеле Дивиденды: консервативные и спекулятивные стратегии заработка Как получать выгоду от владения дивидендными акциями? Как вести себя во время коррекции на фондовом рынке Как работают эти разновидности экзотических деривативов Готовимся к неожиданностям на рынке деривативов: матрица стратегий на любой случай О трейдерах, практикующих высокорисковую стратегию быстрого заработка Дайте мне торговать с плечом, и я переверну этот мирМосква, ул. Летниковская, д. 2, стр. 4
Будьте в курсе новых публикаций!Подпишитесь на дайджест «Открытого журнала» и получайте подборку публикаций за неделю.
Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+
АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 (без ограничения срока действия).
ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.
Читайте также: