FINCEN Money Services List
This sample code reads in the Financial Crimes Enforcement Network’s list of money services businesses (names, address and services provided) across the US. You can use this list to locate and count how many check cashing businesses are in your area.
/*
David Schlangen
david.schlangen@lexisnexis.com
Financial Crimes Enforcement Network - List of Money Services businesses (names, address and services provided) across the US
Link to the website to pull the data from: http://www.fincen.gov/financial_institutions/msb/msbstateselector.html
1. Select All States, click OK.
2. Save the spreadsheet on your desktop.
3. Open the spreadsheet and scroll to the bottom. Delete the Legend rows so they are not included as rows in the DB:
* NEW KEY-Form 107 (effective on 1/2005)
A. issuer of traveler's checks
B. seller of traveler's checks
C. redeemer of traveler's checks
D. issuer of money orders
E. seller of money orders
F. redeemer of money orders
G. currency dealer or exchanger
H. check casher
I. money transmitter
4. Convert that Excel spreadsheet into CSV format
5. Spray the csv file onto thor, giving it the following logical file name:
thor400::in::fincen_money_services_businesses
Format: ASCII
Max Record Length: 8192
Separator: \,
Line Terminator: \n,\r,\r\n
Quote: "
5. After spraying the file, read it in using the following code
*/
import std;
record_layout := RECORD
string LEGAL_NAME;
string DBA_NAME;
string STREET_ADDRESS;
string CITY;
string STATE;
string ZIP;
string Services_Provided;
string STATE_CODES_WHERE_SERVICES_OFFERED;
string NUMBER_OF_BRANCHES;
string Auth_Sign_Date;
string Received_Date;
END;
d := DATASET('~thor400::in::fincen_money_services_businesses', record_layout, csv(quote('"'), heading(single)) )(legal_name<>'' or dba_name<>'');
OUTPUT(d);
record_layout2 := record
string LEGAL_NAME;
string DBA_NAME;
string STREET_ADDRESS;
string CITY;
string STATE;
string ZIP;
string Services_Provided;
string STATE_CODES_WHERE_SERVICES_OFFERED;
string NUMBER_OF_BRANCHES;
string Received_Date;
boolean issuer_of_travelers_checks;
boolean seller_of_travelers_checks;
boolean redeemer_of_travelers_checks;
boolean issuer_of_money_orders;
boolean seller_of_money_orders;
boolean redeemer_of_money_orders;
boolean currency_dealer_or_exchanger;
boolean check_casher;
boolean money_transmitter;
end;
p := project(d, transform(record_layout2,
self.issuer_of_travelers_checks := std.str.find(left.Services_Provided, 'A', 1) > 0;
self.seller_of_travelers_checks := std.str.find(left.Services_Provided, 'B', 1) > 0;
self.redeemer_of_travelers_checks := std.str.find(left.Services_Provided, 'C', 1) > 0;
self.issuer_of_money_orders := std.str.find(left.Services_Provided, 'D', 1) > 0;
self.seller_of_money_orders := std.str.find(left.Services_Provided, 'E', 1) > 0;
self.redeemer_of_money_orders := std.str.find(left.Services_Provided, 'F', 1) > 0;
self.currency_dealer_or_exchanger := std.str.find(left.Services_Provided, 'G', 1) > 0;
self.check_casher := std.str.find(left.Services_Provided, 'H', 1) > 0;
self.money_transmitter := std.str.find(left.Services_Provided, 'I', 1) > 0;
self := left));
output(enth(p, 100), named('sample_FINCEN_MSB_listings')); // sample of 100 random records
minnesota_listings := p(state='MN' or std.str.find(STATE_CODES_WHERE_SERVICES_OFFERED, 'MN', 1)>0);
output(minnesota_listings, named('sample_Minnesota_listings'));
output(count(minnesota_listings), named('count_Minnesota_listings'));
// create some summary stats about the data in the file
t := table(p, {total_records := count(group),
check_cashing_agencies := count(group, check_casher),
currency_dealers_in_minnesota := count(group, currency_dealer_or_exchanger and
(state='MN' or std.str.find(STATE_CODES_WHERE_SERVICES_OFFERED, 'MN', 1)>0 ) );
}, few);
output(t, named('summary_stats'));