Excel'de güncellenmiş döviz kuru

Daha sonra otomatik güncelleme ile verileri İnternet'ten Excel'e aktarmanın yollarını defalarca analiz ettim. Özellikle:

• Excel 2007-2013'ün eski sürümlerinde bu, doğrudan bir web isteğiyle yapılabilir.
• 2010'dan itibaren bu, Power Query eklentisiyle çok rahat bir şekilde yapılabilir.
  

Microsoft Excel'in en son sürümlerindeki bu yöntemlere, şimdi bir başkasını ekleyebilirsiniz - yerleşik işlevleri kullanarak İnternet'ten XML biçiminde veri almak.

XML (Genişletilebilir İşaretleme Dili = Genişletilebilir İşaretleme Dili), her türlü veriyi tanımlamak için tasarlanmış evrensel bir dildir. Aslında, düz metindir, ancak veri yapısını işaretlemek için özel etiketler eklenmiştir. Birçok site, herkesin indirmesi için verilerinin ücretsiz akışlarını XML biçiminde sağlar. Ülkemiz Merkez Bankası'nın (www.cbr.ru) internet sitesinde özellikle benzer bir teknoloji yardımıyla çeşitli para birimlerinin döviz kurlarına ilişkin verilere yer verilmektedir. Moskova Borsası web sitesinden (www.moex.com) hisse senetleri, tahviller ve diğer birçok faydalı bilgiyi aynı şekilde indirebilirsiniz.

2013 sürümünden bu yana, Excel'in XML verilerini İnternet'ten doğrudan çalışma sayfası hücrelerine yüklemek için iki işlevi vardır: İNTERNET SERVİSİ (İNTERNET SERVİSİ) и FİLTRE.XML (FILTERXML). Çiftler halinde çalışırlar – önce fonksiyon İNTERNET SERVİSİ istenen siteye bir istek yürütür ve yanıtını XML biçiminde döndürür ve ardından işlevi kullanır FİLTRE.XML bu cevabı bileşenlere ayrıştırırız ve ondan ihtiyacımız olan verileri çıkarırız.

Klasik bir örnekle bu fonksiyonların işleyişine bakalım – belirli bir tarih aralığında ihtiyacımız olan herhangi bir para biriminin döviz kurunu Ülkemiz Merkez Bankası'nın internet sitesinden ithal etmek. Aşağıdaki yapıyı boş olarak kullanacağız:

İşte:

• Sarı hücreler, ilgilendiğimiz dönemin başlangıç ​​ve bitiş tarihlerini içerir.
• Mavi olan, komutu kullanan bir açılır para birimi listesine sahiptir. Veri – Doğrulama – Liste (Veri — Doğrulama — Liste).
• Yeşil hücrelerde, bir sorgu dizesi oluşturmak ve sunucunun yanıtını almak için işlevlerimizi kullanacağız.
• Sağdaki tablo para birimi kodlarına bir referanstır (biraz sonra ihtiyacımız olacak).
  

Haydi gidelim!

Adım 1. Bir sorgu dizesi oluşturma

Siteden gerekli bilgileri almak için doğru bir şekilde sormanız gerekmektedir. www.cbr.ru adresine gidiyoruz ve ana sayfanın alt kısmındaki bağlantıyı açıyoruz' Teknik Kaynaklar'- XML kullanarak veri alma (http://cbr.ru/development/SXML/). Biraz aşağı kaydırıyoruz ve ikinci örnekte (Örnek 2) ihtiyacımız olan şey olacak – belirli bir tarih aralığı için döviz kurlarını almak:

Örnekte görebileceğiniz gibi, sorgu dizesi başlangıç ​​tarihlerini içermelidir (tarih_req1) ve sonlar (tarih_req2) bizi ilgilendiren dönemin ve para birimi kodunun (VAL_NM_RQ), almak istediğimiz oran. Ana para birimi kodlarını aşağıdaki tabloda bulabilirsiniz:

Merkez Bankası web sitesinde para birimi kodlarına ilişkin eksiksiz bir kılavuz da mevcuttur – bkz. http://cbr.ru/scripts/XML_val.asp?d=0

Şimdi bir sayfadaki bir hücrede aşağıdakileri içeren bir sorgu dizesi oluşturacağız:

• bir araya getirmek için metin birleştirme operatörü (&);
  
• Özellikler VPR (DÜŞEYARA)dizinde ihtiyacımız olan para biriminin kodunu bulmak için;
  
• Özellikler METİN (METİN), tarihi verilen kalıba göre gün-ay-yıl eğik çizgi ile dönüştürür.

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

Adım 2. İsteği yürütün

Şimdi işlevi kullanıyoruz İNTERNET SERVİSİ (İNTERNET SERVİSİ) tek argüman olarak oluşturulan sorgu dizesiyle. Cevap, uzun bir XML kodu satırı olacaktır (bütününü görmek istiyorsanız, kelime kaydırmayı açmak ve hücre boyutunu artırmak daha iyidir):

Adım 3. Cevabı ayrıştırma

Yanıt verilerinin yapısını anlamayı kolaylaştırmak için çevrimiçi XML ayrıştırıcılarından birini kullanmak daha iyidir (örneğin, http://xpather.com/ veya https://jsonformatter.org/xml-parser), XML kodunu görsel olarak biçimlendirebilir, girintiler ekleyebilir ve sözdizimini renkle vurgulayabilir. O zaman her şey çok daha netleşecek:

Artık kurs değerlerinin etiketlerimiz tarafından çerçevelendiğini açıkça görebilirsiniz. ..., ve tarihler niteliklerdir Tarih etiketlerde .

Bunları ayıklamak için, sayfada on (veya daha fazla - bir kenar boşluğu ile yapıldıysa) boş hücreden oluşan bir sütun seçin (10 günlük bir tarih aralığı ayarlandığından) ve işlevi formül çubuğuna girin FİLTRE.XML (FİLTREXML):

Burada, ilk argüman sunucu yanıtına (B8) sahip bir hücreye bağlantıdır ve ikincisi, gerekli XML kod parçalarına erişmek ve bunları ayıklamak için kullanılabilecek özel bir dil olan XPath'deki bir sorgu dizesidir. Örneğin, burada XPath dili hakkında daha fazla bilgi edinebilirsiniz.

Formülü girdikten sonra basılmaması önemlidir. Keşfetve klavye kısayolu Ctrl+vardiya+Keşfet, yani onu bir dizi formülü olarak girin (etrafındaki küme parantezleri otomatik olarak eklenecektir). Excel'de dinamik dizileri destekleyen en son Office 365 sürümüne sahipseniz, basit bir Keşfet, ve önceden boş hücreleri seçmeniz gerekmez - işlevin kendisi ihtiyaç duyduğu kadar hücre alacaktır.

Tarihleri ​​çıkarmak için aynısını yapacağız - bitişik sütunda birkaç boş hücre seçeceğiz ve aynı işlevi kullanacağız, ancak farklı bir XPath sorgusu ile Date özniteliklerinin tüm değerlerini Record etiketlerinden almak için:

=FILTER.XML(B8;”//Kayıt/@Tarih”)

Artık gelecekte orijinal B2 ve B3 hücrelerindeki tarihleri ​​değiştirirken veya B3 hücresinin açılır listesinden farklı bir para birimi seçerken, yeni veriler için Merkez Bankası sunucusuna başvurarak sorgumuz otomatik olarak güncellenecektir. Bir güncellemeyi manuel olarak zorlamak için ayrıca klavye kısayolunu da kullanabilirsiniz. Ctrl+Ara Toplam+F9.

• Power Query aracılığıyla bitcoin oranını Excel'e aktarın
• Excel'in eski sürümlerinde İnternet'ten döviz kurlarını içe aktarın