- symbol translation library
The Rosetta library removes all the difficulties surrounding symbol translation.
"Does the dataset have a Ticker, Symbol, CUSIP, DXLID, ISIN, SEDOL, BB Ticker?"
Rosetta has already completed symbol translation for the datasets we provide, so most of the time you will not need to worry about symbol translation or interact with Rosetta.
However, you can also easily use Rosettas symbol remapping functions on your own imported data, to ensure smooth alignment with the other datasets we provide.
Example
import rosetta
rosetta.query(source_table = 'dxopen', source_field = 'SEDOL', keys = symbollist, target_table = 'cq', target_field = 'Symbol')
Query
query(source_table, source_field, keys, target_table, target_field,
source_rosetta_as_of=None, target_rosetta_as_of=None,
source_system_as_of=None, target_system_as_of=None,
field_check=False, full_paths=False)
| source_table | Table name that we are trying to translate fields from |
| source_field | The field that the keys will be found in, the item in the table that we have and are using to translate to the desired result |
| keys | String or list of strings which are the values that we are trying to translate to an associated value |
| target_table | Table name that has the field we are trying to translate to |
| target_field | The field that we are trying to translate the keys to |
| source_rosetta_as_of | Muts or date string. "What does the source table say the values were on this date?" |
| target_rosetta_as_of | Muts or date string. "What does the target table say the values were on this date?" |
| source_system_as_of | Muts or date string. "Revert the entire source table to what it contained on this date" |
| target_system_as_of | Muts or date string. "Revert the entire target table to what it contained on this date" |
| field_check | If user suspects that the field(s) are not in the tables, this can be set True to check (Will throw exception if so, continue is normal if not) |
| full_paths | If True, returns all the translation paths showing the intermediary values between the keys and result. Mostly for debugging. |
Query Returns
Returns a list of return_type(source_key, not_in_source, cq_instrument_id, target_key).
The indices of this list will correlate with those of the parameter keys.
not_in_source is True if the key did not exist in the source_field in the source_table
Caution
Liberator and Rosetta can be used in both CQ Mariner Backtester and CQ AI.
There are no restrictions on the dates and times you can request.
When submitting a query in the CQ Mariner backtesting environment please be careful not to access future data.
ie in backtesting the 'as_of' time should be the current timestamp or earlier (ie less than the current time stamp).
Script
from cloudquant.interfaces import Strategy
import liberator
import rosetta
import pandas as pd
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
class RosettaDemo(Strategy):
@classmethod
def on_strategy_start(cls, md, service, account):
dataSet = 'ETFGlobal Constituents'
asof = '2020-05-01'
backto = '2020-05-01'
symbols = ['DIA'] # submit a list or use None for all symbols
df = liberator.get_dataframe(liberator.query(symbols = symbols, as_of = asof, back_to = backto, name = dataSet)) # Query and converstion to dataframe in one line! Nice Python!
df['Date10'] = df.apply(lambda row: str(row.timestamp)[0:10], axis = 1) # Trim time off timestamp as it is midnight 00:00:00
tr = rosetta.query(source_table = 'dxopen', source_field = 'SEDOL', keys = df['sedol'].to_list(), target_table = 'cq', target_field = 'Symbol')
df['translated'] = [row[-1][0] for row in tr] # Put the translated symbol name from the rosetta fetch into the translated column.. one by one..
print(df[['Date10', 'translated','weight','constituent_ticker','constituent_name']]) # Display just a few columns
Output
Date10 translated weight constituent_ticker constituent_name
0 2020-05-01 AAPL 0.083510 AAPL APPLE INC
1 2020-05-01 UNH 0.082193 UNH UNITEDHEALTH GROUP INC
2 2020-05-01 HD 0.063143 HD HOME DEPOT INC
3 2020-05-01 MCD 0.052769 MCD MCDONALDS CORP
4 2020-05-01 GS 0.051163 GS GOLDMAN SACHS GROUP INC
5 2020-05-01 V 0.050721 V VISA INC-CLASS A SHARES
6 2020-05-01 MSFT 0.050432 MSFT MICROSOFT CORP
7 2020-05-01 MMM 0.042929 MMM 3M CO
8 2020-05-01 JNJ 0.042840 JNJ JOHNSON & JOHNSON
9 2020-05-01 BA 0.038530 BA BOEING CO
10 2020-05-01 WMT 0.035511 WMT WAL-MART STORES INC
11 2020-05-01 IBM 0.035207 IBM International Business Machines Corporation
12 2020-05-01 PG 0.033748 PG PROCTER & GAMBLE CO/THE
13 2020-05-01 CAT 0.032032 CAT CATERPILLAR INC
14 2020-05-01 DIS 0.030478 DIS WALT DISNEY CO/THE
15 2020-05-01 TRV 0.027748 TRV The Travelers Companies Inc.
16 2020-05-01 JPM 0.026939 JPM JPMORGAN CHASE & CO
17 2020-05-01 CVX 0.025839 CVX CHEVRON CORP
18 2020-05-01 AXP 0.025515 AXP AMERICAN EXPRESS CO
19 2020-05-01 NKE 0.024712 NKE NIKE INC -CL B
20 2020-05-01 MRK 0.022438 MRK MERCK & CO. INC.
21 2020-05-01 RTX 0.017486 RTX RAYTHEON TECHNOLOGIES CORP
22 2020-05-01 INTC 0.016603 INTC INTEL CORP
23 2020-05-01 VZ 0.016418 VZ VERIZON COMMUNICATIONS INC
24 2020-05-01 KO 0.013173 KO COCA-COLA CO/THE
25 2020-05-01 XOM 0.012463 XOM EXXON MOBIL CORP
26 2020-05-01 WBA 0.012035 WBA WALGREENS BOOTS ALLIANCE INC
27 2020-05-01 CSCO 0.011821 CSCO CISCO SYSTEMS INC
28 2020-05-01 PFE 0.010874 PFE PFIZER INC
29 2020-05-01 DOW 0.009802 DOW DOW INC
30 2020-05-01 None 0.001288 US DOLLAR
31 2020-05-01 None 0.000168 UNITED TECHNOLOGIES CORP WI COMMON STOCK