Zaktualizowany kurs wymiany w Excelu

Wielokrotnie analizowałem sposoby importowania danych do Excela z Internetu z późniejszą automatyczną aktualizacją. W szczególności:

• W starszych wersjach programu Excel 2007-2013 można to zrobić za pomocą bezpośredniego żądania internetowego.
• Od 2010 r. można to zrobić bardzo wygodnie za pomocą dodatku Power Query.
  

Do tych metod w najnowszych wersjach Microsoft Excel można teraz dodać kolejną – importowanie danych z Internetu w formacie XML za pomocą wbudowanych funkcji.

XML (eXtensible Markup Language = Extensible Markup Language) to uniwersalny język przeznaczony do opisywania dowolnego rodzaju danych. W rzeczywistości jest to zwykły tekst, ale ze specjalnymi znacznikami dodanymi do oznaczenia struktury danych. Wiele witryn udostępnia bezpłatne strumienie swoich danych w formacie XML, które każdy może pobrać. Na stronie internetowej Banku Centralnego Naszego Kraju (www.cbr.ru), w szczególności za pomocą podobnej technologii, podawane są dane o kursach wymiany różnych walut. Ze strony Moscow Exchange (www.moex.com) możesz w ten sam sposób pobrać notowania akcji, obligacji i wiele innych przydatnych informacji.

Od wersji 2013 Excel ma dwie funkcje do bezpośredniego ładowania danych XML z Internetu do komórek arkusza roboczego: SERWIS INTERNETOWY (SERWIS INTERNETOWY) и FILTR.XML (FILTRXML). Pracują w parach – najpierw funkcja SERWIS INTERNETOWY wykonuje żądanie do żądanej witryny i zwraca odpowiedź w formacie XML, a następnie korzysta z funkcji FILTR.XML „parsujemy” tę odpowiedź na komponenty, wydobywając z niej potrzebne nam dane.

Przyjrzyjmy się działaniu tych funkcji na klasycznym przykładzie – importując kurs dowolnej potrzebnej nam waluty na dany przedział dat ze strony Banku Centralnego Naszego Kraju. Jako blank użyjemy następującej konstrukcji:

Tutaj:

• Żółte komórki zawierają daty rozpoczęcia i zakończenia interesującego nas okresu.
• Niebieski ma rozwijaną listę walut za pomocą polecenia Dane – Walidacja – Lista (Dane — Walidacja — Lista).
• W zielonych komórkach użyjemy naszych funkcji do utworzenia ciągu zapytania i uzyskania odpowiedzi serwera.
• Tabela po prawej to odniesienie do kodów walut (będziemy jej potrzebować nieco później).
  

Chodźmy!

Krok 1. Tworzenie ciągu zapytania

Aby uzyskać wymagane informacje ze strony, musisz zadać je poprawnie. Wchodzimy na www.cbr.ru i otwieramy link w stopce strony głównej” Zasoby techniczne”- Pobieranie danych za pomocą XML (http://cbr.ru/development/SXML/). Przewijamy się trochę niżej i w drugim przykładzie (Przykład 2) będzie to, czego potrzebujemy – uzyskanie kursów walut dla danego przedziału dat:

Jak widać na przykładzie, ciąg zapytania musi zawierać daty rozpoczęcia (data_wymaganie1) i końcówki (data_wymaganie2) okresu, który nas interesuje oraz kod waluty (VAL_NM_RQ), stawkę jaką chcemy uzyskać. Główne kody walut znajdziesz w poniższej tabeli:

Kompletny przewodnik po kodach walut jest również dostępny na stronie Banku Centralnego – patrz http://cbr.ru/scripts/XML_val.asp?d=0

Teraz utworzymy ciąg zapytania w komórce na arkuszu z:

• operator konkatenacji tekstu (&), aby złożyć go razem;
  
• Korzyści VPR (WYSZUKAJ.PIONOWO)znaleźć kod potrzebnej waluty w katalogu;
  
• Korzyści TEKST (TEKST), który konwertuje datę zgodnie z podanym wzorcem dzień-miesiąc-rok za pomocą ukośnika.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Krok 2. Wykonaj żądanie

Teraz używamy funkcji SERWIS INTERNETOWY (SERWIS INTERNETOWY) z wygenerowanym ciągiem zapytania jako jedynym argumentem. Odpowiedzią będzie długa linia kodu XML (lepiej włączyć zawijanie słów i zwiększyć rozmiar komórki, jeśli chcesz zobaczyć ją w całości):

Krok 3. Analiza odpowiedzi

Aby ułatwić zrozumienie struktury danych odpowiedzi, lepiej skorzystać z jednego z internetowych parserów XML (np. http://xpather.com/ lub https://jsonformatter.org/xml-parser), który może wizualnie formatować kod XML, dodając do niego wcięcia i podkreślając składnię kolorem. Wtedy wszystko stanie się znacznie jaśniejsze:

Teraz wyraźnie widać, że wartości kursów są obramowane naszymi tagami ..., a daty są atrybutami Data w tagach .

Aby je wyodrębnić, zaznacz kolumnę dziesięciu (lub więcej – jeśli robisz to z marginesem) pustych komórek w arkuszu (ponieważ ustawiono 10-dniowy przedział dat) i wpisz funkcję w pasku formuły FILTR.XML (FILTRXML):

Tutaj pierwszym argumentem jest łącze do komórki z odpowiedzią serwera (B8), a drugim ciąg zapytania w XPath, specjalnym języku, którego można użyć do uzyskania dostępu do niezbędnych fragmentów kodu XML i ich wyodrębnienia. Możesz przeczytać więcej o języku XPath, na przykład, tutaj.

Ważne, aby po wpisaniu formuły nie naciskać Wchodzęi skrót klawiaturowy Ctrl+Shift+Wchodzę, czyli wprowadź go jako formułę tablicową (nawiasy klamrowe wokół niego zostaną dodane automatycznie). Jeśli masz najnowszą wersję Office 365 z obsługą tablic dynamicznych w programie Excel, to prosty Wchodzę, i nie musisz wcześniej wybierać pustych komórek — sama funkcja zajmie tyle komórek, ile potrzebuje.

Aby wyodrębnić daty, zrobimy to samo – zaznaczymy kilka pustych komórek w sąsiedniej kolumnie i użyjemy tej samej funkcji, ale z innym zapytaniem XPath, aby pobrać wszystkie wartości atrybutów Date z tagów Record:

=FILTR.XML(B8;”//Rekord/@Data”)

Teraz w przyszłości, przy zmianie dat w pierwotnych komórkach B2 i B3 lub wybraniu innej waluty z listy rozwijanej komórki B3, nasze zapytanie zostanie automatycznie zaktualizowane, odwołując się do serwera Banku Centralnego po nowe dane. Aby ręcznie wymusić aktualizację, możesz dodatkowo użyć skrótu klawiaturowego Ctrl+inny+F9.

• Importuj kurs bitcoin do Excela za pomocą Power Query
• Importuj kursy walut z Internetu w starszych wersjach programu Excel