Импортирайте валутни курсове от интернет

Импортирането на курса на дадена валута от интернет с автоматично актуализиране е много често срещана задача за много потребители на 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. Щракнете върху бутона За да получите данни и след няколко секунди виждаме таблица със стойностите на курса, от които се нуждаем за даден интервал от дати. Превъртете получената таблица докрай надолу и я маркирайте за импортиране, като щракнете върху черната и жълта стрелка в долния ляв ъгъл на уеб страницата (само не питайте защо тази стрелка е там, а не до таблицата – това е въпрос към дизайнерите на сайта).

Сега търсим бутон с флопи диск в горния десен ъгъл на прозореца Запазване на заявката (Запазване на заявката) и запазете файла с параметрите на нашата заявка във всяка подходяща папка под произволно удобно име - например в Моите документи под името cbr. iqy.  След това прозорецът за уеб заявки и целият Excel могат да бъдат затворени засега.

4. Отворете папката, в която сте запазили заявката, и потърсете файла със заявката cbr. iqy, след което щракнете с десния бутон върху него – Отворете с – Notepad (или го изберете от списъка – обикновено това е файл Notepad.exe от папката В: Windows). След като отворите файла със заявката в Notepad, трябва да видите нещо подобно:

Най-ценното тук е редът с адреса и параметрите на заявката в него, които ще заменим – кода на необходимата ни валута (маркиран в червено) и крайната дата, която ще заменим с днешната (маркирана в син). Внимателно редактирайте реда, за да получите следното:

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 като основа, т.е. Данни – Връзки – Намерете други (Данни — съществуващи връзки). В прозореца за избор на източник на данни, който се отваря, намерете и отворете файла cbr. iqy. Преди импортиране Excel ще изясни три неща с нас.

Първо, къде да импортирате таблицата с данни:

Второ, откъде да получите кода на валутата (можете да поставите отметка в квадратчето Използвайте тази стойност по подразбиране (Използвайте тази стойност/препратка за бъдещи опреснявания), така че по-късно всеки път тази клетка да не се посочва по време на актуализации и квадратчето за отметка Автоматично актуализиране при промяна на стойността на клетката (Автоматично опресняване при промяна на стойността на клетката):

Трето, от коя клетка да вземете крайната дата (можете също да поставите отметка в двете квадратчета тук, така че утре да не се налага да задавате тези параметри ръчно при актуализиране):

Кликнете OK, изчакайте няколко секунди и получете пълен архив на обменния курс на желаната валута на листа:

Както при първия метод, като щракнете с десния бутон върху импортираните данни и изберете командата Свойства на диапазона (Свойства на диапазон от данни), можете да регулирате честотата на опресняване При отваряне на файл (Опресняване при отворен файл). След това, ако имате достъп до интернет, данните ще се актуализират автоматично всеки ден, т.е. таблицата автоматично ще се актуализира с нови данни.

Най-лесно е да извлечете курса за желаната дата от нашата таблица с помощта на функцията ВПР (VLOOKUP) – ако не сте запознати с него, тогава силно ви съветвам да го направите. С такава формула, например, можете да изберете обменния курс на долара за 10 януари 2000 г. от нашата таблица:

или на английски =VLOOKUP(E5,cbr,3,1)

където

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

• Макрос за получаване на курса на долара за дадена дата в текущата клетка
• Функция за добавяне на PLEX за получаване на обменния курс на долара, еврото, гривната, лирата стерлинги и т.н. за всяка дадена дата
• Въведете произволен валутен курс на всяка дата в добавката PLEX