US Equity Daily Cumulative Adjustment Factors Guide

version 1.2 (Jul 2021)

CONTACT US

We are here to help you do great things with our market and reference data. For questions, feedback, and other concerns, you may reach our team of experts using the following contact information:

algoseek customer support

support@algoseek.com

(+1) 646 583 1832

algoseek sales

sales@algoseek.com

(+1) 646 583 1832

TABLE OF CONTENTS

INTRODUCTION        4

DATA ORGANIZATION AND FILE FORMAT        4

HOW TO APPLY DAILY CUMULATIVE ADJUSTMENT FACTORS        7

DAILY UPDATES        8

APPENDIX A. FREQUENTLY ASKED QUESTIONS        11

INTRODUCTION

The Daily Cumulative Adjustment Factors dataset provides cumulative adjustments for US equities calculated with the backward approach. The dataset also contains information about corporate event types affecting price and/or volume for historical backtesting.

algoseek uses a unique identifier called Security ID (SecId) for each equity, including stocks, ETFs, ETNs, ADRs, preferred stocks, and stock warrants among others.  The SecId remains unchanged when a security’s ticker name changes.

For each SecId, this dataset provides cumulative adjustment factors and event types for the creation of backward adjusted pricing and/or volume for any date from 2007 to the present.

To find the SecId for a combination of exchange ticker and trade date, use the algoseek Lookup File. Please contact your account manager if you don’t have the access to Lookup File.

The dataset is continuous in terms of trade dates. It means that data files contain all trade dates and effective dates when a corporate event has happened and provide corresponding cumulative adjustment factors for price and volume. algoseek rebuilds data daily for each security when it has a new corporate event or adds a new trade date otherwise. Please refer to the “Daily Updates” section to find more details about updates.

DATA ORGANIZATION AND FILE FORMAT

algoseek provides Equity adjustment data in plain-text CSV files. Data files have fixed headers and rows of corresponding data (see Table 1). The data is SecId-based aggregated, for example, all adjustments for the security with an ID 33449 (AAPL) are stored in a single CSV file. Also, there is a possibility to get the data separated by year.

Table 1: Sample Daily Cumulative Adjustment Data

Table 2 below summarizes the name, brief description, and data type for each data field (column)  in Equity Cumulative Backward Adjustment Factors CSV file.

Table 2: Daily Cumulative Adjustment Factor Fields and Descriptions

Adjustment Reason

Each adjustment event relates to an Event Type, and each Event Type may include different Adjustment Reasons. The table below describes the different adjustment types and reasons and whether it affects Price and/or Volume.

Table 3: Adjustment Reasons

Note: if SecId (Ticker) doesn’t have any corporate events, the TradeDate field will show the date when security was listed in algoseek’s records, all factors will be equal to 1 (one), and AdjustmentReason and EventType fields will have the value START (see Table 4).

Table 4: Sample Initial Row in Daily Cumulative Adjustment Data

HOW TO APPLY DAILY CUMULATIVE ADJUSTMENT FACTORS

When backtesting the historical “as-is” prices and volumes, they need to be adjusted in order to account for price events like a dividend and volume changes like a split. Use Table 3: Adjustment Reasons to determine if Price and/or Volume (a.k.a. Size) fields need to be adjusted.

The table below shows AAPL cumulative backward adjustments from 05/06/2014 to 12/06/2014.

Table 5: AAPL Corporate Events in 2012-2014

Steps below describe creating backward adjusted (starting from the latest date) time series:

• The Pricing data on a specific date should be multiplied by a corresponding cumulative factor on that date. For example, to adjust the prices on 20140605, you will need to use cumulative factor AF=0.032233144343822 (Table 5) from column “CumulativeFactorPrice”.
• The Volume data on a specific date should be divided by a corresponding cumulative factor on that date. For example, to adjust the volume on 20140605 you will need to use cumulative factor AF=0.0357142857142858 (Table 5) from column “CumulativeFactorVolume”.

Note: When we have more than one corporate event for the same SecId and TradeDate, in the “AdjustmentFactor” column for that date value will be a cumulative factor for all events on that date. The “AdjustmentReason” and the “EventType” columns will be joined by “and” adjustment reasons and event types for all events on that date.

DAILY UPDATES

The dataset contains backward cumulative adjustment factors. Whenever a new corporate event (e.g. dividend, split, etc) is published for a SecId, all data for this SecId will be rebuilt, otherwise, a new trade date will be added. For both cases, you need to re-download the data.

If you have access to the full universe of SecIDs, you can update the data using AWS CLI SYNC command. This setup will download only updated files from S3 bucket. To sync your local directory and S3 bucket, please make sure to point the SYNC command to your local copy of the dataset. For more details, please refer to algoseek’s AWS Access Guide. For example,

If you have access to a subset of SecIds, you can check files with daily changes and only re-download data for securities from your subscription. For example, if you only subscribed for the following SecIds: 33449 (AAPL), 1914754 (TSLA), 38497 (IBM), and 33127 (AMZN) and would like to know if something has changed for these on Jan 21, 2021, you should download the file with daily changes using the following command:

and check if these SecIds are present in the file (see Table 6). The user then can download each individual data file with

Where NNNNN: secid and NN: first two digits of secid.

Note: If the “Change” column in the file with daily changes contains “deleted” values, that SecIds should be deleted from the local copy.

Path to the file with daily changes has the following pattern:

where bucket_name can be one of

1. us-equity-cumulative-backward-adjustment-secid-all;
2. us-equity-cumulative-backward-adjustment-secid-yyyy;

yyyy - year.

Daily changes files are created by 2 am T+1 EST containing a list of SecIds that were updated.

Field “Change” in the “yyyymmdd-corp-changes.csv” files can be: “updated” - SecId was updated with a corporate event, “added” - new SecId was added, and “deleted” - SecId was deleted (see Table 6).

Table 6: Sample of Daily Changes File

There is a couple of ways to keep the data updated:

1. If you work with the data aggregated by SecId and would like to have the whole history for a specific security in one file, you can use the AWS CLI SYNC command on the us-equity-cumulative-backward-adjustment-secid-all bucket. This setup will download only files from the S3 bucket that are different from the local copy. To sync your local directory and S3 bucket, please make sure to point the SYNC command to your local copy of the dataset. In meantime, this approach may be expensive ($1.5 per month for the universe of 12 000 tickers) if you regularly update the full universe of securities. To overcome it, please check the approach below.

2. You can work with the data aggregated by SecId and by year. In this case, you can use AWS SYNC on us-equity-cumulative-backward-adjustment-secid-year buckets that you need, where yyyy is the year. If you still need to keep the whole history for each security in a single file, you can merge them on your side locally. It will be cheaper than updating data using the us-equity-cumulative-backward-adjustment-secid-all bucket ($0.5 versus $1.5 per month for the universe of 12 000 tickers).

3. Also, algoseek provides a python script that you can use to keep data updated. Using this script, you can easily update data for a list of SecIds and merge all data for each SecId.

APPENDIX A. FREQUENTLY ASKED QUESTIONS

Why do I see a weird first row with AdjustmentReason = START and EventType = START in the data files?

Every file with the corporate events history for a SecId starts with the initial row that contains information on when the SecId was assigned to the security.