Гугл финанс перестал транслировать данные российских акций — что делать? (UPD: починили)
С 5 июня 2021 года сайт гугла, и самое главное гугл таблицы — перестали отдавать данные с Московской биржи.
При попытке получить котировки с префиксом MCX, например для Сбербанка, формулой из гугл таблиц =GOOGLEFINANCE(«MCX:SBER») теперь всегда возвращается результат #N/A .
А при поиске любой российской бумаги на сайте Google находятся все рынки, кроме Московской биржи:
В этой статье я рассказываю как получать в гугл таблицу биржевые данные с помощью API Московской биржи.
Копирование формул из таблицы-примера в ваши собственные таблицы
Вы можете свободно копировать формулы из моей таблицы с примером для использования в собственных таблицах, потому что на формулах нет и не может стоять никакой защиты.
Если после копирования из таблицы-примера в вашу таблицу формула не работает, то проверьте региональные настройки вашей таблицы.
Я использую регион Соединенные Штаты, а если по умолчанию ваш регион Россия, то формулы корректно НЕ копируются!
Вот подробная инструкция как проверить региональные настройки конкретной таблицы:
- Откройте файл в Google Таблицах на компьютере.
- Нажмите Файл затем Настройки таблицы.
- Выберите нужные варианты в разделах «Региональные настройки».
- Нажмите Сохранить настройки.
Если вы не хотите менять регион в своей собственной таблице, то можно просто изменить разделитель в формуле. Для корректной работы надо изменить разделитель с запятой на точку с запятой.
Получение названий акций и облигаций
Можно получать названия акций и облигаций используя сервера Московской Биржи вместо переставшего работать гугл финанса. Сама формула при этом выглядит следующим образом:
=IMPORTxml( "https://iss.moex.com/iss/engines/stock/markets/" & IFS( or( B3 = "TQOB", B3 = "EQOB", B3 = "TQOD", B3 = "TQCB", B3 = "EQQI", B3 = "TQIR" ), "bonds", or( B3 = "TQTF", B3 = "TQBR", B3 = "SNDX", B3 = "TQIF" ), "shares" ) & "/boards/" & B3 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME", "//row[@SECID='" & A3 & "']/@SECNAME" ) Получение цен акций и облигаций
Можно получать цены акций и облигаций используя сервера Московской Биржи вместо переставшего работать гугл финанса. Сама формула при этом выглядит следующим образом:
=IMPORTxml( "https://iss.moex.com/iss/engines/stock/markets/" & IFS( or( B10 = "TQOB", B10 = "EQOB", B10 = "TQOD", B10 = "TQCB", B10 = "EQQI", B10 = "TQIR" ), "bonds", or( B10 = "TQTF", B10 = "TQBR", B10 = "SNDX", B10 = "TQIF" ), "shares" ) & "/boards/" & B10 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE", "//row[@SECID='" & A10 & "']/@PREVADMITTEDQUOTE" ) Получение даты и значения дивиденда для акций
Можно получать даты и значения дивидендов для акций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских акций. Сама формула при этом выглядит следующим образом:
=iferror( INDEX( IMPORTxml( "http://iss.moex.com/iss/securities/" & A22 & "/dividends.xml?iss.meta=off", "//row[@secid='" & A22 & "']/@value" ) , ROWS( IMPORTxml( "http://iss.moex.com/iss/securities/" & A22 & "/dividends.xml?iss.meta=off", "//row[@secid='" & A22 & "']/@value" ) ) , 1 ) , "нет" ) Получение даты купона и значения для облигаций
Можно получать даты и значения купонов для облигаций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских облигаций и вообще облигаций в сервисе нет. Сама формула при этом выглядит следующим образом:
=IMPORTxml( "https://iss.moex.com/iss/engines/stock/markets/" & IFS( or( B12 = "TQOB", B12 = "EQOB", B12 = "TQOD", B12 = "TQCB", B12 = "EQQI", B12 = "TQIR" ), "bonds", or( B12 = "TQTF", B12 = "TQBR", B12 = "SNDX", B12 = "TQIF" ), "shares" ) & "/boards/" & B12 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,NEXTCOUPON,COUPONVALUE", "//row[@SECID='" & A17 & "']/@COUPONVALUE" ) Получение даты оферты
Можно получать оферты облигаций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских облигаций и вообще облигаций в сервисе нет. Сама формула при этом выглядит следующим образом:
=IFNA( IMPORTxml( "https://iss.moex.com/iss/engines/stock/markets/" & IFS( or( B27 = "TQOB", B27 = "EQOB", B27 = "TQOD", B27 = "TQCB", B27 = "EQQI", B27 = "TQIR" ), "bonds", or( B27 = "TQTF", B27 = "TQBR", B27 = "SNDX", B27 = "TQIF" ), "shares" ) & "/boards/" & B27 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,OFFERDATE", "//row[@SECID='" & A27 & "']/@OFFERDATE" ) , "нет" ) Источник данных
Если вы хотите разобраться во всех нюансах работы — откуда берутся данные, то вы, также как и я можете обратиться к официальной документации к информационно-статистическому серверу Московской Биржи (ИСС / ISS). Правда, изучая этот документ, вы можете обнаружить что большая часть интересных функций, приведенная в этой статье, в документе никак не отображена.
С 20 июня 2021 года данные снова появились в гугл финансе!
Формулы вида =GOOGLEFINANCE(«MCX:SBER», «name») снова работают.
Итоги
Король умер, да здравствует король! Считалось, что трон никогда не должен пустовать, поэтому после смерти короля сразу же объявлялся следующий правитель.
Как и в случае c сервисом «Google Финансы», который перестал выдавать российские результаты мы видим что можно использовать API Московской биржи, которое предоставляет широкие возможности.
Эти формулы работают только за счет API Московской биржи, с которой я никак не связан. Использую ИСС Мосбиржи только в личных информационных интересах.
Google Таблицы. Урок 73. Функция GOOGLEFINANCE. Подтягиваем котировки акций и курсы валют с биржи
Google Таблицы. Урок 73. Функция GOOGLEFINANCE. Подтягиваем котировки акций и курсы валют с биржи Урок посвящен функции googlefinance. мы научимся подтягивать с биржи курсы валют и котировки акций. особенно этот урок пригодится тем, кто размещает свои деньги в акциях и следит за их котировками. Внимание! В пример вкралась ошибка. Правильный код Газпрома — GAZP, в примере указан код MOEX — это код Московской биржи Смотрите серию моих уроков по Google Sheets (Гугл таблицы) и Вы сможете профессионально и эффективно использовать этот замечательный и бесплатный инструмент от Google. Ведь Google Sheets (Гугл таблицы), мало того, что не уступают по удобству таблицам Excel, но в некоторых моментах даже превосходят их по удобству. Подписывайтесь на мой канал, ставьте лайки и оставляйте комментарии. Спасибо за просмотр!
Показать больше
Войдите , чтобы оставлять комментарии
Как конвертировать валюту в Google Таблицах
Пользователи, создающие электронные таблицы через веб-сервис от Google, часто сталкиваются с необходимостью выполнить финансовые операции, в том числе и конвертирование валют. Некоторые для этого самостоятельно находят текущий курс и создают формулы, отталкиваясь от него. Однако задачу можно автоматизировать, доверив получение актуального курса непосредственно Google. Так вы получите динамически обновляемую таблицу с постоянной корректной конвертацией.
Определение буквенных кодов валют
Единственное, что вам понадобится узнать самостоятельно – буквенные коды валют разных государств, которые будут участвовать в вашем конвертировании. Для этого введите соответствующий запрос в поисковике для отдельной валюты или для всех сразу.
Сами Google Таблицы предоставляют коды для фондовых бирж, но в рамках этой статьи мы не будем разбираться в сложных операциях котировок и акций, а сосредоточимся исключительно на использовании функции конвертирования валют, которая будет полезна многим юзерам, а не только узконаправленным специалистам.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Создание вспомогательного листа
Вы можете отображать текущий курс валюты на том же листе таблицы, где и осуществляете конвертирование, выделив для данных отдельный блок. Однако подходит такое оформление не всегда, поэтому сначала предлагаю разобраться с тем, как создать вспомогательный лист, куда и будут выведены текущие курсы всех используемых финансовых единиц.
- Создайте новый лист и укажите для него соответствующее название. Создайте список ячеек с названиями соотношения курсов валют. Например, если вас интересует отношение рубля к доллару, значит, так и напишите в названии ячейки. Это нужно для простого понимания того, какая информация будет выводиться рядом.
- Теперь займемся созданием самой функции. Объявите ее напротив первого названия курса, написав =GOOGLEFINANCE().
- Внутри скобок откройте двойные кавычки и введите соотношение валют. Например, запись рубля к доллару будет выглядеть так: «RUBUSD».
- Сделайте то же самое с остальными ячейками, изменяя соотношение валют. Запомните главное – буквы обязательно вписываются внутри двойных кавычек. Итоговая формула выглядит как =GOOGLEFINANCE(«RUBUSD»).
Отдельный лист, на который мы будем ссылаться при дальнейшем конвертировании, готов. Теперь вы знаете, что, например, 1 рубль равняется 0,01256 доллара (на момент написания статьи). Эти данные будут обновляться примерно каждые 20 минут автоматически, поэтому курс можно всегда считать актуальным. Остается только самостоятельно умножить имеющуюся сумму, чтобы понять, сколько это будет в другой валюте.
Конвертирование валют в Google Таблице
Существует множество разных ситуаций, когда нужно преобразовать одну валюту в другую. В качестве примера я взял таблицу с сотрудниками, имеющейся выплатой в рублях и необходимостью отобразить, сколько они получат в другой валюте. Это актуально, например, когда сотрудники работают в других странах и с ними есть договоренность об оплате труда в конкретной валюте. Саму исходную таблицу вы видите на следующем скриншоте.
Преобразование осуществляется путем умножения текущей суммы одной валюты на курс в другой, как и работают стандартные денежные калькуляторы. Для этого не надо использовать никаких особых функций, вам понадобится указать всего две ячейки и математическое действие между ними.
- Напишите =B2*, где B2 – ячейка со значением суммы в текущей валюте.
- Переключитесь на вспомогательный лист и выберите ячейку с курсом той валюты, в которую хотите преобразить сумму.
- Вернитесь на предыдущий лист и нажмите Enter. Вы увидите сумму в общем числовом формате, что видно по количеству знаков после запятой.
- Если сейчас растянуть эту функцию, она сработает некорректно из-за смещения ячеек, поэтому поставьте константу ($) между буквой столбца и строкой курса. В итоге у вас получится функция =B2*’ВспомогательныйЛист’!B$4, где B2 – исходная сумма, ‘ВспомогательныйЛист’! – название листа с курсом валют, а B$4 – закрепленная ячейка с выбранным курсом.
- Растяните функцию, потащив за правый край ячейки вниз, обеспечив корректное отображение всех преобразованных сумм.
- Для более понятного отображения чисел поменяем формат ячейки. Выделите сконвертированные значения, откройте меню «Формат», наведите курсор на «Числа» и выберите «Валюта».
- В списке отыщите необходимую валюту и кликните по ней левой кнопкой мыши.
- Вместо этого можно самостоятельно написать буквенное обозначение и нажать на «Применить».
- Обратите внимание на следующее изображение. Вы видите, что теперь суммы отображаются в более понятном виде с отображением используемой валюты.
Для наглядности покажу еще несколько шагов с заполнением исходной таблицы. Это поможет вам еще лучше понять метод и без проблем справиться с выполнением конвертирования.
- В следующих ячейках вы можете просто вставлять уже готовую формулу, заменяя только последнюю ячейку на ту, которая содержит в себе актуальный курс выбранной валюты. Обязательно сохраняйте знак $ между буквой и номером ячейки, чтобы корректно растянуть функцию.
- Для удобства выбирайте формат ячейки точно так же, как это было показано выше. На следующем изображении вы видите корректное форматирование в тенге.
- С третьим и последующими преобразованиями все будет точно так же, как и в предыдущие разы. Оставляйте номер ячейки с исходной суммой и меняйте номер второй на тот, которая содержит актуальный курс на вспомогательном листе.
- Если все выполнено правильно, можете менять исходные суммы в любой момент, пересчет будет выполнен автоматически с учетом текущего курса указанных валют.
Если вы создаете перечень текущих курсов без вспомогательного листа, формула будет еще короче, поскольку вам не придется ссылаться на другой лист. Указывайте необходимые ячейки и закрепляйте их при помощи константы, после чего растягивайте функции и наблюдайте актуальное конвертирование в другие валюты.
Как отслеживать биржевые данные в Google Таблицах — с помощью функции GOOGLEFINANCE
Диск Google — это не просто место для хранения файлов; это еще и мощный набор веб-приложений для повышения продуктивности. Этот урок будет сосредоточен на использовании Google Таблиц — веб приложения от Google для работы с таблицами, и на том, как работать в них с биржевыми данными.
Акции представляют собой долевую собственность компании. Акции торгуются на таких биржах как: Нью-Йоркская фондовая биржа (NYSE), NASDAQ, Лондонская фондовая биржа (LSE) и многие другие. Вы можете покупать и продавать акции через брокеров.
Google также предлагает Google Финансы как способ для просмотра биржевых данных, включая диаграммы для просмотра производительности компании на протяжении определенного времени. Самое приятное то, что Google предлагает способ легко использовать данные из их службы Google Финансы в Таблицах.
В этом уроке вы узнаете, как использовать функцию GOOGLEFINANCE в Google Таблицах для того, чтобы получить данные от Google Финансов и вставить их в электронную таблицу.
Чтобы начать работу с уроком, убедитесь, что у вас есть аккаунт Google и перейдите в Диск Google. Создайте новую таблицу и продолжайте читать, чтобы узнать больше.
Зачем нужно отслеживать данные об акциях?
Для многих отслеживание данных акций является хобби. Цена акций для многих считается показателем успешности компании. Если вы заинтересованы в успехе компании, можете следить за ценой акций для его оценки.
Для некоторых инвестирование в акции является способом спланировать свой выход на пенсию. Многие люди покупают акции, облигации и взаимные фонды, ожидая увеличение их стоимости с течением времени для финансирования себя в те годы, когда они не будут работать.
Самое главное: биржевые цены постоянно меняются. Если вы хотите контролировать цены на акции, вам нужен простой способ поддерживать ваши данные в актуальном состоянии.
Независимо от того, почему вы отслеживаете биржевые данные, Google Таблицы с подключением к Google Финансам являются самым простым способом, который я нашел, чтобы свести данные об акциях в электронную таблицу. Давайте научимся, как это сделать.
Как отслеживать акции с помощью функции GOOGLEFINANCE (короткий видео-урок)
На скриншоте ниже продемонстрировано, как построить электронную таблицу с динамическим отслеживанием акций в Google Таблицах.
Чтобы узнать больше об использовании функции GOOGLEFINANCE, ознакомьтесь с моей пошаговой инструкцией ниже.
Получение биржевых данных
Первое, что я предпочитаю сделать при настройке таблицы анализа акций — это выбрать компании, за которыми я буду следить. Вам нужно узнать их биржевые коды, которые представлены несколькими буквами для идентификации компании на фондовых биржах. Например кодом акций компании Apple является AAPL, в то время как код корпорации Майкрософт — это MSFT.
Обычно вы можете найти биржевые коды компаний через быстрый поиск, или можно использовать такой инструмент как поиск Yahoo Финансы для поиска кода акций компании по вашему выбору.
Когда я сравниваю компании, я создаю список биржевых кодов в Google Таблице в столбик:
В Таблицах Google, мы можем использовать функцию =GOOGLEFINANCE для импорта биржевых данных. Чтобы начать, щелкните ячейку и начните вводить функцию.
Нет необходимости вводить полное имя компании для каждого биржевого кода. Напишем нашу первую функцию GOOGLEFINANCE и посмотрим на биржевой код и название компании. Вот как выглядит формула, которую мы будем использовать:
В приведенном ниже примере я использую формулу в ячейке A2, которая выводит название «Volkswagen AG» по биржевому коду VLKAY. Затем просто тянем формулу вниз для вывода названия компании для каждого биржевого кода.
Теперь, когда мы составили список биржевых кодов и названий компаний, за которыми мы следим, давайте получим некоторые финансовые данные о них.
Текущий курс акций
Чтобы получить цену акции, используйте формулу GOOGLEFINANCE следующим образом:
Можно ввести символ акции в скобках, или передать формуле номер ячейки с биржевым кодом для получения цены. На скриншоте ниже вы видите использование формулы, можно потянуть ее вниз, чтобы получить цены акций для каждой компании.
Цены, которые Таблицы Google получают — это текущие цены акций и они будут обновляться при каждом открытии таблицы.
Цена акции за определенную дату
Что делать, если вы хотите зафиксировать цену акции за определенный день или узнать сколько акция стоила в прошлом? Мы можем сделать это в нашей таблице.
Давайте используем вот такую формулу, чтобы получить цену акции Amazon на момент закрытия торгов 1 февраля:
Обратите внимание, что эта формула создает сетку с некоторыми дополнительными данными, включая заголовки «Дата» и «Закрытие», а также дату и цену на момент закрытия.
Если вы хотите вывести только цену при закрытии торгов, то нужно обернуть наши формулы в INDEX и потянуть вниз только нижнее правое поле (цена). Вот формула, которую вы можете использовать:
Я просто обернул формулу в INDEX, а затем добавил «,2,2)» в конце формулы, чтобы вывести только цену акции без заголовков.
История цены на акции
Ранее я упоминал, что цена акции отражает производительность компании и будущие ожидания. Отслеживание цен на акции за определенный период времени — это отличный способ, чтобы понять, как инвесторы относятся к компании.
Вот функция, которую можно использовать для получения истории цены акции:
=GOOGLEFINANCE(«F», «price», DATE(2017,1,1), DATE(2017,2,11), «DAILY»)
Давайте разберем эту формулу:
- Формула начинается стандартным =GOOGLEFINANCE.
- «F» в скобках указывает функции получать данные для компании Ford, акции которой мы выбрали.
- Далее мы передаем функции атрибут «price», чтобы указать, что мы хотим получить цену за определенный период времени.
- Затем мы указываем даты начала и окончания периода, данные за который хотим получить.
- Наконец я указал «DAILY» в скобках, чтобы сообщить Таблицам, что мы хотим получить ежедневные котировки акций.
На скриншоте ниже вы увидите, что результаты выводятся в строках таблицы:
Теперь у вас есть список цен на акции за определенный период времени. Вы можете ввести любой период, и Таблицы автоматически будут получать данные из Google Финансов. Далее легко превратить данные в диаграммы непосредственно внутри таблицы.
Теперь у нас есть отличное представление о ценах акций. Давайте узнаем, какие еще полезные финансовые данные мы можем вставить в Таблицах.
Соотношение цены к доходу (P/E)
Коэффициент цена-доход, или P/E является общим методом оценки компаний. Он делит стоимость акций компании на ее доходы. Широко признанный принцип оценки акций состоит в том, что высокий P/E коэффициент указывает на будущий рост акций.
Чтобы получить P/E коэффициент акций от Google Финансов, используйте следующую формулу:
В примере ниже я буду получать P/E коэффициент акций с помощью ссылки на ячейку, вот пример формулы:
Затем просто перетаскиваем формулу вниз, чтобы вывести коэффициент для всех моих выбранных акций.
Как читать коэффициент P/E? Вам необходимо исследовать аналогичные компании, чтобы понять смысл этих значений. Для акций в областях с более высокими темпами роста, таких как технологии (например Google или Facebook) коэффициент P/E будет выше, чем, например, у коммунальных компаний.
Если вы хотите узнать, как рассчитывается P/E коэффициент, посмотрите это определение от Investopedia.
Рост и Падение цен за последние 52 недели
Некоторые акции сильно меняются в цене в течение года. Это поможет следить за их колебаниями.
Чтобы получить самую высокую цену, которую имели акции в течение последних 52 недель, используйте следующую формулу:
Вы также можете получить самую низкую цену закрытия акции за последние 52 недели:
На скриншоте ниже вы можете увидеть, как я применил эти формулы к списку акций:
Я часто отслеживаю, где торгуются акции в привязке к их высшей и низшей цене. Если я собираюсь получить прибыль и продать акции, я стараюсь убедиться, что акции не находятся в низкой точке.
Эти формулы содержат лишь некоторые из доступных атрибутов, которые Google Таблицы могут получать от Google Финансов. Использование сочетаний этих факторов может помочь вам отслеживать ключевые финансовые показатели и исследовать компании — будь то для удовольствия или прибыли.
Для закрепления и дальнейшего изучения
В этом уроке вы использовали Google Таблицы, чтобы создать электронную таблицу для отслеживания ситуации на бирже. Является ли это для вас просто хобби или обучающим упражнением, или может быть потенциальным источником дохода, Таблицы имеют простую интеграцию с Google Финансами для того, чтобы вы могли отслеживать котировки интересующих вас акций.
- Если какие-либо финансовые термины из этого урока были вам незнакомы, то Investopedia имеет отличный словарь финансовых терминов.
- У Google есть расширенная документация по функции =GOOGLEFINANCE, которая охватывает некоторые дополнительные особенности, которые не были затронуты в этом уроке.
- Мелисса Мёрфи написала отличный урок о том, как импортировать и экспортировать документы в Google Диск.
Интересуетесь ли вы котировками акций? Используете ли вы Google Таблицы или другой инструмент для отслеживания ваших данных? Расскажите мне об этом в комментариях.