Функция акции в excel не работает

Free API Мосбиржи в формулах Microsoft Excel

Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.

Microsoft Excel с формулами получения данных с Мосбиржи

И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.

Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).

При работе с Microsoft Excel есть некоторые нюансы:

Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.

API Московской биржи в формулах MS Excel

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

Идентификатор режима торгов

В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.

Идентификатор режима торгов для акций Тинькофф

Также этот идентификатор можно посмотреть через обычный HTTP-запрос к API:

Поиск через HTTP-запрос к API Мосбиржи по слову Пермь

Автоматическое получение имени акций, облигаций и ETF

Очень удобно, что можно получить полное или краткое наименование инструмента. Для облигаций полное название особенно понятно.

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение текущих цен

На этой вкладке представлены актуальные примеры для получения цен акций, облигаций и ETF с Московской биржи.

Цена предыдущего дня берётся через PREVADMITTEDQUOTE, а не LAST с 15 минутной задержкой, поскольку по некоторым низко ликвидным инструментам через LAST цены может просто не быть.

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение дивидендных выплат для акций

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

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение облигационных выплат

По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение дат оферт

Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется 🙂 Но это на страх и риск пользователей.

API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.

Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.

Источник

Как использовать функцию встроенных акций в Microsoft Excel

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

Добавьте данные о запасах в таблицу Excel

Чтобы использовать тип данных Stocks в Microsoft Excel, вам нужно только подключение к Интернету и немного собственных данных для начала.

Откройте электронную таблицу и введите информацию, например название компании или символ акций. Не снимая выделения с ячейки, откройте вкладку «Данные» и нажмите «Акции» в разделе «Типы данных» на ленте.

Через несколько секунд (в зависимости от вашего интернет-соединения) вы можете увидеть, что справа открыта боковая панель «Выбор данных». Это происходит, когда ваш товар не может быть найден или доступно более одной акции с таким названием.

Нажмите «Выбрать» под любым из доступных вариантов на боковой панели.

Если вы щелкните в стороне от ячейки, содержащей ваши исходные данные, просто выберите ее снова, а затем щелкните небольшой значок «Вставить данные», который появляется рядом с ней. Появится длинный прокручиваемый список, содержащий всю информацию о фондовом рынке, которая может вам понадобиться.

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

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

Просмотр карты типа данных

Прокручиваемый список сведений об акции полезен, но вы также можете просмотреть те же данные в формате карты. Просто щелкните правой кнопкой мыши ячейку, содержащую компанию или акции, которые вы впервые ввели, а затем выберите «Показать карточку типа данных» в появившемся меню.

Затем вы можете увидеть те же детали в удобном для чтения снимке. Вы также можете перетащить правый нижний угол, чтобы увеличить карточку, если это необходимо. Чтобы добавить информацию о перемещении с карточки на лист, просто наведите курсор на этот фрагмент данных, а затем щелкните значок «Извлечь в сетку».

Обновите данные о запасах

Как и тип данных Geography в Microsoft Excel, тип данных Stocks извлекает информацию из онлайн-источника. Это означает, что вы можете обновить его, не открывая веб-браузер.

Для этого просто щелкните правой кнопкой мыши ячейку, содержащую название компании или символ акций, а затем выберите Тип данных> Обновить в появившемся меню.

Не видите функцию «Акции»?

Если вы не видите тип данных Stocks в Microsoft Excel, убедитесь, что ваша система соответствует следующим требованиям:

Источник

Получение котировок акций

Чтобы вставить цену акций в Excel, сначала преобразуем текст в тип данных Stocks (Акции). Затем вы сможете извлечь в другой столбец нужные сведения из этого типа данных, такие как цены акций или их изменения.

Примечание: Тип данных Stocks (Акции) доступен толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.

Введите текст в ячейки. Например, в каждой ячейке введите тикер, название компании или название фонда.

Затем выберем ячейки.

Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, выберите Вставка > Таблица.

Выбирая ячейки, перейдите на вкладку Данные и нажмите кнопку Акции.

Если Excel обнаружит совпадение между текстом в ячейках и веб-источниками, текст будет преобразован в тип данных Stocks. Вы будете знать, что они преобразованы, если у них есть значок акций: .

Выберите одну или несколько ячеек с типом данных, и появится добавить столбец. Нажмите эту кнопку, а затем щелкните имя поля, чтобы извлечь дополнительные сведения. Например, для акций можно выбрать Price.

Чтобы увидеть все поля, доступные для компании или фонда, щелкните значок акций ( ) или выберите ячейку и нажмите CTRL+SHIFT+F5.

Если вы видите вместо значка, Excel не удается сопоставить текст с данными в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Кроме того, вы можете щелкнуть , чтобы открыть область выделения. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.

Вы также можете писать формулы, ссылаясь на типы данных, или использовать функцию STOCKHISTORY.

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

Источник

Почему пользовательская функция не работает: проблемы и решения

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

