Tỷ giá hối đoái cập nhật trong Excel

Tôi đã nhiều lần phân tích các cách nhập dữ liệu vào Excel từ Internet với quá trình cập nhật tự động sau đó. Đặc biệt:

• Trong các phiên bản Excel 2007-2013 cũ hơn, việc này có thể được thực hiện bằng yêu cầu trực tiếp trên web.
• Bắt đầu từ năm 2010, việc này có thể được thực hiện rất thuận tiện với phần bổ trợ Power Query.
  

Đối với các phương pháp này trong các phiên bản mới nhất của Microsoft Excel, giờ đây bạn có thể thêm một phương pháp khác – nhập dữ liệu từ Internet ở định dạng XML bằng các hàm dựng sẵn.

XML (Ngôn ngữ đánh dấu mở rộng = Ngôn ngữ đánh dấu mở rộng) là ngôn ngữ phổ quát được thiết kế để mô tả bất kỳ loại dữ liệu nào. Trên thực tế, nó là văn bản thuần túy nhưng có thêm các thẻ đặc biệt để đánh dấu cấu trúc dữ liệu. Nhiều trang web cung cấp các luồng dữ liệu miễn phí ở định dạng XML để mọi người tải xuống. Đặc biệt, trên trang web của Ngân hàng Trung ương Nước ta (www.cbr.ru), với sự trợ giúp của công nghệ tương tự, dữ liệu về tỷ giá hối đoái của các loại tiền tệ khác nhau sẽ được cung cấp. Từ trang web Moscow Exchange (www.moex.com), bạn có thể tải xuống báo giá cổ phiếu, trái phiếu và nhiều thông tin hữu ích khác theo cách tương tự.

Kể từ phiên bản 2013, Excel có hai chức năng tải trực tiếp dữ liệu XML từ Internet vào các ô trong bảng tính: DỊCH VỤ WEB (DỊCH VỤ TRANG WEB) и LỌC.XML (LỌCXML). Họ làm việc theo cặp – đầu tiên là chức năng DỊCH VỤ WEB thực hiện một yêu cầu đến trang mong muốn và trả về phản hồi của nó ở định dạng XML, sau đó sử dụng hàm LỌC.XML chúng tôi “phân tích” câu trả lời này thành các thành phần, trích xuất dữ liệu chúng tôi cần từ nó.

Hãy xem xét hoạt động của các chức năng này bằng một ví dụ cổ điển – nhập tỷ giá hối đoái của bất kỳ loại tiền tệ nào chúng ta cần trong một khoảng thời gian nhất định từ trang web của Ngân hàng Trung ương Nước ta. Chúng ta sẽ sử dụng cấu trúc sau đây làm chỗ trống:

Đây:

• Các ô màu vàng chứa ngày bắt đầu và ngày kết thúc của khoảng thời gian mà chúng tôi quan tâm.
• Cái màu xanh có danh sách thả xuống các loại tiền tệ bằng lệnh Dữ liệu – Xác thực – Danh sách (Dữ liệu - Xác thực - Danh sách).
• Trong các ô màu xanh lá cây, chúng ta sẽ sử dụng các hàm của mình để tạo chuỗi truy vấn và nhận phản hồi của máy chủ.
• Bảng bên phải là tham chiếu đến mã tiền tệ (chúng ta sẽ cần nó sau).
  

Đi nào!

Bước 1. Hình thành chuỗi truy vấn

