Как построить график акций в excel
Исходные файлы excel с которых сделаны скриншоты и в которых есть построенные графики можно скачать по этой ссылке
Где взять данные ? На сайте Российской биржи эти данные предоставляют на платной основе (для физического лица оплата составляет около 2-х долларов США в месяц). Однако, некоторые брокеры публикуют их уже бесплатно и доступно для всех желающих. Далеко ходить не буду, а воспользуюсь услугами банка, брокерскими услугами которого я пользуюсь.
По этой ссылке можно скачать данные в формате текстового или csv файла.
В графе: <<Секция рынка>> выбираем <<ММВБ>>
В графе: <<Инструмент>> выбираем <<Сбербанк>>
В графе: <<Формат>> выбираем <<CSV>>
В графе: <<Периодичность>> выбираем <<ежедневно>>
В графе: <<Выводить за период>> выбираем <<с 1.01.2011 по 2.05.2012>>
В графе: <<Формат даты>> выбираем <<DD/MM/YYYY>>
В графе: <<Разделитель>> выбираем <<Символ табуляции>>
Больше ничего не меняем и нажав на кнопу <<Экспортировать>> сохраняем на своем жестком диске файл с данными.
Теперь раздвинем немного столбец C, чтобы стали видны значения дат.
В зависимости от настроек операционной системы excel различает целую и дробную части чисел по символу точки или запятой. У меня в настройках стоит запятая. В скачанных же данных разделителем служит точка. Чтобы не менять настройки операционной системы я просто точки заменяю на запятые с помощью excel.
Для этого я выделяю пять столбцов: E, F, G, H, I. (то есть кликаю левой кнопки мышки на шапку столбца E, нажимаю клавишу <Shift> и удерживая ее кликаю по шапке столбца I) Далее возвращаюсь на вкладку главного меню Excel <<Главная>> и в подменю <<Найти и выделить>> в раскрывающемся списке выбираю команду <Заменить>.
Для построения биржевого графика требуется четыре столбца данных. Это столбцы:
Выделяем эти столбцы (с шапками E, F, G, H ), копируем и вставляем на новый лист excel.
Поэкспериментировав с настройками диаграммы, диапазоном <Формата оси> и диапазоном данных можно увидеть на экране excel желаемый результат.
Ну как, у Вас получилось построить график цен на акции Сбербанка по историческим данным ?
Исходные файлы excel с которых сделаны скриншоты и в которых есть построенные графики можно скачать по этой ссылке.
Анна в 13:11 одним прекрасным днем, а именно: 29 07 2013 пишет следующее:Добрый день, подскажите, а разве в квике недостаточно данных и графика для анализа, например, за прошлый год, несчитая всего текущего?
pessimist в 13:23 одним прекрасным днем, а именно: 29 07 2013 пишет следующее: Анна в 09:04 одним прекрасным днем, а именно: 30 07 2013 пишет следующее: pessimist в 15:42 одним прекрасным днем, а именно: 30 07 2013 пишет следующее:Снова по порядку:
Анна в 11:44 одним прекрасным днем, а именно: 31 07 2013 пишет следующее: pessimist в 15:29 одним прекрасным днем, а именно: 31 07 2013 пишет следующее: Анна в 11:48 одним прекрасным днем, а именно: 31 07 2013 пишет следующее: pessimist в 16:09 одним прекрасным днем, а именно: 31 07 2013 пишет следующее: andi в 18:36 одним прекрасным днем, а именно: 27 09 2016 пишет следующее:Пока в японские свечи не въехал, пользовался как дурак обычным линейным)
Мне вообще графики не очень нравятся, предпочитаю таблицы, но в данном случае можно столько информации получить, и всего по одной свече.
Денис-искуситель! Руки так и зачесались терминал установить.
Как зачем? Кошмарить)
В терминале можно посмотреть те же исторические данные, без необходимости терзать эксель.
До торговли в терминале руки не дошли. Наверное, не моё это.
andi в 16:56 одним прекрасным днем, а именно: 30 09 2016 пишет следующее:До торговли в терминале руки не дошли. Наверное, не моё это.
Денис, смотрю по картинкам, ты используешь 4 столбца: открытие, максимум, минимум, закрытие.
Как вебмастеру «здесь и сейчас» приходит идея скачать «неправильный» формат и отдать «верный». Только кому это нужно? (: Для опытных проще терминал установить, для начинающих — полезнее и нагляднее твой способ. Японские свечи это такая штука, которую надо «прочувствовать» ручками.
pessimist в 17:23 одним прекрасным днем, а именно: 30 09 2016 пишет следующее:Как вебмастеру «здесь и сейчас» приходит идея скачать «неправильный» формат и отдать «верный». Только кому это нужно?
К сегодняшнему дню я подготовил материал, а точнее получилась подробная инструкция для работы с программой Excel. Или как посчитать, и построить процентный и рублевый график доходностей.
Для начала необходимо заполнить необходимые поля в таблице. Сразу оговорюсь, что применяемые навыки можно использовать как для инвестиций, так и для спекуляций. Соответственно зная базовые навыки из данной статьи, вы сможете самостоятельно построить графики как годовой доходности, так и месячной/дневной и т. д.
Определитесь, за какие дни/месяцы вам нужны данные. Я использовал данные на примере неполного месяца августа. Вы можете выбрать свой интервал. Моя задача передать принцип работы.
Колонка В – Месяц, Колонка С – Число, D – Сумма на начало дня, Е – Сумма наконец дня, сразу можете создать колонку % и Комментарий. Обращаю ваше внимание на то, что колонки Начало и Конец дня отображаются оценку портфеля на плановую дату. То есть с учетом нерассчитанных сделок Т+. Эта оценка включает в себя и деньги и активы. Вы можете использовать и свои данные, но такой подход будет более точным.
Для чего обозначены 2 поля зеленым цветом? Для того, чтобы учесть такие ситуации как зачисление денег (докапитализация) и начисление доходов.
Рассмотрим ситуацию с дозачислением денег. В нашем примере мы зачисляли 50 000 рублей.
Для того, чтобы % считался верно, вам НЕОБХОДИМО в день зачисления прибавить зачисленную сумму к концу предыдущего дня. В нашем случае 101635 (конец 14.08) + 50 000 = 151 635 рублей. Деньги мы зачисляли 15 числа, поэтому на начало 15 числа мы помещаем значение 151 635 на начало дня.
При начислении дохода (дивиденды, купоны и т.д.) лично я закладываю значение начисленной суммы в результат дня. Вы можете поступать иначе. В данном примере нам начислили дивиденды в размере 4 000 рублей, и наш портфель увеличился на 3 000 рублей за день, таким образом, сумма на начало дня 152 550+4 000+3 000=157 550. Для эксперимента или верности расчетов можете закладывать сторонний доход как зачисление денег. Учет будет вестись как с примером 50 000 рублей.
Представляем вашему вниманию статистический метод расчета справедливой стоимости акций. Речь идет о регрессионном анализе. Незаменимую помощь в процессе исследования окажет обычный Excel.
Что такое регрессия
Регрессионный анализ является статистическим методом исследования. Он позволяет оценить зависимость одной (зависимой) переменной от других (независимых) переменных. Самой простой является линейная регрессия. Ее формула такова:
Y = a0 + a1x1 + … + anxn
где Y — зависимая переменная,
x — независимые переменные, влияющие на нее,
a — коэффициенты регрессии.
Зависимой переменной может выступать цена актива. Возможные влияющие факторы — цены других активов, финансовые и макропоказатели и т.д. В нашем случае считать будем теоретическую (расчетную) условно справедливую стоимость акций, зависящую от цен на другие активы.
Важно, чтобы независимых переменных было не слишком мало, но и не слишком много. Влияющие переменные стоит отбирать из экономических соображений, руководствуясь здравым смыслом. В идеале их нужно тестировать на мультиколлинеарность и т.д., но наш обзор посвящен базовым принципам регрессионного анализа. Статистическую значимость модели поможет оценить показатель R2 (R — квадрат), о нем речь пойдет дальше.
Если фактическая цена бумаги заметно отклоняется от расчетной, появляется повод для дополнительного анализа. Стоит также смотреть на техническую картину, мультипликаторы, общерыночную ситуацию. Существуют также методы финансового моделирования, носящие фундаментальный подход, в частности, модели дисконтирования денежных потоков (DCF) и модели дисконтирования дивидендов (DDM).
Пример расчетов в Excel и выводы
В качестве примера возьмем акции американского нефтегазового гиганта Exxon Mobil (XOM). Модель будет упрощенной и учебной и не является рекомендацией для осуществления операций с бумагами, ситуацию нужно смотреть в комплексе.
Независимыми переменными у нас выступят фьючерсы на американскую нефть WTI (склеенные фронтальные контракты) и индекс S&P 500. Логика проста — бизнес компании зависит от цен на нефть, а поведение акций в теории должно быть связано в общерыночной ситуацией.
Шаг 1. Выкачиваем в Excel котировки XOM, SPX и CL1. Данные возьмем за пять лет. Так как на более длительных периодах наблюдалась разная структурная ситуация на нефтяном рынке. Возьмем статистику в недельной разбивке, будет 262 наблюдения.
Шаг 2. Активируем настройку регрессионного анализа. Открываем раздел Файл. Переходим на вкладку Параметры Excel — Надстройки. Внизу появившегося окна будет вкладка Управление, где стоит параметр Надстройки Excel, жмем — Перейти.
Выбираем опцию Пакет анализа.
Готово. Результат появится в разделе Данные — Анализ данных.
Шаг 3. Строим регрессию. При клике на Анализ данных появится меню с опциями функционала для анализа. Выбираем Регрессия.
Заполняем окна по аналогии со схемой, используя ранее выгруженные данные по активам.
На выходе получаем вот такие данные.
Шаг 4. Интерпретация. Статистических показателей много. Не вдаваясь в теорию, наиболее интересными являются значения коэффициентов регрессии и показатель R2.
Наша модель будет иметь следующий вид:
Цена акций Exxon Mobil = $96,2 + 0,28*WTI — 0,01*S&P 500
R — квадрат равен 0,61. Показатель показывает, насколько значение зависимой переменной определяется значениями независимых переменных. Речь идет о статистической значимости модели. Модель является очень хорошей, если R2 превышает 0,8, и при этом сама модель имеет экономическое обоснование. В нашем случае все не настолько идеально, но все же выше 0,5, поэтому модель можно использовать.
Отмечу, что в процессе подготовки материала делались расчеты не только за пять лет, но и за 10, и за три года, также WTI заменялась на Brent. Итоговый вариант был выбран в связи с наибольшим значением R2.
Шаг 5. Применение. Рассчитаем в Excel теоретические значения акций Exxon за весь использовавшийся для построения модели период (5 лет).
Построим линейную диаграмму, на которой будут представлены динамика фактической цены и расчетной цены акций. Заметно, что расхождения между двумя величинами редко носили слишком серьезный характер. По состоянию на 06.06.2019 фактическая цена акций составила $74,2, а теоретическая — $76,7. Исходя из этого, критерия бумаги вполне справедливо оценены рынком. Однако это только один, причем упрощенный подход. Ситуацию нужно рассматривать в комплексе. К примеру, медианный таргет аналитиков на 12 месяцев равен $84. Это усредненный показатель результатов моделей фундаментальной оценки, предполагающий заметный потенциал роста.
Корреляционный анализ
Дополним нашу регрессию корреляционным анализом. Корреляция означает зависимость одного показателя от другого. Коэффициент корреляции — показатель взаимосвязи (в нашем случае финансовых активов).
Строим корреляционную матрицу. В том же разделе Анализ данных выбираем опцию Корреляция. Заполняем окно, как показано ниже, с учетом котировок наших активов.
На выходе получаем корреляционную матрицу. На ней видно, что цена Exxon положительно связана с WTI (коэффициент корреляции = 0,55) и отрицательно зависит от динамики индекса S&P 500 (коэффициент корреляции = -0,48).
Так что Exxon — это преимущественно нефтяная история, зачастую не совпадающая по динамике с широким рынком. Это можно заметить на графике трех активов с 2010 г. Ситуация стала такой с 2014 г., когда рынок нефти обвалился из-за структурных сдвигов. На нашей выборке за 5 лет корреляция между WTI и S&P 500 равна 0,13, то есть несущественна.
Построение графика простой регрессии
Расскажем об еще одном регрессионном функционале Excel. Программа позволяет построить график линейной регрессии. Правда доступно это лишь при наличии одной независимой переменной. В нашем случае ею будет нефть, так как она в большей мере объясняет движения акций Exxon — коэффициент регрессии равен 0,28 против (-0,01) у S&P 500.
Строим точечную диаграмму по XOM и WTI за 5 лет. Получаем поле корреляции. Щелкаем по любой из точек на диаграмме и меню левой кнопки мыши выбираем Добавить линию тренда.
В окне выбираем линейную линию тренда, ставим галочки напротив Показывать уравнение и Поместить на диаграмму R2.
В итоге получим такую схему зависимости Exxon (y) от WTI (x). В нашем случае модель не является статистически значимой — R-квадрат равен лишь 0,3.
Как еще использовать корреляционно-регрессионный анализ
В архивах раздела Обучение БКС Экспресс есть материалы на эту тему.
Отмечу, что наш материал носил ознакомительный характер. В регрессионные модели можно вносить макроэкономические, финансовые и прочие показатели. В идеале, независимые переменные нужно тестировать на ряд факторов. Наш обзор — это пример «мгновенной и грубой» оценки. В любом случае, выводы, полученные в результате регрессионного моделирования, стоит комбинировать с другими подходами к инвестиционному анализу.
БКС Брокер
Зарегистрируйтесь, чтобы иметь возможность оставлять комментарии. Уже есть аккаунт? Войти Покупайте ценные бумаги любимых брендов в один клик Зарегистрируйтесь, чтобы управлять панелью котировок. Уже есть аккаунт? Войти Покупайте ценные бумаги любимых брендов в один клик Шоу «Без плохих новостей»: Американский рынок упадет? Дивиденды и идея на рынке недвижимости Итоги торгов. Ускоряем падение на возросших оборотах Рынок США. Exxon Mobil и ConocoPhillips теряют более 4%Главное за неделю. Геополитическая тревожность покачивает рынок
Итоги торгов. Ускоряем падение на возросших оборотах
Кто быстрее на волне
Татнефть определится с дивидендами 23 ноября. На что рассчитывать
Крупнейшие автопроизводители на СПБ Бирже. Инфографика
ВТБ выходит из Магнита. Что это значит и чего ждать от акций
Акции Cisco упали после отчета. Что не понравилось инвесторам
* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.
Информация не может рассматриваться как публичная оферта, предложение или приглашение приобрести, или продать какие-либо ценные бумаги, иные финансовые инструменты, совершить с ними сделки. Информация не может рассматриваться в качестве гарантий или обещаний в будущем доходности вложений, уровня риска, размера издержек, безубыточности инвестиций. Результат инвестирования в прошлом не определяет дохода в будущем. Не является рекламой ценных бумаг. Перед принятием инвестиционного решения Инвестору необходимо самостоятельно оценить экономические риски и выгоды, налоговые, юридические, бухгалтерские последствия заключения сделки, свою готовность и возможность принять такие риски. Клиент также несет расходы на оплату брокерских и депозитарных услуг, подачи поручений по телефону, иные расходы, подлежащие оплате клиентом. Полный список тарифов ООО «Компания БКС» приведен в приложении № 11 к Регламенту оказания услуг на рынке ценных бумаг ООО «Компания БКС». Перед совершением сделок вам также необходимо ознакомиться с: уведомлением о рисках, связанных с осуществлением операций на рынке ценных бумаг; информацией о рисках клиента, связанных с совершением сделок с неполным покрытием, возникновением непокрытых позиций, временно непокрытых позиций; заявлением, раскрывающим риски, связанные с проведением операций на рынке фьючерсных контрактов, форвардных контрактов и опционов; декларацией о рисках, связанных с приобретением иностранных ценных бумаг.
Приведенная информация и мнения составлены на основе публичных источников, которые признаны надежными, однако за достоверность предоставленной информации ООО «Компания БКС» ответственности не несёт. Приведенная информация и мнения формируются различными экспертами, в том числе независимыми, и мнение по одной и той же ситуации может кардинально различаться даже среди экспертов БКС. Принимая во внимание вышесказанное, не следует полагаться исключительно на представленные материалы в ущерб проведению независимого анализа. ООО «Компания БКС» и её аффилированные лица и сотрудники не несут ответственности за использование данной информации, за прямой или косвенный ущерб, наступивший вследствие использования данной информации, а также за ее достоверность.
Эта статья о том, как построить в Microsoft Excel графики акций по данным котировок в текстовом файле, который можно скачать у агрегатора брокера ФИНАМ. О том, где взять файл с ценами на акции для построения графика в Excel есть целая статья Цены акций и облигаций
Исходный файл для графика цен по выбранной бумаге скачивается в формате csv. Это обычный текстовый файл, где поля данных просто разделены запятыми. Это очень удобно для обработки этого файла в Microsoft Excel.
Итак, после выполнения вышеописанных действий перед глазами должна открыться следующая картина, как на рисунке ниже. То есть, все данные видны, но они как-то сгрудились в единую кучу: цены, даты, тикер и прочее. То есть, собрались хоть и по разным строчкам, но в одном столбце.
Разбор в Excel данных по столбцам
Для того, чтобы разобрать данные по столбцам и нужен был формат текстового файла csv. Эти запятые между полями данных очень удобно использовать для разделения этих полей по столбцам с помощью внутренних средств Excel.
Курсор Excel должен принять форму вертикальной линии с двумя жирными перпендикулярными стрелочками. Когда курсор принимает такую форму, то двойной щелчок левой кнопки мыши раздвигает весь столбец до необходимой ширины для корректного отображения данных.
Замена с помощью Excel в ценах на акцию точки на запятую для отделения дробной части
График цен акции в форме японских свечей начало построения с помощью Excel
Должны выделиться все цены на акцию так, как на рисунке.
Настройка подписей к графику цен на акцию
Я растянул график на все окно, чтобы лучше было видно подписи горизонтальной оси.
Теперь нужно навести курсор мыши на поле рядом с графиком и нажать правую кнопку, чтобы вызвать контекстное меню настройки данных.
На прошлом рисунке был выделен не весь диапазон дат, часть диапазона оказалась закрыта самой диаграммой.
И вот, наконец-то мы получили желаемое. По горизонтальной оси графика размещены даты, в соответствии с ценами на акцию.
Как увидеть на графике цен акции японские свечи
Если взять период цен на акции покороче, то масштаб отображения графика изменится и мы увидим график цен с классическими японскими свечами как на рисунке ниже:
Читайте также: