Nilai tukar yang diperbarui di Excel

Saya telah berulang kali menganalisis cara mengimpor data ke Excel dari Internet dengan pembaruan otomatis berikutnya. Khususnya:

• Di versi Excel 2007-2013 yang lebih lama, ini dapat dilakukan dengan permintaan web langsung.
• Mulai tahun 2010, hal ini dapat dilakukan dengan sangat mudah dengan add-in Power Query.
  

Untuk metode ini di versi terbaru Microsoft Excel, Anda sekarang dapat menambahkan yang lain – mengimpor data dari Internet dalam format XML menggunakan fungsi bawaan.

XML (eXtensible Markup Language = Extensible Markup Language) adalah bahasa universal yang dirancang untuk menggambarkan segala jenis data. Sebenarnya, ini adalah teks biasa, tetapi dengan tag khusus yang ditambahkan untuk menandai struktur data. Banyak situs menyediakan aliran data mereka secara gratis dalam format XML untuk diunduh siapa saja. Di situs web Bank Sentral Negara Kita (www.cbr.ru), khususnya, dengan bantuan teknologi serupa, data tentang nilai tukar berbagai mata uang diberikan. Dari situs web Moscow Exchange (www.moex.com), Anda dapat mengunduh kutipan untuk saham, obligasi, dan banyak informasi berguna lainnya dengan cara yang sama.

Sejak versi 2013, Excel memiliki dua fungsi untuk memuat data XML secara langsung dari Internet ke dalam sel lembar kerja: LAYANAN WEB (LAYANAN WEB) и FILTER.XML (FILTERXML). Mereka bekerja berpasangan – pertama fungsinya LAYANAN WEB mengeksekusi permintaan ke situs yang diinginkan dan mengembalikan responsnya dalam format XML, dan kemudian menggunakan fungsi FILTER.XML kami "mengurai" jawaban ini ke dalam komponen, mengekstraksi data yang kami butuhkan darinya.

Mari kita lihat pengoperasian fungsi-fungsi ini menggunakan contoh klasik – mengimpor nilai tukar mata uang apa pun yang kita butuhkan untuk interval tanggal tertentu dari situs web Bank Sentral Negara Kita. Kami akan menggunakan konstruksi berikut sebagai kosong:

Sini:

• Sel kuning berisi tanggal awal dan akhir periode yang menarik bagi kami.
• Yang biru memiliki daftar drop-down mata uang menggunakan perintah Data – Validasi – Daftar (Data — Validasi — Daftar).
• Di sel hijau, kami akan menggunakan fungsi kami untuk membuat string kueri dan mendapatkan respons server.
• Tabel di sebelah kanan adalah referensi ke kode mata uang (kita akan membutuhkannya nanti).
  

Let’s go!

Langkah 1. Membentuk string kueri

Untuk mendapatkan informasi yang diperlukan dari situs, Anda harus menanyakannya dengan benar. Kami pergi ke www.cbr.ru dan membuka tautan di footer halaman utama' Sumber Daya Teknis'- Mendapatkan data menggunakan XML (http://cbr.ru/development/SXML/). Kami menggulir sedikit lebih rendah dan dalam contoh kedua (Contoh 2) akan ada yang kami butuhkan – mendapatkan nilai tukar untuk interval tanggal tertentu:

Seperti yang Anda lihat dari contoh, string kueri harus berisi tanggal mulai (tanggal_req1) dan akhiran (tanggal_req2) dari periode yang menarik bagi kami dan kode mata uang (VAL_NM_RQ), tingkat yang ingin kita dapatkan. Anda dapat menemukan kode mata uang utama pada tabel di bawah ini:

Panduan lengkap untuk kode mata uang juga tersedia di situs web Bank Sentral – lihat http://cbr.ru/scripts/XML_val.asp?d=0

Sekarang kita akan membentuk string kueri dalam sel pada lembar dengan:

• operator penggabungan teks (&) untuk menyatukannya;
  
• Fitur VPR (VLOOKUP)untuk menemukan kode mata uang yang kita butuhkan di direktori;
  
• Fitur TEKS (TEKS), yang mengonversi tanggal menurut pola yang diberikan hari-bulan-tahun melalui garis miring.

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

Langkah 2. Jalankan permintaan

Sekarang kita menggunakan fungsi LAYANAN WEB (LAYANAN WEB) dengan string kueri yang dihasilkan sebagai satu-satunya argumen. Jawabannya adalah baris kode XML yang panjang (lebih baik mengaktifkan bungkus kata dan menambah ukuran sel jika Anda ingin melihatnya secara keseluruhan):

Langkah 3. Mengurai jawabannya

Untuk memudahkan memahami struktur data respons, lebih baik menggunakan salah satu parser XML online (misalnya, http://xpather.com/ atau https://jsonformatter.org/xml-parser), yang dapat memformat kode XML secara visual, menambahkan indentasi ke dalamnya dan menyorot sintaks dengan warna. Maka semuanya akan menjadi lebih jelas:

Sekarang Anda dapat dengan jelas melihat bahwa nilai kursus dibingkai oleh tag kami ..., dan tanggal adalah atribut Tanggal dalam tag .

Untuk mengekstraknya, pilih kolom sepuluh (atau lebih – jika dilakukan dengan margin) sel kosong pada lembar (karena interval tanggal 10 hari telah ditetapkan) dan masukkan fungsi di bilah rumus FILTER.XML (SARINGXML):

Di sini, argumen pertama adalah tautan ke sel dengan respons server (B8), dan yang kedua adalah string kueri di XPath, bahasa khusus yang dapat digunakan untuk mengakses fragmen kode XML yang diperlukan dan mengekstraknya. Anda dapat membaca lebih lanjut tentang bahasa XPath, misalnya, di sini.

Penting bahwa setelah memasukkan formula, jangan tekan Enter, dan pintasan keyboard Ctrl+perubahan+Enter, yaitu memasukkannya sebagai rumus array (kurung kurawal di sekitarnya akan ditambahkan secara otomatis). Jika Anda memiliki versi terbaru Office 365 dengan dukungan untuk array dinamis di Excel, maka sederhana Enter, dan Anda tidak perlu memilih sel kosong terlebih dahulu – fungsi itu sendiri akan mengambil sel sebanyak yang dibutuhkan.

Untuk mengekstrak tanggal, kami akan melakukan hal yang sama – kami akan memilih beberapa sel kosong di kolom yang berdekatan dan menggunakan fungsi yang sama, tetapi dengan kueri XPath yang berbeda, untuk mendapatkan semua nilai atribut Tanggal dari tag Rekam:

=FILTER.XML(B8;”//Rekam/@Tanggal”)

Sekarang di masa depan, ketika mengubah tanggal di sel asli B2 dan B3 atau memilih mata uang yang berbeda di daftar drop-down sel B3, kueri kami akan diperbarui secara otomatis, merujuk ke server Bank Sentral untuk data baru. Untuk memaksa pembaruan secara manual, Anda juga dapat menggunakan pintasan keyboard Ctrl+lain+F9.

• Impor tingkat bitcoin ke Excel melalui Power Query
• Impor nilai tukar dari Internet di versi Excel yang lebih lama