Query account balances.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json or csv, default is csv)

• start_date (str (YYYY-MM-DD), optional) – limit to account balance snapshots taken on or after this date

• end_date (str (YYYY-MM-DD), optional) – limit to account balance snapshots taken on or before this date

• latest (bool) – return the latest account balance snapshot

• accounts (list of str, optional) – limit to these accounts

• below (dict of FIELD:AMOUNT, optional) – limit to accounts where the specified field is below the specified amount (pass as {field:amount}, for example {‘Cushion’:0.05})

• fields (list of str, optional) – only return these fields. By default a core set of fields is returned. Pass a list of fields, or ‘*’ to return all fields. Pass [‘?’] or any invalid fieldname to see available fields.

• force_refresh (bool) – refresh account balances to ensure the latest data (default is to query the database, which is refreshed every minute)

Return type:

None

Examples

Query latest balances. You can use StringIO to load the CSV into pandas.

>>> f = io.StringIO()
>>> download_account_balances(f, latest=True)
>>> balances = pd.read_csv(f, parse_dates=["LastUpdated"])

Download current portfolio.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json or csv, default is csv)

• brokers (list of str, optional) – limit to these brokers. Possible choices: alpaca, ibkr

• accounts (list of str, optional) – limit to these accounts

• sec_types (list of str, optional) – limit to these security types

• exchanges (list of str, optional) – limit to these exchanges

• sids (list of str, optional) – limit to these sids

• symbols (list of str, optional) – limit to these symbols

• include_zero (bool) – include zero position rows (default is to exclude them). Only supported for Interactive Brokers.

• fields (list of str, optional) – only return these fields. By default a core set of fields is returned. Pass a list of fields, or ‘*’ to return all fields. Pass ‘?’ or any invalid fieldname to see available fields.

Return type:

None

Examples

Download current portfolio. You can use StringIO to load the CSV into pandas.

>>> f = io.StringIO()
>>> download_account_portfolio(f)
>>> portfolio = pd.read_csv(f, parse_dates=["LastUpdated"])

Query exchange rates for the base currency.

The exchange rates in the exchange rate database are sourced from the European Central Bank’s reference rates, which are updated each day at 4 PM CET.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json, csv, default is csv)

• start_date (str (YYYY-MM-DD), optional) – limit to exchange rates on or after this date

• end_date (str (YYYY-MM-DD), optional) – limit to exchange rates on or before this date

• latest (bool) – return the latest exchange rates

• base_currencies (list of str, optional) – limit to these base currencies

• quote_currencies (list of str, optional) – limit to these quote currencies

Return type:

None

Examples

Query latest exchange rates. You can use StringIO to load the CSV into pandas.

>>> f = io.StringIO()
>>> download_exchange_rates(f, latest=True)
>>> rates = pd.read_csv(f, parse_dates=["Date"])

Apply a stock split to an open position.

This endpoint does not interact with the broker but simply applies the split in the blotter database to bring the blotter in line with the broker. The split is also applied to the executions that created the open position, so that PNL calculations will be accurate.

The old_shares and new_shares parameters can be specified either using the published split ratio (for example, 2-for-1) or the actual number of pre- and post-split shares in your account.

Parameters:

• sid (str, required) – the sid that underwent a split. There must currently be an open position in this security.

• old_shares (int, required) – the number of pre-split shares

• new_shares (int, required) – the number of post-split shares

Returns:

the old and new position for this sid, by account and order ref

Return type:

list

Examples

Record a 2-for-1 split:

>>> record_split("FIBBG12345", old_shares=1, new_shares=2)

Record a 1-for-10 reverse split:

>>> record_split("FIBBG98765", old_shares=10, new_shares=1)

Cancel one or more orders by order ID, sid, or order ref.

Parameters:

• order_ids (list of str, optional) – cancel these order IDs

• sids (list of str, optional) – cancel orders for these sids

• order_refs (list of str, optional) – cancel orders for these order refs

• accounts (list of str, optional) – cancel orders for these accounts

• cancel_all (bool) – cancel all open orders

Returns:

status message

Return type:

dict

Examples

Cancel orders by order ID:

>>> cancel_orders(order_ids=['6002:45','6002:46'])

Cancel orders by sid:

>>> cancel_orders(sids=["FIBBG123456"])

Cancel orders by order ref:

>>> cancel_orders(order_refs=['my-strategy'])

Cancel all open orders:

>>> cancel_orders(cancel_all=True)

Generate orders to close positions.

Doesn’t actually place any orders but returns an orders file that can be placed separately. Additional order parameters can be appended with the params argument.

This endpoint can also be used to generate executions for marking a position as closed due to a tender offer, merger/acquisition, etc. (See quantrocket.blotter.record_executions for more info.)

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json or csv, default is csv)

• order_refs (list of str, optional) – limit to these order refs

• accounts (list of str, optional) – limit to these accounts

• sids (list of str, optional) – limit to these sids

• params (dict of PARAM:VALUE, optional) – additional parameters to append to each row in output (pass as {param:value}, for example {“OrderType”:”MKT”})

Return type:

None

Examples

Get orders to close positions, then place the orders:

>>> from quantrocket.blotter import place_orders, close_positions
>>> import io
>>> orders_file = io.StringIO()
>>> close_positions(orders_file, params={"OrderType":"MKT", "Tif":"DAY", "Exchange":"SMART"})
>>> place_orders(infilepath_or_buffer=orders_file)

After receiving 23.50 per share in a tender offer for a position, record the execution in the blotter in order to mark the position as closed:

>>> from quantrocket.blotter import record_executions
>>> executions_file = io.StringIO()
>>> close_positions(executions_file, sids="FIBBG123456", params={"Price": 23.50})
>>> record_executions(infilepath_or_buffer=executions_file)

Query executions from the executions database.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• order_refs (list of str, optional) – limit to these order refs

• accounts (list of str, optional) – limit to these accounts

• sids (list of str, optional) – limit to these sids

• start_date (str (YYYY-MM-DD), optional) – limit to executions on or after this date

• end_date (str (YYYY-MM-DD), optional) – limit to executions on or before this date

Return type:

None

Download order statuses.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json or csv, default is csv)

• order_ids (list of str, optional) – limit to these order IDs

• sids (list of str, optional) – limit to orders for these sids

• order_refs (list of str, optional) – limit to orders for these order refs

• accounts (list of str, optional) – limit to orders for these accounts

• open_orders (bool) – limit to open orders

• start_date (str (YYYY-MM-DD), optional) – limit to orders submitted on or after this date

• end_date (str (YYYY-MM-DD), optional) – limit to orders submitted on or before this date

• fields (list of str, optional) – return these fields in addition to the default fields (pass ‘?’ or any invalid fieldname to see available fields)

Return type:

None

Examples

Download order status by order ID and load into Pandas:

>>> f = io.StringIO()
>>> download_order_statuses(f, order_ids=['6001:45','6001:46'])
>>> order_statuses = pd.read_csv(f)

Download order status for all open orders and include extra fields in output:

>>> download_order_statuses(open_orders=True, fields=["LmtPrice", "OcaGroup"])

Download order status of open orders by sid:

>>> download_order_statuses(sids=["FIBBG123456"], open_orders=True)

Download order status of open orders by order ref:

>>> download_order_statuses(order_refs=['my-strategy'], open_orders=True)

Query trading performance and return a CSV of results or PDF tearsheet.

Trading performance is broken down by account and order ref and optionally by sid.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• order_refs (list of str, optional) – limit to these order refs

• accounts (list of str, optional) – limit to these accounts

• sids (list of str, optional) – limit to these sids

• start_date (str (YYYY-MM-DD), optional) – limit to pnl on or after this date

• end_date (str (YYYY-MM-DD), optional) – limit to pnl on or before this date

• details (bool) – return detailed results for all securities instead of aggregating to account/order ref level (only supported for a single account and order ref at a time)

• timezone (str, optional) – return execution times in this timezone (default UTC)

• output (str, required) – the output format (choices are csv or pdf, default is csv)

Return type:

None

Query current positions and write results to file.

To return positions as a Python list, see list_positions.

There are two ways to view positions: blotter view (default) and broker view.

The default “blotter view” returns positions by account, sid, and order ref. Positions are tracked based on execution records saved to the blotter database.

“Broker view” (view=’broker’) returns positions by account and sid (but not order ref) as reported directly by the broker.

Parameters:

• filepath_or_buffer (str or file-like object) – filepath to write the data to, or file-like object (defaults to stdout)

• output (str) – output format (json or csv, default is csv)

• order_refs (list of str, optional) – limit to these order refs (not supported with broker view)

• accounts (list of str, optional) – limit to these accounts

• sids (list of str, optional) – limit to these sids

• view (str, optional) – whether to return ‘broker’ view of positions (by account and sid) or default ‘blotter’ view (by account, sid, and order ref). Choices are: blotter, broker

• diff (bool) – limit to positions where the blotter quantity and broker quantity disagree (requires view=’broker’)

Return type:

None

Query current positions and return them as a Python list.

There are two ways to view positions: blotter view (default) and broker view.

The default “blotter view” returns positions by account, sid, and order ref. Positions are tracked based on execution records saved to the blotter database.

“Broker view” (view=’broker’) returns positions by account and sid (but not order ref) as reported directly by the broker.

Parameters:

• order_refs (list of str, optional) – limit to these order refs (not supported with broker view)

• accounts (list of str, optional) – limit to these accounts

• sids (list of str, optional) – limit to these sids

• view (str, optional) – whether to return ‘broker’ view of positions (by account and sid) or default ‘blotter’ view (by account, sid, and order ref). Choices are: blotter, broker

• diff (bool) – limit to positions where the blotter quantity and broker quantity disagree (requires view=’broker’)

Return type:

list

Examples

Query current positions and load into Pandas:

>>> positions = list_positions()
>>> if positions:
>>>     positions = pd.DataFrame(positions)

Place one or more orders.

Returns a list of order IDs, which can be used to cancel the orders or check their status.

Parameters:

• orders (list of dict of PARAM:VALUE, optional) – a list of one or more orders, where each order is a dict specifying the order parameters (see examples)

• infilepath_or_buffer (str or file-like object, optional) – place orders from this CSV or JSON file (specify ‘-’ to read file from stdin). Mutually exclusive with orders argument.

Returns:

order IDs

Return type:

list

Examples

>>> orders = []
>>> order1 = {
        'Sid':'FIBBG123456',
        'Action':'BUY',
        'Exchange':'SMART',
        'TotalQuantity':100,
        'OrderType':'MKT',
        'Tif':'Day',
        'Account':'DU12345',
        'OrderRef':'my-strategy'
    }
>>> orders.append(order1)
>>> order_ids = place_orders(orders)

Load a PNL CSV into a DataFrame.

This is a light wrapper around pd.read_csv that handles setting index columns and casting to proper data types.

Parameters:

filepath_or_buffer (string or file-like, required) – path to CSV

Returns:

a multi-index (Field, Date[, Time]) DataFrame of backtest results, with sids or strategy codes as columns

Return type:

DataFrame

Examples

>>> results = read_pnl_csv("pnl.csv")
>>> returns = results.loc["Return"]

Record executions that happened outside of QuantRocket’s knowledge.

This endpoint does not interact with the broker but simply adds one or more executions to the blotter database and updates the blotter’s record of current positions accordingly. It can be used to bring the blotter in line with the broker when they differ. For example, when a position is liquidated because of a tender offer or merger/acquisition, you can use this endpoint to record the price received for your shares.

Parameters:

• executions (list of dict of PARAM:VALUE, optional) –

  a list of one or more executions, where each execution is a dict specifying the execution parameters. The required params are:

  • Account

  • Action (“BUY” or “SELL”)

  • OrderRef

  • Price

  • Sid

  • TotalQuantity

  Optional params (rarely needed):

  • Commission (default is 0)

  • OrderId (default is an auto-generated ID)

  • Time (the time of execution, default is now)

• infilepath_or_buffer (str or file-like object, optional) – record executions from this CSV or JSON file (specify ‘-’ to read file from stdin). Mutually exclusive with executions argument.

Returns:

a list of execution IDs generated by the blotter and inserted in the database

Return type:

list

Examples

>>> executions = []
>>> execution1 = {
        'Sid':'FIBBG123456',
        'Action':'BUY',
        'TotalQuantity':100,
        'Account':'DU12345',
        'OrderRef':'my-strategy',
        'Price': 23.50
    }
>>> executions.append(execution1)
>>> execution_ids = record_executions(executions)

Clone files from a Git repository.

Only the files are copied, not the Git metadata. Can be run multiple times to clone files from multiple repositories. Won’t overwrite any existing files unless replace=True.

Parameters:

• repo (str, required) – the name or URL of the repo. Can be the name of a QuantRocket demo repo (e.g. ‘umd’), a GitHub username/repo (e.g. ‘myuser/myrepo’), or the URL of any Git repository

• branch (str, optional) – the branch to clone (default ‘master’)

• replace (bool, optional) – if a file already exists locally, replace it with the remote file (mutually exclusive with skip_existing)

• skip_existing (bool, optional) – if a file already exists locally, skip it (mutually exclusive with replace)

• target_dir (str, optional) – the directory into which files should be cloned. Default is ‘/codeload’

Returns:

status message

Return type:

dict

Examples

Clone QuantRocket’s “umd” demo repository:

>>> clone("umd")

Clone a GitHub repo and skip files that already exist locally:

>>> clone("myuser/myrepo", skip_existing=True)

Clone a Bitbucket repo:

>>> clone("https://bitbucket.org/myuser/myrepo.git")

Clone a private repo by including username and app password (Bitbucket) or personal access token (GitHub) in the URL:

>>> clone("https://myuser:myapppassword@bitbucket.org/myuser/myrepo.git")

Return the current crontab.

Parameters:

service (str, optional) – the name of the countdown service (default ‘countdown’)

Returns:

string representation of crontab

Return type:

str

Return the service timezone.

Parameters:

service (str, optional) – the name of the countdown service (default ‘countdown’)

Returns:

dict with key timezone

Return type:

dict

Upload a new crontab.

Parameters:

• filename (str, required) – the crontab file to upload to the countdown service

• service (str, optional) – the name of the countdown service (default ‘countdown’)

Returns:

status message

Return type:

dict

Set the countdown service timezone.

Parameters:

• tz (str, required) – the timezone to set (pass a partial timezone string such as ‘newyork’ or ‘europe’ to see close matches, or pass ‘?’ to see all choices)

• service (str, optional) – the name of the countdown service (default ‘countdown’)

Returns:

status message

Return type:

dict

Examples

Set the countdown timezone to America/New_York:

>>> set_timezone("America/New_York")

Returns a connection to a SQLite database.

Parameters:

db_path (str, required) – full path to a SQLite database

Returns:

database connection

Return type:

sqlalchemy.engine.Engine

Return the current S3 configuration, if any.

See http://qrok.it/h/dbs3 to learn more.

Returns:

configuration details

Return type:

dict

Insert a DataFrame into a SQLite database.

In the case of a duplicate record insertion, the function will fail.

Parameters:

• df (DataFrame, required) – the DataFrame to insert. All DataFrame columns must exist in the destination table. The DataFrame index will not be inserted.

• table_name (str, required) – the name of the table to insert the DataFrame into. The table must already exist in the database.

• conn (sqlalchemy.engine.Engine, required) – a connection object for the SQLite database

Return type:

None

Raises:

quantrocket.exceptions.DataInsertionError – catch-all exception class for errors that occur when writing to the SQLite database

Insert a DataFrame into a SQLite database.

In the case of a duplicate record insertion, the incoming record will be ignored.

Parameters:

• df (DataFrame, required) – the DataFrame to insert. All DataFrame columns must exist in the destination table. The DataFrame index will not be inserted.

• table_name (str, required) – the name of the table to insert the DataFrame into. The table must already exist in the database.

• conn (sqlalchemy.engine.Engine, required) – a connection object for the SQLite database

Return type:

None

Raises:

quantrocket.exceptions.DataInsertionError – catch-all exception class for errors that occur when writing to the SQLite database

Insert a DataFrame into a SQLite database.

In the case of a duplicate record insertion, the incoming record will replace the existing record.

Parameters:

• df (DataFrame, required) – the DataFrame to insert. All DataFrame columns must exist in the destination table. The DataFrame index will not be inserted.

• table_name (str, required) – the name of the table to insert the DataFrame into. The table must already exist in the database.

• conn (sqlalchemy.engine.Engine, required) – a connection object for the SQLite database

Return type:

None

Raises:

quantrocket.exceptions.DataInsertionError – catch-all exception class for errors that occur when writing to the SQLite database

List databases.

Parameters:

• services (str, optional) – limit to these services

• codes (list of str, optional) – limit to these codes

• detail (bool) – return database statistics (default is to return a flat list of database names)

• expand (bool) – expand sharded databases to include individual shards (default is to list sharded databases as a single database)

Returns:

dict of lists of databases (one key for PostgreSQL databases and one for SQLite databases)

Return type:

dict

Examples

Load database details in a pandas DataFrame:

>>> from quantrocket.db import list_databases
>>> import itertools
>>> databases = list_databases(detail=True)
>>> databases = pd.DataFrame.from_records(itertools.chain(databases["sqlite"], databases["postgres"]))

Optimize databases to improve performance.

This runs the ‘VACUUM’ command, which defragments the database and reclaims disk space.

Parameters:

• serivces (list of str, optional) – limit to these service

• codes (list of str, optional) – limit to these codes

Returns:

status message

Return type:

json

Pull database(s) from Amazon S3.

See http://qrok.it/h/dbs3 to learn more.

Parameters:

• serivces (list of str, optional) – limit to these services

• codes (list of str, optional) – limit to these codes

• force (bool) – overwrite existing database if one exists (default is to fail if one exists)

Returns:

status message

Return type:

json

Push database(s) to Amazon S3.

See http://qrok.it/h/dbs3 to learn more.

Parameters:

• serivces (list of str, optional) – limit to these services

• codes (list of str, optional) – limit to these codes

Returns:

status message

Return type:

json

Set AWS S3 configuration for pushing and pulling databases to and from S3.

See http://qrok.it/h/dbs3 to learn more.

Credentials are encrypted at rest and never leave your deployment.

Parameters:

• access_key_id (str, optional) – AWS access key ID

• secret_access_key (str, optional) – AWS secret access key (if omitted and access_key_id is provided, will be prompted for secret_access_key)

• bucket (str, optional) – the S3 bucket name to push to/pull from

• region (str, optional) – the AWS region in which to create the bucket (default us-east-1). Ignored if the bucket already exists.

Returns:

status message

Return type:

dict

Returns a log handler that logs to flightlog.

Parameters:

background (bool) – If True, causes logging to happen in a background thread so that logging doesn’t block. Background logging requires Python 3.2 or higher, and defaults to True for supported versions and False otherwise.

Return type:

logging.handlers.QueueHandler or quantrocket.flightlog._ImpatientHttpHandler

Examples

Log a message using the FlightlogHandler:

>>> import logging
>>> from quantrocket.flightlog import FlightlogHandler
>>> logger = logging.getLogger('myapp')
>>> logger.setLevel(logging.DEBUG)
>>> handler = FlightlogHandler()
>>> logger.addHandler(handler)
>>> logger.info('my app just opened a position')

Download the logfile.

Parameters:

• outfile (str or file-like object, required) – filename or file object to write the logfile to

• detail (bool) – download detailed logs from the logspout service, otherwise download the standard logs from the flightlog service

• match (str, optional) – filter the logfile to lines containing this string

Return type:

None

Return the current Papertrail log configuration, if any.

See http://qrok.it/h/pt to learn more.

Returns:

config details

Return type:

dict

Return the flightlog timezone.

Returns:

dict with key timezone

Return type:

dict

Set the Papertrail log configuration.

See http://qrok.it/h/pt to learn more.

Parameters:

• host (str, required) – the Papertrail host to log to

• port (int, required) – the Papertrail port to log to

Returns:

status message

Return type:

dict

Examples

Configure flightlog to log to Papertrail:

>>> set_papertrail_config("logs.papertrailapp.com", 55555)

Set the flightlog timezone.

Parameters:

tz (str, required) – the timezone to set (pass a partial timezone string such as ‘newyork’ or ‘europe’ to see close matches, or pass ‘?’ to see all choices)

Returns:

status message

Return type:

dict

Examples

Set the flightlog timezone to America/New_York:

>>> set_timezone("America/New_York")

Stream application logs, tail -f style.

Parameters:

• detail (bool) – if True, show detailed logs from logspout, otherwise show log messages from flightlog only (default False)

• hist (int, optional) – number of log lines to show right away (ignored if showing detailed logs)

• color (bool) – colorize the logs

Yields:

str – each log line as it arrives

Wait for a message to appear in the logs.

Searches can be performed against the standard or detailed log file. When searching the detailed logs, note that the log file uses the syslog format, which differs from the format used when streaming detailed logs. Download the detailed log file to see the exact format your search will run against.

Parameters:

• message (str, required) – the log message to search for

• regex (bool) – if True, treat the message argument as a regular expression (default is to treat it as a plain string)

• detail (bool, optional) – if True, search the detailed logs from the logspout service (default is to search the standard logs from the flightlog service)

• tail (int, optional) – search the most recent N lines of the logs in addition to searching future logs (default is to only search future logs)

• timeout (str, optional) – fail if the message is not found after this much time (use Pandas timedelta string, e.g. 30sec or 5min or 2h; default is to wait indefinitely)

Returns:

status dict containing the matching log line

Return type:

dict

Examples

Wait up to 10 minutes for a message to appear indicating that data ingestion has finished:

>>> wait_for_message('[usstock-1min] Completed ingesting data', timeout='10m')

Using a regular expression, wait up to 1 hour for a message to appear indicating that data collection has finished:

>>> wait_for_message(r'\[usstock-1d\] Collected [0-9]+ monthly files', regex=True, timeout='1h')