Hero Image
- Abdullah Gulabi

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.

├ľrnek Dosya

PQ1.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.

Other Related Posts:

Excel ile Yuvarlama ─░┼člemleri

G├╝ndelik hayatta pek ├žok durumda yuvarlama i┼člemleri yapmam─▒z gerekiyor. Belirli bir say─▒da ondal─▒k hanesi ile ├žal─▒┼čmak, k├╝suratlardan kurtulmak, devreden bakiyeler olmas─▒ halinde s─▒k─▒nt─▒ ya┼čamamak gibi pek ├žok uygulama olabilir.

6th Mar 2016 - Abdullah Gulabi