Importuj kursy walut z Internetu

Importowanie kursu danej waluty z Internetu z automatyczną aktualizacją jest bardzo częstym zadaniem dla wielu użytkowników Microsoft Excel. Wyobraź sobie, że masz cennik, który każdego ranka trzeba przeliczać według kursu wymiany. Albo budżet projektu. Lub koszt umowy, który należy obliczyć po kursie dolara z dnia zawarcia umowy.

W takich sytuacjach problem można rozwiązać na różne sposoby – wszystko zależy od zainstalowanej wersji Excela i dodatków.

Metoda 1: Proste zapytanie internetowe o aktualny kurs wymiany

Ta metoda jest odpowiednia dla tych, którzy nadal mają na swoim komputerze stare wersje pakietu Microsoft Office 2003-2007. Nie używa żadnych dodatków ani makr innych firm i działa tylko na wbudowanych funkcjach.

Naciśnij przycisk Z Internetu (Sieć) Zakładka Dane (Data). W oknie, które się pojawi, w linii Adres (Adres) wprowadź adres URL strony, z której będą pobierane informacje (na przykład http://www.finmarket.ru/currency/rates/) i naciśnij klawisz Wchodzę.

Po załadowaniu strony na tabelach, które program Excel może zaimportować, pojawią się czarne i żółte strzałki. Kliknięcie na taką strzałkę oznacza tabelę do importu.

Gdy wszystkie niezbędne tabele zostaną zaznaczone, kliknij przycisk import (Import) na dole okna. Po pewnym czasie wymaganym do załadowania danych, zawartość zaznaczonych tabel pojawi się w komórkach na arkuszu:

Aby uzyskać dodatkowe dostosowanie, możesz kliknąć prawym przyciskiem myszy dowolną z tych komórek i wybrać polecenie z menu kontekstowego. Właściwości zakresu (Właściwości zakresu danych).W tym oknie dialogowym, w razie potrzeby, można skonfigurować częstotliwość aktualizacji i inne parametry:

Notowania giełdowe, ponieważ zmieniają się co kilka minut, możesz aktualizować częściej (pole wyboru Odśwież co N min.), ale kursy walut w większości przypadków wystarczy aktualizować raz dziennie (pole wyboru Aktualizacja przy otwartym pliku).

Zwróć uwagę, że cały importowany zakres danych jest traktowany przez Excel jako pojedyncza jednostka i ma swoją własną nazwę, którą można zobaczyć w Menedżerze nazw na zakładce formuła (Formuły — Menedżer nazw).

Metoda 2: Parametryczne zapytanie internetowe, aby uzyskać kurs wymiany dla danego zakresu dat

Ta metoda jest nieco unowocześnioną pierwszą opcją i daje użytkownikowi możliwość otrzymania kursu wybranej waluty nie tylko na bieżący dzień, ale także na dowolną inną datę lub interwał dat. W tym celu nasze żądanie WWW musi zostać zamienione na parametryczne, czyli dodać do niego dwa parametry wyjaśniające (kod potrzebnej waluty i aktualną datę). Aby to zrobić, wykonujemy następujące czynności:

1. Tworzymy żądanie internetowe (patrz metoda 1) do strony internetowej Banku Centralnego Naszego Kraju z archiwum kursów: http://cbr.ru/currency_base/dynamics.aspx

2. W formularzu po lewej stronie wybierz żądaną walutę i ustaw datę rozpoczęcia i zakończenia:

3. Kliknij przycisk Aby uzyskać dane a po kilku sekundach widzimy tabelkę z wartościami kursów, których potrzebujemy dla danego przedziału dat. Przewiń wynikową tabelę do samego dołu i zaznacz ją do zaimportowania, klikając czarno-żółtą strzałkę w lewym dolnym rogu strony (tylko nie pytaj, dlaczego ta strzałka jest tam, a nie obok tabeli – to jest pytanie do projektantów strony).

Teraz szukamy przycisku z dyskietką w prawym górnym rogu okna Zapisz żądanie (Zapisz zapytanie) i zapisz plik z parametrami naszego żądania w dowolnym odpowiednim folderze pod dowolną wygodną nazwą – na przykład w Moje dokumenty pod imieniem cbr. Iqy.  Następnie można na razie zamknąć okno Web Query i cały program Excel.

4. Otwórz folder, w którym zapisałeś wniosek i poszukaj pliku wniosku cbr. racja, a następnie kliknij prawym przyciskiem myszy – Otwórz za pomocą – Notatnik (lub wybierz go z listy – zazwyczaj jest to plik Notepad.exe z folderu C: Okna). Po otwarciu pliku żądania w Notatniku powinieneś zobaczyć coś takiego:

Najcenniejszą rzeczą jest tutaj linia z adresem i parametrami zapytania w nim, którą zastąpimy – kodem potrzebnej nam waluty (podświetlony na czerwono) oraz datą końcową, którą zastąpimy dzisiejszą (podświetloną w niebieski). Ostrożnie edytuj linię, aby uzyskać następujące informacje:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Kod waluty"]&date_req1=01.01.2000&r1=1&date_req2=["Data"]&rt=1&tryb=1

Pozostaw wszystko bez zmian, zapisz i zamknij plik.

5. Utwórz nową książkę w Excelu, otwórz arkusz, do którego chcemy zaimportować archiwum kursów Banku Centralnego. W dowolnej odpowiedniej komórce wpisz formułę, która poda nam aktualną datę w formacie tekstowym dla podstawiania zapytania:

=TEKST(DZIŚ();”DD.MM.RRRR”)

lub w wersji angielskiej

=TEKST(DZIŚ();»dd.mm.rrrr»)

Gdzieś w pobliżu wpisujemy z tabeli kod potrzebnej nam waluty:

Wymagany kod można również podejrzeć w ciągu zapytania bezpośrednio na stronie Banku Centralnego.

6. Wczytujemy dane na arkusz, wykorzystując jako podstawę utworzone komórki i plik cbr.iqy, czyli przechodzimy do zakładki Dane – Połączenia – Znajdź inne (Dane — istniejące połączenia). W otwartym oknie wyboru źródła danych znajdź i otwórz plik cbr. racja. Przed importem Excel wyjaśni nam trzy rzeczy.

Po pierwsze, gdzie zaimportować tabelę danych:

Po drugie, skąd wziąć kod waluty (możesz zaznaczyć pole Użyj tej wartości domyślnej (Użyj tej wartości/odniesienia do przyszłych odświeżeń), aby później za każdym razem ta komórka nie była określana podczas aktualizacji i pole wyboru Automatycznie aktualizuj, gdy zmienia się wartość komórki (Odświeżanie automatyczne po zmianie wartości komórki):

Po trzecie, z której komórki należy pobrać datę końcową (możesz też zaznaczyć tutaj oba pola, aby jutro nie trzeba było ręcznie ustawiać tych parametrów podczas aktualizacji):

Kliknij OK, odczekaj kilka sekund i uzyskaj pełne archiwum kursu wybranej waluty na arkuszu:

Podobnie jak w pierwszej metodzie, klikając prawym przyciskiem myszy importowane dane i wybierając polecenie Właściwości zakresu (Właściwości zakresu danych), możesz dostosować częstotliwość odświeżania Podczas otwierania pliku (Odśwież po otwarciu pliku). Następnie, jeśli masz dostęp do Internetu, dane będą automatycznie aktualizowane codziennie, tzn. Tabela zostanie automatycznie zaktualizowana o nowe dane.

Najłatwiej jest wydobyć stawkę dla żądanej daty z naszej tabeli za pomocą funkcji VPR (WYSZUKAJ.PIONOWO) – jeśli nie znasz tego, to zdecydowanie radzę ci to zrobić. Dzięki takiej formule możesz na przykład wybrać kurs dolara na 10 stycznia 2000 z naszej tabeli:

lub w języku angielskim =WYSZUKAJ.PIONOWO(E5,cbr,3,1)

gdzie

• E5 – komórka zawierająca podaną datę
  
• cbr – nazwa zakresu danych (generowana automatycznie podczas importu i zazwyczaj taka sama jak nazwa pliku zapytania)
• 3 – numer seryjny kolumny w naszej tabeli, z której otrzymujemy dane
• 1 – argument zawierający przybliżone wyszukiwanie funkcji WYSZUKAJ.PIONOWO tak, aby można było znaleźć kursy dla tych dat pośrednich, których faktycznie nie ma w kolumnie A (zostanie podjęta najbliższa poprzednia data i jej przebieg). Możesz przeczytać więcej o wyszukiwaniu przybliżonym za pomocą funkcji WYSZUKAJ.PIONOWO tutaj.

• Makro, aby uzyskać kurs dolara dla danej daty w bieżącej komórce
• Dodatkowa funkcja PLEX, aby uzyskać kurs wymiany dolara, euro, hrywny, funta szterlinga itp. dla dowolnej daty
• Wstaw dowolny kurs waluty w dowolnym dniu w dodatku PLEX