Katelynn's Report

Katelynn's Report

(US Market)

Help










Font Size:

ETF(Exchange Traded Fund)

Dataportal for ETF is composed of several tables capturing various metrics (e.g. annual return, average return, expense over assets, portfolio structure...) which reflect the performance of each ETF.

formnqmain
formnqseriesinfo
formnqcontractinfo
formnqfundcatg
formnqcatgstrt
formnqcatgcostvalue
formnqsecurity

formnqmain

The parent table containing the main entries for Form N-Q filings

CREATE TABLE `formnqmain` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`filelink` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`conformedname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`cik` int(11) NOT NULL,
`period` date NOT NULL,
`fdate` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`filelink`),
UNIQUE KEY `index_2` (`cik`,`period`,`filelink`)
) ENGINE=InnoDB AUTO_INCREMENT=157623 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

filelink: Link to the original filing.

conformedname: Name of the issuer.

cik: Central index key of the issuer.

period: Period of report.

fdate: Filing date.


formnqseriesinfo

Child table of 'formnqmain'. It contains ETF series name and series id reported in each Form N-Q filing.

CREATE TABLE `formnqseriesinfo` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`mainid` int(11) unsigned NOT NULL,
`seriesname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`seriesid` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`seriesid`,`mainid`),
KEY `mainid` (`mainid`),
CONSTRAINT `formnqseriesinfo_ibfk_1` FOREIGN KEY (`mainid`) REFERENCES `formnqmain` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=636865 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

mainid: References to 'id' field of 'formnqmain'.

seriesname: ETF series name (e.g. systematex international fund).

seriesid: ETF series id (e.g. s000050840).


formnqcontractinfo

Child table of 'formnqseriesinfo'. It contains class name and class id for each ETF series.

CREATE TABLE `formnqcontractinfo` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`seriesinfoid` int(11) unsigned NOT NULL,
`contractname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`contractid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`contractsymbol` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `seriesinfoid` (`seriesinfoid`),
CONSTRAINT `formnqcontractinfo_ibfk_1` FOREIGN KEY (`seriesinfoid`) REFERENCES `formnqseriesinfo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1832275 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

seriesinfoid: References to 'id' field of 'formnqseriesinfo'.

contractname: Class name (e.g. class a).

contractid: Class ID (e.g. c000095454).

contractsymbol: Trading symbol (e.g. rndlx)


formnqfundcatg

Child table of 'formnqmain'. It contains the ETF fund name (i.e. series name) and associated categories of investment.

CREATE TABLE `formnqfundcatg` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`mainid` int(11) unsigned NOT NULL,
`fundname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`position` char(1) COLLATE utf8_unicode_ci NOT NULL,
`ord` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`fundname`,`category`,`position`,`mainid`),
KEY `mainid` (`mainid`),
CONSTRAINT `formnqfundcatg_ibfk_1` FOREIGN KEY (`mainid`) REFERENCES `formnqmain` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2210919 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

mainid: References to 'id' field of 'formnqmain'.

fundname: When the ETF issuer has several series, this field corresponds to series name and should have a mapping in 'seriesname' field of 'formnqseriesinfo'. When the ETF issuer has no series, this field corresponds to 'conformedname' of 'formnqmain'.

category: The category of investment, parsed from original unstructured Form N-Q / Form N-CSR documents.

position: Either 'L' for long position or 'S' for short position.

ord: The order of presentation of the categories in original filing.


formnqcatgstrt

Child table of 'formnqfundcatg'. It contains the sub structure of each categories in 'formnqfundcatg'.

CREATE TABLE `formnqcatgstrt` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`fundcatgid` int(11) unsigned NOT NULL,
`parent` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`child` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`parentpct` decimal(6,2) DEFAULT NULL,
`childpct` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`fundcatgid`,`parent`,`child`),
CONSTRAINT `formnqcatgstrt_ibfk_1` FOREIGN KEY (`fundcatgid`) REFERENCES `formnqfundcatg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17519579 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Field definition

fundcatgid: References to 'id' field of 'formnqfundcatg'.

parent: Parent category. If no parent category, string 'BLANK'.

child: Child category. Or the same as 'category' field of 'formnqfundcatg' when the category has no sub structure.

parentpct: Percentage of net assets of parent category.

childpct: Percentage of net assets of child category.


formnqcatgcostvalue

Child table of 'formnqmain'. It contains the cost and value of each category reported in each ETF series.

CREATE TABLE `formnqcatgcostvalue` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`mainid` int(11) unsigned NOT NULL,
`fundname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`ord` int(11) NOT NULL,
`cost` decimal(17,3) DEFAULT NULL,
`proceeds` decimal(17,3) DEFAULT NULL,
`value` decimal(17,3) DEFAULT NULL,
`negvalue` decimal(17,3) DEFAULT NULL,
`pct` decimal(6,2) DEFAULT NULL,
`negpct` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`fundname`,`category`,`mainid`),
KEY `mainid` (`mainid`),
CONSTRAINT `formnqcatgcostvalue_ibfk_1` FOREIGN KEY (`mainid`) REFERENCES `formnqmain` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5278027 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Field definition

mainid: References to 'id' field of 'formnqmain'.

fundname: ETF series name.

category: The category of investment.

ord: The order of presentation of the categories in original filing.

cost: Reported cost of the category of investment.

proceeds: Reported proceeds of the category of investment (short position).

value: Reported value of the category of investment.

negvalue: Reported value of the category of investment (short position).

pct: Percentage of the category of investment relative to net assets.

negpct: Percentage of the category of investment (short position) relative to net assets.


formnqsecurity

Child table of 'formnqcatgstrt'. It contains the securities included in each category of investment.

CREATE TABLE `formnqsecurity` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`catgstrtid` int(11) unsigned NOT NULL,
`security` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
`shares` decimal(17,3) DEFAULT NULL,
`value` decimal(17,3) DEFAULT NULL,
`principal` decimal(17,3) DEFAULT NULL,
`par` decimal(17,3) DEFAULT NULL,
`currency` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `catgstrtid` (`catgstrtid`),
CONSTRAINT `formnqsecurity_ibfk_1` FOREIGN KEY (`catgstrtid`) REFERENCES `formnqcatgstrt` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=148618797 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Field definition

catgstrtid: References to 'id' field of 'formnqcatgstrt'.

security: Security name.

shares: Reported number of shares of security.

value: Reported value of security

principle: Reported principle value of security. Usually applicable to bonds or notes.

par: Reported par value of security.

currency: Type of currency for the 'value' field (USD unless otherwise specified).