Как сделать в excel робота
Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel .
Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.
VBA и Python
VBA (Visual Basic for Applications) , де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.
Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python . На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.
В общем виде можно описать ситуацию через подобный график:
Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.
Если в силу разных причин возможности выбора нет, то и сравнивать нечего.
В пользу VBA
- Отличная работа с объектной моделью Excel и других приложений MS Office . Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
- Поддержка разных форматов MS Office. Самая большая проблема для внешних языков - это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA - это все “файл Excel”, работа с которыми в целом одинаково хороша.
- Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
- Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит “установить” модель макроса в фон офиса.
- Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
- Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.
В пользу Python (и других внешних языков программирования)
- Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
- Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python - откровенно “на троечку”.
- Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки - тоже неудобно.
- Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.
Кейсы
Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:
- Мой выбор: VBA . Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
Задача: Сервис, который должен был позволить пользователю с мобильного устройства конвертировать файлы PowerPoint в PDF для просмотра
- Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
- Мой выбор: Логика VBA + Python для мониторинга
- Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
- Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
- Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
- Мой выбор: VBA . Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
- Мой выбор: Python .
- Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
- Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
Задача: Перевести весь текст в презентации PowerPoint на другой язык машинным переводчиком
- Мой выбор: VBA . Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
Задача: По заданным биржевым тикерам брать данные из API с финансовыми показателями (API выдает сформированный по запросу CSV файл) и считать на их основе ряд бенчмарков для анализа
- Мой выбор: Python . Хотя API - простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
Задача: Ведение базы поручений, рассылка уведомлений исполнителям, генерация отчета для печати
Здесь я выбирал очень долго, так как есть много альтернатив:
- Сторонняя готовая система поручений
- База данных с каким-то обработчиком
- Access
- Excel
Мой выбор: VBA
- Во-первых, Excel сам по себе является готовым UI для работы
- Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
- В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое
Заключение
Надеюсь, для кого-то заметка будет полезна и позволит сэкономить время на выборе стека для решения своих задач.
Сегодня мы научим наш торговый терминал Квик выводить информацию в эксель.
1. Создайте в информационной системе Quik таблицу со списком всех облигаций. Подробно мы изучали данный вопрос в предыдущей теме на этой странице .
2. На рабочем столе своего компьютера создайте файл в формате Эксель. Нажмите правой кнопкой в любом месте рабочего стола. Наведите курсор на пункт "Создать" и выберите параметр "Лист Microsoft Excel".
3. Называем его "Мониторинг_облигаций.xlsx" и нажимаем два раза Enter. Используйте в названии нижнее подчёркивание вместо пробела, так как некоторые версии Квика могут работать некорректно.
4. В открывшемся экселе переименовываем "Лист1". Для этого нажимаем на него правой кнопкой мыши и выбираем пункт "Переименовать".
Присваиваем данному листу название "ОБЛИГАЦИИ". Все буквы заглавные. Для подтверждения нажимаем на кнопку ENTER. Вверху созданного листа обязательно кликаем на значок "Дискетка" для сохранения. Если не сохраниться после сделанных изменений торговая система Квик не сможет загрузить данные.
5. В торговой системе Квик нажимаем правой кнопкой на таблицу со списком облигаций и выбираем пункт "Вывод через DDE сервер".
6. Приступаем к настройке параметров вывода данных. В конце строки "Рабочая книга" нажимаем на кнопку троеточие ". " для указания файла. Далее в корневом меню выбираем "Рабочий стол", находим файл "Мониторинг_облигаций" и дважды кликаем по нему левой кнопкой мыши.
Далее указываем название листа "ОБЛИГАЦИИ" большими буквами и устанавливаем прочие параметры, как отмечено на скриншоте. В разных версиях торговой системы они могут незначительно отличаться.
Завершив настройку нажимаем на кнопку "Начать вывод". Подождите пару минут и завершите выгрузку данных нажав на кнопку "Остановить вывод". Если не остановить данные, то при редактировании ячеек в Экселе торговая система Квик будет выдавать ошибки. Теперь можно перейти в созданную нами таблицу.
Мы видим, что вся текущая информация загрузилась в Эксель. Для удобства дальнейшего использования таблицы проведём работы по наведению красоты.
Нажмите левой кнопкой мыши на цифру "1". Это номер строки. Эксель выделит её зелёным цветом. Затем нажмите на этом же месте правой кнопкой мыши и в открывшемся меню выберите пункт "Формат ячеек".
В новом окне кликаем на вкладку "Выравнивание" и отмечаем следующие параметры: Выравнивание по горизонтали - по центру; по вертикали - по центру; Отображение - ставим галочку рядом с пунктом "переносить по словам". После завершения сделанных настроек кликаем на кнопку ОК.
На следующем шаге сразу выделим жирным шрифтом заголовки столбцов. Вверху Экселя нажимаем на букву "Ж". Друзья, потерпите, осталось только настроить ширину столбцов.
Внимательно посмотрев на созданную нами таблицу можно заметить, что мы не видим часть данных. Поэтому, нам нужно настроить ширину столбцов. Обратите внимание на верхнюю часть таблицы с буквами "А, B, C, D . ". Колонка А дублирует информацию в колонке В. Мы её уберём. Для этого наведите курсор мыши между буквами А и В так, чтобы курсор стал похож на стрелочки вправо и влево одновременно. Нажмите на левую кнопку мыши и удерживая её потяните влево. Затем отпустите. Колонка А скроется. Теперь тоже самое проделайте между В и С только слегка потяните вправо и отпустите. Колонка станет шире и вся информация в каждой ячейке будет нам полностью видна. Сделайте шире следующие колонки. В результате должно получиться так, как показано на скриншоте.
Работа с Экселем иногда напоминает знаменитую игру в "Морской бой", где названия колонок отмечены буквами, а строки цифрами. Нажмите мышкой один раз на ячейку С-2. Она будет выделена зелёной рамкой. Затем вверху, во вкладках кликните левой кнопкой мыши на "Вид" и в панеле инструментов на кнопку "Разделить".
Программа автоматически разделит нашу таблицу на четыре области. Здесь же, рядом кликните на кнопку "Закрепить области".
Кто работал с онлайн-рекламой, тот в цирке не смеётся знает, что поисковики порой выдают неожиданные ответы на запросы или подкидывают совершенно не те объявления, которые могут быть интересны. В последнем случае корень проблемы зачастую кроется в наборе ключевых слов, которые использует рекламодатель в своих кампаниях. Бездумная автоматизация подбора ключевиков приводит к печальным последствиям, среди которых самое удручающее — пустые показы и клики. Excel-изобретатель и рационализатор Realweb Дмитрий Тумайкин озадачился этой проблемой и создал очередной файл-робот, который рад раздать миру и Хабру. Вновь передаём слово автору.
«В моей предыдущей статье речь шла о кластеризации больших семантических ядер с помощью макросов и формул в MS Excel. На этот раз речь пойдет о ещё более интересных вещах – словоформах, лемматизации, Яндексе, Google, словаре Зализняка и снова об Excel – его ограничениях, методах их обхода и невероятных скоростях бинарного поиска. Статья, как и предыдущая, будет интересна специалистам по контекстной рекламе и SEO-специалистам.
Итак, с чего всё началось?
Как известно, ключевым отличием алгоритмов поиска Яндекса от поиска Google является поддержка морфологии русского языка. Что имеется в виду: одна из самых больших приятностей заключается в том, что в Яндекс.Директ достаточно задать одну словоформу как минус-слово (любую), и объявление не будет показываться ни по одной из всех его словоформ. Занёс в минус-слова слово «бесплатно» — и не будет показов по словам «бесплатный», «бесплатная», «бесплатных», «бесплатными» и т.д. Удобно? Конечно же!
Однако не всё так просто. На тему странностей морфологии Яндекса была написана не одна статья, включая посты на самом Хабре, да и я в ходе своей работы неоднократно сталкивался с ними. Споры идут по сей день, но я считаю, данный алгоритм несмотря ни на что можно считать преимуществом перед логикой Google.
Странность же заключается в том, что, если группа всех словоформ, скажем, глагола или прилагательного, содержит омоним с группой словоформ существительного, то Яндекс фактически «склеивает» их в некое единое множество словоформ, по всем из которых будут показываться ваши объявления.
Вот наглядный пример, да простит меня НашЛось:
Как всем ясно, краткая форма прилагательного «великий», «велик», является омонимом слову «велик», который, в свою очередь — синоним слова «велосипед». У самого «велика» словоформы «великий», разумеется, нет, поэтому показывать его по этому запросу очевидно неправильно. Говоря языком лингвистов, Яндекс перепутал парадигмы.
Ситуация в естественной выдаче намного лучше, возможно, алгоритмы там более сложны и оптимизированы. А может, это связано с более высокой конкуренцией, т.к. SEO условно-бесплатно (если в штате есть свой вебмастер), а охотников платить за каждый клик в Директе на порядок меньше, несмотря на позитивные тренды в контекстной рекламе. Может, аукцион VCG все исправит? Поживём — увидим.
Однако у корпорации добра свои причуды. Google плохо говорить по-русски практически не распознаёт словоформы. В отличие от Директа, в AdWords нужно исключать все словоформы минус-слов (что уже само по себе морока). При этом количество исключаемых элементов на уровне одной кампании по внутренним ограничениям системы — не более 5000, а в сумме по всем кампаниям в аккаунте – не более 1 млн. Казалось бы, немало, и должно быть достаточно, но владельцам больших рекламных аккаунтов, уверен, так не покажется.
В общем, вывод, который я сделал для себя, работая с Директом и AdWords – для достижения максимальных результатов будешь вынужден копаться в словоформах, какой бы инструмент ни использовал. Поэтому мне нужна была полная база словоформ, желательно наиболее близкая к алгоритмам Яндекса. Я был невероятно рад, когда узнал о ныне ещё живущем, и дай ему Бог ещё здоровья и долгих лет жизни, Зализняке Андрее Анатольевиче, который и создал такой словарь. Данный словарь содержит порядка 100 000 смысловых парадигм, у самой «многогранной» из которых 182 словоформы. В сумме весь словарь составляет чуть более 2,5 млн. слов. Он лёг в основу множества систем распознавания морфологии. Именно этот словарь в электронной табличной форме я нашёл на просторах Интернета и успешно интегрировал в Excel для рабочих нужд.
У любопытных может возникнуть вопрос — зачем было нужно вставлять в Excel 2,5 млн. слов?
И у меня на это 5 причин, отвечаю:
-
Во-первых, было просто любопытно, что за словарь. Дело в том, что Яндекс начал поддержку словоформ, взяв за основу и используя как базу данных именно его. Далее, конечно, программисты Яндекса существенно продвинулись, что видно хотя бы по последней версии Mystem, в которой присутствует алгоритм снятия омонимии, о которой написано выше (как я понимаю, алгоритм распознает части речи близлежащих слов, и на базе этой информации строит предположения о части речи у исходного «многозначного» слова). Но тем не менее, основное конкурентное преимущество нашего интернет-гиганта — поддержка морфологии «великого и могучего» — результат проделанной работы и частично дело рук 80-летнего профессора.
без встроенной нормализации был никуда не годен, и я сам это осознавал. Какой смысл специалисту по контекстной рекламе кластеризовать не нормализованное ядро? Все равно придётся заходить в веб-интерфейс, там нормализовать запросы, копировать и далее уже обрабатывать в Excel.
Рождение лемматизатора и бинарный поиск
Поэтому я и решил, что создам свой лемматизатор, с блэкджеком пресловутыми макросами и формулами. По ходу дела внесу ясность: лемматизация — процесс приведения слоформы к лемме — начальной словарной форме (инфинитив для глагола, именительный падеж единственного числа — для существительных и прилагательных).
Результат стараний можно скачать по ссылке: Робот-распознаватель — 3
Визуально файл практически не отличается от предыдущей версии. Разница лишь в том, что в него добавлены два дополнительных листа (словарь) и макрос, выполняющий поиск по ним, и возвращающий начальную форму. Поскольку ограничения Excel – 2 в 20-й степени строк минус одна строка (чуть больше миллиона), пришлось разделить словарь на 2 листа и составлять макрос исходя из этой особенности. Изначально предполагалось, что данные займут 3 листа, но на счастье, в словаре оказалось порядочное количество дублей. Дублями они являются для компьютера, для человека это могут быть разные словоформы разных парадигм.
В основе файла — гигантский по меркам файла Excel массив. Обработка такого массива данных требует больших ресурсов и может быть довольно медленной. Эту проблему как раз и решил бинарный (двоичный) поиск в Excel, который я упомянул в начале. Линейный алгоритм поиска может построчно пробегать по всем 2,5 млн.+ записей — это займёт очень много времени. Бинарный поиск позволяет обрабатывать массивы данных очень быстро, так как выполняет четыре основных шага:
- Массив данных делится пополам и позиция чтения перемещается в середину.
- Найденное значение (пусть n) сравнивается с тем, которое мы ищем (пусть m).
- Если m > n, то берется вторая часть массива, если m < n — первая часть.
- Далее шаги 1-3 повторяются на выбранной части массива данных.
Все формулы и макросы работают только в оригинальном файле, и не будут работать в других. И ещё. Если вы будете дополнять словарь в файле, то перед обработкой файла, необходимо отсортировать словарь в алфавитном порядке — как вы уже поняли, этого требует логика бинарного поиска.
Конечно, назвать решение самым изящным не получится хотя бы ввиду использования огромной формулы на 3215 символов. Желающие увидеть её воочию и попробовать разобраться в логике могут зайти и посмотреть.
СЖПРОБЕЛЫ(ЕСЛИ(substring(A1;" ";1)
Однако огромная формула — не единственная проблема, с которой пришлось столкнуться в ходе работы над лемматизатором.
-
Словарь Зализняка — старое издание (1977 год) и среди словоформ нет некоторых самых простых и привычных для 2015 года слов, например, «компьютерный». Именно поэтому его дорабатывает Яндекс, дорабатываю я и при необходимости может доработать любой. Проблема до конца не решена, но ждите скорые апдейты «Робота» — всё будет.
Кроме того, в «Роботе-распознавателе 2» не будет вышеупомянутых ошибок других лемматизаторов, в которых, например, «авито» считается словоформой и возвращает глагол «авить» и генерируются многочисленные словоформы этого несуществующего глагола.
P.S.:Пожелания и багрепорты приветствуются.
Сейчас Дмитрий работает над очередным инструментом, который будет производить обратные операции: генерировать словоформы заданных слов, а не возвращать лемму. Мы ждём очередной поток макросов и гигантских формул. Наряду с системами автоматизации контекстной рекламы, мы в RealWeb активно пользуемся роботами-распознавателями в Excel — это серьёзное подспорье в работе с семантическим ядром, необходимым для работы с web в целом и с онлайн-рекламой в частности.l — это серьёзное подспорье в работе с семантическим ядром, необходимым для работы с web в целом и с онлайн-рекламой в частности. Уверены, что эти инструменты пригодятся и вам!
Тема автоматизированных систем для торговли на бирже довольно популярна в рунете в последние несколько лет. Однако начинающим инвесторам создать своего торгового робота может быть нелегко. Сегодня мы расскажем о том, как это можно сделать без лишних затрат.
Примечание: любая инвестиционная деятельность на бирже связана с определенным риском, это нужно учитывать. Кроме того, для запуска своего торгового робота вам понадобится брокерский счет, открыть его можно онлайн. Вы можете отладить свою стратегию с помощью тестового доступа с виртуальными деньгами.
Варианты создания роботов
Существует несколько вариантов создания роботизированного софта для торговли на бирже:
- Создание роботов для работы на прямом подключении – такие системы работают «в обход» торговой системы брокера, отправляя заявки напрямую в «движок» торговой системы биржи. Этот вариант используют уже опытные трейдеры, которые готовы платить в том числе и за такой способ подключения.
- Подключение к брокерской торговой системе по API. Некоторые брокеры позволяют подключать внешний торговый софт к своим торговым системам по специальным интерфейсам. Клиенты ITI Capital могут делать это с помощью API SMARTcom. В этом случае роботы могут быть достаточно сложными.
- Автоматизация операций напрямую в торговом терминале. Наиболее простой, подходящий для новичков способ, заключается в том, чтобы автоматизировать торговлю напрямую в базовой программе любого трейдера – терминале.
Как это работало раньше
Торговые терминалы предыдущих поколений можно было интегрировать с различными инструментами автоматизации. Одним из наиболее популярных, как ни странно, в свое время был Excel. С его помощью трейдеры могли настроить экспорт данных из торгового терминала, а также получать торговые приказы.
Меню для подключения Excel в одном из торговых терминалов прошлого поколения
Также распространенной практикой среди трейдеров было подключение к своим терминалам мощных систем технического анализа и разработки роботов вроде WealthLab и MetaStock. В таких случаях интеграция обычно осуществляется с помощью дополнительных библиотек.
В перечисленных случаях трейдер получал возможность автоматизации, и, в случае MetaStock и WealthLab, создания довольно сложных торговых систем, но связки с внешними программами часто оказывались ненадежными. С течением времени эта проблема была решена – в некоторых торговых терминалах появились встроенные языки программирования.
Как это работает теперь: изучаем язык TradeScript
В наши дни на российском рынке самый простой способ создать несложного торгового робота, работающего с системой брокера, это использование терминала SMARTx.
В нем есть специальный плагин с конструктором торговых роботов TradeScript. С помощью простого, но довольно мощного скриптового языка трейдеры могут создавать механические системы различного уровня сложности. Язык был изначально создан для разработки торговых роботов, он довольно прост в изучении, а многие алгоритмы схожи по написанию с Metastock, что облегчает работу пользователям, знакомым с этим программным пакетом.
Плюсом TradeScript по сравнению с Wealth-Lab и тем же Metastock является отсутствие необходимости создания сложных конструкций и использования различных коннекторов для передачи приказов в торговый терминал. Конструктор роботов встроен в SMARTx, что позволяет добиваться значительно более высокой надежности и быстродействия.
Вот пример торговой стратегии, записанной на TradeScript:
В пакете с TradeScript поставляет и модуль бэктестинга, который позволяет оценить продуктивность работы описанной стратегии на исторических данных. Помимо прочего, в системе реализована функция тестирования торговой системы «на лету» с использованием текущих биржевых данных, но без вывода приказа на биржу — время виртуальной сделки, цена и получившаяся «доходность» будут показываться в отдельном окне.
Кроме того пользователь может запускать столько одновременно работающих алгоритмов, сколько позволит тактовая частота процессора и память компьютера. Учитывая большое число слов и операндов скриптового языка, это означает возможность создания сколько угодно сложных торговых стратегий.
Что еще: отладка на тестовом доступе
Использование встроенной в торговый терминал функциональности по разработке торговых роботов – удобный и надежный способ автоматизации торговли. Однако несмотря на существующие функции для тестирования стратегий, не стоит пренебрегать и дополнительными возможностями отладки.
Поэтому мы рекомендуем перед запуском стратегии для торговли реальными деньгами «прогнать» ее на тестовом доступе. Этот шаг позволит отладить все моменты, включая реакцию программы на осуществленные сделки, без риска реальных финансовых потерь. Применение анализа с помощью исторических данных, проверки «на лету» и использование тестового доступа позволит максимально полно отладить вашу стратегию.
Раздумывая над реализацией своей торговой идеи, многие игроки отказываются от использования знакомых всем нам офисных приложений, так как считают, что это слишком сложно, к тому же технология прошлого века. Но это не так, и, чтобы доказать обратное, мы разберем простейший пример. Покажем, какие этапы нужно пройти, чтобы создать робота в MS Excel и системе интернет-трейдинга NetInvestor, который выставляет заявки по стратегии на основе осциллятора Momentum.
Начнем с базовой технологии NetInvestor API, которая позволяет работать с рыночными данными и отправлять приказы напрямую из MS Excel.
Яир Голдфингер
Яир Голдфингер, в свое время создавший ICQ, после продажи компании America Online занялся написанием торговых роботов для торговли на NASDAQ. Свою первую механическую торговую систему он написал в Microsoft Excel, однако сделки на бирже совершал руками. Пакет MS Excel по-прежнему является одной из самых используемых трейдерами программой, в силу ее гибкости и простоты для анализа рыночных данных. Экспортировать данные в MS Excel умеет большинство программ для интернет-трейдинга. |
При работе с этой технологией доступно не только получение биржевых данных, но и отправление заявок, тейк-профитов и стоп-лоссов. Главным преимуществом является получения данных в MS Excel без настройки экспорта из торговых терминалов, что позволяет получать данные быстрее.
Стратегия победителя
Для примера рассмотрим стратегию торговли с помощью индикатора Momentum. Мы будем продавать, когда Momentum снижается с максимальных значений, и покупать, когда он растет от минимума (см. рисунок). Подробно останавливаться на описании самого индикатора мы не будем, он достаточно хрестоматийный.
Следующим шагом мы должны четко определить последовательность действий нашей связки.
- 1. Соединение Excel с сервером NetInvestor.
- 2. Получение рыночных данных.
- 3. Анализ рыночных данных.
- 4. Выставление заявки на торги.
- 5. Получение информации о выставленной заявке.
Теперь необходимо открыть окно редактора макросов и скриптов VBA в MS Excel с помощью команды Alt + F11 и объявить нужные нам переменные, классов которые понадобятся:
Public WithEvents NISession As NiApiLib.SrvrSession — соединение с сервером
Public WithEvents TableFu As NiApiLib.Table — таблица заявок
Public NIFilterFutures As NiApiLib.Filter — ограничение трафика только по одной ценной бумаге
Public Row As NiApiLib.DataRow — здесь содержатся данные
Public DataPos As Long, PrevMinute As Date — служебные переменные
Public O As Double, H As Double, L As Double, C As Double — сохранение цены в формате OHLC (Open, High, Low, Close)
Dim FuturesValueList() As String — в массиве будут указаны время, цена в формате OHLC и индикатор Momentum
1. Соединение с сервером NetInvestor
Для подключения к серверу необходимо знать IP-адрес, порт сервера, а также логин и пароль. В нашем примере мы будет торговать на демо-сервере, который не требует криптозащиты и доступен на бесплатной основе.
Подключение к серверу на этом завершено. Чтобы прервать соединение, нужно лишь вызвать команду NISession.Disconnect.
2. Получение рыночных данных
Получение рыночных данных осуществляется из таблицы всех сделок на рынке FORTS: ALL_TRADES_PSFU (если брать данные с биржи ММВБ, то используем ALL_TRADES). Чтобы ограничить данные только по одному инструменту, что снизит нагрузку на робота, воспользуемся объявленным раньше фильтром и откроем таблицу на получение данных:
Private Sub GetStartedButton_Click() — открытие таблицы
Call TableFu.Open(NISession, "ALL_TRADES_PSFU", tabNoneAddr)
NIFilterFutures.Structure = TableFu.Structure
NIFilterFutures.Add "I_NAME", conEqual, Cells(11, 2)
Call TableFu.AddUpdateFilter(0, ELogicalOperation.logAnd, "I_NAME", conEqual, Cells(11, 2)) —
устанавливаем фильтр, чтобы Excel работал быстро и данные приходили только по этому инструменту
TableFu.SetOnlineUpdates subEnabled — режим включенного обновления данных
TableFu.GetData NIFilterFutures — команда получения данных
End Sub
В таблицу мы запишем полученные данные в виде массива, который будем обрабатывать. Запись осуществляется в обработчике onInsert, который вызывается, как только на рынке будут новые сделки.
Private Sub TableFu_OnInsert(ByVal NiDataRow As Object, ByVal TableName As String) — обработка поступивших обновлений
Dim CurMinute As Date, momValue As Double, tmpLast As Double
CurMinute = TimeValue(NiDataRow.Item("I_TIME")) — получаем время сделки
tmpLast = NiDataRow.Item("I_LAST") — цена сделки
Записываем данные в массив в формате время, OHLC, Momentum:
If tmpLast > H Then
H = tmpLast
End If
If tmpLast < L Then
L = tmpLast
End If
If Minute(CurMinute) <> Minute(PrevMinute) Then
C = tmpLast
PrevMinute = TimeValue(NiDataRow.Item("I_TIME"))
ShiftFuturesValues
FuturesValueList(Period, 0) = NiDataRow.Item("I_TIME")
FuturesValueList(Period, 1) = O
FuturesValueList(Period, 2) = H
FuturesValueList(Period, 3) = L
FuturesValueList(Period, 4) = C
momValue = Momentum(5)
FuturesValueList(Period, 5) = momValue
O = tmpLast
H = tmpLast
L = tmpLast
C = tmpLast
Вызов стратегии:
Strategy
Вывод данных в Excel для построения графика:
WritePrices
End If
End Sub
3. Анализ рыночных данных
Функция Strategy содержит код, который анализирует данные и отправляет заявки на биржу. Разработчик робота может добавить для себя другие индикаторы или фильтры. Чтобы написать более сложную стратегию, мы для простоты ограничимся одним индикатором.
Private Sub Strategy()
If FuturesValueList(Period - 1, MOMENTUM_INDEX) <> "" Then
If FuturesValueList(Period - 1, MOMENTUM_INDEX) < -50 And FuturesValueList(Period, MOMENTUM_INDEX) >-50 Then
Call SendOrder(PriceHigh, 1, "B")
End If
If FuturesValueList(Period - 1, MOMENTUM_INDEX) > 250 And FuturesValueList(Period, MOMENTUM_INDEX) < 250 Then
Call SendOrder(PriceLow, 1, "S")
End If
End If
End Sub
4. Выставление заявки на торги
Отправка заявки или выполнение любой транзакции осуществляется созданием объекта ITransaction с названием транзакции и передачей ей параметров. Транзакция исполнится в рамках сессии подключения к серверу, поэтому достаточно трех объектов: сессия, транзакция и строка со значениями.
Private Sub SendOrder(Price As Double, Qty As Integer, Direction As String)
Dim oTransaction As NiApiLib.Transaction, oRow As NiApiLib.DataRow
Set oRow = CreateObject("NiApi.DataRow")
Set oTransaction = CreateObject("NiApi.Transaction")
oTransaction.Open NISession, "FutAddOrder", 0
oRow.Structure = oTransaction.Structure
oRow.Item("I_SECCODE") = Cells(11, 2) — ценная бумага
oRow.Item("I_BUYSELL") = Direction — указать тип операции: B — для покупки, S — для продажи
oRow.Item("I_QUANTITY") = Qty — размер позиции
oRow.Item("I_PRICE") = Price — цена позиции
oRow.Item("I_MKTLIMIT") = "M" — для лимитированной заявки
oRow.Item("I_BROKERREF") = "780" — идентификатор клиента
oRow.Item("I_SECBOARD") = "PSFU" — идентификатор рынка
oRow.Item("I_ACCOUNT") = "AC1" — счет
If MsgBox("Send order: " & Price & " " & Qty & " " & Direction, vbOKCancel) = vbOK Then
Call oTransaction.Execute(oRow)
End If
End Sub
5. Получение информации о выставленной заявке
Аналогично подписке на рыночные данные можно подписаться на данные о получении своих заявок и сделок. Сделки по портфелю можно записывать в массив для анализа доходности. Можно создать быстрые кнопки для остановки робота или закрытия всех позиций по одному клику, а также выставлять автоматические защитные «стопы».
В итоге мы получили полноценный торговый клиент на базе MS Excel. На практике робот может вести торговлю параллельно с человеком. То есть на одном торговом счете. Трейдер может смотреть и корректировать позиции робота через торговый терминал NetInvestor. Для этого просто создаются дополнительные логин и пароль.
Читайте также: