Імпорт курсів валют з Інтернету

Імпорт курсу певної валюти з Інтернету з автоматичним оновленням є дуже поширеним завданням для багатьох користувачів Microsoft Excel. Уявіть, що у вас є прайс-лист, який потрібно щоранку перераховувати за курсом валют. Або бюджет проекту. Або вартість договору, яка повинна бути розрахована за курсом долара на дату укладення договору.

У таких ситуаціях вирішувати проблему можна різними способами – все залежить від того, яку версію Excel у вас встановлено і які доповнення над нею.

Спосіб 1: Простий веб-запит поточного курсу валют

Цей спосіб підійде тим, у кого на комп'ютері ще стоять старі версії Microsoft Office 2003-2007. Він не використовує сторонніх надбудов або макросів і працює лише з вбудованими функціями.

Натисніть кнопку З Інтернету (Інтернет) таб дані (Дата). У вікні, що з'явилося, в рядку адреса (адреса) введіть URL сайту, з якого буде взята інформація (наприклад, http://www.finmarket.ru/currency/rates/) і натисніть клавішу Що натомість? Створіть віртуальну версію себе у .

Коли сторінка завантажиться, на таблицях, які Excel може імпортувати, з’являться чорні та жовті стрілки. Натискання на таку стрілку позначає таблицю для імпорту.

Коли всі необхідні таблиці позначені, натисніть кнопку Імпортувати (імпорт) у нижній частині вікна. Через деякий час, необхідний для завантаження даних, вміст позначених таблиць з'явиться в клітинках на аркуші:

Для додаткового налаштування ви можете клацнути правою кнопкою миші будь-яку з цих комірок і вибрати команду з контекстного меню. Властивості діапазону (Властивості діапазону даних).У цьому діалоговому вікні за бажанням можна налаштувати частоту оновлення та інші параметри:

Котирування акцій, оскільки вони змінюються кожні кілька хвилин, ви можете оновлювати частіше (прапорець Оновлювати кожні N хв.), але курси валют, в більшості випадків, достатньо оновлювати раз на день (прапорець Оновлення відкритого файлу).

Зауважте, що весь імпортований діапазон даних обробляється Excel як єдине ціле та отримує власне ім’я, яке можна побачити в диспетчері імен на вкладці формула (Формули — Менеджер імен).

Спосіб 2. Параметричний веб-запит для отримання курсу обміну для заданого діапазону дат

Цей спосіб є трохи модернізованим першим варіантом і дає користувачеві можливість отримати курс потрібної валюти не тільки на поточний день, але і на будь-яку іншу дату або інтервал дат, що цікавить. Для цього наш веб-запит необхідно перетворити в параметричний, тобто додати в нього два уточнюючих параметра (код потрібної нам валюти і поточну дату). Для цього робимо наступне:

1. Створюємо веб-запит (див. спосіб 1) на сторінку сайту ЦБ нашої країни з архівом курсів: http://cbr.ru/currency_base/dynamics.aspx

2. У формі зліва виберіть потрібну валюту та встановіть дату початку та закінчення:

3. Натисніть кнопку Щоб отримати дані і через пару секунд ми бачимо таблицю з потрібними нам значеннями курсу для заданого інтервалу дат. Прокрутіть отриману таблицю до кінця та позначте її для імпорту, клацнувши чорно-жовту стрілку в нижньому лівому куті веб-сторінки (тільки не питайте, чому ця стрілка саме там, а не поруч із таблицею – це питання до дизайнерів сайту).

Тепер шукаємо кнопку з дискетою в правому верхньому куті вікна Зберегти запит (Зберегти запит) і зберегти файл з параметрами нашого запиту в будь-яку підходящу папку під будь-яким зручним ім'ям – наприклад, в Мої документи під назвою кбр. iqy.  Після цього вікно веб-запиту та весь Excel можна поки закрити.

4. Відкрийте папку, де ви зберегли запит, і знайдіть файл запиту кбр. iqy, потім клацніть на ньому правою кнопкою миші – Відкрити за допомогою – Блокнот (або виберіть його зі списку – зазвичай це файл Блокнот з папки В: Windows). Відкривши файл запиту в Блокноті, ви повинні побачити щось на зразок цього:

Найціннішим тут є рядок з адресою і параметрами запиту в ньому, які ми підставляємо – код потрібної нам валюти (виділений червоним) і кінцева дата, яку ми замінимо на сьогоднішню (виділена в синій). Ретельно відредагуйте рядок, щоб отримати наступне:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Валютний код"]&date_req1=01.01.2000&r1=1&date_req2=["Дата"]&rt=1&mode=1

Залиште все як є, збережіть і закрийте файл.

5. Створюємо нову книгу в Excel, відкриваємо аркуш, куди ми хочемо імпортувати архів курсів ЦБ. У будь-якій відповідній комірці введіть формулу, яка дасть нам поточну дату у текстовому форматі для підстановки запиту:

=ТЕКСТ(СЬОГОДНІ();”ДД.ММ.РРРР”)

або в англійській версії

=ТЕКСТ(СЬОГОДНІ(),»дд.мм.рррр»)

Десь поруч вводимо код потрібної нам валюти з таблиці:

Потрібний код також можна підглянути в рядку запиту прямо на сайті ЦБ.

6. Завантажуємо дані на лист, взявши за основу створені комірки і файл cbr.iqy, тобто переходимо на вкладку Дані – Підключення – Знайти інші (Дані — існуючі підключення). У вікні вибору джерела даних, що відкриється, знайдіть і відкрийте файл кбр. iqy. Перед імпортом Excel уточнює з нами три речі.

По-перше, куди імпортувати таблицю даних:

По-друге, звідки взяти код валюти (можна поставити галочку Використовуйте це значення за умовчанням (Використовуйте це значення/посилання для майбутніх оновлень), щоб потім кожного разу ця комірка не вказувалася під час оновлень і прапорця Автоматично оновлювати, коли змінюється значення клітинки (Оновлювати автоматично, коли змінюється значення клітинки):

По-третє, з якої комірки брати кінцеву дату (тут також можна поставити галочки в обох галочках, щоб завтра не встановлювати ці параметри вручну при оновленні):

Натисніть OK, зачекайте пару секунд і отримаєте повний архів курсу потрібної валюти на аркуші:

Як і в першому способі, клацнувши правою кнопкою миші на імпортованих даних і вибравши команду Властивості діапазону (Властивості діапазону даних), ви можете налаштувати частоту оновлення При відкритті файлу (Оновити після відкриття файлу). Тоді, якщо у вас є доступ до Інтернету, дані будуть автоматично оновлюватися кожен день, т. Е. Таблиця буде автоматично оновлюватися новими даними.

Витягти курс на потрібну дату з нашої таблиці найпростіше за допомогою функції VPR (ВПР) – якщо ви з ним не знайомі, то настійно раджу це зробити. За такою формулою, наприклад, можна вибрати з нашої таблиці курс долара на 10 січня 2000 року:

або англійською =VLOOKUP(E5,cbr,3,1)

де

• E5 – комірка, що містить задану дату
  
• CBR – назва діапазону даних (автоматично генерується під час імпорту та зазвичай збігається з назвою файлу запиту)
• 3 – порядковий номер стовпця в нашій таблиці, звідки ми беремо дані
• 1 – аргумент, який містить приблизний пошук функції VLOOKUP, щоб ви могли знайти курси для тих проміжних дат, яких насправді немає в стовпці A (буде взято найближчу попередню дату та її курс). Детальніше про приблизний пошук за допомогою функції VLOOKUP ви можете прочитати тут.

• Макрос для отримання курсу долара на певну дату в поточній клітинці
• Додаткова функція PLEX для отримання курсу долара, євро, гривні, фунта стерлінгів тощо на будь-яку задану дату
• Вставте будь-який курс валюти на будь-яку дату в надбудові PLEX