Screening firms based on historical listing status over a certain period

Hello Guys,

for a research project I need US firms’ fundamental financial data (BS, CF and P&L). Since usually Compustat North America database is used for this purpose I am seeking to meet the commonly used sample as accurate as possible.

Retrieving the accounting data is straight forward, however, I face difficulties regarding the screen.

For retrieving the accounting data, I need a list of firms (RIC or PermID) which have or had been listed on US exchanges during a specific period (e.g. 1990 until 2020) including the information in which years they were listed.

I try to extract the list requesting the Eikon API using python based on the following general code:

df, e = ek.get_data(syntax, fields)
df

syntax = 'SCREEN(U(IN(Equity(primary,public,active))),IN(TR.ExchangeCountry,"United States of America"), CURN=USD)'

fields = ['TR.RIC','TR.CommonName','TR.OrganizationID']

The default SCREEN search term (primary,public,active) faces the following problems:

• Organisation Type: public vs. private
  • Using the company search leads to incomplete or exhaustive results because limiting the search to public firms only refers to their current listing status (missing firms which are delisted again). Otherwise, private firms cover too many firms especially including firms which have never been listed.
• Active vs. inactive
  • Former active firms might have a changed status to be currently set as inactive and are missed as well.

Additionaly, I am not aware of any item indicating the listing status for a specific fiscal year for the requested fields.

Could you please have a look on this problem? Currently, the screener app does not work within the desktop app nor does it with excel, which limits the possibilities as well. I would be very grateful for any advice!

Best Regards and many thanks in advance!