Инвестграм#7. Работа с Excel. Построение графиков доходностей.
Доброго времени суток, коллеги!
К сегодняшнему дню я подготовил материал, а точнее получилась подробная инструкция для работы с программой 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 рублей.
Далее считаем простую доходность внутри дня. Для этого значение на конец дня делим на значение начала дня, умножаем на 100 и вычитаем 100. Таким образом мы получим чистое значение процента. Далее протягиваем поле вниз по всему столбцу и получаем проценты за каждый день. 
Получаем результат 
Получение котировок акций
Чтобы вставить цену акций в Excel, сначала преобразуем текст в тип данных Stocks (Акции). Затем вы сможете извлечь в другой столбец нужные сведения из этого типа данных, такие как цены акций или их изменения.
Примечание: Тип данных Stocks (Акции) доступен толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.
Введите текст в ячейки. Например, в каждой ячейке введите тикер, название компании или название фонда.
Затем выберем ячейки.
Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, выберите Вставка > Таблица.
Выбирая ячейки, перейдите на вкладку Данные и нажмите кнопку Акции.
Если Excel обнаружит совпадение между текстом в ячейках и веб-источниками, текст будет преобразован в тип данных Stocks. Вы будете знать, что они преобразованы, если у них есть значок акций: 
.
Выберите одну или несколько ячеек с типом данных, и появится 
добавить столбец. Нажмите эту кнопку, а затем щелкните имя поля, чтобы извлечь дополнительные сведения. Например, для акций можно выбрать Price.
Чтобы увидеть все поля, доступные для компании или фонда, щелкните значок акций ( ) или выберите ячейку и нажмите CTRL+SHIFT+F5.
Если вы видите 
вместо значка, Excel не удается сопоставить текст с данными в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Кроме того, вы можете щелкнуть , чтобы открыть область выделения. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.
Вы также можете писать формулы, ссылаясь на типы данных, или использовать функцию STOCKHISTORY.
Данные об акциях задерживаются, предоставляются «как есть» и не являются торговыми целями или рекомендациями. Дополнительные сведения см. в сведениях об источниках данных.
Акции в excel.
Биржевая игра настолько сильно проникла в мировое общество, что один из ее столпов «Технический анализ» проник даже в Microsoft Office.
По определению «Технический анализ» призван для предсказания последующих действий биржевой толпы. Поведение толпы труднопредсказуемо, но оно инерционно.
Анализируя колебания цены на акции в прошлом — «Технический анализ» пытается предсказать поведение цены в будущем. По моему скромному мнению, попытка прогнозирования поведения рынка очень схожа с предсказаниями погоды. Если на дворе осень и сегодня идет дождь, то скорее всего дождь будет идти и завтра. Это очень вероятно, но вовсе не обязательно.
Исходные файлы excel с которых сделаны скриншоты и в которых есть построенные графики можно скачать по этой ссылке
По этой ссылке можно скачать данные в формате текстового или csv файла.
Также исторические данные по интересующему нас инструменту можно скачать на сайте брокера ЗАО «ФИНАМ по этой ссылке.
В графе: > выбираем >
В графе: > выбираем >
В графе: > выбираем >
В графе: > выбираем >
В графе: > выбираем >
В графе: > выбираем >
В графе: > выбираем >
Больше ничего не меняем и нажав на кнопу > сохраняем на своем жестком диске файл с данными.
Для построения графика в excel откроем его с помощью этой чудесной калькуляторной программы. Все данные немного собрались в кучу… см рисунок. Сделаем несколько движений, чтобы разложить их по полочкам.
1) наведем курсор мыши на горизонтальную шапку столбца А и нажмем левую кнопку, чтобы весь столбец выделился.
2) перейдем на вкладку главного меню Excel > и выберем команду >
3) в открывшемся мастере текстов жмем кнопу >.
Теперь раздвинем немного столбец C, чтобы стали видны значения дат.
В зависимости от настроек операционной системы excel различает целую и дробную части чисел по символу точки или запятой. У меня в настройках стоит запятая. В скачанных же данных разделителем служит точка. Чтобы не менять настройки операционной системы я просто точки заменяю на запятые с помощью excel.
В открывшемся диалоговом окне в поле ставлю «.», а в поле — «,» и жму кнопу >
Для построения биржевого графика требуется четыре столбца данных. Это столбцы:
— цена акции на открытии торгового периода;
— максимальная цена акции за текущий период;
— минимальная цена акции за текущий период;
— цена акции на закрытии торгового периода;
Выделяем эти столбцы (с шапками E, F, G, H ), копируем и вставляем на новый лист excel.
Далее следуем на вкладку > и выбираем раскрывающийся список в ряду «Биржевая» щелкаем по второй слева пиктограмме с изображением японских свечей.
Японские свечи — это вид графика который торговцы рисом начали использовать очень давно. На обоих концах свечи два фитиля — это минимум и максимум цены торгового дня. Тело свечи — это полоса между ценой открытия и ценой закрытия. Если цена закрытия торгов была выше цены открытия, то свеча белая (акция дорожала). Если ниже — свеча черная (акция дешевела).
Поэкспериментировав с настройками диаграммы, диапазоном и диапазоном данных можно увидеть на экране excel желаемый результат.
Исходные файлы excel с которых сделаны скриншоты и в которых есть построенные графики можно скачать по этой ссылке.
Читайте также:
Добрый день, подскажите, а разве в квике недостаточно данных и графика для анализа, например, за прошлый год, несчитая всего текущего?
В квике даже есть модуль технического анализа, как и во многих других торговых программах. Там есть все, кроме истории собственных сделок за предыдущие торговые сессии и неторговых операций.
Дело в том, что эта статья предназначена для тех, кому лень искать дистрибутив торговой программы, устанавливать его, регистрироваться для демо-доступа и т.д. А вот excel — он всегда под рукой. Всегда легко проверить на исторических данных любую книжную бредень про технический анализ. В этой статье и сказано где взять исторические данные торгов и как их обработать в excel безо всяких регистраций и инсталляций 🙂
«и неторговых операций»-что за операции? А я вот читала, что люди уходят от индикаторов и ориентируются по объемам ( индекс РТС)-не подскажите ли как это практически сделать?
— примерами неторговых операций могут служить зачисления денег на торговый счет, вывод денег с торгового счета, перевод денег на другую площадку (со срочного рынка на фондовый и т.д.) ну и удержание подоходного налога 🙂
— есть множество индикаторов и осцилляторов, построение которых основано на объемах торгов, они (индикаторы) не обязательно строятся на ценах. В QUIK в дополнение к окну графика цен можно добавить окно гистограммы объемов торгов. Можно скачать данные и у брокеров, там есть отдельная графа объема торгов, выраженного в рублях.
P.S. Я скептически отношусь к оценке торгов по объемам. Например, ликвидность на срочном рынке фьючерса на золото поддерживается роботами. Смысл этой поддержки в том, что они получают данные по цене на базовый инструмент на доли секунды раньше, чем можете это сделать Вы. Но иногда и этого недостаточно, тогда они снимают все заявки из стакана и в стакане остаются практически только заявки рукопашных скальперов. Иногда это приводит к сильным колебаниям цены, не соответствующим колебаниям значения базового актива (цене на физическое золото на спотовом рынке лондонской биржи). Это происходит потому, что кто-то «промахивается» рассчитывая на заявки, выставленные роботами, из-за крайне низкой естественной ликвидности этого инструмента.
Т.е. роботы поддерживают нормальную цену характерную для инструмента? Я думала это делают маркет-мейкеры, а роботы ставят торгующие трейдеры. «….тогда они снимают все заявки из стакана…»-они-это роботы? а как они могут снимать заявки?
Здравствуйте, Анна!
Вы абсолютно правильно понимаете функции маркет-мейкера. А почему Вы решили, что маркет-мейкеры не могут выполнять свои функции с помощью роботов? Если долго наблюдать за поведением заявок в стакане — то становится очевидным, что даже целой команде трейдеров не под силу формировать заявки в стакане с такой скоростью. Человеческий фактор, так сказать. Конечно, за работой программного комплекса пристально наблюдает опытный трейдер и переходит на ручное управление в критической ситуации. Может именно в этот момент исчезают маркет-мейкерские заявки из стакана.
Здесь и ранее я имею ввиду, что роботы снимают только свои заявки из стакана — наши с Вами заявки им конечно же недоступны. Простите за неточность в изложении, которая смутила Вас.
Расскажите пожалуйста как работают роботы и функции маркет-мейкеров, а так же кто снимает стоп-лоссы и как это практически происходит на примере. Если конечно у вас есть время…
Робот — это программа, которая может ставить заявки и снимать их в зависимости от изменения цены или другой доступной информации. Торговля с использованием роботов называется алготорговлей или торговлей по определенному алгоритму. Такие программные продукты подключаются к специальным серверам брокера и не каждый брокер поддерживает алготорговлю. Такой программный продукт нужно купить у разработчика и подключить к торговле у брокера. Требуется хороший выделенный канал связи. Я никогда не торговал с помощью робота, хотя брокер, услугами которого я пользуюсь, сообщил мне, что существует возможность подключения такого комплекса. Может я когда-нибудь и озабочусь поиском удобного робота для покупки, но явно не в ближайшее время.
Ваш вопрос про снятие стоп-лоссов я не совсем понял. Снять свой собственный стоп-лосс может в любой момент участник торговли. Есть еще понятие сорвать стопы — это когда резкое движение цены в одну сторону вызывает лавину заявок, выставленных в торговую систему в результате срабатывания стоп-приказов, а после удовлетворения всех этих заявок цена нахально разворачивается в другую сторону. Специальное манипулирование ценой для возникновения такого срыва запрещено законом.
Когда я писал о тех, кто «промахивается» я имел ввиду следующую историю:
— на срочном рынке нельзя оставить заявку на продажу или покупку «по рыночной цене»;
— тем, кто пришел на срочный рынок с фондового это кажется неудобным в силу возможности проскальзывания и они выходят из положения с помощью увеличения фиксированного отступа от цены срабатывания стоп-заявки;
когда роботы (маркет-мейкера, если хотите) постоянно поддерживают маленький спрэд (разницу между заявками на продажу и покупку) — такое увеличение фиксированного отступа работает аналогично заявке «по рыночной цене», потому что в торговой системе заявка всегда исполняется по лучшей текущей цене. Но, когда вдруг неожиданно из стакана исчезают эти (выставленные роботами) заявки — спрэд резко вырастает и объемы в стакане резко падают. Такое исчезновение длится недолго, но если именно в этот момент срабатывает стоп-заявка с повышенным отступом, то она удовлетворяется с существенно худшей ценой. Эта цена фиксируется и по ней может сработать стоп-заявка другого скальпера, а если и она с повышенным отступом — возникает лавинообразный процесс движения цены в неоправданную рынком сторону. Затем торговые роботы возвращают свои заявки в стакан и цена нормализуется.
Может немного муторно получилось на словах, картинки бы к ним приложить… Хоть целую статью пиши, но если честно — то лень 🙂
Давайте лучше, Вы спросите о том, что непонятно, а я попробую изложить такие моменты пояснее.
Пока в японские свечи не въехал, пользовался как дурак обычным линейным)
Мне вообще графики не очень нравятся, предпочитаю таблицы, но в данном случае можно столько информации получить, и всего по одной свече.
Денис-искуситель! Руки так и зачесались терминал установить.
Как зачем? Кошмарить)
В терминале можно посмотреть те же исторические данные, без необходимости терзать эксель.
До торговли в терминале руки не дошли. Наверное, не моё это.
До торговли в терминале руки не дошли. Наверное, не моё это.
Так я и не предлагаю торговать акциями. Я — excel предлагаю посмотреть. Может у тебя идеи вебмастерские возникнут, когда посмотришь 🙂
Денис, смотрю по картинкам, ты используешь 4 столбца: открытие, максимум, минимум, закрытие.
Как вебмастеру «здесь и сейчас» приходит идея скачать «неправильный» формат и отдать «верный». Только кому это нужно? (: Для опытных проще терминал установить, для начинающих — полезнее и нагляднее твой способ. Японские свечи это такая штука, которую надо «прочувствовать» ручками.
Как вебмастеру «здесь и сейчас» приходит идея скачать «неправильный» формат и отдать «верный». Только кому это нужно?
А ты — отдай! Глядишь, и узнаем кому это нужно 🙂
Акции в Excel
Эта статья о том, как построить в Microsoft Excel графики акций по данным котировок в текстовом файле, который можно скачать у агрегатора брокера ФИНАМ. О том, где взять файл с ценами на акции для построения графика в Excel есть целая статья Цены акций и облигаций
Исходный файл для графика цен по выбранной бумаге скачивается в формате csv. Это обычный текстовый файл, где поля данных просто разделены запятыми. Это очень удобно для обработки этого файла в Microsoft Excel.
Шаг первый — открываем файл формата csv с ценами на акции в Microsoft Excel для построения графика
В «Проводнике » операционной системы Windows кликаем левой кнопкой мыши, чтобы выделить файл и нажимаем правую кнопку мыши, чтобы открыть контекстное меню «Проводника».
Возможно, что в операционной системе формату файлов в csv не назначен по умолчанию обработчиком Microsoft Excel. В этому случае нужно воспользоваться контекстным меню «Проводника», чтобы выбрать «Открыть с помощью >> Microsoft Office Excel»
Итак, после выполнения вышеописанных действий перед глазами должна открыться следующая картина, как на рисунке ниже. То есть, все данные видны, но они как-то сгрудились в единую кучу: цены, даты, тикер и прочее. То есть, собрались хоть и по разным строчкам, но в одном столбце.
Разбор в Excel данных по столбцам
Для того, чтобы разобрать данные по столбцам и нужен был формат текстового файла csv. Эти запятые между полями данных очень удобно использовать для разделения этих полей по столбцам с помощью внутренних средств Excel.
На ленте меню кликом левой кнопки мыши следует перейти в раздел ленты «Данные». Затем, кликнуть левой кнопкой мыши по «шапке» крайнего левого столбца, чтобы его выделить, как на рисунке ниже.
После того, как крайний левый столбец выделен, а на ленте меню активирована вкладка «Данные» следует кликнуть левой кнопкой мыши по элементу «Текст по столбцам». Смотрим на рисунок ниже:
После клика мышью по элементу «Текст по столбцам» открывается диалоговое окно для настройки сортировки данных. Нам требуется кликнуть левой кнопкой мыши по «радиокнопке» напротив опции «с разделителями». После этого можно нажимать кнопку «Далее».
На втором шаге в диалоговом окне настройки следует поставить два флажка в разделе «Символом-разделителем является:» напротив «знак табуляции» и «запятая». После установки флажков следует нажать кнопку «Готово».
На самом деле еще не все «Готово», хотя график уже можно построить. Давайте посмотрим, что у нас должно получиться при нажатии на кнопку «Готово».
Вот такие символы «######» в Microsoft Excel появляются, когда для отображения данных не хватает ширины ячейки. Чтобы Excel сам определил какая требуется ширина — нужно навести курсор мыши на правую границу шапки столбца, в котором отображаются «######».
Курсор Excel должен принять форму вертикальной линии с двумя жирными перпендикулярными стрелочками. Когда курсор принимает такую форму, то двойной щелчок левой кнопки мыши раздвигает весь столбец до необходимой ширины для корректного отображения данных.
Замена с помощью Excel в ценах на акцию точки на запятую для отделения дробной части
Теперь, когда дата отображается корректно — нужно разобраться с ценами. Все дело в разделителе целой и дробной частей числа. Excel не воспринимает точку, как разделитель — ему нужна запятая, а чтобы построить правильный график — без дробной части цены не обойтись. Можно, конечно, изменить настройки операционной системы, но проще поменять в данных точку на запятую с помощью средств самого Excel.
Для этого нужно выделить все столбцы, в которых содержатся непосредственно цены акций и точку заменить запятой. Чтобы это сделать, следует кликнуть по шапке крайнего левого столбца левой кнопкой мыши. Затем, нужно нажать и удерживать на клавиатуре клавишу «Shift», а левой кнопкой мыши кликнуть по шапке крайнего правого столбца.
Столбцы с ценами на акции «OPEN», «HIGH», «LOW», «CLOSE» должны выделиться как на рисунке.
После того, как столбцы с ценами на акцию выделены нужно перейти на вкладку ленты меню «Главная» и кликнуть левой кнопкой мыши по пункту вкладки «Найти и выделить».
В выпадающем списке пункта меню «Найти и выделить» следует кликнуть левой кнопкой мыши по строке «Заменить».
Для тех, кто любит «горячие клавиши» — в Excel для вызова этого диалогового окна, вместо блуждания по менюшкам, можно просто удерживая клавишу «Ctrl» нажать клавишу «h».
Неважно каким образом вы вызвали это диалоговое окно — используя менюшки или нажав «Ctrl+h». Важно, что в первой строке с наименованием «Найти» нужно поставить точку, а во второй строке с наименованием «Заменить на» нужно поставить запятую.
Когда мы указали Microsoft Excel что и на что нужно заменить — можно нажимать кнопку «Заменить все».
После нажатия кнопки «Заменить все» в выделенных столбцах все точки должны быть заменены запятыми и во всех ценах на акцию дробная часть, как требуется для Excel, отделена уже запятой.
О том, какое количество замен удалось произвести Excel сообщит в диалоговом окне.
В нашем примере Excel заменил в ценах на акцию 3136 точек на 3136 запятых. Можно нажимать кнопку «ОК».
График цен акции в форме японских свечей начало построения с помощью Excel
Наконец-то, когда данные разбиты по столбцам, а точки заменены на запятые — пришло время построить долгожданный график цен на акции в форме японских свечей.
Для построения графика требуется сначала выделить цены на акцию в столбцах с названиями «OPEN», «HIGH», «LOW» и «CLOSE».
Наводим курсор мыши на верхнюю ячейку с ценой акции из столбца «OPEN» и кликаем левую кнопку мыши, после чего ячейка выделяется жирной черной рамочкой. Затем, прокручиваем за ползунок (который находится слева на листе Excel) или с помощью колесика мыши лист Excel в самый низ, где заканчиваются цены на акцию.
Промотав лист в самый конец данных нужно нажать клавишу «Shift» и удерживая ее кликнуть левой кнопкой мыши в нижнюю крайне правую ячейку с ценами.
Должны выделиться все цены на акцию так, как на рисунке.
После того, как данные выделены следует перейти на вкладку ленты «Вставка» и кликнуть левой кнопкой мыши по пункту меню Excel: «Другие диаграммы».
После клика левой кнопки мыши по пункту «Другие диаграммы» откроется матрица пиктограммок различных диаграмм. Нам следует выбрать в ряду «Биржевые» пиктограммку с графиком японских свечей и кликнуть по ней левой кнопкой мыши.
Перед нами появилась диаграмма биржевого графика изменения цены на акцию. И если по вертикальной оси к этому графику вопросов нет, то по горизонтальной оси вместо 1,2,3,4,5… хотелось бы видеть даты, соответствующие ценам на графике.
Настройка подписей к графику цен на акцию
Этим мы и займемся. Кстати, график можно растянуть на весь лист «цепляя» ее мышкой за края или углы. График при этом будет автоматически масштабироваться.
Я растянул график на все окно, чтобы лучше было видно подписи горизонтальной оси.
Теперь нужно навести курсор мыши на поле рядом с графиком и нажать правую кнопку, чтобы вызвать контекстное меню настройки данных.
После того, как курсор мыши наведен на поле графика и нажата правая кнопка мыши — открывается небольшое контекстное меню в котором левой кнопкой мыши следует кликнуть по строчке «Выбрать данные».
В открывшемся меню выборки данных для графика нас будет интересовать правая часть окна с наименованием «Подписи горизонтальной оси (категории)».
Можно отметить, что именно там сейчас под графиком находятся так не приглянувшиеся цифры 1,2,3,4,5… Чтобы исправить эту ситуацию нажимаем кнопку «Изменить».
Не обращая пока особого внимания на открывшееся окно «Подписи оси» наводим курсор мыши на ячейку, которая содержит дату первой группы цен на акцию и кликаем левой кнопкой мыши так, чтобы ячейка с датой ограничилась пунктирной линией, как на рисунке.
За ползунок, или используя колесико мыши — прокручиваем лист Excel до последней, видимой даты. Нажимаем и удерживаем нажатой клавишу «Shift», а при этом наводим курсор мыши на ячейку с самой нижней датой и кликаем левой кнопкой мыши, чтобы все даты охватились пунктирной границей.
На прошлом рисунке был выделен не весь диапазон дат, часть диапазона оказалась закрыта самой диаграммой.
Выход из этой ситуации — прост. Слева видно, что ряды подсвечены только до номера 785, а значит в 785 ряду находится последняя ячейка с датой диапазона. В менюшке «Диапазон подписей осей» последнее число исправляем на 785 и жмем кнопку «ОК».
В этом окне мы можем узреть, что теперь на его правой половине в разделе «Подписи горизонтальной оси (категории)» у нас теперь отображаются даты, вместо 1,2,3,4,5…
Можно и здесь нажимать кнопку «ОК».
И вот, наконец-то мы получили желаемое. По горизонтальной оси графика размещены даты, в соответствии с ценами на акцию.
Как увидеть на графике цен акции японские свечи
А где же японские свечи на графике? — спросите вы. Не волнуйтесь, они — на графике по этой акции. Просто их так много, что они слились в сплошные черные штрихи. Чтобы узреть свечи — достаточно уменьшить период, за который построен график.
Если взять период цен на акции покороче, то масштаб отображения графика изменится и мы увидим график цен с классическими японскими свечами как на рисунке ниже:
Если у вас получилось построить график цен на акции с помощью этой статьи и Microsoft Excel, то поздравляю вас с маленькой победой! А если нет — жду описания ваших трудностей в комментариях к этой статье.
В помощь предлагается скачать файл в формате Microsoft Excel, из которого были сделаны скриншоты для написания данной статьи Скачать_Акции_в_Excel_График
Если Вам понравилась эта статья — поделитесь ею в социальных сетях, пожалуйста…
