Capital IQ integration 1 – time series data

This page demonstrates the usage of an orchestration program allowing querying of the S&P Capital IQ database for time series data and uploading of the results to Quantemplate

Quantemplate's Capital IQ integration enables you to augment your pipeline output with time series financial data from Capital IQ.

📘

Capital IQ IDs

This demo shows a basic integration with the Capital IQ company IDs stored locally. You can also take a look at a more advanced example which uses Capital IQ company IDs stored in a Quantemplate dataset. The dataset could be the product of a pipeline to match incoming company names to Capital IQ IDs.

📘

Prerequistes

Before trying this example, make sure you have:

• an existing Quantemplate API User and Secret
• a valid Capital IQ API license
• and Java 8 environment installed on your system

Step 1: Configure target dataset and its permissions

First, navigate to the Quantemplate app, choose the Data tab and create a new dataset that will host the Capital IQ data.

Share the dataset with API User via the sharing popup.

Check the URL to get the relevant IDs, and share the dataset with the whole organisation

Extracting the IDs from the URL

Moving forward we will need ID of your dataset and the organisation
Take a look at the URL used by your dataset. In the screenshot above this is https://app.quantemplate.com/c-jcre-hi/dataset/d-jvv2uapp2rcfnreytumsmniw/preview.

From this, you can extract the IDs as follows:
app.quantemplate.com/organisation id/dataset/dataset id/preview

In this particular example:

• organisation ID is c-jcre-hi
• dateset ID is is d-jvv2uapp2rcfnreytumsmniw

Step 2: Setting up the orchestration program

As the code for Capital IQ integration is a little bit more involved, instead of writing it from scratch we will use an existing open-source Quantemplate API integration helper.
In order to download the helper, navigate to the releases section and click on qt-integration-0.1.0.jar. This will download Java 8+ compatible jar.

Environment variables

The integration helper expects a few environment variables used for configuring API access, in particular:

Text

CAPITALIQ_API_USERNAME=<api username you got with Capital IQ API license>
 CAPITALIQ_API_PASSWORD=<password for the corresponding Capital IQ API user>
 CAPITALIQ_DEMO_ACCOUNT=<indicates whether the Capital IQ demo account is used>
 QT_ENV=<name of the Quantemplate environment, should be `prod` for any consumers>
 QT_AUTH_REALM=<name of the Quantemplate auth ream, should be `qt` for any consumers>
 QT_CLIENT_ID=<id of the api-client user generated by the Quantemplate team>
 QT_CLIENT_SECRET=＜password for the used api-client user, also provided by the Quantemplate team>

You can save this file as a .env and fill out the < > spots with your settings.
Then these options could be exported as environment variables. In macOS and Linux this could be done using a simple export $(xargs < .env) command.

Step 3: Configure the Capital IQ integration properties

Having environment variables from the previous step in your command line session you can start using the program.

The orchestration helper accepts parameters either as command-line arguments or reads them from YAML, which should be familiar to many Quantemplate users.

YAML

command: generateRevenueReport
params: 
  orgId: c-jcre-hi                        # Quantemplate organisationId
  datasetId: d-jvv2uapp2rcfnreytumsmniw   # Quantemplate datasetId
  currency: USD                           # Currency supported by the CapitalIQ
  from: 1988-12-31                        # Starting date in yyy-mm-dd format for the CapitalIQ query
  to: 2018-12-31                          # Ending date in yyy-mm-dd format for the CapitalIQ query
  identifiers:                            # CapitalIQ identifiers, that could be downloaded from:
    # dataset: d-pjuq6lofrnbszq5zntfy4rvv #  - remote Quantemplate `dataset` (currently it only takes data from the first column)                                                  
    # local: ./capitaliq-identifiers.txt  #  - `local` .txt file,  (relative file path will be resolved against the config file)
    inline:                               #  - defined `inline` in the config file
      - IQ121238                          # Multiple sources of identifiers will be merged together
      - IQ598807
      - IQ723344
      - IQ956057

As you can see in the config above this will instruct the orchestration program to generate the revenue report in d-jvv2uapp2rcfnreytumsmniw dataset which belongs to the c-jcre-hi organisation, for the periods between 1988 and 2018 for the companies which Capital IQ identifiers are included in the identifiers field.

Identifiers could be inlined in the YAML file, could come from a local text file, or be loaded from existing Quantemplate datasets. The last option allows us to achieve rich automated workflows.

For the sake of simplicity, in this example, we will use inline identifiers.

Save the YAML file defined above as revReport.yml and replace the orgId and datsetId with identifiers extracted from the dataset URL.

Step 4: Upload the revenue report from Capital IQ to the Quantemplate

With a YAML file created in the previous step now, you can run the integration helper with just a:

Shell

java -jar qt-integration-0.1.0.jar apply ./revReport.yml

After a few seconds, the program should inform you about a successful upload.

Now you can refresh the dataset page and see the results:

Capital IQ data uploaded using the Quantemplate API integration

The report data comes from Capital IQ and it's using IQ_TOTAL_REV and IQ_COMPANY_NAME_LONG mnemonics.

The whole program is open-sourced so you can either extend it yourself or roll out your own solution based on its internals.