Dataportal Access Instruction

Dataportal access (Amazon EC2 infrastructure) is available to Tier 3 (contact us for upgrade to Tier 3) or above users, who will be granted "SELECT" privilege (with X509 certificate authentication) to tables in corresponding tier. Once upgrade to Tier 3 or above is finished, a MySQL password will be generated and available in "My Account" page, together with links to certificate files which are required for establishing dataportal connection.

Before continuing, please make sure your internet connection to port 3306 of our host is not blocked by firewall (either a local one or at the country level). If you can see this webpage, most likely you are good. A more secure way is to run nc -zv 54.69.33.110 3306 and make sure you see something like Connection to 54.69.33.110 port 3306 [tcp/mysql] succeeded!.

The codes below show connections through various approaches

Access through MySQL Client

mysql -u registration_email_address -p${your_password} -h 54.69.33.110 -D strestat_production --ssl-key /path/client-key.pem --ssl-cert /path/client-cert.pem

Note there is no space between -p and ${your_password}
This is the basic type of connection you should try before moving to other connection interfaces.
If you have purchased Tier 3 or above access but have difficulty in establishing connection, please contact customer service immediately.

Access through Perl

use DBI;
my $dbh = DBI->connect(
"dbi:mysql:database=strestat_production;host=54.69.33.110;mysql_ssl=1;mysql_ssl_client_key=${path_to_client_key};mysql_ssl_client_cert=${path_to_client_cert};",
registration_email_address, your_password);

Note there should be no newline in the first argument

You need to install DBI if it is not installed yet

Download Perl package DBI from http://search.cpan.org/~timb/DBI-1.634/DBI.pm and complete the installation.
Note if you don't have access to the default directory, use perl Makefile.PL PREFIX="custom_path" instead.
Include the module's path to perl's library path in ~/.bashrc (ubuntu)

export PERL5LIB=$PERL5LIB:custom_path

Access through R

First create a file called "config.tmp", or whatever name you like. Input the following lines into the file.

[client]
ssl-ca=/path/ca-cert.pem
ssl-cert=/path/client-cert.pem
ssl-key=/path/client-key.pem

#run install.packages("RMySQL") and install.packages("DBI") first
require("RMySQL");
mydb = dbConnect(MySQL(), user='registration_email_address', password='your_password', dbname='strestat_production', host='54.69.33.110', default.file='/path/config.tmp');

RMySQL can also be installed by downloading R package from https://cran.r-project.org/web/packages/RMySQL/index.html and complete the installation with
R CMD INSTALL -l path_to_your_R_library RMySQL_xx.xx.tar.gz

Examples on data retrieval

Users with dataportal access can retrieve financial metrics either from prepared data tables, or if not available, from low-level data (e.g. XBRL-minisql) and do the calculation by themselves. Examples in the following sections assume connection to the dataportal has been successfully established

Example 1: retrieve liabilities, shareholder's equity, and shares outstanding of AAPL (Apple Inc.) from 2013 to 2016 (require Tier 3 or above) form x_balancesheet, using MySQL client.
We need to first find out the CIK that AAPL used during that time period. This is not always necessary, but it will make the search more accurate since same ticker might be used by different companies over time.

mysql -u username -pxxxx -D strestat_production -e "SELECT * FROM symciks WHERE symbol='AAPL'";

The query shows AAPL has only one CIK (320193) as of this writing. In this case we can query the CIK or just query the ticker name (although less recommended)

mysql xxxx -e "SELECT period,liabilities,shareholdersequity,shares_outstanding FROM x_balancesheet WHERE symbol='AAPL' AND period >='2013-01-01' AND period <='2016-12-31'" > aapl_balance.txt;

The data in x_balancesheet table are extracted from XBRL-minisql by our accounting and informatics specialists. Users (Tier 3 or above) are opt to obtain the information directly from XBRL-minisql, as shown in the example below.

Example 2: retrieve shares outstanding of AAPL from XBRL-minisql (Tier 3 or above). This can only be achieved by joint query on several tables.

mysql xxxx -e "SELECT xbrls.fdate,xbrls.periodenddate,xbrls.symbol,xbrls.cik,xbrldts.numericvalue FROM xbrls INNER JOIN xbrlpres ON xbrls.zipfilename=xbrlpres.zipfilename LEFT JOIN xbrldts ON xbrlpres.zipfilename=xbrldts.zipfilename AND xbrlpres.tolabel=xbrldts.tolabel WHERE symbol='AAPL' AND cik=320193 AND xbrlpres.tolabel='dei_EntityCommonStockSharesOutstanding'" > aapl_shares_outstanding.txt

An alternative way is to query x_so table (Tier 3 or above), which contains shares outstanding information curated by our accounting and informatics specialists from XBRL-minisql.

mysql xxxx -e "SELECT * FROM x_so WHERE cik=320193" > aapl_shares_outstanding_x_so.txt

Since AAPL does not have different classes of stock, it is relatively easy to retrieve its shares outstanding information.

Example 3: retrieve shares outstanding of GOOG (Alphabet Inc. previously Google Inc.) from XBRL-minisql. Notice that GOOG has three classes of stocks as of this writing.
The additional informatino is stored in xbrldts.metadata field, which in this case contains the class of security. Also, we noticed from symciks table that GOOG mapped to different CIKs (due to name change from Google. Inc. [1288776] to Alphabet. Inc. [1652044])

mysql xxxx -e "SELECT xbrls.fdate,xbrls.periodenddate,xbrls.symbol,xbrls.cik,xbrldts.numericvalue,xbrldts.metadata FROM xbrls INNER JOIN xbrlpres ON xbrls.zipfilename=xbrlpres.zipfilename LEFT JOIN xbrldts ON xbrlpres.zipfilename=xbrldts.zipfilename AND xbrlpres.tolabel=xbrldts.tolabel WHERE cik in (1288776,1652044) AND xbrlpres.tolabel='dei_EntityCommonStockSharesOutstanding' AND xbrlpres.xmlpart='PRE' ORDER BY periodenddate"; > goog_shares_outstanding.txt

Note there are some duplicated items since GOOG filed under both CIKs for period 2015-09-30 and 2015-12-31

An alternative way is to query x_so table (Tier 3 or above), which contains shares outstanding information curated by our accounting and informatics specialists from XBRL-minisql.

mysql xxxx -e "SELECT * FROM x_so WHERE cik in (1288776,1652044)" > goog_shares_outstanding_x_so.txt

Example 4: retrieve insider trading of GOOG from 2016-01-01 to 2016-01-05 (Tier 3 or above)

mysql xxxx -e "SELECT * FROM form345s WHERE icik IN (1288776,1652044) AND fdate >= '2016-01-01' AND fdate <= '2016-01-05'" > goog_insider.txt

Example 5: Retrieve sector level IVC holding between 2010 and 2016 (Tier 4).

mysql xxxx -e "SELECT period,ivcsechlds.seccode,seccode2sectors.secname,sum(value) AS tvalue FROM ivcsechlds LEFT JOIN seccode2sectors ON ivcsechlds.seccode = seccode2sectors.seccode WHERE period>='2010-01-01' AND period <='2016-12-31' GROUP BY period,seccode" > ivcsechld_2010_2016.txt