Normalized Financial Symbols

In Apperate, you can refer to an equity symbol using any supported financial identifier type. Apperate relates equivalent symbols across different identifier types. You can, for example, use a CUSIP symbol to query data that uses FIGI, ISIN, or another supported identifier type. Apperate, in effect, normalizes the financial identifiers.

Subscribing to the financial identifier data would cost you tens of thousands of dollars annually. Implementing a mapping between the identifiers is complicated and time-consuming. We have the financial identifiers and normalized all the symbols for you, so you can concentrate on your business logic.

Here we’ll demonstrate two things:

• Querying normalized symbols

• Joining datasets on normalized symbols

Querying Normalized Symbols

You can query normalized financial data using symbols of any supported financial identifier type. For example, the following dataset uses the FIGI financial identifier type for its ticker column values. It refers to Apple using the US0378331005 FIGI symbol.

You can, however, query the data refering to Apple as AAPL. Here is a SQL query and HTTP request that query the data using the AAPL symbol.

SQL Query:

SELECT * FROM IEXCLOUDAPPERATE.`MY_NEWS_DATASET` where ticker='AAPL';

REST API URL:

https://IEXCLOUDAPPERATE.iex.cloud/v1/data/IEXCLOUDAPPERATE/MY_NEWS_DATASET/AAPL?token=TOKEN

REST API URL Response:

[
  {
    "content": "Yada yada yada",
    "date": "2023-01-18",
    "headline": "This Earbud's for you!",
    "source": "IEX Underground",
    "ticker": "US0378331005"
  }
]

You can join datasets on normalized symbol data.

Joining Datasets on Normalized Symbols

The COMPANY IEX Cloud Financial dataset identifies Apple using the symbol value AAPL.

Note

If there is grayed-out data you want, purchase the Data Bundle it belongs to. The Data Bundles are available to purchase in the Console.

Despite COMPANY and the example MY_NEWS_DATASET dataset identifying Apple with different values (the US0378331005 FIGI symbol and common AAPL symbol), both datasets normalize their ticker/symbol properties using Apperate SmartLinks. SmartLinks enables you to join datasets on equivalent, SmartLinked values.

You can, for example, create a view of Apple news from MY_NEWS_DATASET and Apple company information from COMPANY by joining the datasets on their SmartLinked ticker/symbol properties.

SQL Query:

SELECT a.ticker, c.ceo, c.companyName, c.city, a.date, a.headline, a.content
  FROM IEXCLOUDAPPERATE.`MY_NEWS_DATASET` a
  JOIN core.`COMPANY` c
    ON c.symbol = a.ticker 
  WHERE a.ticker = 'AAPL';

Results:

Important

WHERE clauses and ON clauses must only operate on indexed properties (columns). See the Unique Index components here.

You can then create a view from the results by clicking Create view. Voila! Your view dataset is available to use like any other dataset.

What’s Next

Create a view from the query. See Create a View to learn how.

Want to connect with the financial time series datasets? Browse them in the Console.