Functions - Financial#

The following financial functions are supported:

GET_SYMBOLOGY_MAPPING, CORP_ACTIONS, MKT_ACTIVITY, TICK_SHIFT, TIME_SHIFT.

GET_SYMBOLOGY_MAPPING#

Translates and returns the symbol in dest_symbology using the current timestamp as the symbol date. The remaining optional parameters are specified to overwrite the symbology of the current symbol, the current symbol, and the timestamp, respectively.

Simple Syntax:

string GET_SYMBOLOGY_MAPPING(string dest_symbology [, string src_symbology, string symbol, datetime timestamp])

GET_SYMBOLOGY_MAPPING Function Example#

select PRICE, GET_SYMBOLOGY_MAPPING('FGC') as S_GET_SYMBOLOGY_MAPPING
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 1

GET_SYMBOLOGY_MAPPING Function Example Results#
	Timestamp	PRICE	SIZE	S_GET_SYMBOLOGY_MAPPING
0	2024-01-03 00:00:40.413537416	50.56	BBG000C3J3C9

CORP_ACTIONS#

Returns the adjusted value of the specified field. Only field_name paramter is mandatory. Optional adjust_date string parameter has format YYYYMMDD, and defaults to the end time of the query.

Optional adjust_rule string paramater has enumerated values PRICE and SIZE. When set to PRICE, adjustments are applied under the assumption that fields to be adjusted contain prices. When set to SIZE, adjustments are applied under the assumption that fields contain sizes/quantities (adjustment direction is opposite to that when the parameter’s value is PRICE).

Optional adjustment_types string parameter is a comma separated list of adjustment types, with a default type of SPLIT. Possible values are SPLIT, SPINOFF, CASH_DIVIDEND, STOCK_DIVIDEND, RIGHTS, SECURITY_SPLICE and ALL (meaning to apply all adjustment types). For specifying custom adjustment types the type should be prepended with CUSTOM_ prefix.

Simple Syntax:

double CORP_ACTIONS(string field_name [, string adjustment_date, string adjust_rule, string adjustment_types])

CORP_ACTIONS Function Example#

select CLOSE,
CORP_ACTIONS('CLOSE') as ADJ_CLOSE
from US_COMP_SAMPLE_DAILY.DAY
where SYMBOL_NAME = 'WMT' AND EXCHANGE = ''
and TIMESTAMP >= '2024-02-10 00:00:00.000 UTC'
and TIMESTAMP < '2024-03-10 00:00:00.000 UTC'

CORP_ACTIONS Function Example Results#
	Timestamp	CLOSE	ADJ_CLOSE
0	2024-02-10 01:15:00	169.28	56.4266661024
1	2024-02-13 01:15:00	170.3	56.766666099
2	2024-02-14 01:15:00	169.14	56.379999436199995
3	2024-02-15 01:15:00	168.6	56.19999943799999
4	2024-02-16 01:15:00	169.29	56.4299994357
5	2024-02-17 01:15:00	170.36	56.786666098800005
6	2024-02-21 01:15:00	175.86	58.6199994138
7	2024-02-22 01:15:00	173.7	57.89999942099999
8	2024-02-23 01:15:00	175.41	58.469999415299995
9	2024-02-24 01:15:00	175.56	58.5199994148
10	2024-02-27 01:15:00	59.6	59.6
11	2024-02-28 01:15:00	59.59	59.59
12	2024-02-29 01:15:00	59.62	59.62
13	2024-03-01 01:15:00	58.61	58.61
14	2024-03-02 01:15:00	58.76	58.76
15	2024-03-05 01:15:00	59.3	59.3
16	2024-03-06 01:15:00	60.04	60.04
17	2024-03-07 01:15:00	60.57	60.57
18	2024-03-08 01:15:00	60.36	60.36
19	2024-03-09 01:15:00	60.12	60.12

MKT_ACTIVITY#

Returns a string with a value for the specified calendar. The first character of the string represents the day type.

L - Half Day,
H - Holiday,
W - Weekend,
R - Regular

The second letter represents the general timing of trading periods.

Rb - Regular - Pre-Market
Rr - Regular - During Trading
Ra - Regular - Post Market
Ra - Regular - Post Market
R1 - Regular - Morning Session
Rx - Regular - Lunch Break
R2 - Regular - Afternoon Session

Note: Markets may have specific random auction timings. Exact timings of trading sessions can be retrieved from the MKT table.

Simple Syntax:

string MKT_ACTIVITY(string calendar_name)

MKT_ACTIVITY Function Daily Example#

select CLOSE, MKT_ACTIVITY('CLOUD_DB_US_COMP') as ACTIVITY_CODE
from US_COMP_SAMPLE_DAILY.DAY
where
SYMBOL_NAME = 'HD' AND EXCHANGE = ''
and TIMESTAMP >= '2024-01-01 00:00:00.000 UTC'
and TIMESTAMP < '2024-02-01 00:00:00.000 UTC'
limit 10

CORP_ACTIONS Function Daily Example Results#
	Timestamp	CLOSE	ACTIVITY_CODE
0	2024-01-03 01:15:00	345.08	R
1	2024-01-04 01:15:00	338.26	R
2	2024-01-05 01:15:00	338.59	R
3	2024-01-06 01:15:00	342.94	R
4	2024-01-09 01:15:00	347.93	R
5	2024-01-10 01:15:00	346.19	R
6	2024-01-11 01:15:00	356.8	R
7	2024-01-12 01:15:00	356.53	R
8	2024-01-13 01:15:00	355.71	R
9	2024-01-17 01:15:00	358.43	R

MKT_ACTIVITY Function Intraday Example#

select PRICE,SIZE,COND,EXCHANGE,
MKT_ACTIVITY('CLOUD_DB_US_COMP') as MKT_ACTIVITY
from US_COMP_SAMPLE.TRD
where
SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:29:30.000 America/New_York'
and TIMESTAMP < '2024-01-03 09:30:00.200  America/New_York'
limit 10

CORP_ACTIONS Function Intraday Example Results#
	Timestamp	PRICE	SIZE	COND	EXCHANGE	MKT_ACTIVITY
0	2024-01-03 14:29:38.014214683	50.04	10	@ TI	P	Rb
1	2024-01-03 14:29:38.570056786	50.04	89	@FTI	P	Rb
2	2024-01-03 14:29:50.768847900	50.06	2	@FTI	P	Rb
3	2024-01-03 14:29:59.990898650	50.16	1995	@ T	Q	Rb
4	2024-01-03 14:29:59.990941169	50.16	471	@FT	Q	Rb
5	2024-01-03 14:30:00.065443591	50.02	2	@ I	Z	Rr
6	2024-01-03 14:30:00.111130049	50.16	3	@ I	Z	Rr
7	2024-01-03 14:30:00.127459523	50.17	100	@	V	Rr
8	2024-01-03 14:30:00.128498068	50.17	5	@F I	Z	Rr
9	2024-01-03 14:30:00.135190071	50.13	46	@FTI	Q	Rr

TICK_SHIFT#

Returns the value of the specified field from a tick preceding the current one by offset. group_by_field_names if specified should represent a comma-separated list of field names, in which case offset is counted among the ticks with same values of the specied fields.

Simple Syntax:

field type TICK_SHIFT(string field_name,int offset [,string group_by_field_names])

TICK_SHIFT Function Example#

select PRICE, SIZE, EXCHANGE,
TICK_SHIFT('PRICE',1) N_TICK_SHIFT,
TICK_SHIFT('PRICE',1,'EXCHANGE') N_TICK_SHIFT_BY_EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 10

CORP_ACTIONS Function Intraday Example Results#
	Timestamp	PRICE	SIZE	EXCHANGE	N_TICK_SHIFT	N_TICK_SHIFT_BY_EXCHANGE
0	2024-01-03 00:00:40.413537416	50.56	17	Q
1	2024-01-03 00:00:40.413540299	50.57	33	Q	50.56	50.56
2	2024-01-03 00:03:34.026455739	50.52	26	P	50.57
3	2024-01-03 00:03:34.026458843	50.51	123	P	50.52	50.52
4	2024-01-03 00:03:34.026459421	50.49	3	P	50.51	50.51
5	2024-01-03 00:05:06.042217019	50.4896	1	D	50.49
6	2024-01-03 00:07:11.737983252	50.4897	1	D	50.4896	50.4896
7	2024-01-03 00:12:17.787582635	50.49	1	D	50.4897	50.4897
8	2024-01-03 00:13:16.630750984	50.49	3	P	50.49	50.49
9	2024-01-03 00:14:14.187418230	50.49	100	P	50.49	50.49

TIME_SHIFT#

Returns the value of the specified field, from a record, whose timestamp differs from current record’s timestamp by shift time in milliseconds. If shift time is negative, it returns the value for the preceding record, and if shift time is positive, succeeding ticks (those that have not arrived yet) are considered.

The selection criteria optional parameter is a constant string indicating record selection criteria. Possible values for the criteria parameter are: LAST_BEFORE, LAST_AT_OR_BEFORE, FIRST_AFTER`, ``FIRST_AT_OR_AFTER. The default value for tick selection criteria is:

LAST_AT_OR_BEFORE for negative shift times
FIRST_AT_OR_AFTER for positive shifts

Simple Syntax:

field type TIME_SHIFT(field name,shift time [,selection criteria,timestamp field])

TIME_SHIFT Function Example#

select PRICE, SIZE, EXCHANGE,
TIME_SHIFT('PRICE',-1000) N_TIME_SHIFT_BACK_1S,
TIME_SHIFT('PRICE',1000) N_TIME_SHIFT_FWD_1S
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 10

TIME_SHIFT Function Intraday Example Results#
	Timestamp	PRICE	SIZE	EXCHANGE	N_TIME_SHIFT_BACK_1S	N_TIME_SHIFT_FWD_1S
0	2024-01-03 00:00:40.413537416	50.56	17	Q		50.52
1	2024-01-03 00:00:40.413540299	50.57	33	Q		50.52
2	2024-01-03 00:03:34.026455739	50.52	26	P	50.57	50.4896
3	2024-01-03 00:03:34.026458843	50.51	123	P	50.57	50.4896
4	2024-01-03 00:03:34.026459421	50.49	3	P	50.57	50.4896
5	2024-01-03 00:05:06.042217019	50.4896	1	D	50.49	50.4897
6	2024-01-03 00:07:11.737983252	50.4897	1	D	50.4896	50.49
7	2024-01-03 00:12:17.787582635	50.49	1	D	50.4897	50.49
8	2024-01-03 00:13:16.630750984	50.49	3	P	50.49	50.49
9	2024-01-03 00:14:14.187418230	50.49	100	P	50.49	50.49