Impor nilai tukar dari Internet

Mengimpor nilai mata uang tertentu dari Internet dengan pembaruan otomatis adalah tugas yang sangat umum bagi banyak pengguna Microsoft Excel. Bayangkan Anda memiliki daftar harga yang harus dihitung ulang setiap pagi sesuai kurs. Atau anggaran proyek. Atau biaya kontrak, yang harus dihitung dengan menggunakan nilai tukar dolar pada tanggal penutupan kontrak.

Dalam situasi seperti itu, Anda dapat memecahkan masalah dengan cara yang berbeda – semuanya tergantung pada versi Excel yang telah Anda instal dan add-on apa yang ada di atasnya.

Metode 1: Permintaan web sederhana untuk nilai tukar saat ini

Metode ini cocok untuk mereka yang masih memiliki versi lama Microsoft Office 2003-2007 di komputer mereka. Itu tidak menggunakan add-on atau makro pihak ketiga dan hanya beroperasi pada fungsi bawaan.

Klik tombol Dari internet (Web) tab Data (Tanggal). Di jendela yang muncul, di baris Alamat (Alamat) masukkan URL situs dari mana informasi akan diambil (misalnya, http://www.finmarket.ru/currency/rates/) dan tekan tombol Enter.

Saat halaman dimuat, panah hitam dan kuning akan muncul di tabel yang bisa diimpor Excel. Mengklik panah seperti itu menandai tabel untuk diimpor.

Ketika semua tabel yang diperlukan ditandai, klik tombol impor (Impor) di bagian bawah jendela. Setelah beberapa waktu diperlukan untuk memuat data, isi tabel yang ditandai akan muncul di sel pada lembar:

Untuk penyesuaian tambahan, Anda dapat mengklik kanan pada salah satu sel ini dan memilih perintah dari menu konteks. Rentang properti (Properti rentang data).Dalam kotak dialog ini, jika diinginkan, dimungkinkan untuk mengonfigurasi frekuensi pembaruan dan parameter lainnya:

Harga saham, karena berubah setiap beberapa menit, Anda dapat memperbarui lebih sering (kotak centang Segarkan setiap N menit.), tetapi nilai tukar, dalam banyak kasus, cukup diperbarui sekali sehari (kotak centang Pembaruan pada file terbuka).

Perhatikan bahwa seluruh rentang data yang diimpor diperlakukan oleh Excel sebagai satu unit dan diberi namanya sendiri, yang dapat dilihat di Pengelola Nama pada tab rumus (Rumus — Manajer Nama).

Metode 2: Kueri web parametrik untuk mendapatkan nilai tukar untuk rentang tanggal tertentu

Metode ini adalah opsi pertama yang sedikit dimodernisasi dan memberi pengguna kesempatan untuk menerima nilai tukar mata uang yang diinginkan tidak hanya untuk hari ini, tetapi juga untuk tanggal atau interval tanggal lain yang menarik. Untuk melakukan ini, permintaan web kita harus diubah menjadi permintaan parametrik, yaitu menambahkan dua parameter klarifikasi ke dalamnya (kode mata uang yang kita butuhkan dan tanggal saat ini). Untuk melakukan ini, kami melakukan hal berikut:

1. Kami membuat permintaan web (lihat metode 1) ke halaman situs web Bank Sentral Negara Kita dengan arsip kursus: http://cbr.ru/currency_base/dynamics.aspx

2. Pada formulir di sebelah kiri, pilih mata uang yang diinginkan dan atur tanggal mulai dan berakhir:

3. Klik tombol Untuk mendapatkan data dan setelah beberapa detik kami melihat tabel dengan nilai kursus yang kami butuhkan untuk interval tanggal tertentu. Gulir tabel yang dihasilkan ke bawah dan tandai untuk diimpor dengan mengklik panah hitam dan kuning di sudut kiri bawah halaman web (jangan tanya mengapa panah ini ada di sana dan bukan di sebelah tabel – ini dia pertanyaan untuk perancang situs).

Sekarang kami mencari tombol dengan floppy disk di sudut kanan atas jendela Simpan Permintaan (Simpan Kueri) dan simpan file dengan parameter permintaan kami ke folder mana pun yang sesuai dengan nama yang sesuai – misalnya, di Dokumen saya atas nama cbr. iqy.  Setelah itu, jendela Web Query dan semua Excel dapat ditutup untuk saat ini.

4. Buka folder tempat Anda menyimpan permintaan dan cari file permintaan cbr. iqy, lalu klik kanan di atasnya – Buka Dengan – Notepad (atau pilih dari daftar – biasanya berupa file Notepad.exe dari folder C: Jendela). Setelah membuka file permintaan di Notepad, Anda akan melihat sesuatu seperti ini:

Hal yang paling berharga di sini adalah baris dengan alamat dan parameter kueri di dalamnya, yang akan kita ganti – kode mata uang yang kita butuhkan (disorot dengan warna merah) dan tanggal akhir, yang akan kita ganti dengan yang hari ini (disorot dalam biru). Edit baris dengan hati-hati untuk mendapatkan yang berikut:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Kode mata uang"]&date_req1=01.01.2000&r1=1&date_req2=["Tanggal"]&rt=1&mode=1

Biarkan yang lainnya apa adanya, simpan dan tutup file.

5. Buat buku baru di Excel, buka lembar tempat kita ingin mengimpor arsip tarif Bank Sentral. Di sel mana pun yang sesuai, masukkan rumus yang akan memberi kita tanggal saat ini dalam format teks untuk substitusi kueri:

=TEXT(HARI INI();”DD.MM.YYYY”)

atau dalam versi bahasa Inggris

=TEXT(HARI INI(),»dd.mm.yyyy»)

Di suatu tempat di dekatnya, kami memasukkan kode mata uang yang kami butuhkan dari tabel:

Kode yang diperlukan juga dapat diintip dalam string kueri langsung di situs web Bank Sentral.

6. Kami memuat data pada lembar, menggunakan sel yang dibuat dan file cbr.iqy sebagai dasarnya, yaitu pergi ke tab Data – Koneksi – Temukan Lainnya (Data — Koneksi yang Ada). Di jendela pemilihan sumber data yang terbuka, temukan dan buka file cbr. iqy. Sebelum mengimpor, Excel akan mengklarifikasi tiga hal dengan kami.

Pertama, tempat mengimpor tabel data:

Kedua, dari mana mendapatkan kode mata uang (Anda dapat mencentang kotak Gunakan nilai default ini (Gunakan nilai/referensi ini untuk penyegaran di masa mendatang), sehingga nanti setiap kali sel ini tidak ditentukan selama pembaruan dan kotak centang Perbarui secara otomatis ketika nilai sel berubah (Segarkan secara otomatis saat nilai sel berubah):

Ketiga, dari sel mana untuk mengambil tanggal akhir (Anda juga dapat mencentang kedua kotak di sini sehingga besok Anda tidak perlu mengatur parameter ini secara manual saat memperbarui):

Klik OK, tunggu beberapa detik dan dapatkan arsip lengkap nilai tukar mata uang yang diinginkan pada lembar:

Seperti pada metode pertama, dengan mengklik kanan pada data yang diimpor dan memilih perintah Rentang properti (Properti rentang data), Anda dapat menyesuaikan kecepatan refresh Saat membuka file (Segarkan pada file yang terbuka). Kemudian, jika Anda memiliki akses ke Internet, data akan diperbarui secara otomatis setiap hari, yaitu Tabel akan diperbarui secara otomatis dengan data baru.

Cara termudah untuk mengekstrak tarif untuk tanggal yang diinginkan dari tabel kami menggunakan fungsi VPR (VLOOKUP) - jika Anda tidak terbiasa dengannya, maka saya sangat menyarankan Anda untuk melakukan ini. Dengan rumus seperti itu, misalnya, Anda dapat memilih nilai tukar dolar untuk 10 Januari 2000 dari tabel kami:

atau dalam bahasa Inggris =VLOOKUP(E5,cbr,3,1)

dimana

• E5 – sel yang berisi tanggal yang diberikan
  
• cbr – nama rentang data (dihasilkan secara otomatis selama impor dan biasanya sama dengan nama file kueri)
• 3 – nomor seri kolom di tabel kami, tempat kami mendapatkan datanya
• 1 – argumen yang menyertakan pencarian perkiraan untuk fungsi VLOOKUP sehingga Anda dapat menemukan kursus untuk tanggal antara yang sebenarnya tidak ada di kolom A (tanggal terdekat sebelumnya dan jalurnya akan diambil). Anda dapat membaca lebih lanjut tentang perkiraan pencarian menggunakan fungsi VLOOKUP di sini.

• Makro untuk mendapatkan nilai dolar untuk tanggal tertentu di sel saat ini
• Fungsi tambahan PLEX untuk mendapatkan nilai tukar dolar, euro, hryvnia, pound sterling, dll. untuk tanggal tertentu
• Masukkan nilai tukar mata uang apa pun pada tanggal apa pun di add-on PLEX