Nhập tỷ giá hối đoái từ Internet

Nhập tỷ giá của một loại tiền nhất định từ Internet với cập nhật tự động là một nhiệm vụ rất phổ biến đối với nhiều người dùng Microsoft Excel. Hãy tưởng tượng rằng bạn có một bảng giá phải được tính toán lại mỗi sáng theo tỷ giá hối đoái. Hoặc ngân sách dự án. Hoặc chi phí của hợp đồng, phải được tính theo tỷ giá hối đoái đô la tại ngày ký kết hợp đồng.

Trong những tình huống như vậy, bạn có thể giải quyết vấn đề theo nhiều cách khác nhau - tất cả phụ thuộc vào phiên bản Excel bạn đã cài đặt và những tiện ích bổ sung nào có trên nó.

Phương pháp 1: Một yêu cầu web đơn giản cho tỷ giá hối đoái hiện tại

Phương pháp này phù hợp với những người vẫn còn phiên bản cũ của Microsoft Office 2003-2007 trên máy tính của họ. Nó không sử dụng bất kỳ tiện ích bổ sung hoặc macro nào của bên thứ ba và chỉ hoạt động trên các chức năng được tích hợp sẵn.

Ấn nút Từ trên mạng (Web) chuyển hướng Ngày (Ngày). Trong cửa sổ xuất hiện, trong dòng Địa Chỉ (Địa chỉ nhà) nhập URL của trang web mà từ đó thông tin sẽ được lấy (ví dụ: http://www.finmarket.ru/currency/rates/) và nhấn phím đăng ký hạng mục thi.

Khi trang tải, các mũi tên màu đen và vàng sẽ xuất hiện trên các bảng mà Excel có thể nhập. Nhấp vào một mũi tên như vậy sẽ đánh dấu bảng cần nhập.

Khi tất cả các bảng cần thiết được đánh dấu, hãy nhấp vào nút Nhập khẩu (Nhập khẩu) ở cuối cửa sổ. Sau một thời gian cần thiết để tải dữ liệu, nội dung của các bảng được đánh dấu sẽ xuất hiện trong các ô trên trang tính:

Để có thêm tùy chỉnh, bạn có thể nhấp chuột phải vào bất kỳ ô nào trong số này và chọn lệnh từ menu ngữ cảnh. Thuộc tính phạm vi (Thuộc tính phạm vi dữ liệu).Trong hộp thoại này, nếu muốn, có thể định cấu hình tần suất cập nhật và các thông số khác:

Báo giá cổ phiếu, vì chúng thay đổi vài phút một lần, bạn có thể cập nhật thường xuyên hơn (hộp kiểm Làm mới mỗi N phút.), nhưng tỷ giá hối đoái, trong hầu hết các trường hợp, chỉ cần cập nhật một lần một ngày là đủ (hộp kiểm Cập nhật khi mở tệp).

Lưu ý rằng toàn bộ phạm vi dữ liệu đã nhập được Excel coi là một đơn vị duy nhất và được đặt tên riêng, bạn có thể thấy tên này trong Trình quản lý tên trên tab công thức (Công thức - Trình quản lý tên).

Phương pháp 2: Truy vấn web tham số để nhận tỷ giá hối đoái cho một phạm vi ngày nhất định

Phương pháp này là một tùy chọn đầu tiên được hiện đại hóa một chút và mang lại cho người dùng cơ hội nhận được tỷ giá hối đoái của đơn vị tiền tệ mong muốn không chỉ cho ngày hiện tại mà còn cho bất kỳ ngày nào khác hoặc khoảng thời gian quan tâm. Để thực hiện điều này, yêu cầu web của chúng tôi phải được chuyển thành một tham số, tức là thêm hai tham số làm rõ vào nó (mã đơn vị tiền tệ chúng tôi cần và ngày hiện tại). Để làm điều này, chúng tôi làm như sau:

1. Chúng tôi tạo một yêu cầu trên web (xem phương pháp 1) đến trang của trang web của Ngân hàng Trung ương Nước ta với kho lưu trữ các khóa học: http://cbr.ru/currency_base/dynamics.aspx

2. Trong biểu mẫu bên trái, hãy chọn đơn vị tiền tệ mong muốn và đặt ngày bắt đầu và ngày kết thúc:

3. Nhấp vào nút Để lấy dữ liệu và sau một vài giây, chúng tôi thấy một bảng với các giá trị khóa học mà chúng tôi cần cho một khoảng ngày nhất định. Cuộn toàn bộ bảng kết quả xuống và đánh dấu nó để nhập bằng cách nhấp vào mũi tên màu đen và màu vàng ở góc dưới bên trái của trang web (chỉ cần đừng hỏi tại sao mũi tên này ở đó mà không phải bên cạnh bảng - đây là một câu hỏi cho các nhà thiết kế trang web).

Bây giờ chúng ta đang tìm một nút có đĩa mềm ở góc trên bên phải của cửa sổ Lưu yêu cầu (Lưu truy vấn) và lưu tệp với các thông số theo yêu cầu của chúng tôi vào bất kỳ thư mục phù hợp nào dưới bất kỳ tên tiện lợi nào - ví dụ: trong Tài liệu của tôi dưới tên cbr. iqy.  Sau đó, cửa sổ Truy vấn Web và tất cả Excel có thể bị đóng ngay bây giờ.

4. Mở thư mục nơi bạn đã lưu yêu cầu và tìm tệp yêu cầu cbr. iqy, sau đó nhấp chuột phải vào nó - Mở bằng - Notepad (hoặc chọn nó từ danh sách - thường nó là một tệp Notepad.exe từ thư mục C:Cửa sổ). Sau khi mở tệp yêu cầu trong Notepad, bạn sẽ thấy một cái gì đó như sau:

Điều giá trị nhất ở đây là dòng có địa chỉ và các tham số truy vấn trong đó, chúng tôi sẽ thay thế - mã đơn vị tiền tệ chúng tôi cần (được đánh dấu màu đỏ) và ngày kết thúc, mà chúng tôi sẽ thay thế bằng mã của ngày hôm nay (được đánh dấu trong màu xanh da trời). Hãy cẩn thận chỉnh sửa dòng để có được những điều sau:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Mã tiền tệ"]&date_req1=01.01.2000&r1=1&date_req2=["Ngày"]& rt = 1 & mode = 1

Để mọi thứ khác như cũ, lưu và đóng tệp.

5. Tạo một cuốn sách mới trong Excel, mở trang tính mà chúng tôi muốn nhập kho lưu trữ tỷ giá của Ngân hàng Trung ương. Trong bất kỳ ô thích hợp nào, hãy nhập một công thức sẽ cung cấp cho chúng tôi ngày hiện tại ở định dạng văn bản để thay thế truy vấn:

= TEXT (TODAY (); ”DD.MM.YYYY”)

hoặc phiên bản tiếng Anh

= TEXT (TODAY (), »dd.mm.yyyy»)

Ở đâu đó gần đó, chúng tôi nhập mã của đơn vị tiền tệ mà chúng tôi cần từ bảng:

Mã yêu cầu cũng có thể được nhìn trộm trong chuỗi truy vấn trực tiếp trên trang web của Ngân hàng Trung ương.

6. Chúng tôi tải dữ liệu trên trang tính, sử dụng các ô đã tạo và tệp cbr.iqy làm cơ sở, tức là chuyển đến tab Dữ liệu - Kết nối - Tìm người khác (Dữ liệu - Kết nối hiện có). Trong cửa sổ lựa chọn nguồn dữ liệu mở ra, hãy tìm và mở tệp cbr. iqy. Trước khi nhập, Excel sẽ cùng chúng tôi làm rõ ba điều.

Đầu tiên, nơi nhập bảng dữ liệu:

Thứ hai, lấy mã tiền tệ từ đâu (bạn có thể đánh dấu vào ô Sử dụng giá trị mặc định này (Sử dụng giá trị / tham chiếu này cho các lần làm mới trong tương lai), để sau này mỗi lần ô này không được chỉ định trong quá trình cập nhật và hộp kiểm Tự động cập nhật khi giá trị ô thay đổi (Tự động làm mới khi giá trị ô thay đổi):

Thứ ba, từ ô nào sẽ lấy ngày kết thúc (bạn cũng có thể chọn cả hai ô ở đây để ngày mai bạn không phải đặt các thông số này theo cách thủ công khi cập nhật):

Nhấp chuột OK, đợi một vài giây và nhận được bản lưu trữ đầy đủ về tỷ giá hối đoái của đơn vị tiền tệ mong muốn trên trang tính:

Như trong phương pháp đầu tiên, bằng cách nhấp chuột phải vào dữ liệu đã nhập và chọn lệnh Thuộc tính phạm vi (Thuộc tính phạm vi dữ liệu), bạn có thể điều chỉnh tốc độ làm mới Khi mở một tệp (Làm mới khi mở tệp). Sau đó, nếu bạn có quyền truy cập Internet, dữ liệu sẽ được tự động cập nhật hàng ngày, tức là Bảng sẽ tự động được cập nhật dữ liệu mới.

Dễ dàng nhất để trích xuất tỷ lệ cho ngày mong muốn từ bảng của chúng tôi bằng cách sử dụng hàm VPR (Vlookup) - nếu bạn không quen với nó, thì tôi thực sự khuyên bạn nên làm điều này. Ví dụ: với công thức như vậy, bạn có thể chọn tỷ giá hối đoái đô la cho ngày 10 tháng 2000 năm XNUMX từ bảng của chúng tôi:

hoặc bằng tiếng Anh = VLOOKUP (E5, cbr, 3,1)

Ở đâu

• E5 - ô chứa ngày đã cho
  
• cbr - tên của phạm vi dữ liệu (được tạo tự động trong quá trình nhập và thường giống với tên của tệp truy vấn)
• 3 - số sê-ri của cột trong bảng của chúng tôi, nơi chúng tôi lấy dữ liệu từ
• 1 - một đối số bao gồm tìm kiếm gần đúng cho hàm Vlookup để bạn có thể tìm các khóa học cho những ngày trung gian không thực sự có trong cột A (ngày trước đó gần nhất và khóa học của nó sẽ được thực hiện). Bạn có thể đọc thêm về tìm kiếm gần đúng bằng hàm Vlookup tại đây.

• Macro để nhận tỷ giá đô la cho một ngày nhất định trong ô hiện tại
• Chức năng bổ sung PLEX để nhận tỷ giá hối đoái của đồng đô la, euro, hryvnia, bảng Anh, v.v. cho bất kỳ ngày cụ thể nào
• Chèn bất kỳ tỷ giá tiền tệ nào vào bất kỳ ngày nào trong tiện ích bổ sung PLEX