Вот о каких проблемах мы поговорим:

Почему не пересчитывается пользовательская функция?

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

Это позволяет избежать ненужных вычислений, которые ничего не меняют на вашем рабочем листе.

Это относится и к пользовательским формулам. Excel не может проверить код VBA и определить другие ячейки, которые также могли повлиять на результат.

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

Чтобы решить эту проблему, вам просто нужно использовать оператор Application.Volatile. Прочтите следующую главу, чтобы получить пошаговые инструкции по его применению.

Как создаются волатильные и не волатильные функции.

По умолчанию пользовательская функция в Excel не волатильная. Это означает, что она пересчитывается только тогда, если значение любой из ячеек, на которые она ссылается, изменится. Но если изменятся формат ячеек, имя рабочего листа, имя файла, то никаких изменений не произойдет.

К примеру, вам необходимо записать в ячейке имя вашей рабочей книги.

Для этого вы создали пользовательскую функцию:

Вы записали =WorkbookName() в ячейку и получили там имя файла. Но вот вы решили переименовать файл и сохранили его под другим именем. И вдруг видите, что значение в ячейке не изменилось! Там по-прежнему старое имя файла, которое уже не существует.

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

Как решить эту проблему, чтобы лишний раз не нажимать клавиши?

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

То есть, ваш код будет выглядеть так:

Ваша функция стала волатильной. То есть, она будет пересчитана автоматически, если была изменена любая ячейка на рабочем листе или произошло любое другое изменение в рабочей книге. Как только вы, к примеру, переименуете рабочую книгу или лист, эти изменения вы увидите немедленно.

Однако имейте в виду, что слишком много волатильных функций может замедлить работу Excel. Ведь многие формулы выполняют достаточно сложные вычисления и работают с большими диапазонами данных.

Поэтому рекомендую применять волатильность только там, где это действительно необходимо.

Почему пользовательской функции нет в списке?

При вводе первых букв имени пользовательской функции она появляется в выпадающем списке рядом с ячейкой ввода, как и стандартные функции Excel.

Вы можете видеть такой пример на скриншоте ниже.

Однако, это происходит не всегда. Какие ошибки могут стать причиной того, что вы не видите вашу функцию в списке?

Если у вас Excel 2003-2007, то пользовательская функция никогда не появляется в выпадающем списке. Там вы можете увидеть только стандартные функции.

Но даже если вы используете более новую версию Excel, есть еще одна ошибка, которую вы можете случайно сделать.

Пользовательская функция должна находиться в стандартном модуле VBA. Что это означает?

Когда вы добавляете новый модуль для записи кода, автоматически создается папка Modules, в которую записываются все модули. Это вы видите на скриншоте ниже.

Но иногда случается, что новый модуль не был создан.

На скриншоте ниже вы видите, что код находится в таких модулях, как ThisWorkbook или Sheet1.

Нельзя размещать настраиваемую функцию в области кода рабочего листа или рабочей книги. Эту ошибку вы и видите на скриншоте ниже.

В этом случае функция работать не будет. Она также не появится в выпадающем списке. Поэтому код всегда должен находиться в папке Modules, как показано на первом скриншоте.

Не отображается подсказка для пользовательской функции

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

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

Для этого я предлагаю дополнительно использовать метод Application.MacroOptions. Он поможет вам показать описание не только функции, но и каждого её аргумента в окне Мастера. Это окно вы видите, когда нажимаете кнопку fx в строке формул.

Давайте попробуем создать такую справку.

Ранее мы с вами рассматривали настраиваемую функцию GetMaxBetween(). Она находит максимальное число в указанном интервале и использует три аргумента: диапазон числовых значений, а также максимальное и минимальное значение для поиска.

Добавим в нее описание и справочную информацию. Для этого нужно создать и запустить команду Application.MacroOptions.

Для GetMaxBetween вы можете выполнить следующую команду:

Переменная strFuncName — это имя. strDescr —описание. В переменных strArgs записаны подсказки для каждого аргумента.

Вы спросите, для чего нужен четвертый аргумент Application.MacroOptions? Этот необязательный аргумент называется Category и указывает на категорию функций Excel, в которую будет помещена наша пользовательская функция GetMaxBetween(). Вы можете присвоить ему имя любой из существующих категорий: Математические, Статистические, Логические и т.д. Можно указать имя новой категории, в которую вы будете помещать созданные вами макросы. Если же не использовать аргумент Category, то она будет автоматически помещена в раздел «Определенные пользователем».

Вставьте код в окно модуля, как показано на скриншоте ниже:

Затем нажмите кнопку “Run”.

Команда выполнит все настройки для использования кнопки fx с вашей функцией GetMaxBetween().

Теперь посмотрим, что у нас получилось. Если попробовать вставить в ячейку функцию при помощи инструмента Вставка функции, то увидите, что GetMaxBetween() находится в категории «Определенные пользователем»:

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

