Katelynn's Report

Katelynn's Report

(US Market)

Help










Font Size:

ID mappings

Symbol to CIK mapping

Map trading symbol to the most up-to-date central index key (CIK). CIK is a ten digits number maintained by the SEC to identify a person or company. CIK might be reused to identify totally unrelated different entities.

CREATE TABLE `symciks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`cik` int(11) NOT NULL,
`recdate` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_symciks_on_symbol_and_cik` (`symbol`,`cik`),
KEY `index_symciks_on_cik` (`cik`),
KEY `index_symciks_on_recdate` (`recdate`)
) ENGINE=InnoDB AUTO_INCREMENT=3353806 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

symbol: The trading symbol.

cik: The most up-to-date central index key.

recdate: The date when the mapping was generated.


Standard industry classification

Map standard industry classification (SIC) code to industry name.

CREATE TABLE `sics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int(11) DEFAULT NULL,
`sic_industry` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_sics_on_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=450 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

code: SIC code.

sic_industry: Industry name.


CIK to company information mapping

Map CIK to the most up-to-date company information.

CREATE TABLE `compadrs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cik` int(11) NOT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`sic` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`irs` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`fisc` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
`adrone` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
`adrtwo` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`state` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`zip` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`recdate` date DEFAULT NULL,
`stateofincorp` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`mailingadr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`cikname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_compadrs_on_cik` (`cik`),
KEY `index_compadrs_on_sic` (`sic`),
KEY `index_compadrs_on_irs` (`irs`),
KEY `index_compadrs_on_state` (`state`)
) ENGINE=InnoDB AUTO_INCREMENT=45446043 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

cik: The central index key.

name: Name of the company or person.

sic: Standard industry classification code, which can be mapped to industry name through sics table.

irs: Internal revenue service number.

fisc: Fiscal year end.

adrone: Address line one.

adrtwo: Address line two.

city: Self-explanatory.

state: Either state name if in the United States, or country name if outside of the US.

zip: Self-explanatory.

phone: Self-explanatory.

recdate: The date when the record was generated or last updated.


GSTIDSEC to FIGI mapping

Map gstidsec to openFIGI id.

CREATE TABLE `gstidsec2figis` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gstidsec` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`figi` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`compositefigi` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`shareclassfigi` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`uniqueid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`exchcode` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`ticker` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`marketsector` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`securitytype` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_gstidsec2figis_on_gstidsec` (`gstidsec`),
UNIQUE KEY `index_gstidsec2figis_on_figi` (`figi`),
KEY `index_gstidsec2figis_on_compositefigi` (`compositefigi`),
KEY `index_gstidsec2figis_on_shareclassfigi` (`shareclassfigi`),
KEY `index_gstidsec2figis_on_uniqueid` (`uniqueid`),
KEY `index_gstidsec2figis_on_exchcode` (`exchcode`),
KEY `index_gstidsec2figis_on_ticker` (`ticker`),
KEY `index_gstidsec2figis_on_marketsector` (`marketsector`),
KEY `index_gstidsec2figis_on_securitytype` (`securitytype`)
) ENGINE=InnoDB AUTO_INCREMENT=197944584 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

gstidsec: See gstidsec definition in gstidanno table.

figi: Financial Instrument Global Identifier (FIGI) (formerly Bloomberg Global Identifier (BBGID)) is an open standard, unique identifier of financial instruments that can be issued to instruments including common stock, options, derivatives, futures, corporate and government bonds, municipals, currencies, and mortgage products.

compositefigi:

shareclassfigi: FIGI for identification of security classes.

uniqueid:

exchcode: Code of the security exchange.

name: Name of the security issuer.

ticker: Trading ticker.

marketsector: Identify whether the security is an equity, corporate/government/municipal bond, or mortgage.

securitytype: Identify the detailed security type. E.g. common stock, open-end fund, closed-end fund, ADR, and et ac.


GSTID annotation

Map gstidcpn to issuer name and gstidsec.

CREATE TABLE `gstidanno` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gstidcpn` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
`gstidsec` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`sectype` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_gstidsec` (`gstidsec`),
KEY `index_gstidcpn` (`gstidcpn`)
) ENGINE=InnoDB AUTO_INCREMENT=24356435 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

gstidcpn: Global STatistical analysis IDentification for ComPany Name (or security issuer), a self maintained identification system for unique identification of companies in the United States (can be partially mapped to OpenFIGI for US companies), mainland China, and Hongkong. GSTIDCPN is a 14-character string with the first two characters the country code (e.g. "US","CN","HK"), followed by "CPN", and nine alphanumeric characters.

name: The name of company or security issuer.

gstidsec: Global STatistical analysis IDentification for SECurity, a self maintained identification system for unique identification of securities traded in the United States, or mainland China, or Hongkong. GSTIDSEC is a 14 character string with the first two characters the country code (e.g. "US","CN","HK"), followed by "SEC", and nine alphanumeric characters.

sectype: The security type.


Industry code to NAICS and industry name

Map internal industry code to NAICS code as well as industry name.

CREATE TABLE `idscode2naics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`idscode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`naics` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`idsname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_idscode2naics_on_idscode` (`idscode`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

idscode: Internal industry code used throughout the dataportal to represent industry classification.

naics: North american industry classification system.

idsname: Name of the industry.


Sector code to sector name

Map internal sector code to sector name.

CREATE TABLE `seccode2sectors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`seccode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`secname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_seccode2sectors_on_seccode` (`seccode`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

seccode: Internal sector code used throughout the dataportal to represent sector classification.

secname: Name of the sector.