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'
account:
  account_limit: 100000 USD
exchanges: ASX
licensekey: XXXXXXXXXXXXXXXX
plan:
  currency: USD
  name: Bottle Rocket

>>> from quantrocket.license import set_license
>>> set_license("XXXXXXXXXXXXXXXX")
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'account': {'account_limit': '100000 USD'},
 'exchanges': 'ASX',
 'plan': {'name': 'Bottle Rocket', 'currency': 'USD'}}

$ curl -X PUT 'http://houston/license-service/license/XXXXXXXXXXXXXXXX'
{"licensekey": "XXXXXXXXXXXXXXXX", "account": {"account_limit": "100000 USD"}, "exchanges": "ASX", "plan": {"name": "Bottle Rocket", "currency": "USD"}}

View your license

You can view the details of the currently installed license:

$ quantrocket license get
account:
  account_limit: 100000 USD
exchanges: ASX
licensekey: XXXXXXXXXXXXXXXX
plan:
  currency: USD
  name: Bottle Rocket

>>> from quantrocket.license import get_license_profile
>>> get_license_profile()
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'account': {'account_limit': '100000 USD'},
 'exchanges': 'ASX',
 'plan': {'name': 'Bottle Rocket', 'currency': 'USD'}}

$ curl -X GET 'http://houston/license-service/license'
{"licensekey": "XXXXXXXXXXXXXXXX", "account": {"account_limit": "100000 USD"}, "exchanges": "ASX", "plan": {"name": "Bottle Rocket", "currency": "USD"}}

Refresh license profile

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
account:
  account_limit: 100000 USD
exchanges: ASX
licensekey: XXXXXXXXXXXXXXXX
plan:
  currency: USD
  name: Bottle Rocket

>>> from quantrocket.license import get_license_profile
>>> get_license_profile(force_refresh=True)
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'account': {'account_limit': '100000 USD'},
 'exchanges': 'ASX',
 'plan': {'name': 'Bottle Rocket', 'currency': 'USD'}}

$ curl -X GET 'http://houston/license-service/license?force_refresh=true'
{"licensekey": "XXXXXXXXXXXXXXXX", "account": {"account_limit": "100000 USD"}, "exchanges": "ASX", "plan": {"name": "Bottle Rocket", "currency": "USD"}}

Account limit validation

For subscription plans with an account limit, the account limit 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:

• stop IB Gateway if it is running
• switch to your live account
• start IB Gateway again
• Wait approximately 1 minute. The software queries your account balance every minute whenever IB Gateway 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
account:
  account_balance: 73716.57 USD
  account_balance_details:
  - Account: U12345
    Currency: USD
    NetLiquidation: 73716.57
  account_balance_under_limit: true
  account_limit: 100000 USD
exchanges: ASX
licensekey: XXXXXXXXXXXXXXXX
plan:
  currency: USD
  name: Bottle Rocket

>>> from quantrocket.license import get_license_profile
>>> get_license_profile(force_refresh=True)
{'licensekey': 'XXXXXXXXXXXXXXXX',
 'account': {'account_limit': '100000 USD'
  'account_balance': '73716.57 USD',
  'account_balance_under_limit': True,
  'account_balance_details': [{'Account': 'U12345',
    'Currency': 'USD',
    'NetLiquidation': 73716.57}]},
 'exchanges': 'ASX',
 'plan': {'name': 'Bottle Rocket', 'currency': 'USD'}}

$ curl -X GET 'http://houston/license-service/license?force_refresh=true'
{"licensekey": "XXXXXXXXXXXXXXXX", "account": {"account_limit": "100000 USD", "account_balance": "73716.57 USD", "account_balance_under_limit": true, "account_balance_details": [{"Account": "U12345", "Currency": "USD", "NetLiquidation": 73716.57}]}, "exchanges": "ASX", "plan": {"name": "Bottle Rocket", "currency": "USD"}}

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

IB account structure

Multiple logins and data concurrency

The structure of your IB 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 IB servers, including real-time and historical data collection, is routed through IB Gateway, IB's slimmed-down version of Trader Workstation.
• IB 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 IB, 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 IB Client Portal. (This refers to IB market data subscriptions, not QuantRocket exchange permissions.)

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 IB 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.

IB market data permissions

To collect IB data using QuantRocket, you must subscribe to the relevant market data in your IB account. In IB 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

IB paper accounts do not directly subscribe to market data. Rather, to access market data using your IB paper account, subscribe to the data in your live account and share it with your paper account. Log in to IB 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 IB API to collect market data from IB, submit orders, and track positions and account balances. All communication with IB 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 IB username and password.

Connect to IB

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

The steps for connecting to your IB account and starting IB Gateway differ depending on whether your IB 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), IB'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 IB 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 IB'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 IB login (no security card)

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

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

>>> from quantrocket.launchpad import set_credentials
>>> set_credentials("ibg1", username="myuser", trading_mode="paper")
Enter IB 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 IB login (security card required)

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

$ quantrocket launchpad credentials 'ibg1' --username 'myuser' --live
Enter IB 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.launchpad import set_credentials
>>> set_credentials("ibg1", username="myuser", trading_mode="live")
Enter IB 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 IB connection

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

$ quantrocket account balance --latest --fields 'NetLiquidation' | csvlook
| Account   | Currency | NetLiquidation |         LastUpdated |
| --------- | -------- | -------------- | ------------------- |
| DU12345   | USD      |     500,000.00 | 2018-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 2018-02-02 22:57:13

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

Switch between live and paper account

When you sign up for an IB paper account, IB 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 launchpad credentials 'ibg1' --username 'myliveuser' --paper
Enter IB Password:
status: successfully set ibg1 credentials

>>> from quantrocket.launchpad import set_credentials
>>> set_credentials("ibg1", username="myliveuser", trading_mode="paper")
Enter IB 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 launchpad 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 launchpad credentials 'ibg1'
TRADING_MODE: live
TWSUSERID: myliveuser

>>> from quantrocket.launchpad 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 launchpad status
ibg1: stopped

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

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

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

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

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

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

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

$ curl -X DELETE 'http://houston/launchpad/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 launchpad stop --wait && quantrocket launchpad start

Or, perhaps you use one of your IB 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 launchpad start --wait --gateways 'ibg2' && quantrocket history collect "nasdaq-1d" && quantrocket launchpad 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 IB 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 launchpad credentials 'ibg2' --username 'myuser' --paper
Enter IB Password:
status: successfully set ibg2 credentials

>>> from quantrocket.launchpad import set_credentials
>>> set_credentials("ibg2", username="myuser", trading_mode="paper")
Enter IB 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 launchpad start --gateways 'ibg2'
status: the gateways will be started asynchronously

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

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

Market data permission file

To retrieve market data from IB, you must subscribe to the appropriate market data subscriptions in IB 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 IB Client Portal.

QuantRocket looks for a market data permission file called quantrocket.launchpad.permissions.yml in the top-level of the Jupyter file browser (that is, /codeload/quantrocket.launchpad.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: reuters, wsh)
    research:
        - reuters
        - wsh
# if you have multiple IB Gateway services, include a section for each
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:

2018-08-12 09:39:31 quantrocket.launchpad: INFO Successfully loaded /codeload/quantrocket.launchpad.permissions.yml

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

2018-08-12 09:46:46 quantrocket.launchpad: ERROR Could not load /codeload/quantrocket.launchpad.permissions.yml:
2018-08-12 09:46:46 quantrocket.launchpad: 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 launchpad config
ibg1:
  marketdata:
    CASH:
    - IDEALPRO
    FUT:
    - GLOBEX
    - OSE
    STK:
    - NYSE
    - ISLAND
    - TSEJ
  research:
  - reuters
  - wsh
ibg2:
  marketdata:
    STK:
    - NYSE

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

$ curl -X GET 'http://houston/launchpad/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 IB 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

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 1.7.x, you can install the latest 1.7.x client:

$ pip install 'quantrocket-client>=1.7,<1.8'

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. You can deploy QuantRocket to two or in some cases more than two computers or cloud servers, depending on your subscription plan.

The 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.

Universe Selection

QuantRocket supports dozens of global exchanges and tens of thousands of financial instruments across multiple asset classes including equities, futures, forex, and options. You can easily collect all listings or contracts for the exchanges that interest you and flexibly group them into universes that make sense for your trading strategies.

Collect listings

$ quantrocket master 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_exchanges
>>> list_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?regions=asia&sec_types=STK'
{"STK": {"Australia": ["ASX", "CHIXAU"], "Hong Kong": ["SEHK", "SEHKNTL", "SEHKSZSE"], "India": ["NSE"], "Japan": ["CHIXJ", "JPNNEXT", "TSEJ"], "Singapore": ["SGX"]}}

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

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

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

$ quantrocket flightlog stream --hist 5
12:07:40 quantrocket.master: INFO Collecting SEHK STK listings from IB website
12:08:29 quantrocket.master: INFO Requesting details for 2220 SEHK listings found on IB website
12:10:06 quantrocket.master: INFO Saved 2215 SEHK listings to securities master database

The master file

After you collect listings, you can inspect the master file, which provides the symbol, exchange, currency, and many other fields:

$ quantrocket master get --exchanges 'SEHK' -o listings.csv
$ csvlook listings.csv --max-rows 5 --max-columns 10 -I
| ConId   | Symbol | Etf | SecType | PrimaryExchange | Currency | LocalSymbol | TradingClass | MarketName | LongName                     | ... |
| ------- | ------ | --- | ------- | --------------- | -------- | ----------- | ------------ | ---------- | ---------------------------- | --- |
| 1616383 | 11     | 0   | STK     | SEHK            | HKD      | 11          | 11           | 11         | HANG SENG BANK LTD           | ... |
| 1616387 | 44     | 0   | STK     | SEHK            | HKD      | 44          | 44           | 44         | HONG KONG AIRCRAFT ENGINEERG | ... |
| 1616390 | 5      | 0   | STK     | SEHK            | HKD      | 5           | 5            | 5          | HSBC HOLDINGS PLC            | ... |
| 1616393 | 101    | 0   | STK     | SEHK            | HKD      | 101         | 101          | 101        | HANG LUNG PROPERTIES LTD     | ... |
| 1616396 | 2      | 0   | STK     | SEHK            | HKD      | 2           | 2            | 2          | CLP HOLDINGS LTD             | ... |
| ...     | ...    | ... | ...     | ...             | ...      | ...         | ...          | ...        | ...                          | ... |

>>> import pandas as pd
>>> from quantrocket.master import download_master_file
>>> download_master_file("listings.csv", exchanges="SEHK")
>>> securities = pd.read_csv("listings.csv")
>>> securities.head()
     ConId  Symbol  Etf SecType PrimaryExchange Currency  LocalSymbol  TradingClass
0  1616383      11    0     STK            SEHK      HKD           11            11
1  1616387      44    0     STK            SEHK      HKD           44            44
2  1616390       5    0     STK            SEHK      HKD            5             5
3  1616393     101    0     STK            SEHK      HKD          101           101
4  1616396       2    0     STK            SEHK      HKD            2             2

$ curl -X GET 'http://houston/master/securities.csv?exchanges=SEHK' > listings.csv
$ head listings.csv
ConId,Symbol,Etf,SecType,PrimaryExchange,Currency,LocalSymbol,TradingClass,...
1616383,11,0,STK,SEHK,HKD,11,11,...
1616387,44,0,STK,SEHK,HKD,44,44,...
...

Define 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 large 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.

There are different ways to create a universe. You can download a CSV of securities, manually pare it down to the desired securities, and create the universe from the edited list:

$ quantrocket master get --exchanges 'SEHK' --outfile hongkong_securities.csv
$ # edit the CSV, then:
$ quantrocket master universe 'hongkong' --infile hongkong_securities_edited.csv
code: hongkong
inserted: 2216
provided: 2216
total_after_insert: 2216

>>> from quantrocket.master import download_master_file, create_universe
>>> download_master_file("hongkong_securities.csv", exchanges=["SEHK"])
>>> # edit the CSV, then:
>>> create_universe("hongkong", infilepath_or_buffer="hongkong_securities_edited.csv")
{'code': 'hongkong',
 'inserted': 2216,
 'provided': 2216,
 'total_after_insert': 2216}

$ curl -X GET 'http://houston/master/securities.csv?exchanges=SEHK' > hongkong_securities.csv
$ # edit the CSV, then:
$ curl -X PUT 'http://houston/master/universes/hongkong' --upload-file hongkong_securities_edited.csv
{"code": "hongkong", "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:

$ quantrocket master get --exchanges 'SEHK' --sectors 'Financial' | quantrocket master universe 'hongkong-fin' --infile -
code: hongkong-fin
inserted: 416
provided: 416
total_after_insert: 416

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}

Filter by securities master fields with csvgrep

You can filter securities master queries by a variety of fields including Symbol, Exchange, Currency, Sector, and more. (Run quantrocket master get -h to see filtering options.) However, sometimes you may want to filter by a field that is not exposed by the API. From a terminal, you can use csvgrep for this purpose. For example, NASDAQ stocks are divided into the NMS (National Market System) and SCM (SmallCap Market) listing tiers, which are stored in the TradingClass field. To create a separate universe for each listing tier:

$ quantrocket master get -e 'NASDAQ' | csvgrep --columns 'TradingClass' --match 'NMS' | quantrocket master universe 'nasdaq-nms' -f -
code: nasdaq-nms
inserted: 2252
provided: 2252
total_after_insert: 2252
$ quantrocket master get -e 'NASDAQ' | csvgrep --columns 'TradingClass' --match 'SCM' | quantrocket master universe 'nasdaq-scm' -f -
code: nasdaq-scm
inserted: 778
provided: 778
total_after_insert: 778

Or save a CSV of OTC stocks, excluding the "NOINFO" trading class:

$ quantrocket master get -e 'PINK' | csvgrep --columns 'TradingClass' --match 'NOINFO' --invert-match > pink_with_info.csv

Define universes by fundamental data availability

If you want to limit a universe to stocks with fundamental data, the best approach is to create a universe comprising the entire pool of relevant securities, collect the needed data for this universe, then create the sub-universe.

Suppose we've collected all NYSE stock listings and want to create a universe of all NYSE stocks with Reuters estimates available. First, define a universe of all NYSE stocks and collect estimates:

$ quantrocket master get -e 'NYSE' -t 'STK' | quantrocket master universe 'nyse-stk' -f -
code: nyse-stk
inserted: 3109
provided: 3109
total_after_insert: 3109
$ quantrocket fundamental collect-estimates --universes 'nyse-stk'
status: the fundamental data will be collected asynchronously

Wait for the fundamental data to be collected (monitor flightlog for status). Then, since a universe can be created from any file with a ConId column, simply download a file of estimates for the desired codes and re-upload the file to create the universe:

$ quantrocket fundamental estimates 'BVPS' 'EPS' 'NAV' 'ROE' 'ROA' -u 'nyse-stk' | quantrocket master universe 'nyse-stk-with-estimates' -f -
code: nyse-stk-with-estimates
inserted: 1957
provided: 1957
total_after_insert: 1957

Define universes by dollar volume

Alternatively, suppose we want to create 3 universes - smallcaps, midcaps, and largecaps - based on the 90-day average dollar volume of NYSE stocks. First, create a history database and collect historical data for all NYSE stocks (see the Historical Data section for more detail).

$ quantrocket history create-db 'nyse-eod' --bar-size '1 day' --universes 'nyse-stk'
status: successfully created quantrocket.history.nyse-eod.sqlite
$ quantrocket history collect 'nyse-eod'
status: the historical data will be collected asynchronously

>>> from quantrocket import get_prices
>>> from quantrocket.master import create_universe
>>> import io
>>> prices = get_prices("nyse-eod", fields=["Close", "Volume"])

Next we calculate daily dollar volume and take a 90-day average:

>>> closes = prices.loc["Close"]
>>> volumes = prices.loc["Volume"]
>>> dollar_volumes = closes * volumes
>>> avg_dollar_volumes = dollar_volumes.rolling(window=90).mean()
>>> # we'll make our universes based on the latest day's averages
>>> avg_dollar_volumes = avg_dollar_volumes.iloc[-1]
>>> avg_dollar_volumes.describe()
Out[60]:
count    2.255000e+03
mean     3.609773e+07
std      9.085866e+07
min      3.270559e+04
25%      1.058080e+06
50%      6.229675e+06
75%      3.399090e+07
max      1.719344e+09
Name: 2017-08-15 00:00:00, dtype: float64

Let's make universes of $1-5M, $5-25M, and $25M+:

>>> sml = avg_dollar_volumes[(avg_dollar_volumes >= 1000000) & (avg_dollar_volumes < 5000000)]
>>> mid = avg_dollar_volumes[(avg_dollar_volumes >= 5000000) & (avg_dollar_volumes < 25000000)]
>>> lrg = avg_dollar_volumes[avg_dollar_volumes >= 25000000]

The DataFrame indexes contain the conids which are needed to make the universes, so we write the DataFrames to in-memory CSVs and pass the CSVs to the master service:

>>> f = io.StringIO()
>>> sml.to_csv(f, header=True)
>>> create_universe("nyse-sml", infilepath_or_buffer=f)
{'code': 'nyse-sml',
 'inserted': 509,
 'provided': 509,
 'total_after_insert': 509}
>>> f = io.StringIO()
>>> mid.to_csv(f, header=True)
>>> create_universe("nyse-mid", infilepath_or_buffer=f)
 {'code': 'nyse-mid',
  'inserted': 530,
  'provided': 530,
  'total_after_insert': 530}
>>> f = io.StringIO()
>>> lrg.to_csv(f, header=True)
>>> create_universe("nyse-lrg", infilepath_or_buffer=f)
{'code': 'nyse-lrg',
'inserted': 665,
'provided': 665,
'total_after_insert': 665}

On a side note, now that you've created different universes for different market caps, a typical workflow might involve creating a history database for each universe. As described more fully in the Historical Data documentation, you can seed your databases for each market cap segment from the historical data you've already collected, saving you the trouble of re-collecting the data from scratch.

$ quantrocket history create-db 'nyse-sml-eod' --bar-size '1 day' --universes 'nyse-sml'
status: successfully created quantrocket.history.nyse-sml-eod.sqlite
$ quantrocket history get 'nyse-eod' --universes 'nyse-sml' | quantrocket history load 'nyse-sml-eod'
db: nyse-sml-eod
loaded: 572081

See the Historical Data documentation for more details on copying data from one history database to another.

Security types

The following security types or asset classes are available:

¹ For collecting options and futures options, see option chains.

With the exception of ETFs, these security type codes are stored in the SecType field of the master file. ETFs are a "pseudo" security type in QuantRocket. See ETF classification.

ETF classification

The IB API does not classify ETFs as a separate security type; they are simply classified as stocks (STK). However, the IB website organizes ETF and stock listings separately, and QuantRocket uses this information to classify ETFs. Stocks and ETFs are indicated as follows in the master file:

ADR classification

ADRs (American Depositary Receipts) are not identified as such by the IB API. The best option for identifying ADRs is to search the LongName field for the text "ADR" and create a universe of the results.

This can be done using the CLI with csvgrep. First, peek at a few results:

$ quantrocket master get -e 'NYSE' --fields 'Symbol' 'LongName' | csvgrep --columns 'LongName' --match ' ADR' | csvlook --max-rows 10
| ConId | Symbol | LongName                     |
| ----- | ------ | ---------------------------- |
| 4,442 | AMX    | AMERICA MOVIL-SPN ADR CL L   |
| 4,656 | AU     | ANGLOGOLD ASHANTI-SPON ADR   |
| 4,815 | BBVA   | BANCO BILBAO VIZCAYA-SP ADR  |
| 4,839 | BCH    | BANCO DE CHILE-ADR           |
| 4,854 | BCS    | BARCLAYS PLC-SPONS ADR       |
| 4,940 | BFR    | BBVA BANCO FRANCES SA-ADR    |
| 4,986 | BHP    | BHP BILLITON LTD-SPON ADR    |
| 5,171 | BP     | BP PLC-SPONS ADR             |
| 5,315 | BT     | BT GROUP PLC-SPON ADR        |
| 6,257 | CX     | CEMEX SAB-SPONS ADR PART CER |
|   ... | ...    | ...                          |

Now create a universe of ADRs from all of the results:

$ quantrocket master get -e 'NYSE' --fields 'Symbol' 'LongName' | csvgrep --columns 'LongName' --match ' ADR' | quantrocket master universe 'nyse-adrs' -f -
code: nyse-adrs
inserted: 228
provided: 228
total_after_insert: 228

This can also be accomplished with the Python API combined with pandas. Searching for "ADR" and creating the universe might look like this:

>>> adrs = securities[securities.LongName.str.contains(" ADR")]
>>> f = io.StringIO()
>>> adrs.to_csv(f)
>>> create_universe("nyse-adrs", f)
{'code': 'nyse-adrs',
 'provided': 228,
 'inserted': 228,
 'total_after_insert': 228}

Moonshot strategies that wish to exclude ADRs could do so using the EXCLUDE_UNIVERSES attribute:

class MyStrategyWithoutADRs(Moonshot):

    EXCLUDE_UNIVERSES = ['nyse-adrs']
    ...

Option chains

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

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

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

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

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

>>> from quantrocket.master import download_master_file, collect_option_chains
>>> import io
>>> f = io.StringIO()
>>> download_master_file(f, exchanges=["NASDAQ"], sec_types=["STK"], symbols=["GOOG", "FB", "AAPL"])
>>> collect_option_chains(infilepath_or_buffer=f)
{'status': 'the 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' --upload-file nasdaq_mega.csv
{"status": "the 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

Maintain listings

Listings change over time and QuantRocket helps you keep your securities master database up-to-date. Your can monitor for changes to your existing listings (such as a company moving its listing from one exchange to another), you can delist securities to exclude them from your backtests and trading (without deleting them), and you can look for new listings.

Listings diffs

$ quantrocket master diff --universes 'nasdaq'
status: the diff, \if any, will be logged to flightlog asynchronously

>>> from quantrocket.master import diff_securities
>>> diff_securities(universes=["nasdaq"])
{'status': 'the diff, if any, will be logged to flightlog asynchronously'}

$ curl -X GET 'http://houston/master/diff?universes=nasdaq'
{"status": "the diff, if any, will be logged to flightlog asynchronously"}

If any listings have changed, they'll be logged to flightlog at the WARNING level with a description of what fields have changed. You may wish to schedule this command on your countdown service and monitor Papertrail:

Delist stocks

When a stock ceases trading, IB removes it from their system. To reflect this status in QuantRocket, you can delist the security, which doesn't delete it but simply marks it as delisted. In the master file, delisted securities will be reflected by the Delisted field having a value of 1.

Delisting a security is a matter of proper record-keeping and also benefits data collection as it instructs QuantRocket not to waste time requesting data from IB for this security.

To delist a single security:

$ quantrocket master delist --conid 194245757
msg: delisted conid 194245757

>>> from quantrocket.master import delist_security
>>> delist_security(conid=194245757)
{'msg': 'delisted conid 194245757'}

$ curl -X DELETE 'http://houston/master/securities?conids=194245757'
{"msg": "delisted conid 194245757"}

$ quantrocket master diff --universes 'nasdaq' --fields 'ConId' --delist-missing --delist-exchanges 'VALUE' 'PINK'
status: the diff, \if any, will be logged to flightlog asynchronously

>>> from quantrocket.master import diff_securities
>>> diff_securities(universes="nasdaq", fields="ConId", delist_missing=True, delist_exchanges=["VALUE", "PINK"])
{'status': 'the diff, if any, will be logged to flightlog asynchronously'}

$ curl -X GET 'http://houston/master/diff?universes=nasdaq&fields=ConId&delist_missing=True&delist_exchanges=VALUE&delist_exchanges=PINK'
{"status": "the diff, if any, will be logged to flightlog asynchronously"}

Delisted securities will still be included by default in the master file, but you can optionally exclude them:

$ quantrocket master get --universes 'nasdaq' --exclude-delisted --outfile nasdaq_active.csv

>>> download_master_file("nasdaq_active.csv", universes="nasdaq", exclude_delisted=True)

$ curl -X GET 'http://houston/master/securities.csv?universes=nasdaq&exclude_delisted=True' > nasdaq_active.csv

Ticker symbol changes

Sometimes when a ticker symbol changes IB will preserve the conid (contract ID); in this case, to incorporate the changes into our database, we can simply collect the listing details for the symbol we care about, which will overwrite the old (stale) listing details:

$ # Look up the symbol's conid and collect the listings for just that conid
$ quantrocket master get --exchanges TSE --symbols OLD --pretty --fields ConId
ConId = 123456
$ quantrocket master collect -i 123456
status: the listing details will be collected asynchronously

However, sometimes IB will issue a new conid. In this case, if you want to continue trading the symbol, you should delist the old symbol, collect the new listing, and append the new symbol to the universe(s) you care about:

$ quantrocket master delist --exchange TSE --symbol OLD
msg: delisted conid 123456
$ quantrocket master collect --exchanges TSE --symbols NEW --sec-types STK
$ # check flightlog and wait for listing download to complete, then:
$ quantrocket master get -e TSE -s NEW -t STK | quantrocket master universe "canada" --append --infile -

The above examples expect you to take action in response to individual ticker changes, but what if your universes consist of thousands of stocks and you don't want to deal with them individually? Use quantrocket master diff --delist-missing to automate the delisting of symbols that go missing, as described in the previous section, and use quantrocket master collect to periodically collect any listings that might belong in your universe(s), as described in the next section. If any symbols go missing due to ticker changes that cause IB to issue a new conid, you'll pick up the new listings the next time you run quantrocket master collect.

Add new listings

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

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

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

$ quantrocket master get --exchanges SEHK --exclude-universes "hongkong" --outfile new_hongkong_securities.csv

>>> from quantrocket.master import download_master_file
>>> download_master_file("new_hongkong_securities.csv", exchanges=["SEHK"], exclude_universes=["hongkong"])

$ curl -X GET 'http://houston/master/securities.csv?exchanges=SEHK&exclude_universes=hongkong' > new_hongkong_securities.csv

$ quantrocket master universe "hongkong" --infile new_hongkong_securities.csv
code: hongkong
inserted: 10
provided: 10
total_after_insert: 2226

>>> from quantrocket.master import create_universe
>>> create_universe("hongkong", infilepath_or_buffer="new_hongkong_securities.csv", append=True)
{'code': 'hongkong',
 'inserted': 10,
 'provided': 10,
 'total_after_insert': 2226}

$ curl -X PATCH 'http://houston/master/universes/hongkong' --upload-file new_hongkong_securities.csv
{"code": "hongkong", "provided": 10, "inserted": 10, "total_after_insert": 2226}

IB Historical Data

QuantRocket makes it easy to retrieve and work with IB's abundant, global historical market data. (Appropriate IB market data subscriptions required.) Simply define your historical data requirements, and QuantRocket will retrieve data from IB according to your requirements and store it in a database for fast, flexible querying. You can create as many databases as you need for your backtesting and trading.

About IB historical data

Split adjustments

All IB historical data is split-adjusted.

Thus, your data will be split-adjusted when you initially retrieve it into your history database. If a split occurs after the initial retrieval, the data that was already stored needs to be adjusted for the split. QuantRocket handles this circumstance by comparing a recent price in the database to the equivalently-timestamped price from IB. 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 IB, in order to make sure the database stays synced with IB.

Dividend adjustments

By default, IB historical data is not dividend-adjusted. However, dividend-adjusted data is available from IB 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-db 'us-stk-1d' --universes 'us-stk' --bar-size '1 day' --bar-type 'ADJUSTED_LAST'
status: successfully created quantrocket.history.us-stk-1d.sqlite

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

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

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

Primary vs consolidated prices

By default, IB returns consolidated prices for equities. (Consolidated prices are the aggregated prices across all exchanges where a security trades.) If you run an end-of-day strategy that enters and exits in the opening or closing auction, using consolidated prices may be less accurate than using prices from the primary exchange only. This issue is especially significant in US markets due to after-hours trading and the large number of exchanges and ECNs. (For more on this topic, see this blog post by Ernie Chan.)

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

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

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

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

Note that volume is also filtered by the primary exchange when using this option. Thus, volume will be lower (possibly significant lower) than the consolidated volume typically reported on financial websites (or if omitting this option).

Bar sizes

IB 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-db to indicate what type of historical data you want:

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

How far back historical data goes

For stocks and currencies, IB 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 IB API is the same as the amount of data available when viewing the corresponding chart in Trader Workstation.

Historical data availability for select exchanges is shown here.

For futures, historical data is available for contracts that expired no more than 2 years ago. IB 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.

Survivorship bias

IB historical data does not include delisted companies. If a stock went bankrupt, was acquired, went private, etc. it won't be in IB's data.

QuantRocket doesn't delete delisted tickers, so over time you will build up a database that includes delisted tickers.

End-of-day data that includes active and delisted tickers for US stocks and is free of survivorship bias is available as a premium dataset from Sharadar.

Data collection

Create historical databases

Create a database by defining, at minimum, the bar size you want and the universe of securities to include. Suppose we've used the master service to define a universe of banking stocks on the Tokyo Stock Exchange, and now we want to collect end-of-day historical data for those stocks. First, create the database:

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

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

$ curl -X PUT 'http://houston/history/databases/japan-bank-eod?universes=japan-bank&bar_size=1 day'
{"status": "successfully created quantrocket.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 IB API to determine how far back historical data is available for each security, then query the IB 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 IB API is responding to requests, QuantRocket will periodically estimate how long it will take to collect the data. You can monitor flightlog via the command line or Papertrail to track progress:

$ quantrocket flightlog stream
2017-08-22 13:24:09 quantrocket.history: INFO [japan-bank-eod] Determining how much history is available from IB for japan-bank-eod
2017-08-22 13:25:45 quantrocket.history: INFO [japan-bank-eod] Collecting history from IB for japan-bank-eod
2017-08-22 13:26:11 quantrocket.history: INFO [japan-bank-eod] Expected remaining runtime to collect japan-bank-eod history based on IB response times so far: 0:23:11
2017-08-22 13:55:00 quantrocket.history: INFO [japan-bank-eod] Saved 468771 total records for 85 total securities to quantrocket.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 API Reference.

As you become interested in new exchanges or want to test new ideas, you can keep adding as many new databases with as many different configurations as you like.

Update historical data

After you create a history database and run the initial data collection, you will often want to keep the data up-to-date over time. To do so, simply collect the data again:

$ 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 bring the database current, appending new data to what you already have. The update process will run much faster than the initial data collection due to collecting fewer records.

If QuantRocket detects that a split or other adjustment has occurred, it will not only collect the new data but replace the existing data for that security.

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

List databases

List your historical databases to see which ones you've created:

$ quantrocket history list
es-fut-1min
japan-bank-eod
uk-etf-15min
usa-stk-1d

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

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

$ quantrocket history config 'japan-bank-eod'
bar_size: 1 day
fields:
- Open
- High
- Low
- Close
- Volume
- Wap
- TradeCount
universes:
- japan-bank
vendor: ib

>>> from quantrocket.history import get_db_config
>>> get_db_config("japan-bank-eod")
{'universes': ['japan-bank'],
 'bar_size': '1 day',
 'vendor': 'ib',
 'fields': ['Open', 'High', 'Low', 'Close', 'Volume', 'Wap', 'TradeCount']}

$ curl -X GET 'http://houston/history/databases/japan-bank-eod'
{"universes": ["japan-bank"], "bar_size": "1 day", "vendor": "ib", "fields": ["Open", "High", "Low", "Close", "Volume", "Wap", "TradeCount"]}

Historical data collection queue

You can queue as many historical data requests as you wish, and they will be processed in sequential order, one at a time:

$ quantrocket history collect 'aus-lrg-eod' 'singapore-15min' 'germany-1hr-bid-ask'
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history(["aus-lrg-eod", "singapore-15min", "germany-1hr-bid-ask"])
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=aus-lrg-eod&codes=singapore-15min&codes=germany-1hr-bid-ask'
{"status": "the historical data will be collected asynchronously"}

$ quantrocket history queue
priority: []
standard:
- aus-lrg-eod
- singapore-15min
- germany-1hr-bid-ask

>>> quantrocket.history import get_history_queue
>>> get_history_queue()
{'priority': [],
 'standard': ['aus-lrg-eod', 'singapore-15min', 'germany-1hr-bid-ask']}

$ curl -X GET 'http://houston/history/queue'
{"priority": [], "standard": ["aus-lrg-eod", "singapore-15min", "germany-1hr-bid-ask"]}

$ quantrocket history cancel 'aus-lrg-eod' 'singapore-15min'
priority: []
standard:
- germany-1hr-bid-ask
$ quantrocket history collect 'aus-lrg-eod' 'singapore-15min'
status: the historical data will be collected asynchronously
$ quantrocket history queue
priority: []
standard:
- germany-1hr-bid-ask
- aus-lrg-eod
- singapore-15min

>>> from quantrocket.history import get_history_queue, cancel_collections, collect_history
>>> cancel_collections(codes=["aus-lrg-eod", "singapore-15min"])
{'priority': [],
 'standard': ['germany-1hr-bid-ask']}
>>> collect_history(["aus-lrg-eod", "singapore-15min"])
{'status': 'the historical data will be collected asynchronously'}
>>> get_history_queue()
{'priority': [],
 'standard': ['germany-1hr-bid-ask', 'aus-lrg-eod', 'singapore-15min']}

$ curl -X DELETE 'http://houston/history/queue?codes=aus-lrg-eod&codes=singapore-15min'
{"priority": [], "standard": ["germany-1hr-bid-ask"]}
$ curl -X POST 'http://houston/history/queue?codes=aus-lrg-eod&codes=singapore-15min'
{"status": "the historical data will be collected asynchronously"}
$ curl -X GET 'http://houston/history/queue'
{"priority": [], "standard": ["germany-1hr-bid-ask", "aus-lrg-eod", "singapore-15min"]}

There's another way to control queue priority: QuantRocket provides a standard queue and a priority queue. 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 US data each weekday at 5:30 pm
30 17 * * mon-fri quantrocket history collect --priority 'nyse-eod'

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

$ quantrocket history collect 'nyse-15min' # many symbols + smaller granularity = slower

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.

Delete historical 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 'japan-bank-eod' --confirm-by-typing-db-code-again 'japan-bank-eod'
status: deleted quantrocket.history.japan-bank-eod.sqlite

>>> from quantrocket.history import drop_db
>>> drop_db("japan-bank-eod", confirm_by_typing_db_code_again="japan-bank-eod")
{'status': 'deleted quantrocket.history.japan-bank-eod.sqlite'}

$ curl -X DELETE 'http://houston/history/databases/japan-bank-eod?confirm_by_typing_db_code_again=japan-bank-eod'
{"status": "deleted quantrocket.history.japan-bank-eod.sqlite"}

Intraday data collection

IB is a treasure trove of global market data, but few IB customers tap its potential due to the complexity of the API and the long runtimes required to collect it due to IB's rate limits. QuantRocket can collect data in the background continuously for days, weeks, or months on end, surviving network interruptions, IB server blackouts, and other challenges and idiosyncrasies of the IB API. With sufficient hard drive space and a little patience, you can collect terabytes and terabytes of market data.

Initial data collection

Depending on the bar size, number of securities, and date range of your historical database, initial data collection from the IB 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 IB 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.

IB 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. See the exchanges table on the account page for the approximate number of listings for each exchange.

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 1000 liquid securities trading on NYSE and NASDAQ. Instead of collecting intraday bars for all NYSE and NASDAQ securities then filtering out illiquid ones, you could try this approach:

• collect a year's worth of daily bars for all NYSE and NASDAQ securities (this requires only 1 request to the IB 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 (see usage guide section on using price data to define universes)
• 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 many small-cap stocks, fundamental data won't 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-db 'usa-liquid-15min' -u 'usa-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 'usa-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.

Don't collect what you don't need

Many of the strategies outlined above can be summarized in one principle: try to keep your databases as small as possible. Small databases are faster to fill initially, take up less disk space, and, most importantly, are faster and easier to work with in research, backtesting, and trading. If you need a large universe of minute bars, by all means collect it, but in light of the runtime and performance costs of working with large amounts of data, it pays to analyze your data requirements in advance and exclude any data you know you won't need.

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 conid for a conid-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 conid and time
$ quantrocket history create-db 'usa-stk-15min' --universes 'usa-stk' --bar-size '15 mins' --shard 'conid,time'
status: successfully created quantrocket.history.usa-stk-15min.sqlite

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

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

The choices are:

• year
• month
• day
• time
• conid
• conid,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 conid shard:

$ # sharded by time
$ quantrocket db list 'history' 'usa-stk-15min' --expand
quantrocket.history.usa-stk-15min.093000.sqlite
quantrocket.history.usa-stk-15min.094500.sqlite
...
$ # sharded by conid
$ quantrocket db list 'history' 'usa-stk-1min' --expand
quantrocket.history.usa-stk-1min.100248135.sqlite
quantrocket.history.usa-stk-1min.100296007.sqlite
quantrocket.history.usa-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("usa-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 conid

Sharding by conid 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 conid 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 conid-sharded database because it only needs to look in 1 shard:

>>> # get AAPL 1-min prices
>>> aapl_prices = get_prices("usa-stk-1min", conids=[265598])

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

Sharding by conid and time

Sharding by conid and time results in duplicate copies of the database, one sharded by time and one by conid. 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 conid-sharded database to satisfy your request:

>>> # this query will look in 3 time shards:
>>> #  - quantrocket.history.usa-stk-15min.094500.sqlite
>>> #  - quantrocket.history.usa-stk-15min.120000.sqlite
>>> #  - quantrocket.history.usa-stk-15min.154500.sqlite
>>> prices = get_prices("usa-stk-15min", times=["09:30:00", "12:00:00", "15:45:00"])
>>> # this query will look in 2 conid shards:
>>> #  - quantrocket.history.usa-stk-15min.265598.sqlite
>>> #  - quantrocket.history.usa-stk-15min.4075.sqlite
>>> prices = get_prices("usa-stk-15min", conids=[265598, 4075])

Sharding by time and by conid 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.

Time filters for intraday databases

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 IB 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:30 and 15:45, inclusive. (Note that bar times correspond to the start of the bar, so the final bar for US stocks using 15-min bars would be 15:45:00.)

$ quantrocket history create-db 'nasdaq-stk-15min' --universes 'nasdaq-stk' --bar-size '15 mins' --between-times '09:30:00' '15:45:00'
status: successfully created quantrocket.history.nasdaq-stk-15min.sqlite

>>> from quantrocket.history import create_db
>>> create_db("nasdaq-stk-15min", universes=["nasdaq-stk"], bar_size="15 mins", between_times=["09:30:00", "15:45:00"])
{'status': 'successfully created quantrocket.history.nasdaq-stk-15min.sqlite'}

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

$ quantrocket history config "nasdaq-stk-15min"
bar_size: 15 mins
times:
- 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'
- '15:00:00'
- '15:15:00'
- '15:30:00'
- '15:45:00'
universes:
- nasdaq-stk
vendor: ib

>>> from quantrocket.history import get_db_config
>>> get_db_config("nasdaq-stk-15min")
 {'bar_size': '15 mins',
  'times': ['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',
   '15:00:00',
   '15:15:00',
   '15:30:00',
   '15:45:00'],
  'universes': ['nasdaq-stk'],
  'vendor': 'ib'}

$ curl 'http://houston/history/databases/nasdaq-stk-15min'
{"universes": ["nasdaq-stk"], "bar_size": "15 mins", "vendor": "ib", "times": ["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", "15:00:00", "15:15:00", "15:30:00", "15:45:00"]}

$ quantrocket history create-db 'nasdaq-stk-15min' --universes 'nasdaq-stk' --bar-size '15 mins' --times '09:30:00' '09:45:00' '10:00:00' '15:45:00'
status: successfully created quantrocket.history.nasdaq-stk-15min.sqlite

>>> from quantrocket.history import create_db
>>> create_db("nasdaq-stk-15min", universes=["nasdaq-stk"], bar_size="15 mins", times=["09:30:00", "09:45:00", "10:00:00", "15:45:00"])
{'status': 'successfully created quantrocket.history.nasdaq-stk-15min.sqlite'}

$ curl -X PUT 'http://houston/history/databases/nasdaq-stk-15min?universes=nasdaq-stk&bar_size=15+mins&times=09%3A30%3A00&times=09%3A45%3A00&times=10%3A00%3A00&times=15%3A45%3A00'
{"status": "successfully created quantrocket.history.nasdaq-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, as described below.

Data collection start date

When collecting historical data, QuantRocket first queries the IB API to determine how far back historical data is available for each security. By default, QuantRocket will collect as much data as is available. However, for large databases, such as intraday databases with many securities, it may be useful to set a fixed start date. Typically, the further back you go, the fewer securities there are with available data. Setting a fixed start date limits the size of your database (reducing initial data collection time and improving backtest speed).

Deciding how far back to collect data is made easier if you know how far back it's possible to go, and how many securities in your universe are available back to any given date. Historical data availability for select exchanges is shown here. For other exchanges or for more up-to-date availability, you can use the following approach. First, create a database with no start date:

$ quantrocket history create-db 'usa-stk-15min' --universes 'usa-stk' --bar-size '15 mins'
status: successfully created quantrocket.history.usa-stk-15min.sqlite

>>> from quantrocket.history import create_db
>>> create_db("usa-stk-15min", universes=["usa-stk"], bar_size="15 mins")
{'status': 'successfully created quantrocket.history.usa-stk-15min.sqlite'}

$ curl -X PUT 'http://houston/history/databases/usa-stk-15min?universes=usa-stk&bar_size=15+mins'
{"status": "successfully created quantrocket.history.usa-stk-15min.sqlite"}

$ quantrocket history collect 'usa-stk-15min' --availability
status: the historical data will be collected asynchronously

>>> from quantrocket.history import collect_history
>>> collect_history("usa-stk-15min", availability_only=True)
{'status': 'the historical data will be collected asynchronously'}

$ curl -X POST 'http://houston/history/queue?codes=usa-stk-15min&availability_only=true'
{"status": "the historical data will be collected asynchronously"}

Monitor flightlog, and after the data availability has been saved to your database, use the Python client to query and summarize the start dates:

>>> from quantrocket.history import get_history_availability
>>> start_dates = get_history_availability("usa-stk-15min")
>>> start_dates.head()
ConId
4027   2001-11-29 14:30:00
4050   1980-03-17 14:30:00
4065   1980-03-17 14:30:00
4151   1994-04-15 13:30:00
4157   1980-03-17 14:30:00
Name: StartDate, dtype: datetime64[ns]
>>> # Group start dates by year and plot cumulative totals
>>> cumulative_ticker_counts = start_dates.groupby(start_dates.dt.year).count().cumsum()
>>> # Exclude far future dates, which indicate data is not available
>>> cumulative_ticker_counts = cumulative_ticker_counts[cumulative_ticker_counts.index < 2100]
>>> cumulative_ticker_counts.head()
StartDate
1980    564
1981    591
1982    614
1983    662
1984    693
>>> cumulative_ticker_counts.plot(kind="bar")

Based on your findings, you can drop and re-create the database with a fixed start date (the historical availability records you just collected are stored in a separate database, quantrocket.history.availability.sqlite, so you won't lose any data when dropping your history database):

$ quantrocket history drop-db 'usa-stk-15min' --confirm-by-typing-db-code-again 'usa-stk-15min'
status: deleted quantrocket.history.usa-stk-15min.sqlite
$ quantrocket history create-db 'usa-stk-15min' --universes 'usa-stk' --bar-size '15 mins' --start-date '2005-01-01'
status: successfully created quantrocket.history.usa-stk-15min.sqlite

>>> from quantrocket.history import drop_db, create_db
>>> drop_db("usa-stk-15min", confirm_by_typing_db_code_again="usa-stk-15min")
{'status': 'deleted quantrocket.history.usa-stk-15min.sqlite'}
>>> create_db("usa-stk-15min", universes=["usa-stk"], bar_size="15 mins", start_date="2005-01-01")
{'status': 'successfully created quantrocket.history.usa-stk-15min.sqlite'}

$ curl -X DELETE 'http://houston/history/databases/usa-stk-15min?confirm_by_typing_db_code_again=usa-stk-15min'
{"status": "deleted quantrocket.history.usa-stk-15min.sqlite"}
$ curl -X PUT 'http://houston/history/databases/usa-stk-15min?universes=usa-stk&bar_size=15+mins&start_date=2005-01-01'
{"status": "successfully created quantrocket.history.usa-stk-15min.sqlite"}

Query historical data

You can download a file of historical data:

$ $ quantrocket history get 'demo-stocks-1d' --start-date '2019-01-01' | csvlook --max-rows 5
| ConId  | Date       | Open   | High   | Low    | Close  | Volume   | Wap      | TradeCount |
| ------ | ---------- | ------ | ------ | ------ | ------ | -------- | -------- | ---------- |
| 265598 | 2019-01-02 | 154.89 | 158.85 | 154.23 | 157.92 | 25246800 | 156.9095 | 119993     |
| 265598 | 2019-01-03 | 143.95 | 145.72 | 142.0  | 142.19 | 72021700 | 143.634  | 336633     |
| 265598 | 2019-01-04 | 144.58 | 148.55 | 143.8  | 148.26 | 46077300 | 146.743  | 211279     |
| 265598 | 2019-01-07 | 148.64 | 148.83 | 145.9  | 147.93 | 45127300 | 147.3325 | 202827     |
| 265598 | 2019-01-08 | 149.34 | 151.82 | 148.52 | 150.75 | 32422400 | 150.095  | 155894     |
| ...    | ...        | ...    | ...    | ...    | ...    | ...      | ...      | ...        |

>>> import pandas as pd
>>> from quantrocket.history import download_history_file
>>> download_history_file("demo-stocks-1d",
                          start_date="2019-01-01",
                          filepath_or_buffer="demo_stocks_1d.csv")
>>> prices = pd.read_csv("demo_stocks_1d.csv", parse_dates=["Date"])
>>> prices.head()
    ConId       Date    Open    High     Low   Close    Volume       Wap  TradeCount
0  265598 2019-01-02  154.89  158.85  154.23  157.92  25246800  156.9095      119993
1  265598 2019-01-03  143.95  145.72  142.00  142.19  72021700  143.6340      336633
2  265598 2019-01-04  144.58  148.55  143.80  148.26  46077300  146.7430      211279
3  265598 2019-01-07  148.64  148.83  145.90  147.93  45127300  147.3325      202827
4  265598 2019-01-08  149.34  151.82  148.52  150.75  32422400  150.0950      155894

$ curl -X GET 'http://houston/history/demo-stocks-1d.csv?start_date=2019-01-01' | head
ConId,Date,Open,High,Low,Close,Volume,Wap,TradeCount
265598,2019-01-02,154.89,158.85,154.23,157.92,25246800,156.9095,119993
265598,2019-01-03,143.95,145.72,142.0,142.19,72021700,143.634,336633
265598,2019-01-04,144.58,148.55,143.8,148.26,46077300,146.743,211279
265598,2019-01-07,148.64,148.83,145.9,147.93,45127300,147.3325,202827
265598,2019-01-08,149.34,151.82,148.52,150.75,32422400,150.095,155894

For a higher-level API for loading historical data into Python, see the get_prices function outlined in the Research section.

IB Fundamental Data

IB provides customers with access to global fundamental data sourced from Reuters. IB enables data access by default; no subscription in IB Client Portal is required. Appropriate exchange permissions in QuantRocket are required. There are two available datasets: estimates and actuals, and financial statements.

Reuters estimates and actuals

Collect Reuters estimates

To use Reuters estimates and actuals in QuantRocket, first collect the data from IB into your QuantRocket database. Then you can run queries against the database in your research and backtests.

To collect analyst estimates and actuals, specify one or more conids or universes to collect data for:

$ quantrocket fundamental collect-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. You can monitor flightlog via the command line or Papertrail to track progress:

$ quantrocket flightlog stream
quantrocket.fundamental: INFO Collecting Reuters estimates from IB for universes japan-banks, singapore-banks
quantrocket.fundamental: INFO Expected remaining runtime to collect Reuters estimates for universes japan-banks, singapore-banks: 0:04:25
quantrocket.fundamental: INFO Saved 3298 total records for 60 total securities to quantrocket.fundamental.reuters.estimates.sqlite for universes japan-banks, singapore-banks

Query Reuters estimates

To query Reuters estimates and actuals, first look up the code(s) for the metrics you care about:

$ quantrocket fundamental codes --report-types 'estimates'
estimates:
  BVPS: Book Value Per Share
  CAPEX: Capital Expenditure
  CPS: Cash Flow Per Share
  DPS: Dividend Per Share
  EBIT: Earnings Before Interest and Tax
...

>>> from quantrocket.fundamental import list_reuters_codes
>>> list_reuters_codes(report_types=["estimates"])
{'estimates': {'BVPS': 'Book Value Per Share',
  'CAPEX': 'Capital Expenditure',
  'CPS': 'Cash Flow Per Share',
  'DPS': 'Dividend Per Share',
  'EBIT': 'Earnings Before Interest and Tax',
...
}}

$ curl -X GET 'http://houston/fundamental/reuters/codes?report_types=estimates'
{"estimates": {"BVPS": "Book Value Per Share", "CAPEX": "Capital Expenditure", "CPS": "Cash Flow Per Share", "DPS": "Dividend Per Share", "EBIT": "Earnings Before Interest and Tax",...}}

Let's query EPS estimates and actuals:

$ quantrocket fundamental 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
| ConId | Indicator | Unit | FiscalYear | FiscalPeriodEndDate | FiscalPeriodType | FiscalPeriodNumber | High | Low  | Mean   | ... |
| ----- | --------- | ---- | ---------- | ------------------- | ---------------- | ------------------ | ---- | ---- | ------ | --- |
| 9029  | EPS       | U    | 2014       | 2014-03-31          | Q                | 1                  | 0.31 | 0.2  | 0.255  | ... |
| 9029  | EPS       | U    | 2014       | 2014-06-30          | Q                | 2                  | 0.77 | 0.73 | 0.7467 | ... |
| 9029  | EPS       | U    | 2014       | 2014-09-30          | Q                | 3                  | 0.71 | 0.63 | 0.6667 | ... |
| 9029  | EPS       | U    | 2014       | 2014-12-31          | A                |                    | 2.25 | 2.23 | 2.2433 | ... |
| 9029  | EPS       | U    | 2014       | 2014-12-31          | Q                | 4                  | 0.49 | 0.47 | 0.4833 | ... |
| ...   | ...       | ...  | ...        | ...                 | ...              | ...                | ...  | ...  | ...    | ... |

>>> from quantrocket.fundamental import download_reuters_estimates
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_reuters_estimates(["EPS"],f,universes=["us-banks"],
                            start_date="2014-01-01", end_date="2017-01-01")
>>> eps_estimates = pd.read_csv(f, parse_dates=["FiscalPeriodEndDate", "AnnounceDate"])
>>> eps_estimates.head()
    ConId Indicator Unit  FiscalYear FiscalPeriodEndDate FiscalPeriodType  \
0   9029       EPS    U        2014          2014-03-31                Q
1   9029       EPS    U        2014          2014-06-30                Q
2   9029       EPS    U        2014          2014-09-30                Q
3   9029       EPS    U        2014          2014-12-31                A
4   9029       EPS    U        2014          2014-12-31                Q

   FiscalPeriodNumber  High   Low    Mean  Median  StdDev  NumOfEst  \
0                 1.0  0.31  0.20  0.2550   0.255  0.0550       2.0
1                 2.0  0.77  0.73  0.7467   0.740  0.0170       3.0
2                 3.0  0.71  0.63  0.6667   0.660  0.0330       3.0
3                 NaN  2.25  2.23  2.2433   2.250  0.0094       3.0
4                 4.0  0.49  0.47  0.4833   0.490  0.0094       3.0

         AnnounceDate          UpdatedDate  Actual
0 2014-05-01 11:45:00  2014-05-01T12:06:31    0.12
1 2014-07-31 11:45:00  2014-07-31T13:47:24    1.02
2 2014-11-04 12:45:00  2014-11-04T13:27:49    0.62
3 2015-02-27 12:45:00  2015-02-27T13:20:27    2.29
4 2015-02-27 12:45:00  2015-02-27T13:20:26    0.53

$ 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
ConId,Indicator,Unit,FiscalYear,FiscalPeriodEndDate,FiscalPeriodType,FiscalPeriodNumber,High,Low,Mean,Median,StdDev,NumOfEst,AnnounceDate,UpdatedDate,Actual
9029,EPS,U,2014,2014-03-31,Q,1,0.31,0.2,0.255,0.255,0.055,2,2014-05-01T11:45:00,2014-05-01T12:06:31,0.12
9029,EPS,U,2014,2014-06-30,Q,2,0.77,0.73,0.7467,0.74,0.017,3,2014-07-31T11:45:00,2014-07-31T13:47:24,1.02
9029,EPS,U,2014,2014-09-30,Q,3,0.71,0.63,0.6667,0.66,0.033,3,2014-11-04T12:45:00,2014-11-04T13:27:49,0.62
9029,EPS,U,2014,2014-12-31,A,,2.25,2.23,2.2433,2.25,0.0094,3,2015-02-27T12:45:00,2015-02-27T13:20:27,2.29
9029,EPS,U,2014,2014-12-31,Q,4,0.49,0.47,0.4833,0.49,0.0094,3,2015-02-27T12:45:00,2015-02-27T13:20:26,0.53

Reuters estimates aligned to prices

You can use a DataFrame of historical prices 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, use .loc and .xs to isolate a particular field and time, so that the DataFrame index consists only of dates. Again, the particular field and time don't matter, as only the columns and index will be used:

>>> from quantrocket import get_prices
>>> prices = get_prices("japan-bank-15min", start_date="2017-01-01", fields=["Close", "Volume"])
>>> closes = prices.loc["Close"].xs("15:30:00", level="Time")

Now use the DataFrame of prices to get a DataFrame of estimates and actuals.

>>> # 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"])

Similar to historical data, 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()
ConId                       265598    3691937   15124833  208813719
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"]

Since the columns and date index match that of the historical data, you can perform matrix operations on prices and estimates/actuals together:

>>> # 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 financial statements

Collect Reuters financials

To use Reuters financial statements in QuantRocket, first collect the data from IB into your QuantRocket database. Then you can run queries against the database in your research and backtests.

To collect financial statements, specify one or more conids or universes to collect data for:

$ quantrocket fundamental collect-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. You can monitor flightlog via the command line or Papertrail to track progress:

$ quantrocket flightlog stream
quantrocket.fundamental: INFO Collecting Reuters financials from IB for universes japan-banks, singapore-banks
quantrocket.fundamental: INFO Expected remaining runtime to collect Reuters financials for universes japan-banks, singapore-banks: 0:00:33
quantrocket.fundamental: INFO Saved 12979 total records for 100 total securities to quantrocket.fundamental.reuters.financials.sqlite for universes japan-banks, singapore-banks

Query Reuters financials

To query Reuters financials, first look up the code(s) for the metrics you care about, optionally limiting to a particular statement type:

$ quantrocket fundamental codes --report-types 'financials' --statement-types 'CAS'
financials:
  FCDP: Total Cash Dividends Paid
  FPRD: Issuance (Retirement) of Debt, Net
  FPSS: Issuance (Retirement) of Stock, Net
  FTLF: Cash from Financing Activities
  ITLI: Cash from Investing Activities
  OBDT: Deferred Taxes
  OCPD: Cash Payments
  OCRC: Cash Receipts
...

>>> from quantrocket.fundamental import list_reuters_codes
>>> list_reuters_codes(report_types=["financials"], statement_types=["CAS"])
{'financials': {'FCDP': 'Total Cash Dividends Paid',
  'FPRD': 'Issuance (Retirement) of Debt, Net',
  'FPSS': 'Issuance (Retirement) of Stock, Net',
  'FTLF': 'Cash from Financing Activities',
  'ITLI': 'Cash from Investing Activities',
  'OBDT': 'Deferred Taxes',
  'OCPD': 'Cash Payments',
  'OCRC': 'Cash Receipts',
...
}}

$ curl -X GET 'http://houston/fundamental/reuters/codes?report_types=financials&statement_types=CAS'
{"financials": {"FCDP": "Total Cash Dividends Paid", "FPRD": "Issuance (Retirement) of Debt, Net", "FPSS": "Issuance (Retirement) of Stock, Net", "FTLF": "Cash from Financing Activities", "ITLI": "Cash from Investing Activities", "OBDT": "Deferred Taxes", "OCPD": "Cash Payments", "OCRC": "Cash Receipts",...}}

Let's query Net Income Before Taxes (code EIBT) for a universe of securities:

$ quantrocket fundamental 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 | ConId | Amount | FiscalYear | FiscalPeriodEndDate | FiscalPeriodType | ... |
| ------- | ----- | ------ | ---------- | ------------------- | ---------------- | --- |
| EIBT    | 9029  | 13.53  | 2014       | 2014-12-31          | Annual           | ... |
| EIBT    | 9029  | 28.117 | 2015       | 2015-12-31          | Annual           | ... |
| EIBT    | 12190 | -7.307 | 2014       | 2014-05-31          | Annual           | ... |
| EIBT    | 12190 | -4.188 | 2015       | 2015-05-31          | Annual           | ... |
| EIBT    | 12190 | 1.873  | 2016       | 2016-05-31          | Annual           | ... |
| ...     | ...   | ...    | ...        | ...                 | ...              | ... |

>>> from quantrocket.fundamental import download_reuters_financials
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_reuters_financials(["EIBT"],f,universes=["us-banks"],
                            start_date="2014-01-01", end_date="2017-01-01")
>>> financials = pd.read_csv(f, parse_dates=["SourceDate", "FiscalPeriodEndDate"])
>>> financials.head()
CoaCode   ConId  Amount  FiscalYear FiscalPeriodEndDate FiscalPeriodType  \
0    EIBT    9029  13.530        2014          2014-12-31           Annual
1    EIBT    9029  28.117        2015          2015-12-31           Annual
2    EIBT   12190  -4.188        2015          2015-05-31           Annual
3    EIBT   12190   1.873        2016          2016-05-31           Annual
4    EIBT  270422  -3.770        2015          2015-09-30           Annual

 FiscalPeriodNumber StatementType  StatementPeriodLength  \
0                 NaN           INC                     12
1                 NaN           INC                     12
2                 NaN           INC                     12
3                 NaN           INC                     12
4                 NaN           INC                     12

StatementPeriodUnit UpdateTypeCode UpdateTypeDescription StatementDate  \
0                   M            UPD        Updated Normal    2014-12-31
1                   M            UPD        Updated Normal    2015-12-31
2                   M            UPD        Updated Normal    2015-05-31
3                   M            UPD        Updated Normal    2016-05-31
4                   M            UPD        Updated Normal    2015-09-30

AuditorNameCode        AuditorName AuditorOpinionCode AuditorOpinion Source  \
0              EY  Ernst & Young LLP                UNQ    Unqualified   10-K
1              EY  Ernst & Young LLP                UNQ    Unqualified   10-K
2            CROW  Crowe Horwath LLP                UNQ    Unqualified   10-K
3            CROW  Crowe Horwath LLP                UNQ    Unqualified   10-K
4            CROW  Crowe Horwath LLP                UNQ    Unqualified   10-K

SourceDate
0 2015-03-13
1 2016-02-29
2 2015-08-26
3 2016-08-05
4 2015-12-18

$ 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,ConId,Amount,FiscalYear,FiscalPeriodEndDate,FiscalPeriodType,FiscalPeriodNumber,StatementType,StatementPeriodLength,StatementPeriodUnit,UpdateTypeCode,UpdateTypeDescription,StatementDate,AuditorNameCode,AuditorName,AuditorOpinionCode,AuditorOpinion,Source,SourceDate
EIBT,9029,13.53,2014,2014-12-31,Annual,,INC,12,M,UPD,"Updated Normal",2014-12-31,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2015-03-13
EIBT,9029,28.117,2015,2015-12-31,Annual,,INC,12,M,UPD,"Updated Normal",2015-12-31,EY,"Ernst & Young LLP",UNQ,Unqualified,10-K,2016-02-29
EIBT,12190,-4.188,2015,2015-05-31,Annual,,INC,12,M,UPD,"Updated Normal",2015-05-31,CROW,"Crowe Horwath LLP",UNQ,Unqualified,10-K,2015-08-26
EIBT,12190,1.873,2016,2016-05-31,Annual,,INC,12,M,UPD,"Updated Normal",2016-05-31,CROW,"Crowe Horwath LLP",UNQ,Unqualified,10-K,2016-08-05
EIBT,270422,-3.77,2015,2015-09-30,Annual,,INC,12,M,UPD,"Updated Normal",2015-09-30,CROW,"Crowe Horwath LLP",UNQ,Unqualified,10-K,2015-12-18

$ quantrocket fundamental financials 'EIBT' -u 'us-banks' -s '2014-01-01' -e '2017-01-01' --interim --exclude-restatements -o interim_financials.csv
$ csvlook -I --max-columns 6 --max-rows 5 financials.csv
| CoaCode | ConId  | Amount | FiscalYear | FiscalPeriodEndDate | FiscalPeriodType | ... |
| ------- | ------ | ------ | ---------- | ------------------- | ---------------- | --- |
| EIBT    | 9029   | 15.386 | 2016       | 2016-06-30          | Interim          | ... |
| EIBT    | 9029   | 8.359  | 2016       | 2016-09-30          | Interim          | ... |
| EIBT    | 12190  | 0.744  | 2017       | 2016-08-31          | Interim          | ... |
| EIBT    | 12190  | -0.595 | 2017       | 2016-11-30          | Interim          | ... |
| EIBT    | 270422 | 1.599  | 2016       | 2016-07-01          | Interim          | ... |
| ...     | ...    | ...    | ...        | ...                 | ...              | ... |

>>> from quantrocket.fundamental import download_reuters_financials
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_reuters_financials(["EIBT"],f,universes=["us-banks"],
                            interim=True,
                            exclude_restatements=True,
                            start_date="2014-01-01", end_date="2017-01-01")
>>> interim_financials = pd.read_csv(f, parse_dates=["SourceDate", "FiscalPeriodEndDate"])
>>> interim_financials.head()
CoaCode   ConId  Amount  FiscalYear FiscalPeriodEndDate FiscalPeriodType  \
0    EIBT    9029   8.359        2016          2016-09-30          Interim
1    EIBT    9029   3.459        2016          2016-12-31          Interim
2    EIBT   12190   0.744        2017          2016-08-31          Interim
3    EIBT   12190  -0.595        2017          2016-11-30          Interim
4    EIBT  270422   1.599        2016          2016-07-01          Interim

 FiscalPeriodNumber StatementType  StatementPeriodLength  \
0                   3           INC                      3
1                   4           INC                      3
2                   1           INC                      3
3                   2           INC                      3
4                   3           INC                      3

StatementPeriodUnit UpdateTypeCode UpdateTypeDescription StatementDate  \
0                   M            UPD        Updated Normal    2016-09-30
1                   M            UCA    Updated Calculated    2016-12-31
2                   M            UPD        Updated Normal    2016-08-31
3                   M            UPD        Updated Normal    2016-11-30
4                   M            UPD        Updated Normal    2016-07-01

AuditorNameCode            AuditorName AuditorOpinionCode AuditorOpinion  \
0             NaN                    NaN                NaN            NaN
1             DHS  Deloitte & Touche LLP                UNQ    Unqualified
2             NaN                    NaN                NaN            NaN
3             NaN                    NaN                NaN            NaN
4             NaN                    NaN                NaN            NaN

Source SourceDate
0   10-Q 2016-11-04
1   10-K 2017-03-03
2   10-Q 2016-10-13
3   10-Q 2017-01-12
4   10-Q 2016-08-10

$ curl -X GET 'http://houston/fundamental/reuters/financials.csv?codes=EIBT&universes=us-banks&interim=True&exclude_restatements=True&start_date=2014-01-01&end_date=2017-01-01' --output interim_financials.csv
$ head interim_financials.csv
CoaCode,ConId,Amount,FiscalYear,FiscalPeriodEndDate,FiscalPeriodType,FiscalPeriodNumber,StatementType,StatementPeriodLength,StatementPeriodUnit,UpdateTypeCode,UpdateTypeDescription,StatementDate,AuditorNameCode,AuditorName,AuditorOpinionCode,AuditorOpinion,Source,SourceDate
EIBT,9029,8.359,2016,2016-09-30,Interim,3,INC,3,M,UPD,"Updated Normal",2016-09-30,,,,,10-Q,2016-11-04
EIBT,9029,3.459,2016,2016-12-31,Interim,4,INC,3,M,UCA,"Updated Calculated",2016-12-31,DHS,"Deloitte & Touche LLP",UNQ,Unqualified,10-K,2017-03-03
EIBT,12190,0.744,2017,2016-08-31,Interim,1,INC,3,M,UPD,"Updated Normal",2016-08-31,,,,,10-Q,2016-10-13
EIBT,12190,-0.595,2017,2016-11-30,Interim,2,INC,3,M,UPD,"Updated Normal",2016-11-30,,,,,10-Q,2017-01-12
EIBT,270422,1.599,2016,2016-07-01,Interim,3,INC,3,M,UPD,"Updated Normal",2016-07-01,,,,,10-Q,2016-08-10

Reuters financials aligned to prices

As with Reuters estimates, you can use a DataFrame of historical prices to get Reuters fundamental data that is aligned to the price data. This makes it easy to perform matrix operations using fundamental data.

First, isolate a particular field of your prices DataFrame. It doesn't matter what field you select, as only the date index and the column names will be used to query the fundamentals. For daily data, use .loc:

>>> 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, use .loc and .xs to isolate a particular field and time, so that the DataFrame index consists only of dates. Again, the particular field and time don't matter, as only the columns and index will be used:

>>> from quantrocket import get_prices
>>> prices = get_prices("japan-bank-15min", start_date="2017-01-01", fields=["Close", "Volume"])
>>> closes = prices.loc["Close"].xs("15:30:00", level="Time")

Now use the DataFrame of prices to get a DataFrame of fundamentals.

>>> # 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"])

Similar to historical data, 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()
ConId                           4157       4165        4187       4200
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

Since the columns and date index match that of the historical data, you can perform matrix operations on prices and fundamentals together:

>>> # 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 fundamental 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("usa-stk-eod", 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("usa-stk-eod", 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 conid by conid:

# 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 conid by conid and returns a Series of
# earlier fundamentals
def n_periods_ago(fundamentals_for_conid):
    conid = fundamentals_for_conid.name
    # remove all rows except for new fiscal periods
    new_period_fundamentals = fundamentals_for_conid.where(are_new_fiscal_periods[conid]).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_conid.index, method="ffill")
    return earlier_fundamentals

earlier_current_ratios = current_ratios.apply(n_periods_ago)

Wall Street Horizon earnings announcement dates

The Wall Street Horizon earnings calendar, available via IB, 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 IB into your QuantRocket database. Specify one or more conids or universes to collect data for:

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

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

$ curl -X POST 'http://houston/fundamental/wsh/calendar?universes=usa-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: INFO Collecting Wall Street Horizon earnings dates from IB for universes usa-stk
quantrocket.fundamental: INFO Expected remaining runtime to collect Wall Street Horizon earnings dates for universes usa-stk: 1:34:28
quantrocket.fundamental: INFO Saved 2671 total records for 2556 total securities to quantrocket.fundamental.wsh.calendar.sqlite for universes usa-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 'usa-stk' -o announcement_dates.csv
$ csvlook -I announcement_dates.csv
| ConId | Date       | Time          | Status      | Period | LastUpdated         |
| ----- | ---------- | ------------- | ----------- | ------ | ------------------- |
| 4027  | 2019-05-21 | Before Market | Unconfirmed | 2019-1 | 2019-04-04T02:17:27 |
| 4050  | 2019-06-05 | After Market  | Unconfirmed | 2019-2 | 2019-04-04T02:17:27 |
| 4065  | 2019-04-17 | Before Market | Confirmed   | 2019-1 | 2019-04-04T01:15:46 |
| 4151  | 2019-04-22 | After Market  | Confirmed   | 2019-1 | 2019-04-04T01:15:49 |
| 4157  | 2019-05-29 | Before Market | Unconfirmed | 2019-2 | 2019-04-04T02:17:27 |
| ...   | ...        | ...           | ...         | ...    | ...                 |

>>> from quantrocket.fundamental import download_wsh_earnings_dates
>>> import pandas as pd
>>> download_wsh_earnings_dates("announcement_dates.csv", universes="usa-stk")
>>> announcement_dates = pd.read_csv("announcement_dates.csv", parse_dates=["Date", "LastUpdated"])
>>> announcement_dates.head()
ConId       Date           Time       Status  Period         LastUpdated
0   4027 2019-05-21  Before Market  Unconfirmed  2019-1 2019-04-04 02:17:27
1   4050 2019-06-05   After Market  Unconfirmed  2019-2 2019-04-04 02:17:27
2   4065 2019-04-17  Before Market    Confirmed  2019-1 2019-04-04 01:15:46
3   4151 2019-04-22   After Market    Confirmed  2019-1 2019-04-04 01:15:49
4   4157 2019-05-29  Before Market  Unconfirmed  2019-2 2019-04-04 02:17:27

$ curl -X GET 'http://houston/fundamental/wsh/calendar.csv?universes=usa-stk' --output announcement_dates.csv
$ head announcement_dates.csv
ConId,Date,Time,Status,Period,LastUpdated
4027,2019-05-21,"Before Market",Unconfirmed,2019-1,2019-04-04T02:17:27
4050,2019-06-05,"After Market",Unconfirmed,2019-2,2019-04-04T02:17:27
4065,2019-04-17,"Before Market",Confirmed,2019-1,2019-04-04T01:15:46
4151,2019-04-22,"After Market",Confirmed,2019-1,2019-04-04T01:15:49
4157,2019-05-29,"Before Market",Unconfirmed,2019-2,2019-04-04T02:17:27
4165,2019-04-30,"Before Market",Confirmed,2019-1,2019-04-04T02:17:27
4200,2019-08-15,"Before Market",Confirmed,2019-1,2019-04-04T02:17:27
4205,2019-04-25,"After Market",Confirmed,2019-1,2019-04-04T01:15:51
4211,2019-04-25,"Before Market",Unconfirmed,2019-1,2019-04-04T02:17:27

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

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

$ curl -X GET 'http://houston/fundamental/wsh/calendar.csv?conids=234647242'
ConId,Date,Time,Status,Period,LastUpdated
234647242,2019-04-25,Unspecified,Unconfirmed,2019-1,2018-03-22T13:07:13
234647242,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 ConId and Period, keeping only the latest record as indicated by the LastUpdated field:

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

Earnings announcement dates aligned to prices

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()
ConId                265598    3691937   15124833      208813719
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 ib_trading_calendars:

>>> from ib_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)).date()
>>> closes = closes.reindex(closes.index.union([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 (Reuters, Sharadar, etc.), 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.

Short Sale Data

QuantRocket provides current and historical short sale availability data from IB. 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.

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

No IB market data subscriptions are required to access this dataset but you must have the appropriate exchange permissions in QuantRocket.

Collect short sale data

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-shortshares --countries 'japan' 'usa'
status: the shortable shares will be collected asynchronously

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

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

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

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

$ curl -X POST 'http://houston/fundamental/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:

2018-07-30 13:40:31 quantrocket.fundamental: INFO Collecting japan shortable shares from 2018-04-01 to present
2018-07-30 13:40:40 quantrocket.fundamental: INFO Collecting usa shortable shares from 2018-04-01 to present
2018-07-30 13:42:07 quantrocket.fundamental: INFO Saved 2993493 total shortable shares records to quantrocket.fundamental.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.

Short sale data characteristics

Data storage

IB 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_shortable_shares_reindexed_like and get_borrow_fees_reindexed_like do this for you.)

Missing data

The shortable shares and borrow fees datasets represent IB'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 IB 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."

Query short sale data

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

$ quantrocket fundamental shortshares -u 'usa-stk' -o usa_shortable_shares.csv
$ csvlook -I --max-rows 5 usa_shortable_shares.csv
| ConId | Date                | Quantity |
| ----- | ------------------- | -------- |
| 4027  | 2018-04-15T21:45:02 | 2200000  |
| 4027  | 2018-04-16T13:15:03 | 2300000  |
| 4027  | 2018-04-17T09:15:03 | 2100000  |
| 4027  | 2018-04-17T11:15:02 | 2000000  |
| 4027  | 2018-04-17T11:45:02 | 2100000  |

>>> from quantrocket.fundamental import download_shortable_shares
>>> import pandas as pd
>>> download_shortable_shares(
        "usa_shortable_shares.csv",
        universes=["usa-stk"])
>>> shortable_shares = pd.read_csv(
        "usa_shortable_shares.csv",
        parse_dates=["Date"])
>>> shortable_shares.head()
    ConId               Date  Quantity
0   4027 2018-04-15 21:45:02   2200000
1   4027 2018-04-16 13:15:03   2300000
2   4027 2018-04-17 09:15:03   2100000
3   4027 2018-04-17 11:15:02   2000000
4   4027 2018-04-17 11:45:02   2100000

$ curl -X GET 'http://houston/fundamental/stockloan/shares.csv?&universes=usa-stk' --output usa_shortable_shares.csv
$ head usa_shortable_shares.csv
ConId,Date,Quantity
4027,2018-04-15T21:45:02,2200000
4027,2018-04-16T13:15:03,2300000
4027,2018-04-17T09:15:03,2100000
4027,2018-04-17T11:15:02,2000000
4027,2018-04-17T11:45:02,2100000

$ quantrocket fundamental shortfees -u 'usa-stk' -o usa_borrow_fees.csv
$ csvlook -I --max-rows 5 usa_borrow_fees.csv
| ConId | Date                | FeeRate |
| ----- | ------------------- | ------- |
| 4027  | 2018-04-15T21:45:02 | 0.25    |
| 4027  | 2018-04-24T14:15:02 | 0.262   |
| 4027  | 2018-04-25T14:15:03 | 0.2945  |
| 4027  | 2018-04-26T14:15:03 | 0.2642  |
| 4027  | 2018-04-27T14:15:02 | 0.2609  |

>>> from quantrocket.fundamental import download_borrow_fees
>>> import pandas as pd
>>> download_borrow_fees(
        "usa_borrow_fees.csv",
        universes=["usa-stk"])
>>> borrow_fees = pd.read_csv(
        "usa_borrow_fees.csv",
        parse_dates=["Date"])
>>> borrow_fees.head()
    ConId               Date  FeeRate
0   4027 2018-04-15 21:45:02   0.2500
1   4027 2018-04-24 14:15:02   0.2620
2   4027 2018-04-25 14:15:03   0.2945
3   4027 2018-04-26 14:15:03   0.2642
4   4027 2018-04-27 14:15:02   0.2609

$ curl -X GET 'http://houston/fundamental/stockloan/fees.csv?&universes=usa-stk' --output usa_borrow_fees.csv
$ head usa_borrow_fees.csv
ConId,Date,FeeRate
4027,2018-04-15T21:45:02,0.25
4027,2018-04-24T14:15:02,0.262
4027,2018-04-25T14:15:03,0.2945
4027,2018-04-26T14:15:03,0.2642
4027,2018-04-27T14:15:02,0.2609

Short sale data aligned to prices

As with Reuters fundamentals, you can use a DataFrame of historical prices to get shortable shares or borrow fees data that is aligned to the price data.

First, isolate a particular field of your prices DataFrame. It doesn't matter what field you select, as only the date index and the column names will be used to query the short sale data. For daily data, use .loc:

>>> from quantrocket import get_prices
>>> prices = get_prices("usa-stk-1d", start_date="2018-04-16", fields=["Open","Close", "Volume"])
>>> closes = prices.loc["Close"]

For intraday databases, use .loc and .xs to isolate a particular field and time, so that the DataFrame index consists only of dates. Again, the particular field and time don't matter, as only the columns and index will be used:

>>> from quantrocket import get_prices
>>> prices = get_prices("usa-stk-15min", start_date="2018-04-16", fields=["Close", "Volume"], times="15:30:00")
>>> closes = prices.loc["Close"].xs("15:30:00", level="Time")

Now use the DataFrame of prices to get a DataFrame of shortable shares and/or borrow fees:

>>> from quantrocket.fundamental import get_shortable_shares_reindexed_like, get_borrow_fees_reindexed_like
>>> shortable_shares = get_shortable_shares_reindexed_like(closes)
>>> borrow_fees = get_borrow_fees_reindexed_like(closes)

The resulting DataFrame has a DatetimeIndex matching the input DataFrame:

>>> shortable_shares.head()
ConId       4027       4050       4065         4151       \
Date
2018-04-16  2200000.0  3000000.0  10000000.0   550000.0
2018-04-17  2300000.0  2900000.0  10000000.0   600000.0
2018-04-18  2100000.0  3000000.0  10000000.0   550000.0
2018-04-19  2100000.0  3000000.0  10000000.0   550000.0
2018-04-20  2100000.0  3000000.0  10000000.0   550000.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_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_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()
ConId          4187       4200       4205       4211  \
Date
2018-04-16     1.0198     0.7224     0.5954     0.2500
2018-04-17     0.5023     0.5912     0.5954     0.2500
2018-04-18     0.6257     0.5925     0.5943     0.8844
2018-04-19     0.9537     0.5946     0.6463     0.8844
2018-04-20     1.6422     0.5936     0.3096     0.6476

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

borrow_fees = get_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

Sharadar Data

Sharadar provides premium datasets of US fundamentals and end-of-day prices, including active and delisted tickers, with over 20 years of history. You can use these datasets together for performing research and backtests that are free of survivorship bias, or you can use Sharadar fundamentals with IB historical prices for research, backtesting, and live trading.

A Sharadar premium data subscription is required.

Sharadar master

Sharadar domain

Sharadar data comes with its own securities master file listing all tickers (active and delisted) included in the dataset. The Sharadar master file forms a Venn diagram with IB listings: Sharadar includes delisted tickers not available from IB; IB includes global listings not available from Sharadar; and both include currently listed US stocks. Each vendor assigns its own unique IDs (conids) to its listings. For example, the IB conid for AAPL is 265598, while the Sharadar conid for AAPL is 199059.

In QuantRocket each securities master is referred to as a "domain". IB listings and conids constitute the "main" domain and are stored in quantrocket.master.main.sqlite. Sharadar listings and conids constitute the "sharadar" domain and are stored in quantrocket.master.sharadar.sqlite.

Collect Sharadar listings

Sharadar listings are automatically updated each time you collect Sharadar fundamentals or historical prices. Therefore the listings do not need to be collected by the user. However, if you wish to update the master listings without collecting fundamentals or prices, you can do so:

$ quantrocket master collect-sharadar
status: Saved 15411 Sharadar US stock listings to quantrocket.master.sharadar.sqlite

>>> from quantrocket.master import collect_sharadar_listings
>>> collect_sharadar_listings()
{'status': 'Saved 15411 Sharadar US stock listings to quantrocket.master.sharadar.sqlite'}

$ curl -X POST 'http://houston/master/sharadar/securities'
{"status": "Saved 15411 Sharadar US stock listings to quantrocket.master.sharadar.sqlite"}

Sharadar master file

You can download the Sharadar master file by specifying the domain parameter, which tells QuantRocket to run the command against quantrocket.master.sharadar.sqlite instead of quantrocket.master.main.sqlite (with no domain parameter the command runs against the main, or IB, domain):

$ quantrocket master get --exchanges 'NYSE' --domain 'sharadar' -o sharadar_nyse_securities.csv

>>> from quantrocket.master import download_master_file
>>> download_master_file("sharadar_nyse_securities.csv", exchanges="NYSE", domain="sharadar")

$ curl -X GET 'http://houston/master/sharadar/securities.csv?exchanges=NYSE' > sharadar_nyse_securities.csv

The Sharadar master file provides all of the fields in the IB master file (with NULLs if Sharadar doesn't populate the field), plus additional fields not included in the IB master file.

Sharadar universes

The domain parameter allows you to create and manage universes in the Sharadar master:

$ # create it
$ quantrocket master universe 'nyse-stk' -f sharadar_nyse_securities.csv --domain 'sharadar'
code: nyse-stk
inserted: 5067
provided: 5067
total_after_insert: 5067
$ # list it
$ quantrocket master list-universes --domain 'sharadar'
nyse-stk: 5067
$ # delete it
$ quantrocket master delete-universe 'nyse-stk' --domain 'sharadar'
code: nyse-stk
deleted: 5067

>>> from quantrocket.master import create_universe, list_universes, delete_universe
>>> # create it
>>> create_universe("nyse-stk", infilepath_or_buffer="sharadar_nyse_securities.csv", domain="sharadar")
{'code': 'nyse-stk',
 'provided': 5067,
 'inserted': 5067,
 'total_after_insert': 5067}
>>> # list it
>>> list_universes(domain="sharadar")
{'nyse-stk': 5067}
>>> # delete it
>> delete_universe("nyse-stk", domain="sharadar")
{'code': 'nyse-stk', 'deleted': 5067}

$ # create it
$ curl -X PUT 'http://houston/master/sharadar/universes/nyse-stk' --upload-file sharadar_nyse_securities.csv
{"code": "nyse-stk", "provided": 5067, "inserted": 5067, "total_after_insert": 5067}
$ # list it
$ curl -X GET 'http://houston/master/sharadar/universes'
{"nyse-stk": 5067}
$ # delete it
$ curl -X DELETE 'http://houston/master/sharadar/universes/nyse-stk'
{"code": "nyse-stk", "deleted": 5067}

Sharadar-to-IB ticker translations

To support using Sharadar fundamentals with IB historical prices, QuantRocket provides a mapping of Sharadar conids to IB conids. These translations are stored in quantrocket.master.translations.sqlite.

Let's compare the AAPL listing in IB vs Sharadar. First, check the AAPL listing from IB (not passing a domain parameter defaults to the "main" or IB domain):

$ quantrocket master get -s 'AAPL' -e 'NASDAQ' -f 'Symbol' 'PrimaryExchange' 'LongName' 'Sector' 'Industry' --json | json2yaml
---
  -
    ConId: 265598
    Symbol: "AAPL"
    PrimaryExchange: "NASDAQ"
    LongName: "APPLE INC"
    Sector: "Technology"
    Industry: "Computers"

>>> from quantrocket.master import download_master_file
>>> import io
>>> import pandas as pd
>>> f = io.StringIO()
>>> download_master_file(f, symbols=["AAPL"], exchanges=["NASDAQ"], fields=["Symbol","PrimaryExchange","LongName","Sector","Industry"])
>>> ib_listings = pd.read_csv(f)
>>> ib_listings.iloc[0]
ConId                  265598
Symbol                   AAPL
PrimaryExchange        NASDAQ
LongName            APPLE INC
Sector             Technology
Industry            Computers
Name: 0, dtype: object

$ curl -X GET 'http://houston/master/securities.json?exchanges=NASDAQ&symbols=AAPL&fields=Symbol&fields=PrimaryExchange&fields=LongName&fields=Sector&fields=Industry' | json2yaml
---
  -
    ConId: 265598
    Symbol: "AAPL"
    PrimaryExchange: "NASDAQ"
    LongName: "APPLE INC"
    Sector: "Technology"
    Industry: "Computers"

$ quantrocket master get -s 'AAPL' -e 'NASDAQ' -f 'Symbol' 'PrimaryExchange' 'LongName' 'Sector' 'Industry' --domain 'sharadar' --json | json2yaml
---
  -
    ConId: 199059
    Symbol: "AAPL"
    PrimaryExchange: "NASDAQ"
    LongName: "Apple Inc"
    Sector: "Technology"
    Industry: "Consumer Electronics"

>>> f = io.StringIO()
>>> download_master_file(f, symbols=["AAPL"], exchanges=["NASDAQ"], fields=["Symbol","PrimaryExchange","LongName","Sector","Industry"], domain="sharadar")
>>> sharadar_listings = pd.read_csv(f)
>>> sharadar_listings.iloc[0]
ConId                            199059
Symbol                             AAPL
PrimaryExchange                  NASDAQ
LongName                      Apple Inc
Sector                       Technology
Industry           Consumer Electronics
Name: 0, dtype: object

$ curl -X GET 'http://houston/master/sharadar/securities.json?exchanges=NASDAQ&symbols=AAPL&fields=Symbol&fields=PrimaryExchange&fields=LongName&fields=Sector&fields=Industry' | json2yaml
---
  -
    ConId: 199059
    Symbol: "AAPL"
    PrimaryExchange: "NASDAQ"
    LongName: "Apple Inc"
    Sector: "Technology"
    Industry: "Consumer Electronics"

$ quantrocket master translate 265598 --to 'sharadar'
265598: 199059

>>> from quantrocket.master import translate_conids
>>> translate_conids(list(ib_listings.ConId), to_domain="sharadar")
{265598: 199059}

$ curl -X GET 'http://houston/master/translations?conids=265598&to_domain=sharadar'
{"265598": 199059}

$ quantrocket master translate 199059 --from 'sharadar'
199059: 265598

>>> translate_conids(list(sharadar_listings.ConId), from_domain="sharadar")
{199059: 265598}

$ curl -X GET 'http://houston/master/translations?conids=199059&from_domain=sharadar'
{"199059": 265598}

US historical prices

Collect historical prices

To collect US historical prices from Sharadar, create a database tied to the "sharadar" vendor:

$ quantrocket history create-db 'sharadar-1d' --vendor 'sharadar'
status: successfully created quantrocket.history.sharadar-1d.sqlite

>>> from quantrocket.history import create_db
>>> create_db("sharadar-1d", vendor="sharadar")
{'status': 'successfully created quantrocket.history.sharadar-1d.sqlite'}

$ curl -X PUT 'http://houston/history/databases/sharadar-1d?vendor=sharadar'
{"status": "successfully created quantrocket.history.sharadar-1d.sqlite"}

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

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

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

Each time you collect the data, QuantRocket first updates the Sharadar master database with the latest listings, then collects the entire historical prices dataset your subscription gives you access to. This ensures you always have the latest, up-to-date data. Collecting and loading the data into your database takes anywhere from a few minutes up to 10 or 15 minutes, depending on the number of exchanges and years of history. When finished you'll see a completion message in flightlog:

quantrocket.history: INFO [sharadar-1d] Collecting updated Sharadar securities listings
quantrocket.history: INFO [sharadar-1d] Saved 15411 Sharadar US stock listings to quantrocket.master.sharadar.sqlite
quantrocket.history: INFO [sharadar-1d] Collecting all available history from Sharadar
quantrocket.history: INFO [sharadar-1d] Saved 9019707 total Sharadar records for 8380 total securities to quantrocket.history.sharadar-1d.sqlite

Query historical prices

You can query the database like any other history database:

>>> from quantrocket import get_prices
>>> prices = get_prices("sharadar-1d", universes=["nyse-stk"], start_date="2017-01-01", fields=["Close"])
ConId             114299  114300  114301  114302  114303  ...
Field Date
Close 2017-01-03   16.94    4.17  11.210   19.31    9.00
      2017-01-04   16.73    4.17  11.074   19.36    9.00
      2017-01-05   16.79    4.17  11.175   20.11    9.60

Note that the query runs against the Sharadar master, not the IB master. This means:

• Conids in the query results are Sharadar conids, not IB conids
• If you filter by universes, the Sharadar master is consulted, so the universe should have been defined in the Sharadar master
• If you filter by conids, the conids you provide are interpreted as Sharadar conids, not IB conids

US fundamentals

QuantRocket supports using Sharadar fundamentals with Sharadar historical prices or with IB historical prices. For example, you can run backtests that are free of survivorship bias by using Sharadar fundamentals with Sharadar historical prices. Then, to switch to live trading, you can use Sharadar fundamentals with IB prices.

Collect fundamentals

To collect Sharadar fundamental data:

$ quantrocket fundamental collect-sharadar
status: the fundamental data will be collected asynchronously

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

$ curl -X POST 'http://houston/fundamental/sharadar/sf1'
{"status": "the fundamental data will be collected asynchronously"}

Each time you collect the data, QuantRocket first updates the Sharadar master database with the latest listings, then collects the entire fundamental dataset your subscription gives you access to. This ensures you always have the latest, up-to-date data. Collecting and loading the data into your database takes approximately 5 minutes or less, depending on the number of exchanges and years of history. When finished you'll see a completion message in flightlog:

quantrocket.fundamental: INFO Collecting updated Sharadar securities listings
quantrocket.fundamental: INFO Saved 15411 Sharadar US stock listings to quantrocket.master.sharadar.sqlite
quantrocket.fundamental: INFO Collecting all available fundamentals from Sharadar
quantrocket.fundamental: INFO Saved 1998652 total Sharadar fundamental records for 14082 total securities to quantrocket.fundamental.sharadar.sf1.sqlite

Query fundamentals

Sharadar fundamentals can be used with Sharadar historical prices or with IB historical prices, based on the domain parameter. If the domain is "main," the query results are returned with IB conids; if the domain is "sharadar", the query results are returned with Sharadar conids. The domain parameter also determines whether the universes and conids parameters, if provided, are interpreted as referring to IB conids or Sharadar conids.

For example, in the following query, which specifies the "main" domain, we request as-reported trailing twelve month (ART) fundamentals for AAPL, specified by its IB conid (265598):

$ quantrocket fundamental sharadar --conids 265598 --domain 'main' --dimensions 'ART' -o aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 7 --max-rows 3
|  ConId | DIMENSION |    DATEKEY | REPORTPERIOD | CALENDARDATE | LASTUPDATED |       REVENUE | ... |
| ------ | --------- | ---------- | ------------ | ------------ | ----------- | ------------- | --- |
| 265598 | ART       | 1997-12-05 |   1997-09-26 |   1997-09-30 |  2018-08-01 | 7,081,000,000 | ... |
| 265598 | ART       | 1998-02-09 |   1997-12-26 |   1997-12-31 |  2018-08-01 |               | ... |
| 265598 | ART       | 1998-05-11 |   1998-03-27 |   1998-03-31 |  2018-08-01 |               | ... |

>>> from quantrocket.fundamental import download_sharadar_fundamentals
>>> download_sharadar_fundamentals(domain="main", filepath_or_buffer="aapl_fundamentals.csv", conids=265598, dimensions="ART")
>>> fundamentals = pd.read_csv("aapl_fundamentals.csv", parse_dates=["REPORTPERIOD", "DATEKEY", "CALENDARDATE"])
>>> fundamentals.tail()
     ConId DIMENSION    DATEKEY REPORTPERIOD CALENDARDATE LASTUPDATED       REVENUE
78  265598       ART 2017-08-02   2017-07-01   2017-06-30  2018-08-01  2.235070e+11
79  265598       ART 2017-11-03   2017-09-30   2017-09-30  2018-08-01  2.292340e+11
80  265598       ART 2018-02-02   2017-12-30   2017-12-31  2018-08-01  2.391760e+11

$ curl -X GET 'http://houston/fundamental/sharadar/sf1.csv?conids=265598&domain=main&dimensions=ART' > aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 7 --max-rows 3
|  ConId | DIMENSION |    DATEKEY | REPORTPERIOD | CALENDARDATE | LASTUPDATED |       REVENUE | ... |
| ------ | --------- | ---------- | ------------ | ------------ | ----------- | ------------- | --- |
| 265598 | ART       | 1997-12-05 |   1997-09-26 |   1997-09-30 |  2018-08-01 | 7,081,000,000 | ... |
| 265598 | ART       | 1998-02-09 |   1997-12-26 |   1997-12-31 |  2018-08-01 |               | ... |
| 265598 | ART       | 1998-05-11 |   1998-03-27 |   1998-03-31 |  2018-08-01 |               | ... |

$ quantrocket fundamental sharadar --conids 199059 --domain 'sharadar' --dimensions 'ART' -o aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 7 --max-rows 3
|  ConId | DIMENSION |    DATEKEY | REPORTPERIOD | CALENDARDATE | LASTUPDATED |       REVENUE | ... |
| ------ | --------- | ---------- | ------------ | ------------ | ----------- | ------------- | --- |
| 199059 | ART       | 1997-12-05 |   1997-09-26 |   1997-09-30 |  2018-08-01 | 7,081,000,000 | ... |
| 199059 | ART       | 1998-02-09 |   1997-12-26 |   1997-12-31 |  2018-08-01 |               | ... |
| 199059 | ART       | 1998-05-11 |   1998-03-27 |   1998-03-31 |  2018-08-01 |               | ... |

>>> from quantrocket.fundamental import download_sharadar_fundamentals
>>> download_sharadar_fundamentals(domain="sharadar", filepath_or_buffer="aapl_fundamentals.csv", conids=199059, dimensions="ART")
>>> fundamentals = pd.read_csv("aapl_fundamentals.csv", parse_dates=["REPORTPERIOD", "DATEKEY", "CALENDARDATE"])
>>> fundamentals.tail()
     ConId DIMENSION    DATEKEY REPORTPERIOD CALENDARDATE LASTUPDATED       REVENUE
78  199059       ART 2017-08-02   2017-07-01   2017-06-30  2018-08-01  2.235070e+11
79  199059       ART 2017-11-03   2017-09-30   2017-09-30  2018-08-01  2.292340e+11
80  199059       ART 2018-02-02   2017-12-30   2017-12-31  2018-08-01  2.391760e+11

$ curl -X GET 'http://houston/fundamental/sharadar/sf1.csv?conids=199059&domain=sharadar&dimensions=ART' > aapl_fundamentals.csv
$ csvlook aapl_fundamentals.csv --max-columns 7 --max-rows 3
|  ConId | DIMENSION |    DATEKEY | REPORTPERIOD | CALENDARDATE | LASTUPDATED |       REVENUE | ... |
| ------ | --------- | ---------- | ------------ | ------------ | ----------- | ------------- | --- |
| 199059 | ART       | 1997-12-05 |   1997-09-26 |   1997-09-30 |  2018-08-01 | 7,081,000,000 | ... |
| 199059 | ART       | 1998-02-09 |   1997-12-26 |   1997-12-31 |  2018-08-01 |               | ... |
| 199059 | ART       | 1998-05-11 |   1998-03-27 |   1998-03-31 |  2018-08-01 |               | ... |

Sharadar fundamentals aligned to prices

You can use a DataFrame of Sharadar historical prices or IB historical prices to get Sharadar fundamental data that is aligned to the price data. This makes it easy to perform matrix operations using fundamental data.

First, load the historical prices into Pandas. In this example we load Sharadar prices:

>>> from quantrocket import get_prices
>>> prices = get_prices("sharadar-1d", start_date="2018-01-01", end_date="2019-01-01", fields=["Close"])
>>> closes = prices.loc["Close"]

Now use the DataFrame of prices to get a DataFrame of fundamentals for several desired indicators. We pass domain="sharadar" to tell the function that the input DataFrame contains Sharadar conids rather than IB conids.

>>> from quantrocket.fundamental import get_sharadar_fundamentals_reindexed_like
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                       closes,
                       domain="sharadar",
                       fields=["EPS", "REVENUE", "EVEBITDA"],
                       dimension="ARQ")

Similar to historical data, 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 in this example are Sharadar conids, 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 to avoid lookahead bias.

>>> fundamentals.head()
ConId                   124254       124256     124257      124258
Field   Date
EPS     2018-01-02       -0.21        -0.18      -0.02        0.28
        2018-01-03       -0.21        -0.18      -0.02        0.28
        2018-01-04       -0.21        -0.18      -0.02        0.28
        2018-01-05       -0.21        -0.18      -0.02        0.28
        2018-01-08       -0.21        -0.18      -0.02        0.28
...
REVENUE 2018-10-26  61651000.0  416087000.0  2019730.0  86217000.0
        2018-10-29  61651000.0  416087000.0  2019730.0  86217000.0
        2018-10-30  61651000.0  416087000.0  2019730.0  86217000.0
        2018-10-31  61651000.0  416087000.0  2019730.0  86217000.0
        2018-11-01  61651000.0  416087000.0  2019730.0  86217000.0

You can use .loc to isolate a particular indicator:

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

Using this function with IB historical prices works exactly the same way except that you would pass domain="main" to tell QuantRocket that you're passing IB conids. The resulting fundamentals DataFrame will contain IB conids matching the input DataFrame.

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,
                     domain="sharadar",
                     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,
                     domain="sharadar",
                     fields=["EPS"], # numeric fields
                     dimension="ARQ")
>>> eps = fundamentals.loc["EPS"]
>>> fundamentals = get_sharadar_fundamentals_reindexed_like(
                     closes,
                     domain="sharadar",
                     fields=["REPORTPERIOD"], # non-numeric fields
                     dimension="ARQ")
>>> fiscal_period_end_dates = fundamentals.loc["REPORTPERIOD"]

Available indicators

Available indicators can be found on the Sharadar data guide page or using the API:

$ quantrocket fundamental sharadar-codes
sf1:
  ACCOCI:
    Description: A component of [EQUITY] representing the accumulated change in equity
      from transactions and other events and circumstances from non-owner sources;
      net of tax effect; at period end. Includes foreign currency translation items;
      certain pension adjustments; unrealized gains and losses on certain investments
      in debt and equity securities.
    IndicatorType: Balance Sheet
    Name: Accumulated Other Comprehensive Income
    UnitType: currency
  ASSETS:
    Description: Sum of the carrying amounts as of the balance sheet date of all assets
      that are recognized. Major components are [CASHNEQ]; [INVESTMENTS];[INTANGIBLES];
      [PPNENET];[TAXASSETS] and [RECEIVABLES].
    IndicatorType: Balance Sheet
    Name: Total Assets
    UnitType: currency
    ...

>>> from quantrocket.fundamental import list_sharadar_codes
>>> list_sharadar_codes()
{'sf1': {'ACCOCI': {'Name': 'Accumulated Other Comprehensive Income',
   'Description': 'A component of [EQUITY] representing the accumulated change in equity from transactions and other events and circumstances from non-owner sources; net of tax effect; at period end. Includes foreign currency translation items; certain pension adjustments; unrealized gains and losses on certain investments in debt and equity securities.',
   'UnitType': 'currency',
   'IndicatorType': 'Balance Sheet'},
  'ASSETS': {'Name': 'Total Assets',
   'Description': 'Sum of the carrying amounts as of the balance sheet date of all assets that are recognized. Major components are [CASHNEQ]; [INVESTMENTS];[INTANGIBLES]; [PPNENET];[TAXASSETS] and[RECEIVABLES].',
   'UnitType': 'currency',
   'IndicatorType': 'Balance Sheet'},
   ...}}

$ curl -X GET 'http://houston/fundamental/sharadar/codes'
{"sf1": {"ACCOCI": {"Name": "Accumulated Other Comprehensive Income", "Description": "A component of [EQUITY] representing the accumulated change in equity from transactions and other events and circumstances from non-owner sources; net of tax effect; at period end. Includes foreign currency translation items; certain pension adjustments; unrealized gains and losses on certain investments in debt and equity securities.", "UnitType": "currency", "IndicatorType": "Balance Sheet"}, "ASSETS": {"Name": "Total Assets", "Description": "Sum of the carrying amounts as of the balance sheet date of all assets that are recognized. Major components are [CASHNEQ]; [INVESTMENTS];[INTANGIBLES]; [PPNENET];[TAXASSETS] and [RECEIVABLES].", "UnitType": "currency", "IndicatorType": "Balance Sheet"},...}}

Fundamentals query cache

The fundamental service utilizes a file cache which applies to Sharadar fundamentals and which is explained in another section.

Real-time Data

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

• stream or snapshot: collect a continuous stream of market data or a single snapshot of data
• 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
• live market recording: store the data in a database for later replay

Collect tick data

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 conids, and optionally specify the fields to collect. (If not specified, "LastPrice" and "Volume" are collected. See the market data field reference for available fields.)

$ quantrocket realtime create-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_tick_db
>>> create_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'
{"status": "successfully created tick database fang-stk-tick"}

$ 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"}

You can create any number of databases with differing configurations and collect data for more than one database at a time (subject to concurrent ticker limits).

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 conids by database code instead of summary counts):

$ quantrocket realtime active
ib:
  etf-sampler-trades: 6
  fang-stk-tick: 5

>>> from quantrocket.realtime import get_active_collections
>>> get_active_collections()
{'ib': {'etf-sampler-trades': 6, 'fang-stk-tick': 5}}

$ curl -X GET 'http://houston/realtime/collections'
{"ib": {"etf-sampler-trades": 6, "fang-stk-tick": 5}}

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
...
┌──────────────────────────────────────────────────┐
│ IB 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 conid), which returns the remaining active collections after cancellation:

$ quantrocket realtime cancel 'fang-stk-tick'
ib:
  etf-sampler-trades: 6

>>> from quantrocket.realtime import cancel_market_data
>>> cancel_market_data("fang-stk-tick")
{'ib': {'etf-sampler-trades': 6}}

$ curl -X DELETE 'http://houston/realtime/collections?codes=fang-stk-tick'
{"ib": {"etf-sampler-trades": 6}}

$ 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

>>> from quantrocket.realtime import collect_market_data
>>> 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"}

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 IB 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 IB website to learn more about how concurrent ticker limits are calculated.

When you exceed your ticker limits, the IB 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 (conid 12374), 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:

$ quantrocket realtime collect 'usa-stk-quote' --snapshot --wait
status: completed market data snapshot for usa-stk-quote

>>> from quantrocket.realtime import collect_market_data
>>> collect_market_data("usa-stk-quote", snapshot=True, wait=True)
{'status': 'completed market data snapshot for usa-stk-quote'}

$ curl -X POST 'http://houston/realtime/collections?codes=usa-stk-quote&snapshot=True&wait=True'
{"status": "completed market data snapshot for usa-stk-quote"}

Aside from the obvious difference that snapshot data captures a single point in time while streaming data captures a period of time, below are the major points of comparison between streaming and snapshot data.

Ticker limit

The primary advantage of snapshot data is that it is not subject to concurrent ticker limits. If you want the latest quote for several thousand stocks and are limited to 100 concurrent tickers, snapshot data is the best choice.

Initialization latency

When collecting market data (streaming or snapshot) for several thousand securities, it can take a few minutes to issue all of the initial market data requests to the IB API, after which data flows in real time. (This is because the IB API limits the rate of messages that the client can send to the API, but not the rate of messages that the API can send to the client). With streaming data collection, you can work around this initial latency by simply initiating data collection a few minutes before you need the data. With snapshot data, this isn't possible since you're not collecting a continuous stream.

Fields supported

Snapshot data only supports a subset of the fields supported by streaming data. See the market data field reference.

IB market data field reference

Trades and quotes

LastTradeSize vs Volume

The Volume field contains the cumulative volume for the day, while the LastSize field contains the size of the last trade. Consider using the Volume field for trade size calculation rather than using LastSize. Because IB market data is not tick-by-tick, LastSize may not provide a complete picture of all trades that have occurred. However, the cumulative Volume field will. Trade size can be derived from volume by taking a diff in Pandas:

volumes = prices.loc["Volume"]
trade_sizes = volumes.diff()

Time and sales

TimeSales and TimeSalesFiltered provide an alternative method of collecting trades (but not quotes). These fields are the API equivalent of the Time and Sales window in Trader Workstation.

The primary advantage of these fields is that they provide the trade price, trade size, and trade timestamp (plus other fields) as a unified whole, unlike LastPrice, LastSize, and LastTimestamp which arrive independently and thus can be difficult to associate with one another in fast-moving markets.

When you request TimeSales or TimeSalesFiltered, several nested fields are returned.

• LastPrice - trade price
• LastSize - trade size
• LastTimestamp - UTC datetime of trade
• Volume - total traded volume for the day
• Vwap - volume-weighted average price for the day
• OneFill - whether or not the trade was filled by a single market maker

When streaming over WebSockets, these fields will arrive in a nested data structure:

{
    "v": "ib", # v=vendor
    "i": 15124833, # i=conid
    "t": "2019-06-05T18:23:16.532644", # t=timestamp
    "f": "TimeSales", # f=field
    "d":  { # d=data
        "LastPrice":356.31,
        "LastSize": 100,
        "LastTimestamp": "2019-06-05T18:23:16.409000",
        "Volume": 3043700,
        "Vwap": 353.30651072,
        "OneFill": 1
    }
}

CSV output queried from the database will flatten the nested structure using the following naming convention: TimeSalesLastPrice, TimeSalesLastSize, etc.

Option Greeks

When you request an option computation field, several nested fields will be returned representing the different Greeks. When streaming over WebSockets, these fields will arrive in a nested data structure:

{
    "v": "ib", # v=vendor
    "i": 350561917, # i=conid
    "t": "2019-06-05T16:10:16.162728", # t=timestamp
    "f": "ModelOptionComputation", # f=field
    "d": { # d=data
        "ImpliedVolatility": 0.27965811846647004,
        "Delta": 0.01105129271665234,
        "OptionPrice": 0.028713083045907993,
        "PvDividend": 0.09943775573849334,
        "Gamma": 0.0036857174753818366,
        "Vega": 0.0103567465788384,
        "Theta": -0.0011149809872252135,
        "UnderlyingPrice": 52.37
    }
}

CSV output queried from the database will flatten the nested structure using the following naming convention: ModelOptionComputationImpliedVolatility, ModelOptionComputationDelta, etc.

See Miscellaneous fields for other options-related fields.

Auction imbalance

Miscellaneous fields

Stream tick data over WebSockets

With data collection in progress, you can connect to the incoming data stream over WebSockets. This allows you to push the data stream to your code; meanwhile the realtime service also saves the incoming data to the database in the background for future use.

Streaming market data to a JupyterLab terminal provides a simple technique to monitor the incoming data. To start the stream:

$ quantrocket realtime stream
Received ping
{"v": "ib", "i": 265598, "t": "2019-06-06T14:07:48.750025", "f": "LastPrice", "d": 182.87}
{"v": "ib", "i": 265598, "t": "2019-06-06T14:07:48.750321", "f": "LastSize", "d": 100}
...

Data arrives as a JSON array, with the following structure:

{
    # v = vendor
    "v": "ib",
    # i = conid
    "i": 265598,
    # t = timestamp (UTC)
    "t": "2019-06-06T14:07:48.732735",
    # f = field
    "f": "LastPrice",
    # d = data
    "d": 182.87
}

By default all incoming data is streamed, that is, all collected tickers and all fields, even fields that you have not configured to save to the database. You can optionally limit the fields and conids:

$ quantrocket realtime stream --conids '265598' --fields 'LastPrice' 'BidPrice' 'AskPrice'

WebSocket Python integration

Streaming data is not currently integrated into any of QuantRocket's Python libraries or APIs. We plan to add this integration in the future. For now, users can stream data to their own custom scripts by installing and using the WebSockets library.

The wscat utility is a useful tool to help you understand the WebSocket API for the purpose of Python development.

wscat

The command quantrocket realtime stream is a lightweight wrapper around wscat, a command-line utility written in Node.js for making WebSocket connections. You can use wscat directly if you prefer, which is useful for experimenting with the WebSocket API. To start the stream:

$ wscat -c 'http://houston/realtime/stream'
connected (press CTRL+C to quit)
< Received ping
< {"v": "ib", "i": 265598, "t": "2019-06-06T14:07:48.750025", "f": "LastPrice", "d": 182.87}
< {"v": "ib", "i": 265598, "t": "2019-06-06T14:07:48.750321", "f": "LastSize", "d": 100}
...

You can send a JSON message to limit the fields:

> {"fields": ["LastPrice", "BidPrice", "AskPrice"]}

To limit the securities being returned, send JSON messages with the keys "conids" or "exclude_conids" to indicate which tickers you want to add to, or subtract from, the current stream. For example, this sequence of messages would exclude all tickers from the stream then re-enable only AAPL:

> {"exclude_conids":"*"}
> {"conids":[265598]}

You can also provide the filters as query string parameters at the time you initiate the WebSocket connection:

$ wscat -c 'http://houston/realtime/stream?conids=265598&conids=3691937&fields=LastPrice&fields=BidPrice'

Query tick data

You can download a file of the ticks stored in your tick database:

$ quantrocket realtime get 'fang-stk-tick' --start-date '2019-06-06' --conids 265598 --fields 'LastPrice' 'BidPrice' 'AskPrice' | csvlook --max-rows 10
| ConId  | Date                          | LastPrice | BidPrice | AskPrice |
| ------ | ----------------------------- | --------- | -------- | -------- |
| 265598 | 2019-06-06 12:56:38.253084+00 | 182.77    |          |          |
| 265598 | 2019-06-06 12:56:38.273889+00 |           | 182.76   |          |
| 265598 | 2019-06-06 12:56:38.2748+00   |           |          | 182.82   |
| 265598 | 2019-06-06 12:56:39.348729+00 | 182.82    |          |          |
| 265598 | 2019-06-06 12:56:39.349447+00 |           |          | 182.85   |
| 265598 | 2019-06-06 12:56:39.848083+00 | 182.85    |          |          |
| 265598 | 2019-06-06 12:56:40.600397+00 |           |          | 182.98   |
| 265598 | 2019-06-06 12:56:41.102615+00 |           | 182.85   |          |
| 265598 | 2019-06-06 12:56:46.864118+00 |           |          | 182.95   |
| 265598 | 2019-06-06 12:56:47.365166+00 |           | 182.9    |          |
| ...    | ...                           | ...       | ...      | ...      |

>>> import pandas as pd
>>> from quantrocket.realtime import download_market_data_file
>>> download_market_data_file("fang-stk-tick",
                              start_date="2019-06-06",
                              conids=265598, fields=["LastPrice","BidPrice","AskPrice"],
                              filepath_or_buffer="fang_stk_tick.csv")
>>> ticks = pd.read_csv("fang_stk_tick.csv", parse_dates=["Date"])
>>> ticks.head()
    ConId                       Date  LastPrice  BidPrice  AskPrice
0  265598 2019-06-06 12:56:38.253084     182.77       NaN       NaN
1  265598 2019-06-06 12:56:38.273889        NaN    182.76       NaN
2  265598 2019-06-06 12:56:38.274800        NaN       NaN    182.82
3  265598 2019-06-06 12:56:39.348729     182.82       NaN       NaN
4  265598 2019-06-06 12:56:39.349447        NaN       NaN    182.85
5  265598 2019-06-06 12:56:39.848083     182.85       NaN       NaN
6  265598 2019-06-06 12:56:40.600397        NaN       NaN    182.98
7  265598 2019-06-06 12:56:41.102615        NaN    182.85       NaN
8  265598 2019-06-06 12:56:46.864118        NaN       NaN    182.95
9  265598 2019-06-06 12:56:47.365166        NaN    182.90       NaN

$ curl -X GET 'http://houston/realtime/fang-stk-tick.csv?start_date=2019-06-06&conids=265598&fields=LastPrice&fields=BidPrice&fields=AskPrice' | head
ConId,Date,LastPrice,BidPrice,AskPrice
265598,2019-06-06 12:56:38.253084+00,182.77,,
265598,2019-06-06 12:56:38.273889+00,,182.76,
265598,2019-06-06 12:56:38.2748+00,,,182.82
265598,2019-06-06 12:56:39.348729+00,182.82,,
265598,2019-06-06 12:56:39.349447+00,,,182.85
265598,2019-06-06 12:56:39.848083+00,182.85,,
265598,2019-06-06 12:56:40.600397+00,,,182.98
265598,2019-06-06 12:56:41.102615+00,,182.85,
265598,2019-06-06 12:56:46.864118+00,,,182.9

Aggregate databases

Aggregate databases provide rolled-up views of tick databases. Tick data can be rolled up to any bar size, for example 1 second, 1 minute, 15 minutes, 2 hours, or 1 day. One of the major benefits of aggregate databases is that they provide a consistent API with historical databases, using the get_prices function.

Create aggregate database

Create an aggregate database by providing a database code, the tick database to aggregate, the bar size (using a Pandas timedelta string such as '1s', '1m', '1h' or '1d'), and how to aggregate the tick fields. For example, the following command creates a 1-minute aggregate database with OHLCV bars, that is, with bars containing the open, high, low, and close of the LastPrice field, plus the close of the Volume field:

$ quantrocket realtime create-agg-db 'fang-stk-tick-1min' --tick-db 'fang-stk-tick' --bar-size '1m' --fields 'LastPrice:Open,High,Low,Close' 'Volume:Close'
status: successfully created aggregate database fang-stk-tick-1min from tick database fang-stk-tick

>>> from quantrocket.realtime import create_agg_db
>>> create_agg_db("fang-stk-tick-1min",
                  tick_db_code="fang-stk-tick",
                  bar_size="1m",
                  fields={"LastPrice":["Open","High","Low","Close"],
                          "Volume": ["Close"]})
{'status': 'successfully created aggregate database fang-stk-tick-1min from tick database fang-stk-tick'}

$ curl -X PUT 'http://houston/realtime/databases/fang-stk-tick/aggregates/fang-stk-tick-1min?bar_size=1m&fields=LastPrice%3AOpen%2CHigh%2CLow%2CClose&fields=Volume%3AClose'
{"status": "successfully created aggregate database fang-stk-tick-1min from tick database fang-stk-tick"}

$ quantrocket realtime config 'fang-stk-tick-1min'
bar_size: 1m
fields:
- LastPriceClose
- LastPriceHigh
- LastPriceLow
- LastPriceOpen
- VolumeClose
tick_db_code: fang-stk-tick

>>> from quantrocket.realtime import get_db_config
>>> get_db_config("fang-stk-tick-1min")
{'tick_db_code': 'fang-stk-tick',
 'bar_size': '1m',
 'fields': ['LastPriceClose',
  'LastPriceHigh',
  'LastPriceLow',
  'LastPriceOpen',
  'VolumeClose']}

$ curl -X GET 'http://houston/realtime/databases/fang-stk-tick/aggregates/fang-stk-tick-1min'
{"tick_db_code": "fang-stk-tick", "bar_size": "1m", "fields": ["LastPriceClose", "LastPriceHigh", "LastPriceLow", "LastPriceOpen", "VolumeClose"]}

You can create multiple aggregate databases from a single tick database.

Materialization of aggregate databases

An aggregate database is populated by aggregating the tick data and storing the aggregated results as a separate database table which can then be queried directly. In database terminology, this process is called materialization.

No user action is required to materialize the aggregate database.

QuantRocket uses TimescaleDB to store tick data as well as to build aggregate databases from tick data. After you create an aggregate database, background workers will materialize the aggregate database from the tick data and will periodically run again to keep the aggregate database up-to-date. Moreover, the aggregate database is refreshed at the last second each time you query it to ensure it is fully up-to-date. This last-second refresh is usually very quick due to the ongoing background refresh of the aggregate database that has already happened. However, the first query might run slowly if the aggregate database is still being materialized for the first time.

Query aggregate data

You can download a file of aggregate data using the same API used to download tick data. Instead of ticks, bars are returned. As with tick data, all timestamps are UTC:

$ quantrocket realtime get 'fang-stk-tick-1min' --start-date '2019-06-06' --conids 265598 | csvlook --max-rows 5
| ConId  | Date                   | VolumeClose | LastPriceOpen | LastPriceClose | LastPriceHigh | LastPriceLow |
| ------ | ---------------------- | ----------- | ------------- | -------------- | ------------- | ------------ |
| 265598 | 2019-06-06 12:56:00+00 | 127000      | 182.77        | 182.9          | 182.9         | 182.77       |
| 265598 | 2019-06-06 12:57:00+00 | 129300      | 182.95        | 182.9          | 182.95        | 182.78       |
| 265598 | 2019-06-06 12:58:00+00 | 131900      | 182.78        | 182.8          | 182.95        | 182.78       |
| 265598 | 2019-06-06 12:59:00+00 | 133400      | 182.81        | 182.75         | 182.88        | 182.75       |
| 265598 | 2019-06-06 13:00:00+00 | 123400      | 182.79        | 182.81         | 182.81        | 182.79       |
| ...    | ...                    | ...         | ...           | ...            | ...           | ...          |

>>> import pandas as pd
>>> from quantrocket.realtime import download_market_data_file
>>> download_market_data_file("fang-stk-tick-1min",
                              start_date="2019-06-06",
                              conids=265598,
                              filepath_or_buffer="fang_stk_tick_1min.csv")
>>> prices = pd.read_csv("fang_stk_tick_1min.csv", parse_dates=["Date"])
>>> prices.head()
    ConId                Date  VolumeClose  LastPriceOpen  LastPriceClose  LastPriceHigh  LastPriceLow
0  265598 2019-06-06 12:56:00     127000.0         182.77          182.90         182.90        182.77
1  265598 2019-06-06 12:57:00     129300.0         182.95          182.90         182.95        182.78
2  265598 2019-06-06 12:58:00     131900.0         182.78          182.80         182.95        182.78
3  265598 2019-06-06 12:59:00     133400.0         182.81          182.75         182.88        182.75
4  265598 2019-06-06 13:00:00     123400.0         182.79          182.81         182.81        182.79

$ curl -X GET 'http://houston/realtime/fang-stk-tick-1min.csv?start_date=2019-06-06&conids=265598' | head
ConId,Date,VolumeClose,LastPriceOpen,LastPriceClose,LastPriceHigh,LastPriceLow
265598,2019-06-06 12:56:00+00,127000,182.77,182.9,182.9,182.77
265598,2019-06-06 12:57:00+00,129300,182.95,182.9,182.95,182.78
265598,2019-06-06 12:58:00+00,131900,182.78,182.8,182.95,182.78
265598,2019-06-06 12:59:00+00,133400,182.81,182.75,182.88,182.75

For a higher-level API, you can load real-time aggregate data with the get_prices function which is also used for loading historical data.

Database size

Collecting tick data can quickly consume a considerable amount of disk space. Creating an aggregate database from the tick database uses additional space. Therefore you should keep an eye on your disk space.

Tick data collection strategy

Here is an example strategy for collecting more tick data than will fit on your local disk.

Suppose you have the following constraints:

1. you have only enough local disk space for 3 months of tick data
2. you want data that won't fit on your local disk to be preserved in the cloud indefinitely
3. your trading strategies require at minimum that the past 2 weeks of tick data are available on the local disk

First, create the tick database and append a date or version number:

$ quantrocket realtime create-tick-db 'globex-fut-taq-1' --universes 'globex-fut' --fields 'LastPrice' 'BidPrice' 'AskPrice'
status: successfully created tick database globex-fut-taq-1

>>> from quantrocket.realtime import create_tick_db
>>> create_tick_db("globex-fut-taq-1", universes="globex-fut", fields=["LastPrice","BidPrice","AskPrice"])
{'status': 'successfully created tick database globex-fut-taq-1'}

$ curl -X PUT 'http://houston/realtime/databases/globex-fut-taq-1?universes=globex-fut&fields=LastPrice&fields=BidPrice&fields=AskPrice'
{"status": "successfully created tick database globex-fut-taq-1"}

$ quantrocket realtime create-tick-db 'globex-fut-taq-2' --universes 'globex-fut' --fields 'LastPrice' 'BidPrice' 'AskPrice'
status: successfully created tick database globex-fut-taq-2

>>> create_tick_db("globex-fut-taq-2", universes="globex-fut", fields=["LastPrice","BidPrice","AskPrice"])
{'status': 'successfully created tick database globex-fut-taq-2'}

$ curl -X PUT 'http://houston/realtime/databases/globex-fut-taq-2?universes=globex-fut&fields=LastPrice&fields=BidPrice&fields=AskPrice'
{"status": "successfully created tick database globex-fut-taq-2"}

$ quantrocket db s3push --services 'realtime' --codes 'globex-fut-taq-1'
status: the databases will be pushed to S3 asynchronously

>>> from quantrocket.db import s3_push_databases
>>> s3_push_databases(services="realtime", codes="globex-fut-taq-1")
{'status': 'the databases will be pushed to S3 asynchronously'}

$ curl -X PUT 'http://houston/db/s3?services=realtime&codes=globex-fut-taq-1'
{"status": "the databases will be pushed to S3 asynchronously"}

$ quantrocket realtime drop-db 'globex-fut-taq-1' --confirm-by-typing-db-code-again 'globex-fut-taq-1'
status: deleted tick database globex-fut-taq-1

>>> from quantrocket.realtime import drop_db
>>> drop_db("globex-fut-taq-1", confirm_by_typing_db_code_again="globex-fut-taq-1")
{'status': 'deleted tick database globex-fut-taq-1'}

$ curl -X DELETE 'http://houston/realtime/databases/globex-fut-taq-1?confirm_by_typing_db_code_again=globex-fut-taq-1'
{"status": "deleted tick database globex-fut-taq-1"}

History database as real-time feed

Each time you update a history database, the data is brought current as of the moment you collect it. Thus, for some use cases it may be suitable to use a history database as a real-time data source. One advantage of this approach, compared to using the realtime service, is simplicity: you only have to worry about a single database.

The primary limitation of this approach is that it takes longer to collect data using the history service than using the realtime service. This difference isn't significant for a small number of symbols, but it can be quite significant if you need up-to-date quotes for thousands of securities.

Wait for historical data collection

When using a history database as a real-time data source, you may need to coordinate data collection with other tasks that depend on the data. For example, if trading an intraday strategy using a history database, you will typically want to run your strategy shortly after collecting data, but you want to ensure that the strategy doesn't run while data collection is still in progress. You can use the command quantrocket history wait for this purpose. This command simply blocks until the specified database is no longer being collected:

$ # start data collection
$ quantrocket history collect 'arca-15min'
status: the historical data will be collected asynchronously
$ # wait for data collection to finish
$ quantrocket history wait 'arca-15min'
status: data collection finished for arca-15min

An optional timeout can be provided using a Pandas timedelta string; if the data collection doesn't finish within the allotted timeout, the wait command will return an error message and exit nonzero:

$ quantrocket history wait 'arca-15min' --timeout '10sec'
msg: data collection for arca-15min not finished after 10sec
status: error

To use the wait command on your countdown service crontab, you can run it before your trade command. In the example below, we collect data at 9:45 and want to place orders at 10:00. In case data collection is too slow, we will wait up to 5 minutes to place orders (that is, until 10:05). If data collection is still not finished, the wait command will exit nonzero and the strategy will not run. (If data collection is finished before 10:00, the wait command will return immediately and our strategy will run immediately.)

# Update history db at 9:45 AM
45 9 * * mon-fri quantrocket master isopen 'ARCA' && quantrocket history collect 'arca-15min'

# Run strategy at 10:00 AM, waiting up to 5 minutes for data collection to finish
0 10 * * mon-fri quantrocket master isopen 'ARCA' && quantrocket history wait 'arca-15min' --timeout '5min' && quantrocket moonshot trade 'intraday-strategy' | quantrocket blotter order -f '-'

Alternatively, if you want to run your strategy as soon as data collection finishes, you can place everything on one line:

45 9 * * mon-fri quantrocket master isopen 'ARCA' && quantrocket history collect 'arca-15min' && quantrocket history wait 'arca-15min' --timeout '15min' && quantrocket moonshot trade 'intraday-strategy' | quantrocket blotter order -f '-'

Research

Once you've collected some historical data, you're ready to open a research notebook in JupyterLab and start analyzing your ideas. QuantRocket makes it easy to work with historical and fundamental data using Pandas. You can analyze your alpha factors using Alphalens. Once you're ready to backtest, your research code readily transfers to Moonshot since Moonshot is Pandas-based.

Why research notebooks?

The workflow of many quants includes a research stage prior to backtesting. The purpose of a separate research stage is to rapidly test ideas in a preliminary manner to see if they're worth the effort of a full-scale backtest. The research stage typically ignores transaction costs, liquidity constraints, and other real-world challenges that traders face and that backtests try to simulate. Thus, the research stage constitutes a "first cut": promising ideas advance to the more stringent simulations of backtesting, while unpromising ideas are discarded.

Jupyter notebooks provide Python quants with an excellent tool for ad-hoc research. Jupyter notebooks let you write code to crunch your data, run visualizations, and make sense of the results with narrative commentary.

The get_prices function

End-of-day historical data

Using the Python client, you can load historical data into a Pandas DataFrame using the database code:

>>> from quantrocket import get_prices
>>> prices = get_prices("japan-bank-eod", start_date="2017-01-01", fields=["Open","High","Low","Close", "Volume"])

The DataFrame will have a column for each security (represented by conids). For daily bar sizes and larger, the DataFrame will have a two-level index: an outer level for each field (Open, Close, Volume, etc.) and an inner level containing a DatetimeIndex:

>>> prices.head()
ConId              13857203   13905344   13905462   13905522   13905624   \
Field Date
Close 2017-01-04    11150.0     3853.0     4889.0     4321.0     2712.0
      2017-01-05    11065.0     3910.0     4927.0     4299.0     2681.0
      2017-01-06    11105.0     3918.0     4965.0     4266.0     2672.5
      2017-01-10    11210.0     3886.0     4965.0     4227.0     2640.0
      2017-01-11    11115.0     3860.0     4970.0     4208.0     2652.0
...
Volume 2018-01-29   685800.0  2996700.0  1000600.0  1339000.0  6499600.0
       2018-01-30   641700.0  2686100.0  1421900.0  1709900.0  7039800.0
       2018-01-31   603400.0  3179000.0  1517100.0  1471000.0  5855500.0
       2018-02-01   447300.0  3300900.0  1295800.0  1329600.0  5540600.0
       2018-02-02   510200.0  4739800.0  2060500.0  1145200.0  5585300.0

The DataFrame can be thought of as several stacked DataFrames, one for each field. You can use .loc to isolate a DataFrame for each field:

>>> closes = prices.loc["Close"]
>>> closes.head()
ConId        13857203   13905344   13905462   13905522   13905624   13905665   \
Date
2017-01-04    11150.0     3853.0     4889.0     4321.0     2712.0      655.9
2017-01-05    11065.0     3910.0     4927.0     4299.0     2681.0      658.4
2017-01-06    11105.0     3918.0     4965.0     4266.0     2672.5      656.2
2017-01-10    11210.0     3886.0     4965.0     4227.0     2640.0      652.8
2017-01-11    11115.0     3860.0     4970.0     4208.0     2652.0      665.1

Each field's DataFrame has the same columns and index, which makes it easy to perform matrix operations. For example, calculate dollar volume (or Euro volume, Yen volume, etc. depending on the universe):

>>> volumes = prices.loc["Volume"]
>>> dollar_volumes = closes * volumes

Or calculate overnight (close-to-open) returns:

>>> opens = prices.loc["Open"]
>>> prior_closes = closes.shift()
>>> overnight_returns = (opens - prior_closes) / prior_closes
>>> overnight_returns.head()
ConId        13857203   13905344   13905462   13905522   13905624   13905665   \
Date
2017-01-04        NaN        NaN        NaN        NaN        NaN        NaN
2017-01-05   0.001345   0.004412   0.003477  -0.002083   0.002765   0.021497
2017-01-06  -0.000904  -0.005115  -0.000812  -0.011165  -0.016039  -0.012606
2017-01-10  -0.003152  -0.006891   0.009869  -0.008204  -0.011038  -0.002591
2017-01-11   0.000446  -0.000257   0.007049   0.004968   0.001894   0.009498

Intraday historical data

In contrast to daily bars, the stacked DataFrame for intraday bars is a three-level index, consisting of the field, the date, and the time as a string (for example, 09:30:00):

>>> prices = get_prices("etf-1h", start_date="2017-01-01", fields=["Open","High","Low","Close", "Volume"])
>>> prices.head()
ConId                         756733  72195411  73128548
Field Date        Time
Close 2017-07-20  09:30:00    247.28    324.30    216.27
                  10:00:00    247.08    323.94    216.25
                  11:00:00    246.97    323.63    215.90
                  12:00:00    247.25    324.11    216.22
                  13:00:00    247.29    324.32    216.22
...
Volume 2017-08-04 11:00:00   5896400.0  168700.0  170900.0
                  12:00:00   2243700.0  237300.0  114100.0
                  13:00:00   2228000.0  113900.0  107600.0
                  14:00:00   2841400.0   84500.0  116700.0
                  15:00:00  11351600.0  334000.0  357000.0

As with daily bars, use .loc to isolate a particular field.

>>> closes = prices.loc["Close"]
>>> closes.head()
ConId                  756733  72195411  73128548
Date       Time
2017-07-20 09:30:00    247.28    324.30    216.27
           10:00:00    247.08    323.94    216.25
           11:00:00    246.97    323.63    215.90
           12:00:00    247.25    324.11    216.22
           13:00:00    247.29    324.32    216.22

To isolate a particular time, use Pandas' .xs method (short for "cross-section"):

>>> session_closes = closes.xs("15:45:00", level="Time")
>>> session_closes.head()
ConId         756733  72195411  73128548
Date
2017-07-20    247.07    323.84    216.16
2017-07-21    246.89    322.93    215.53
2017-07-24    246.81    323.50    215.09
2017-07-25    247.39    326.37    215.88
2017-07-26    247.45    323.36    216.81

After taking a cross-section of an intraday DataFrame, you can perform matrix operations with bars from different times of day:

>>> opens = prices.loc["Open"]
>>> session_opens = opens.xs("09:30:00", level="Time")
>>> session_closes = closes.xs("15:45:00", level="Time")
>>> prior_session_closes = session_closes.shift()
>>> overnight_returns = (session_opens - prior_session_closes) / prior_session_closes
>>> overnight_returns.head()
ConId       756733    72195411  73128548
Date
2017-07-20       NaN       NaN       NaN
2017-07-21 -0.002509 -0.001637 -0.004441
2017-07-24 -0.000405 -0.000929 -0.000139
2017-07-25  0.003525  0.005286  0.006555
2017-07-26  0.001455  0.000123  0.004308

Timezone of intraday data

Intraday historical data is stored in the database in ISO-8601 format, which consists of the date followed by the time in the local timezone of the exchange, followed by a UTC offset. For example, a 9:30 AM bar for a stock trading on the NYSE might have a timestamp of 2017-07-25T09:30:00-04:00, where -04:00 indicates that New York is 4 hours behind Greenwich Mean Time/UTC. This storage format allows QuantRocket to properly align data that may originate from different timezones.

If you don't specify the timezone parameter when loading prices into Pandas using get_prices, the function will infer the timezone from the data itself. (This is accomplished by querying the securities master database to determine the timezone of the securities in your dataset.) This approach works fine as long as your data originates from a single timezone. If multiple timezones are represented, an error will be raised.

>>> prices = get_prices("aapl-arb-5min")
ParameterError: cannot infer timezone because multiple timezones are present in data, please specify timezone explicitly (timezones: America/New_York, America/Mexico_City)

In this case, you should manually specify the timezone to which you want the data to be aligned:

>>> prices = get_prices("aapl-arb-5min", timezone="America/New_York")

Historical data with a bar size of 1 day or higher is stored and returned in YYYY-MM-DD format. Specifying a timezone for such a database has no effect.

Securities master fields aligned to prices

Sometimes it might be useful to use securities master fields such as the primary exchange in your data analysis. To do so, first use .loc (or .loc and .xs for intraday data) to isolate a particular price field:

>>> prices = get_prices("usa-1d", fields=["Close","Open"], start_date="2018-01-01")
>>> closes = prices.loc["Close"]

Then use the DataFrame of prices to get a DataFrame of securities master fields shaped like the prices:

>>> from quantrocket.master import get_securities_reindexed_like
>>> securities = get_securities_reindexed_like(closes, domain="main", fields=["PrimaryExchange", "Symbol"])

You can isolate the securities master fields using .loc:

>>> exchanges = securities.loc["PrimaryExchange"]
>>> exchanges.head()
ConId           4027      4157      4205 309001160 309221203
Date
2018-01-02      NYSE    NASDAQ      NYSE      AMEX    NASDAQ
2018-01-03      NYSE    NASDAQ      NYSE      AMEX    NASDAQ
2018-01-04      NYSE    NASDAQ      NYSE      AMEX    NASDAQ
2018-01-05      NYSE    NASDAQ      NYSE      AMEX    NASDAQ
2018-01-08      NYSE    NASDAQ      NYSE      AMEX    NASDAQ

And perform matrix operations using your securities master data and price data:

>>> closes.where(exchanges=="NYSE").head()
ConId           4027      4157      4205 309001160 309221203
Date
2018-01-02    106.09       NaN     46.73       NaN       NaN
2018-01-03    107.05       NaN     46.46       NaN       NaN
2018-01-04       111       NaN     46.86       NaN       NaN
2018-01-05    112.18       NaN     47.02       NaN       NaN
2018-01-08    111.39       NaN     47.13       NaN       NaN

Load only what you need

The more data you load into Pandas, the slower the performance will be. Therefore, it's a good idea to filter the dataset before loading it, particularly when working with large universes and intraday bars. Use the fields, times, start_date, and end_date parameters to load only the data you need:

>>> prices = get_prices("usa-stk-15min", start_date="2017-01-01", fields=["Open","Close"], times=["09:30:00", "15:45:00"])

Cumulative daily prices for intraday data

For historical databases with bar sizes smaller than 1 day, QuantRocket will calculate and store the day's high, low, and volume as of each intraday bar. When querying intraday data, the additional fields DayHigh, DayLow, and DayVolume are available. Other fields represent only the trading activity that occurred within the duration of a particular bar: for example, the Volume field for a 15:00:00 bar in a database with 1-hour bars represents the trading volume from 15:00:00 to 16:00:00. In contrast, DayHigh, DayLow, and DayVolume represent the trading activity for the entire day up to and including the particular bar.

>>> prices = get_prices(
              "spy-1h",
              fields=["Open","High","Low","Close","Volume","DayHigh","DayLow","DayVolume"])
>>> # Below, the volume from 15:00 to 16:00 is 16.9M shares, while the day's total
>>> # volume through 16:00 (the end of the bar) is 48M shares. The low between
>>> # 15:00 and 16:00 is 272.97, while the day's low is 272.42.
>>> prices.xs("2018-03-08", level="Date").xs("15:00:00", level="Time")
ConId           756733
Field
Close           274.09
DayHigh         274.24
DayLow          272.42
DayVolume  48126000.00
High            274.24
Low             272.97
Open            273.66
Volume     16897100.00

A common use case for cumulative daily totals is if your research idea or trading strategy needs a selection of intraday prices but also needs access to daily price fields (e.g. to calculate average daily volume). Instead of requesting and aggregating all intraday bars (which for large universes might require loading too much data), you can use the times parameter to load only the intraday bars you need, including the final bar of the trading session to give you access to the daily totals. For example, here is how you might screen for stocks with heavy volume in the opening 30 minutes relative to their average volume:

>>> # load the 9:45-10:00 bar and the 15:45-16:00 bar
>>> prices = get_prices("usa-stk-15min", start_date="2018-01-01", times=["09:45:00","15:45:00"], fields=["DayVolume"])
>>> # the 09:45:00 bar contains the cumulative volume through the end of the bar (10:00:00)
>>> early_session_volumes = prices.loc["DayVolume"].xs("09:45:00", level="Time")
>>> # the 15:45:00 bar contains the cumulative volume for the entire day
>>> daily_volumes = prices.loc["DayVolume"].xs("15:45:00", level="Time")
>>> avg_daily_volumes = daily_volumes.rolling(window=30).mean()
>>> # look for early volume that is more than twice the average daily volume
>>> volume_surges = early_session_volumes > (avg_daily_volumes.shift() * 2)

Real-time aggregate data

You can use the get_prices function to load data from real-time aggregate databases, just like from history databases. Simply provide a real-time aggregate database code instead of a history database code:

>>> from quantrocket import get_prices
>>> prices = get_prices("fang-stk-tick-1min", start_date="2019-06-06")
>>> prices.head()
ConId                               265598     3691937    15124833   107113386  208813720
Field          Date       Time
LastPriceClose 2019-06-06 09:30:00     183.30    1736.32     354.59     167.84    1039.85
                          09:31:00     182.77    1735.79     353.04     167.72    1037.79
                          09:32:00     183.12    1730.72     352.99     167.41    1035.11
                          09:33:00     183.40    1730.54     353.23     167.79    1035.80
                          09:34:00     183.40    1730.01     353.24     168.16    1036.18

Using get_prices, it is possible to load data from history databases and real-time aggregate databases into the same DataFrame (provided the databases have the same bar size). This allows you (for example) to combine historical data with today's real-time updates:

>>> # query a history db and a real-time aggregate db that use the same universe
>>> prices = get_prices(["fang-stk-1min", # history database
                         "fang-stk-tick-1min"], # real-time aggregate database
                         start_date="2019-06-01",
                         fields=["Close", "LastPriceClose"])

>>> # the history database has a Close field, while the real-time aggregate
>>> # database has a LastClose field
>>> history_closes = prices.loc["Close"]
>>> realtime_closes = prices.loc["LastPriceClose"]

>>> # Use the value from the real-time aggregate db if we have it,
>>> # otherwise from the history db
>>> combined_closes = realtime_closes.fillna(history_closes)

Alphalens

Alphalens is an open source library created by Quantopian for analyzing alpha factors. You can use Alphalens early in your research process to determine if your ideas look promising.

For example, suppose you wanted to analyze the momentum factor, which says that recent winners tend to outperform recent losers. First, load your historical data and extract the closing prices:

>>> prices = get_prices("demo-stocks-1d", start_date="2010-01-01", fields=["Close"])
>>> closes = prices.loc["Close"]

Next, calculate the 12-month returns, skipping the most recent month (as commonly prescribed in academic papers about the momentum factor):

>>> MOMENTUM_WINDOW = 252 # 12 months = 252 trading days
>>> RANKING_PERIOD_GAP = 22 # 1 month = 22 trading days
>>> earlier_closes = closes.shift(MOMENTUM_WINDOW)
>>> later_closes = closes.shift(RANKING_PERIOD_GAP)
>>> returns = (later_closes - earlier_closes) / earlier_closes

The 12-month returns are the predictive factor we will pass to Alphalens, along with pricing data so Alphalens can see whether the factor was in fact predictive. To avoid lookahead bias, in this example we should shift() our factor forward one period to align it with the subsequent prices, since the subsequent prices would represent our entry prices after calculating the factor. Alphalens expects the predictive factor to be stacked into a MultiIndex Series, while pricing data should be a DataFrame:

>>> # shift factor to avoid lookahead bias
>>> returns = returns.shift()
>>> # stack as expected by Alphalens
>>> returns = returns.stack()
>>> factor_data = alphalens.utils.get_clean_factor_and_forward_returns(returns, closes)
>>> alphalens.tears.create_returns_tear_sheet(factor_data)

You'll see tabular statistics as well as graphs that look something like this:

Code reuse in Jupyter

If you find yourself writing the same code again and again, you can factor it out into a .py file in Jupyter and import it into your notebooks and algo files. Any .py files in or under the /codeload directory inside Jupyter (that is, in or under the top-level directory visible in the Jupyter file browser) can be imported using standard Python import syntax. For example, suppose you've implemented a function in /codeload/research/utils.py called analyze_fundamentals. You can import and use the function in another file or notebook:

from codeload.research.utils import analyze_fundamentals

The .py files can live wherever you like in the directory tree; subdirectories can be reached using standard Python dot syntax.

QGrid

QGrid is a Jupyter notebook extension created by Quantopian that provides Excel-like sorting and filtering of DataFrames in Jupyter notebooks. You can use it to explore a DataFrame interactively without writing code. A basic example is shown below:

from quantrocket import get_prices
import qgrid

# Load prices (or any other DataFrame)
prices = get_prices("usa-stk-1d")

# A wide DataFrame with columns for each security will be too wide for the screen
# so reshape to put the fields as columns instead
prices = prices.stack().unstack("Field")

# Construct and display the grid
widget = qgrid.show_grid(prices)
widget

You'll see a grid like this:

After filtering the grid, you can get the edited DataFrame:

prices_edited = widget.get_changed_df()

Moonshot

Moonshot is a fast, vectorized Pandas-based backtester that supports daily or intraday data, multi-strategy backtests and parameter scans, and live trading. It is well-suited for running cross-sectional strategies or screens involving hundreds or even thousands of securities.

What is Moonshot?

Key features

Pandas-based: Moonshot is based on Pandas, the centerpiece of the Python data science stack. If you love Pandas you'll love Moonshot. Moonshot can be thought of as a set of conventions for organizing Pandas code for the purpose of running backtests.

Lightweight: Moonshot is simple and lightweight because it relies on the power and flexibility of Pandas and doesn't attempt to re-create functionality that Pandas can already do. No bloated codebase full of countless indicators and models to import and learn. Most of Moonshot's code is contained in a single Moonshot class.

Fast: Moonshot is fast because Pandas is fast. No event-driven backtester can match Moonshot's speed. Speed promotes alpha discovery by facilitating rapid experimentation and research iteration.

Multi-asset class, multi-time frame: Moonshot supports end-of-day and intraday strategies using equities, futures, and forex.

Machine learning support: Moonshot supports machine learning and deep learning strategies using scikit-learn or Keras.

Live trading: Live trading with Moonshot can be thought of as running a backtest on up-to-date historical data and generating a batch of orders based on the latest signals produced by the backtest.

No black boxes, no magic: Moonshot provides many conveniences to make backtesting easier, but it eschews hidden behaviors and complex, under-the-hood simulation rules that are hard to understand or audit. What you see is what you get.

Vectorized vs event-driven backtesters

What's the difference between event-driven backtesters like Zipline and vectorized backtesters like Moonshot? Event-driven backtests process one event at a time, where an event is usually one historical bar (or in the case of live trading, one real-time quote). Vectorized backtests process all events at once, by performing simultaneous calculations on an entire vector or matrix of data. (In pandas, a Series is a vector and a DataFrame is a matrix).

Imagine a simplistic strategy of buying a security whenever the price falls below $10 and selling whenever it rises above $10. We have a time series of prices and want to know which days to buy and which days to sell. In an event-driven backtester we loop through one date at a time and check the price at each iteration:

>>> data = {
>>>     "2017-02-01": 10.07,
>>>     "2017-02-02": 9.87,
>>>     "2017-02-03": 9.91,
>>>     "2017-02-04": 10.01
>>> }
>>> for date, price in data.items():
>>>     if price < 10:
>>>         buy_signal = True
>>>     else:
>>>         buy_signal = False
>>>     print(date, buy_signal)
2017-02-01 False
2017-02-02 True
2017-02-03 True
2017-02-04 False

In a vectorized backtest, we check all the prices at once to calculate our buy signals:

>>> import pandas as pd
>>> data = {
>>>     "2017-02-01": 10.07,
>>>     "2017-02-02": 9.87,
>>>     "2017-02-03": 9.91,
>>>     "2017-02-04": 10.01
>>> }
>>> prices = pd.Series(data)
>>> buy_signals = prices < 10
>>> buy_signals.head()
2017-02-01    False
2017-02-02     True
2017-02-03     True
2017-02-04    False
dtype: bool

Both backtests produce the same result but use a different approach.

Vectorized backtests are faster than event-driven backtests

Speed is one of the principal benefits of vectorized backtests, thanks to running calculations on an entire time series at once. Event-driven backtests can be prohibitively slow when working with large universes of securities and large amounts of data. Because of their speed, vectorized backtesters support rapid experimentation and testing of new ideas.

Watch out for look-ahead bias with vectorized backtesters

Look-ahead bias refers to making decisions in your backtest based on information that wouldn't have been available at the time of the trade. Because event-driven backtesters only give you one bar at a time, they generally protect you from look-ahead bias. Because a vectorized backtester gives you the entire time-series, it's easier to introduce look-ahead bias by mistake, for example generating signals based on today's close but then calculating the return from today's open instead of tomorrow's.

How does live trading work?

With event-driven backtesters, switching from backtesting to live trading typically involves changing out a historical data feed for a real-time market data feed, and replacing a simulated broker with a real broker connection.

With a vectorized backtester, live trading can be achieved by running an up-to-the-moment backtest and using the final row of signals (that is, today's signals) to generate orders.

Supported types of strategies

The vectorized design of Moonshot is well-suited for cross-sectional and factor-model strategies with regular rebalancing intervals, or for any strategy that "wakes up" at a particular time, checks current and historical market conditions, and makes trading decisions accordingly.

Examples of supported strategies:

• End-of-day strategies
• Intraday strategies that trade once per day at a particular time of day
• Intraday strategies that trade throughout the day
• Cross-sectional and factor-model strategies
• Market neutral strategies
• Seasonal strategies (where "seasonal" might be time of year, day of month, day of week, or time of day)
• Strategies that use fundamental data
• Strategies that screen thousands of stocks using daily data
• Strategies that screen thousands of stocks using 15- or 30-minute intraday data
• Strategies that screen a few hundred stocks using 5-minute intraday data
• Strategies that screen a few stocks using 1-minute intraday data

Examples of unsupported strategies:

• Path-dependent strategies that don't lend themselves to Moonshot's vectorized design

Total data quantity for intraday strategies

Moonshot supports any number of securities and can utilize any bar size, from 1 month down to 1 minute. However, the combination of bar size and the number of securities in your trading universe determines the total data quantity, and there are practical limits on total data quantity. Smaller universes can support higher data frequencies (i.e. smaller bar sizes); larger universes require lower data frequencies (i.e. larger bar sizes).

In backtesting, the practical limit on total data quantity is imposed by the amount of time it takes to initially collect intraday historical data from IB. See the usage guide for more details on the practicalities of historical data collection.

In live trading, the practical limit on data quantity is imposed by the amount of time it takes to updating your historical database before live trading. Updating a database with fewer securities is faster than updating a database with many securities. Consequently small universes can be traded using higher data frequencies (smaller bar sizes) than large universes.

To give a few rough examples (these are conservative estimates, not hard limits), you should have no problem using 1-minute bars with a universe of 10 securities, 3-minute bars with a universe of 50 securities, and 15-minute bars with a universes of 1000 securities. 1-minute bars with a universe of 6,000 securities probably won't work because the data quantity will be prohibitive to collect, store, and work with.

Backtesting

Backtesting quickstart

Let's design a dual moving average strategy which buys tech stocks when their short moving average is above their long moving average. Assume we've already created a history database of daily bars for several tech stocks, like so:

$ # get the tech stock listings...
$ quantrocket master collect --exchanges 'NASDAQ' --symbols 'GOOGL' 'NFLX' 'AAPL' 'AMZN'
status: the listing details will be collected asynchronously
$ # monitor flightlog for listing details to be collected, then make a universe:
$ quantrocket master get -e 'NASDAQ' -s 'GOOGL' 'NFLX' 'AAPL' 'AMZN' | quantrocket master universe 'tech-giants' -f -
code: tech-giants
inserted: 4
provided: 4
total_after_insert: 4
$ # get 1 day bars for the stocks
$ quantrocket history create-db 'tech-giants-1d' -u 'tech-giants' --bar-size '1 day'
status: successfully created quantrocket.history.tech-giants-1d.sqlite
$ quantrocket history collect 'tech-giants-1d'
status: the historical data will be collected asynchronously

Now let's write the minimal strategy code to run a backtest:

from moonshot import Moonshot

class DualMovingAverageStrategy(Moonshot):

    CODE = "dma-tech"
    DB = "tech-giants-1d"
    LMAVG_WINDOW = 300
    SMAVG_WINDOW = 100

    def prices_to_signals(self, prices):
        closes = prices.loc["Close"]

        # Compute long and short moving averages
        lmavgs = closes.rolling(self.LMAVG_WINDOW).mean()
        smavgs = closes.rolling(self.SMAVG_WINDOW).mean()

        # Go long when short moving average is above long moving average
        signals = smavgs.shift() > lmavgs.shift()

        return signals.astype(int)

A strategy is a subclass of the Moonshot class. You implement your trading logic in the class methods and store your strategy parameters as class attributes. Class attributes include built-in Moonshot parameters which you can specify or override, as well as your own custom parameters. In the above example, CODE and DB are built-in parameters while LMAVG_WINDOW and SMAVG_WINDOW are custom parameters which we've chosen to store as class attributes, which will allow us to run parameter scans or create similar strategies with different parameters.

Place your code in a file inside the 'moonshot' directory in JupyterLab. QuantRocket recursively scans .py files in this directory and loads your strategies.

You can run backtests via the command line or inside a Jupyter notebook, and you can get back a CSV of backtest results or a tear sheet with performance plots.

$ quantrocket moonshot backtest 'dma-tech' -s '2005-01-01' -e '2017-01-01' --pdf -o dma_tech_tearsheet.pdf --details

>>> from quantrocket.moonshot import backtest
>>> from moonchart import Tearsheet
>>> backtest("dma-tech", start_date="2005-01-01", end_date="2017-01-01",
             filepath_or_buffer="dma_tech.csv")
>>> Tearsheet.from_moonshot_csv("dma_tech.csv")

$ curl -X POST 'http://houston/moonshot/backtests?strategies=dma-tech&start_date=2005-01-01&end_date=2017-01-01&pdf=true' > dma_tech_tearsheet.pdf

The performance plots will resemble the following:

Backtest visualization and analysis in Jupyter

In addition to running backtests from the CLI, you can run backtests from a Jupyter notebook and perform analysis and visualizations inside the notebook. First, run the backtest and save the results to a CSV:

>>> from quantrocket.moonshot import backtest
>>> backtest("dma-tech", start_date="2005-01-01", end_date="2017-01-01",
        filepath_or_buffer="dma_tech_results.csv")

You can do four main things with the CSV results:

1. generate a performance tear sheet using Moonchart, an open source companion library to Moonshot;
2. generate a performance tear sheet using pyfolio, an open source library created by Quantopian;
3. use Moonchart to get a DailyPerformance object and create your own plots; and
4. load the results into a Pandas DataFrame for further analysis.

Moonchart tear sheet

To look at a Moonchart tear sheet:

>>> from moonchart import Tearsheet
>>> Tearsheet.from_moonshot_csv("dma_tech_results.csv")

pyfolio tear sheet

To look at a pyfolio tear sheet:

>>> import pyfolio as pf
>>> pf.from_moonshot_csv("dma_tech_results.csv")

Moonchart and pyfolio offer somewhat different visualizations so it's nice to look at both.

Custom plots with Moonchart

For finer-grained control with Moonchart or for times when you don't want a full tear sheet, you can instantiate a DailyPerformance object and create your own individual plots:

>>> from moonchart import DailyPerformance
>>> perf = DailyPerformance.from_moonshot_csv("dma_tech_results.csv")
>>> perf.cum_returns.tail()
             AAPL(265598)  AMZN(3691937)  NFLX(15124833)  GOOGL(208813719)
Date
2016-12-23      1.886121       2.456078        2.094612          1.656564
2016-12-27      1.889116       2.464804        2.106120          1.657656
2016-12-28      1.887102       2.465388        2.096028          1.654913
2016-12-29      1.886981       2.459816        2.093697          1.654044
2016-12-30      1.883303       2.447535        2.087307          1.648672
>>> perf.cum_returns.plot()

You can use the DailyPerformance object to construct an AggregateDailyPerformance object representing aggregated backtest results:

>>> from moonchart import AggregateDailyPerformance
>>> agg_perf = AggregateDailyPerformance(perf)
>>> agg_perf.cum_returns.tail()
Date
2016-12-23    13.610334
2016-12-27    13.764051
2016-12-28    13.663911
2016-12-29    13.609782
2016-12-30    13.429575
>>> agg_perf.cum_returns.plot()

See Moonchart reference for available performance attributes.

Raw backtest results analysis

You can also load the backtest results into a DataFrame:

>>> from quantrocket.moonshot import read_moonshot_csv
>>> results = read_moonshot_csv("dma_tech_results.csv")
>>> results.tail()
                   AAPL(265598)  AMZN(3691937)  NFLX(15124833)  GOOGL(208813719)
Field  Date
Weight 2016-12-23          0.25           0.25            0.25              0.25
       2016-12-27          0.25           0.25            0.25              0.25
       2016-12-28          0.25           0.25            0.25              0.25
       2016-12-29          0.25           0.25            0.25              0.25
       2016-12-30          0.25           0.25            0.25              0.25

The DataFrame consists of several stacked DataFrames, one DataFrame per field (see backtest field reference). Use .loc to isolate a particular field:

>>> returns = results.loc["Return"]
>>> returns.tail()
            AAPL(265598)  AMZN(3691937)  NFLX(15124833)  GOOGL(208813719)
Date
2016-12-23      0.000494      -0.001876        0.000020         -0.000580
2016-12-27      0.001588       0.003553        0.005494          0.000659
2016-12-28     -0.001066       0.000237       -0.004792         -0.001654
2016-12-29     -0.000064      -0.002260       -0.001112         -0.000525
2016-12-30     -0.001949      -0.004992       -0.003052         -0.003248

Since we specified details=True when running the backtest, there is a column per security. Had we omitted details=True, or if we were running a multi-strategy backtest, there would be a column per strategy.

How a Moonshot backtest works

Moonshot is all about DataFrames. In a Moonshot backtest, we start with a DataFrame of historical prices and derive a variety of equivalently-indexed DataFrames, including DataFrames of signals, trade allocations, positions, and returns. These DataFrames consist of a time-series index (vertical axis) with one or more securities as columns (horizontal axis). A simple example of a DataFrame of signals is shown below for a strategy with a 2-security universe (securities are identified by conid):

ConId       12345  67890
Date
2017-09-19      0     -1
2017-09-20      1     -1
2017-09-21      1      0

A Moonshot strategy consists of strategy parameters (stored as class attributes) and strategy logic (implemented in class methods). The strategy logic required to run a backtest is spread across four main methods, mirroring the stages of a trade:

Since Moonshot is a vectorized backtester, each of these methods is called only once per backtest.

Our demo strategy above relies on the default implementations of several of these methods, but since it's better to be explicit than implicit, you should always implement these methods even if you copy the default behavior. Let's explicitly implement the default behavior in our demo strategy:

from moonshot import Moonshot

class DualMovingAverageStrategy(Moonshot):

    CODE = "dma-tech"
    DB = "tech-giants-1d"
    LMAVG_WINDOW = 300
    SMAVG_WINDOW = 100

    def prices_to_signals(self, prices):
        closes = prices.loc["Close"]

        # Compute long and short moving averages
        lmavgs = closes.rolling(self.LMAVG_WINDOW).mean()
        smavgs = closes.rolling(self.SMAVG_WINDOW).mean()

        # Go long when short moving average is above long moving average
        signals = smavgs.shift() > lmavgs.shift()

        return signals.astype(int)

    def signals_to_target_weights(self, signals, prices):
        # spread our capital equally among our trades on any given day
        weights = self.allocate_equal_weights(signals) # provided by moonshot.mixins.WeightAllocationMixin
        return weights

    def target_weights_to_positions(self, weights, prices):
        # we'll enter in the period after the signal
        positions = weights.shift()
        return positions

    def positions_to_gross_returns(self, positions, prices):
        # Our return is the security's close-to-close return, multiplied by
        # the size of our position. We must shift the positions DataFrame because
        # we don't have a return until the period after we open the position
        closes = prices.loc["Close"]
        gross_returns = closes.pct_change() * positions.shift()
        return gross_returns

To summarize the above code, we generate signals based on moving average crossovers, we divide our capital equally among the securities with signals, we enter the positions the next day, and compute our (gross) returns using the securities' close-to-close returns.

Several weight allocation algorithms are provided out of the box via moonshot.mixins.WeightAllocationMixin.

Benchmarks

Optionally, we can identify a benchmark security and get a plot of the strategy's performance against the benchmark. The benchmark can exist within the same database used by the strategy, or a different database. Our ETF strategy universe includes SPY, so let's make that our benchmark. First, lookup the conid (contract ID) if needed, since that's how we specify the benchmark:

$ quantrocket master get -e ARCA -s SPY -f ConId -p
ConId = 756733

Now set this conid as the benchmark:

class DualMovingAverageStrategyETF(DualMovingAverageStrategy):

    CODE = "dma-etf"
    DB = "etf-sampler-1d"
    LMAVG_WINDOW = 300
    SMAVG_WINDOW = 100
    BENCHMARK = 756733 # exists within DB

Run the backtest again, and we'll see an additional chart in our tear sheet:

To use a benchmark security from a different database, specify a BENCHMARK_DB:

class MeanReversionStrategy(Moonshot):

    CODE = "mean-revert-us"
    DB = "usa-stk-1d"
    BENCHMARK = 756733
    BENCHMARK_DB = "etf-sampler-1d"

Multi-strategy backtests

We can easily backtest multiple strategies at once to simulate running complex portfolios of strategies. Simply specify all of the strategies:

$ quantrocket moonshot backtest 'dma-tech' 'dma-etf' -s '2005-01-01' -e '2017-01-01' --pdf -o dma_multistrat.pdf

>>> from quantrocket.moonshot import backtest
>>> from moonchart import Tearsheet
>>> backtest(["dma-tech", "dma-etf"], start_date="2005-01-01", end_date="2017-01-01",
             filepath_or_buffer="dma_multistrat.csv")
>>> Tearsheet.from_moonshot_csv("dma_multistrat.csv")

$ curl -X POST 'http://houston/moonshot/backtests?strategies=dma-etf&strategies=dma-tech&start_date=2005-01-01&end_date=2017-01-01&pdf=true' > dma_multistrat.pdf

Our tear sheet will show the aggregate portfolio performance as well as the individual strategy performance:

By default, when backtesting multiple strategies, capital is divided equally among the strategies; that is, each strategy's allocation is 1.0 / number of strategies. If this isn't what you want, you can specify custom allocations for each strategy (which need not add up to 1):

$ # allocate 125% of capital to dma-tech and another 25% to dma-etf
$ quantrocket moonshot backtest 'dma-tech' 'dma-etf' --allocations 'dma-tech:1.25' 'dma-etf:0.25' -s '2005-01-01' -e '2017-01-01' --pdf -o dma_multistrat.pdf

>>> from quantrocket.moonshot import backtest
>>> # allocate 125% of capital to dma-tech and another 25% to dma-etf
>>> backtest(["dma-tech", "dma-etf"],
             allocations={"dma-tech": 1.25, "dma-etf": 0.25},
             start_date="2005-01-01", end_date="2017-01-01",
             filepath_or_buffer="dma_multistrat.csv")

$ # allocate 125% of capital to dma-tech and another 25% to dma-etf
$ curl -X POST 'http://houston/moonshot/backtests?strategies=dma-etf&strategies=dma-tech&start_date=2005-01-01&end_date=2017-01-01&allocations=dma-tech%3A1.25&allocations=dma-etf%3A0.25&pdf=true' > dma_multistrat.pdf

On-the-fly parameters

You can change Moonshot parameters on-the-fly from the Python client or CLI when running backtests, without having to edit your .py algo files. Pass parameters as KEY:VALUE pairs:

$ # disable commissions for this backtest
$ quantrocket moonshot backtest 'dma-tech' -o dma_tech_no_commissions.csv --params 'COMMISSION_CLASS:None'

>>> # disable commissions for this backtest
>>> backtest("dma-tech", filepath_or_buffer="dma_tech_no_commissions.csv",
             params={"COMMISSION_CLASS":None})

$ # disable commissions for this backtest
$ curl -X POST 'http://houston/moonshot/backtests?strategies=dma-tech&params=COMMISSION_CLASS%3ANone' > dma_tech_no_commissions.csv