Excel Fuzzy Lookup ile OFAC Yaptırım Listesinde Bulunan Müşterilerin Tespiti

ofac_logoBir önceki yazımda bahsettiğim Excel Fuzzy Lookup eklentisi ile, şirketinize ait müşterilerin, bankalar tarafından sıklıkla kullanılan ve ABD Hazine Bakanlığı’na bağlı olan OFAC (Office of Foreign Assets Control) tarafından yayınlanan yaptırım listesinde bulunan kişi ve kurumlarla eşleştirilmesi örneğini ele alacağım. Sonuç olarak yaptırım listesinde bulunan kayıtlarla benzerlik gösteren müşterilerin kimler olduğunu görmüş olacağız.

 YÖNERGELER

1 – Tekil müşteri eşleşmeleri için OFAC Sanctions List Search web sayfası kullanılabilir. Ayrıca, aşağıda bahsedilecek yöntemin sonuçları da bu sayfa üzerinden doğrulanmalıdır (bu yöntem ile daha düşük benzerlik oranlarında da arama yapılabildiği için, bazı sonuçların OFAC web sitesinde bulunmaması doğaldır).

https://sdnsearch.ofac.treas.gov

 2 – Güncel OFAC Specially Designated Nationals List (SDN) dokümanı, aşağıdaki adresten bilgisayara yüklenmelidir (kaydedilecek dosya adı : SDN.xml)

Dosyanın geçerlilik tarihi web sitesinde belirtilmektedir. Dosya bir kez yüklendikten sonra, OFAC tarafından yeni bir dosya yayınlanana kadar mevcut dosya ile çalışılabilecektir.

Tüm biçimlere ait dosya listesi :

http://www.treasury.gov/resource-center/sanctions/SDN-List/Pages/default.aspx

Xml biçimindeki SDN.xml dosya bağlantısı:

http://www.treasury.gov/ofac/downloads/sdn.xml

 3 – Eşleştirmelerde kullanılacak olan Microsoft Fuzzy Lookup Add-In for Excel uygulaması, aşağıdaki adresten bilgisayara yüklenmelidir.

https://www.microsoft.com/en-us/download/details.aspx?id=15011

4 – Aşağıda ekli bulunan ilgili Excel dosyaları açılarak bilgisayara kaydedilir. Örnek veri içeren ikinci doküman ile testler yapılmak istenmesi durumunda; madde 5 ve 6 atlanarak, doğrudan madde 7’ye geçilebilir.

Customer_SDN_Match

Customer_SDN_Match_SAMPLE_DATA

5-  Müşteri – SDN Eşleştirme Excel dokümanı açılarak, Customer List çalışma sayfası güncel müşteri listesi ile doldurulmalıdır.

Bireysel müşteriler için ad – soyad bilgileri First Name ve Last Name / Company Name kolonlarında, kurumsal müşteriler için ünvan bilgisi ise Last Name / Company Name kolonunda yer almalıdır. Customer Id kolonu her müşteri için dolu olmalıdır.

 Kopyala – yapıştır ile eklenecek  müşteri listesi, otomatik olarak mavi – beyaz renklerle işaretlenecektir (yani bir tablo formatında olacaktır). İşaretleme yapılmayan müşteri kayıtları, eşleştirme işlemine dahil olmayacaktır.

ofac_sdn_match_5_1

Tablo biçiminin (mavi – beyaz renklerin) kaybolması durumunda, müşteri verisinin olduğu hücreler seçilerek :

INSERT -> Table

menüsünden bu alanın bir tablo olarak işaretlenmesi sağlanmalıdır.

ofac_sdn_match_5_2

6 – Excel üzerindeki SDN List çalışma sayfası, madde 2’de yüklenen SDN.xml dokümanı ile doldurulmalıdır.

SDN List çalışma sayfası açılarak, A1 hücresi seçilir.

Data -> From Other Sources -> From XML Data Import

menüsünden, SDN.xml dokümanı seçilir ve aktarım tamamlanır.

ofac_sdn_match_6_1

ofac_sdn_match_6_2

7 – Result çalışma sayfası açılarak Fuzzy Lookup -> Fuzzy Lookup menüsünden eklenti çalıştırılır.

ofac_sdn_match_7_1

ofac_sdn_match_7_2

 8 – Açılan Fuzzy Lookup ayarlarından, Left Table için müşteri listesinin bulunduğu ve Right Table için SDN listesinin bulunduğu ilgili tablolar seçilir.

ofac_sdn_match_8_1

Sol kolonlardan First Name, sağ kolonlardan firstName ibaresini içeren tüm satırlar ctrl tuşuna basılı tutularak seçilir ve ortadaki eşleştirme butonuna basılır.

ofac_sdn_match_8_2

Sol kolonlardan Last Name / Company Name, sağ kolonlardan lastName ibaresi içeren tüm satırlar ctrl tuşuna basılı tutularak seçilir ve ortadaki eşleştirme butonuna basılır.

ofac_sdn_match_8_3

Eşleşecek kolonlar aşağıdaki şekilde görünecektir.

ofac_sdn_match_8_4

Bir müşteri için en fazla kaç eşleşme bulunacağı bilgisi (Number of Matches) değeri girilerek, Similarity Threshold değeri belirlenir. Bu değer eşleşmenin  benzerlik oranını belirlemekte; 0 için minimum, 1 için maksimum benzerlik oranına sahip kayıtlar getirilmektedir.

ofac_sdn_match_8_5

A1 hücresi seçili konumda iken Go butonuna basılır. İşlem kayıt sayısı oranında uzun sürecektir.

Sonuç olarak her müşteri için benzerlik bulunan kayıtlar, müşteri kaydının yanında yer alacaktır. SDN Listesinde benzerliği bulunmayan kayıtlar için ns1:uid kolonu boş olacaktır.

ofac_sdn_match_8_6

Yeni bir Similarity Threshold değeri denemek için, sayfa temizlenip imleç A1 konumuna getirilmeli ve tekrar Go butonuna basılmalıdır.

Excel Fuzzy Lookup ile OFAC Yaptırım Listesinde Bulunan Müşterilerin Tespiti” için bir yorum

  1. Geri bildirim: Excel ile Bulanık Mantık Kullanarak Veri Benzerliği Analizi | Mustafa Ulus'un Kişisel Blogu

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir