Installation and Deployment

Installation Guides

For installation instructions, please see the Installation tutorial for your platform.

Architecture

QuantRocket utilizes a Docker-based microservice architecture. Users who are unfamiliar with microservices or new to Docker may find it helpful to read the overview of QuantRocket's architecture.

License key

Activation

To activate QuantRocket, look up your license key on your account page and enter it in your deployment:

$ quantrocket license set 'XXXXXXXXXXXXXXXX'

>>> from quantrocket.license import set_license
>>> set_license("XXXXXXXXXXXXXXXX")

$ curl -X PUT 'http://houston/license-service/license/XXXXXXXXXXXXXXXX'

View your license

You can view the details of the currently installed license:

$ quantrocket license get
licensekey: XXXXXXXXXXXXXXXX
software_license:
  account:
    account_limit: XXXXXX USD
  concurrent_install_limit: XX
  license_type: Professional
  user_limit: XX

>>> from quantrocket.license import get_license_profile
>>> get_license_profile()
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'software_license': {'license_type': 'Professional',
  'user_limit': XX,
  'concurrent_install_limit': XX,
  'account': {'account_limit': 'XXXXXX USD'}}}

$ curl -X GET 'http://houston/license-service/license'
{"licensekey": "XXXXXXXXXXXXXXXX", "software_license": {"license_type": "Professional", "user_limit": XX, "concurrent_install_limit": XX, "account": {"account_limit": "XXXXXX USD"}}}

The license service will re-query your subscriptions and permissions every 10 minutes. If you make a change to your billing plan and want your deployment to see the change immediately, you can force a refresh:

$ quantrocket license get --force-refresh

>>> from quantrocket.license import get_license_profile
>>> get_license_profile(force_refresh=True)

$ curl -X GET 'http://houston/license-service/license?force_refresh=true'

Account limit validation

The account limit displayed in your license profile output applies to live trading using the blotter and to real-time data. The account limit does not apply to historical data collection, research, or backtesting. For advisor accounts, the account size is the sum of all master and sub-accounts.

Paper trading is not subject to the account limit, however paper trading requires that the live account limit has previously been validated. Thus before paper trading it is first necessary to connect your live account at least once and let the software validate it.

To validate your account limit if you have only connected your paper account:

• Switch to your live account using the instructions for your broker
• Wait approximately 1 minute. The software queries your account balance every minute whenever your broker is connected.

To verify that account validation has occurred, refresh your license profile. It should now display your account balance and whether the balance is under the account limit:

$ quantrocket license get --force-refresh
licensekey: XXXXXXXXXXXXXXXX
software_license:
  account:
    account_balance: 593953.42 USD
    account_balance_details:
    - Account: U12345
      Currency: USD
      NetLiquidation: 593953.42 USD
    account_balance_under_limit: true
    account_limit: XXXXXX USD
  concurrent_install_limit: XX
  license_type: Professional
  user_limit: XX

>>> from quantrocket.license import get_license_profile
>>> get_license_profile(force_refresh=True)
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'software_license': {'license_type': 'Professional',
  'user_limit': XX,
  'concurrent_install_limit': XX,
  'account': {'account_limit': 'XXXXXX USD',
   'account_balance': '593953.42 USD',
   'account_balance_under_limit': True,
   'account_balance_details': [{'Account': 'U12345',
     'Currency': 'USD',
     'NetLiquidation': 593953.42}]}}}

$ curl -X GET 'http://houston/license-service/license?force_refresh=true'
{"licensekey": "XXXXXXXXXXXXXXXX", "software_license": {"license_type": "Professional", "user_limit": XX, "concurrent_install_limit": XX, "account": {"account_limit": "XXXXXX USD", "account_balance": "593953.42 USD", "account_balance_under_limit": true, "account_balance_details": [{"Account": "U12345", "Currency": "USD", "NetLiquidation": 593953.42}]}}}

Now you can switch back to your paper account and begin paper trading.

User limit vs concurrent install limit

The output of your license profile displays your user limit and your concurrent install limit. User limit indicates the total number of distinct users who are licensed to use the software in any given month. Concurrent install limit indicates the total number of copies of the software that may be installed and running at any given time.

The concurrent install limit is set to (user limit + 1).

Connect from other applications

If you run other applications, you can connect them to your QuantRocket deployment for the purpose of querying data, submitting orders, etc.

To utilize the Python API and/or CLI from outside of QuantRocket, install the client on the application or system you wish to connect from:

$ pip install 'quantrocket-client'

To ensure compatibility, the MAJOR.MINOR version of the client should match the MAJOR.MINOR version of your deployment. For example, if your deployment is version 2.1.x, you can install the latest 2.1.x client:

$ pip install 'quantrocket-client>=2.1,<2.2'

Next, set environment variables to tell the client how to connect to your QuantRocket deployment. For a cloud deployment, this means providing the deployment URL and credentials:

$ # Linux/MacOS syntax:
$ export HOUSTON_URL=https://quantrocket.123capital.com
$ export HOUSTON_USERNAME=myusername
$ export HOUSTON_PASSWORD=mypassword

$ # Windows syntax (restart PowerShell afterwards for change to take effect):
$ [Environment]::SetEnvironmentVariable("HOUSTON_URL", "https://quantrocket.123capital.com", "User")
$ [Environment]::SetEnvironmentVariable("HOUSTON_USERNAME", "myusername", "User")
$ [Environment]::SetEnvironmentVariable("HOUSTON_PASSWORD", "mypassword", "User")

For connecting to a local deployment, only the URL is needed:

$ # Linux/MacOS syntax:
$ export HOUSTON_URL=http://localhost:1969

$ # Windows syntax (restart PowerShell afterwards for change to take effect):
$ [Environment]::SetEnvironmentVariable("HOUSTON_URL", "http://localhost:1969", "User")

Finally, test that it worked:

$ quantrocket houston ping
msg: hello from houston

>>> from quantrocket.houston import ping
>>> ping()
{u'msg': u'hello from houston'}

$ curl -u myusername:mypassword https://quantrocket.123capital.com/ping
{"msg": "hello from houston"}

To connect from applications running languages other than Python, you can skip the client installation and use the HTTP API directly.

Multi-user deployments

Hedge funds and other multi-user organizations can benefit from the ability to run more than one QuantRocket deployment.

The primary user interface for QuantRocket is JupyterLab, which is best suited for use by a single user at a time. While it is possible for multiple users to log in to the same QuantRocket cloud deployment, it is usually not ideal because they will be working in a shared JupyterLab environment, with a shared filesytem and notebooks, shared JupyterLab terminals and kernels, and shared compute resources. This will likely lead to stepping on each other's toes.

For hedge funds, a recommended deployment strategy is to run a primary deployment for data collection and live trading, and one or more research deployments (depending on subscription) for research and backtesting.

Collect data on the primary deployment and push it to S3. Once pushed, deep historical data can optionally be purged from the primary deployment, retaining only enough historical data to run live trading. Then, selectively pull databases from S3 onto the research deployment(s), where researchers analyze the data and run backtests.

Research deployments can be hosted in the cloud or run on the researcher's local workstation.

Each researcher's code, notebooks, and JupyterLab environment are isolated from those of other researchers. The code can be pushed to separate Git repositories, with sharing and access control managed on the Git repositories.

Broker and Data Connections

This section outlines how to connect to brokers and third-party data providers.

Interactive Brokers

IBKR Account Structure

Multiple logins and data concurrency

The structure of your Interactive Brokers (IBKR) account has a bearing on the speed with which you can collect real-time and historical data with QuantRocket. In short, the more IB Gateways you run, the more data you can collect. The basics of account structure and data concurrency are outlined below:

• All interaction with the IBKR servers, including real-time and historical data collection, is routed through IB Gateway, IBKR's slimmed-down version of Trader Workstation.
• IBKR imposes rate limits on the amount of historical and real-time data that can be received through IB Gateway.
• Each IB Gateway is tied to a particular set of login credentials. Each login can be running only one active IB Gateway session at any given time.
• However, an account holder can have multiple logins—at least two logins or possibly more, depending on the account structure. Each login can run its own IB Gateway session. In this way, an account holder can potentially run multiple instances of IB Gateway simultaneously.
• QuantRocket is designed to take advantage of multiple IB Gateways. When running multiple gateways, QuantRocket will spread your market data requests among the connected gateways.
• Since each instance of IB Gateway is rate limited separately by IBKR, the combined data throughput from splitting requests among two IB Gateways is twice that of sending all requests to one IB Gateway.
• Each separate login must separately subscribe to the relevant market data in IBKR Client Portal.

Below are a few common ways to obtain additional logins.

Second user login

Individual account holders can add a second login to their account. This is designed to allow you to use one login for API trading while using the other login to use Trader Workstation for manual trading or account monitoring. However, you can use both logins to collect data with QuantRocket. Note that you can't use the same login to simultaneously run Trader Workstation and collect data with QuantRocket. However, QuantRocket makes it easy to start and stop IB Gateway on a schedule, so the following is an option:

• Login 1 (used for QuantRocket only)
  • IB Gateway always running and available for data collection and placing API orders
• Login 2 (used for QuantRocket and Trader Workstation)
  • automatically stop IB Gateway daily at 9:30 AM
  • Run Trader Workstation during trading session for manual trading/account monitoring
  • automatically start IB Gateway daily at 4:00 PM so it can be used for overnight data collection

Advisor/Friends and Family accounts

An advisor account or the similarly structured Friends and Family account offers the possibility to obtain additional logins. Even an individual trader can open a Friends and Family account, in which they serve as their own advisor. The account setup is as follows:

