Импорт курса биткойна в Excel через Power Query. Excel курс валют
Автоматическое обновление курса валют в Excel
Автоматическое обновление курса валют в Excel
Добрый день, уважаемые читатели! Сегодня поговорим об автоматическом обновлении курса валют. В предыдущем УРОКЕ мы рассматривали загрузку курсов валют, но чтобы они обновлялись необходимо либо нажимать кнопку «Обновить» на вкладке «Данные» либо щёлкать правой кнопкой по таблице и выбирать «XML» — > «Обновить XML данные».
Сегодня создадим макрос, который будет обновлять курсы при открытии рабочей книги.
Пойдём стандартным путем, сначала загрузим курсы валют через «Данные» — > «Из интернета» (сразу оговорюсь, данный способ опробован на сайте worldcoinicons.com, где можно отметить таблицу с курсами, но макрос, который будет ниже, подойдёт и для загрузки через XML с сайта Центрального банка России). Отмечаем галкой таблицу с курсами и жмём «Импорт», затем «ОК» (когда спросит в какую ячейку помещать курсы валют).
Теперь получим вот такую картину.
А вот теперь нам понадобится вкладка «Разработчик», где жмём кнопку «Visual Basic».
Теперь нам нужно для активного листа создать модуль и имя процедуры, которую будем вызывать при открытии книги. Жмём «Insert» -> «Module».
В тексте модуля пишем следующий текст:
Sub BTC()ActiveWorkbook.RefreshAllEnd Sub
Тем самым мы создали команду обновления всей книги (имя команды в моём случае BTC, вы можете написать что угодно!).
Далее переходим в пункту в левом меню «Эта книга» ( двойным щелчком), в поле «Object» выбираем «Workbook», в поле «Procedure» у нас само появится «Open», т.е. при открытии, далее пишем вызов команды BTC. которая и будет обновлять курс валюты.
Private Sub Workbook_Open()Call BTCEnd Sub
Тем самым мы вызываем созданную выше команду.
БУДЬТЕ ОЧЕНЬ ВНИМАТЕЛЬНЫ! Когда будете сохранять книгу выбираем тип книги .xlsm в окне сохранения иначе макросы не сохранятся!
Осталось проверить, как работает наш макрос. Для этого открываем книгу и обращаем внимание вверх, там будет кнопка «Включить содержимое», щёлкаем её, а затем смотрим в нижний левый угол программы. Там будет следующая картина.
Идёт фоновый запрос. Значит макрос работает и мы увидим обновлённый курс валют. Также хочу отметить, что иногда обновление занимает много времени (особенно с загрузкой XML данных), наберитесь терпения и всё будет хорошо!
Если что-то было непонятно — смотрим видео! Не забывайте писать комментарии и подписываться на канал!
pcandlife.ru
Курсы валют в Excel
Курсы валют в Excel
Сегодня мне понадобились для расчётов курсы валют, как их загрузить в Excel я помнил, но столкнулся с неприятной картиной — старый способ (в котором мы отмечали таблицу жёлтой стрелкой) не работает. Посмотрев в сторону макросов загрузки — получается слишком много кода. так что пришлось остановиться на способе, который предлагает Центральный банк, а именно XML.
Давайте взглянем на страницу технической информации (банк, к моему удивлению, выложил подробный комментарий для работы с XML, вот ссылка — http://cbr.ru/scripts/Root.asp?PrtId=SXML).
Так что картина получается очень простой — мне нужно скопировать строчку с адресом запроса (www.cbr.ru/scripts/XML_daily.asp?date_req=02/03/2002) и стереть её до вида — www.cbr.ru/scripts/XML_daily.asp? (это нам даст курсы валют на текущую дату без указания дополнительных атрибутов функции), если же мы хотим курсы на определённую дату, то тогда её просто нужно добавить в конец ссылки запроса (www.cbr.ru/scripts/XML_daily.asp?date_req=02/04/2017).
Теперь дело за малым — идём на вкладку «Данные», жмём «Получить данные», затем «Из Интернета», но в адресной строке открывшегося окна указываем ссылку www.cbr.ru/scripts/XML_daily.asp? или с определённой датой. Немного ждём и отфильтрованная таблица будет готова!
Если в дальнейшем будет нужно эти курсы обновить — жмём правой кнопкой по таблице, выбираем пункт XML, жмём «Обновить XML данные».
Если возникли вопросы — к вашему вниманию предлагаю видеоурок!
Как запустить автоматическое обновление курсов валют — смотрите ЗДЕСЬ.
pcandlife.ru
Фишки в Excel: секрет 1. Импорт курса валют
Чаще всего мы пользуемся возможностями программы Microsoft Word, но время от времени нам приходится обращаться и к Microsoft Excel. В основном это 5% от ее возможностей, но не стоит так бояться таблиц и формул. На самом деле это программа скрывает в себе огромное количество секретов, раскрыв которые можно облегчить себе работу, автоматизировать расчеты и многое другое.
Раскроем небольшие секреты MS Excel, которые могут быть полезны для бизнеса.
Секрет №1. Импорт курса валют
Настройка в MS Excel постоянно обновляющегося курса валют:
- На вкладке Данные группы Получение внешних данных выбрать команду Из интернета
- В появившемся диалоговом окне Создание веб-запроса в строку Адрес ввести http://www.cbr.ru и нажать кнопку Пуск.
- Интернет-страница отобразиться в окне и появятся чёрные стрелки на жёлтом фоне, которые позволяют импортировать данные в таблицу MS Excel. Щелчок по такой стрелке помечает таблицу для импорта.
- Пометить таблицу с курсом валют и нажать кнопку Импорт.
- В появившемся окне выбрать диапазон в таблице с помощью мыши. В этом же окне нажать кнопку Свойства и выбрать нужные параметры, в том числе частоту обновления.
- Нажать кнопку ОК.
- В таблице MS Excel появятся результаты, которые будут обновляться через установленное время.
Есть другой способ, который также позволит вам импортировать курс валют в таблицу.
СДЕЛАЙТЕ ДОБРОЕ ДЕЛО, ПОДЕЛИТЕСЬ С ДРУЗЬЯМИ
Добавить комментарий
nagel-heart.ru
Обновляемый курс валют в прайс-листе xls / uCozMagazines.ru
Сергей 06.03.2015 7191 5.0 из 5.0 (12)Оцените материал:
О чем здесь?
Привязки колонки цен к валюте в прайс-листе Excel и настройка автоматического обновления курса
Зачем это нужно?
Если вы работаете с поставщиками, которые дают цены в валюте, которая не совпадает с основной валютой магазина, то наверняка уже устали исправлять курс в магазине. А возможно, вы или ваши клиенты стали жертвой скачков курсов за последнее время.
Сейчас мы настроим свой прайс лист в Excel так, что нам не придется больше думать об обновлении курсов.
Настройка XLS файла
Откройте прайс и создайте в нем новый пустой лист:
Перейдите во вкладку "Данные" и выберите в ней Получить внешний данные - Из Веба:
У меня открылась страница www.yandex.ru, вы можете выбрать любую другую (например сайт Центробанка), главное, чтобы на ней был курс необходимой валюты. Щелкните по значку с черной стрелкой на желтом фоне и нажмите кнопку "Импорт":
Укажите ячейку, в которую следует поместить данные из веба:
Дождитесь, пока данные загрузятся и найдите ячейку с валютой (у меня они оказались на 83 строке):
Нажмите правой кнопкой мыши и выберите пункт "Свойства диапазона данных":
Укажите интервал обновления или дайте команду курсу обновляться каждый раз, когда открываете файл.
Теперь осталось умножить колонку с нашими ценами на курс. Но что делать, если в ячейке содержаться текстовые символы валюты, например руб. 61,8457 как на cbr.ru? Для этого нам понадобится функция ПСТР:
=ПСТР(Лист1!B48;6;10)Где: Лист1!B48 - ячейка с курсом; 6 - количество символов, которые нужно "отрезать" от начала строки; 10 - количество знаков, которое следует извлечь из текста.
Все что, что нужно теперь - это создать новую колонку в которой умножить цену в у.е. на курс. Теперь ваши цены автоматически обновляются в xls прайсе и могут за несколько минут попасть в интернет магазин.
Что дальше?
Чтобы научиться загружать и обновлять товары из прайс-листа в интернет магазин, пройдите эти уроки:
Порядок вывода комментариев: По умолчаниюСначала новыеСначала старые
ucozmagazines.ru
Как в Экселе посчитать курс валют
Давайте научимся считать курсы валют с помощью экселя. Благодаря этой полезной программе можно импортировать данные о курсах валют с сайтов, которые предоставили такую возможность. Для примера возьмём сайт ЦБРФ, но вы можете взять сайт любого другого финансового учреждения.
Итак, заходим на сайт (cbr.ru), в нижнем правом углу сайта ищем ссылку «Технические ресурсы». Кликаем на первую ссылку, с помощью которой мы получим данные с сервера в формате XML. Копируем первую ссылку, без указания даты в ней.
В экселе клацаем на вкладку «Данные» и указываем, что хотим получить их из интернета.
Вставляем в адресную строку нашу ссылку и два раза нажимаем «Импорт».
Ждём. Когда появится информационное окно, нажимаем «ОК».
Затем выбираем место, с которого будет начинаться импортированная таблица.
В появившейся таблице нажимаем на выпадающее меню «Name2», выбираем нужные нам валюты.
После всех проделанных шагов у нас появляется таблица со свежими курсами валют.
Благодаря этим данным мы знаем, сколько рублей стоит одна единица, но нам нужна определённая сумма, поэтому добавим в конце ещё одну колонку, в которой будет конвертация. Конечно же, чтобы она выводила нам результат, нужно в ней написать правильную формулу. А формула простая – умножим номинал на курс.
Теперь мы можем поменять номинал на любое число и узнать, сколько же это будет в рублях.
kak-v-excele.ru
Импорт курса валют из Интернета
В случае необходимости проведения расчетов на основе текущего курса валют, можно, используя Импорт внешних данных в EXCEL, автоматически загружать данные о курсе валют в файл.
Для этого:
1. Выберите в меню Данные - Импорт внешних данных - Создать веб-запрос
2. В появившемся окне в строку Адрес введите URL сайта, с которого будет браться информация (например: www.cbr.ru или www.yandex.ru ), и нажмите Enter или Пуск.
3. Когда страница загрузится, то на таблицах, которые EXCEL может импортировать, появятся черно-желтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта.
4. Когда все необходимые таблицы помечены - нажмите кнопку Импорт внизу окна.
5. В появившемся окне укажите место для загрузки данных и нажмите ОК
Спустя некоторое время, нужное для загрузки данных, содержимое импортируемых таблиц появится в ячейках на листе.
Для дополнительной настройки можно щелкнуть по любой из этих ячеек правой кнопкой мыши и выбрать в контекстном меню команду Свойства диапазона данных.
В этом диалоговом окне можно, при желании, настроить периодичность обновления и другие параметры:
Обновление экрана:
Фоновое обновление – рекомендуется для больших наборов данных. При этом вы сможете продолжать работу в EXCEL, не дожидаясь завершения обновления.
Обновлять каждые - регулярное автоматическое обновление данных, через выбранный промежуток времени.
Обновление при открытии файла - автоматическое обновление данных при открытии книги.
lk.usoft.ru
Импорт курса биткойна в Excel через Power Query
Предположим, что вы проявили деловое чутье и интуицию и закупили несколько порций какой-нибудь криптовалюты (того же биткойна, например) в прошлом. В виде умной таблицы ваш "инвестиционный портфель" выглядит, допустим, так:
Задача: оперативно оценивать текущую стоимость ваших инвестиций по текущему курсу криптовалюты. Курс будем брать в интернете с любого подходящего сайта (биржи, обменника) и усреднять для достоверности.
Один из вариантов решения - классический веб-запрос - я уже подробно рассматривал на примере импорта курса валют. Теперь же давайте попробуем, для разнообразия, использовать другой способ - надстройку Power Query, которая идеально подходит для импорта данных в Excel из внешнего мира, в том числе, и из интернета.
Выбираем сайт для импорта
С какого именно сайта будем брать данные - это, по большому счету, не принципиально. Классический веб-запрос Excel весьма требователен к структуре и внутренней конструкции импортируемой веб-страницы и, бывает, срабатывает не на каждом сайте. Power Query в этом вопросе гораздо более всеяден. Так что можно взять средний курс покупки на выбор:
- в обменниках www.bestchange.ru - большой выбор вариантов, минимальные риски, но не очень выгодный курс
- с торговой площадки www.localbitcoins.net - чуть больше риска, но сильно лучше курс
- с сайта биржи - если вы торгуете напрямую на бирже, то эта статья вам вряд ли нужна :)
Для начала откроем нужный нам сайт в обозревателе. Возьмем, для конкретности, торговую площадку localbitcoins.net. Выберем сверху вкладку Быстрая продажа и опцию Переводы через конкретный банк (или любую другую, нужную вам) и жмем кнопку Поиск:
Теперь нужно скопировать адрес появившейся страницы в буфер, т.к. он содержит все необходимые нам параметры запроса:
https://localbitcoins.net/instant-bitcoins/?action=sell&country_code=RU&amount=¤cy=RUB&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=ПоискДальше дело за Power Query.
Импортируем курс в Excel через Power Query
Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то нужная нам команда находится на одноименной вкладке - Power Query. Если у вас Excel 2016, то на вкладке Данные (Data) жмем кнопку Из интернета (From Internet). В появившееся затем окно нужно вставить скопированный адрес веб-страницы из предыдущего пункта и нажать ОК:
После анализа веб-страницы Power Query выдаст окно со списком таблиц, которые можно импортировать. Нужно найти требуемую таблицу в списке слева (их там бывает несколько), ориентируясь на предпросмотр справа, и нажать затем внизу кнопку Правка (Edit):
После этого откроется главное окно редактора запросов Power Query, в котором мы сможем отобрать только нужные строки и усреднить по ним курс покупки:
Рекомендую сразу переименовать наш запрос в панели справа, дав ему какое-нибудь вменяемое имя:
Фильтруем и зачищаем данные
В дальнейшем нам нужны будут только столбцы с описанием Payment method и курсом покупки Price / BTC - так что можно смело выделить их оба с Ctrl и, щелкнув по ним правой кнопкой мыши, выбрать команду Удалить другие столбцы (Remove other columns) - будут удалены все столбцы кроме выделенных.
Допустим, что мы хотим отобрать только тех трейдеров, которые работают через Сбербанк. Фильтр - штука знакомая, но нюанс в том, что фильтр в Power Query чувствителен к регистру, т.е. Сбербанк, СБЕРБАНК и СберБанк для него не одно и то же. Поэтому, прежде чем отбирать нужные строки, давайте приведем регистр всех описаний к одному виду. Для этого нужно выделить столбец Payment method и на вкладке Преобразование выбрать команду Форматировать - нижний регистр (Transform - Format - Lower case):
Теперь отфильтруем по столбцу Payment method с использованием опции Текстовые фильтры - Содержит (Text filters - Contains):
В окне фильтра сразу переключаемся сверху в режим Дополнительно (Advanced) и вводим три правила для отбора:
Как легко догадаться, этим мы отбираем все строки, где присутствует слово "сбер" на русском или английском, плюс тех, кто работает через любой банк. Не забудьте установить слева логическую связку Или (OR) вместо И (And) - иначе правило не сработает корректно. После нажатия на ОК на экране должны остаться только нужные нам варианты:
Теперь удаляем столбец Payment method правой кнопкой мыши по заголовку столбца - Удалить столбец (Remove column) и работаем дальше уже с оставшимся единственным столбцом курсов:
Проблема с ним в том, что там, кроме числа, лежит еще и обозначение валюты. Это можно легко вычистить простой заменой, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Замена значений (Replace values):
Получившиеся после удаления RUB числа, на самом деле, тоже еще не числа, т.к. в них используются нестандартные разделители. Это можно вылечить, нажав в шапке таблицы кнопку формата и выбрав затем опцию Используя локаль (Use locals):
Наиболее подходящей будет локаль Английский (США) и тип данных - Десятичное число:
После нажатия на ОК мы получим полноценные числовые значения курсов покупки:
Останется посчитать по ним среднее на вкладке Трансформация - Статистика - Среднее (Transform - Statistics - Average) и выгрузить получившееся число на лист командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load To...):
Теперь можно добавить ссылку на загруженный курс в формулу к нашей таблице портфеля и посчитать разницу в стоимости по всем нашим инвестициям на текущий момент:
Теперь можно периодически открывать этот файл, щелкать правой кнопкой мыши по запросу и, выбрав команду Обновить (Refresh), наблюдать изменения, которые автоматически будут подгружаться в нашу таблицу.
P.S.
Как легко сообразить, совершенно аналогичным образом можно импортировать курс не только биткойна, но и любой другой валюты, акции или ценной бумаги. Главное - найти подходящий сайт и построить запрос, а дальше все сделает умный Power Query.
Ссылки по теме
www.planetaexcel.ru