В окне Мастера вы видите описание для функции, а также подсказку для первого аргумента. Если установить курсор на второй или третий аргумент, вы также увидите подсказки для них.

Если вы захотите поменять текст этих подсказок, в коде RegisterUDF() измените значения переменных strDescr и strArgs. Затем снова выполните команду RegisterUDF().

Если вы захотите отменить все сделанные настройки и очистить описание, выполните этот код:

Есть еще один способ получить подсказку при вводе пользовательской функции.

Введите название функции и затем нажмете комбинацию CTRL+SHIFT+A :

Вы увидите список всех аргументов функции:

К сожалению, здесь вы не увидите описания функции и её аргументов. Но если названия аргументов достаточно информативны, то это может помочь. Всё же это лучше, чем ничего.

Чтобы создать интеллектуальную подсказку, которая будет работать так же, как в стандартных функциях Excel, необходимо немного больше усилий. К сожалению, Microsoft не предоставляет для этого никаких возможностей.

Единственным доступным решением в настоящее время является Excel-DNA IntelliSense extension. Более подробную информацию вы можете найти по этой ссылке.

Надеюсь, эти рекомендации помогут вам решить проблему, когда ваша пользовательская функция не работает либо работает не так, как вам хотелось бы.

Источник

Как импортировать цены на акции и географию в Excel

В отличие от диаграмм и графиков, где необходимо заполнить данные, инструменты Excel для определения цены акций и географии позволяют импортировать (и обновлять в реальном времени) данные из Интернета.

Большинство людей знают, как добавлять диаграммы или графики в Excel, но знаете ли вы, что вы также можете импортировать цены на акции и географическую информацию?

В отличие от диаграмм и графиков, где необходимо заполнить данные, инструменты Excel для определения цены акций и географии позволяют импортировать (и обновлять в реальном времени) данные из Интернета. Давайте посмотрим, как это работает.

Примечание. Следующие функции доступны только в Excel для Microsoft 365 (для настольных компьютеров и в Интернете) и Excel для устройств iPad, iPhone и Android.

Как импортировать цены на акции в Excel

Прежде чем пытаться создать полную панель мониторинга, рекомендуется сначала изучить простой импорт данных. Цены на акции очень легко привести в Excel. Однако, если вы не знаете, как это работает, простое нажатие на инструмент Stocks ничего не изменит.

Вот как это работает.

Чтобы начать, создайте столбец в Excel и перечислите все биржевые символы, для которых вы хотите биржевые котировки. Выберите все эти символы. Затем выберите Данные в меню и в группе «Типы данных» на ленте выберите Запасы,

Excel получит доступ к Интернету и получит данные для выбранных вами символов. Вы будете знать, что это было успешно, потому что символ изменится на правильное название компании.

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

Выбрав значок «Вставить данные», вы увидите раскрывающийся список точек данных, которые можно импортировать из Интернета.

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

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

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

Как импортировать географию в Excel

Сбор информации о разных странах работает так же, как сбор данных о запасах.

Самый простой способ сделать это — создать столбец со списком стран, для которых вы хотите получить данные.

Далее выберите Данные в меню и выберите география из группы типов данных на ленте.

Вы узнаете, что страны были признаны действительными по значку, который появляется слева от названия страны. Это также означает, что для этих стран доступны наборы данных.

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

Как и в случае с запасами, выбирайте каждое поле по одному.

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

Как видите, импортировать географические наборы данных легко и быстро с помощью инструмента «География» в Excel. Это также отличный способ провести исследование и найти корреляции в данных по стране.

Имейте в виду, что вы не ограничены только данными страны. Подойдет любой географический идентификатор. Например, вы можете сравнить данные о доходах и населении между несколькими штатами США. Просто введите состояния и повторите процесс выше.

Это работает и для названий городов.

Обновление данных запаса и географии в Excel

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

Для этого вы вставите специальный код VBA это запустится, когда откроется ваша электронная таблица.

Выбрать разработчик в меню и в разделе «Элементы управления» ленты выберите Посмотреть код,

Запись: Если вы не видите разработчик в меню Excel, затем добавьте его. Выбрать файл, Опции, Настроить лентуи выберите Все команды из выпадающего списка слева. Тогда двигайся разработчик от левой панели вправо и выберите OK, чтобы закончить.

После того, как вы окажетесь в редакторе кода, вам нужно найти правильный код, который будет запускаться при открытии таблицы. Для этого дважды щелкните ThisWorkbook, чтобы выбрать этот объект. В раскрывающемся меню слева над панелью кода выберите рабочая тетрадь, В правом раскрывающемся списке выберите открыто,

Наконец, вставьте следующий код в функцию Workbook_Open ():

ActiveWorkbook.RefreshAll
NextTime = Time + TimeSerial(0, 1, 0)
Application.OnTime NextTime, «AutoRefresh»

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

Это только один из примеров креативных советов по Excel, которые могут повысить вашу производительность. Существует множество других замечательных советов по Excel.

Источник