Categories
Excel Power Query

Excel ile TCMB Döviz Kurları ve Power Query ❤

Excel ile TCMB Döviz Kurları zaman serileri arasında bağlantı kurmak ve sürekli olarak güncel veriyi çekmek mümkün ve çok kolay.

Bu yazımızda Excel’de uzun zamandır yer alan ancak gündelik uygulamada çok da yaygınlaşamamış Power Query (Veri Al ve Dönüştür) özelliklerinden bahsetmek istiyorum. Elbette Excel Uygulamaları felsefesine uygun olarak somut ve yaygın olarak kullanılan bir örnek üzerinden bunu yapacağız.

Neler Yapacağız?

  • Power Query ile web bağlantısı üzerinden veri çekeceğiz.
  • Power Query ile verileri, istediğimiz şekilde işledikten sonra kayıt altına alacağız.
  • Doğrudan TCMB üzerinden döviz kurlarını alacağız.
  • TCMB döviz kurlarını istediğimiz tarih aralığı için tam seri halinde alacağız. Veri eksiğimiz olmayacak.
  • Uzun formülleri birleştirme yöntemlerini göreceğiz.
  • Power Query sorgu oluştururken, Excel içerisinde yazdığımız değerlere göre değişken bir sorgu oluşturacağız.
  • İstediğimiz tarih aralığı için TÜM döviz kuru verilerini Excel içerisine düzgün bir tablo olarak aktarmış olacağız.

Düzgün tablo ile ne demek istediğimi “Excel ile Tablo Oluşturma” başlıklı makalede, düzgün Excel kullanma ile ilgili tavsiyeler için “Daha iyi Excel kullanmak için 5 Altın Kural” başlıklı makalede bulabilirsiniz.

Her zamanki gibi tüm kaynakları linkleri ile birlikte paylaşıyorum, makalenin en alt kısmında tüm kaynakların dökümünü bulabilirsiniz.

Video Anlatım

Bu makalede anlatılanları YouTube kanalımızda yayınlanan video anlatım ile takip edebilirsiniz.

Excel ile TCMB Döviz Kurlarını ve Power Query ❤ Video Anlatımı

Örnek Dosya

Örnek dosyayı indirmek için link: https://www.muhendisrehberi.com/pq1/

Nedir – Power Query?

Çalışmalarınızda dış veriler ile bağlantı kurmak gerektiğinde bunu nasıl başarıyorsunuz? En etkin ve güvenilir bağlantı şeklini mi kullanıyorsunuz? Yoksa sürekli hatalar alıp, çalışmaya harcadığınız vakitten çok hata ayıklama ile mi vakit kaybediyorsunuz?

Power Query aslında sizin en büyük yardımcınız olmaya aday, tekrarlı işlemleri delege edebileceğiniz çok kullanışlı bir özellik. Excel’in dış veriye bağlanmak için geliştirdiği ve Power plaftormu adı altında sunulan hizmetlerden birisi. Power BI, Power Query ve Power Automate hizmetlerine önümüzdeki zamanlarda daha da geniş yer vermek istiyorum.

Nerede Bu Power Query?

Yeni Excel Sürümlerinde (2016+)

2016 ve sonraki sürümlerde (Excel 2016, Excel 2019, Office 365 ve Microsoft 365 abonelikleri kapsamındaki masaüstü uygulamalarında) Power Query şerit menü üzerindeki “Veri” sekmesinde “Verileri Al ve Dönüştür” kısmında yer almaktadır. (İngilizce Data -> Get and Transform Data).

Eski Sürümler (2010/2013)

Excel 2010 ve 2013 sürümleri için Power Query özelliklerini bir eklenti kapsamında indirebilirsiniz. Bu eklenti ücretsizdir ve doğrudan Microsoft’un Power Query eklentisi için oluşturduğu sayfadan indirilebilir [1]Excel için Microsoft Power Query indirme sayfası https://www.microsoft.com/tr-Tr/download/details.aspx?id=39379

İndirirken sisteminizde kurulu olan Ofis versiyonuna göre 64-bit veya 32-bit olarak indirmeniz gerektiğini unutmayın.

Ne işe Yarar – Power Query?

Çalışmalarınız sırasında eğer düzenli olarak verileri alıp, ihtiyacınıza göre düzenleyip bunları rapor olarak yayınlıyor musunuz? Eğer bu sorulara “Evet” cevabını veriyorsanız sizin derdinizin dermanı “Power Query”.

  • Verilerin ihtiyacınız kadarını alıp, dönüştürmek ve raporlamak
  • Birden fazla kaynaktan verileri birleştirmek
  • Büyük veri setleri arasından ihtiyacınız kadarını kullanmak

Uygulamalı Örnek – Excel ile TCMB Döviz Kurları ve Power Query ❤

Excel içerisine Merkez Bankası döviz kurlarını almak ile ilgili eski yöntemleri içeren pek çok örnek var. Dikkat ettiyseniz yeni tarihleri örneklerin çoğunluğu Merkez Bankası üzerinden değil, üçüncü taraf web siteleri üzerinden döviz kurlarını Excel içerisine aktarmaya yönelik örnekler.

Bizim burada yapacağımız ise Türkiye Cumhuriyeti Merkez Bankası Elektronik Veri Dağıtım Sistemi (TCMB EVDS) üzerinden doğrudan Excel dosyamızın içine güvenilir bir şekilde aktarmak. Üçüncü taraf olmayacak.

TCMB EVDS sistemi üzerinden pek çok yardımcı evrak ve anlatıma erişebilirsiniz [2]TCMB EVDS Kullanıcı Dokümanları sayfası üzerinden Yardımcı Dokümanlara erişebilirsiniz https://evds2.tcmb.gov.tr/index.php?/evds/userDocs.

Hangi Döviz Kurları Gelsin?

Şimdi sırada ne isteyeceğinize karar vermek var. Bu örnek için USD, EUR ve GBP döviz cinsleri için Efektif Alış ve Efektif Satış kurlarını, 01 Ocak 2010 – 30 Haziran 2020 tarih aralığında, tüm verileri içerecek şekilde günlük kayıtlar halinde çekeceğiz. Sonraki aşamalarda bu kısımları nasıl dinamik olarak değiştirebileceğimizi göreceğiz.

  1. Kullanmak istediğiniz serilerin kodlarını bulmanız gerekiyor. Tüm Seriler -> Kurlar kısmından eklemek istediğiniz kurları seçin. “Ekle” Tuşu ile “Seçtiklerim” kısmına aktarımını yapın.
  2. Sonrasında “Rapor Oluştur” düğmesine tıklayın ve sayfanın alt kısmında tabloyu görün.
    1. Bu tablonun en altında “Seri Açıklamaları” kısmında “TP.DK.USD.A.YTL” gibi kodlar yer almaktadır.
    2. “(USD) ABD Doları (Döviz Alış)-DüzeyKurlar-Döviz Kurları” gibi açıklamalarını da görebilirsiniz.
    3. Biz bu örnekte “TP.DK.USD.A.YTL” gibi kodları kullanacağız.
    4. Kolaylık olması açısından, bu raporu hemen sağ üst köşesinde “Excel İndir” düğmesini kullanarak indirebilirsiniz.
      1. Bu aşamada indirdiğiniz Excel sadece odur, güncellenmez, statik bir excel olarak inmiş olur. Örnek dosya kaynaklar kısmında eklidir [3]Statik Sorgu Sonucu Excel dosyası indirme linki.
      2. Bu indirdiğiniz dosyadaki sütun başlıkları, almak istediğimiz döviz bilgilerinin “seri kodu” kısmını verecek bize. Sütun başlıklarını alıp, liste olarak kullanabiliriz.

Hangi Zaman Aralığı Gelsin?

  1. XML aramasını oluşturacağız. Yardımcı evrak kısmında detayları verildiği üzere XML sorgu adresini oluşturmamız gerekiyor.
  2. EVDS’nin XML yapısı şu şekilde:
https://evds2.tcmb.gov.tr/service/evds/series=SERİLER&startDate=GG-AA-YYYY&endDate=GG-AA-YYYY&type=xml&key=APIANAHTARI

Bu XML sorgusunu tariflemek için bir zincir gibi hayal edelim:

Oluşturulacak XML Sorgu yapısı = (Adres) + (Seriler) + (Zaman Aralığı)
Oluşturulacak XML sorgu yapısı.

Bu XML sorgusunu Excel içine yazdığımız seri isimlerini, başlangıç ve bitiş tarihini ve API anahtarımızı okuyacak şekilde ister & işareti ile birleştirerek, ister METİNBİRLEŞTİR (TEXTJOIN) veya hernangi bir şekilde yan yana ekleyerek birleştireceğiz.

API Anahtarı ve EVDS Kayıt İşlemleri

Öncelikle EVDS sisteminde ücretsiz bir kullanıcı hesabı oluşturmalısınız. Sonrasında profil sayfanızdan API Anahtarınızı bir kenara not almalısınız. Bu yazı boyunca APIANAHTARI olarak göreceğiniz kısımları kendi API anahtarınız ile değiştirmelisiniz. API anahtarınız karmaşık görünen “xaqARGBpo983…..” gibi harf ve rakam dizisi olup size özel bir anahtardır.

Power Query Adımları

Merkez Bankası TCMB Döviz Kurlarını Power Query ile Excel içine aktarımı

XML Sorgusu

Yukarıda anlatıldığı şekilde XML sorgusunu oluşturalım.

XML sorgusunun anatomisi.
XML sorgusunun anatomisi.

XML Sorgusunu deneyin

Emeklerimiz boşa gitmesin, bu adıma kadar yaptıklarımızı denemek için oluşturulan XML sorgusunu tarayıcıya yazarak sonuçlarına bakabiliriz. Hata yok ise şöyle bir içerik görmelisiniz:

XML Sorgusunu tarayıcı ile deneyelim…

Eğer yukarıdaki gibi bir içerik ile karşılaşıyorsanız tebrikler! XML sorgusu doğru bir şekilde kurguladınız demektir. Bu gördüğümüz XML kodu olarak verinin sunulması.

Adlandırılmış Aralık (Named Range)

XML Sorgusunu oluşturduğumuz hücreyi bir “adlandırılmış aralık” veya İngilizce “named range” olarak tanımlamamız gerekiyor.

Bu tanım, Power Query sorgumuza, adresi okuması için tariflerken işimize yarayacak.

Oluşturulan XML sorgusunu içeren hücreyi Formül > Ad Yöneticisi > Yeni... menüsünden adlandırılmış aralık olarak tanımlayabilirsiniz.

Verileri Al

Bu XML kodunu “Veri Al >Web’den” seçeneği ile Web kaynaklı bir Power Query için adres gösterip veri dönüştürme adımına geçelim…

Web’den veri al seçeneği ile adres olarak oluşturduğumuz XML sorgusunu girelim.

Dönüştür

Verileri dönüştürme işlemlerimizin tamamını “Power Query Düzenleyicisi” (Query Editor) ekranından yapacağız.

Açılan ekranda “Table” veya “Tablo” kısmına çift tıklayarak XML sorgusundan elde edilen verilerin “Tablo” sunumunu görebilirsiniz.

Power Query Düzenleyici ekranı.

Bu aşamada görüldüğü üzere tam damak tadımıza uygun olmasa bile, tarihleri ve her biri ayrı sütunda o tarihlere denk gelen alış-satış döviz kurlarını birden fazla döviz çeşidi için görebiliyoruz.

Power Query kaynağını Excel içinden Okutalım

Gelişmiş düzenleyici görünümü.

Yukarıda görüldüğü üzere Power Query Düzenleyicisi ekranında “Görünüm -> Gelişmiş Düzenleyici” menüsünden açabileceğiniz gelişmiş düzenleyici ekranında, Power Query arka planında çalışan “M Code” programlama dilindeki kodları görebilirsiniz.

Kodumuzun şu andaki izlediği adımlar şunlar:

  • Birinci adım olarak, XML türünden bir kaynak olan …. adresinden verileri alıyor.
  • Sonrasında dönüştürme adımlarını sırasıyla uyguluyor.

Bizim ilk adım olarak araya ekleyeceğimiz bir satır ile yapmak istediğimiz:

  • Kaynak olarak, Adlandırılmış Aralık “Named Range” olarak tanımladığımız hücreden XML adresini okunmasını isteyeceğiz.
DinamikKaynak= Excel.CurrentWorkbook(){[Name="XMLKaynak"]}[Content][Column1]{0},
  • Mevcut kod içinde statik olarak yer alan XML sorgu adresini, bu eklediğimiz ilk adımdaki okumaya yönlendireceğiz.
Gelişmiş Düzenleyici ekranında M Code düzenlemesi.

Kapat ve Yükle

Düzenlemeler bitince, Power Query düzenleyici penceresindeki “Dosya/File” sekmesinde “Kapat ve Yükle” komutu ile power query kapatılır ve sonuçları Excel içine yüklenir.

Power Query sonucunun Tablo olarak Excel içine aktarılması.

Power Query sorgusunun her güncellenmesinde, oluşturduğumuz XML sorgusu kriterlerine göre TCMB EVDS sistemine bağlantı sağlanır ve veriler, Power Query düzenleyicisi ekranında tanımladığımız adımlar sırası ile uygulanarak düzenlenir, sonuç Excel içine aktarılır.

Sonraki Adımlar

Umarım Power Query, Power BI ve M Code hakkında merakınız uyanmıştır.

Ne gibi işleriniz Power Query, Power BI ve M Code ile daha kolay olur?

  • ERP sisteminden çekilen verilerin düzenlenerek raporlanması,
  • Tutulan kayıtların düzgün rapor formatına oturtulması,
  • Çok sayıdaki dosyadan verilerin tek bir raporda derlenmesi,
  • Verileri bir yerden alma, düzenleme ve sunma adımlarını herhangi bir veri kaynağından rutin olarak yapma işlemleri.

Yukarıda sıralanan işler, pek çok iş alanında çokça vakit kaybederek defalarca yaptığımız pek çok işlemi kapsıyor.

Power Query, Power BI ve bunların altyapısını oluşturan M Code ile tüm bunları yorulmadan, sıkılmadan, hata yapmadan düzenleyip, iş hayatınızdaki veriminizi arttırabilirsiniz.

Excel içerisinde macro ve artık güncellenmeyecek olan, sadece geçmişe yönelik destek olarak varlığını VBA programlama dilinin

Sonraki yazılarımda Power Query, Power BI, M Code, RPA (Robot Süreç Otomasyonu, Robotic Process Automation), Microsoft Power Platformu, Microsoft Power Automate, Office Scripts yeni ve yenilikçi konulara ağırlıklı olarak yer vermeyi planlıyorum.

Bu konularda görmek istediğiniz örnekleri, uygulamaları ve genel paylaşımlar hakkında istek, görüş ve önerilerinizi yorum veya e-posta olarak iletin.

Bir sonraki paylaşımda görüşmek dileği ile.

Kaynaklar

Kaynaklar
1 Excel için Microsoft Power Query indirme sayfası https://www.microsoft.com/tr-Tr/download/details.aspx?id=39379
2 TCMB EVDS Kullanıcı Dokümanları sayfası üzerinden Yardımcı Dokümanlara erişebilirsiniz https://evds2.tcmb.gov.tr/index.php?/evds/userDocs
3 Statik Sorgu Sonucu Excel dosyası indirme linki

By Abdullah Gülabi

Sözleşme, iş süreçleri ve projelerin etkin yönetilmesine çaba harcayan bir mühendisim.

İletişimin, proje başarısında hayati bir unsur olduğunu düşünüyorum.

Leave a Reply

Your email address will not be published. Required fields are marked *