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])``

.. code-block:: sql
   :caption: 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


.. csv-table:: GET_SYMBOLOGY_MAPPING Function Example Results
   :header: ,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])``

.. code-block:: sql
   :caption: 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' 


.. csv-table:: CORP_ACTIONS Function Example Results
   :header: ,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)``

.. code-block:: sql
   :caption: 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


.. csv-table:: CORP_ACTIONS Function Daily Example Results
   :header: ,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

.. code-block:: sql
   :caption: 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

.. csv-table:: CORP_ACTIONS Function Intraday Example Results
   :header: ,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])``

.. code-block:: sql
   :caption: 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

.. csv-table:: CORP_ACTIONS Function Intraday Example Results
   :header: ,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])``

.. code-block:: sql
   :caption: 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


.. csv-table:: TIME_SHIFT Function Intraday Example Results
   :header: ,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