Để có được thông tin cần thiết từ trang web, bạn cần phải hỏi chính xác. Chúng tôi truy cập www.cbr.ru và mở liên kết ở chân trang của trang chính' Tài nguyên kỹ thuật'- Lấy dữ liệu bằng XML (http://cbr.ru/development/SXML/). Chúng ta cuộn xuống thấp hơn một chút và trong ví dụ thứ hai (Ví dụ 2) sẽ có thứ chúng ta cần – nhận tỷ giá hối đoái trong một khoảng thời gian nhất định:

Như bạn có thể thấy trong ví dụ, chuỗi truy vấn phải chứa ngày bắt đầu (ngày_req1) và kết thúc (ngày_req2) của khoảng thời gian mà chúng tôi quan tâm và mã tiền tệ (VAL_NM_RQ), tỷ lệ mà chúng tôi muốn nhận được. Bạn có thể tìm thấy các mã tiền tệ chính trong bảng dưới đây:

Hướng dẫn đầy đủ về mã tiền tệ cũng có trên trang web của Ngân hàng Trung ương – xem http://cbr.ru/scripts/XML_val.asp?d=0

Bây giờ chúng ta sẽ tạo một chuỗi truy vấn trong một ô trên một trang tính với:

• toán tử nối văn bản (&) để ghép nó lại với nhau;
  
• Tính năng VPR (Vlookup)để tìm mã loại tiền chúng ta cần trong thư mục;
  
• Tính năng TEXT (BẢN VĂN), chuyển đổi ngày theo mẫu ngày-tháng-năm nhất định thông qua dấu gạch chéo.

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

Bước 2. Thực hiện yêu cầu

Bây giờ chúng ta sử dụng hàm DỊCH VỤ WEB (DỊCH VỤ TRANG WEB) với chuỗi truy vấn được tạo làm đối số duy nhất. Câu trả lời sẽ là một dòng mã XML dài (tốt hơn hết bạn nên bật tính năng ngắt dòng và tăng kích thước ô nếu muốn xem toàn bộ):

Bước 3. Phân tích câu trả lời

Để hiểu cấu trúc của dữ liệu phản hồi dễ dàng hơn, tốt hơn nên sử dụng một trong các trình phân tích cú pháp XML trực tuyến (ví dụ: http://xpather.com/ hoặc https://jsonformatter.org/xml-parser), có thể định dạng trực quan mã XML, thêm thụt lề vào mã và làm nổi bật cú pháp bằng màu sắc. Sau đó mọi thứ sẽ trở nên rõ ràng hơn nhiều:

Bây giờ bạn có thể thấy rõ rằng các giá trị khóa học được đóng khung bởi các thẻ của chúng tôi ...và ngày tháng là thuộc tính Ngày trong thẻ .

Để trích xuất chúng, hãy chọn một cột gồm mười (hoặc nhiều hơn – nếu được thực hiện với lề) trên trang tính (vì khoảng thời gian 10 ngày đã được đặt) và nhập hàm vào thanh công thức LỌC.XML (LỌCXML):

Ở đây, đối số đầu tiên là một liên kết đến một ô có phản hồi của máy chủ (B8) và đối số thứ hai là chuỗi truy vấn trong XPath, một ngôn ngữ đặc biệt có thể được sử dụng để truy cập các đoạn mã XML cần thiết và trích xuất chúng. Ví dụ, bạn có thể đọc thêm về ngôn ngữ XPath tại đây.

Điều quan trọng là sau khi nhập công thức không được nhấn đăng ký hạng mục thi, và phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thi, tức là nhập nó dưới dạng công thức mảng (dấu ngoặc nhọn xung quanh nó sẽ được thêm tự động). Nếu bạn có phiên bản Office 365 mới nhất có hỗ trợ mảng động trong Excel thì một cách đơn giản đăng ký hạng mục thivà bạn không cần phải chọn trước các ô trống – bản thân hàm sẽ lấy bao nhiêu ô tùy ý.

Để trích xuất ngày tháng, chúng tôi sẽ thực hiện tương tự – chúng tôi sẽ chọn một số ô trống trong cột liền kề và sử dụng cùng một hàm, nhưng với truy vấn XPath khác, để lấy tất cả các giá trị của thuộc tính Ngày từ thẻ Bản ghi:

=FILTER.XML(B8;”//Record/@Date”)

Bây giờ, trong tương lai, khi thay đổi ngày trong các ô B2 và B3 ban đầu hoặc chọn một loại tiền tệ khác trong danh sách thả xuống của ô B3, truy vấn của chúng tôi sẽ được cập nhật tự động, tham chiếu đến máy chủ Ngân hàng Trung ương để có dữ liệu mới. Để buộc cập nhật thủ công, bạn có thể sử dụng thêm phím tắt Ctrl+Khác+F9.

• Nhập tỷ giá bitcoin vào Excel qua Power Query
• Nhập tỷ giá hối đoái từ Internet trong các phiên bản Excel cũ hơn