February 5, 2007 at 1:55 pm
For those not familiar with the OFAC (Office of Foreign Assets Control), they maintain a list of Specially Designated Nationals (SDN) in ASCII and now XML with an XSD layout that financial institutions check against.
This list is updated aperiodically (I've only seen additions, never deletions or updates).
I am wanting to automate the updating of this list into a SQL table.
To view the XML/XSD go here: http://www.treas.gov/offices/enforcement/ofac/sdn/index.shtml
Unfortunately, they do not provide a webservice type of interface, simply the XSD/XML.
Originally, I manually created the SQL table and then used DTS to import the data. Since they can update the XSD, I was looking to automate this process a bit more. Has anyone already tackled this task in a more automated fashion that would be willing to share their methodology?
February 8, 2007 at 8:00 am
This was removed by the editor as SPAM
December 12, 2007 at 2:30 pm
my name is Pablo Cecere.
I have to deal with the OFAC list to import it to SQL Server.
If you didnt do it yet, we can work together.
Pablo
December 12, 2007 at 4:31 pm
Pablo,
I posted a query on Guru.com and found someone that had already written a C# app to import the XML data into SQL. They gave me a very reasonable quote and it works very well. For the time it saved, its a no-brainer.
His contact info if you want: Mark Gerlach [mark AT efgtech DOT com]
(modified email for those nasty spam bots)
January 2, 2008 at 10:24 am
I tried contacting this guy, but he didnt answer my emails.
Can you try to contact him? Or help me get this ?
Thanks a lot
Pablo
Happy New Year
January 2, 2008 at 10:27 am
I will send him an email with a link to this forum posting and ask him to contact you.
January 3, 2008 at 10:48 am
Tim, after importing the list ...
did you programmed a Stored Procedure or something to match the names?
Do you match exact names, or did something different?
Thanks
Pablo
Happy New Year!!
January 3, 2008 at 11:03 am
The OFAC data is imported from the XML file the USTD provides to its own database (multiple tables). The import program drops/recreates the tables each time.
The main identity table (sdnentry) has a unique ID that stays the same from version to version from the USTD.
I have a separate table to maintain the one to many relationship between our clients and the potential matches. So each time the OFAC data is updated and we compare against our current client list, we know which ones were potential matches before and only need to check/verify any new matches. I actually run the check once each day as one of our daily processes. This compares any new clients that were added during the day.
As for what I use to compare - the data in the OFAC list is 'ugly' to be polite, so doing a straight string compare would miss anything unless they matched exactly. So, I use a simply SOUNDEX methodology. Nothing special - but a heck of a lot better than what the USTD/SEC websites provide.
To do the SOUNDEX comparison, I use the TSQL DIFFERENCE function with a value of 4. I have found that gets any matches that are close and accounts for the most common english misspellings in names. Its not sophisticated, but it works.
I did find a commercial library (no longer have the name) that worked exceptionally well- but they wanted a huge sum to license (over 25k min) and it was very restrictive in its use.
January 5, 2008 at 3:38 pm
I am working on the import process. Hopefully finished by tomorrow.
Do you want to see the way I am doing it.
It is a stored procedure that checks if the SDN list has been updated, and the imports the new list if necessary.
Interested?
January 5, 2008 at 9:04 pm
I appreciate the offer, but no need - our methdology is customized to our needs and works well.
January 5, 2008 at 10:32 pm
Can I ask where you work? Or at least the kind of company?
Thanks>
Pablo
January 6, 2008 at 5:50 am
A financial services company in Atlanta, GA (The Henssler Financial Group).
January 9, 2008 at 7:53 am
Tim, one last doubt ...
when I have a new transaction and I have to check the customer against SDN, I have a lot of cases with the same SOUNDEX result, and that has nothing to do with the name i am searching.
So I need to do something else in order to decide if I got a match ...
what else are u doing?
January 9, 2008 at 8:00 am
SOUNDEX of course is far from exact - Joe Smith will match Jose Smithers, etc.
The first time the comparison was run, we had a few dozen false positives.
That is why we maintain a table of the 'potential matches', so they can be excluded by our daily job that does the compare. This table also maintains who checked it and when, a column indicated if a match/false positive and any comments.
Once the initial compare is run and the false positives identified, subsequent runs (we run the job daily) only return results when:
1) The SDN is updated
2) We add a new client
The numbers for us in the above two instances, are very small.
This downside also has an upside, in that you know that if the names are even close, you will get a notification and it provides a great audit trail for the SEC.
This is also why we could not use any of the existing services. They have no methodology to know that you have already checked Joe Smith against that specific SDN entry for Jose Smithers and it is NOT a match. All existing products/web services are new searches/compares each time. By maintaining our own results/compares, we can run the comparison against our entire client list each time there are changes to the SDN and for each new client. Our auditors love it.
January 9, 2008 at 8:08 am
sounds good, thanks!
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply