Technology

Data Syncing Guide

In order to seed your database and keep it up-to-date with Intrinio stock price data, follow the steps below.

Schema

First you will need to create database tables that correspond to the various entities that compose this dataset.

Stock Exchanges

The table for stock exchanges should look like this:

create table stock_exchanges
(
  id                      text not null primary key,
  name                    text,
  mic                     text,
  acronym                 text,
  city                    text,
  country                 text,
  country_code            text,
  website                 text,
  first_stock_price_date  date,
  last_stock_price_date   date
);

Which corresponds to the Stock Exchange API format:

{
  "id": "sxg_AXGAyM",
  "name": "LONDON STOCK EXCHANGE",
  "mic": "XLON",
  "acronym": "LSE",
  "city": "LONDON",
  "country": "UNITED KINGDOM",
  "country_code": "GB",
  "website": "HTTP://WWW.LONDONSTOCKEXCHANGE.COM",
  "first_stock_price_date": "2001-11-08",
  "last_stock_price_date": "2020-05-21"
}

Note that the primary key is the Intrinio id field, which is text. If you would prefer to key your tables with integers, add an intrinio_id column instead, with a unique constraint.

You may want to add a sync boolean field for specific stock exchanges that you wish to keep up-to-date.

Securities

A stock exchange has many securities trading on it. The table for securities should look like this:

create table securities
(
  id                           text not null primary key,
  company_id                   text,
  stock_exchange_id            text,
  name                         text,
  type                         text,
  code                         text,
  share_class                  text,
  currency                     text,
  round_lot_size               integer,
  ticker                       text,
  exchange_ticker              text,
  composite_ticker             text,
  alternate_tickers            text[],
  figi                         text,
  cik                          text,
  composite_figi               text,
  share_class_figi             text,
  figi_uniqueid                text,
  primary_security             boolean,
  primary_listing              boolean,
  active                       boolean,
  etf                          boolean,
  delisted                     boolean,
  first_stock_price            date,
  last_stock_price             date,
  last_stock_price_adjustment  date,
  last_corporate_action        date,
  previous_tickers             text[],
  listing_exchange_mic         text
);

You will likely want to add database indexes on various security columns, such as ticker, currency, figi, stock_exchange_id, etc. This will depend on your use cases.

You may want to add a sync boolean field for specific securities that you wish to keep up-to-date, later on.

Stock Prices

A security has many stock price records. The table for stock prices should look like this:

create table stock_prices
(
  security_id  text not null primary key,
  date         date,
  intraperiod  boolean,
  frequency    text,
  open         double precision,
  high         double precision,
  low          double precision,
  close        double precision,
  volume       double precision,
  adj_open     double precision,
  adj_high     double precision,
  adj_low      double precision,
  adj_close    double precision,
  adj_volume   double precision
);

Stock Price Adjustments

A security’s stock price may be adjusted for splits and dividends. These are stored in a stock price adjustments table, which should look like this:

create table stock_price_adjustments
(
  security_id         text not null primary key,
  date                date,
  factor              double precision,
  dividend            double precision,
  dividend_currency   text,
  split_ratio         double precision
);

Seeding

Now that your schema is in place, it’s time to use the Intrinio API to seed your tables and keep them up-to-date.

Stock Exchanges

First, let’s use the All Stock Exchanges API to get all stock exchanges. This endpoint returns all exchanges, so no paging is necessary.

Web API

GET https://api-v2.intrinio.com/stock_exchanges

{
  "stock_exchanges": [
    {
      "id": "sxg_xgxM8X",
      "name": "AB NASDAQ VILNIUS",
      "mic": "XLIT",
      "acronym": "LIT",
      "city": "VILNIUS",
      "country": "LITHUANIA",
      "country_code": "LT",
      "website": "WWW.NASDAQBALTIC.COM",
      "first_stock_price_date": "2004-05-14",
      "last_stock_price_date": "2020-05-22"
    },
    {
      "id": "sxg_2z9OBg",
      "name": "ABU DHABI SECURITIES EXCHANGE",
      "mic": "XADS",
      "acronym": "ADSM",
      "city": "ABU DHABI",
      "country": "UNITED ARAB EMIRATES",
      "country_code": "AE",
      "website": "WWW.ADX.AE",
      "first_stock_price_date": "2002-01-02",
      "last_stock_price_date": "2020-05-21"
    },
    ...
  ]
}

Load these records into the stock_exchanges table.

Securities

For each stock exchange that you are interested in, fetch the securities in that exchange using the Securities by Exchange API. Use the stock exchange id as the identifier, for maximum specificity. Use Paging so that you get all of the results.

Web API

GET https://api-v2.intrinio.com/stock_exchanges/sxg_AXGAyM/securities

{
  "stock_exchanges": [
    {
      "id": "sxg_xgxM8X",
      "name": "AB NASDAQ VILNIUS",
      "mic": "XLIT",
      "acronym": "LIT",
      "city": "VILNIUS",
      "country": "LITHUANIA",
      "country_code": "LT",
      "website": "WWW.NASDAQBALTIC.COM",
      "first_stock_price_date": "2004-05-14",
      "last_stock_price_date": "2020-05-22"
    },
    {
      "id": "sxg_2z9OBg",
      "name": "ABU DHABI SECURITIES EXCHANGE",
      "mic": "XADS",
      "acronym": "ADSM",
      "city": "ABU DHABI",
      "country": "UNITED ARAB EMIRATES",
      "country_code": "AE",
      "website": "WWW.ADX.AE",
      "first_stock_price_date": "2002-01-02",
      "last_stock_price_date": "2020-05-21"
    },
    ...
  ]
}

Load these records into the securities table.

Stock Prices

Now that you have securities in your database, let’s populate their stock prices using the Stock Prices by Security API. Iterate each security and use its id as the idenifier, for maximum specificity. Use Paging so that you get all of the results.

Web API

GET https://api-v2.intrinio.com/securities/sec_agjrgj/prices

{
  "stock_prices": [
    {
      "date": "2020-05-21",
      "intraperiod": false,
      "frequency": "daily",
      "open": 318.66,
      "high": 320.89,
      "low": 315.87,
      "close": 316.85,
      "volume": 25672211.0,
      "adj_open": 318.66,
      "adj_high": 320.89,
      "adj_low": 315.87,
      "adj_close": 316.85,
      "adj_volume": 25672211.0
    },
    ...
  ],
  "security": {
    "id": "sec_agjrgj",
    "company_id": "com_NX6GzO",
    "stock_exchange_id": "sxg_ozMr9y",
    "name": "Apple Inc",
    "code": "EQS",
    "currency": "USD",
    "ticker": "AAPL",
    "composite_ticker": "AAPL:US",
    "figi": "BBG000B9Y5X2",
    "composite_figi": "BBG000B9XRY4",
    "share_class_figi": "BBG001S5N8V8"
  },
  "next_page": "MjAyMC0wNS0yMHwxODc4MjMxOTcxNg=="
}

Load these records into the stock_prices table, making sure to append security_id to each record, based on the id of the security used.

Stock Price Adjustments

Lastly, we will populate stock price adjustments per security using the Stock Price Adjustments by Security API, in the same fashion as stock prices. Use Paging so that you get all of the results.

Web API

GET https://api-v2.intrinio.com/securities/sec_agjrgj/prices/adjustments

{
  "stock_price_adjustments": [
    {
      "date": "2020-05-08",
      "factor": 0.997300322644367,
      "dividend": 0.82,
      "dividend_currency": "USD",
      "split_ratio": 1.0
    },
    {
      "date": "2020-02-07",
      "factor": 0.997632299129793,
      "dividend": 0.77,
      "dividend_currency": "USD",
      "split_ratio": 1.0
    }
  ],
  "security": {
    "id": "sec_agjrgj",
    "company_id": "com_NX6GzO",
    "stock_exchange_id": "sxg_ozMr9y",
    "name": "Apple Inc",
    "code": "EQS",
    "currency": "USD",
    "ticker": "AAPL",
    "composite_ticker": "AAPL:US",
    "figi": "BBG000B9Y5X2",
    "composite_figi": "BBG000B9XRY4",
    "share_class_figi": "BBG001S5N8V8"
  },
  "next_page": "MjAyMC0wMi0wN3w3OTgxMjMxNg=="
}

Load these records into the stock_price_adjustments table, making sure to append security_id to each record, based on the id of the security used.

Syncing

There are two methods to keep your database up-to-date as daily stock prices continue to accumulate:

Full Rebuild

This requires following the Seeding steps above on a nightly basis. You must either clear each database table before re-seeding or incorporate an upsert mechanism. This method is the safest but also the slowest. Use cases include:

Storing historical prices for hundreds of securities
Storing latest prices for all securities in a few exchanges

Incremental Updates

Updating your database incrementally is a fast way to get the freshest data, but it does require a few considerations that we will outline below. The ideal use cases include:

Storing historical prices for all securities
Storing latest prices for all securities in all exchanges

In order to incrementally update your database, perform the following steps for each stock_exchanges record for which you want to get up-to-date data:

Check for New Prices. Use the Lookup Stock Exchange API to check the last_stock_price_date. If this date is greater than the date you have in your stock_exchanges table for the same record, new pricing data is available. Continue to step 2 if this is the case, otherwise repeat step 1 periodically (every minute or hour depending on your needs).
Fetch Daily Prices. Use the Stock Prices by Exchange API to get all of the day’s prices for the exchange (remember to use paging). Append these records to the stock_prices table.
Update Securities. For all securities returned by the API in step 2, check if they exist in your database. If not, use the Lookup Security API to add them. Otherwise, update the security’s last_stock_price field in the database to reflect the new price data.
Fetch Daily Adjustments. Use the Stock Price Adjustments by Exchange API to get all of the day’s price adjustments for the exchange (remember to use paging). Append these records to the stock_price_adjustments table.
Reload Adjusted Pricing. Every security with a new stock price adjustment must have its entire stock prices history reloaded using the Stock Prices by Security API. See the blue box below for more details. It’s best to delete all stock_prices records for the security before reloading them from the API.
Update the Exchange. Update the stock_exchanges record with the latest stock exchange info from step 1, notably the last_stock_price_date field, so you know how up-to-date the exchange prices are.

Stock price adjustments affect a security’s entire price history. A stock split of 2:1 causes a security’s trading price to fall by half the following trading day, since everyone’s shares were effectively doubled. In order to do meaningful analysis on stock prices, you have to adjust the price historically for both splits and dividends. The Intrinio API provides this data via adj_ fields, but in order to keep your database up-to-date, you have to re-fetch a security’s entire price history when there is a new adjustment.

Even though incremental updates are an effective way to keep your database up-to-date, we recommend that you perform a Full Rebuild every weekend. This will allow you to take advantage of corrections made by the Intrinio Data Quality team, as well as smoothing out any failed updates or other inconsistencies.

Considerations

Please consider the following topics when writing your data syncing operation.

Limits

Your Intrinio account has certain limits for accessing our API. The most common limit is requests-per-minute, which determines how many HTTP requests you can send our API in a wall-clock minute before it start to respond with 429 errors. See the Limits section of our API reference for more details.

Performance

In order to maximize performance and reliability, we recommend that you use the default page size of 100 when accessing our API (omitting page_size parameter does the same). This default page size is the optimal setting for ensuring fast, consistent API responses.

Robustness

Integrating with an API requires a lenient mindset towards availability. There may be intermittent network issues or a deploy gone wrong that will prevent 100% of your requests from being satisfied. In order to make your data syncing operation robust, we recommend using a “retry” library or code module to automatically retry failed requests several times before throwing an exception. Here are a few examples:

Python: retrying
R: RETRY
Ruby: retries
JavaScript: attempt
Java: retry4j
C#: Polly

Another way to make your data syncing operation more robust is to use a job processing system. A job processing system can provide you with several important benefits:

Concurrency: Many jobs can run in parallel, across multiple CPU threads and/or servers. This can yield much faster processing times.
Isolation: Jobs run in isolation, and so fail in isolation, as well. Your entire data sync operation does not come to a halt if a single API request fails.
Retries: Most job processing include automatic retries of failed jobs, or manual retries after human review. This is much better than poring over error logs, since failed jobs can be restarted with all of their parameters intact.
Scheduling: Jobs can be scheduled to run around the clock, at optimal processing times.
Logging: Jobs and their outcomes are logged, so you can verify that the system is operating correctly.
Alerts: Failed jobs can alert the proper team members in order to take remedial action.

Here are some examples of job processing systems:

Python: Celery
Ruby: faktory
JavaScript: kue
Java: Spring Batch
C#: Hangfire

You may also consider writing your own. Intrinio uses a custom-built, sophisticated job processing system in order to power our data processing engine.

If you have further questions, please reach out to your customer success manager.