Excel ile TCMB Döviz 💵💷💶 Kurları ve Power Query ❤️

Nisan 2024 sonrası EVDS sisteminin HTTP request alanına key talep eden güncellemesine uyumlu şekilde 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.

2024 Güncellemesi

TCMB’nin EVDS2 sistemi üzerinde yaptığı güncelleme ile, bu yazının orijinal hali geçerliliğini yitirmişti. Sevgili Emrah‘ın hatırlatması üzerine, Sn. İbrahim Yeğit‘in LinkedIn paylaşımını ve EVDS kullanıcı dokümanları sayfalarından faydalanarak, hepsini bir araya getirip, dosyayı tekrar çalışır hale kavuşturduk. Paylaşımı makalenin sonuna iliştirdim.

• API anahtarını Excel içinde bir adlandırılmış aralık olarak tanımadık
• Power Query sorgusunun ilk kısımında tanımlama ile Anahtarı excel içeriğinden okuduk
• XML tablosunu sunucudan isterken, API anahtarını HTTP request header içerisine yerleştirdik
• Bazı kozmetik düzenlemeler. Örnek dosyayı indirip, PowerQuery düzenleyici üzerinden Gelişmiş düzenleyici penceresi açtığınızda, aşağıdaki kodu görebilirsiniz.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

let 
    //Öncelikle Excel'de formül ile birleştirdiğimiz, XMLKaynak adını taşıyan hücreyi tanımlayalım
     DinamikKaynak= Excel.CurrentWorkbook(){[Name="XMLKaynak"]}[Content][Column1]{0},
    //APIkey olarak adlandırdığımız hücreyi tanımlayalım
    Anahtar= Excel.CurrentWorkbook(){[Name="APIkey"]}[Content][Column1]{0},
    
    //DinamikKaynak adresine git, giderken Anahtar kullan, XML tablosu getir
    Kaynak = Xml.Tables(Web.Contents(DinamikKaynak, [Headers=[key=Anahtar]])),
    
    TürünüDeğiştir1= Table.TransformColumnTypes(Kaynak,{{"totalCount", Int64.Type}}),
    
    items = TürünüDeğiştir1 {0}[items],
    
    TürünüDeğiştir2 = Table.TransformColumnTypes(items,{{"Tarih", type text}, {"TP_DK_USD_A_EF_YTL", type number}, {"TP_DK_USD_S_EF_YTL", type number}, {"TP_DK_EUR_A_EF_YTL", type number}, {"TP_DK_EUR_S_EF_YTL", type number}, {"TP_DK_GBP_A_EF_YTL", type number}, {"TP_DK_GBP_S_EF_YTL", type number}}),
 
	//Haftasonu veya tatil gibi boş satırlı günler için, bir önceki gün kapanış kurunu kullan.
    AşağıDoldur = Table.FillDown(TürünüDeğiştir2,{"TP_DK_USD_A_EF_YTL", "TP_DK_USD_S_EF_YTL", "TP_DK_EUR_A_EF_YTL", "TP_DK_EUR_S_EF_YTL", "TP_DK_GBP_A_EF_YTL", "TP_DK_GBP_S_EF_YTL"}),
   
	//Tablo en başında boş satır var ise, örneğin 1 Ocak resmi tatil için, bir sonraki günü kullan.
    YukarıDoldur = Table.FillUp(AşağıDoldur,{"TP_DK_USD_A_EF_YTL", "TP_DK_USD_S_EF_YTL", "TP_DK_EUR_A_EF_YTL", "TP_DK_EUR_S_EF_YTL", "TP_DK_GBP_A_EF_YTL", "TP_DK_GBP_S_EF_YTL"}),
    BölgeAyarları = Table.TransformColumnTypes(YukarıDoldur, {{"Tarih", type date}}, "tr-TR"),
    Sırala1 = Table.Sort(BölgeAyarları,{{"Tarih", Order.Ascending}}),
    Sütunİsimleri = Table.RenameColumns(Sırala1,{{"TP_DK_USD_A_EF_YTL", "USD Alış"}, {"TP_DK_USD_S_EF_YTL", "USD Satış"}, {"TP_DK_EUR_A_EF_YTL", "EUR Alış"}, {"TP_DK_EUR_S_EF_YTL", "EUR Satış"}, {"TP_DK_GBP_A_EF_YTL", "GBP Alış"}, {"TP_DK_GBP_S_EF_YTL", "GBP Satış"}}),
    Sırala2 = Table.Sort(Sütunİsimleri,{{"Tarih", Order.Descending}}),
    SütunSil = Table.RemoveColumns(Sırala2,{"UNIXTIME"})
in
    SütunSil

Yazının devamı, eskiden yayınlandığı gibidir. Geçersiz kısımlar silindi.

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.

Örnek Dosya

Örnek dosyaya ve diğer tüm Excel paylaşımlarıma github bağlantımdan erişebilirsiniz. TCMB.xlsx

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.

İ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 ((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 eklidi
      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ığı?

XML sorgusunu oluşturacağız. Yardımcı evrak kısmında detayları verildiği üzere XML sorgu adresini oluşturmamız gerekiyor.

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:

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

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:

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.

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.

Soru - Cevap

Soner A., 24 Haziran 2021’de sordu:

Hepsi çok güzel ama XML oluşmuyor. Sizin sayfanızda paylaşmış olduğunuz Excel içerisinde de XML oluşmuyor sanırım “https://evds2.tcmb.gov.tr/service/evds/series=” kaynağı değişmiş olabilir. Excel 2016 kullanıyorum.

Dosya içerisindeki örneği yaparken TCMB EVDS üzerinden kendi API anahtarınız ile işlem yapmanız gerekiyor, büyük ihtimal ile örnek dosyada yazan API geçersiz durumda olabilir.

Alperen 12 Ağustos 2021’de sordu:

Merhaba, Çok güzel paylaşım emeğinize sağlık, sormak istediğim konu 2019, 2020,2021 yıllık ortalama usd satış kuru çekmek istiyorum evds den, 2021 yenilenince yeni veri gelsin istiyorum bu işlem için hangi yolu izlemem gerekiyor acaba? Teşekkür eder, kolaylıklar dilerim.

Veritabanında sorgu oluştururken başlangıç ve bitiş tarihlerini düzenleyerek istediğiniz aralığı tanımlayabilirsiniz.

Tanımladığınız dönemin ortalamasını dilerseniz PowerQuery içinde son adım olarak tanımlayıp sonuç olarak sadece ortalama değeri yazdırabilir veya tüm aralığı örnekte olduğu gibi Excel işine alıp, ayrı bir kenarda ortalamayı hesaplayabilirsiniz.

Yıllar için ayrı ayrı ortalama için toplam dönem verisini alıp Excel içinde SUMIFS veya AVERAGEIFS formüller ile hesaplayabilirsiniz.

Elbette her dönem için farklı sorgu oluşturup ayrı ayrı ortalama hesaplayıp yazdırmak da mümkün. Eğer dosya boyutu veya performans konusunda sıkıntı olmuyor ise AVERAGEIFS yöntemini tavsiye ederim.

Teşekkür

Ibrahim Yegit’in Nisan 2024 Paylaşımı