• Master/advisor account: no trading occurs in this account. The account is funded only with enough money to cover market data costs. This yields 1 IB Gateway login.
• Master/advisor second user login: like an individual account, the master account can create a second login, subscribe to market data with this login, and use it for data collection.
• Client account: this is main trading account where the trading funds are deposited. This account receives its own login (for 3 total). By default this account does not having trading permissions, but you can enable client trading permissions via the master account, then subscribe to market data in the client account and begin using the client login to run another instance of IB Gateway. (Note that it's not possible to add a second login for a client account.)

If you have other accounts such as retirement accounts, you can add them as additional client accounts and obtain additional logins.

Paper trading accounts

Each IBKR account holder can enable a paper trading account for simulated trading. You can share market data with your paper account and use the paper account login with QuantRocket to collect data, as well as to paper trade your strategies. You don't need to switch to using your live account until you're ready for live trading (although it's also fine to use your live account login from the start).

Note that, due to restrictions on market data sharing, it's not possible to run IB Gateway using the live account login and corresponding paper account login at the same time. If you try, one of the sessions will disconnect the other session.

IBKR market data permissions

To collect IBKR data using QuantRocket, you must subscribe to the relevant market data in your IBKR account. In IBKR Client Portal, click on Settings > User Settings > Market Data Subscriptions:

Click the edit icon then select and confirm the relevant subscriptions:

Market data for paper accounts

IBKR paper accounts do not directly subscribe to market data. Rather, to access market data using your IBKR paper account, subscribe to the data in your live account and share it with your paper account. Log in to IBKR Client Portal with your live account login and go to Settings > Account Settings > Paper Trading Account:

Then select the option to share your live account's market data with your paper account:

IB Gateway

QuantRocket uses the IBKR API to collect market data from IBKR, submit orders, and track positions and account balances. All communication with IBKR is routed through IB Gateway, a Java application which is a slimmed-down version of Trader Workstation (TWS) intended for API use. You can run one or more IB Gateway services through QuantRocket, where each gateway instance is associated with a different IBKR username and password.

Connect to IBKR

IB Gateway runs inside the ibg1 container and connects to IBKR using your IBKR username and password. (If you have multiple IBKR usernames, you can run multiple IB Gateways.) The ibgrouter container provides an API that allows you to start and stop IB Gateway inside the ibg container(s).

The steps for connecting to your IBKR account and starting IB Gateway differ depending on whether your IBKR account requires the use of a security card at login.

Secure Login System (SLS)

For fully automated configuration and running of IB Gateway, you must partially opt out of the Secure Login System (SLS), IBKR's two-factor authentication. With a partial opt-out, your username and password (but not your security device) are required for logging into IB Gateway and other IBKR trading platforms. Your security device is still required for logging in to Client Portal. A partial opt-out can be performed in Client Portal by going to Settings > User Settings > Secure Login System > Secure Login Settings.

If you prefer not to perform a partial opt-out of IBKR's Secure Login System (SLS) or can't for regulatory reasons, you can still use QuantRocket but will need to manually enter your security code each time you start IB Gateway using your live login.

Enter IBKR login (no security card)

To connect to your IBKR account, enter your IBKR login into your deployment, as well as the desired trading mode (live or paper). You'll be prompted for your password:

$ quantrocket ibg credentials 'ibg1' --username 'myuser' --paper # or --live
Enter IBKR Password:
status: successfully set ibg1 credentials

>>> from quantrocket.ibg import set_credentials
>>> set_credentials("ibg1", username="myuser", trading_mode="paper")
Enter IBKR Password:
{'status': 'successfully set ibg1 credentials'}

$ curl -X PUT 'http://houston/ibg1/credentials' -d 'username=myuser' -d 'password=mypassword' -d 'trading_mode=paper'
{"status": "successfully set ibg1 credentials"}

When setting your credentials, QuantRocket performs several steps. It stores your credentials inside your deployment so you don't need to enter them again. It then starts and stops IB Gateway, which causes IB Gateway to download a settings file which QuantRocket then configures appropriately. The entire process takes approximately 30 seconds to complete.

Enter IBKR login (security card required)

To connect to a live IBKR account which requires second factor authentication, enter your IBKR login into your deployment. You'll be prompted for your password:

$ quantrocket ibg credentials 'ibg1' --username 'myuser' --live
Enter IBKR Password:
msg: Cannot start gateway because second factor authentication is required. API settings
  not updated. Please open the IB Gateway GUI to complete authentication, then manually
  update the API settings. See http://qrok.it/h/ib2fa to learn more
status: error

>>> from quantrocket.ibg import set_credentials
>>> set_credentials("ibg1", username="myuser", trading_mode="live")
Enter IBKR Password:
HTTPError: ('401 Client Error: UNAUTHORIZED for url: http://houston/ibg1/credentials', {'status': 'error', 'msg': 'Cannot start gateway because second factor authentication is required. API settings not updated. Please open the IB Gateway GUI to complete authentication, then manually update the API settings. See http://qrok.it/h/ib2fa to learn more'})

$ curl -X PUT 'http://houston/ibg1/credentials' -d 'username=myuser' -d 'password=mypassword' -d 'trading_mode=live'
{"status": "error", "msg": "Cannot start gateway because second factor authentication is required. API settings not updated. Please open the IB Gateway GUI to complete authentication, then manually update the API settings. See http://qrok.it/h/ib2fa to learn more"}

An error message advises you to open the IB Gateway GUI to complete the login. Follow the instructions in a later section to open the GUI, and enter your security code to complete the login.

Due to the security card requirement, QuantRocket wasn't able to programatically update IB Gateway settings, so you should update those manually. In the IB Gateway GUI, click Configure > Settings and change the following settings:

• uncheck Read-only API (if you intend to place orders using QuantRocket)
• set Master Client ID to 6000 (if you want QuantRocket to track your trades)

To quit the GUI session but leave IB Gateway running, simply close your browser tab.

Verify IBKR connection

Querying your IBKR account balance is a good way to verify your IBKR connection:

$ quantrocket account balance --latest --fields 'NetLiquidation' | csvlook
| Account   | Currency | NetLiquidation |         LastUpdated |
| --------- | -------- | -------------- | ------------------- |
| DU12345   | USD      |     500,000.00 | 2020-02-02 22:57:13 |

>>> from quantrocket.account import download_account_balances
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_account_balances(f, latest=True, fields=["NetLiquidation"])
>>> balances = pd.read_csv(f, parse_dates=["LastUpdated"])
>>> balances.head()
   Account Currency  NetLiquidation         LastUpdated
0  DU12345      USD        500000.0 2020-02-02 22:57:13

$ curl 'http://houston/account/balances.csv?latest=true&fields=NetLiquidation'
Account,Currency,NetLiquidation,LastUpdated
DU12345,USD,500000.0,2020-02-02 22:57:13

Switch between live and paper account

When you sign up for an IBKR paper account, IBKR provides login credentials for the paper account. However, it is also possible to login to the paper account by using your live account credentials and specifying the trading mode as "paper". Thus, technically the paper login credentials are unnecessary.

Using your live login credentials for both live and paper trading allows you to easily switch back and forth. Supposing you originally select the paper trading mode:

$ quantrocket ibg credentials 'ibg1' --username 'myliveuser' --paper
Enter IBKR Password:
status: successfully set ibg1 credentials

>>> from quantrocket.ibg import set_credentials
>>> set_credentials("ibg1", username="myliveuser", trading_mode="paper")
Enter IBKR Password:
{'status': 'successfully set ibg1 credentials'}

$ curl -X PUT 'http://houston/ibg1/credentials' -d 'username=myliveuser' -d 'password=mypassword' -d 'trading_mode=paper'
{"status": "successfully set ibg1 credentials"}

$ quantrocket ibg credentials 'ibg1' --live
status: successfully set ibg1 credentials

>>> set_credentials("ibg1", trading_mode="live")
{'status': 'successfully set ibg1 credentials'}

$ curl -X PUT 'http://houston/ibg1/credentials' -d 'trading_mode=live'
{"status": "successfully set ibg1 credentials"}

$ quantrocket ibg credentials 'ibg1'
TRADING_MODE: live
TWSUSERID: myliveuser

>>> from quantrocket.ibg import get_credentials
>>> get_credentials("ibg1")
{'TWSUSERID': 'myliveuser', 'TRADING_MODE': 'live'}

$ curl -X GET 'http://houston/ibg1/credentials'
{"TWSUSERID": "myliveuser", "TRADING_MODE": "live"}

Start/stop IB Gateway

IB Gateway must be running whenever you want to collect market data or place or monitor orders. You can optionally stop IB Gateway when you're not using it.

To check the current status of your IB Gateway(s):

$ quantrocket ibg status
ibg1: stopped

>>> from quantrocket.ibg import list_gateway_statuses
>>> list_gateway_statuses()
{'ibg1': 'stopped'}

$ curl -X GET 'http://houston/ibgrouter/gateways'
{"ibg1": "stopped"}

$ quantrocket ibg start --wait
ibg1:
  status: running

>>> from quantrocket.ibg import start_gateways
>>> start_gateways(wait=True)
{'ibg1': {'status': 'running'}}

$ curl -X POST 'http://houston/ibgrouter/gateways?wait=True'
{"ibg1": {"status": "running"}}

$ quantrocket ibg stop --wait
ibg1:
  status: stopped

>>> from quantrocket.ibg import stop_gateways
>>> stop_gateways(wait=True)
{'ibg1': {'status': 'stopped'}}

$ curl -X DELETE 'http://houston/ibgrouter/gateways?wait=True'
{"ibg1": {"status": "stopped"}}

Although IB Gateway is advertised as not having to be restarted once a day like Trader Workstation, it's not unusual for IB Gateway to display unexpected behavior (such as not returning market data when requested) which is then resolved simply by restarting IB Gateway. Therefore you might find it beneficial to restart your gateways from time to time, which you could do via countdown, QuantRocket's cron service:

# Restart IB Gateways nightly at 1AM
0 1 * * * quantrocket ibg stop --wait && quantrocket ibg start

Or, perhaps you use one of your IBKR logins during the day to monitor the market using Trader Workstation, but in the evenings you'd like to use this login to add concurrency to your historical data collection. You could start and stop the IB Gateway service in conjunction with the data collection:

# Collect data in the evenings using all logins, but then disconnect from ibg2
30 17 * * 1-5 quantrocket ibg start --wait --gateways 'ibg2' && quantrocket history collect "nasdaq-1d" && quantrocket history wait "nasdaq-1d" && quantrocket ibg stop --gateways 'ibg2'

IB Gateway GUI

Normally you won't need to access the IB Gateway GUI. However, you might need access to troubleshoot a login issue, or if you've enabled two-factor authentication for IB Gateway.

To allow access to the IB Gateway GUI, QuantRocket uses NoVNC, which uses the WebSockets protocol to support VNC connections in the browser. To open an IB Gateway GUI connection in your browser, click the "IB Gateway GUI" button located on the JupyterLab Launcher or from the File menu. The IB Gateway GUI will open in a new window (make sure your browser doesn't block the pop-up).

To quit the VNC session but leave IB Gateway running, simply close your browser tab.

Multiple IB Gateways

QuantRocket support running multiple IB Gateways, each associated with a particular IBKR login. Two of the main reasons for running multiple IB Gateways are:

1. To trade multiple accounts
2. To increase market data concurrency

The default IB Gateway service is called ibg1. To run multiple IB Gateways, create a file called docker-compose.override.yml in the same directory as your docker-compose.yml and add the desired number of additional services as shown below. In this example we are adding two additional IB Gateway services, ibg2 and ibg3, which inherit from the definition of ibg1:

# docker-compose.override.yml
version: '2.4'
services:
  ibg2:
    extends:
        file: docker-compose.yml
        service: ibg1
  ibg3:
    extends:
        file: docker-compose.yml
        service: ibg1

You can learn more about docker-compose.override.yml in another section.

Then, deploy the new service(s):

$ cd /path/to/docker-compose.yml
$ docker-compose -p quantrocket up -d

You can then enter your login for each of the new IB Gateways:

$ quantrocket ibg credentials 'ibg2' --username 'myuser' --paper
Enter IBKR Password:
status: successfully set ibg2 credentials

>>> from quantrocket.ibg import set_credentials
>>> set_credentials("ibg2", username="myuser", trading_mode="paper")
Enter IBKR Password:
{'status': 'successfully set ibg2 credentials'}

$ curl -X PUT 'http://houston/ibg2/credentials' -d 'username=myuser' -d 'password=mypassword' -d 'trading_mode=paper'
{"status": "successfully set ibg2 credentials"}

$ quantrocket ibg start --gateways 'ibg2'
status: the gateways will be started asynchronously

>>> from quantrocket.ibg import start_gateways
>>> start_gateways(gateways=["ibg2"])
{'status': 'the gateways will be started asynchronously'}

$ curl -X POST 'http://houston/ibgrouter/gateways?gateways=ibg2'
{"status": "the gateways will be started asynchronously"}

Market data permission file

To retrieve market data from IBKR, you must subscribe to the appropriate market data subscriptions in IBKR Client Portal. QuantRocket can't identify your subscriptions via API, so you must tell QuantRocket about your subscriptions by loading a YAML configuration file. If you don't load a configuration file, QuantRocket will assume you have market data permissions for any data you request through QuantRocket. If you only run one IB Gateway service, this is probably sufficient and you can skip the configuration file. However, if you run multiple IB Gateway services with separate market data permissions for each, you will probably want to load a configuration file so QuantRocket can route your requests to the appropriate IB Gateway service. You should also update your configuration file whenever you modify your market data permissions in IBKR Client Portal.

QuantRocket looks for a market data permission file called quantrocket.ibg.permissions.yml in the top-level of the Jupyter file browser (that is, /codeload/quantrocket.ibg.permissions.yml). The format of the YAML file is shown below:

# each top-level key is the name of an IB Gateway service
ibg1:
    # list the exchanges, by security type, this gateway has permission for
    marketdata:
        STK:
            - NYSE
            - ISLAND
            - TSEJ
        FUT:
            - GLOBEX
            - OSE
        CASH:
            - IDEALPRO
    # list the research services this gateway has permission for
    # (options: wsh)
    research:
        - wsh
# Include a separate section for each IB Gateway service
ibg2:
    marketdata:
        STK:
            - NYSE

When you create or edit this file, QuantRocket will detect the change and load the configuration. It's a good idea to have flightlog open when you do this. If the configuration file is valid, you'll see a success message:

quantrocket.ibgrouter: INFO Successfully loaded /codeload/quantrocket.ibg.permissions.yml

If the configuration file is invalid, you'll see an error message:

quantrocket.ibgrouter: ERROR Could not load /codeload/quantrocket.ibg.permissions.yml:
quantrocket.ibgrouter: ERROR unknown key(s) for service ibg1: marketdata-typo

You can also dump out the currently loaded config to confirm it is as you expect:

$ quantrocket ibg config
ibg1:
  marketdata:
    CASH:
    - IDEALPRO
    FUT:
    - GLOBEX
    - OSE
    STK:
    - NYSE
    - ISLAND
    - TSEJ
  research:
  - reuters
  - wsh
ibg2:
  marketdata:
    STK:
    - NYSE

>>> from quantrocket.ibg import get_ibg_config
>>> get_ibg_config()
{
    'ibg1': {
        'marketdata': {
            'CASH': [
                'IDEALPRO'
            ],
            'FUT': [
                'GLOBEX',
                'OSE'
            ],
            'STK': [
                'NYSE',
                'ISLAND',
                'TSEJ'
            ]
        },
        'research': [
            'reuters',
            'wsh'
        ]
    },
    'ibg2': {
        'marketdata': {
            'STK': [
                'NYSE'
            ]
        }
    }
 }

$ curl -X GET 'http://houston/ibgrouter/config'
{
    "ibg1": {
        "marketdata": {
            "CASH": [
                "IDEALPRO"
            ],
            "FUT": [
                "GLOBEX",
                "OSE"
            ],
            "STK": [
                "NYSE",
                "ISLAND",
                "TSEJ"
            ]
        },
        "research": [
            "reuters",
            "wsh"
        ]
    },
    "ibg2": {
        "marketdata": {
            "STK": [
                "NYSE"
            ]
        }
    }
 }

IB Gateway log files

If you need to send your IB Gateway log files to IBKR for troubleshooting, you can use the IB Gateway GUI to export the log files to the Docker filesystem, then copy them to your local filesystem.

1. With IB Gateway running, open the GUI.
2. In the IB Gateway GUI, click File > Gateway Logs, and select the day you're interested in.
3. For small logs, you can view the logs directly in IB Gateway and copy them to your clipboard.
4. For larger logs, click Export Logs or Export Today Logs. A file browser will open, showing the filesystem inside the Docker container.
5. Export the log file to an easy-to-find location such as /tmp/ibgateway-exported-logs.txt.
6. From the host machine, copy the exported logs from the Docker container to your local filesystem. For ibg1 logs saved to the above location, the command would be:

$ docker cp quantrocket_ibg1_1:/tmp/ibgateway-exported-logs.txt ibgateway-exported-logs.txt

Alpaca

Alpaca supports live and paper trading using two separate pairs of API keys and secret keys. Enter each pair of keys to enable the respective type of trading:

$ # live
$ quantrocket license alpaca-key --api-key 'XXXXXXXXXXXXXXXXXX' --live
Enter Alpaca secret key:
status: successfully set Alpaca live API key
$ # paper
$ quantrocket license alpaca-key --api-key 'PXXXXXXXXXXXXXXXXXX' --paper
Enter Alpaca secret key:
status: successfully set Alpaca paper API key

>>> # live
>>> from quantrocket.license import set_alpaca_key
>>> set_alpaca_key(api_key="XXXXXXXXXXXXXXXXXX", trading_mode="live")
Enter Alpaca secret key:
{'status': 'successfully set Alpaca live API key'}
>>> # paper
>>> set_alpaca_key(api_key="PXXXXXXXXXXXXXXXXXX", trading_mode="paper")
Enter Alpaca secret key:
{'status': 'successfully set Alpaca paper API key'}

$ # live
$ curl -X PUT 'http://houston/license-service/credentials/alpaca' -d 'api_key=XXXXXXXXXXXXXXXXXX&secret_key=XXXXXXXXXXXXXXXXXX&trading_mode=live'
{"status": "successfully set Alpaca live API key"}
$ # paper
$ curl -X PUT 'http://houston/license-service/credentials/alpaca' -d 'api_key=PXXXXXXXXXXXXXXXXXX&secret_key=XXXXXXXXXXXXXXXXXX&trading_mode=paper'
{"status": "successfully set Alpaca paper API key"}

$ quantrocket license alpaca-key
live:
  api_key: XXXXXXXXXXXXXXXXXX
paper:
  api_key: PXXXXXXXXXXXXXXXXXX

>>> from quantrocket.license import get_alpaca_key
>>> get_alpaca_key()
{'live': {'api_key': 'XXXXXXXXXXXXXXXXXX'},
 'paper': {'api_key': 'PXXXXXXXXXXXXXXXXXX'}}

$ curl -X GET 'http://houston/license-service/credentials/alpaca'
{"live": {"api_key": "XXXXXXXXXXXXXXXXXX"}, "paper": {"api_key": "PXXXXXXXXXXXXXXXXXX"}}

If you have access to Polygon.io data through Alpaca and wish to access Polygon.io data in QuantRocket, you should additionally enter your Alpaca key as your Polygon API key, as described below.

Polygon.io

To enable access to Polygon.io data, enter your Polygon.io API key (or Alpaca API key, for users with Polygon.io access through Alpaca):

$ quantrocket license polygon-key 'XXXXXXXXXXXXXXXXXX'
status: successfully set Polygon API key

>>> from quantrocket.license import set_polygon_key
>>> set_polygon_key(api_key="XXXXXXXXXXXXXXXXXX")
{'status': 'successfully set Polygon API key'}

$ curl -X PUT 'http://houston/license-service/credentials/polygon' -d 'api_key=XXXXXXXXXXXXXXXXXX'
{"status": "successfully set Polygon API key"}

$ quantrocket license polygon-key
api_key: XXXXXXXXXXXXXXXXXX

>>> from quantrocket.license import get_polygon_key
>>> get_polygon_key()
{'api_key': 'XXXXXXXXXXXXXXXXXX'}

curl -X GET 'http://houston/license-service/credentials/polygon'
{"api_key": "XXXXXXXXXXXXXXXXXX"}

Quandl

Professional users who subscribe to Sharadar data through Quandl can access Sharadar data in QuantRocket. To enable access, enter your Quandl API key:

$ quantrocket license quandl-key 'XXXXXXXXXXXXXXXXXX'
status: successfully set Quandl API key

>>> from quantrocket.license import set_quandl_key
>>> set_quandl_key(api_key="XXXXXXXXXXXXXXXXXX")
{'status': 'successfully set Quandl API key'}

$ curl -X PUT 'http://houston/license-service/credentials/quandl' -d 'api_key=XXXXXXXXXXXXXXXXXX'
{"status": "successfully set Quandl API key"}

$ quantrocket license quandl-key
api_key: XXXXXXXXXXXXXXXXXX

>>> from quantrocket.license import get_quandl_key
>>> get_quandl_key()
{'api_key': 'XXXXXXXXXXXXXXXXXX'}

curl -X GET 'http://houston/license-service/credentials/quandl'
{"api_key": "XXXXXXXXXXXXXXXXXX"}

IDEs and Editors

QuantRocket allows you to work in several different IDEs (integrated development environments) and editors.

Comparison

A summary comparison of the availables IDEs and editors is shown below:

JupyterLab is the primary user interface for QuantRocket. It is an ideal environment for interactive research. You can access QuantRocket's Python API through JupyterLab Consoles and Notebooks, and you can access QuantRocket's command line interface (CLI) through JupyterLab Terminals.

A limitation of JupyterLab is that its text editor is very basic, providing syntax highlighting but not much more. For a better code editing experience, you can use Eclipse Theia or Visual Studio Code.

Eclipse Theia and VS Code have similar user interfaces, so what are the differences? Eclipse Theia runs in the browser and requires no setup; thus you can edit your code from any computer. Theia provides syntax highlighting, auto-completion, linting, and a Git integration. Other features such as terminals are disabled.

VS Code runs on your desktop and requires some basic setup, but offers a fuller-featured editing experience. We suggest using VS Code on your main workstation and using Eclipse Theia when on-the-go.

JupyterLab

See the QuickStart for a hands-on overview of JupyterLab.

A recommended workflow for Moonshot strategies and custom scripts is to develop your code interactively in a Jupyter notebook then transfer it to a .py file.

Eclipse Theia

Access Eclipse Theia from the JupyterLab launcher:

Visual Studio Code

You can install Visual Studio Code on your desktop and attach it to your local or cloud deployment. This allows you to edit code and open terminals from within VS Code. VS Code utilizes the environment provided by the QuantRocket container you attach to, so autocomplete and other features are based on the QuantRocket environment, meaning there's no need to manually replicate QuantRocket's environment on your local computer.

Follow these steps to use VS Code with QuantRocket.

1. First, download and install VS Code for your operating system.
2. In VS Code, open the extension manager and install the following extensions:
   • Python
   • Docker
   • Remote - Containers
3. For cloud deployments only: By default, VS Code will be able to see any Docker containers running on your local machine. To make VS Code see your QuantRocket containers running remotely in the cloud, follow these steps:
   1. Open the command palette (View > Command Palette) and search for and run the command called: Shell Command: Install 'code' command in PATH. This makes it possible to launch VS Code from a Terminal or Powershell by typing code.
   2. Completely close VS Code.
   3. In a Terminal or Powershell, run docker-machine env quantrocket and run the provided command output, just as you would to deploy QuantRocket. This command sets environment variables which point Docker to the remote host where you are running QuantRocket.
   4. From the same Terminal or Powershell window, type code to launch VS Code. This causes VS Code to inherit the environment of the terminal from which you launched it, which enables VS Code to see the containers running remotely.
   5. Each time you launch VS Code in the future, you must launch it from a terminal as described in steps 3 and 4.
4. Open the Docker panel in the side bar, find the jupyter container, right-click, and choose "Attach Visual Studio Code". A new window opens.
5. (The original VS Code window still points to your local computer and can be used to edit your local projects.)
6. The new VS Code window that opened is attached to the jupyter container. VS code will automatically install itself on the jupyter container.
7. Any extensions you may have installed on your local VS Code are not automatically installed on the remote VS Code, so you should install them. Open the Extensions Manager and install, at minimum, the Python extension, and anything else you like. VS Code remembers what you install in a local configuration file and restores your desired environment in the future even if you destroy and re-create the container.
8. In the Explorer window, click Open Folder, type 'codeload', then Open Folder. The files on your jupyter container will now be displayed in the VS Code file browser.

Jupyter notebooks in VS Code

If you wish to use Jupyter notebooks in VS Code, follow these steps:

1. Open the command palette (View > Command Palette) and search for and select the command called: Python: Specify Local or Remote Jupyter Server for connections.
2. On the next menu, select Existing: Specify the URI of an existing server.
3. Enter the following URL: http://localhost/jupyter (this applies both to local and cloud deployments)
4. Reload the VS Code window if prompted to do so.
5. Open an existing Jupyter notebook. (Creating notebooks from within VS Code may or may not work.)
6. The first time you execute a cell, VS Code will prompt for a password. Simply hit enter. (No password is needed as you are already inside jupyter and simply connecting to localhost.)

Terminal utilities

.bashrc

You can customize your JupyterLab Terminals by creating a .bashrc file and storing it at /codeload/.bashrc. This file will be run when you open a new terminal, just like on a standard Linux distribution.

An example use is to create aliases for commonly typed commands. For example, placing the following alias in your /codeload/.bashrc file will allow you to check your balance by simply typing balance:

alias balance="quantrocket account balance -l -f NetLiquidation | csvlook"

After adding or editing a .bashrc file, you must open new Terminals for the changes to take effect.

csvkit

Many QuantRocket API endpoints return CSV files. csvkit is a suite of utilities that makes it easier to work with CSV files from the command line. To make a CSV file more easily readable, use csvlook:

$ quantrocket master get --exchanges 'XNAS' 'XNYS' | csvlook -I
| Sid            | Symbol | Exchange | Country | Currency | SecType | Etf | Timezone            | Name                       |
| -------------- | ------ | -------- | ------- | -------- | ------- | --- | ------------------- | -------------------------- |
| FIBBG000B9XRY4 | AAPL   | XNAS     | US      | USD      | STK     | 0   | America/New_York    | APPLE INC                  |
| FIBBG000BFWKC0 | MON    | XNYS     | US      | USD      | STK     | 0   | America/New_York    | MONSANTO CO                |
| FIBBG000BKZB36 | HD     | XNYS     | US      | USD      | STK     | 0   | America/New_York    | HOME DEPOT INC             |
| FIBBG000BMHYD1 | JNJ    | XNYS     | US      | USD      | STK     | 0   | America/New_York    | JOHNSON & JOHNSON          |

Another useful utility is csvgrep, which can be used to filter CSV files on fields not natively filterable by QuantRocket's API:

$ # save a CSV of NYSE ADRs by filtering on the usstock_SecurityType2 field
$ quantrocket master get --exchanges 'XNYS' --fields 'usstock_SecurityType2' | csvgrep --columns 'usstock_SecurityType2' --match 'Depositary Receipt' > nyse_adrs.csv

json2yml

For records which are too wide for the Terminal viewing area in CSV format, a convenient option is to request JSON and convert it to YAML using the json2yml utility:

$ quantrocket master get --symbols 'AAPL' --json | json2yml
  -
    Sid: "FIBBG000B9XRY4"
    Symbol: "AAPL"
    Exchange: "XNAS"
    Country: "US"
    Currency: "USD"
    SecType: "STK"
    Etf: 0
    Timezone: "America/New_York"
    Name: "APPLE INC"
    PriceMagnifier: 1
    Multiplier: 1
    Delisted: 0
    DateDelisted: null
    LastTradeDate: null
    RolloverDate: null

Custom JupyterLab environments

Follow these steps to create a custom conda environment and make it available as a custom kernel from the JupyterLab launcher.

First-time install

First, in a JupyterLab terminal, initialize your bash shell then exit the terminal:

$ conda init 'bash'
$ exit

Open a new JupyterLab terminal, then clone the base environment and activate your new environment:

$ conda create --name 'myclone' --clone 'base'
$ conda activate 'myclone'

Install new packages to customize your conda environment. For easier repeatability, list your packages in a text file in the /codeload directory and install the packages from file. One of the packages should be ipykernel:

$ (myclone) $ echo 'ipykernel' > /codeload/quantrocket.jupyter.conda.myclone.txt
$ (myclone) $ # add other packages to quantrocket.jupyter.conda.myclone.txt, then:
$ (myclone) $ conda install --file '/codeload/quantrocket.jupyter.conda.myclone.txt'

Next, create a new kernel spec associated with your custom conda environment. For easier repeatability, create the kernel spec under the /codeload directory instead of directly in the default location:

$ (myclone) $ # Install the spec to codeload so you have it for the future
$ (myclone) $ ipython kernel install --name 'mykernel' --display-name 'My Custom Kernel' --prefix '/codeload/kernels'

Install the kernel. This command copies the kernel spec to a location where JupyterLab looks:

$ (myclone) $ jupyter kernelspec install '/codeload/kernels/share/jupyter/kernels/mykernel'

Finally, to activate the change, open Terminal (MacOS/Linux) or PowerShell (Windows) and restart the jupyter container:

$ docker-compose restart jupyter

The new kernel will appear in the Launcher menu:

Re-install after container redeploy

Whenever you redeploy the jupyter container (either due to updating the container version or force recreating the container), the filesystem is replaced and thus your custom conda environment and JupyterLab kernel will be lost. The re-install process can omit a few steps because you saved the conda package file and kernel spec to your /codeload directory. The simplified process is as follows. Initialize your shell:

$ conda init 'bash'
$ exit

Reopen a terminal, then:

$ # clone base environment and activate new environment
$ conda create --name 'myclone' --clone 'base'
$ conda activate 'myclone'
$ (myclone) $ # install packages
$ (myclone) $ conda install --file '/codeload/quantrocket.jupyter.conda.myclone.txt'
$ (myclone) $ # install kernel spec
$ (myclone) $ jupyter kernelspec install '/codeload/kernels/share/jupyter/kernels/mykernel'

Then, restart the jupyter container to activate the change:

$ docker-compose restart jupyter

Securities Master

The securities master is the central repository of available assets. With QuantRocket's securities master, you can:

• Collect lists of all available securities from multiple data providers;
• Query reference data about securities, such as ticker symbol, currency, exchange, sector, expiration date (in the case of derivatives), and so on;
• Flexibly group securities into universes that make sense for your research or trading strategies.

QuantRocket assigns each security a unique ID known as its "Sid" (short for "security ID"). Sids allow securities to be uniquely and consistently referenced over time regardless of ticker changes or ticker symbol inconsistencies between vendors. Sids make it possible to mix-and-match data from different providers. QuantRocket Sids are primarily based on Bloomberg-sponsored OpenFIGI identifiers.

All components of the software, from historical and fundamental data collection to order and execution tracking, utilize Sids and thus depend on the securities master.

Collect listings

Generally, the first step before utilizing any dataset or sending orders to any broker is to collect the list of available securities for that provider.

Because QuantRocket supports multiple data vendors and brokers, you may collect the same listing (for example AAPL stock) from multiple providers. QuantRocket will consolidate the overlapping records into a single, combined record, as explained in more detail below.

Alpaca

Alpaca customers should collect Alpaca's list of available securities before they begin live or paper trading:

$ quantrocket master collect-alpaca
msg: successfully loaded alpaca securities
status: success

>>> from quantrocket.master import collect_alpaca_listings
>>> collect_alpaca_listings()
{'status': 'success', 'msg': 'successfully loaded alpaca securities'}

$ curl -X POST 'http://houston/master/securities/alpaca'
{"status": "success", "msg": "successfully loaded alpaca securities"}

An example Alpaca record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
alpaca_AssetClass: "us_equity"
alpaca_AssetId: "b0b6dd9d-8b9b-48a9-ba46-b9d54906e415" # Alpaca-assigned ID
alpaca_EasyToBorrow: 1 # whether an asset is easy-to-borrow or not
alpaca_Exchange: "NASDAQ"
alpaca_Marginable: 1 # whether an asset is marginable or not
alpaca_Name: null
alpaca_Shortable: 1 # whether an asset is shortable or not
alpaca_Status: "active" # active or inactive
alpaca_Symbol: "AAPL"
alpaca_Tradable: 1 # whether an asset is tradable on Alpaca or not

EDI

EDI listings are automatically collected when you collect EDI historical data, but they can also be collected separately. Specify one or MICs (market identifier codes):

$ quantrocket master collect-edi --exchanges 'XSHG' 'XSHE'
exchanges:
  XSHE: successfully loaded XSHE securities
  XSHG: successfully loaded XSHG securities
status: success

>>> from quantrocket.master import collect_edi_listings
>>> collect_edi_listings(exchanges=["XSHG", "XSHE"])
{'status': 'success',
 'exchanges': {'XSHG': 'successfully loaded XSHG securities', 'XSHE': 'successfully loaded XSHE securities'}}

$ curl -X POST 'http://houston/master/securities/edi?exchanges=XSHG&exchanges=XSHE'
{"status": "success", "exchanges": {"XSHG": "successfully loaded XSHG securities", "XSHE": "successfully loaded XSHE securities"}}

For sample data, use the MIC code FREE.

An example EDI record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
edi_Cik: 320193 # Central Index Key
edi_CountryInc: "United States of America" # Country of Incorporation of Issuer
edi_CountryListed: "United States of America" # Country of Exchange where listed
edi_Currency: "USD"
edi_DateDelisted: null
edi_ExchangeListingStatus: "Listed" # whether Listed or Unlisted on an Exchange
edi_FirstPriceDate: "2007-01-03" # first date a price is available
edi_GlobalListingStatus: "Active" # whether active or inactive at the global level. Not to be confused with delisted which is inactive at the exchange level
edi_Industry: "Information Technology"
edi_IsPrimaryListing: 1 # 1 if PrimaryMic = Mic
edi_IsoCountryInc: "US" # ISO Country of Incorporation of Issuer
edi_IsoCountryListed: "US" # ISO Country of Exchange where listed
edi_IssuerId: 30017 # EDI-assigned unique issuer ID
edi_IssuerName: "Apple Inc"
edi_LastPriceDate: null # latest date a price is available
edi_LocalSymbol: "AAPL" # Local code unique at Market level - a ticker or number
edi_Mic: "XNAS" # ISO standard Market Identification Code
edi_MicSegment: "XNGS"
edi_MicTimezone: "America/New_York"
edi_PreferredName: "Apple Inc" # for ETFs, the SecurityDesc, else the IssuerName
edi_PrimaryMic: "XNAS" # MIC code for the primary listing exchange; for depositary receipts, this might be in another country
edi_RecordCreated: "2001-05-05"
edi_RecordModified: "2020-02-10 13:17:27"
edi_SecId: 33449 # EDI-assigned unique global level Security ID
edi_SecTypeCode: "EQS" # security type (code)
edi_SecTypeDesc: "Equity Shares" # security type (description)
edi_SecurityDesc: "Ordinary Shares"
edi_Sic: "Electronic Computers"
edi_SicCode: 3571 # Standard Industrial Classification Code
edi_SicDivision: "Manufacturing"
edi_SicIndustryGroup: "Computer And Office Equipment"
edi_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
edi_StructureCode: null
edi_StructureDesc: null

Figi

QuantRocket Sids are based on FIGI identifiers. While the OpenFIGI API is primarily a way to map securities to FIGI identifiers, it also provides several useful security attributes including market sector, a detailed security type, and share class-level FIGI identifiers. You can collect FIGI fields for all available QuantRocket securities:

$ quantrocket master collect-figi
msg: successfully loaded FIGIs
status: success

>>> from quantrocket.master import collect_figi_listings
>>> collect_figi_listings()
{'status': 'success', 'msg': 'successfully loaded FIGIs'}

$ curl -X POST 'http://houston/master/securities/figi'
{"status": "success", "msg": "successfully loaded FIGIs"}

An example FIGI record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
figi_CompositeFigi: "BBG000B9XRY4" # country-level FIGI
figi_ExchCode: "US" # Bloomberg exchange code
figi_Figi: "BBG000B9XRY4" # usually the country-level FIGI, sometimes the exchange-level FIGI
figi_IsComposite: 1 # whether the figi_Figi column contains a composite FIGI
figi_MarketSector: "Equity"
figi_Name: "APPLE INC"
figi_SecurityDescription: "AAPL"
figi_SecurityType: "Common Stock" # security type (more detailed)
figi_SecurityType2: "Common Stock" # security type (less detailed)
figi_ShareClassFigi: "BBG001S5N8V8" # share class-level FIGI
figi_Ticker: "AAPL"
figi_UniqueId: "EQ0010169500001000" # Bloomberg ID
figi_UniqueIdFutOpt: null

Interactive Brokers

$ quantrocket master list-ibkr-exchanges --regions 'asia' --sec-types 'STK'
STK:
  Australia:
  - ASX
  - CHIXAU
  Hong Kong:
  - SEHK
  - SEHKNTL
  - SEHKSZSE
  India:
  - NSE
  Japan:
  - CHIXJ
  - JPNNEXT
  - TSEJ
  Singapore:
  - SGX

>>> from quantrocket.master import list_ibkr_exchanges
>>> list_ibkr_exchanges(regions=["asia"], sec_types=["STK"])
{'STK': {'Australia': ['ASX', 'CHIXAU'],
         'Hong Kong': ['SEHK', 'SEHKNTL', 'SEHKSZSE'],
         'India': ['NSE'],
         'Japan': ['CHIXJ', 'JPNNEXT', 'TSEJ'],
         'Singapore': ['SGX']}}

$ curl 'http://houston/master/exchanges/ibkr?sec_types=STK&regions=asia'
{"STK": {"Australia": ["ASX", "CHIXAU"], "Hong Kong": ["SEHK", "SEHKNTL", "SEHKSZSE"], "India": ["NSE"], "Japan": ["CHIXJ", "JPNNEXT", "TSEJ"], "Singapore": ["SGX"]}}

$ quantrocket master collect-ibkr --exchanges 'SEHK' --sec-types 'STK'
status: the IBKR listing details will be collected asynchronously

>>> from quantrocket.master import collect_ibkr_listings
>>> collect_ibkr_listings(exchanges="SEHK", sec_types=["STK"])
{'status': 'the IBKR listing details will be collected asynchronously'}

$ curl -X POST 'http://houston/master/securities/ibkr?exchanges=SEHK&sec_types=STK'
{"status": "the IBKR listing details will be collected asynchronously"}

$ quantrocket flightlog stream --hist 5
quantrocket.master: INFO Collecting SEHK STK listings from IBKR website
quantrocket.master: INFO Requesting details for 2630 SEHK listings found on IBKR website
quantrocket.master: INFO Saved 2630 SEHK listings to securities master database

For free sample data, specify the exchange code FREE.

An example IBKR record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
ibkr_AggGroup: 1
ibkr_Category: "Computers" # Sector > Industry > Category
ibkr_ComboLegs: null # stores user-defined combo legs
ibkr_ConId: 265598 # IBKR-assigned unique ID
ibkr_ContractMonth: null # expiration year-month for derivatives
ibkr_Currency: "USD"
ibkr_Cusip: null
ibkr_DateDelisted: null
ibkr_Delisted: 0 # 1 if delisted, otherwise 0
ibkr_Etf: 0 # 1 if ETF, otherwise 0
ibkr_EvMultiplier: 0 # applicable to certain Australian securities
ibkr_EvRule: null # applicable to certain Australian securities
ibkr_Industry: "Computers" # Sector > Industry > Category
ibkr_Isin: "US0378331005" # ISIN identifier, if subscribed
ibkr_LastTradeDate: null # last trade date for derivatives (may be earlier than ibkr_RealExpirationDate)
ibkr_LocalSymbol: "AAPL" # ticker symbol used on the exchange
ibkr_LongName: "APPLE INC"
ibkr_MarketName: "NMS"
ibkr_MarketRuleIds: "26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26,26" # market rule IDs corresponding to ibkr_ValidExchanges (market rules IDs specify valid tick sizes and are used internally, user can disregard)
ibkr_MdSizeMultiplier: 100 # multiplier for volume data (used internally, user can disregard)
ibkr_MinTick: 0.01 # minimum tick size
ibkr_Multiplier: null # contract multiplier for options and futures
ibkr_PriceMagnifier: 1 # price divisor to use when prices are quoted in a different currency than the security's currency (for example GBP-denominated securities which trade in GBX will have an ibkr_PriceMagnifier of 100)
ibkr_PrimaryExchange: "NASDAQ" # IBKR exchange code of primary listing exchange
ibkr_RealExpirationDate: null # expiration date for derivative contracts
ibkr_Right: null # For options: P for PUT or C for CALL
ibkr_SecType: "STK" # security type
ibkr_Sector: "Technology" # Sector > Industry > Category
ibkr_Strike: 0 # option strike price
ibkr_Symbol: "AAPL" # IBKR ticker symbol (sometimes different from ibkr_LocalSymbol)
ibkr_Timezone: "America/New_York"
ibkr_TradingClass: "NMS"
ibkr_UnderConId: 0 # ConId of underlying (for derivatives)
ibkr_UnderSecType: null # security type of underlying (for derivatives)
ibkr_UnderSymbol: null # symbol of underlying (for derivatives)
ibkr_ValidExchanges: "SMART,AMEX,NYSE,CBOE,PHLX,ISE,CHX,ARCA,ISLAND,DRCTEDGE,BEX,BATS,EDGEA,CSFBALGO,JEFFALGO,BYX,IEX,EDGX,FOXRIVER,TPLUS1,NYSENAT,PSX" # all exchanges where security can be routed

Option chains

To collect option chains from Interactive Brokers, first collect listings for the underlying securities:

$ quantrocket master collect-ibkr --exchanges 'NASDAQ' --sec-types 'STK' --symbols 'GOOG' 'FB' 'AAPL'
status: the IBKR listing details will be collected asynchronously

>>> from quantrocket.master import collect_ibkr_listings
>>> collect_ibkr_listings(exchanges="NASDAQ", sec_types=["STK"], symbols=["GOOG", "FB", "AAPL"])
{'status': 'the IBKR listing details will be collected asynchronously'}

$ curl -X POST 'http://houston/master/securities/ibkr?exchanges=NASDAQ&sec_types=STK&symbols=GOOG&symbols=FB&symbols=AAPL'
{"status": "the IBKR listing details will be collected asynchronously"}

$ quantrocket master get -e 'NASDAQ' -t 'STK' -s 'GOOG' 'FB' 'AAPL' | quantrocket master collect-ibkr-options --infile -
status: the IBKR option chains will be collected asynchronously

>>> from quantrocket.master import download_master_file, collect_ibkr_option_chains
>>> import io
>>> f = io.StringIO()
>>> download_master_file(f, exchanges=["NASDAQ"], sec_types=["STK"], symbols=["GOOG", "FB", "AAPL"])
>>> collect_ibkr_option_chains(infilepath_or_buffer=f)
{'status': 'the IBKR option chains will be collected asynchronously'}

$ curl -X GET 'http://houston/master/securities.csv?exchanges=NASDAQ&sec_types=STK&symbols=GOOG&symbols=FB&symbols=AAPL' > nasdaq_mega.csv
$ curl -X POST 'http://houston/master/options/ibkr' --upload-file nasdaq_mega.csv
{"status": "the IBKR option chains will be collected asynchronously"}

$ quantrocket master get -s 'GOOG' 'FB' 'AAPL' -t 'OPT' --outfile 'options.csv'

>>> from quantrocket.master import download_master_file
>>> download_master_file("options.csv", symbols=["GOOG", "FB", "AAPL"], sec_types=["OPT"])

$ curl -X GET 'http://houston/master/securities.csv?symbols=GOOG&symbols=FB&symbols=AAPL&sec_types=OPT' > options.csv

Sharadar

Sharadar listings are automatically collected when you collect Sharadar fundamental or price data, but they can also be collected separately. Specify the country (US):

$ quantrocket master collect-sharadar --countries 'US'
countries:
  US: successfully loaded US securities
status: success

>>> from quantrocket.master import collect_sharadar_listings
>>> collect_sharadar_listings(countries="US")
>>> {'status': 'success', 'countries': {'US': 'successfully loaded US securities'}}

$ curl -X POST 'http://houston/master/securities/sharadar?countries=US'
{"status": "success", "countries": {"US": "successfully loaded US securities"}}

For sample data, use the country code FREE.

An example Sharadar record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
sharadar_Category: "Domestic" # "Domestic", "Canadian" or "ADR"
sharadar_CompanySite: "http://www.apple.com" # URL of company website
sharadar_CountryListed: "US" # ISO country code where security is listed
sharadar_Currency: "USD"
sharadar_Cusips: 37833100
sharadar_DateDelisted: null
sharadar_Delisted: 0 # 1 if delisted, otherwise 0
sharadar_Exchange: "NASDAQ"
sharadar_FamaIndustry: "Computers"
sharadar_FamaSector: null
sharadar_FirstAdded: "2014-09-24" # date that the ticker was first added to coverage in the dataset
sharadar_FirstPriceDate: "1986-01-01" # date of the first price observation
sharadar_FirstQuarter: "1996-09-30" # first financial quarter available in the dataset
sharadar_Industry: "Consumer Electronics" # industry classification based on SIC codes in a format which approximates to GICS
sharadar_LastPriceDate: null # date of most recent price observation available
sharadar_LastQuarter: "2020-06-30" # last financial quarter available in the dataset
sharadar_LastUpdated: "2020-07-03"
sharadar_Location: "California; U.S.A" # company location as registered with the SEC
sharadar_Name: "Apple Inc"
sharadar_Permaticker: 199059 # Sharadar-assigned unique security ID
sharadar_RelatedTickers: null # prior tickers and/or alternative share classes
sharadar_ScaleMarketCap: "6 - Mega"
sharadar_ScaleRevenue: "6 - Mega"
sharadar_SecFilings: "https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0000320193" # URL pointing to the SEC filings
sharadar_Sector: "Technology" # sector classification based on SIC codes in a format which approximates to GICS
sharadar_SicCode: 3571 # Standard Industrial Classification Code
sharadar_SicIndustry: "Electronic Computers"
sharadar_SicSector: "Manufacturing"
sharadar_Ticker: "AAPL"

US Stock

All plans include access to historical intraday and end-of-day US stock prices. US stock listings are automatically collected when you collect the price data, but they can also be collected separately.

$ quantrocket master collect-usstock
msg: successfully loaded US stock listings
status: success

>>> from quantrocket.master import collect_usstock_listings
>>> collect_usstock_listings()
{'status': 'success', 'msg': 'successfully loaded US stock listings'}

$ curl -X POST 'http://houston/master/securities/usstock'
{"status": "success", "msg": "successfully loaded US stock listings"}

An example US stock record for AAPL is shown below:

Sid: "FIBBG000B9XRY4"
usstock_DateDelisted: null
usstock_FirstPriceDate: "2007-01-03" # date of first available price
usstock_LastPriceDate: null # date of last available price
usstock_Mic: "XNAS"
usstock_Name: "APPLE INC"
usstock_SecurityType: "Common Stock" # security type (more detailed) - copied from FIGI securityType
usstock_SecurityType2: "Common Stock" # security type (less detailed) - copied from FIGI securityType2
usstock_Sic: "Electronic Computers"
usstock_SicCode: 3571 # Standard Industrial Classification Code
usstock_SicDivision: "Manufacturing"
usstock_SicIndustryGroup: "Computer And Office Equipment"
usstock_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
usstock_Symbol: "AAPL"

Master file

After you collect listings, you can download and inspect the master file, querying by symbol, exchange, currency, sid, or universe. When querying by exchange, you can use the MIC as in the following example (preferred), or the vendor-specific exchange code:

$ quantrocket master get --exchanges 'XNAS' 'XNYS' -o listings.csv
$ csvlook listings.csv
| Sid            | Symbol | Exchange | Country | Currency | SecType | Etf | Timezone            | Name                       |
| -------------- | ------ | -------- | ------- | -------- | ------- | --- | ------------------- | -------------------------- |
| FIBBG000B9XRY4 | AAPL   | XNAS     | US      | USD      | STK     | 0   | America/New_York    | APPLE INC                  |
| FIBBG000BFWKC0 | MON    | XNYS     | US      | USD      | STK     | 0   | America/New_York    | MONSANTO CO                |
| FIBBG000BKZB36 | HD     | XNYS     | US      | USD      | STK     | 0   | America/New_York    | HOME DEPOT INC             |
| FIBBG000BMHYD1 | JNJ    | XNYS     | US      | USD      | STK     | 0   | America/New_York    | JOHNSON & JOHNSON          |
| FIBBG000BPH459 | MSFT   | XNAS     | US      | USD      | STK     | 0   | America/New_York    | MICROSOFT CORP             |

>>> import pandas as pd
>>> from quantrocket.master import download_master_file
>>> download_master_file("listings.csv", exchanges=["XNYS", "XNAS"])
>>> securities = pd.read_csv("listings.csv")
>>> securities.head()
              Sid Symbol Exchange Country Currency SecType  Etf          Timezone               Name
0  FIBBG000B9XRY4   AAPL     XNAS      US      USD     STK    0  America/New_York          APPLE INC
1  FIBBG000BFWKC0    MON     XNYS      US      USD     STK    0  America/New_York        MONSANTO CO
2  FIBBG000BKZB36     HD     XNYS      US      USD     STK    0  America/New_York     HOME DEPOT INC
3  FIBBG000BMHYD1    JNJ     XNYS      US      USD     STK    0  America/New_York  JOHNSON & JOHNSON
4  FIBBG000BPH459   MSFT     XNAS      US      USD     STK    0  America/New_York     MICROSOFT CORP

$ curl -X GET 'http://houston/master/securities.csv?exchanges=XNYS&exchanges=XNAS' > listings.csv
$ head listings.csv
Sid,Symbol,Exchange,Country,Currency,SecType,Etf,Timezone,Name
FIBBG000B9XRY4,AAPL,XNAS,US,USD,STK,0,America/New_York,"APPLE INC"
FIBBG000BFWKC0,MON,XNYS,US,USD,STK,0,America/New_York,"MONSANTO CO"
FIBBG000BKZB36,HD,XNYS,US,USD,STK,0,America/New_York,"HOME DEPOT INC"
FIBBG000BMHYD1,JNJ,XNYS,US,USD,STK,0,America/New_York,"JOHNSON & JOHNSON"
FIBBG000BPH459,MSFT,XNAS,US,USD,STK,0,America/New_York,"MICROSOFT CORP"

Core vs extended fields

By default, the securities master file returns a core set of fields:

• Sid: unique security ID
• Symbol: ticker symbol
• Exchange: the MIC (market identifier code) of the primary exchange
• Country: ISO country code
• Currency: ISO currency
• SecType: the security type. See available types
• ETF: 1 if the security is an ETF, otherwise 0
• Timezone: timezone of the exchange
• Name: issuer name or security description
• PriceMagnifier: price divisor to use when prices are quoted in a different currency than the security's currency (for example GBP-denominated securities which trade in GBX will have an PriceMagnifier of 100). This is used by QuantRocket but users won't usually need to worry about it.
• Multiplier: contract multiplier for derivatives
• Delisted: 1 if the security is delisted, otherwise 0
• DateDelisted: date security was delisted
• LastTradeDate: last trade date for derivatives
• RolloverDate: rollover date for futures contracts

These fields are consolidated from the available vendor records you've collected. In other words, QuantRocket will populate the core fields from any vendor that provides that field, based on the vendors you have collected listings from.

You can also access the extended fields, which are not consolidated but rather provide the exact values for a specific vendor. Extended fields are named like <vendor>_<FieldName> and can be requested in several ways, including by field name (e.g. usstock_Mic):

$ quantrocket master get --symbols 'AAPL' --fields 'Symbol' 'Exchange' 'usstock_Symbol' 'usstock_Mic' --json | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    Symbol: "AAPL"
    Exchange: "XNAS"
    usstock_Mic: "XNAS"
    usstock_Symbol: "AAPL"

>>> download_master_file("aapl.csv", symbols="AAPL", fields=["Symbol", "Exchange", "usstock_Symbol", "usstock_Mic"])
>>> securities = pd.read_csv("aapl.csv")
>>> securities.iloc[0]

Sid               FIBBG000B9XRY4
Symbol                      AAPL
Exchange                    XNAS
usstock_Mic                 XNAS
usstock_Symbol              AAPL

$ curl -X GET 'http://houston/master/securities.json?symbols=AAPL&fields=Symbol&fields=Exchange&fields=usstock_Symbol&fields=usstock_Mic' | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    Symbol: "AAPL"
    Exchange: "XNAS"
    usstock_Mic: "XNAS"
    usstock_Symbol: "AAPL"

$ quantrocket master get --symbols 'AAPL' --fields 'usstock*' --json | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    usstock_DateDelisted: null
    usstock_FirstPriceDate: "2007-01-03"
    usstock_LastPriceDate: "2020-04-03"
    usstock_Mic: "XNAS"
    usstock_Name: "APPLE INC"
    usstock_SecurityType: "Common Stock"
    usstock_SecurityType2: "Common Stock"
    usstock_Sic: "Electronic Computers"
    usstock_SicCode: 3571
    usstock_SicDivision: "Manufacturing"
    usstock_SicIndustryGroup: "Computer And Office Equipment"
    usstock_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
    usstock_Symbol: "AAPL"

>>> download_master_file("aapl.csv", symbols="AAPL", fields="usstock*")
>>> securities = pd.read_csv("aapl.csv")
>>> securities.iloc[0]

Sid                                                            FIBBG000B9XRY4
usstock_DateDelisted                                                      NaN
usstock_FirstPriceDate                                             2007-01-03
usstock_LastPriceDate                                              2020-04-03
usstock_Mic                                                              XNAS
usstock_Name                                                        APPLE INC
usstock_SecurityType                                             Common Stock
usstock_SecurityType2                                            Common Stock
usstock_Sic                                              Electronic Computers
usstock_SicCode                                                          3571
usstock_SicDivision                                             Manufacturing
usstock_SicIndustryGroup                        Computer And Office Equipment
usstock_SicMajorGroup       Industrial And Commercial Machinery And Comput...
usstock_Symbol                                                           AAPL

$ curl -X GET 'http://houston/master/securities.json?symbols=AAPL&fields=usstock%2A' | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    usstock_DateDelisted: null
    usstock_FirstPriceDate: "2007-01-03"
    usstock_LastPriceDate: "2020-04-03"
    usstock_Mic: "XNAS"
    usstock_Name: "APPLE INC"
    usstock_SecurityType: "Common Stock"
    usstock_SecurityType2: "Common Stock"
    usstock_Sic: "Electronic Computers"
    usstock_SicCode: 3571
    usstock_SicDivision: "Manufacturing"
    usstock_SicIndustryGroup: "Computer And Office Equipment"
    usstock_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
    usstock_Symbol: "AAPL"

$ quantrocket master get --symbols 'AAPL' --fields '*' --json | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    Symbol: "AAPL"
    Exchange: "XNAS"
    ...
    usstock_SicIndustryGroup: "Computer And Office Equipment"
    usstock_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
    usstock_Symbol: "AAPL"

>>> download_master_file("aapl.csv", symbols="AAPL", fields="*")
>>> securities = pd.read_csv("aapl.csv")
>>> securities.iloc[0]

Sid                                                            FIBBG000B9XRY4
Symbol                                                                   AAPL
Exchange                                                                 XNAS
                                                  ...
usstock_SicIndustryGroup                        Computer And Office Equipment
usstock_SicMajorGroup       Industrial And Commercial Machinery And Comput...
usstock_Symbol                                                           AAPL

$ curl -X GET 'http://houston/master/securities.json?symbols=AAPL&fields=%2A' | json2yml
---
  -
    Sid: "FIBBG000B9XRY4"
    Symbol: "AAPL"
    Exchange: "XNAS"
    ...
    usstock_SicIndustryGroup: "Computer And Office Equipment"
    usstock_SicMajorGroup: "Industrial And Commercial Machinery And Computer Equipment"
    usstock_Symbol: "AAPL"

Limit by vendor

In some cases, you might want to limit records to those provided by a specific vendor. For example, you might wish to create a universe of securities supported by your broker. For this purpose, use the --vendors/vendors parameter. This will cause the query to search the requested vendors only:

$ quantrocket master get --exchanges 'XNYS' --vendors 'ibkr' -o ibkr_securities.csv

>>> download_master_file("ibkr_securities.csv", exchanges="XNYS", vendors="ibkr")

$ curl -X GET 'http://houston/master/securities.csv?exchanges=XNYS&vendors=ibkr' -o ibkr_securities.csv

Security types

The following security types or asset classes are available:

With the exception of ETFs, these security type codes are stored in the SecType field of the master file. ETFs are a special case. Stocks and ETFs are distinguished as follows in the master file:

More detailed security types are also available from many vendors. See the following fields:

• edi_SecTypeCode and edi_SecTypeDesc
• figi_SecurityType and figi_SecurityType2
• sharadar_Category
• usstock_SecurityType and usstock_SecurityType2

Universes

Once you've collected listings that interest you, you can group them into meaningful universes. Universes provide a convenient way to refer to and manipulate groups of securities when collecting historical data, running a trading strategy, etc. You can create universes based on exchanges, security types, sectors, liquidity, or any criteria you like.

The most common way to create a universe is to download a master file that includes the securities you want, then create the universe from the master file:

$ quantrocket master get --exchanges 'XHKG' --sec-types 'STK' --outfile hongkong_securities.csv
$ quantrocket master universe 'hong-kong-stk' --infile hongkong_securities.csv
code: hong-kong-stk
inserted: 2216
provided: 2216
total_after_insert: 2216

>>> from quantrocket.master import download_master_file, create_universe
>>> download_master_file("hongkong_securities.csv", exchanges=["XHKG"], sec_types="STK")
>>> create_universe("hong-kong-stk", infilepath_or_buffer="hongkong_securities.csv")
{'code': 'hong-kong-stk',
 'inserted': 2216,
 'provided': 2216,
 'total_after_insert': 2216}

$ curl -X GET 'http://houston/master/securities.csv?exchanges=XHKG&sec_types=STK' > hongkong_securities.csv
$ curl -X PUT 'http://houston/master/universes/hong-kong-stk' --upload-file hongkong_securities.csv
{"code": "hong-kong-stk", "provided": 2216, "inserted": 2216, "total_after_insert": 2216}

Using the CLI, you can create a universe in one-line by piping the downloaded CSV to the universe command, using --infile - to specify reading the input file from stdin:

$ quantrocket master get --exchanges 'XCME' --symbols 'ES' --sec-types 'FUT' | quantrocket master universe 'es-fut' --infile -
code: es-fut
inserted: 12
provided: 12
total_after_insert: 12

Using the Python API, you can filter the master file in pandas, or using QGrid, then save the DataFrame to CSV and upload it:

>>> download_master_file("us_stk.csv", exchanges=["XNYS", "XNAS", "ARCX", "XASE"], sec_types="STK", fields="usstock*")
>>> securities = pd.read_csv("us_stk.csv")
>>> adrs = securities[securities.usstock_SecurityType2=="Depositary Receipt"]
>>> adrs.to_csv("adrs.csv")
>>> create_universe("us-adrs", infilepath_or_buffer="adrs.csv")
{'code': 'us-adrs',
 'provided': 669,
 'inserted': 669,
 'total_after_insert': 669}

You can also manually edit a CSV file, deleting rows you don't want, before uploading the file to create a universe.

You can also create a universe from existing universes:

$ quantrocket master universe 'asx' --from-universes 'asx-sml' 'asx-mid' 'asx-lrg'
code: asx
inserted: 1604
provided: 1604
total_after_insert: 1604

>>> from quantrocket.master import create_universe
>>> create_universe("asx", from_universes=["asx-sml", "asx-mid", "asx-lrg"])
{'code': 'asx',
 'inserted': 1604,
 'provided': 1604,
 'total_after_insert': 1604}

$ curl -X PUT 'http://houston/master/universes/asx?from_universes=asx-sml&from_universes=asx-mid&from_universes=asx-lrg'
{"code": "asx", "provided": 1604, "inserted": 1604, "total_after_insert": 1604}

$ quantrocket master get --exchanges 'XCME' --symbols 'ES' --sec-types 'FUT' | quantrocket master universe 'es-fut' --infile - --append
code: es-fut
inserted: 22
provided: 34
total_after_insert: 34

>>> download_master_file("es_fut.csv", exchanges="XCME", symbols="ES", sec_types="FUT")
>>> create_universe("es-fut", infilepath_or_buffer="es_fut.csv", append=True)
{'code': 'es-fut',
 'provided': 34,
 'inserted': 22,
 'total_after_insert': 34}

$ curl -X GET 'http://houston/master/securities.csv?exchanges=XCME&sec_types=FUT&symbols=ES' > es_fut.csv
$ curl -X PATCH 'http://houston/master/universes/es-fut' --upload-file es_fut.csv
{"code": "es-fut", "provided": 34, "inserted": 22, "total_after_insert": 34}

$ quantrocket master list-universes
arca-etf: 1267
asx-stk: 2387
es-fut: 34
usa-stk: 6518

>>> from quantrocket.master import list_universes
>>> list_universes()
{'arca-etf': 1267,
 'asx-stk': 2387,
 'es-fut': 34,
 'usa-stk': 6518}

curl -X GET 'http://houston/master/universes'
{"arca-etf": 1267, "asx-stk": 2387, "es-fut": 34, "usa-stk": 6518}

$ quantrocket master delete-universe 'es-fut'
code: es-fut
deleted: 34

>>> from quantrocket.master import delete_universe
>>> delete_universe("es-fut")
{"code": "es-fut",
"deleted": 34}

$ curl -X DELETE 'http://houston/master/universes/es-fut'
{"code": "es-fut", "deleted": 34}

Maintain listings

While securities master fields are relatively static, they do sometimes change. Stocks change ticker symbols or switch exchanges or are delisted. Although such changes do not affect a security's Sid, it's still a good idea to keep your securities master database up-to-date, especially as you transition from researching to trading.

To update the securities master database, simply collect the listings again.

Delist IBKR stocks

For most data vendors, you can keep the Delisted and DateDelisted fields up-to-date simply by re-collecting the listings from time to time. However, Interactive Brokers is a special case, because when stocks are delisted, Interactive Brokers removes them from its system. Thus, if you want the Delisted and DateDelisted fields in the securities master database to be accurate, you cannot simply re-collect the listings with the updated fields, since they are no longer available to collect.

To delist IBKR stocks, you can use the command quantrocket master diff-ibkr. This command queries the IBKR API and compares securities as stored in the local database with the securities as reflected in IBKR's system. This command can be used to flag changes to fields (such as ibkr_PrimaryExchange) and can also be used to detect securities that have been removed from IBKR's system.

A good way to use this command is to schedule it to run weekly on your countdown service crontab, as shown in the example below:

# delist IBKR stocks once a week on Sunday
0 5 * * sun quantrocket ibg start --wait && quantrocket master get --sec-types 'STK' 'ETF' --vendors 'ibkr' --fields 'Sid' --exclude-delisted | quantrocket master diff-ibkr --infile - --fields 'ibkr_ConId' --delist-missing --delist-exchanges 'VALUE'

The explanation of the command is as follows:

• 0 5 * * sun: run the command on Sundays at 5 AM
• quantrocket ibg start --wait: start IB Gateway
• quantrocket master get --sec-types 'STK' 'ETF' --vendors 'ibkr' --fields 'Sid' --exclude-delisted: download a CSV of all IBKR stocks and ETFs that are not already marked as delisted
• | quantrocket master diff-ibkr --infile -: query the IBKR API for each security in the downloaded CSV file
• --fields 'ibkr_ConId': only flag differences in the ibkr_ConId field; this avoids the potential for noisy output
• --delist-missing: delist securities that are no longer available from IBKR
• --delist-exchanges 'VALUE': delist securities associated with the 'VALUE' exchange (IBKR uses the "VALUE" exchange as a placeholder for some delisted symbols)

Historical Price Data

Data collection overview

Historical data collection follows a common workflow for all data providers:

1. Create an empty database that defines your historical data requirements (vendor, bar size, securities, etc.)
2. Collect data from the data provider and store in the local database. The data will be collected according to the requirements you originally defined.
3. Periodically collect data again to obtain updated history.
4. Query data from the local database for use in your analysis and trading strategies.

You can create as many databases as you need.

Create history database

Create a database by choosing the vendor to use and defining the data collection parameters, which vary by vendor. You assign each database an alphanumeric code for easy reference. In this example, we create an end-of-day database for free sample US stock data:

$ quantrocket history create-usstock-db 'usstock-free-1d' --bar-size '1 day' --universe 'FREE'
status: successfully created quantrocket.v2.history.usstock-free-1d.sqlite

>>> from quantrocket.history import create_usstock_db
>>> create_usstock_db("usstock-free-1d", bar_size="1 day", universe="FREE")
{'status': 'successfully created quantrocket.v2.history.usstock-free-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/usstock-free-1d?vendor=usstock&bar_size=1+day&universe=FREE'
{"status": "successfully created quantrocket.v2.history.usstock-free-1d.sqlite"}

$ quantrocket history config 'usstock-free-1d'
bar_size: 1 day
fields:
- Symbol
- Open
- High
- Low
- Close
- Volume
- Vwap
- TotalTrades
- UnadjOpen
- UnadjHigh
- UnadjLow
- UnadjClose
- UnadjVolume
- UnadjVwap
shard: year
universe: FREE
vendor: usstock

>>> from quantrocket.history import get_db_config
>>> get_db_config("usstock-free-1d")
{'vendor': 'usstock',
 'universe': 'FREE',
 'bar_size': '1 day',
 'shard': 'year',
 'fields': ['Symbol',
  'Open',
  'High',
  'Low',
  'Close',
  'Volume',
  'Vwap',
  'TotalTrades',
  'UnadjOpen',
  'UnadjHigh',
  'UnadjLow',
  'UnadjClose',
  'UnadjVolume',
  'UnadjVwap']}

$ curl -X GET 'http://houston/history/databases/usstock-free-1d'
{"vendor": "usstock", "universe": "FREE", "bar_size": "1 day", "shard": "year", "fields": ["Symbol", "Open", "High", "Low", "Close", "Volume", "Vwap", "TotalTrades", "UnadjOpen", "UnadjHigh", "UnadjLow", "UnadjClose", "UnadjVolume", "UnadjVwap"]}

$ quantrocket history list
es-fut-1min
japan-stk-1d
uk-etf-15min
usstock-free-1d
usstock-1d

>>> from quantrocket.history import list_databases
>>> list_databases()
['es-fut-1min',
'japan-stk-1d',
'uk-etf-15min',
'usstock-free-1d',
'usstock-1d']

$ curl -X GET 'http://houston/history/databases'
["es-fut-1min", "japan-stk-1d", "uk-etf-15min", "usstock-free-1d", "usstock-1d"]

$ quantrocket history collect 'usstock-free-1d'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("usstock-free-1d")
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=usstock-free-1d'
{"status": "the historical data will be collected asynchronously"}

Data collection runs in the background. Progress is logged to flightlog, which you should monitor for completion status:

$ quantrocket flightlog stream
quantrocket.history: INFO [usstock-free-1d] Collecting FREE history from 2007-01 to present
quantrocket.history: INFO [usstock-free-1d] Collecting updated FREE securities listings
quantrocket.history: INFO [usstock-free-1d] Applying price adjustments for 6 securities
quantrocket.history: INFO [usstock-free-1d] Collected 160 monthly files in quantrocket.v2.history.usstock-free-1d.sqlite

Later, to bring the database current with new data, simply run data collection again. The update process will run faster than the initial data collection due to collecting fewer records.

You can use the countdown service to schedule your databases to be updated regularly.

Data collection queue

Multiple data collection requests will be queued and run sequentially. You can view the current queue, which is organized by vendor:

$ quantrocket history queue
edi: []
ibkr:
  priority: []
  standard: []
sharadar: []
usstock:
- usstock-free-1d

>>> quantrocket.history import get_history_queue
>>> get_history_queue()
{'edi': [],
 'sharadar': [],
 'usstock': ['usstock-free-1d'],
 'ibkr': {'priority': [], 'standard': []}}

$ curl -X GET 'http://houston/history/queue'
{"edi": [], "sharadar": [], "usstock": ["usstock-free-1d"], "ibkr": {"priority": [], "standard": []}}

Delete history database

Once you've created a database, you can't edit the configuration; you can only add new databases. If you made a mistake or no longer need an old database, you can drop the database and its associated config:

$ quantrocket history drop-db 'usstock-free-1d' --confirm-by-typing-db-code-again 'usstock-free-1d'
status: deleted quantrocket.v2.history.usstock-free-1d.sqlite

>>> from quantrocket.history import drop_db
>>> drop_db("usstock-free-1d", confirm_by_typing_db_code_again="usstock-free-1d")
{'status': 'deleted quantrocket.v2.history.usstock-free-1d.sqlite'}

$ curl -X DELETE 'http://houston/history/databases/usstock-free-1d?confirm_by_typing_db_code_again=usstock-free-1d'
{"status": "deleted quantrocket.v2.history.usstock-free-1d.sqlite"}

Historical data file

The most convenient way to load historical data into Python is using the get_prices function, which parses the data into a Pandas DataFrame and works for history databases, real-time aggregate databases, and Zipline bundles. This function is outlined in the Research section.

Alternatively, for a more raw approach, you can download a CSV file of historical data:

$ quantrocket history get 'usstock-free-1d' --start-date '2020-01-01' --fields 'Open' 'High' 'Low' 'Close' 'Volume' 'Vwap' | csvlook --max-rows 5
| Sid            |       Date |     Open |     High |      Low |    Close |     Volume |     Vwap |
| -------------- | ---------- | -------- | -------- | -------- | -------- | ---------- | -------- |
| FIBBG000GZQ728 | 2020-01-02 |  69.246… |  70.015… |  69.243… |  69.896… | 12,681,101 |  69.771… |
| FIBBG000BPH459 | 2020-01-02 | 158.348… | 160.292… | 157.899… | 160.182… | 22,634,546 | 159.341… |
| FIBBG000BMHYD1 | 2020-01-02 | 144.946… | 145.095… | 144.161… | 145.045… |  5,769,137 | 144.702… |
| FIBBG000B9XRY4 | 2020-01-02 | 295.539… | 299.888… | 294.491… | 299.639… | 33,911,864 | 297.733… |
| FIBBG00B3T3HD3 | 2020-01-02 |  21.860… |  21.860… |  21.315… |  21.420… |  3,097,556 |  21.474… |
| ...            |        ... |      ... |      ... |      ... |      ... |        ... |      ... |

>>> import pandas as pd
>>> from quantrocket.history import download_history_file
>>> download_history_file("usstock-free-1d",
                          start_date="2020-01-01",
                          fields=["Open", "High", "Low", "Close", "Volume", "Vwap"],
                          filepath_or_buffer="usstock_free_1d.csv")
>>> prices = pd.read_csv("usstock_free_1d.csv", parse_dates=["Date"])
>>> prices.head()
              Sid       Date      Open      High       Low     Close    Volume      Vwap
0  FIBBG000GZQ728 2020-01-02   69.2459   70.0148   69.2427   69.8965  12681101   69.7712
1  FIBBG000BPH459 2020-01-02  158.3475  160.2922  157.8987  160.1825  22634546  159.3413
2  FIBBG000BMHYD1 2020-01-02  144.9457  145.0948  144.1607  145.0451   5769137  144.7020
3  FIBBG000B9XRY4 2020-01-02  295.5386  299.8883  294.4911  299.6389  33911864  297.7330
4  FIBBG00B3T3HD3 2020-01-02   21.8600   21.8600   21.3150   21.4200   3097556   21.4739

$ curl -X GET 'http://houston/history/usstock-free-1d.csv?start_date=2020-01-01&fields=Open&fields=High&fields=Low&fields=Close&fields=Volume&fields=Vwap' | head
FIBBG000GZQ728,2020-01-02,69.2459,70.0148,69.2427,69.8965,12681101,69.7712
FIBBG000BPH459,2020-01-02,158.3475,160.2922,157.8987,160.1825,22634546,159.3413
FIBBG000BMHYD1,2020-01-02,144.9457,145.0948,144.1607,145.0451,5769137,144.702
FIBBG000B9XRY4,2020-01-02,295.5386,299.8883,294.4911,299.6389,33911864,297.733
FIBBG00B3T3HD3,2020-01-02,21.86,21.86,21.315,21.42,3097556,21.4739

EDI

To collect EDI price data, create a database by specifying one or more MICs (market identifier codes) to include in the database (for sample data, use the exchange code FREE). This example creates a database that includes prices from the Shanghai Stock Exchange (XSHG) and Shenzhen Stock Exchange (XSHE):

$ quantrocket history create-edi-db 'china-1d' --exchanges 'XSHG' 'XSHE'
status: successfully created quantrocket.v2.history.china-1d.sqlite

>>> from quantrocket.history import create_edi_db
>>> create_edi_db("china-1d", exchanges=["XSHG", "XSHE"])
{'status': 'successfully created quantrocket.v2.history.china-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/china-1d?vendor=edi&exchanges=XSHG&exchanges=XSHE'
{"status": "successfully created quantrocket.v2.history.china-1d.sqlite"}

$ quantrocket history collect 'china-1d'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("china-1d")
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=china-1d'
{"status": "the historical data will be collected asynchronously"}

Monitor the status in flightlog:

quantrocket.history: INFO [china-1d] Collecting EDI XSHG history from 2007-01 to present
quantrocket.history: INFO [china-1d] Collecting updated EDI XSHG securities listings
quantrocket.history: INFO [china-1d] Collecting EDI XSHE history from 2007-01 to present
quantrocket.history: INFO [china-1d] Collecting updated EDI XSHE securities listings
quantrocket.history: INFO [china-1d] Applying price adjustments for 3648 securities
quantrocket.history: INFO [china-1d] Collected 320 monthly files in quantrocket.v2.history.china-1d.sqlite

For EDI databases, QuantRocket loads the raw prices and adjustments, then applies the adjustments in your local database. This design is optimized for efficiently collecting new data on an ongoing basis. However, the first time data is collected, applying adjustments can take awhile for large exchanges. For this reason, pre-built databases with adjustments already applied are available for select exchanges; QuantRocket will automatically check if this is the case.

EDI data guide

A sample record from the dataset is shown below:

Sid: "FIBBG000Q13NZ6"
Date: "2020-04-07"
Symbol: 510010
Open: 1.077
High: 1.105
Low: 1.077
Close: 1.093
Mid: 0
Ask: 1.093
Last: 0
Bid: 1.082
BidSize: 0
AskSize: 0
Volume: 86100
TradedValue: 93110
TotalTrades: 0
UnadjOpen: 1.077
UnadjHigh: 1.105
UnadjLow: 1.077
UnadjClose: 1.093
UnadjMid: 0
UnadjAsk: 1.093
UnadjLast: 0
UnadjBid: 1.082
UnadjVolume: 86100
Confirmed: 1

Note: VWAP can easily be calculated as TradedValue / Volume. (For unadjusted VWAP, use TradedValue / UnadjVolume.)

Split and dividend adjustments

EDI price data is split- and dividend-adjusted.

Primary vs consolidated prices

EDI price data is from the primary exchange.

Learn more about the difference between consolidated and primary exchange prices.

Delisted stocks

EDI price data includes stocks that delisted due to bankruptcies, mergers and acquisitions, etc.

Update schedule

EDI is updated on a rolling basis as the data becomes available from the exchange.

Point-in-time ticker symbols

There is a Symbol column in the EDI price data as well as a Symbol column (and edi_LocalSymbol column) in the securities master file. The Symbol column in the price data contains the ticker code provided by the exchange, while the Symbol/edi_LocalSymbol column in the securities master file contains the canonical ticker for the security as determined by EDI. Usually these are the same but sometimes they may differ. In addition, the price data Symbol column is point-in-time, that is, it does not change even if the security subsequently undergoes a ticker change. In contrast, the securities master Symbol/edi_LocalSymbol columns always reflect the security's latest ticker symbol.

Interactive Brokers

To collect historical data from Interactive Brokers, you must first collect securities master listings from Interactive Brokers. It is not sufficient to have collected the listings from another vendor; specific IBKR fields must be present in the securities master database. To check if you have collected IBKR listings, query the securities master and make sure the ibkr_ConId field is populated:

$ quantrocket master get --symbols 'AAPL' --fields 'Symbol' 'ibkr_ConId' | csvlook -I
| Sid            | Symbol | ibkr_ConId |
| -------------- | ------ | ---------- |
| FIBBG000B9XRY4 | AAPL   | 265598     |

>>> from quantrocket.master import download_master_file
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_master_file(f, symbols="AAPL",
                        fields=["Symbol", "ibkr_ConId"])
>>> securities = pd.read_csv(f)
>>> securities.head()
              Sid Symbol  ibkr_ConId
0  FIBBG000B9XRY4   AAPL      265598

$ curl -X GET 'http://houston/master/securities.csv?symbols=AAPL&fields=Symbol&fields=ibkr_ConId' | csvlook -I
| Sid            | Symbol | ibkr_ConId |
| -------------- | ------ | ---------- |
| FIBBG000B9XRY4 | AAPL   | 265598     |

$ quantrocket history create-ibkr-db 'japan-bank-eod' --universes 'japan-bank' --bar-size '1 day'
status: successfully created quantrocket.v2.history.japan-bank-eod.sqlite

>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("japan-bank-eod", universes=["japan-bank"], bar_size="1 day")
{'status': 'successfully created quantrocket.v2.history.japan-bank-eod.sqlite'}

$ curl -X PUT 'http://houston/history/databases/japan-bank-eod?universes=japan-bank&bar_size=1+day&vendor=ibkr'
{"status": "successfully created quantrocket.v2.history.japan-bank-eod.sqlite"}

$ quantrocket history collect 'japan-bank-eod'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("japan-bank-eod")
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=japan-bank-eod'
{"status": "the historical data will be collected asynchronously"}

QuantRocket will first query the IBKR API to determine how far back historical data is available for each security, then query the IBKR API again to collect the data for that date range. Depending on the bar size and the number of securities in the universe, collecting data can take from several minutes to several hours. If you're running multiple IB Gateway services, QuantRocket will spread the requests among the services to speed up the process. Based on how quickly the IBKR API is responding to requests, QuantRocket will periodically estimate how long it will take to collect the data. Monitor flightlog to track progress:

$ quantrocket flightlog stream
quantrocket.history: INFO [japan-bank-eod] Determining how much history is available from IBKR for japan-bank-eod
quantrocket.history: INFO [japan-bank-eod] Collecting history from IBKR for japan-bank-eod
quantrocket.history: INFO [japan-bank-eod] Expected remaining runtime to collect japan-bank-eod history based on IBKR response times so far: 0:23:11
quantrocket.history: INFO [japan-bank-eod] Saved 468771 total records for 85 total securities to quantrocket.v2.history.japan-bank-eod.sqlite

In addition to bar size and universe(s), you can optionally define the type of data you want (for example, trades, bid/ask, midpoint, etc.), a fixed start date instead of "as far back as possible", whether to include trades from outside regular trading hours, whether to use consolidated prices or primary exchange prices, and more. For a complete list of options, view the command or function help or the API Reference.

Cancel collections

Because IBKR historical data collection can be long-running, there is support for canceling a pending or running collection:

$ quantrocket history cancel 'japan-bank-eod'
edi: []
ibkr:
  priority: []
  standard: []
sharadar: []
usstock: []

>>> from quantrocket.history import cancel_collections
>>> cancel_collections(codes="japan-bank-eod")
{'edi': [],
 'sharadar': [],
 'usstock': [],
 'ibkr': {'priority': [], 'standard': []}}

$ curl -X DELETE 'http://houston/history/queue?codes=japan-bank-eod'
{"edi": [], "sharadar": [], "usstock": [], "ibkr": {"priority": [], "standard": []}}

The output returns the data collection queue after cancellation.

Priority queue

Due to rate limits on data collection enforced by the IBKR API, only one IBKR data collection can run at a time (additional requests will be queued). To maximize flexibility, there is a standard queue and a priority queue for Interactive Brokers. The standard queue will only be processed when the priority queue is empty. This can be useful when you're trying to collect a large amount of historical data for backtesting but you don't want it to interfere with daily updates to the databases you use for trading. First, schedule your daily updates on your countdown (cron) service, using the --priority flag to route them to the priority queue:

# collect some futures data each weekday at 5:30 pm
30 17 * * mon-fri quantrocket history collect --priority 'es-fut-1min'

Then, queue your long-running requests on the standard queue:

$ quantrocket history collect 'asx-stk-15min'

At 5:30pm, when a request is queued on the priority queue, the long-running request on the standard queue will pause until the priority queue is empty again, and then resume.

IBKR data guide

Split adjustments

All IBKR historical data is split-adjusted.

If a split occurs after the initial data collection, the locally stored data needs to be adjusted for the split. QuantRocket handles this by comparing a recent price in the database to the equivalently-timestamped price from IBKR. If the prices differ, this indicates either that a split has occurred or in some other way the vendor has adjusted their data since QuantRocket stored it. Regardless of the reason, QuantRocket deletes the data for that particular security and re-collects the entire history from IBKR, in order to make sure the database stays synced with IBKR.

Dividend adjustments

By default, IBKR historical data is not dividend-adjusted. However, dividend-adjusted data is available using the ADJUSTED_LAST bar type. This bar type has an important limitation: it is only available with a 1 day bar size.

$ quantrocket history create-ibkr-db 'asx-stk-1d' --universes 'asx-stk' --bar-size '1 day' --bar-type 'ADJUSTED_LAST'
status: successfully created quantrocket.v2.history.asx-stk-1d.sqlite

>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("asx-stk-1d", universes=["asx-stk"], bar_size="1 day", bar_type="ADJUSTED_LAST")
{'status': 'successfully created quantrocket.v2.history.asx-stk-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/asx-stk-1d?universes=asx-stk&bar_size=1+day&bar_type=ADJUSTED_LAST&vendor=ibkr'
{"status": "successfully created quantrocket.v2.history.us-stk-1d.sqlite"}

With ADJUSTED_LAST, QuantRocket handles dividend adjustments in the same way it handles split adjustments: whenever IBKR applies a dividend adjustment, QuantRocket will detect the discrepancy between the IBKR data and the locally stored data, and will delete the stored data and re-sync with IBKR.

Primary vs consolidated prices

By default, IBKR returns consolidated prices for equities. You can instruct QuantRocket to collect primary exchange prices instead of consolidated prices using the --primary-exchange option. This instructs IBKR to filter out trades that didn't take place on the primary listing exchange for the security:

$ quantrocket history create-ibkr-db 'us-stk-1d-primary' --universes 'us-stk' --bar-size '1 day' --primary-exchange
status: successfully created quantrocket.v2.history.us-stk-1d-primary.sqlite

>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("us-stk-1d-primary", universes=["us-stk"], bar_size="1 day", primary_exchange=True)
{'status': 'successfully created quantrocket.v2.history.us-stk-1d-primary.sqlite'}

$ curl -X PUT 'http://houston/history/databases/us-stk-1d-primary?universes=us-stk&bar_size=1 day&primary_exchange=true&vendor=ibkr'
{"status": "successfully created quantrocket.v2.history.us-stk-1d-primary.sqlite"}

Learn more about the tradeoffs between consolidated and primary exchange prices.

Bar sizes

IBKR offers over 20 bar sizes ranging from 1 month to 1 second. The full list includes: 1 month, 1 week, 1 day, 8 hours, 4 hours, 3 hours, 2 hours, 1 hour, 30 mins, 20 mins, 15 mins, 10 mins, 5 mins, 3 mins, 2 mins, 1 min, 30 secs, 15 secs, 10 secs, 5 secs, and 1 secs.

Types of data

You can use the --bar-type parameter with create-ibkr-db to indicate what type of historical data you want:

If --bar-type is omitted, it defaults to MIDPOINT for FX and TRADES for everything else.

How far back historical data goes

For stocks and currencies, IBKR historical data depth varies by exchange and bar size. End of day prices go back as far as 1980 for some exchanges, while intraday prices down to 1-minute bars go back as far as 2004. The amount of data available from the IBKR API is the same as the amount of data available when viewing the corresponding chart in Trader Workstation.

For futures, historical data is available for contracts that expired no more than 2 years ago. IBKR removes historical futures data from its system 2 years after the contract expiration date. Deeper historical data is available for indices. Thus, for futures contracts with a corresponding index (and for which backwardation and contango are negligible factors), you can run deeper backtests on the index then switch to the futures contract for recent backtests or live trading.

For bar sizes of 30 seconds or smaller, historical data goes back 6 months only.

Intraday data collection

Initial data collection runtime

Depending on the bar size, number of securities, and date range of your historical database, initial data collection from the IBKR API can take some time. After the initial data collection, keeping your database up to date is much faster and much easier.

QuantRocket fills your historical database by making a series of requests to the IBKR API to get a portion of the data, from earlier data to later data. The smaller the bars, the more requests are required to collect all the data.

If you run multiple IB Gateways, each with appropriate IB market data subscriptions, QuantRocket splits the requests between the gateways which results in a proportionate reduction in runtime.

IBKR API response times also vary by the monthly commissions generated on the account. Accounts with monthly commissions of several thousand USD/month or higher will see response times which are about twice as fast as those for small accounts (or for large accounts with small commissions).

The following table shows estimated runtimes and database sizes for a variety of historical database configurations:

You can use the table above to infer the collection times for other bar sizes and universe sizes.

Data collection strategies

Below are several data collection strategies that may help speed up data collection, reduce the amount of data you need to collect, or allow you to begin working with a subset of data while collecting the full amount of data.

Run multiple IB Gateways

You can cut down initial data collection time by running multiple IB gateways. See the section on obtaining and using multiple IB logins.

Daily bars before intraday bars

Suppose you want to collect intraday bars for the top 500 liquid securities trading on ASX. Instead of collecting intraday bars for all ASX securities then filtering out illiquid ones, you could try this approach:

• collect a year's worth of daily bars for all ASX securities (this requires only 1 request to the IBKR API per security and will run much faster than collecting multiple years of intraday bars)
• in a notebook, query the daily bars and use them to calculate dollar volume, then create a universe of liquid securities only
• collect intraday bars for the universe of liquid securities only

You can periodically repeat this process to update the universe constituents.

Filter by availability of fundamentals

Suppose you have a strategy that requires intraday bars and fundamental data and utilizes a universe of small-cap stocks. For some small-cap stocks, fundamental data might not be available, so it doesn't make sense to spend time collecting intraday historical data for stocks that won't have fundamental data. Instead, collect the fundamental data first and filter your universe to stocks with fundamentals, then collect the historical intraday data. For example:

• create a universe of all Japanese small-cap stocks called 'japan-sml'
• collect fundamentals for the universe 'japan-sml'
• in a notebook, query the fundamentals for 'japan-sml' and use the query results to create a new universe called 'japan-sml-with-fundamentals'
• collect intraday price history for 'japan-sml-with-fundamentals'

Earlier history before later history

Suppose you want to collect numerous years of intraday bars. But you'd like to test your ideas on a smaller date range first in order to decide if collecting the full history is worthwhile. This can be done as follows. First, define your desired start date when you create the database:

$ quantrocket history create-ibkr-db 'hong-kong-liquid-15min' -u 'hong-kong-liquid' -z '15 mins' -s '2011-01-01'

The above database is designed to collect data back to 2011-01-01 and up to the present. However, you can temporarily specify an end date when collecting the data:

$ quantrocket history collect 'hong-kong-liquid-15min' -e '2012-01-01'

In this example, only a year of data will be collected (that is, from the start date of 2011-01-01 specified when the database was created to the end date of 2012-01-01 specified in the above command). That way you can start your research sooner. Later, you can repeat this command with a later end date or remove the end date entirely to bring the database current.

In contrast, it's a bad idea to use a temporary start date to shorten the date range and speed up the data collection, with the intention of going back later to get the earlier data. Since data is filled from back to front (that is, from older dates to newer), once you've collected a later portion of data for a given security, you can't append an earlier portion of data without starting over.

Database per decade

Data for some securities goes back 30 years or more. After testing on recent data, you might want to explore earlier years. While you can't append earlier data to an existing database, you can collect the earlier data in a completely separate database. Depending on your bar size and universe size, you might create a separate database for each decade. These databases would be for backtesting only and, after the initial data collection, would not need to be updated. Only your database of the most recent decade would need to be updated.

Small universes before large universes

Another option to get you researching and backtesting sooner is to collect a subset of your target universe before collecting the entire universe. For example, instead of collecting intraday bars for 1000 securities, collect bars for 100 securities and start testing with those while collecting the remaining data.

Time filters

When creating a historical database of intraday bars, you can use the times or between-times options to filter out unwanted bars.

For example, it's usually a good practice to explicitly specify the session start and end times, as the IBKR API sometimes sends a small number of bars from outside regular trading hours, and any trading activity from these bars will be included in the cumulative daily totals calculated by QuantRocket. The following command instructs QuantRocket to keep only those bars that fall between 9:00 and 14:45, inclusive. (Note that bar times correspond to the start of the bar, so the final bar for Japan stocks using 15-min bars would be 14:45:00, since the Tokyo Stock Exchange closes at 15:00.)

$ quantrocket history create-ibkr-db 'japan-stk-15min' --universes 'japan-stk' --bar-size '15 mins' --between-times '09:00:00' '14:45:00'--shard 'time'
status: successfully created quantrocket.v2.history.japan-stk-15min.sqlite

>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("japan-stk-15min", universes=["japan-stk"], bar_size="15 mins", between_times=["09:00:00", "14:45:00"], shard="time")
{'status': 'successfully created quantrocket.v2.history.japan-stk-15min.sqlite'}

$ curl -X PUT 'http://houston/history/databases/japan-stk-15min?universes=japan-stk&bar_size=15+mins&between_times=09%3A00%3A00&between_times=14%3A45%3A00&shard=time&vendor=ibkr'
{"status": "successfully created quantrocket.v2.history.japan-stk-15min.sqlite"}

$ quantrocket history config 'japan-stk-15min'
bar_size: 15 mins
fields:
- Open
- High
- Low
- Close
- Volume
- Wap
- TradeCount
- DayHigh
- DayLow
- DayVolume
shard: time
times:
- '09:00:00'
- '09:15:00'
- '09:30:00'
- '09:45:00'
- '10:00:00'
- '10:15:00'
- '10:30:00'
- '10:45:00'
- '11:00:00'
- '11:15:00'
- '11:30:00'
- '11:45:00'
- '12:00:00'
- '12:15:00'
- '12:30:00'
- '12:45:00'
- '13:00:00'
- '13:15:00'
- '13:30:00'
- '13:45:00'
- '14:00:00'
- '14:15:00'
- '14:30:00'
- '14:45:00'
universes:
- japan-stk
vendor: ibkr

>>> from quantrocket.history import get_db_config
>>> get_db_config("japan-stk-15min")
{'universes': ['japan-stk'],
 'vendor': 'ibkr',
 'bar_size': '15 mins',
 'shard': 'time',
 'times': ['09:00:00',
  '09:15:00',
  '09:30:00',
  '09:45:00',
  '10:00:00',
  '10:15:00',
  '10:30:00',
  '10:45:00',
  '11:00:00',
  '11:15:00',
  '11:30:00',
  '11:45:00',
  '12:00:00',
  '12:15:00',
  '12:30:00',
  '12:45:00',
  '13:00:00',
  '13:15:00',
  '13:30:00',
  '13:45:00',
  '14:00:00',
  '14:15:00',
  '14:30:00',
  '14:45:00'],
 'fields': ['Open',
  'High',
  'Low',
  'Close',
  'Volume',
  'Wap',
  'TradeCount',
  'DayHigh',
  'DayLow',
  'DayVolume']}

$ curl 'http://houston/history/databases/japan-stk-15min'
{"universes": ["japan-stk"], "vendor": "ibkr", "bar_size": "15 mins", "shard": "time", "times": ["09:00:00", "09:15:00", "09:30:00", "09:45:00", "10:00:00", "10:15:00", "10:30:00", "10:45:00", "11:00:00", "11:15:00", "11:30:00", "11:45:00", "12:00:00", "12:15:00", "12:30:00", "12:45:00", "13:00:00", "13:15:00", "13:30:00", "13:45:00", "14:00:00", "14:15:00", "14:30:00", "14:45:00"], "fields": ["Open", "High", "Low", "Close", "Volume", "Wap", "TradeCount", "DayHigh", "DayLow", "DayVolume"]}

$ quantrocket history create-ibkr-db 'japan-stk-15min' --universes 'japan-stk' --bar-size '15 mins' --times '09:00:00' '09:15:00' '10:00:00' '14:45:00' --shard 'time'
status: successfully created quantrocket.v2.history.japan-stk-15min.sqlite

>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("japan-stk-15min", universes=["japan-stk"], bar_size="15 mins", times=["09:00:00", "09:15:00", "10:00:00", "14:45:00"], shard="time")
{'status': 'successfully created quantrocket.v2.history.japan-stk-15min.sqlite'}

$ curl -X PUT 'http://houston/history/databases/japan-stk-15min?universes=japan-stk&bar_size=15+mins&times=09%3A00%3A00&times=09%3A15%3A00&times=10%3A00%3A00&times=14%3A45%3A00&shard=time&vendor=ibkr'
{"status": "successfully created quantrocket.v2.history.japan-stk-15min.sqlite"}

The downside of keeping only a few times is that you'll have to collect data again if you later decide you want to analyze prices at other times of the session. An alternative is to save all the times but filter by time when querying the data.

Database sharding

Summary of sharding options

More detailed descriptions are provided below.

What is sharding?

In database design, "sharding" refers to dividing a large database into multiple smaller databases, with each smaller database or "shard" containing a subset of the total database rows. A collection of database shards typically performs better than a single large database by allowing more efficient queries. When a query is run, the rows from each shard are combined into a single result set as if they came from a single database.

When you query a sharded database using a filter that corresponds to the sharding scheme (for example, filtering by time for a time-sharded database, or filtering by sid for a sid-sharded database), the query runs faster because it only needs to look in the subset of relevant shards based on the query parameters.

To get the benefit of improved query performance, the sharding scheme must correspond to how you will usually query the database; thus it is necessary to think about this in advance.

A secondary benefit of sharding is that smaller database files are easier to move around, including copying them to and from S3.

Choose sharding option

For intraday databases, you must indicate your sharding option at the time you create the database:

$ # shard by sid and time
$ quantrocket history create-ibkr-db 'uk-stk-15min' --universes 'uk-stk' --bar-size '15 mins' --shard 'sid,time'
status: successfully created quantrocket.v2.history.uk-stk-15min.sqlite

>>> # shard by sid and time
>>> from quantrocket.history import create_ibkr_db
>>> create_ibkr_db("uk-stk-15min", universes=["uk-stk"], bar_size="15 mins", shard="sid,time")
{'status': 'successfully created quantrocket.v2.history.uk-stk-15min.sqlite'}

$ # shard by sid and time
$ curl -X PUT 'http://houston/history/databases/uk-stk-15min?universes=uk-stk&bar_size=15%20mins&shard=sid,time'
{"status": "successfully created quantrocket.v2.history.uk-stk-15min.sqlite"}

The choices are:

• year
• month
• day
• time
• sid
• sid,time
• off

Sharded database storage

If you list a sharded database using the --expand/expand=True parameter, you'll see a separate database file for each time or sid shard:

$ # sharded by time
$ quantrocket db list --services 'history' --codes 'uk-stk-15min' --expand
quantrocket.v2.history.uk-stk-15min.093000.sqlite
quantrocket.v2.history.uk-stk-15min.094500.sqlite
...
$ # sharded by sid
$ quantrocket db list --services 'history' --codes 'uk-stk-1min' --expand
quantrocket.v2.history.uk-stk-1min.100248135.sqlite
quantrocket.v2.history.uk-stk-1min.100296007.sqlite
quantrocket.v2.history.uk-stk-1min.100296028.sqlite
...

Shard by year, month, or day

Sharding by year, month, or day results in a separate database shard for each year, month, or day of data, with each separate database containing all securities for only that time period. The number of shards is equal to the number of years, months, or days of data collected, respectively.

As a broad guideline, if collecting 1-minute bars, sharding by year would be suitable for a universe of tens of securities, sharding by month would be suitable for a universe of hundreds of securities, and sharding by day would be suitable for a universe of thousands of securities.

Sharding by year, month, or day is a sensible approach when you need to analyze the entire universe of securities but only for a small date range at a time. This approach pairs well with segmented backtests in Moonshot.

Shard by time

Sharding by time results in a separate database shard for each time of day. For example, assuming 15-minute bars, there will be a separate database for 09:30:00 bars, 09:45:00 bars, etc. (with each separate database containing all dates and all securities for only that bar time). The number of shards is equal to the number of bar times per day.

Sharding by time is an efficient approach when you are working with a large universe of securities but only need to query a handful of times for any given analysis. For example, the following query would run efficiently on a time-sharded database because it only needs to look in 3 shards:

>>> prices = get_prices("uk-stk-15min", times=["09:30:00", "12:00:00", "15:45:00"])

Sharding by time is well-suited to intraday Moonshot strategies that trade once a day, since such strategies typically only utilize a subset of bar times.

Sharding by sid

Sharding by sid results in a separate database shard for each security. Each shard will contain the entire date range and all bar times for a single security. The number of shards is equal to the number of securities in the universe.

Sharding by sid is an efficient approach when you need to query bars for all times of day but can do so for one or a handful of securities at a time. For example, the following query would run efficiently on a sid-sharded database because it only needs to look in 1 shard:

>>> bp_prices = get_prices("uk-stk-1min", sids="FIBBG000C059M6")

Sharding by sid is well-suited for ingesting data into Zipline for backtesting because Zipline ingests data one security at a time.

Sharding by sid and time

Sharding by sid and time results in duplicate copies of the database, one sharded by time and one by sid. QuantRocket will look in whichever copy of the database allows for the most efficient query based on your query parameters, that is, whichever copy allows looking in the fewest number of shards. For example, if you query prices at a few times of day for many securities, QuantRocket will use the time-sharded database to satisfy your request; if you query prices for many times of day for a few securities, QuantRocket will use the sid-sharded database to satisfy your request:

>>> # this query will look in 3 time shards:
>>> #  - quantrocket.v2.history.uk-stk-15min.094500.sqlite
>>> #  - quantrocket.v2.history.uk-stk-15min.120000.sqlite
>>> #  - quantrocket.v2.history.uk-stk-15min.154500.sqlite
>>> prices = get_prices("uk-stk-15min", times=["09:30:00", "12:00:00", "15:45:00"])
>>> # this query will look in 2 sid shards:
>>> #  - quantrocket.v2.history.uk-stk-15min.FIBBG000C059M6.sqlite
>>> #  - quantrocket.v2.history.uk-stk-15min.FIBBG000BF46K3.sqlite
>>> prices = get_prices("usa-stk-15min", sids=["FIBBG000C059M6", "FIBBG000BF46K3"])

Sharding by time and by sid allows for more flexible querying but requires double the disk space. It may also increase collection runtime due to the larger volume of data that must be written to disk.

Sharadar

To collect Sharadar price data, specify the security type (STK or ETF) and the country (US for the full dataset, or FREE for sample data):

$ quantrocket history create-sharadar-db 'sharadar-us-stk-1d' --sec-type 'STK' --country 'US'
status: successfully created quantrocket.v2.history.sharadar-us-stk-1d.sqlite

>>> from quantrocket.history import create_sharadar_db
>>> create_sharadar_db("sharadar-us-stk-1d", sec_type="STK", country="US")
{'status': 'successfully created quantrocket.v2.history.sharadar-us-stk-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/sharadar-us-stk-1d?vendor=sharadar&sec_type=STK&country=US'
{"status": "successfully created quantrocket.v2.history.sharadar-us-stk-1d.sqlite"}

$ quantrocket history collect 'sharadar-us-stk-1d'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("sharadar-us-stk-1d")
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=sharadar-us-stk-1d'
{"status": "the historical data will be collected asynchronously"}

Collecting the full dataset the first time takes approximately 10-15 minutes. Monitor the status in flightlog:

quantrocket.history: INFO [sharadar-us-stk-1d] Collecting Sharadar US STK prices
quantrocket.history: INFO [sharadar-us-stk-1d] Collecting updated Sharadar US securities listings
quantrocket.history: INFO [sharadar-us-stk-1d] Finished collecting Sharadar US STK prices

Sharadar data guide

A snippet of the dataset is shown below:

| Sid            |       Date |  Open |   High |    Low |  Close |     Volume | CloseUnadj | Dividends | LastUpdated |
| -------------- | ---------- | ----- | ------ | ------ | ------ | ---------- | ---------- | --------- | ----------- |
| FIBBG000C2V3D6 | 2020-04-06 | 72.97 | 74.990 | 72.245 | 74.360 |  2,311,703 |     74.360 |         0 |  2020-04-06 |
| FIBBG00B3T3HD3 | 2020-04-06 |  6.37 |  6.840 |  6.250 |  6.550 |  9,887,881 |      6.550 |         0 |  2020-04-06 |
| FIBBG000V2S3P6 | 2020-04-06 |  0.95 |  0.950 |  0.936 |  0.945 |      3,532 |      0.945 |         0 |  2020-04-06 |
| FIBBG001R3QP52 | 2020-04-06 | 35.62 | 37.110 | 35.620 | 37.110 |    414,424 |     37.110 |         0 |  2020-04-06 |
| FIBBG005P7Q881 | 2020-04-06 |  9.72 |  9.940 |  9.110 |  9.500 | 93,272,261 |      9.500 |         0 |  2020-04-06 |

Split and dividend adjustments

Sharadar price data is split-adjusted.

Sharadar price data is not dividend-adjusted. However, a Dividends column is included which provides the split-adjusted dividend amount, if any, for each date. If desired, you can apply the dividends to the prices as follows:

closes = prices.loc["Close"]
dividends = prices.loc["Dividends"].fillna(0)

# calculate adjustment factors; the adjustment factor is the dividend
# amount as a percentage of the prior close
prior_closes = closes.shift()
adjustment_factors = (prior_closes - dividends) / prior_closes

# calculate cumulative adjustment factors. To do this, we sort in reverse
# order (latest to earliest), take the cumulative product, then restore
# the original sort order (earliest to latest). This means larger cumulative
# adjustments will be applied the further back we go.
cum_adjustment_factors = adjustment_factors.sort_index(ascending=False).cumprod().sort_index(ascending=True)

# shift the factors back a day, because dividends should only be applied
# to prices before the ex date, not on the ex date itself
cum_adjustment_factors = cum_adjustment_factors.shift(-1)

# Then multiply prices by the cumulative adjustment factors
adj_closes = closes * cum_adjustment_factors

You need not load the entire dataset into memory and apply dividend adjustments all at once. Simply apply the dividend adjustments to the window of data you are currently working with. While only dividends occurring within that window will be applied , this will still ensure a smooth, adjusted price series.

Primary vs consolidated prices

Sharadar price data is consolidated, that is, represents the combined trading activity across US exchanges.

Learn more about the difference between consolidated and primary exchange prices.

Delisted stocks

Sharadar price data includes stocks that delisted due to bankruptcies, mergers and acquisitions, etc.

Update schedule

The Sharadar dataset is usually updated by 7 PM New York time. Occasionally it is delayed, in which case it will be updated by 5 AM the following morning.

US Stock

The US Stock dataset is available to all QuantRocket customers and provides end-of-day and 1-minute intraday historical prices, with history back to 2007.

US Stock data guide

A sample record from the end-of-day dataset is shown below:

Sid: "FIBBG000B9XRY4"
Date: "2020-04-06"
Symbol: "AAPL"
Open: 250.9
High: 263.11
Low: 249.38
Close: 262.47
Volume: 50455071
Vwap: 256.1566
TotalTrades: 486681
UnadjOpen: 250.9
UnadjHigh: 263.11
UnadjLow: 249.38
UnadjClose: 262.47
UnadjVolume: 50455071
UnadjVwap: 256.1566

A snippet from the intraday dataset is shown below:

| Field  | Date                      | FIBBG00B3T3HD3 | FIBBG000B9XRY4 | FIBBG000BKZB36 | FIBBG000BMHYD1 |
| ------ | ------------------------- | -------------- | -------------- | -------------- | -------------- |
| Close  | 2020-03-20 09:31:00-04:00 | 6.04           | 248.08         | 163.35         | 125.98         |
| High   | 2020-03-20 09:31:00-04:00 | 6.145          | 248.96         | 163.4          | 126.25         |
| Low    | 2020-03-20 09:31:00-04:00 | 5.96           | 246.84         | 162.075        | 125.73         |
| Open   | 2020-03-20 09:31:00-04:00 | 6.13           | 247.63         | 162.696        | 126.0          |
| Volume | 2020-03-20 09:31:00-04:00 | 23756.0        | 208466.0       | 31806.0        | 50147.0        |

Split and dividend adjustments

US Stock price data is split- and dividend-adjusted.

Primary vs consolidated prices

US Stock price data is consolidated, that is, represents the combined trading activity across US exchanges.

Learn more about the difference between consolidated and primary exchange prices.

Delisted stocks

US Stock price data includes stocks that delisted due to bankruptcies, mergers and acquisitions, etc.

Update schedule

The US Stock dataset is usually updated by 1 AM New York time with the previous day's prices, but in rare cases may not be updated until 7 AM. For users collecting daily incremental updates of either the end-of-day or intraday dataset, the recommended time to schedule the data collection is 7:30 AM each weekday.

Point-in-time ticker symbols

There is a Symbol column in the end-of-day US stock price data as well as a Symbol column (and usstock_Symbol column) in the securities master file. The Symbol column in the price data contains the point-in-time ticker symbol, that is, the ticker symbol as of that date. This field does not change if a security subsequently undergoes a ticker change. In contrast, the Symbol/usstock_Symbol column in the securities master file always reflects the security's latest ticker symbol.

US Stock end-of-day

To collect end-of-day US Stock price data, specify a bar size of 1 day and a universe of either US (for the full dataset) or FREE (for sample data):

$ quantrocket history create-usstock-db 'usstock-1d' --bar-size '1 day' --universe 'US'
status: successfully created quantrocket.v2.history.usstock-1d.sqlite

>>> from quantrocket.history import create_usstock_db
>>> create_usstock_db("usstock-1d", bar_size="1 day", universe="US")
{'status': 'successfully created quantrocket.v2.history.usstock-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/usstock-1d?vendor=usstock&bar_size=1+day&universe=US'
{"status": "successfully created quantrocket.v2.history.usstock-1d.sqlite"}

$ quantrocket history collect 'usstock-1d'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("usstock-1d")
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=usstock-1d'
{"status": "the historical data will be collected asynchronously"}

Monitor the status in flightlog:

quantrocket.history: INFO [usstock-1d] Collecting US history from 2007 to present
quantrocket.history: INFO [usstock-1d] Collecting updated US securities listings
quantrocket.history: INFO [usstock-1d] Collecting additional US history from 2020-04 to present
quantrocket.history: INFO [usstock-1d] Applying price adjustments for 52 securities
quantrocket.history: INFO [usstock-1d] Collected 161 monthly files in quantrocket.v2.history.usstock-1d.sqlite

The data is collected by loading pre-built 1-year chunks of data in which split and dividend adjustments have already been applied, then loading any additional price and adjustment history that has occurred since the pre-built chunks were last generated.

US Stock intraday

The intraday US Stock dataset provides 1-minute prices with history back to 2007.

Unlike other historical price datasets which are stored in SQLite databases and managed by the history service, the intraday US Stock dataset is stored in a Zipline bundle and managed by the zipline service. Although Zipline is primarily a backtesting engine, it includes a storage backend which was originally designed for 1-minute US stock prices and thus is very well suited for this dataset.

Storage requirements

A particular advantage of Zipline's storage backend is that it utilizes a highly compressed columnar storage format called bcolz. This makes the otherwise very large size of the dataset much more manageable.

The total bundle size is about 50 GB for all listed US stocks. You are free to load a subset of securities in which case the size will be smaller.

Data collection runtime

The full dataset consists of several million small files which are synced from the cloud to your local deployment. Collecting the entire dataset the first time takes approximately 12-15 hours depending on network speed. Collecting the incremental daily updates takes approximately 10-15 minutes. (See the data guide section above for the dataset's update schedule and the recommended time to schedule collection of daily updates.)

Collect minute bundle

The workflow for collecting the US Stock minute bundle is similar to the workflow for history databases, but adapted to Zipline:

• Create an empty database ("bundle" in Zipline terminology) which defines your data requirements.
• Collect ("ingest" in Zipline terminology) the historical data.
• Periodically collect/ingest the data again to obtain updated history.
• Query the minute data in your anlaysis or trading.

First, define the bundle you want. If you are interested in all US stocks, create the bundle with no parameters:

$ quantrocket zipline create-usstock-bundle 'usstock-1min'
msg: successfully created usstock-1min bundle
status: success

>>> from quantrocket.zipline import create_usstock_bundle
>>> create_usstock_bundle("usstock-1min")
{'status': 'success', 'msg': 'successfully created usstock-1min bundle'}

$ curl -X PUT 'http://houston/zipline/bundles/usstock-1min?ingest_type=usstock'
{"status": "success", "msg": "successfully created usstock-1min bundle"}

$ quantrocket zipline create-usstock-bundle 'free-usstock-1min' --free
msg: successfully created free-usstock-1min bundle
status: success

>>> create_usstock_bundle("free-usstock-1min", free=True)
{'status': 'success', 'msg': 'successfully created free-usstock-1min bundle'}

$ curl -X PUT 'http://houston/zipline/bundles/free-usstock-1min?ingest_type=usstock&free=true'
{"status": "success", "msg": "successfully created free-usstock-1min bundle"}

If you are interested in a subset of stocks other than free sample data, there are two options. You can specify sids and/or universes at the time of bundle creation (using the sids and universes parameters) or at the time of data ingestion. Any sids or universes that you specify at the time of bundle creation can be considered the default parameters, while any sids or universes you specify at data ingestion time will override the default parameters.

The next step is to ingest the data. If your bundle definition is for the full dataset, consider using the sids or universes parameters to collect a subset of data so you can begin experimenting while waiting for the full dataset to be collected:

$ # ingest a subset of securities first
$ quantrocket zipline ingest 'usstock-1min' --sids 'FIBBG000B9XRY4' 'FIBBG000BKZB36' 'FIBBG000BMHYD1' 'FIBBG00B3T3HD3'
status: the data will be ingested asynchronously
$ # then ingest everything
$ quantrocket zipline ingest 'usstock-1min'
status: the data will be ingested asynchronously

>>> from quantrocket.zipline import ingest_bundle
>>> # ingest a subset of securities first
>>> ingest_bundle("usstock-1min", sids=["FIBBG000B9XRY4", "FIBBG000BKZB36", "FIBBG000BMHYD1", "FIBBG00B3T3HD3"])
{'status': 'the data will be ingested asynchronously'}
>>> # then ingest everything
>>> ingest_bundle("usstock-1min")
{'status': 'the data will be ingested asynchronously'}

$ # ingest a subset of securities first
$ curl -X POST 'http://houston/zipline/ingestions/usstock-1min?sids=FIBBG000B9XRY4&sids=FIBBG000BKZB36&sids=FIBBG000BMHYD1&sids=FIBBG00B3T3HD3'
{"status": "the data will be ingested asynchronously"}
$ # then ingest everything
$ curl -X POST 'http://houston/zipline/ingestions/usstock-1min'
{"status": "the data will be ingested asynchronously"}

Monitor flightlog for completion status:

quantrocket.zipline: INFO [usstock-1min] Ingesting minute bars for 4 securities in usstock-1min bundle
quantrocket.zipline: INFO [usstock-1min] Ingesting daily bars for usstock-1min bundle
quantrocket.zipline: INFO [usstock-1min] Ingesting adjustments for usstock-1min bundle
quantrocket.zipline: INFO [usstock-1min] Ingesting assets for usstock-1min bundle
quantrocket.zipline: INFO [usstock-1min] Completed ingesting data for 4 securities in usstock-1min bundle

Update minute bundle

To update the minute bundle with new data, simply run the ingestion again (with or without specifying sids or universes, depending on your needs):

$ quantrocket zipline ingest 'usstock-1min'
status: the data will be ingested asynchronously

>>> ingest_bundle("usstock-1min")
{'status': 'the data will be ingested asynchronously'}

$ curl -X POST 'http://houston/zipline/ingestions/usstock-1min'
{"status": "the data will be ingested asynchronously"}

Because only the new data will be ingested, updating the bundle runs much faster than the initial ingestion.

For more on the Zipline bundle API, see the Zipline docs.

Query minute file

The most convenient way to load minute data into Python is using the get_prices function, which parses the data into a Pandas DataFrame and also works for history databases and real-time aggregate databases in addition to Zipline bundles. This function is outlined in the Research section.

Alternatively, for a more raw approach, you can download a CSV file of minute data:

$ $ quantrocket zipline get 'usstock-1min' --sids 'FIBBG000B9XRY4' 'FIBBG000BKZB36' --start-date '2020-04-06' --end-date '2020-04-06' --times '09:31:00' '09:32:00' | csvlook
| Field  | Date                      | FIBBG000B9XRY4 | FIBBG000BKZB36 |
| ------ | ------------------------- | -------------- | -------------- |
| Close  | 2020-04-06 09:31:00-04:00 |        250.780 |        186.635 |
| Close  | 2020-04-06 09:32:00-04:00 |        250.330 |        185.730 |
| High   | 2020-04-06 09:31:00-04:00 |        251.535 |        187.425 |
| High   | 2020-04-06 09:32:00-04:00 |        250.960 |        186.940 |
| Low    | 2020-04-06 09:31:00-04:00 |        250.560 |        186.120 |
| Low    | 2020-04-06 09:32:00-04:00 |        250.200 |        185.127 |
| Open   | 2020-04-06 09:31:00-04:00 |        250.850 |        186.650 |
| Open   | 2020-04-06 09:32:00-04:00 |        250.689 |        186.640 |
| Volume | 2020-04-06 09:31:00-04:00 |    221,336.000 |     29,524.000 |
| Volume | 2020-04-06 09:32:00-04:00 |    185,522.000 |     23,366.000 |

>>> from quantrocket.zipline import download_minute_file
>>> download_minute_file("usstock-1min",
                         sids=["FIBBG000B9XRY4", "FIBBG000BKZB36"],
                         start_date="2020-04-06", end_date="2020-04-06",
                         times=["09:31:00", "09:32:00"],
                         filepath_or_buffer="minute_prices.csv")
>>> prices = pd.read_csv("minute_prices.csv", parse_dates=["Date"], index_col=["Field","Date"])
>>> prices.head()
                                 FIBBG000B9XRY4  FIBBG000BKZB36
Field Date
Close 2020-04-06 09:31:00-04:00         250.780         186.635
      2020-04-06 09:32:00-04:00         250.330         185.730
High  2020-04-06 09:31:00-04:00         251.535         187.425
      2020-04-06 09:32:00-04:00         250.960         186.940
Low   2020-04-06 09:31:00-04:00         250.560         186.120

$ $ curl -X GET 'http://houston/zipline/bundles/data/usstock-1min.csv?start_date=2020-04-06&end_date=2020-04-06&sids=FIBBG000B9XRY4&sids=FIBBG000BKZB36&times=09%3A31%3A00&times=09%3A32%3A00' | csvlook
| Field  | Date                      | FIBBG000B9XRY4 | FIBBG000BKZB36 |
| ------ | ------------------------- | -------------- | -------------- |
| Close  | 2020-04-06 09:31:00-04:00 |        250.780 |        186.635 |
| Close  | 2020-04-06 09:32:00-04:00 |        250.330 |        185.730 |
| High   | 2020-04-06 09:31:00-04:00 |        251.535 |        187.425 |
| High   | 2020-04-06 09:32:00-04:00 |        250.960 |        186.940 |
| Low    | 2020-04-06 09:31:00-04:00 |        250.560 |        186.120 |
| Low    | 2020-04-06 09:32:00-04:00 |        250.200 |        185.127 |
| Open   | 2020-04-06 09:31:00-04:00 |        250.850 |        186.650 |
| Open   | 2020-04-06 09:32:00-04:00 |        250.689 |        186.640 |
| Volume | 2020-04-06 09:31:00-04:00 |    221,336.000 |     29,524.000 |
| Volume | 2020-04-06 09:32:00-04:00 |    185,522.000 |     23,366.000 |

Primary vs consolidated prices

Pricing data can either be "consolidated" or from the "primary exchange". Consolidated prices provide combined trading activity from all exchanges within a country. Primary exchange prices provide trading activity from the primary listing exchange only. Both have pros and cons.

Primary exchange prices provide a truer indication of the opening and closing auction price. This can result in more accurate backtests for trading strategies that enter and exit in the opening or closing auction. This issue is especially significant in US markets due to after-hours trading and the large number of exchanges and ECNs. The closing or opening price in consolidated data may represent small trades from an ECN that would be hard to obtain, rather than the opening or closing auction price. For more on this topic, see this blog post by Ernie Chan.

However, consolidated prices provide a more complete picture of total trading volume. In the US market, for example, trading volume on the primary exchange often accounts for only 25% of total daily volume.

Fundamental Data

Alpaca ETB

Alpaca publishes a daily list of easy-to-borrow (ETB) stocks, which indicates whether the stock is shortable through Alpaca. QuantRocket maintains a historical archive dating back to March 2019.

Collect Alpaca ETB

To collect the data:

$ quantrocket fundamental collect-alpaca-etb
status: the easy-to-borrow data will be collected asynchronously

>>> from quantrocket.fundamental import collect_alpaca_etb
>>> collect_alpaca_etb()
{'status': 'the easy-to-borrow data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/alpaca/stockloan/etb'
{"status": "the easy-to-borrow data will be collected asynchronously"}

QuantRocket will collect the data in 1-month batches and save it to your database. Monitor flightlog for progress:

quantrocket.fundamental: INFO Collecting alpaca usa easy-to-borrow data from 2019-03-01 to present
quantrocket.fundamental: INFO Saved 216389 total alpaca easy-to-borrow records to quantrocket.v2.fundamental.alpaca.stockloan.etb.sqlite

Query Alpaca ETB

You can query the ETB data by universe or sid. The returned data is a boolean value (1 or 0) indicating whether the security was on the easy-to-borrow list on a given date:

$ quantrocket fundamental alpaca-etb --sids 'FIBBG000B9XRY4' 'FIBBG00LBLDHJ2' --start-date '2020-03-01' -o etb.csv
$ csvlook -I --max-rows 5 etb.csv
| Sid            | Date       | EasyToBorrow |
| -------------- | ---------- | ------------ |
| FIBBG000B9XRY4 | 2020-03-02 | 1            |
| FIBBG000B9XRY4 | 2020-04-01 | 1            |
| FIBBG00LBLDHJ2 | 2020-03-02 | 0            |
| FIBBG00LBLDHJ2 | 2020-03-05 | 1            |
| FIBBG00LBLDHJ2 | 2020-03-11 | 0            |

>>> from quantrocket.fundamental import download_alpaca_etb
>>> import pandas as pd
>>> download_alpaca_etb("etb.csv", start_date="2020-03-01", sids=["FIBBG000B9XRY4", "FIBBG00LBLDHJ2"])
>>> etb = pd.read_csv("etb.csv", parse_dates=["Date"])
>>> etb.head()
              Sid       Date  EasyToBorrow
0  FIBBG000B9XRY4 2020-03-02             1
1  FIBBG000B9XRY4 2020-04-01             1
2  FIBBG00LBLDHJ2 2020-03-02             0
3  FIBBG00LBLDHJ2 2020-03-05             1
4  FIBBG00LBLDHJ2 2020-03-11             0

$ curl -X GET 'http://houston/fundamental/alpaca/stockloan/etb.csv?start_date=2020-03-01&sids=FIBBG000B9XRY4&sids=FIBBG00LBLDHJ2' --output etb.csv
$ head etb.csv
Sid,Date,EasyToBorrow
FIBBG000B9XRY4,2020-03-02,1
FIBBG000B9XRY4,2020-04-01,1
FIBBG00LBLDHJ2,2020-03-02,0
FIBBG00LBLDHJ2,2020-03-05,1
FIBBG00LBLDHJ2,2020-03-11,0

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get easy-to-borrow status that is aligned to the price data:

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2020-03-04", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_alpaca_etb_reindexed_like
>>> etb = get_alpaca_etb_reindexed_like(closes)

The resulting boolean DataFrame has an index and columns matching the input DataFrame:

>>> etb.head()
Sid         FIBBG000B9XRY4  FIBBG000BVPV84  FIBBG000CL9VN6  FIBBG00LBLDHJ2
Date
2020-03-04            True            True            True           False
2020-03-05            True            True            True            True
2020-03-06            True            True            True            True
2020-03-09            True            True            True            True
2020-03-10            True            True            True            True
2020-03-11            True            True            True           False

Alpaca ETB data guide

Data storage

Alpaca updates the easy-to-borrow list daily, but the data for any given stock doesn't always change that frequently. To conserve disk space, QuantRocket stores the data sparsely. That is, the data for any given security is stored only when the data changes. The following example illustrates:

With this data storage design, the data is intended to be forward-filled after you query it. (The function get_alpaca_etb_reindexed_like does this for you.)

Update schedule

Daily updates to the Alpaca ETB dataset are made available each weekday morning by 8:15 AM New York time.

IBKR stockloan

QuantRocket provides current and historical short sale availability data from Interactive Brokers. The dataset includes the number of shortable shares available and the associated borrow fees. You can use this dataset to model the constraints and costs of short selling.

IBKR updates short sale availability data every 15 minutes. IBKR does not provide a historical archive of data but QuantRocket maintains a historical archive dating from April 16, 2018.

No IBKR market data subscriptions are required to access this dataset.

Collect IBKR stockloan

Shortable shares data and borrow fee data are stored separately but have similar APIs. Both datasets are organized by country. The available country names are:

To use the data, first collect the desired dataset and countries from QuantRocket's archive into your local database. For shortable shares:

$ quantrocket fundamental collect-ibkr-shortshares --countries 'japan' 'usa'
status: the shortable shares will be collected asynchronously

>>> from quantrocket.fundamental import collect_ibkr_shortable_shares
>>> collect_ibkr_shortable_shares(countries=["japan","usa"])
{'status': 'the shortable shares will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/ibkr/stockloan/shares?countries=japan&countries=usa'
{"status": "the shortable shares will be collected asynchronously"}

$ quantrocket fundamental collect-ibkr-borrowfees --countries 'japan' 'usa'
status: the borrow fees will be collected asynchronously

>>> from quantrocket.fundamental import collect_ibkr_borrow_fees
>>> collect_ibkr_borrow_fees(countries=["japan","usa"])
{'status': 'the borrow fees will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/ibkr/stockloan/fees?countries=japan&countries=usa'
{"status": "the borrow fees will be collected asynchronously"}

QuantRocket will collect the data in 1-month batches and save it to your database. Monitor flightlog for progress:

quantrocket.fundamental: INFO Collecting ibkr japan shortable shares from 2018-04-01 to present
quantrocket.fundamental: INFO Collecting ibkr usa shortable shares from 2018-04-01 to present
quantrocket.fundamental: INFO Saved 2993493 total shortable shares records to quantrocket.v2.fundamental.ibkr.stockloan.shares.sqlite

To update the data later, re-run the same command(s) you ran originally. QuantRocket will collect any new data since your last update and add it to your database.

Query IBKR stockloan

You can export the short sale data to CSV (or JSON), querying by universe or sid:

$ quantrocket fundamental ibkr-shortshares -u 'usa-stk' -o usa_shortable_shares.csv
$ csvlook -I --max-rows 5 usa_shortable_shares.csv
| Sid            | Date                | Quantity |
| -------------- | ------------------- | -------- |
| FIBBG000C26F38 | 2018-04-15T21:45:02 | 10000000 |
| FIBBG000C26F38 | 2018-04-21T02:15:03 | 7300000  |
| FIBBG000C26F38 | 2018-04-22T16:15:03 | 10000000 |
| FIBBG000C26F38 | 2018-05-01T04:00:03 | 10000000 |
| FIBBG000C26F38 | 2018-06-01T04:00:03 | 10000000 |

>>> from quantrocket.fundamental import download_ibkr_shortable_shares
>>> import pandas as pd
>>> download_ibkr_shortable_shares(
        "usa_shortable_shares.csv",
        universes=["usa-stk"])
>>> shortable_shares = pd.read_csv(
        "usa_shortable_shares.csv",
        parse_dates=["Date"])
>>> shortable_shares.head()
                Sid                 Date  Quantity
0    FIBBG000C26F38  2018-04-15T21:45:02  10000000
1    FIBBG000C26F38  2018-04-21T02:15:03   7300000
2    FIBBG000C26F38  2018-04-22T16:15:03  10000000
3    FIBBG000C26F38  2018-05-01T04:00:03  10000000
4    FIBBG000C26F38  2018-06-01T04:00:03  10000000

$ curl -X GET 'http://houston/fundamental/ibkr/stockloan/shares.csv?&universes=usa-stk' --output usa_shortable_shares.csv
$ head usa_shortable_shares.csv
Sid,Date,Quantity
FIBBG000C26F38,2018-04-15T21:45:02,10000000
FIBBG000C26F38,2018-04-21T02:15:03,7300000
FIBBG000C26F38,2018-04-22T16:15:03,10000000
FIBBG000C26F38,2018-05-01T04:00:03,10000000
FIBBG000C26F38,2018-06-01T04:00:03,10000000

$ quantrocket fundamental ibkr-borrowfees -u 'usa-stk' -o usa_borrow_fees.csv
$ csvlook -I --max-rows 5 usa_borrow_fees.csv
| Sid            | Date                | FeeRate |
| -------------- | ------------------- | ------- |
| FIBBG000C26F38 | 2018-04-15T21:45:02 | 0.886   |
| FIBBG000C26F38 | 2018-04-16T14:15:03 | 0.887   |
| FIBBG000C26F38 | 2018-04-17T14:15:02 | 0.89    |
| FIBBG000C26F38 | 2018-04-19T14:15:02 | 0.895   |
| FIBBG000C26F38 | 2018-04-20T14:15:03 | 0.894   |

>>> from quantrocket.fundamental import download_ibkr_borrow_fees
>>> import pandas as pd
>>> download_ibkr_borrow_fees(
        "usa_borrow_fees.csv",
        universes=["usa-stk"])
>>> borrow_fees = pd.read_csv(
        "usa_borrow_fees.csv",
        parse_dates=["Date"])
>>> borrow_fees.head()
                Sid                 Date  FeeRate
0    FIBBG000C26F38  2018-04-15T21:45:02   0.8860
1    FIBBG000C26F38  2018-04-16T14:15:03   0.8870
2    FIBBG000C26F38  2018-04-17T14:15:02   0.8900
3    FIBBG000C26F38  2018-04-19T14:15:02   0.8950
4    FIBBG000C26F38  2018-04-20T14:15:03   0.8940

$ curl -X GET 'http://houston/fundamental/ibkr/stockloan/fees.csv?&universes=usa-stk' --output usa_borrow_fees.csv
$ head usa_borrow_fees.csv
Sid,Date,FeeRate
FIBBG000C26F38,2018-04-15T21:45:02,0.886
FIBBG000C26F38,2018-04-16T14:15:03,0.887
FIBBG000C26F38,2018-04-17T14:15:02,0.89
FIBBG000C26F38,2018-04-19T14:15:02,0.895
FIBBG000C26F38,2018-04-20T14:15:03,0.894

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get shortable shares or borrow fees data that is aligned to the price data:

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2018-04-16", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_ibkr_shortable_shares_reindexed_like, get_ibkr_borrow_fees_reindexed_like
>>> shortable_shares = get_ibkr_shortable_shares_reindexed_like(closes)
>>> borrow_fees = get_ibkr_borrow_fees_reindexed_like(closes)

The resulting DataFrame has an index and columns matching the input DataFrame:

>>> shortable_shares.head()
Sid         FIBBG000006F71  FIBBG000006L78  FIBBG000006LG8  FIBBG000006RN7
Date
2018-04-16          3000.0          2000.0           100.0         20000.0
2018-04-17          4000.0          2000.0           100.0         20000.0
2018-04-18          4000.0          3000.0             0.0         20000.0
2018-04-19          3000.0          3000.0             0.0         20000.0
2018-04-20          3000.0          3000.0             0.0         20000.0

The data for each date is as of midnight UTC. You can specify a different time and timezone using the time parameter:

>>> # request shortable shares as of the US market open
>>> shortable_shares = get_ibkr_shortable_shares_reindexed_like(closes, time="09:30:00 America/New_York")
>>> # request borrow fees as of 5:00 PM New York time
>>> borrow_fees = get_ibkr_borrow_fees_reindexed_like(closes, time="17:00:00 America/New_York")

Dates prior to April 16, 2018 (the start date of QuantRocket's historical archive) will have NaNs in the resulting DataFrame.

Borrow fees are stored as annualized interest rates. For example, 1.0198 indicates an annualized interest rate of 1.0198%:

>>> borrow_fees.head()
Sid         FIBBG000B9XRY4  FIBBG000BVPV84  FIBBG000CL9VN6  FIBBG009S3NB30
Date
2018-04-16            0.25          1.3575            0.25          0.3388
2018-04-17            0.25          1.3348            0.25          0.3291
2018-04-18            0.25          0.2500            0.25          0.2533
2018-04-19            0.25          0.2500            0.25          0.2500
2018-04-20            0.25          0.2500            0.25          0.3865

Below is an example of calculating borrow fees for a DataFrame of positions (adapted from Moonshot's IBKRBorrowFees slippage class):

borrow_fees = get_ibkr_borrow_fees_reindexed_like(positions)
borrow_fees = borrow_fees / 100
# Fees are assessed daily but the dataframe is expected to only
# includes trading days, thus use 252 instead of 365. In reality the
# borrow fee is greater for weekend positions than weekday positions,
# but this implementation doesn't model that.
daily_borrow_fees = borrow_fees / 252
assessed_fees = positions.where(positions < 0, 0).abs() * daily_borrow_fees

IBKR stockloan data guide

Data storage

IBKR updates short sale availability data every 15 minutes, but the data for any given stock doesn't always change that frequently. To conserve disk space, QuantRocket stores the shortable shares and borrow fees data sparsely. That is, the data for any given security is stored only when the data changes. The following example illustrates:

With this data storage design, the data is intended to be forward-filled after you query it. (The functions get_ibkr_shortable_shares_reindexed_like and get_ibkr_borrow_fees_reindexed_like do this for you.)

Missing data

The shortable shares and borrow fees datasets represent IBKR's comprehensive list of shortable stocks. If stocks are missing from the data, that means they were never available to short. Stocks that were available to short and later became unavailable will be present in the data and will have values of 0 when they became unavailable (possibly followed by nonzero values if they later became available again).

Timestamps and latency

The data timestamps are in UTC and indicate the time at which IBKR made the data available. It takes approximately two minutes for the data to be processed and made available in QuantRocket's archive. Once available, the data will be added to your local database the next time you collect it.

Stocks with >10M shortable shares

In the shortable shares dataset, 10000000 (10 million) is the largest number reported and means "10 million or more."

Reuters estimates

Interactive Brokers provides its customers with access to global fundamental data sourced from Reuters. IBKR enables data access by default; no subscription in IBKR Client Portal is required. There are two available datasets: estimates and actuals, and financial statements.

Collect Reuters estimates

To collect Reuters estimates and actuals, specify one or more sids or universes:

$ quantrocket fundamental collect-reuters-estimates --universes 'japan-banks' 'singapore-banks'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_reuters_estimates
>>> collect_reuters_financials(universes=["japan-banks","singapore-banks"])
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/reuters/estimates?universes=japan-banks&universes=singapore-banks'
{"status": "the fundamental data will be collected asynchronously"}

Multiple requests will be queued and processed sequentially. Monitor flightlog for progress:

$ quantrocket flightlog stream
quantrocket.fundamental: INFO Collecting Reuters estimates from IBKR for universes japan-banks, singapore-banks
quantrocket.fundamental: INFO Saved 3298 total records for 60 total securities to quantrocket.v2.fundamental.reuters.estimates.sqlite for universes japan-banks, singapore-banks

Query Reuters estimates

You can download the data to CSV:

$ quantrocket fundamental reuters-estimates 'EPS' -u 'us-banks' -s '2014-01-01' -e '2017-01-01' -o eps_estimates.csv
$ csvlook -I --max-columns 10 --max-rows 5 eps_estimates.csv
| Sid            | Indicator | Unit | FiscalYear | FiscalPeriodEndDate | FiscalPeriodType | FiscalPeriodNumber | High   | Low    | Mean   | ... |
| -------------- | --------- | ---- | ---------- | ------------------- | ---------------- | ------------------ | ------ | ------ | ------ | --- |
| FIBBG000B9XRY4 | EPS       | U    | 2014       | 2014-03-31          | Q                | 2                  | 1.5271 | 1.3857 | 1.4563 | ... |
| FIBBG000B9XRY4 | EPS       | U    | 2014       | 2014-06-30          | Q                | 3                  | 1.37   | 1.1386 | 1.231  | ... |
| FIBBG000B9XRY4 | EPS       | U    | 2014       | 2014-09-30          | A                |                    | 6.5    | 6.25   | 6.3339 | ... |
| FIBBG000B9XRY4 | EPS       | U    | 2014       | 2014-09-30          | Q                | 4                  | 1.47   | 1.21   | 1.3051 | ... |
| FIBBG000B9XRY4 | EPS       | U    | 2015       | 2014-12-31          | Q                | 1                  | 2.966  | 2.44   | 2.6019 | ... |

>>> from quantrocket.fundamental import download_reuters_estimates
>>> import pandas as pd
>>> download_reuters_estimates(["EPS"], filepath_or_buffer="eps_estimates.csv",
                               universes=["us-banks"],
                               start_date="2014-01-01", end_date="2017-01-01")
>>> eps_estimates = pd.read_csv("eps_estimates.csv", parse_dates=["FiscalPeriodEndDate", "AnnounceDate"])
>>> eps_estimates.head()
              Sid Indicator Unit  FiscalYear FiscalPeriodEndDate FiscalPeriodType  FiscalPeriodNumber    High     Low    Mean  Median  StdDev  NumOfEst        AnnounceDate          UpdatedDate  Actual
0  FIBBG000B9XRY4       EPS    U        2014          2014-03-31                Q                 2.0  1.5271  1.3857  1.4563  1.4578  0.0291        46 2014-04-23 20:30:00  2014-04-23T20:39:10    1.66
1  FIBBG000B9XRY4       EPS    U        2014          2014-06-30                Q                 3.0  1.3700  1.1386  1.2310  1.2286  0.0431        45 2014-07-22 20:30:00  2014-07-22T20:59:01    1.28
2  FIBBG000B9XRY4       EPS    U        2014          2014-09-30                A                 NaN  6.5000  6.2500  6.3339  6.3400  0.0457        48 2014-10-20 20:30:00  2014-10-20T20:40:38    6.45
3  FIBBG000B9XRY4       EPS    U        2014          2014-09-30                Q                 4.0  1.4700  1.2100  1.3051  1.3100  0.0491        41 2014-10-20 20:30:00  2014-10-20T20:40:37    1.42
4  FIBBG000B9XRY4       EPS    U        2015          2014-12-31                Q                 1.0  2.9660  2.4400  2.6019  2.5700  0.1162        41 2015-01-27 21:30:00  2015-01-27T21:35:10    3.06

$ curl -X GET 'http://houston/fundamental/reuters/estimates.csv?codes=EPS&universes=us-banks&start_date=2014-01-01&end_date=2017-01-01' --output eps_estimates.csv
$ head eps_estimates.csv
Sid,Indicator,Unit,FiscalYear,FiscalPeriodEndDate,FiscalPeriodType,FiscalPeriodNumber,High,Low,Mean,Median,StdDev,NumOfEst,AnnounceDate,UpdatedDate,Actual
FIBBG000B9XRY4,EPS,U,2014,2014-03-31,Q,2,1.5271,1.3857,1.4563,1.4578,0.0291,46,2014-04-23T20:30:00,2014-04-23T20:39:10,1.66
FIBBG000B9XRY4,EPS,U,2014,2014-06-30,Q,3,1.37,1.1386,1.231,1.2286,0.0431,45,2014-07-22T20:30:00,2014-07-22T20:59:01,1.28
FIBBG000B9XRY4,EPS,U,2014,2014-09-30,A,"",6.5,6.25,6.3339,6.34,0.0457,48,2014-10-20T20:30:00,2014-10-20T20:40:38,6.45
FIBBG000B9XRY4,EPS,U,2014,2014-09-30,Q,4,1.47,1.21,1.3051,1.31,0.0491,41,2014-10-20T20:30:00,2014-10-20T20:40:37,1.42

See the available indicators.

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Reuters estimates and actuals data that is aligned to the price data. This makes it easy to perform matrix operations using fundamental data.

>>> from quantrocket import get_prices
>>> prices = get_prices("japan-bank-eod", start_date="2017-01-01", fields=["Open","High","Low","Close", "Volume"])
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> # Query earnings per share (EPS) and book value per share (BVPS)
>>> from quantrocket.fundamental import get_reuters_estimates_reindexed_like
>>> estimates = get_reuters_estimates_reindexed_like(
                     closes,
                     codes=["EPS", "BVPS"])

The resulting DataFrame can be thought of as several stacked DataFrames, with a MultiIndex consisting of the indicator code, the field (by default only Actual is returned), and the date. Note that get_reuters_estimates_reindexed_like shifts values forward by one day to avoid any lookahead bias.

>>> estimates.head()
Sid                       FIBBG265598 FIBBG3691937 FIBBG15124833 FIBBG208813719
Indicator Field  Date
BVPS      Actual 2016-01-04     21.39      26.5032        5.0875        336.454
                 2016-01-05     21.39      26.5032        5.0875        336.454
                 2016-01-06     21.39      26.5032        5.0875        336.454
                 2016-01-07     21.39      26.5032        5.0875        336.454
                 2016-01-08     21.39      26.5032        5.0875        336.454
...
EPS       Actual 2016-01-04      1.96         0.17          0.07           7.35
                 2016-01-05      1.96         0.17          0.07           7.35
                 2016-01-06      1.96         0.17          0.07           7.35
                 2016-01-07      1.96         0.17          0.07           7.35
                 2016-01-08      1.96         0.17          0.07           7.35

You can use .loc to isolate a particular indicator and field and perform matrix operations:

>>> book_values_per_share = estimates.loc["BVPS"].loc["Actual"]
>>> # calculate price-to-book ratio
>>> pb_ratios = closes/book_values_per_share

For best performance, make two separate calls to get_reuters_estimates_reindexed_like to retrieve numeric (integer or float) vs non-numeric (string or date) fields. Pandas loads numeric fields in an optimized format compared to non-numeric fields, but mixing numeric and non-numeric fields prevents Pandas from using this optimized format, resulting in slower loads and higher memory consumption.

>>> # DON'T DO THIS
>>> estimates = get_reuters_estimates_reindexed_like(
                     closes,
                     codes=["EPS", "BVPS"],
                     fields=["Actual", "FiscalPeriodEndDate"]) # numeric and non-numeric fields
>>> eps_actuals = estimates.loc["EPS"].loc["Actual"]
>>> fiscal_periods = estimates.loc["EPS"].loc["FiscalPeriodEndDate"]

>>> # DO THIS
>>> estimates = get_reuters_estimates_reindexed_like(
                     closes,
                     codes=["EPS", "BVPS"],
                     fields=["Actual"]) # numeric fields
>>> eps_actuals = estimates.loc["EPS"].loc["Actual"]
>>> estimates = get_reuters_estimates_reindexed_like(
                     closes,
                     codes=["EPS", "BVPS"],
                     fields=["FiscalPeriodEndDate"]) # non-numeric fields
>>> fiscal_periods = estimates.loc["EPS"].loc["FiscalPeriodEndDate"]

Reuters financials

Interactive Brokers provides its customers with access to global fundamental data sourced from Reuters. IBKR enables data access by default; no subscription in IBKR Client Portal is required. There are two available datasets: estimates and actuals, and financial statements.

Collect Reuters financials

To collect Reuters financial statements, specify one or more sids or universes:

$ quantrocket fundamental collect-reuters-financials --universes 'japan-banks' 'singapore-banks'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_reuters_financials
>>> collect_reuters_financials(universes=["japan-banks","singapore-banks"])
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/reuters/financials?universes=japan-banks&universes=singapore-banks'
{"status": "the fundamental data will be collected asynchronously"}

Multiple requests will be queued and processed sequentially. Monitor flightlog for progress:

$ quantrocket flightlog stream
quantrocket.fundamental: INFO Collecting Reuters financials from IBKR for universes japan-banks, singapore-banks
quantrocket.fundamental: INFO Saved 12979 total records for 100 total securities to quantrocket.v2.fundamental.reuters.financials.sqlite for universes japan-banks, singapore-banks

Query Reuters financials

You can download the data to CSV:

$ quantrocket fundamental reuters-financials 'EIBT' -u 'us-banks' -s '2014-01-01' -e '2017-01-01' -o financials.csv
$ csvlook -I --max-columns 6 --max-rows 5 financials.csv
| CoaCode | Sid            | Amount  | FiscalYear | FiscalPeriodEndDate | FiscalPeriodType |
| ------- | -------------- | ------- | ---------- | ------------------- | ---------------- |
| EIBT    | FIBBG000B9XRY4 | 53483.0 | 2014       | 2014-09-27          | Annual           |
| EIBT    | FIBBG000B9XRY4 | 72515.0 | 2015       | 2015-09-26          | Annual           |
| EIBT    | FIBBG000B9XRY4 | 61372.0 | 2016       | 2016-09-24          | Annual           |
| EIBT    | FIBBG000BVPV84 | -111.0  | 2014       | 2014-12-31          | Annual           |
| EIBT    | FIBBG000BVPV84 | 1568.0  | 2015       | 2015-12-31          | Annual           |

>>> from quantrocket.fundamental import download_reuters_financials
>>> download_reuters_financials(["EIBT"], filepath_or_buffer="financials.csv",
                                universes=["us-banks"],
                                start_date="2014-01-01", end_date="2017-01-01")
>>> financials = pd.read_csv("financials.csv", parse_dates=["SourceDate", "FiscalPeriodEndDate"])
>>> financials.head()
  CoaCode             Sid   Amount  FiscalYear FiscalPeriodEndDate FiscalPeriodType ...
0    EIBT  FIBBG000B9XRY4  53483.0        2014          2014-09-27           Annual
1    EIBT  FIBBG000B9XRY4  72515.0        2015          2015-09-26           Annual
2    EIBT  FIBBG000B9XRY4  61372.0        2016          2016-09-24           Annual
3    EIBT  FIBBG000BVPV84   -111.0        2014          2014-12-31           Annual
4    EIBT  FIBBG000BVPV84   1568.0        2015          2015-12-31           Annual

$ curl -X GET 'http://houston/fundamental/reuters/financials.csv?codes=EIBT&universes=us-banks&start_date=2014-01-01&end_date=2017-01-01' --output financials.csv
$ head financials.csv
CoaCode,Sid,Amount,FiscalYear,FiscalPeriodEndDate,FiscalPeriodType,FiscalPeriodNumber,StatementType,StatementPeriodLength,StatementPeriodUnit,UpdateTypeCode,UpdateTypeDescription,StatementDate,AuditorNameCode,AuditorName,AuditorOpinionCode,AuditorOpinion,Source,SourceDate
EIBT,FIBBG000B9XRY4,53483.0,2014,2014-09-27,Annual,"",INC,52,W,UPD,"Updated Normal",2014-09-27,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2014-10-27
EIBT,FIBBG000B9XRY4,72515.0,2015,2015-09-26,Annual,"",INC,52,W,UPD,"Updated Normal",2015-09-26,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2015-10-28
EIBT,FIBBG000B9XRY4,61372.0,2016,2016-09-24,Annual,"",INC,52,W,UPD,"Updated Normal",2016-09-24,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2016-10-26
EIBT,FIBBG000BVPV84,-111.0,2014,2014-12-31,Annual,"",INC,12,M,UPD,"Updated Normal",2014-12-31,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2015-01-30

By default, annual rather than interim statements are returned, and restatements are included; see the function parameters to override this.

See the available indicators.

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Reuters fundamental data that is aligned to the price data. This makes it easy to perform matrix operations using fundamental data.

>>> from quantrocket import get_prices
>>> prices = get_prices("japan-bank-eod", start_date="2017-01-01", fields=["Open","High","Low","Close", "Volume"])
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> # Query total assets (ATOT), total liabilities (LTLL), and common shares
>>> # outstanding (QTCO)
>>> from quantrocket.fundamental import get_reuters_financials_reindexed_like
>>> financials = get_reuters_financials_reindexed_like(
                     closes,
                     coa_codes=["ATOT", "LTLL", "QTCO"])

The resulting DataFrame can be thought of as several stacked DataFrames, with a MultiIndex consisting of the COA (Chart of Account) code, the field (by default only Amount is returned), and the date. Note that get_reuters_financials_reindexed_like shifts fundamental values forward by one day to avoid any lookahead bias.

>>> financials.head()
Sid                        FIBBG4157  FIBBG4165   FIBBG4187  FIBBG4200
CoaCode Field  Date
ATOT    Amount 2018-01-02  21141.294    39769.0  1545.50394   425935.0
               2018-01-03  21141.294    39769.0  1545.50394   425935.0
               2018-01-04  21141.294    39769.0  1545.50394   425935.0
               2018-01-05  21141.294    39769.0  1545.50394   425935.0
               2018-01-08  21141.294    39769.0  1545.50394   425935.0
...
QTCO    Amount 2018-04-03  368.63579      557.0  101.73566  2061.06063
               2018-04-04  368.63579      557.0  101.73566  2061.06063
               2018-04-05  368.63579      557.0  101.73566  2061.06063
               2018-04-06  368.63579      557.0  101.73566  2061.06063
               2018-04-09  368.63579      557.0  101.73566  2061.06063

You can use .loc to isolate particular COA codes and fields and perform matrix operations:

>>> # calculate book value per share
>>> tot_assets = financials.loc["ATOT"].loc["Amount"]
>>> tot_liabilities = financials.loc["LTLL"].loc["Amount"]
>>> shares_out = financials.loc["QTCO"].loc["Amount"]
>>> book_values_per_share = (tot_assets - tot_liabilities)/shares_out
>>> # calculate price-to-book ratio
>>> pb_ratios = closes/book_values_per_share

For best performance, make two separate calls to get_reuters_financials_reindexed_like to retrieve numeric (integer or float) vs non-numeric (string or date) fields. Pandas loads numeric fields in an optimized format compared to non-numeric fields, but mixing numeric and non-numeric fields prevents Pandas from using this optimized format, resulting in slower loads and higher memory consumption.

>>> # DON'T DO THIS
>>> financials = get_reuters_financials_reindexed_like(
                     closes,
                     codes=["ATOT", "SREV"],
                     fields=["Amount", "FiscalPeriodEndDate"]) # numeric and non-numeric fields
>>> tot_assets = financials.loc["ATOT"].loc["Amount"]
>>> fiscal_periods = financials.loc["ATOT"].loc["FiscalPeriodEndDate"]

>>> # DO THIS
>>> financials = get_reuters_financials_reindexed_like(
                     closes,
                     codes=["ATOT", "SREV"],
                     fields=["Amount"]) # numeric fields
>>> tot_assets = financials.loc["ATOT"].loc["Amount"]
>>> financials = get_reuters_financials_reindexed_like(
                     closes,
                     codes=["ATOT", "BVPS"],
                     fields=["FiscalPeriodEndDate"]) # non-numeric fields
>>> fiscal_periods = financials.loc["ATOT"].loc["FiscalPeriodEndDate"]

Reuters financials snippets

Enterprise Multiple (EV/EBITDA)

Enterprise multiple (enterprise value divided by EBITDA) is a popular valuation ratio that is not directly provided by the Reuters datasets. It can be calculated from metrics available in the Reuters financials dataset:

# Formulas:
#
# Enterprise Value:
#
#   EV = market value of common stock + market value of preferred equity + market value of debt + minority interest - cash and investments
#
#   Reuters codes:
#     QTCO: Total Common Shares Outstanding (multiply by price to get market value of common stock)
#     QTPO: Total Preferred Shares Outstanding (multiply by price to get market value of preferred stock)
#     STLD: Total Debt
#     LMIN: Minority Interest
#     ACAE: Cash & Equivalents
#
#   Reuters formula:
#     EV = (price X QTCO) + (price X QTPO) + STLD + LMIN - ACAE
#
# EBITDA
#
#   EBITDA = Operating Profit + Depreciation Expense + Amortization Expense
#
#   Reuters codes:
#     SOPI: Operating Income (= EBIT)
#     SDPR: Depreciation/Amortization
#
#   Reuters formula:
#     EBITDA = SOPI + SDPR

from quantrocket import get_prices
from quantrocket.fundamental import get_reuters_financials_reindexed_like

prices = get_prices("usstock-1d", fields=["Close"])
closes = prices.loc["Close"]

financials = get_reuters_financials_reindexed_like(
    closes,
    ["QTCO", "QTPO", "STLD", "LMIN", "ACAE", "SOPI", "SDPR"])

# EV
shares_out = financials.loc["QTCO"].loc["Amount"]
preferred_shares_out = financials.loc["QTPO"].loc["Amount"]
total_debts = financials.loc["STLD"].loc["Amount"]
minority_interests = financials.loc["LMIN"].loc["Amount"]
cash = financials.loc["ACAE"].loc["Amount"]

market_values_common = prices * shares_out
market_values_preferred = prices * preferred_shares_out.fillna(0)
evs = market_values_common + market_values_preferred + total_debts + minority_interests.fillna(0) - cash

# EBITDA
operating_profits = financials.loc["SOPI"].loc["Amount"]
depr_amorts = financials.loc["SDPR"].loc["Amount"]
ebitdas = operating_profits + depr_amorts.fillna(0)

enterprise_multiples = evs / ebitdas.where(ebitdas > 0)

Current vs prior fiscal period

Sometimes you may wish to calculate the change in a financial metric between the prior and current fiscal period. For example, suppose you wanted to calculate the change in the working capital ratio (defined as total assets / total liabilities). First, query the financial statements and calculate the current ratios:

from quantrocket import get_prices
from quantrocket.fundamental import get_reuters_financials_reindexed_like

prices = get_prices("usstock-1d", fields=["Close"])
closes = prices.loc["Close"]

# ATOT = total assets, LTLL = total liabilities
financials = get_reuters_financials_reindexed_like(
    closes,
    ["ATOT", "LTLL"])

tot_assets = financials.loc["ATOT"].loc["Amount"]
tot_liabilities = financials.loc["LTLL"].loc["Amount"]
current_ratios = tot_assets / tot_liabilities.where(total_liabilities != 0) # avoid DivisionByZero errors

To get the prior year ratios, a simplistic method would be to shift the current ratios forward 1 year (current_ratios.shift(252)), but this would be suboptimal because company reporting dates may not be spaced exactly one year apart. A more reliable approach is shown below:

# query fiscal period end date and get a boolean mask of the first day of each
# newly reported fiscal period
fiscal_periods = get_reuters_financials_reindexed_like(
    closes,
    ["ATOT"],
    fields=["FiscalPeriodEndDate"]).loc["ATOT"].loc["FiscalPeriodEndDate"]
are_new_fiscal_periods = fiscal_periods != fiscal_periods.shift()

# shift the ratios forward one fiscal period by (1) shifting the ratios,
# (2) keeping only the ones that fall on the first day of the newly reported
# fiscal period, and (3) forward-filling
previous_current_ratios = current_ratios.shift().where(are_new_fiscal_periods).fillna(method="ffill")

# Now use the previous and current ratios however desired
ratio_increases = current_ratios > previous_current_ratios

If you want to go back more than one period, you can use the following approach, which is more flexible but has the disadvantage of running slower since the calculation is performed sid by sid:

# query fiscal period end date and get a boolean mask of the first day of each
# newly reported fiscal period
fiscal_periods = get_reuters_financials_reindexed_like(
    closes,
    ["ATOT"],
    fields=["FiscalPeriodEndDate"]).loc["ATOT"].loc["FiscalPeriodEndDate"]
are_new_fiscal_periods = fiscal_periods != fiscal_periods.shift()

periods_ago = 4

# this function will be applied sid by sid and returns a Series of
# earlier fundamentals
def n_periods_ago(fundamentals_for_sid):
    sid = fundamentals_for_sid.name
    # remove all rows except for new fiscal periods
    new_period_fundamentals = fundamentals_for_sid.where(are_new_fiscal_periods[sid]).dropna()
    # Shift the desired number of periods
    earlier_fundamentals = new_period_fundamentals.shift(periods_ago)
    # Reindex and forward-fill to restore original shape
    earlier_fundamentals = earlier_fundamentals.reindex(fundamentals_for_sid.index, method="ffill")
    return earlier_fundamentals

earlier_current_ratios = current_ratios.apply(n_periods_ago)

Sharadar fundamentals

Updated daily, the Sharadar fundamentals dataset provides up to 20 years of history, for 150 essential fundamental indicators and financial ratios, for more than 14,000 US public companies.

Key features:

• More than 5,000 active and 9,000 delisted companies.
• Continuously expanding ticker and indicator coverage, and history extensions.
• Data including or excluding restatements.
• Point-in-time dimension to data with time-indexing to the filing date or the fiscal/report period.
• Includes foreign issuers (ADRs and Canadian) that trade publicly on US markets.
• Annual, Trailing Twelve month, and Quarterly (domestic-only) datasets available.

Collect Sharadar fundamentals

To collect Sharadar fundamental data, specify a country (use FREE for sample data):

$ quantrocket fundamental collect-sharadar-fundamentals --country 'US'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_fundamentals
>>> collect_sharadar_fundamentals(country="US")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/fundamentals?country=US'
{"status": "the fundamental data will be collected asynchronously"}

Collecting the full dataset takes less than 5 minutes. Monitor flightlog for completion:

quantrocket.fundamental: INFO Collecting Sharadar US fundamentals
quantrocket.fundamental: INFO Collecting updated Sharadar US securities listings
quantrocket.fundamental: INFO Finished collecting Sharadar US fundamentals

Query Sharadar fundamentals

The data can be queried by sid, universe, date range, and dimension:

$ quantrocket fundamental sharadar-fundamentals --sids "FIBBG000B9XRY4" --dimensions 'ART' -o aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 6 --max-rows 3
| Sid            |    DATEKEY | DIMENSION | CALENDARDATE |       REVENUE |   EPS |
| -------------- | ---------- | --------- | ------------ | ------------- | ----- |
| FIBBG000B9XRY4 | 2000-02-01 | ART       |   1999-12-31 | 6,767,000,000 | 0.150 |
| FIBBG000B9XRY4 | 2000-05-11 | ART       |   2000-03-31 | 7,182,000,000 | 0.166 |
| FIBBG000B9XRY4 | 2000-07-31 | ART       |   2000-06-30 | 7,449,000,000 | 0.160 |

>>> from quantrocket.fundamental import download_sharadar_fundamentals
>>> download_sharadar_fundamentals(filepath_or_buffer="aapl_fundamentals.csv", sids="FIBBG000B9XRY4", dimensions="ART")
>>> fundamentals = pd.read_csv("aapl_fundamentals.csv", parse_dates=["REPORTPERIOD", "DATEKEY", "CALENDARDATE"])
>>> fundamentals.tail()
              Sid     DATEKEY DIMENSION CALENDARDATE       REVENUE    EPS
0  FIBBG000B9XRY4  2000-02-01       ART   1999-12-31  6.767000e+09  0.150
1  FIBBG000B9XRY4  2000-05-11       ART   2000-03-31  7.182000e+09  0.166
2  FIBBG000B9XRY4  2000-07-31       ART   2000-06-30  7.449000e+09  0.160
3  FIBBG000B9XRY4  2000-12-14       ART   2000-09-30  7.983000e+09  0.173
4  FIBBG000B9XRY4  2001-02-12       ART   2000-12-31  6.647000e+09  0.091

$ curl -X GET 'http://houston/fundamental/sharadar/fundamentals.csv?sids=FIBBG000B9XRY4&dimensions=ART' > aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 6 --max-rows 3
| Sid            |    DATEKEY | DIMENSION | CALENDARDATE |       REVENUE |   EPS |
| -------------- | ---------- | --------- | ------------ | ------------- | ----- |
| FIBBG000B9XRY4 | 2000-02-01 | ART       |   1999-12-31 | 6,767,000,000 | 0.150 |
| FIBBG000B9XRY4 | 2000-05-11 | ART       |   2000-03-31 | 7,182,000,000 | 0.166 |
| FIBBG000B9XRY4 | 2000-07-31 | ART       |   2000-06-30 | 7,449,000,000 | 0.160 |

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Sharadar fundamental data that is aligned to the price data. This makes it easy to perform matrix operations using fundamental data.

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2018-04-16", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_sharadar_fundamentals_reindexed_like
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                       closes,
                       fields=["EPS", "REVENUE", "EVEBITDA"],
                       dimension="ARQ")

The resulting DataFrame can be thought of as several stacked DataFrames, with a MultiIndex consisting of the field (indicator code) and the date. The columns are sids, matching the input DataFrame. The DataFrame gives each indicator's current value as of the given date. The function get_sharadar_fundamentals_reindexed_like shifts values forward by one day (based on the DATEKEY field) to avoid lookahead bias.

>>> fundamentals.head()
Sid                           FIBBG000B9XRY4  FIBBG000BFWKC0  FIBBG000BKZB36  FIBBG000BMHYD1
Field    Date
EPS      2018-04-16 00:00:00            3.92            3.31            1.54           -3.98
         2018-04-17 00:00:00            3.92            3.31            1.54           -3.98
         2018-04-18 00:00:00            3.92            3.31            1.54           -3.98
         2018-04-19 00:00:00            3.92            3.31            1.54           -3.98
         2018-04-20 00:00:00            3.92            3.31            1.54           -3.98
...
EVEBITDA 2020-03-31 00:00:00          18.297          13.724          12.712          16.342
         2020-04-01 00:00:00          18.297          13.724          12.712          16.342
         2020-04-02 00:00:00          18.297          13.724          12.712          16.342
         2020-04-03 00:00:00          18.297          13.724          12.712          16.342
         2020-04-06 00:00:00          18.297          13.724          12.712          16.342

You can use .loc to isolate a particular indicator:

>>> enterprise_multiples = fundamentals.loc["EVEBITDA"]

For best performance, make two separate calls to get_sharadar_fundamentals_reindexed_like to retrieve numeric (integer or float) vs non-numeric (string or date) fields. Pandas loads numeric fields in an optimized format compared to non-numeric fields, but mixing numeric and non-numeric fields prevents Pandas from using this optimized format, resulting in slower loads and higher memory consumption.

>>> # DON'T DO THIS
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                     closes,
                     fields=["EPS", "REPORTPERIOD"], # numeric and non-numeric fields
                     dimension="ARQ")
>>> eps = fundamentals.loc["EPS"]
>>> fiscal_period_end_dates = fundamentals.loc["REPORTPERIOD"]

>>> # DO THIS
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                     closes,
                     fields=["EPS"], # numeric fields
                     dimension="ARQ")
>>> eps = fundamentals.loc["EPS"]
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                     closes,
                     fields=["REPORTPERIOD"], # non-numeric fields
                     dimension="ARQ")
>>> fiscal_period_end_dates = fundamentals.loc["REPORTPERIOD"]

Sharadar fundamentals data guide

Dimensions

The two primary dimensions to the database are the As Reported (AR) and Most-Recent Reported (MR) dimensions:

As Reported view (AR)

• excludes restatements
• point-in-time view with data time-indexed to the date the form 10 regulatory filing was submitted to the SEC
• presents data for the latest reporting period at that filing date
• may include multiple observations in a quarter if more than one filing is made during the quarter
• on limited occassion may not have any observations in a particular quarter. Sometimes companies are delayed in reporting for up to 18 months. On such occassions they may report multiple documents on the same date to catch up, in which case these datasets will only provide date for the most recent reporting period.
• most suitable for back-testing

Most-Recent Reported view (MR)

• includes restatements
• time indexed to the financial/report period presents the most recently reported data for that reporting period
• typically suitable for assessing business performance after restatements for mergers/divestitures

In addition there are 3 time dimensions:

• Annual (Y): Annual observations of one year duration
• Trailing Twelve Months (T): Quarterly observations of one year duration
• Quarterly (Q): Quarterly observations of quarterly duration (available only for US domestic companies, unavailable for foreign companies)

Time-indexing

As previously noted, the As-Reported dimensions present a point-in-time view with data time-indexed to the date of the form 10 regulatory filing to the SEC. This is in order to more closely align with the date that information was disseminated to the market, and the corresponding market impact. This is is a more accurate measure than the reporting period which the Most-Recent Reported dimensions utilize, which are typically months before the information reaches the market, and subject to restatement. However, it must be noted that the information contained in the form 10 may have been separately disclosed to the market days (or on rare occasion - weeks) earlier under separate form 8 regulatory filing. It is safe to assume that the information would have been available the day after the As-Reported date (at the latest). We source our data from a company's form 10 filing rather than their form 8 filing since the form 8 filings do not consistently contain full consolidated financial statements.

Negative P/E Ratios

Where a company reports negative earnings it's calculated PE (or PE1) ratio will be negative - please be aware of this when filtering for low P/E ratios.

Exception Handling

SHARESWADIL and EPSDIL are not consistently reported by all companies, and there is a higher incidence of non-availability of both these indicators, and the DILUTIONRATIO indicator which is subsequently derived.

Ratios which have zero in the denominator cannot be calculated and will be blank. For example, where a company's trailing twelve month EPS sums to 0.0 the subsequently derived PE1 indicator cannot be calculated. Therefore due to the unavailability of "N/A" values there will be no observation returned. This also applies to ROS, NETMARGIN, PS, PS1, GROSSMARGIN and EBITDAMARGIN for companies that have zero REVENUE. Companies that have zero revenue are generally, but not exclusively, early stage Biotech firms.

Not all companies operate a classified Balance Sheet, approximately 20% of the companies in the database do not, most of which are financial firms. As such: ASSETSC (Current Assets) & LIABILITIESC (Current Liabilities), and the subsequently derived ASSETSNC, LIABILITIESNC, CURRENTRATIO and WORKINGCAPITAL, are not reported for all companies. In addition, companies can change their financial statement presentation and start or stop operating a classified Balance Sheet, therefore there may be gaps in the availability of these indicators.

Newly listed companies may not have the four quarters of reporting history required to calculate the trailing twelve month dimension, therefore the dataset may be blank until this history is available.

On limited occasion Annual and Quarterly financial statement presentation does not conform. For example, sometimes companies only report DEPAMOR, INTEXP and/or TAXEXP annually and not quarterly. In these instances the quarterly values will not sum to the annual values.

Update schedule

Data is updated daily by 5 AM New York time.

How soon after a company reports will the database be updated? The database is updated within 24 hours of the form 10 SEC filing. Note that companies may report abbreviated financial statements via a separate form 8 SEC filing days or on occasion weeks before the form 10 filing. We do not source our data from the form 8 filing since it does not reliably contain full consolidated financial statements (income statement, balance sheet & cash flow statement).

"N/A" values (non-reported items)

The treatment of N/A values depends on the indicator. For example, if a company has no DEBT on it's balance sheet then this means the value is zero. If a company doesn't report ASSETSC (Current Assets) on it's balance sheet - this does not mean that the value is zero. In this instance the appropriate value is "N/A", however, "N/A" is not presently supported on the platform and therefore nothing is reported on this date. For detail on how N/A values are treated for each indicator please see the individual indicator descriptions (eg REVENUE).

Sharadar fundamental indicators

Income Statement

Cash Flow Statement

Balance Sheet

Metrics

Entity

Sharadar insiders

This database provides insider holdings and transactions for more than 15,000 issuers and 200,000 insiders. Data are sourced from SEC form 3, 4 & 5 filings.

Collect Sharadar insiders

To collect Sharadar insiders data, specify a country (use FREE for sample data):

$ quantrocket fundamental collect-sharadar-insiders --country 'US'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_insiders
>>> collect_sharadar_insiders(country="US")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/insiders?country=US'
{"status": "the fundamental data will be collected asynchronously"}

Collecting the full dataset takes less than 5 minutes. Monitor flightlog for completion:

quantrocket.fundamental: INFO Collecting Sharadar US insider holdings data
quantrocket.fundamental: INFO Collecting updated Sharadar US securities listings
quantrocket.fundamental: INFO Finished collecting Sharadar US insider holdings data

Query Sharadar insiders

The data can be queried by sid, universe, and date range:

$ quantrocket fundamental sharadar-insiders -i 'FIBBG000B9XRY4' -o aapl_insiders.csv
$ csvlook aapl_insiders.csv --max-columns 9 --max-rows 5
| Sid            | TICKER | FILINGDATE | FORMTYPE | ISSUERNAME | OWNERNAME           | OFFICERTITLE          | ISDIRECTOR | ISOFFICER | ... |
| -------------- | ------ | ---------- | -------- | ---------- | ------------------- | --------------------- | ---------- | --------- | --- |
| FIBBG000B9XRY4 | AAPL   | 2005-01-05 | 4        | APPLE INC  | RUBINSTEIN JONATHAN | Senior Vice President |      False |      True | ... |
| FIBBG000B9XRY4 | AAPL   | 2005-01-05 | 4        | APPLE INC  | RUBINSTEIN JONATHAN | Senior Vice President |      False |      True | ... |
| FIBBG000B9XRY4 | AAPL   | 2005-01-11 | 4        | APPLE INC  | SERLET BERTRAND     | Senior Vice President |      False |      True | ... |

>>> from quantrocket.fundamental import download_sharadar_insiders
>>> download_sharadar_insiders(filepath_or_buffer="aapl_insiders.csv", sids="FIBBG000B9XRY4")
>>> insiders = pd.read_csv("aapl_insiders.csv", parse_dates=["FILINGDATE"])
>>> insiders.tail()
               Sid TICKER FILINGDATE FORMTYPE ISSUERNAME            OWNERNAME           OFFICERTITLE
0   FIBBG000B9XRY4   AAPL 2005-01-05        4  APPLE INC  RUBINSTEIN JONATHAN  Senior Vice President
1   FIBBG000B9XRY4   AAPL 2005-01-05        4  APPLE INC  RUBINSTEIN JONATHAN  Senior Vice President
3   FIBBG000B9XRY4   AAPL 2005-01-11        4  APPLE INC      SERLET BERTRAND  Senior Vice President

$ curl -X GET 'http://houston/fundamental/sharadar/insiders.csv?sids=FIBBG000B9XRY4' > aapl_insiders.csv
$ csvlook aapl_insiders.csv --max-columns 9 --max-rows 5
| Sid            | TICKER | FILINGDATE | FORMTYPE | ISSUERNAME | OWNERNAME           | OFFICERTITLE          | ISDIRECTOR | ISOFFICER | ... |
| -------------- | ------ | ---------- | -------- | ---------- | ------------------- | --------------------- | ---------- | --------- | --- |
| FIBBG000B9XRY4 | AAPL   | 2005-01-05 | 4        | APPLE INC  | RUBINSTEIN JONATHAN | Senior Vice President |      False |      True | ... |
| FIBBG000B9XRY4 | AAPL   | 2005-01-05 | 4        | APPLE INC  | RUBINSTEIN JONATHAN | Senior Vice President |      False |      True | ... |
| FIBBG000B9XRY4 | AAPL   | 2005-01-11 | 4        | APPLE INC  | SERLET BERTRAND     | Senior Vice President |      False |      True | ... |

Sharadar insiders data guide

A sample record from the dataset including field descriptions is shown below:

Sid: "FIBBG000B9XRY4" # Security ID
TICKER: "AAPL"
FILINGDATE: "2005-01-05" # Filing Date - The date the form was filed with the SEC.
FORMTYPE: 4 # Form Type - The type of SEC form . Available options are 3; 4 or 5 that the data are sourced from. Preprended by "RESTATED" in the event that the filing is subsequently restated.
ISSUERNAME: "APPLE INC" # Issuer Name - The name of the security issuer.
OWNERNAME: "RUBINSTEIN JONATHAN" # Owner Name - The name of the insider.
OFFICERTITLE: "Senior Vice President" # Officer Title - Is the owner is an officer of the company the officer's title is provided.
ISDIRECTOR: "N" # Is Director? - Is the owner a Board Director? [Y]es or [N]o.
ISOFFICER: "Y" # Is Officer? - Is the owner an officer of the company? [Y]es or [N]o.
ISTENPERCENTOWNER: "N" # Is Ten Percent Owner? - Does the owner hold ten percent or more of the class of security? [Y]es or [N]o.
TRANSACTIONDATE: "2005-01-03" # Transaction Date - If there has been a transaction; the date of the transaction is provided here.
SECURITYADCODE: "ND" # Security Acquired/Disposed Code - [D] Derivative; No Transaction [DA] Derivative Acquisition [DD] Derivative Disposition [N] Non-Derivative; No Transaction [NA] Non-Derivative Acquisition [ND] Non-Derivative Disposition
TRANSACTIONCODE: "M" # Transaction Code - The available [Transaction Codes] [Transaction Categories] Descriptions are as follows: [P] [General] Open market or private purchase of non-derivative or derivative security [S] [General] Open market or private sale of non-derivative or derivative security [V] [General] Transaction voluntarily reported earlier than required [A] [Rule 16b-3] Grant; award or other acquisition pursuant to Rule 16b-3(d) [D] [Rule 16b-3] Disposition to the issuer of issuer equity securities pursuant to Rule 16b-3(e) [F] [Rule 16b-3] Payment of exercise price or tax liability by delivering or withholding securities [I] [Rule 16b-3] Discretionary transaction in accordance with Rule 16b-3(f) [M] [Rule 16b-3] Exercise or conversion of derivative security exempted pursuant to Rule 16b-3 [C] [Derivative Codes] Conversion of derivative security [E] [Derivative Codes] Expiration of short derivative position [H] [Derivative Codes] Expiration (or cancellation) of long derivative position with value received [O] [Derivative Codes] Exercise of out-of-the-money derivative security [X] [Derivative Codes] Exercise of in-the-money or at-the-money derivative security [G] [Other Section 16(b) Exempt] Bona fide gift [L] [Other Section 16(b) Exempt] Small acquisition under Rule 16a-6 [W] [Other Section 16(b) Exempt] Acquisition or disposition by will or the laws of descent and distribution [Z] [Other Section 16(b) Exempt] Deposit into or withdrawal from voting trust [J] [Other] Other acquisition or disposition [K] [Other] Transaction in equity swap or instrument with similar characteristics [U] [Other] Disposition pursuant to a tender of shares in a change of control transaction
SHARESOWNEDBEFORETRANSACTION: 45087 # Shares Owned Before Transaction - The number of shares owned before the transaction.
TRANSACTIONSHARES: -34000 # Transaction Shares - The number of shares transacted.
SHARESOWNEDFOLLOWINGTRANSACTION: 11087 # Shares Owned Following Transaction - The number of shares owned following the transaction.
TRANSACTIONPRICEPERSHARE: 17.313 # Transaction Price per Share - The transaction price per share.
TRANSACTIONVALUE: 588642 # Transaction Value - The value of the transaction.
SECURITYTITLE: "Common Stock" # Security Title - The title of the class of security.
DIRECTORINDIRECT: "D" # Direct or Indirect? - Is the ownership held [D]irectly or [I]ndirectly?
NATUREOFOWNERSHIP: null  # Nature of Ownership - Where the ownership is held through an investment vehicle (trust; fund etc) the name of that investment vehicle is provided here.
DATEEXERCISABLE: null # Date Exercisable - The date that an option is exercisable; where applicable and available.
PRICEEXERCISABLE: null # Price Exercisable - The price at which an option is exercisable; where applicable and available.
EXPIRATIONDATE: null # Expiration Date - The data at which an option expires; where applicable and available
ROWNUM: 1 # Row number - The record number for a particular owner and filing date; which forms part of the key for the record.

Update schedule

Data is updated daily by 5 AM New York time.

Notes from the data provider

• data are sourced from SEC form 3, 4 and 5.
• The SHARESOWNEDBEFORETRANSACTION and SHARESOWNEDFOLLOWINGTRANSACTION are as reported in the underlying SEC filings. There is some complexity to them which it is necessary to bear in mind. At a minimum these fields represent separate sub-totals for each of derivative and non-derivative holdings, identifiable through the SECURITYADCODE field. Some filers segment this further to represent subtotals for DIRECTORINDIRECT holdings and/or SECURITYTITLE.
• data are currently not adjusted for stock splits.
• where a filing has been subsequently restated the FORMTYPE field of the restated filing will be prepended with "RESTATED".

Sharadar institutions

This dataset provides institutional investor holdings data for 20,000+ issuers and approximately 6,000 investors, covering all types of securities reported, categorised into: common shares, funds, calls, puts, warrants, preferred stock, and debt.

Data are sourced from SEC form 13F filings, which requires that medium to large institutional investment managers report details of certain US security holdings.

Collect Sharadar institutions

To collect Sharadar institutional ownership data, specify a country (use FREE for sample data):

$ quantrocket fundamental collect-sharadar-institutions --country 'US'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_institutions
>>> collect_sharadar_institutions(country="US")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/institutions?country=US'
{"status": "the fundamental data will be collected asynchronously"}

Monitor flightlog for completion:

quantrocket.fundamental: INFO Collecting Sharadar US institutional investor data
quantrocket.fundamental: INFO Collecting updated Sharadar US securities listings
quantrocket.fundamental: INFO Finished collecting Sharadar US institutional investor data

By default the collected data is aggregated by security; that is, there is a separate record per security per quarter. It is also possible to collect detailed, non-aggregated records; that is, a separate record per investor per security per quarter. Use the --detail/detail=True parameter. Detailed data is stored in a separate database, allowing you to collect both the detailed and aggregated views of the data:

$ quantrocket fundamental collect-sharadar-institutions --country 'US' --detail
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_institutions
>>> collect_sharadar_institutions(country="US", detail=True)
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/institutions?country=US&detail=true'
{"status": "the fundamental data will be collected asynchronously"}

Query Sharadar institutions

The data can be queried by sid, universe, and date range:

$ quantrocket fundamental sharadar-institutions -i 'FIBBG000B9XRY4' -o aapl_institutions.csv
$ csvlook aapl_institutions.csv --max-columns 9 --max-rows 5
 Sid             | CALENDARDATE | TICKER | NAME      | SHRHOLDERS | CLLHOLDERS | PUTHOLDERS | WNTHOLDERS | DBTHOLDERS | ... |
| -------------- | ------------ | ------ | --------- | ---------- | ---------- | ---------- | ---------- | ---------- | --- |
| FIBBG000B9XRY4 |   2013-06-30 | AAPL   | APPLE INC |      1,855 |         89 |         61 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2013-09-30 | AAPL   | APPLE INC |      1,881 |        107 |         63 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2013-12-31 | AAPL   | APPLE INC |      2,066 |         86 |         57 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2014-03-31 | AAPL   | APPLE INC |      2,040 |         81 |         67 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2014-06-30 | AAPL   | APPLE INC |      2,110 |         98 |         66 |      False |          0 | ... |

>>> from quantrocket.fundamental import download_sharadar_institutions
>>> download_sharadar_institutions(filepath_or_buffer="aapl_institutions.csv", sids="FIBBG000B9XRY4")
>>> institutions = pd.read_csv("aapl_institutions.csv", parse_dates=["CALENDARDATE"])
>>> institutions.head()
              Sid CALENDARDATE TICKER       NAME  SHRHOLDERS  CLLHOLDERS  PUTHOLDERS ...
0  FIBBG000B9XRY4   2013-06-30   AAPL  APPLE INC        1855          89          61
1  FIBBG000B9XRY4   2013-09-30   AAPL  APPLE INC        1881         107          63
2  FIBBG000B9XRY4   2013-12-31   AAPL  APPLE INC        2066          86          57
3  FIBBG000B9XRY4   2014-03-31   AAPL  APPLE INC        2040          81          67
4  FIBBG000B9XRY4   2014-06-30   AAPL  APPLE INC        2110          98          66

$ curl -X GET 'http://houston/fundamental/sharadar/institutions.csv?sids=FIBBG000B9XRY4' > aapl_institutions.csv
$ csvlook aapl_institutions.csv --max-columns 9 --max-rows 5
 Sid             | CALENDARDATE | TICKER | NAME      | SHRHOLDERS | CLLHOLDERS | PUTHOLDERS | WNTHOLDERS | DBTHOLDERS | ... |
| -------------- | ------------ | ------ | --------- | ---------- | ---------- | ---------- | ---------- | ---------- | --- |
| FIBBG000B9XRY4 |   2013-06-30 | AAPL   | APPLE INC |      1,855 |         89 |         61 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2013-09-30 | AAPL   | APPLE INC |      1,881 |        107 |         63 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2013-12-31 | AAPL   | APPLE INC |      2,066 |         86 |         57 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2014-03-31 | AAPL   | APPLE INC |      2,040 |         81 |         67 |      False |          0 | ... |
| FIBBG000B9XRY4 |   2014-06-30 | AAPL   | APPLE INC |      2,110 |         98 |         66 |      False |          0 | ... |

$ quantrocket fundamental sharadar-institutions -i 'FIBBG000B9XRY4' --detail -o aapl_institutions.csv
$ csvlook aapl_institutions.csv --max-columns 9 --max-rows 5
| Sid            | TICKER | INVESTORNAME                | SECURITYTYPE | CALENDARDATE |      VALUE |   UNITS | PRICE |
| -------------- | ------ | --------------------------- | ------------ | ------------ | ---------- | ------- | ----- |
| FIBBG000B9XRY4 | AAPL   | 1832 ASSET MANAGEMENT LP    | SHR          |   2013-06-30 | 16,159,000 |  40,910 |   394 |
| FIBBG000B9XRY4 | AAPL   | 1919 INVESTMENT COUNSEL LLC | SHR          |   2013-06-30 | 64,522,000 | 162,716 |   396 |
| FIBBG000B9XRY4 | AAPL   | 1ST GLOBAL ADVISORS INC     | SHR          |   2013-06-30 |    250,000 |     630 |   396 |
| FIBBG000B9XRY4 | AAPL   | 1ST SOURCE BANK             | SHR          |   2013-06-30 |  4,571,000 |  11,527 |   396 |
| FIBBG000B9XRY4 | AAPL   | 300 NORTH CAPITAL LLC       | SHR          |   2013-06-30 |  1,496,000 |   3,776 |   396 |

>>> from quantrocket.fundamental import download_sharadar_institutions
>>> download_sharadar_institutions(filepath_or_buffer="aapl_institutions.csv", sids="FIBBG000B9XRY4", detail=True)
>>> institutions = pd.read_csv("aapl_institutions.csv", parse_dates=["CALENDARDATE"])
>>> institutions.head()
              Sid TICKER                 INVESTORNAME SECURITYTYPE CALENDARDATE       VALUE   UNITS
0  FIBBG000B9XRY4   AAPL     1832 ASSET MANAGEMENT LP          SHR   2013-06-30  16159000.0   40910
1  FIBBG000B9XRY4   AAPL  1919 INVESTMENT COUNSEL LLC          SHR   2013-06-30  64522000.0  162716
2  FIBBG000B9XRY4   AAPL      1ST GLOBAL ADVISORS INC          SHR   2013-06-30    250000.0     630
3  FIBBG000B9XRY4   AAPL              1ST SOURCE BANK          SHR   2013-06-30   4571000.0   11527
4  FIBBG000B9XRY4   AAPL        300 NORTH CAPITAL LLC          SHR   2013-06-30   1496000.0    3776

$ curl -X GET 'http://houston/fundamental/sharadar/institutions.csv?sids=FIBBG000B9XRY4&detail=true' > aapl_institutions.csv
$ csvlook aapl_institutions.csv --max-columns 9 --max-rows 5
| Sid            | TICKER | INVESTORNAME                | SECURITYTYPE | CALENDARDATE |      VALUE |   UNITS | PRICE |
| -------------- | ------ | --------------------------- | ------------ | ------------ | ---------- | ------- | ----- |
| FIBBG000B9XRY4 | AAPL   | 1832 ASSET MANAGEMENT LP    | SHR          |   2013-06-30 | 16,159,000 |  40,910 |   394 |
| FIBBG000B9XRY4 | AAPL   | 1919 INVESTMENT COUNSEL LLC | SHR          |   2013-06-30 | 64,522,000 | 162,716 |   396 |
| FIBBG000B9XRY4 | AAPL   | 1ST GLOBAL ADVISORS INC     | SHR          |   2013-06-30 |    250,000 |     630 |   396 |
| FIBBG000B9XRY4 | AAPL   | 1ST SOURCE BANK             | SHR          |   2013-06-30 |  4,571,000 |  11,527 |   396 |
| FIBBG000B9XRY4 | AAPL   | 300 NORTH CAPITAL LLC       | SHR          |   2013-06-30 |  1,496,000 |   3,776 |   396 |

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Sharadar institutional data (aggregated by security) that is aligned to the price data.

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2018-04-16", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_sharadar_institutions_reindexed_like
>>> insti = get_sharadar_institutions_reindexed_like(closes, fields=["SHRVALUE"])

The resulting DataFrame can be thought of as several stacked DataFrames, with a MultiIndex consisting of the field and the date. The columns are sids, matching the input DataFrame. The DataFrame is forward-filled, giving each field's latest value as of the given date.

>>> insti.head()
Sid                  FIBBG000B9XRY4  FIBBG000BVPV84  FIBBG000CL9VN6  FIBBG000MM2P62
Field    Date
SHRVALUE 2019-12-16    5.889395e+11    4.816904e+11    9.471188e+10    3.238884e+11
         2019-12-17    5.889395e+11    4.816904e+11    9.471188e+10    3.238884e+11
         2019-12-18    5.889395e+11    4.816904e+11    9.471188e+10    3.238884e+11
         2019-12-19    5.889395e+11    4.816904e+11    9.471188e+10    3.238884e+11
         2019-12-20    5.889395e+11    4.816904e+11    9.471188e+10    3.238884e+11

By default, values are shifted forward by 45 days to account for the reporting lag (see the data provider's notes below); this can be controled with the shift parameter.

You can use .loc to isolate a particular indicator:

>>> insti_share_values = insti.loc["SHRVALUE"]

For best performance, make two separate calls to get_sharadar_institutions_reindexed_like to retrieve numeric (integer or float) vs non-numeric (string or date) fields. Pandas loads numeric fields in an optimized format compared to non-numeric fields, but mixing numeric and non-numeric fields prevents Pandas from using this optimized format, resulting in slower loads and higher memory consumption. See the Sharadar fundamentals docs for an example.

Sharadar institutions data guide

A sample aggregated (non-detailed) record from the dataset including field descriptions is shown below:

Sid: "FIBBG000B9XRY4" # Security ID
CALENDARDATE: "2013-06-30" # Calendar Date - The calendar date field represents the last day of the calendar quarter.
TICKER: "AAPL"
NAME: "APPLE INC" # Issuer Name - The name of the issuer.
SHRHOLDERS: 1855 # Number of Shareholders (Institutional) - The number of shareholders.
CLLHOLDERS: 89 # Number of Call holders (Institutional) - The number of call holders.
PUTHOLDERS: 61 # Number of Put holders (institutional) - The number of put holders.
WNTHOLDERS: 0 # Number of Warrant holders (institutional) - The number of warrant holders.
DBTHOLDERS: 0 # Number of Debt holders (institutional) - The number of debt holders.
PRFHOLDERS: 0 # Number of Preferred Stock holders (institutional) - The number of preferred stock holders.
FNDHOLDERS: 0 # Number of Fund holders (institutional) - The number of fund holders.
UNDHOLDERS: 0 # Number of Unidentified Security type holders (institutional) - The number of unidentified security type holders.
SHRUNITS: 552964087 # Number of Share Units held (institutional) - The total number of share units held.
CLLUNITS: 46560649 # Number of Call Units held (institutional) - The total number of call units held.
PUTUNITS: 49769940 # Number of Put Units held (institutional) - The total number of put units held.
WNTUNITS: 0 # Number of Warrant Units held (institutional) - The total number of warrant units held.
DBTUNITS: 0 # Number of Debt Units held (institutional) - The total number of debt units held.
PRFUNITS: 0 # Number of Preferred Stock units held (institutional) - The total number of preferred stock units held.
FNDUNITS: 0 # Number of Fund units held (institutional) - The total number of fund units held.
UNDUNITS: 0 # Number of Unidentified Security type units held (institutional) - The total number of unidentified security type units held.
SHRVALUE: 219200769570 # Value of Share units held (institutional) - The total value of share units held.
CLLVALUE: 17952276435 # Value of Call units held (institutional) - The total value of call units held.
PUTVALUE: 20366468206 # Value of Put units held (institutional) - The total value of put units held.
WNTVALUE: 0 # Value of Warrant units held (institutional) - The total value of warrant units held.
DBTVALUE: 0 # Value of Debt units held (institutional) - The total value of debt units held.
PRFVALUE: 0 # Value of Preferred Stock units held (institutional) - The total value of preferred stock units held.
FNDVALUE: 0 # Value of Fund units held (institutional) - The total value of fund units held.
UNDVALUE: 0 # Value of Unidentified Security type units held (institutional) - The total value of unidentified security type units held.
TOTALVALUE: 257519514211 # Total Value of all Security types held (institutional) - The total value of all security types held.
PERCENTOFTOTAL: 1.46 # Percentage of Total Institutional Holdings for the Quarter - The percentage that the [TotalValue] of this line item constitutes of all institutional holdings for this quarter.

A sample detailed record is shown below:

Sid: "FIBBG000B9XRY4" # Security ID
TICKER: "AAPL"
INVESTORNAME: "WAVERTON INVESTMENT MANAGEMENT LTD" # Institutional Investor Name - The investor name is a unique identifier for the institutional investor.
SECURITYTYPE: "SHR" # Security Type - The available options to filter the SecurityType field are as follows: [SHR] Common Shares [FND] Fund Units [CLL] Call Options [PUT] Put Options [WNT] Warrants [DBT] Debt [PRF] Preferred Shares [UND] Unidentified Security Type
CALENDARDATE: "2013-06-30" # Calendar Date - The calendar date field represents the last day of the calendar quarter.
VALUE: 17385000 # Value - The total USD value of the current line item.
UNITS: 43842 # Units - The number of units in the current line item.
PRICE: 396 # Price - The imputed price per unit of the current line item.

Update schedule

Data is updated daily by 5 AM New York time.

Notes from the data provider

• Data are sourced from SEC form 13F filings, which require that medium to large institutional investment managers report details of certain US security holdings. This means that the database may not contain: the smaller investors in a particular security; 100% of the securities that an investor holds; and the large investors in a small security if that investor is not large enough to be subject to SEC form 13F disclosure. More information on SEC form 13F reporting can be found on the SEC's website.
• Reporting by large managers is generally of high quality, however, there is a small percentage of reporting errors that are made. We identify and correct many but not all of these, and are continuously improving our efforts to do so where possible.
• Where errors are made, the reporting investment manager may restate their prior prior holdings. We will update our records accordingly and always present the most up to date record of holdings for a particular period.
• The reporting deadline is 45 days after the end of the quarter. For example by May 15th for the quarter ending March 31st. As such the most recent quarter holdings is typically incomplete until the end of this 45 day deadline as a high percentage of investors report their holdings as late as possible.
• On very limited occasions investors may have permission to delay disclosure of certain new holdings, for example Berkshire Hathaway has done so in the past. This means that from time-to-time there is a small window after the 45 day reporting deadline where newly reported data is incomplete for a particular investor, until they report the new holdings.
• Investors occasionally report securities where either the issuer or share class are unidentifiable. Generally this is the case when the investor is reporting securities which are not required to be reported to the SEC, eg for private companies or for foreign listed stocks. We assign these the UND security type, and the ticker U10D.
• Data is currently not adjusted for stock splits.

Sharadar SEC Form 8-K

This dataset provides corporate events data as reported on SEC Form 8-K.

Collect Sharadar SEC Form 8-K

To collect Sharadar SEC Form 8-K data, specify the country as US (use FREE for sample data):

$ quantrocket fundamental collect-sharadar-sec8 --country 'US'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_sec8
>>> collect_sharadar_sec8(country="US")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/sec8?country=US'
{"status": "the fundamental data will be collected asynchronously"}

Monitor flightlog for completion:

quantrocket.fundamental: INFO Collecting Sharadar US SEC Form 8-K events
quantrocket.fundamental: INFO Collecting updated Sharadar US securities listings
quantrocket.fundamental: INFO Finished collecting Sharadar US SEC Form 8-K events

Query Sharadar SEC Form 8-K

The data can be queried by sid, universe, date range, or event code:

$ quantrocket fundamental sharadar-sec8 --event-codes '13' -o bankruptcies.csv
$ csvlook bankruptcies.csv --max-rows 5
| Sid            |       DATE | TICKER | EVENTCODE |
| -------------- | ---------- | ------ | --------- |
| FIBBG000BXNJ07 | 1994-01-05 | CY     |        13 |
| FIBBG000BCPB71 | 1994-01-06 | AVT    |        13 |
| FIBBG000BRKN86 | 1994-01-20 | PPW    |        13 |
| FIBBG000DM86Y7 | 1994-01-21 | CCI1   |        13 |
| FIBBG000BKFZM4 | 1994-01-24 | GLW    |        13 |

>>> from quantrocket.fundamental import download_sharadar_sec8
>>> download_sharadar_sec8(filepath_or_buffer="bankruptcies.csv", event_codes=[13])
>>> bankruptcies = pd.read_csv("bankruptcies.csv", parse_dates=["DATE"])
>>> bankruptcies.head()
              Sid       DATE TICKER  EVENTCODE
0  FIBBG000BXNJ07 1994-01-05     CY         13
1  FIBBG000BCPB71 1994-01-06    AVT         13
2  FIBBG000BRKN86 1994-01-20    PPW         13
3  FIBBG000DM86Y7 1994-01-21   CCI1         13
4  FIBBG000BKFZM4 1994-01-24    GLW         13

$ curl -X GET 'http://houston/fundamental/sharadar/sec8.csv?event_codes=13' > bankruptcies.csv
$ csvlook bankruptcies.csv --max-rows 5
| Sid            |       DATE | TICKER | EVENTCODE |
| -------------- | ---------- | ------ | --------- |
| FIBBG000BXNJ07 | 1994-01-05 | CY     |        13 |
| FIBBG000BCPB71 | 1994-01-06 | AVT    |        13 |
| FIBBG000BRKN86 | 1994-01-20 | PPW    |        13 |
| FIBBG000DM86Y7 | 1994-01-21 | CCI1   |        13 |
| FIBBG000BKFZM4 | 1994-01-24 | GLW    |        13 |

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Sharadar SEC Form 8-K data that is aligned to the price data.

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2018-04-16", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_sharadar_sec8_reindexed_like
>>> filed_for_bankruptcy = get_sharadar_sec8_reindexed_like(closes, event_codes=[13])

The function returns a Boolean DataFrame indicating whether the company filed SEC Form 8-K on that date for any of the requested event_codes. The columns and index match the input DataFrame.

>>> filed_for_bankruptcy.head()
Sid         FIBBG000B9XRY4  FIBBG000PX3XC0  FIBBG009S3NB30
Date
2020-03-30           False           False           False
2020-03-31           False           False           False
2020-04-01           False            True           False
2020-04-02           False           False           False
2020-04-03           False           False           False

Sharadar SEC Form 8-K data guide

The SEC Form 8-K event codes are shown below:

11: 'Entry into a Material Definitive Agreement'
12: 'Termination of a Material Definitive Agreement'
13: 'Bankruptcy or Receivership'
14: 'Mine Safety: 'Reporting of Shutdowns and Patterns of Violations'
15: 'Receipt of an Attorney's Written Notice Pursuant to 17 CFR 205.3(d)'
21: 'Completion of Acquisition or Disposition of Assets'
22: 'Results of Operations and Financial Condition'
23: 'Creation of a Direct Financial Obligation or an Obligation under an Off-Balance Sheet Arrangement of a Registrant'
24: 'Triggering Events That Accelerate or Increase a Direct Financial Obligation or an Obligation under an Off-Balance Sheet Arrangement'
25: 'Cost Associated with Exit or Disposal Activities'
26: 'Material Impairments'
31: 'Notice of Delisting or Failure to Satisfy a Continued Listing Rule or Standard; Transfer of Listing'
32: 'Unregistered Sales of Equity Securities'
33: 'Material Modifications to Rights of Security Holders'
34: 'Schedule 13G Filing'
35: 'Schedule 13D Filing'
36: 'Notice under Rule 12b25 of inability to timely file all or part of a Form 10-K or 10-Q'
40: 'Changes in Registrant's Certifying Accountant'
41: 'Changes in Registrant's Certifying Accountant'
42: 'Non-Reliance on Previously Issued Financial Statements or a Related Audit Report or Completed Interim Review'
51: 'Changes in Control of Registrant'
52: 'Departure of Directors or Certain Officers; Election of Directors; Appointment of Certain Officers: Compensatory Arrangements of Certain Officers'
53: 'Amendments to Articles of Incorporation or Bylaws; and/or Change in Fiscal Year'
54: 'Temporary Suspension of Trading Under Registrant's Employee Benefit Plans'
55: 'Amendments to the Registrant's Code of Ethics; or Waiver of a Provision of the Code of Ethics'
56: 'Change in Shell Company Status'
57: 'Submission of Matters to a Vote of Security Holders'
58: 'Shareholder Nominations Pursuant to Exchange Act Rule 14a-11'
61: 'ABS Informational and Computational Material'
62: 'Change of Servicer or Trustee'
63: 'Change in Credit Enhancement or Other External Support'
64: 'Failure to Make a Required Distribution'
65: 'Securities Act Updating Disclosure'
71: 'Regulation FD Disclosure'
81: 'Other Events'
91: 'Financial Statements and Exhibits'

Update schedule

Data is updated daily by 5 AM New York time.

Sharadar S&P 500

This dataset provides historical and current additions to and removals from the S&P 500 index.

Collect Sharadar S&P 500

To collect Sharadar S&P 500 changes, specify the country as US (or use FREE for sample data):

$ quantrocket fundamental collect-sharadar-sp500 --country 'US'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_sharadar_sp500
>>> collect_sharadar_sp500(country="US")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/sharadar/sp500?country=US'
{"status": "the fundamental data will be collected asynchronously"}

Monitor flightlog for completion:

quantrocket.fundamental: INFO Collecting Sharadar US S&P 500 index constituents
quantrocket.fundamental: INFO Collecting updated Sharadar US securities listings
quantrocket.fundamental: INFO Finished collecting Sharadar US S&P 500 index constituents

Query Sharadar S&P 500

The data can be queried by sid, universe, or date range and shows index additions and removals:

$ quantrocket fundamental sharadar-sp500 --start-date '2019-10-01' -o sp500_changes.csv
$ csvlook sp500_changes.csv --max-rows 5
| Sid            |       DATE | ACTION  | TICKER | NAME                 | ... |
| -------------- | ---------- | ------- | ------ | -------------------- | --- |
| FIBBG000BHCYJ1 | 2019-10-03 | removed | NKTR   | Nektar Therapeutics  | ... |
| FIBBG000JWD753 | 2019-10-03 | added   | LVS    | Las Vegas Sands Corp | ... |
| FIBBG000BFC8J2 | 2019-11-21 | removed | CELG   | Celgene Corp         | ... |
| FIBBG000M1R011 | 2019-11-21 | added   | NOW    | ServiceNow Inc       | ... |
| FIBBG000DHSPT0 | 2019-12-05 | removed | VIAB   | Viacom Inc           | ... |

>>> from quantrocket.fundamental import download_sharadar_sp500
>>> download_sharadar_sp500(filepath_or_buffer="sp500_changes.csv", start_date="2019-10-01")
>>> sp500_changes = pd.read_csv("sp500_changes.csv", parse_dates=["DATE"])
>>> sp500_changes.head()
              Sid       DATE   ACTION TICKER                  NAME ...
0  FIBBG000BHCYJ1 2019-10-03  removed   NKTR   Nektar Therapeutics
1  FIBBG000JWD753 2019-10-03    added    LVS  Las Vegas Sands Corp
2  FIBBG000BFC8J2 2019-11-21  removed   CELG          Celgene Corp
3  FIBBG000M1R011 2019-11-21    added    NOW        ServiceNow Inc
4  FIBBG000DHSPT0 2019-12-05  removed   VIAB            Viacom Inc

$ curl -X GET 'http://houston/fundamental/sharadar/sp500.csv?start_date=2019-10-01' > sp500_changes.csv
$ csvlook sp500_changes.csv --max-rows 5
| Sid            |       DATE | ACTION  | TICKER | NAME                 | ... |
| -------------- | ---------- | ------- | ------ | -------------------- | --- |
| FIBBG000BHCYJ1 | 2019-10-03 | removed | NKTR   | Nektar Therapeutics  | ... |
| FIBBG000JWD753 | 2019-10-03 | added   | LVS    | Las Vegas Sands Corp | ... |
| FIBBG000BFC8J2 | 2019-11-21 | removed | CELG   | Celgene Corp         | ... |
| FIBBG000M1R011 | 2019-11-21 | added   | NOW    | ServiceNow Inc       | ... |
| FIBBG000DHSPT0 | 2019-12-05 | removed | VIAB   | Viacom Inc           | ... |

In Python, you can use a DataFrame of prices (or any DataFrame with a DatetimeIndex and sids as columns) to get Sharadar S&P 500 constituents data that is aligned to the price data.

>>> from quantrocket import get_prices
>>> prices = get_prices("usstock-1d", start_date="2018-04-16", fields="Close")
>>> closes = prices.loc["Close"] # for intraday databases also isolate a time with .xs
>>> from quantrocket.fundamental import get_sharadar_sp500_reindexed_like
>>> are_in_sp500 = get_sharadar_sp500_reindexed_like(closes)

The function returns a Boolean DataFrame indicating whether the security was in the S&P 500 as of each date. The columns and index match the input DataFrame.

>>> are_in_sp500.head()
Sid         FIBBG000D6L294  FIBBG000MM2P62  FIBBG000PX3XC0  FIBBG009S3NB30
Date
2020-02-28            True            True           False            True
2020-03-02            True            True           False            True
2020-03-03           False            True           False            True
2020-03-04           False            True           False            True
2020-03-05           False            True           False            True

Sharadar S&P 500 data guide

A sample record from the dataset including field descriptions is shown below:

Sid: "FIBBG000D6L294" # Security ID
DATE: "2020-03-03" # The action date.
ACTION: "removed" # available actions are: "added" and "removed".
TICKER: "XEC"
NAME: "Cimarex Energy Co" # Issuer Name - The name of the issuer.
CONTRATICKER: "IR" # Contra Ticker Symbol - The contra ticker is the opposing ticker entry. It represents the ticker that has been removed where the action="added", and the ticker that has been added where the action="removed".
CONTRANAME: "Ingersoll Rand Inc" # Contra Issuer Name - The name of the contra issuer.
NOTE: null

Update schedule

Data is updated daily by 5 AM New York time.

WSH earnings dates

The Wall Street Horizon earnings calendar, available by subscription through Interactive Brokers, provides forward-looking earnings announcement dates. (By contrast, the Reuters estimates and actuals dataset provides historical earnings announcement dates but does not provide forward-looking announcement dates.)

Collect earnings announcement dates

To use Wall Street Horizon earnings announcement dates in QuantRocket, first collect the data from IBKR into your QuantRocket database. Specify one or more sids or universes to collect data for:

$ quantrocket fundamental collect-wsh --universes 'us-stk'
status: the fundamental data will be collected asynchronously

>>> from quantrocket.fundamental import collect_wsh_earnings_dates
>>> collect_wsh_earnings_dates(universes="us-stk")
{'status': 'the fundamental data will be collected asynchronously'}

$ curl -X POST 'http://houston/fundamental/wsh/calendar?universes=us-stk'
{"status": "the fundamental data will be collected asynchronously"}

Multiple requests will be queued and processed sequentially. Monitor flightlog to track progress:

$ quantrocket flightlog stream
quantrocket.fundamental: Collecting Wall Street Horizon earnings dates from IBKR for universes us-stk
quantrocket.fundamental: INFO Saved 2671 total records for 2556 total securities to quantrocket.v2.fundamental.wsh.calendar.sqlite for universes us-stk (data unavailable for 1742 securities)

Wall Street Horizon returns the upcoming announcement for each security, including the date, status (confirmed or unconfirmed), and the time of day if available. Over successive data collection runs the details of a particular announcement may change as Wall Street Horizon gains new information. For example, an "unconfirmed" status may change to "confirmed." When this happens, QuantRocket will preserve both the old record and the updated record, allowing you to establish a point-in-time database of announcement forecasts.

Query earnings announcement dates

You can download the earnings announcement dates to CSV:

$ quantrocket fundamental wsh -u 'us-stk' -o announcement_dates.csv
$ csvlook -I announcement_dates.csv
| Sid            | Date       | Time         | Status      | Period | LastUpdated         |
| -------------- | ---------- | ------------ | ----------- | ------ | ------------------- |
| FIBBG000B9XRY4 | 2019-04-30 | After Market | Confirmed   | 2019-2 | 2019-04-05T01:20:11 |
| FIBBG000B9XRY4 | 2020-04-30 | After Market | Confirmed   | 2020-2 | 2020-04-07T18:54:45 |
| FIBBG000BVPV84 | 2019-04-25 | After Market | Unconfirmed | 2019-1 | 2019-04-05T02:18:42 |
| FIBBG000BVPV84 | 2020-04-23 | After Market | Unconfirmed | 2020-1 | 2020-01-31T00:50:08 |

>>> from quantrocket.fundamental import download_wsh_earnings_dates
>>> import pandas as pd
>>> download_wsh_earnings_dates("announcement_dates.csv", universes="us-stk")
>>> announcement_dates = pd.read_csv("announcement_dates.csv", parse_dates=["Date", "LastUpdated"])
>>> announcement_dates.head()
              Sid       Date          Time       Status  Period         LastUpdated
0  FIBBG000B9XRY4 2019-04-30  After Market    Confirmed  2019-2 2019-04-05 01:20:11
1  FIBBG000B9XRY4 2020-04-30  After Market    Confirmed  2020-2 2020-04-07 18:54:45
2  FIBBG000BVPV84 2019-04-25  After Market  Unconfirmed  2019-1 2019-04-05 02:18:42
3  FIBBG000BVPV84 2020-04-23  After Market  Unconfirmed  2020-1 2020-01-31 00:50:08
4  FIBBG000CL9VN6 2019-04-16  After Market    Confirmed  2019-1 2019-04-05 01:25:11

$ curl -X GET 'http://houston/fundamental/wsh/calendar.csv?universes=us-stk' --output announcement_dates.csv
$ head announcement_dates.csv
Sid,Date,Time,Status,Period,LastUpdated
FIBBG000B9XRY4,2019-04-30,"After Market",Confirmed,2019-2,2019-04-05T01:20:11
FIBBG000B9XRY4,2020-04-30,"After Market",Confirmed,2020-2,2020-04-07T18:54:45
FIBBG000BVPV84,2019-04-25,"After Market",Unconfirmed,2019-1,2019-04-05T02:18:42
FIBBG000BVPV84,2020-04-23,"After Market",Unconfirmed,2020-1,2020-01-31T00:50:08
FIBBG000CL9VN6,2019-04-16,"After Market",Confirmed,2019-1,2019-04-05T01:25:11

$ quantrocket fundamental wsh -i 'FIBBG234647242' | csvlook
|            Sid |       Date | Time          | Status      | Period |         LastUpdated |
| -------------- | ---------- | ------------- | ----------- | ------ | ------------------- |
| FIBBG234647242 | 2019-04-25 | Unspecified   | Unconfirmed | 2019-1 | 2018-03-22 13:07:13 |
| FIBBG234647242 | 2019-04-30 | Before Market | Confirmed   | 2019-1 | 2019-04-04 02:17:27 |

>>> download_wsh_earnings_dates("announcement_dates.csv", sids="FIBBG234647242")
>>> announcement_dates = pd.read_csv("announcement_dates.csv", parse_dates=["Date", "LastUpdated"])
>>> announcement_dates.head()
              Sid       Date           Time       Status  Period         LastUpdated
0  FIBBG234647242 2019-04-25    Unspecified  Unconfirmed  2019-1 2018-03-22 13:07:13
1  FIBBG234647242 2019-04-30  Before Market    Confirmed  2019-1 2019-04-04 02:17:27

$ curl -X GET 'http://houston/fundamental/wsh/calendar.csv?sids=FIBBG234647242'
Sid,Date,Time,Status,Period,LastUpdated
FIBBG234647242,2019-04-25,Unspecified,Unconfirmed,2019-1,2018-03-22T13:07:13
FIBBG234647242,2019-04-30,"Before Market",Confirmed,2019-1,2019-04-04T02:17:27

If you only want the latest record for any given fiscal period, you should dedupe on Sid and Period, keeping only the latest record as indicated by the LastUpdated field:

>>> # sort by LastUpdated and dedupe, keeping the latest record per sid + period
>>> announcement_dates = announcement_dates.sort_values("LastUpdated").drop_duplicates(subset=["Sid", "Period"], keep="last")

You can use a DataFrame of historical prices to get earnings announcement dates that are aligned to the price data.

>>> from quantrocket import get_prices
>>> from quantrocket.fundamental import get_wsh_earnings_dates_reindexed_like
>>> prices = get_prices("tech-giants-1d", start_date="2019-01-01", fields="Close")
>>> closes = prices.loc["Close"]
>>> announcements = get_wsh_earnings_dates_reindexed_like(closes)

By default, only the Time field is returned:

>>> announcements.tail()
Sid                FIBBG265598 FIBBG3691937 FIBBG15124833 FIBBG208813719
Field Date
Time  2019-04-26           NaN          NaN           NaN            NaN
      2019-04-27           NaN          NaN           NaN            NaN
      2019-04-28           NaN          NaN           NaN            NaN
      2019-04-29           NaN          NaN           NaN   After Market
      2019-04-30 Before Market          NaN           NaN            NaN

The resulting DataFrame is sparse, not forward-filled, nor are the announcement dates shifted forward. By default the results are limited to confirmed announcements.

You can get a boolean DataFrame indicating announcements that occurred since the prior close by combining announcements that occurred before today's open or after yesterday's close:

>>> announce_times = announcements.loc["Time"]
>>> announced_since_prior_close = (announce_times == "Before Market") | (announce_times.shift() == "After Market")

Suppose you are live trading an end-of-day Moonshot strategy and want to get a boolean DataFrame indicating announcements that will occur before the next session's open. First, you must extend the index of the prices DataFrame to include the next session. This can be done with quantrocket_trading_calendars:

>>> from quantrocket_trading_calendars import get_calendar
>>> nyse_cal = get_calendar("NYSE")
>>> latest_session = closes.index.max()
>>> # wind latest session to end of day and use calendar to get next session
>>> next_session = nyse_cal.next_open(latest_session.replace(hour=23, minute=59)).normalize().tz_localize(None)
>>> closes = closes.reindex(closes.index.append(pd.DatetimeIndex([next_session])))
>>> closes.index.name = "Date" # reindex loses name attribute, restore it

Then get the announcements, shifting pre-market announcements backward:

>>> announcements = get_wsh_earnings_dates_reindexed_like(closes)
>>> announce_times = announcements.loc["Time"]
>>> announces_before_next_open = (announce_times == "After Market") | (announce_times.shift(-1) == "Before Market")

Finally, if needed, restore the DataFrame indexes to their original shape:

>>> closes = closes.drop(next_session)
>>> announces_before_next_open = announces_before_next_open.drop(next_session)

Fundamentals query cache

The fundamental service utilizes a file cache to improve query performance. When you query any of the fundamentals endpoints, the data is loaded from the database and the resulting file is cached by the fundamental service. Later, if you query again using exactly the same query parameters, the cached file will be returned without hitting the database, resulting in a faster response. Whenever you collect fundamental data, the cached files are invalidated, forcing the subsequent query to hit the database in order to see the refreshed data.

Clear the cache

File caching usually requires no special action or awareness by the user, but there are a few edge cases where you might need to clear the cache manually:

• if you query fundamentals by universe, then change the constituents of the universe, then query again with the same parameters, the fundamental service won't know the universe constituents changed and will return the cached file that was generated using the original universe constituents
• if you query fundamentals, then overwrite the database by pulling another version of the database from S3, then query again with the same parameters, the fundamental service will return the cached file that was generated using the original database

If a fundamentals query is not returning expected results and you suspect caching is to blame, you can either vary the query parameters slightly (for example change the date range) to bypass the cache, or re-create the fundamental container (not just restart it) to clear all cached files.

Real-time Data

QuantRocket provides a powerful feature set for collecting, querying, and streaming real-time market data. Highlights include:

• tick or aggregate: collect tick data and optionally aggregate it into bar data of any size
• pull or push: pull tick or aggregate data into your code by querying, or push the stream of tick data to your code over WebSockets
• stream or snapshot: collect a continuous stream of market data or a single snapshot of data (supported vendors only)
• live market recording: store the data in a database for later replay

Tick data collection overview

Create tick database

To get started with real-time data, first create an empty database for collecting tick data. Assign a code for the database, specify one or more universes or sids, and the fields to collect.

$ quantrocket realtime create-ibkr-tick-db 'fang-stk-tick' --universes 'fang-stk' --fields 'LastPrice' 'Volume' 'BidPrice' 'AskPrice' 'BidSize' 'AskSize'
status: successfully created tick database fang-stk-tick

>>> from quantrocket.realtime import create_ibkr_tick_db
>>> create_ibkr_tick_db("fang-stk-tick", universes="fang-stk",
                        fields=["LastPrice", "Volume", "BidPrice",
                                "AskPrice", "BidSize", "AskSize"])
{'status': 'successfully created tick database fang-stk-tick'}

$ curl -X PUT 'http://houston/realtime/databases/fang-stk-tick?universes=fang-stk&fields=LastPrice&fields=Volume&fields=BidPrice&fields=AskPrice&fields=BidSize&fields=AskSize&vendor=ibkr'
{"status": "successfully created tick database fang-stk-tick"}

$ quantrocket realtime config 'fang-stk-tick'
fields:
- LastPrice
- Volume
- BidPrice
- AskPrice
- BidSize
- AskSize
universes:
- fang-stk
vendor: ibkr

>>> from quantrocket.realtime import get_db_config
>>> get_db_config("fang-stk-tick")
{'universes': ['fang-stk'],
 'vendor': 'ibkr',
 'fields': ['LastPrice',
  'Volume',
  'BidPrice',
  'AskPrice',
  'BidSize',
  'AskSize']}

$ curl -X GET 'http://houston/realtime/databases/fang-stk-tick'
{"universes": ["fang-stk"], "vendor": "ibkr", "fields": ["LastPrice", "Volume", "BidPrice", "AskPrice", "BidSize", "AskSize"]}

$ quantrocket realtime list
etf-tick: []
fang-stk-tick: []

>>> from quantrocket.realtime import list_databases
>>> list_databases()
{'etf-tick': [], 'fang-stk-tick': []}

$ curl -X GET 'http://houston/realtime/databases'
{"etf-tick": [], "fang-stk-tick": []}

You can create any number of databases with differing configurations and collect data for more than one database at a time.

Collect data

Next you are ready to begin collecting market data:

$ quantrocket realtime collect 'fang-stk-tick'
status: the market data will be collected until canceled

>>> from quantrocket.realtime import collect_market_data
>>> collect_market_data("fang-stk-tick")
{'status': 'the market data will be collected until canceled'}

$ curl -X POST 'http://houston/realtime/collections?codes=fang-stk-tick'
{"status": "the market data will be collected until canceled"}

$ quantrocket realtime collect 'us-stk-tick' --sids 'FIBBG000B9XRY4' 'FIBBG000BDTBL9'
status: the market data will be collected until canceled

>>> collect_market_data("us-stk-tick", sids=["FIBBG000B9XRY4", "FIBBG000BDTBL9"])
{'status': 'the market data will be collected until canceled'}

$ curl -X POST 'http://houston/realtime/collections?codes=us-stk-tick&sids=FIBBG000B9XRY4&sids=FIBBG000BDTBL9'
{"status": "the market data will be collected until canceled"}

Monitor data collection

There are numerous ways to monitor the flow of data as it's being collected.

You can view a simple summary of active collections, which will display the number of securities by database code (you can use --detail/detail=True if you want to see actual sids by database code instead of summary counts):

$ quantrocket realtime active
ibkr:
  fang-stk-tick: 5
polygon:
  etf-tick: 10

>>> from quantrocket.realtime import get_active_collections
>>> get_active_collections()
{'ibkr': {'fang-stk-tick': 5},
'polygon': {'etf-tick': 10}}

$ curl -X GET 'http://houston/realtime/collections'
{"ibkr": {"fang-stk-tick": 5}, "polygon": {"etf-tick": 10}}

You can monitor the detailed flightlog stream, which will print a summary approximately every minute of the total ticks and tickers recently received:

$ quantrocket flightlog stream -d
...
┌──────────────────────────────────────────────────┐
│ IBKR market data received:                       │
│                                 ibg1             │
│                       unique_tickers total_ticks │
│ received at 20:04 UTC             11        2759 │
│ received at 20:05 UTC             11        2716 │
│ received at 20:06 UTC             11        2624 │
│ received at 20:07 UTC             11        2606 │
│ received at 20:08 UTC             11        2602 │
│ received at 20:09 UTC             11        2613 │
│ received at 20:10 UTC             11        2800 │
│ received at 20:11 UTC             11        2518 │
│ received at 20:12 UTC             11        2444 │
│ active collections                11             │
└──────────────────────────────────────────────────┘
...

You can connect directly to the data over a WebSocket to see the full, unfiltered stream, or you can query the database to see what's recently arrived.

Cancel data collection

You can cancel data collection by database code (optionally limiting by universe or sid), which returns the remaining active collections after cancellation, if any:

$ quantrocket realtime cancel 'fang-stk-tick'
polygon:
  etf-tick: 10

>>> from quantrocket.realtime import cancel_market_data
>>> cancel_market_data("fang-stk-tick")
{'polygon': {'etf-tick': 10}}

$ curl -X DELETE 'http://houston/realtime/collections?codes=fang-stk-tick'
{"polygon": {"etf-tick": 10}}

$ quantrocket realtime cancel --all

>>> cancel_market_data(cancel_all=True)
{}

$ curl -X DELETE 'http://houston/realtime/collections?cancel_all=True'
{}

$ quantrocket realtime collect 'fang-stk-tick' --until '16:01:00 America/New_York'
status: the market data will be collected until 16:01:00 America/New_York

>>> from quantrocket.realtime import collect_market_data
>>> collect_market_data("fang-stk-tick", until="16:01:00 America/New_York")
{'status': 'the market data will be collected until 16:01:00 America/New_York'}

$ curl -X POST 'http://houston/realtime/collections?codes=fang-stk-tick&until=16:01:00+America/New_York'
{"status": "the market data will be collected until 16:01:00 America/New_York"}

$ quantrocket realtime collect 'fang-stk-tick' --until '30m'
status: the market data will be collected until 30m

>>> collect_market_data("fang-stk-tick", until="30m")
{'status': 'the market data will be collected until 30m'}

$ curl -X POST 'http://houston/realtime/collections?codes=fang-stk-tick&until=30m'
{"status": "the market data will be collected until 30m"}

Delete database

You can delete a database:

$ quantrocket realtime drop-db 'fang-stk-tick' --confirm-by-typing-db-code-again 'fang-stk-tick'
status: deleted tick database fang-stk-tick

>>> from quantrocket.realtime import drop_db
>>> drop_db("fang-stk-tick", confirm_by_typing_db_code_again="fang-stk-tick")
{"status": "deleted tick database fang-stk-tick"}

$ curl -X DELETE 'http://houston/realtime/databases/fang-stk-tick?confirm_by_typing_db_code_again=fang-stk-tick'
{"status": "deleted tick database fang-stk-tick"}

Interactive Brokers

To collect real-time market data from Interactive Brokers, you must first collect securities master listings from Interactive Brokers. It is not sufficient to have collected the listings from another vendor; specific IBKR fields must be present in the securities master database. To check if you have collected IBKR listings, query the securities master and make sure the ibkr_ConId field is populated:

$ quantrocket master get --symbols 'AAPL' --fields 'Symbol' 'ibkr_ConId' | csvlook -I
| Sid            | Symbol | ibkr_ConId |
| -------------- | ------ | ---------- |
| FIBBG000B9XRY4 | AAPL   | 265598     |

>>> from quantrocket.master import download_master_file
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_master_file(f, symbols="AAPL",
                        fields=["Symbol", "ibkr_ConId"])
>>> securities = pd.read_csv(f)
>>> securities.head()
              Sid Symbol  ibkr_ConId
0  FIBBG000B9XRY4   AAPL      265598

$ curl -X GET 'http://houston/master/securities.csv?symbols=AAPL&fields=Symbol&fields=ibkr_ConId' | csvlook -I
| Sid            | Symbol | ibkr_ConId |
| -------------- | ------ | ---------- |
| FIBBG000B9XRY4 | AAPL   | 265598     |

$ quantrocket realtime create-ibkr-tick-db 'fang-stk-tick' --universes 'fang-stk' --fields 'LastPrice' 'Volume' 'BidPrice' 'AskPrice' 'BidSize' 'AskSize'
status: successfully created tick database fang-stk-tick

>>> from quantrocket.realtime import create_ibkr_tick_db
>>> create_ibkr_tick_db("fang-stk-tick", universes="fang-stk",
                        fields=["LastPrice", "Volume", "BidPrice",
                                "AskPrice", "BidSize", "AskSize"])
{'status': 'successfully created tick database fang-stk-tick'}

$ curl -X PUT 'http://houston/realtime/databases/fang-stk-tick?universes=fang-stk&fields=LastPrice&fields=Volume&fields=BidPrice&fields=AskPrice&fields=BidSize&fields=AskSize&vendor=ibkr'
{"status": "successfully created tick database fang-stk-tick"}

$ quantrocket ibg start --wait
ibg1:
  status: running
$ quantrocket realtime collect 'fang-stk-tick'
status: the market data will be collected until canceled

>>> from quantrocket.ibg import start_gateways
>>> start_gateways(wait=True)
{'ibg1': {'status': 'running'}}
>>> from quantrocket.realtime import collect_market_data
>>> collect_market_data("fang-stk-tick")
{'status': 'the market data will be collected until canceled'}

$ curl -X POST 'http://houston/ibgrouter/gateways?wait=True'
{"ibg1": {"status": "running"}}
$ curl -X POST 'http://houston/realtime/collections?codes=fang-stk-tick'
{"status": "the market data will be collected until canceled"}

Concurrent ticker limits

Interactive Brokers limits the number of securities you can stream simultaneously. By default, the limit is 100 concurrent tickers per IB Gateway. The limit can be increased in several ways:

• run multiple IB Gateways. QuantRocket will split requests between the IB Gateways, thereby increasing your ticker limit.
• purchase quote booster packs through IBKR Client Portal. Each purchased booster pack enables an additional 100 concurrent market data lines.
• accounts which are of significant size or which generate significant monthly commissions are allotted more generous ticker limits. See the "Market Data Display" section of the IBKR website to learn more about how concurrent ticker limits are calculated.

When you exceed your ticker limits, the IBKR API returns a "max tickers exceeded" error message for each security above the limit. QuantRocket automatically detects this error message and, if multiple IB Gateways are running, attempts to re-submit the rejected request to a different IB Gateway with additional capacity. Thus, you can run multiple IB Gateways with differing ticker limits and QuantRocket will split up the requests appropriately. If the ticker capacity is maxed out on all connected gateways, you will see warnings in flightlog:

quantrocket.realtime: WARNING All connected gateways have maxed out their concurrent market data collections, skipping SQM STK (sid FI12374), please cancel existing collections or increase your market data lines then re-collect this security (max tickers: ibg1:100)

Streaming vs snapshot data

By default, streaming market data is collected. An alternative option is to collect a single snapshot of data. To do so, use the snapshot parameter. The optional wait parameter will cause the command to block until the data collection is complete: