Select US listed companies only (Compustat)

[Roy Mathijsen]

Hi all,

I would like to select only the firms (gvkey) in my dataset which are listed in the US (US listed companies, i.e. registered under the SEC). Does anyone know an unique identifier (from Compustat) to do this? Maybe based on this site: http://www.crsp.com/products/documen...ata-industrial. I would like to hear from you.

Roy

[Andrew Musau]

I would suggest that you present a few observations from this data set including US and non-US firms (about 10 observations will do). In this way, someone may find a way to distinguish these. Please use dataex for this. The way you frame your question requires that one be familiar with the Compustat database which significantly decreases your chances of making progress.

[Roy Mathijsen]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double(fyear exchg) str4 fic str10 cik str4 sic
"001166" 2002 104 "NLD" "0000351483" "3559"
"001166" 2003 104 "NLD" "0000351483" "3559"
"001166" 2004 104 "NLD" "0000351483" "3559"
"001166" 2005 104 "NLD" "0000351483" "3559"
"001166" 2006 104 "NLD" "0000351483" "3559"
"001166" 2007 104 "NLD" "0000351483" "3559"
"001166" 2008 104 "NLD" "0000351483" "3559"
"001166" 2009 104 "NLD" "0000351483" "3559"
"001166" 2010 104 "NLD" "0000351483" "3559"
"001166" 2011 104 "NLD" "0000351483" "3559"
"001166" 2012 104 "NLD" "0000351483" "3559"
"001166" 2013 104 "NLD" "0000351483" "3559"
"001166" 2014 104 "NLD" "0000351483" "3559"
"001166" 2015 104 "NLD" "0000351483" "3559"
"001166" 2016 104 "NLD" "0000351483" "3559"
"001166" 2017 104 "NLD" "0000351483" "3559"
"001491" 2002 262 "ISR" "0000005337" "2621"
"001491" 2003 262 "ISR" "0000005337" "2621"
"001491" 2004 262 "ISR" "0000005337" "2621"
"001491" 2005 262 "ISR" "0000005337" "2621"
"001491" 2006 262 "ISR" "0000005337" "2621"
"001491" 2007 262 "ISR" "0000005337" "2621"
"001491" 2008 262 "ISR" "0000005337" "2621"
"001491" 2009 262 "ISR" "0000005337" "2621"
"001491" 2010 262 "ISR" "0000005337" "2621"
"001491" 2011 262 "ISR" "0000005337" "2621"
"001491" 2012 262 "ISR" "0000005337" "2621"
"001491" 2013 262 "ISR" "0000005337" "2621"
"001491" 2014 262 "ISR" "0000005337" "2621"
"001491" 2015 262 "ISR" "0000005337" "2621"
"001491" 2016 262 "ISR" "0000005337" "2621"
"001491" 2017 262 "ISR" "0000005337" "2621"
"001855" 2002 203 "PHL" "0001575721" "1000"
"001855" 2003 203 "PHL" "0001575721" "1000"
"001855" 2004 203 "PHL" "0001575721" "1000"
"001855" 2005 203 "PHL" "0001575721" "1000"
"001855" 2006 203 "PHL" "0001575721" "1000"
"001855" 2007 203 "PHL" "0001575721" "1000"
"001855" 2008 203 "PHL" "0001575721" "1000"
"001855" 2009 203 "PHL" "0001575721" "1000"
"001855" 2010 203 "PHL" "0001575721" "1000"
"001855" 2011 203 "PHL" "0001575721" "1000"
"001855" 2012 203 "PHL" "0001575721" "1000"
"001855" 2013 203 "PHL" "0001575721" "1000"
"001855" 2014 203 "PHL" "0001575721" "1000"
"001855" 2015 203 "PHL" "0001575721" "1000"
"001855" 2016 203 "PHL" "0001575721" "1000"
"001855" 2017 203 "PHL" "0001575721" "1000"
"001932" 2002 194 "GBR" "0001303523" "2111"
"001932" 2003 194 "GBR" "0001303523" "2111"
"001932" 2004 194 "GBR" "0001303523" "2111"
"001932" 2005 194 "GBR" "0001303523" "2111"
"001932" 2006 194 "GBR" "0001303523" "2111"
"001932" 2007 194 "GBR" "0001303523" "2111"
"001932" 2008 194 "GBR" "0001303523" "2111"
"001932" 2009 194 "GBR" "0001303523" "2111"
"001932" 2010 194 "GBR" "0001303523" "2111"
"001932" 2011 194 "GBR" "0001303523" "2111"
"001932" 2012 194 "GBR" "0001303523" "2111"
"001932" 2013 194 "GBR" "0001303523" "2111"
"001932" 2014 194 "GBR" "0001303523" "2111"
"001932" 2015 194 "GBR" "0001303523" "2111"
"001932" 2016 194 "GBR" "0001303523" "2111"
"001932" 2017 194 "GBR" "0001303523" "2111"
"002162" 2002 203 "PHL" "0000011290" "1000"
"002162" 2003 203 "PHL" "0000011290" "1000"
"002162" 2004 203 "PHL" "0000011290" "1000"
"002162" 2005 203 "PHL" "0000011290" "1000"
"002162" 2006 203 "PHL" "0000011290" "1000"
"002162" 2007 203 "PHL" "0000011290" "1000"
"002162" 2008 203 "PHL" "0000011290" "1000"
"002162" 2009 203 "PHL" "0000011290" "1000"
"002162" 2010 203 "PHL" "0000011290" "1000"
"002162" 2011 203 "PHL" "0000011290" "1000"
"002162" 2012 203 "PHL" "0000011290" "1000"
"002162" 2013 203 "PHL" "0000011290" "1000"
"002162" 2014 203 "PHL" "0000011290" "1000"
"002162" 2015 203 "PHL" "0000011290" "1000"
"002162" 2016 203 "PHL" "0000011290" "1000"
"002162" 2017 203 "PHL" "0000011290" "1000"
"002338" 2002 194 "GBR" "0000013522" "3411"
"002338" 2003 194 "GBR" "0000013522" "3411"
"002338" 2004 194 "GBR" "0000013522" "3411"
"002338" 2005 194 "GBR" "0000013522" "3411"
"002338" 2006 194 "GBR" "0000013522" "3411"
"002338" 2007 194 "GBR" "0000013522" "3411"
"002338" 2008 194 "GBR" "0000013522" "3411"
"002338" 2009 194 "GBR" "0000013522" "3411"
"002338" 2010 194 "GBR" "0000013522" "3411"
"002338" 2011 194 "GBR" "0000013522" "3411"
"002338" 2012 194 "GBR" "0000013522" "3411"
"002338" 2013 194 "GBR" "0000013522" "3411"
"002338" 2014 194 "GBR" "0000013522" "3411"
"002410" 2002 194 "GBR" "0000313807" "2911"
"002410" 2003 194 "GBR" "0000313807" "2911"
"002410" 2004 194 "GBR" "0000313807" "2911"
"002410" 2005 194 "GBR" "0000313807" "2911"
"002410" 2006 194 "GBR" "0000313807" "2911"
"002410" 2007 194 "GBR" "0000313807" "2911"
"002410" 2008 194 "GBR" "0000313807" "2911"
end

[Andrew Musau]

Thanks for the example. There are two issues here

listed in the US (US listed companies, i.e. registered under the SEC)

Listed to me suggests that the firm is trading in an exchange, e.g., NYSE, Nasdaq, etc. From the following, it appears that a firm can file disclosures with the SEC but is not listed.

A Central Index Key or CIK number is a number given to an individual or company by the United States Securities and Exchange Commission. The number is used to identify the filings of a company, person, or entity in several online databases, including Compustat and EDGAR

Therefore, firms that have not filed disclosures with the SEC will not have a "cik" number. I do not see an obvious way to identify a firm listed in a US exchange from your variables.

[Roy Mathijsen]

Thank you for your response. The variable [exchg] means exchange code. Compustat's documentation indicates that this code runs from 0 to 20. However, as can be seen above, my code consists of 3 digits. Does anyone know where I can find the meaning of this 3-digit code. It looks like this in my dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double exchg
104
104
104
104
104
104
104
104
104
104
104
104
104
104
104
104
104
104
104
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
203
203
203
203
203
203
203
203
203
203
203
203
203
203
203
203
203
203
203
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
194
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
262
end

[Andrew Musau]

Thank you for your response. The variable [exchg] means exchange code. Compustat's documentation indicates that this code runs from 0 to 20.

Actually, you are correct - this is the variable that you need to identify a firm listed in a US exchange. From this link, you have that

 
(EXCHGCD)
(EXCHGDESC)
Country

0
Subsidiary/Private
Canada

0
Subsidiary/Private
United States

1
Non-traded Company or Security
Canada

1
Non-traded Company or Security
Germany

1
Non-traded Company or Security
Switzerland

1
Non-traded Company or Security
Taiwan

1
Non-traded Company or Security
United States

2
Consolidated Parent or Tracking Stock Company
United States

3
Leveraged Buyout
United States

4
Additional Company Record-PreSFAS 94, ProForma, PreAmended
United States

7
Toronto Stock Exchange
Canada

8
Montreal Stock Exchange
Canada

9
TSX Venture Exchange
Canada

10
Alberta Stock Exchange
Canada

11
New York Stock Exchange
United States

12
American SE
United States

13
OTC Bulletin Board
United States

14
NASDAQ-NMS Stock Market
United States

15
NASDAQ OMX Boston
United States

16
Midwest Exchange (Chicago)
United States

17
NYSE Arca
United States

18
Philadelphia Exchange
United States

19
Other-OTC
United States

20
Unlisted Evaluated Equity
United States

100
Accra
Ghana

101
Adelaide
Australia

102
Alien Market
Korea

103
Amman
Jordan

104
NYSE Euronext Amsterdam
Netherlands

105
Antwerp
Belgium

106
Australian Stock Exchange
Australia

107
Athens
Greece

108
Auckland
New Zealand

109
Autoquote
United Kingdom

110
Bangkok
Thailand

111
Bangkok Alien Market
Thailand

112
Barcelona
Spain

113
Basel
Switzerland

114
Beirut
Lebanon

115
Berlin
Germany

116
Berne
Switzerland

117
Bilbao
Spain

118
Bogota
Colombia

119
Bologna
Italy

120
Bombay
India

121
Bordeaux
France

122
Bratislava
Slovakia

123
Bremen
Germany

126
Brisbane
Australia

127
Broker
Argentina

127
Broker
Australia

127
Broker
Austria

127
Broker
Belgium

127
Broker
Botswana

127
Broker
Brazil

127
Broker
Chile

127
Broker
China

127
Broker
Colombia

127
Broker
Cyprus

127
Broker
Czech Republic

127
Broker
Denmark

127
Broker
Finland

127
Broker
France

127
Broker
Germany

127
Broker
Greece

127
Broker
Hong Kong

127
Broker
Hungary

127
Broker
India

127
Broker
Indonesia

127
Broker
Ireland

127
Broker
Israel

127
Broker
Italy

127
Broker
Jamaica

127
Broker
Japan

127
Broker
Jordan

127
Broker
Kenya

127
Broker
Luxembourg

127
Broker
Malaysia

127
Broker
Malta

127
Broker
Mauritius

127
Broker
Mexico

127
Broker
Morocco

127
Broker
Netherlands

127
Broker
New Zealand

127
Broker
Nigeria

127
Broker
Norway

127
Broker
Pakistan

127
Broker
Peru

127
Broker
Philippines

127
Broker
Poland

127
Broker
Portugal

127
Broker
Russia

127
Broker
Singapore

127
Broker
Slovakia

127
Broker
Slovenia

127
Broker
South Africa

127
Broker
South Korea

127
Broker
Spain

127
Broker
Sri Lanka

127
Broker
Sweden

127
Broker
Switzerland

127
Broker
Taiwan

127
Broker
Thailand

127
Broker
Tunisia

127
Broker
Tunisia

127
Broker
United Arab Emerates

127
Broker
United Kingdom

127
Broker
United States

127
Broker
Venezuela

127
Broker
Zambia

127
Broker
Zimbabwe

132
NYSE Euronext Brussels
Belgium

133
Bucharest
Romania

134
Budapest
Hungary

135
Buenos Aires
Argentina

136
Cairo
Egypt

137
Calcutta
India

138
Caracas
Venezuela

139
Casablanca
Morocco

141
CLOB Intl - Alien Market
Singapore

142
Colombo
Sri Lanka

144
Copenhagen
Denmark

145
Cordoba
Argentina

147
Delhi
India

148
Deutsche Terminborse (DTB)
Germany

149
Dusseldorf
Germany

150
Easdaq
Austria

150
Easdaq
Belgium

150
Easdaq
France

150
Easdaq
Ireland

150
Easdaq
Israel

150
Easdaq
Italy

150
Easdaq
Liberia

150
Easdaq
Netherlands

150
Easdaq
Spain

150
Easdaq
United Kingdom

151
SWX Swiss Exchange
Israel

151
SWX Swiss Exchange
Switzerland

152
Florence
Italy

153
Foreign Section
Japan

154
Frankfurt
Germany

156
Fukuoka
Japan

157
Fund Managers
Brazil

157
Fund Managers
India

157
Fund Managers
South Korea

157
Fund Managers
Taiwan

157
Fund Managers
Thailand

158
Gaborone
Botswana

159
Geneva
Switzerland

160
Genoa
Italy

161
Ghent
Belgium

162
Granville
United Kingdom

163
Hamburg
Germany

164
Hamilton
Bermuda

165
Hanover
Germany

166
Harare
Zimbabwe

167
Helsinki
Finland

169
Hobart
Australia

170
Hong Kong
Hong Kong

171
IBIS
Germany

172
Irish
Ireland

173
Smaller Companies Market
Ireland

174
Istanbul
Turkey

175
Indonesia Stock Exchange
Indonesia

176
Jakarta Alien Market
Indonesia

177
Johannesburg
South Africa

178
Karachi
Pakistan

179
Kiev
Ukraine

180
Kingston
Jamaica

181
Bursa Malaysia
Malaysia

183
Kyoto
Japan

184
La Paz
Bolivia

185
Lagos
Nigeria

186
Lausanne
Switzerland

187
Liberia
Liberia

188
Liege
Belgium

189
LIFFE/Options
United Kingdom

190
Lille
France

191
Lima
Peru

192
Lisbon
Portugal

193
Ljubljana
Slovenia

194
London
United Kingdom

197
Lusaka
Zambia

198
Luxembourg
Luxembourg

199
Lyon
France

200
Madras
India

201
Madrid
Spain

202
Makita
Philippines

203
Manila
Philippines

204
Marrakech
Morocco

205
Marseille
France

206
Matif
France

207
Melbourne
Australia

208
Mexico City Stock Exchange
Mexico

209
Milan
Italy

210
Montevideo
Uruguay

211
Moscow
Russia

212
Munich
Germany

213
Nagoya
Japan

214
Nairobi
Kenya

216
Nancy
France

217
Nantes
France

218
Naples
Italy

219
National Market
India

220
Neuchatel
Switzerland

221
Nicosia
Cyprus

224
Novosibirsk
Russia

225
NZSE National Market
New Zealand

226
OFEX
United Kingdom

227
Osaka
Japan

228
Oslo
Norway

229
OTC
Israel

229
OTC
Japan

229
OTC
Malaysia

229
OTC
Norway

229
OTC
Singapore

229
OTC
South Korea

229
OTC
Sweden

229
OTC
Taiwan

229
OTC
United States

229
OTC
Zimbabwe

230
Palermo
Italy

231
Paris
France

232
Perth
Australia

233
Port Louis
Mauritius

234
Porto
Portugal

235
Prague
Czech Republic

236
Riga
Latvia

237
Reykjavik
Iceland

238
Rio de Janeiro
Brazil

240
Rome
Italy

241
Russian Trading System
Russia

242
Santiago
Chile

243
BM and F Bovespa
Brazil

244
Sapporo
Japan

245
GreTai Securities Market
Taiwan

246
SEAQ International
United Kingdom

247
SEATS
United Kingdom

248
Seoul
South Korea

249
Shanghai
China

250
Shenzhen
China

251
Singapore
Singapore

252
Singapore Alien Market
Singapore

253
Sofia
Bulgaria

254
St. Gallen
Switzerland

255
St Petersburg
Russia

256
Stockholm
Sweden

257
Stuttgart
Germany

258
Surabaya
Indonesia

260
Taipei
Taiwan

261
Tallinn
Estonia

262
Tel Aviv
Israel

263
Third Market
United Kingdom

264
Tokyo
Japan

265
Trieste
Italy

266
Tunis
Tunisia

267
Turin
Italy

269
Unlisted Securities Market
Ireland

269
Unlisted Securities Market
United Kingdom

270
Valencia
Spain

271
Valletta
Malta

272
Venice
Italy

273
Vienna
Austria

274
Vilnius
Lithuania

275
Vladivostok
Russia

276
Warsaw
Poland

277
Wellington
New Zealand

278
Zagreb
Croatia

280
Zurich
Switzerland

282
International(London)
United Kingdom

283
Fund Manager
United Kingdom

283
Fund Manager
Luxembourg

285
Euro NM
Belgium

286
NYSE Euronext Paris
Finland

286
NYSE Euronext Paris
France

286
NYSE Euronext Paris
Israel

286
NYSE Euronext Paris
Italy

286
NYSE Euronext Paris
Netherlands

286
NYSE Euronext Paris
Norway

286
NYSE Euronext Paris
Spain

287
Neuer Markt
Germany

289
Nouveau Marche
France

290
Hors-Cote (OTC)
France

291
European Options Market
Luxembourg

292
NMAX (Nieuwe Markt)
Netherlands

293
Jasdaq
Japan

294
Euronext (Obsolete)
Belgium

294
Euronext (Obsolete)
France

294
Euronext (Obsolete)
Netherlands

295
RASDAQ
Romania

296
Nasdaq Japan
Japan

298
KOSDAQ
South Korea

299
Alexandria
Egypt

300
Kuwait
Kuwait

301
Muscat
Oman

303
TAISDAQ
Taiwan

304
MESDAQ
Malaysia

305
NOREX
Denmark

305
NOREX
Iceland

305
NOREX
Sweden

306
Riyadh
Saudi Arabia

307
Manama
Bahrain

308
Chittagong
Bangladesh

309
Abidjan
Cote d'Ivoire

310
Tehran
Iran

311
Windhoek
Namibia

312
Quito
Ecuador

313
Guayaquil
Ecuador

314
Dacca
Bangladesh

315
Doha
Qatar

316
Yaounde
Cameroon

317
Bourse Regionale Des Valeurs Mobilieres (BRVM)
Senegal

318
Abu Dhabi
United Arab Emirates

320
Unquoted Security
Morocco

322
Bermuda
United Kingdom

323
Dubai Financial Market
United Arab Emirates

325
Port of Spain
Trinidad and Tobago

326
OTOB Clearing Bank
Austria

327
Ho Chi Minh City
Vietnam

328
Hanoi Securities Trading Center
Vietnam

329
Almaty
Kazakhstan

330
WAEMU West African Economics MU
Ivory Coast

331
Nasdaq Dubai
United Arab Emirates

332
Moscow Interbank Currency Exchange
Russia

333
Belgrade Stock Exchange
Serbia

334
Ukrainian Stock Exchange
Ukraine

which means that you need

gen wanted = inrange(exchg, 11, 18)

[Phil Bromiley]

This is really a Compustat question, not a Stata question. In the WRDS Compustat interface, it let's you select by country. It will also output a country identifier. Alternatively, you can select to output the Headquarters ISO Country code and then keep if US.