Order Execution Analytics Utility Functions

This page provides an overview of the Equities Order Analytics Accelerator and its utility functions.

The Equity Order Analytics Accelerator includes a number of utility functions that provide users with tools to write custom Equities Execution Analytics. These utility functions cover a wide range of scenarios, simplify the development process, and ensure optimal performance of custom analytics.

Custom analytics design principles

The Order Analytics framework has been designed to be extended to include custom analytics.

When writing the custom analytics, you must keep the following principles in mind:

• The .eqea.analytics.cfg configuration table is at the heart of the framework.

  • This table defines the order of operations, whereby functions are called in the order that they appear in the funcName column of this table. Understanding this order is important because it lets you write analytics that are dependent on the output of other analytics.

    For example: The analytic intervalSpread depends on the values of arrivalAskPrice and arrivalBidPrice, which means that it appears in the .eqea.analytics.cfg table after these analytics.

  • This table also defines the behavior of the utility functions. This guide further explains how each function makes use of this configuration.

  • For maintainability and upgradability, it is recommended that custom analytics are added to the table .eqea.config.custom.analytics. Refer to the Equity Analytics Framework guide for more information on adding custom analytics.

    • The data in .eqea.config.custom.analytics is appended to .eqea.analytics.cfg on initialization.

• Considerations regarding inputs and outputs:

  • All functions named in the funcName column of .eqea.analytics.cfg should have the same inputs and outputs:

    • Input = A single keyed table. This table contains all Order data as well as all results of all previous Order Analytics.

    • Output = A single keyed table. This table contains all Order data, all results of all previous Order Analytics as well as the results of the current function.

  • As previously mentioned, the functions are called in the order they appear in the .eqea.analytics.cfg config table.

  • As the framework iterates across the list of functions, all previous results are passed into each successive function.

  • This allows you to write functions that have dependencies on the outputs of previous functions.

• In general, we have three types of analytics:

  • Simple analytics. These analytics are defined by standard operations on existing columns from the OrderAnalytics table. For best results, use the utility function .eqea.util.simpleAnalytics when writing custom simple analytics.

    For example: The analytic arrivalValue makes use of the .eqea.util.simpleAnalytics function as it simply multiplies arrivalMid by executedQty, both of which are columns that exist in the OrderAnalytics table.

  • Get market data and aggregate analytics. These analytics are defined by aggregations on market data. For best results, use the utility function .eqea.util.tickData.getDataAndAggFromCfg when writing custom data aggregation analytics.

    For example: The analytic intervalHigh makes use of the .eqea.util.tickData.getDataAndAggFromCfg function as it defined by the max trade price of the order's instrumentID between the strikeTime and orderCompletedTime.

  • As-of join analytics. These analytics are defined as market values at a time relative to a given order. For best results, use the utility function .eqea.util.asof.ajFromCfg when writing custom data asof join analytics.

    For example: The analytic reversionAskPrice_60 gets the value of the instrumentID's askPrice from the Quote data at time = orderCompletedTime + 60 seconds.

.eqea.util.simpleAnalytics

The .eqea.util.simpleAnalytics utility function enables users to run "simple" analytic, that is, analytics that use standard operators on existing columns from the OrderAnalytics table.

This function takes a dictionary as its input. The accepted dictionary keys are outlined in the table below:

The .eqea.util.simpleAnalytics function uses rows from .eqea.config.custom.analytics to dictate its behavior.

Analytics that make use of .eqea.util.simpleAnalytics should have at a minimum the following values populated in .eqea.config.custom.analytics:

• analytic. The name of the analytic you are calculating. This should match a corresponding column name in the OrderAnalytics table.

• analyticType. This is simply a value that can be used to group analytics together.

• funcName. This is the name of the function used to calculate these analytics. The function .fsi.eqea.generateOrderAnalytics calculates order analytics in the order that they appear in the .eqea.analytics.cfg table. This information can help you build out analytics that are dependent on other analytics by placing them lower down the config table than the analytics they depend on.

  For example: The analytic intervalSpread depends on the values of arrivalAskPrice and arrivalBidPrice, which means that it appears in the .eqea.analytics.cfg table after these analytics.

• aggClause. This is a q-language parse tree which defines the calculation for each analytic.

  Note that we match the aggregation clause to the corresponding analytic / column name by creating a dictionary with analytic as the key and aggClause as the values, as in the example below:

  q

  Copy

  // Create a dictionary to be used in a functional select to calculate our analytics.
  aggDict:(exec analytic from cfg)!(exec aggClause from cfg);
           
  // This dictionary is then used in a functional select, similar to below to generate our analytics:
  ?[data;wc;bc;aggDict]

  Refer to the q documentation for more information on how functional qSQL works.

Grouping analytics where possible ensures best performance as it reduces the amount of operations needed. For examples, calculating 10 analytics in 1 functional select is much more efficient than calculating the same 10 analytics in 10 different functional selects.

The only reasons analytics should not be grouped are:

• Analytics that require different logic (that is, analytics that are as-of join to market data are calculated differently to analytics that are aggregations of market data and so cannot be grouped together in a single operation).

• Analytics that are dependent on the output of other analytics.

  Note

  The analytics are calculated in order, so any analytics that are dependent on others should be added further down the .eqea.config.custom.analytics table than their dependencies.

Example usage

This example demonstrates writing a custom function that makes use of .eqea.util.simpleAnalytics as follows:

• In the example custom configuration file, example.eqeaCustomAnalytics.q, define your config table .eqea.config.custom.analytics, as shown below:

  q

  Copy

  .eqea.config.custom.analytics:flip `analytic`analyticType`funcName`aggClause`marketDataTabName`joinTimeOffset! flip (
      (`myArrivalCalc  ; `mySimpleAnalytic ; `.custom.mySimpleAnalytic  ; (-;`arrivalAskPrice;`arrivalBidPrice) ; ` ; 0Nt);
      (`myEndCalc      ; `mySimpleAnalytic ; `.custom.mySimpleAnalytic  ; (-;`endAskPrice;`endBidPrice)         ; ` ; 0Nt)
      );

• Content of the custom function, .custom.mySimpleAnalytic:

  q

  Copy

  .custom.mySimpleAnalytic:{[OrderAnalyticsRes]
  
      // Run simple analytics that use already existing columns / analytics
      cfg:select from .eqea.config.custom.analytics where analyticType=`mySimpleAnalytic;
      // We can use a utility function `.eqea.util.runSimpleAnalytic` to run analytics that are dependent on columns that already exist in the OrderAnalytics table
      .eqea.util.simpleAnalytics[(`OrderAnalyticsRes`cfg)!(OrderAnalyticsRes;cfg)]
  
      }

Parameter:

Returns:

.eqea.util.tickData.getDataAndAggFromCfg

The .eqea.util.tickData.getDataAndAggFromCfg utility function is used to calculate "get market data and aggregate analytics" - that is, analytics based on aggregations of market data.

This function:

• Derives a where clause from the input OrderAnalytics data. For example, where eventTimestamp within (strikeTime;orderCompletedTime).

• Gets analytic definitions from the aggClause column of the input cfg data.

• Gets the corresponding column names from the analytic column of the input cfg data.

• Takes an optional by clause argument.

• Performs a functional select on the table defined by the marketDataTabName which generally takes the form of:

  q

  Copy

  ?[marketDataTabName;wc;bc;(analytic)!(aggClause)]

• The result of this functional select becomes the value of the column(s) defined by the value of the analytic column in the input cfg data.

For example, the out-of-the-box analytic intervalHigh makes use of the .eqea.util.tickData.getDataAndAggFromCfg utility function to get the max trade price of an instrumentID between the strikeTime and orderCompletedTime values of each order.

This function takes a dictionary as its input. Accepted dictionary keys are outlined in the below table:

Analytics that make use of the .eqea.util.tickData.getDataAndAggFromCfg utility function should have at a minimum the following values populated in .eqea.config.custom.analytics:

• analytic. The name of the analytic you are calculating. This should match a corresponding column name in the OrderAnalytics table.

• analyticType. This is simply a value that can be used to group analytics together.

• funcName. This is the name of the function used to calculate these analytics. The function .fsi.eqea.generateOrderAnalytics calculates order analytics in the order that they appear in the .eqea.analytics.cfg table. This information can help you build out analytics that are dependent on other analytics by placing them lower down the config table than the analytics they depend on.

  For example: The analytic intervalSpread depends on the values of arrivalAskPrice and arrivalBidPrice, which means that it appears in the .eqea.analytics.cfg table after these analytics.

• aggClause. This is a q-language parse tree which defines the calculation for each analytic.

  Note that we match the aggregation clause to the corresponding analytic / column name by creating a dictionary with analytic as the key and aggClause as the values, as in the example below:

  q

  Copy

  // Create a dictionary to be used in a functional select to calculate our analytics.
  aggDict:(exec analytic from cfg)!(exec aggClause from cfg);
           
  // This dictionary is then used in a functional select, similar to below to generate our analytics:
  ?[data;wc;bc;aggDict]

  Refer to the q documentation for more information on how functional qSQL works.

  Note

  For the .eqea.util.tickData.getDataAndAggFromCfg utility function, the aggClause should only contain columns from the market data table specified by the value of marketDataTabName.

• marketDataTabName. This is the name of the market data table you are querying in this analytic.

Example usage

This section provides an example custom function that makes use of the .eqea.util.tickData.getDataAndAggFromCfg utility function.

• In the example custom configuration file, example.eqeaCustomAnalytics.q, define your config table .eqea.config.custom.analytics, as shown below:

  q

  Copy

  .eqea.config.custom.analytics:flip `analytic`analyticType`funcName`aggClause`marketDataTabName`joinTimeOffset! flip (
  
      // These two analytics are intended to show how the query time window can be derived from names of timestamp columns in the OrderAnalytics table e.g. `strikeTime`
      (`intervalMaxBidPrice                 ; `myIntervalAnalytic         ; `.custom.myAggFromCfgAnalytic       ; (max;`bidPrice)    ; `Quote ; 0Nt);
      (`intervalMaxAskPrice                 ; `myIntervalAnalytic         ; `.custom.myAggFromCfgAnalytic       ; (max;`askPrice)    ; `Quote ; 0Nt);
  
      // These two analytics are intended to show how the query time window can be derived from operations on timestamp columns in the OrderAnalytics table e.g. ``` (-; `strikeTime; 00:00:05) ```
      (`intervalMaxBidPriceWiderWindow      ; `myWiderIntervalAnalytic    ; `.custom.myAggFromCfgAnalytic       ; (max;`bidPrice)    ; `Quote ; 0Nt);
      (`intervalMaxAskPriceWiderWindow      ; `myWiderIntervalAnalytic    ; `.custom.myAggFromCfgAnalytic       ; (max;`askPrice)    ; `Quote ; 0Nt);
  
      // These two analytics are intended to show how the query time window can be derived from operations on timestamp and time columns in the OrderAnalytics table e.g. ``` (+; `strikeTime; `bucketDuration) ```
      (`intervalMaxBidPriceDerivedWindow    ; `myDerivedIntervalAnalytic  ; `.custom.myAggFromCfgAnalytic       ; (max;`bidPrice)    ; `Quote ; 0Nt);
      (`intervalMaxAskPriceDerivedWindow    ; `myDerivedIntervalAnalytic  ; `.custom.myAggFromCfgAnalytic       ; (max;`askPrice)    ; `Quote ; 0Nt);
  
      // These two analytics are intended to show how additional filtering logic can be derived from the incoming OrderAnalytics data e.g. add a greater than OR less than filter depending on whether the Order in question is a buy or sell.
      (`intervalMaxPriceWhereClause         ; `myWhereClauseIntervalAnalytic  ; `.custom.myAggFromCfgAnalytic       ; (max;`price)    ; `Trade ; 0Nt);
      (`intervalMaxPriceWhereClause         ; `myWhereClauseIntervalAnalytic  ; `.custom.myAggFromCfgAnalytic       ; (max;`price)    ; `Trade ; 0Nt)
      );

• Content of the custom function, .custom.mySimpleAnalytic:

  q

  Copy

  .custom.myAggFromCfgAnalytic:{[OrderAnalyticsRes]
               
      // Example analytics that are based on aggregations on market data.
      // Here we simply use strikeTime and orderCompletedTime as the bounds of our query window.
      cfg:select from .eqea.config.custom.analytics where analyticType=`myIntervalAnalytic;
      OrderAnalyticsRes:.eqea.util.tickData.getDataAndAggFromCfg[(`OrderAnalyticsRes`cfg`wcList`startTime`endTime`useQueryWindowAdjustmentFactor)!(OrderAnalyticsRes;cfg;();`strikeTime;`orderCompletedTime;1b)];
               
      // Here show how we can derive timestamps using a time column +/- some value.
      cfg:select from .eqea.config.custom.analytics where analyticType=`myWiderIntervalAnalytic;
      OrderAnalyticsRes:.eqea.util.tickData.getDataAndAggFromCfg[(`OrderAnalyticsRes`cfg`wcList`startTime`endTime`useQueryWindowAdjustmentFactor)!(OrderAnalyticsRes;cfg;();(-;`strikeTime;00:00:05);(+;`orderCompletedTime;00:00:05);1b)];
               
      // Here we show how we can derive the query window using a combination of columns.
      // We are using strikeTime + bucketDuration as the startTime and orderCompletedTime - bucketDuration as the end time of our query window.
      cfg:select from .eqea.config.custom.analytics where analyticType=`myDerivedIntervalAnalytic;
      OrderAnalyticsRes:.eqea.util.tickData.getDataAndAggFromCfg[(`OrderAnalyticsRes`cfg`wcList`startTime`endTime`useQueryWindowAdjustmentFactor)!(OrderAnalyticsRes;cfg;();(+;`strikeTime;`bucketDuration);(-;`orderCompletedTime;`bucketDuration);0b)];
  
      // Here we are using a where clause as part of our analytic.
      // Here we generate a list of where clauses which use greater than or less than depending on whether the Order in question is a buy or sell.
      wcList:exec whereClause from update whereClause:(((((`BUY`SELL)!(<=;>=)) orderSideCode),\:(`price)),'limitPrice) from OrderAnalyticsRes where not null limitPrice;
      cfg:select from .eqea.config.custom.analytics where analyticType=`myWhereClauseIntervalAnalytic;
      OrderAnalyticsRes:.eqea.util.tickData.getDataAndAggFromCfg[(`OrderAnalyticsRes`cfg`wcList`startTime`endTime`useQueryWindowAdjustmentFactor)!(OrderAnalyticsRes;cfg;wcList;`strikeTime;`orderCompletedTime;1b)];
               
      // Return our results 
      OrderAnalyticsRes
                   
      };

Parameter:

Returns:

.eqea.util.asof.ajFromCfg

The .eqea.util.asof.ajFromCfg utility function is used to calculate "as-of join analytics" - that is, analytics defined by an as-of join on market data.

For example, the analytics arrivalBidPrice and arrivalAskPrice make use of the .eqea.util.asof.ajFromCfg function as they are defined by the bidPrice and askPrice values from the Quote table at strike time.

This function takes a dictionary as its input. Accepted dictionary keys are outlined in the below table:

Analytics that make use of the .eqea.util.asof.ajFromCfg utility function should have at a minimum the following values populated in .eqea.config.custom.analytics:

• analytic. The name of the analytic you are calculating. This should match a corresponding column name in the OrderAnalytics table.

• analyticType. This is simply a value that can be used to group analytics together.

• funcName. This is the name of the function used to calculate these analytics. The function .fsi.eqea.generateOrderAnalytics calculates order analytics in the order that they appear in the .eqea.analytics.cfg table. This information can help you build out analytics that are dependent on other analytics by placing them lower down the config table than the analytics they depend on.

  For example: The analytic intervalSpread depends on the values of arrivalAskPrice and arrivalBidPrice, which means that it appears in the .eqea.analytics.cfg table after these analytics.

• aggClause. This the name of the column containing the values required for your analytic. This column needs to exist in the table specified by the value of marketDataTabName.

  Note

  For the .eqea.util.asof.ajFromCfg utility function, the aggClause as this is an as-of join, not an aggregation, this value should be an atomic symbol.

• marketDataTabName. This is the name of the market data table you are as-of-joining to.

• joinTimeOffset. This is a timespan that is added to the joinOrderTimeCol when building the dataset used in the as-of join. This can be a simple timestamp value (for example, 00:00:30) or it can be a derived timestamp (for example, (*;`bucketDuration;4))

How the .eqea.util.asof.ajFromCfg function works

The best way to understand how the .eqea.util.asof.ajFromCfg function works is by taking the reversion analytics as an example.

Take for example some reversion analytics:

• Analytic reversionAskPrice_60 has joinTimeOffset = 00:01:00

• Analytic reversionAskPrice_120 has joinTimeOffset = 00:02:00

• Analytic reversionAskPrice_3600 has joinTimeOffset = 01:00:00

The function .eqea.analytics.reversion groups these analytics together by filtering ```cfg:select from .eqea.analytics.cfg where analyticType=`reversion```.

This is then passed into .eqea.util.asof.ajFromCfg as follows:

.eqea.util.asof.ajFromCfg[(`OrderAnalyticsRes`cfg`joinOrderTimeCol`kCols`wc)!(OrderAnalyticsRes;cfg;`orderCompletedTime;kCols;())]

The function .eqea.util.asof.ajFromCfg then:

1. Expands the input dataset based on the joinTimeOffset values.

   • Taking the three reversion analytics outlined above, this means we generate a dataset that has 3 rows for every row in the input OrderAnalyticsRes dataset. This expanded dataset contains:

     • A row with time = orderCompletedTime + 00:01:00 for the reversionAskPrice_60 analytic.

     • A row with time = orderCompletedTime + 00:02:00 for the reversionAskPrice_120 analytic.

     • A row with time = orderCompletedTime + 01:00:00 for the reversionAskPrice_3600 analytic.

   • The expanded dataset (or timeRack) then has Number of output Rows = (Number of Input Rows) * (Number of distinct joinTimeOffset values).

2. Performs an as-of join, joining the dataset output by step 1 to the table specified by the value of the marketDataTabName column in the input cfg data.

3. Pivots the result of the as-of join such that the data is pivoted to a single row per order, where the column names correspond to the values of the analytic column from the input cfg data.

   • This pivot essentially reduces our expanded dataset back to the original number of rows, and ensures the joined values are contained in the correct OrderAnalytic columns.

4. The result is then returned to be joined onto our input OrderAnalytics data.

Example usage

This example demonstrates writing a custom function that makes use of .eqea.util.asof.ajFromCfg as follows:

• In the example custom configuration file, example.eqeaCustomAnalytics.q, define your config table .eqea.config.custom.analytics, as shown below:

  q

  Copy

  .eqea.config.custom.analytics:flip `analytic`analyticType`funcName`aggClause`marketDataTabName`joinTimeOffset! flip (
      (`tradePriceAtStrikeTime            ; `myAsofAnalytic  ; `.custom.asof        ; `price    ; `Trade ; 00:00:00);
      (`tradePriceAtStrikeTimePlus1min    ; `myAsofAnalytic  ; `.custom.asof        ; `price    ; `Trade ; 00:01:00);
      (`tradePriceAtStrikeTimePlus30min   ; `myAsofAnalytic  ; `.custom.asof        ; `price    ; `Trade ; 00:30:00)
      );

• Content of the custom function, .custom.asof:

  q

  Copy

  .custom.asof:{[OrderAnalyticsRes]
           
      // Filter the config for the relevant analytics.
      cfg:select from .eqea.config.custom.analytics where analyticType=`myAsofAnalytic;
      kCols:keys OrderAnalyticsRes;
      // Function `.eqea.util.asof.ajFromCfg` easily performs all aj logic based on inputted config and arguments.
      OrderAnalyticsRes:.eqea.util.asof.ajFromCfg[(`OrderAnalyticsRes`cfg`joinOrderTimeCol`kCols`wc)!(OrderAnalyticsRes;cfg;`strikeTime;kCols;())];
      OrderAnalyticsRes
           
      };

Parameter:

Returns:

Advanced order execution analytics utility functions

The functions .eqea.util.simpleAnalytics, .eqea.util.tickData.getDataAndAggFromCfg and .eqea.util.asof.ajFromCfg have been written to cover the vast majority of custom analytics. However, there may be edge cases that haven't been accounted for.

If a custom analytic encounters an edge case that requires more flexibility than these functions provide, you can switch to using the more advanced utility functions for greater control.

• For more advanced "simple analytics", use the advanced function .eqea.util.simpleAnalyticsWithWc. This function is an extension of .eqea.util.simpleAnalytics and includes the use of a where clause.

• For more advanced "get data and aggregate analytics", use the advanced function .eqea.util.tickData.getDataAndAggregate. This function is a lower-level version of .eqea.util.tickData.getDataAndAggFromCfg, and offers more flexibility but at the cost of requiring more code to set up necessary variables and conditions.

• For more advanced "as-of join analytics", there are three utility functions that can be used together to reproduce the same behavior as .eqea.util.asof.ajFromCfg:

  • .eqea.util.timeRackFromCfg. Use this function to create a time rack for analytics that require values at multiple points. For example, reversion analytics are defined by trade price values at many points in time after orderCompletedTime.

    This function expands the dataset such that 'number of output rows' = 'number of orders' * 'number of timestamps of interest'.

  • .eqea.util.asof.ajTickDataToInput. This function is a lower level as-of join function compared to .eqea.util.asof.ajFromCfg but includes logic to ensure late data is not missed (as could happen with a simple `aj`). For more information, refer to the late data documentation.

  • .eqea.util.pivotByCfg. If .eqea.util.timeRackFromCfg has been used to expand the data set before running .eqea.util.asof.ajTickDataToInput, then .eqea.util.pivotByCfg can be used to pivot the resulting values into their respective columns. After running .eqea.util.timeRackFromCfg you will have 'number of output rows' = 'number of orders'.

.eqea.util.simpleAnalyticsWithWc

The .eqea.util.simpleAnalyticsWithWc utility function is used to apply aggregations based on a where clause. This is a utility function to run an analytic that uses standard operators on existing columns from the OrderAnalytics table.

This function is an extension of .eqea.util.simpleAnalytics and includes the use of a where clause.

This function can be used with the .eqea.analytics.prevailing.cfg table to update null/infinite interval analytic values with the relevant previous non-null/infinite values.

For example, take the analytic intervalHigh_5:

• If .eqea.useIntervalPrevailingValues:1b in the equitiesEa.api.config.q file.

• And if intervalHigh_5 = -0w

• Then .eqea.util.simpleAnalytics is used to replaced the value of intervalHigh_5 with the value of intervalHigh_4

This function takes a dictionary as its input. Accepted dictionary keys are outlined in the below table:

The function ..eqea.util.simpleAnalyticsWithWc uses rows from .eqea.config.custom.analytics to dictate its behavior.

Analytics that make use of ..eqea.util.simpleAnalyticsWithWc should have at a minimum the following values populated in .eqea.config.custom.analytics:

• analytic. The name of the analytic you are calculating. This should match a corresponding column name in the OrderAnalytics table.

• analyticType. This is simply a value that can be used to group analytics together.

• funcName. This is the name of the function used to calculate these analytics. The function .fsi.eqea.generateOrderAnalytics calculates order analytics in the order that they appear in the .eqea.analytics.cfg table. This information can help you build out analytics that are dependent on other analytics by placing them lower down the config table than the analytics they depend on.

  For example: The analytic intervalSpread depends on the values of arrivalAskPrice and arrivalBidPrice, which means that it appears in the .eqea.analytics.cfg table after these analytics.

• whereClause. This is a q-language where clause which defines the conditions to be met to perform the update defined by aggClause.

• aggClause. This is a q-language parse tree which defines the calculation for each analytic.

  Note that we match the aggregation clause to the corresponding analytic / column name by creating a dictionary with analytic as the key and aggClause as the values, as in the example below:

  q

  Copy

  // Create a dictionary to be used in a functional select to calculate our analytics.
  aggDict:(exec analytic from cfg)!(exec aggClause from cfg);
           
  // This dictionary is then used in a functional select, similar to below to generate our analytics:
  ?[data;wc;bc;aggDict]

  Refer to the q documentation for more information on how functional qSQL works.

Parameter:

Returns:

.eqea.util.tickData.getDataAndAggregate

This utility function can be used to calculate analytics based on aggregations on market data.

The start time and end time of the query window for these aggregations is usually derived from the order data.

This function is intended as an alternative to .eqea.util.tickData.getDataAndAggFromCfg, to be used for more advanced use-cases where .eqea.util.tickData.getDataAndAggFromCfg is not flexible enough.

This function takes a dictionary as its input. Accepted dictionary keys are outlined in the below table:

Example usage

This example demonstrates writing a custom function that makes use of .eqea.util.tickData.getDataAndAggregate as follows:

• In the example custom configuration file, example.eqeaCustomAnalytics.q, define your config table .eqea.config.custom.analytics, as shown below:

  q

  Copy

  .eqea.config.custom.analytics:flip `analytic`analyticType`funcName`aggClause`marketDataTabName`joinTimeOffset! flip (
      (`intervalMaxPrice                 ; `myAggAnalytic         ; `.custom.myAggAnalytic       ; (max;`price)    ; `Trade ; 0Nt);
      (`intervalMaxPrice                 ; `myAggAnalytic         ; `.custom.myAggAnalytic       ; (max;`price)    ; `Trade ; 0Nt)
      );

• Content of the custom function, .custom.myAggAnalytic:

  q

  Copy

  // The function .eqea.util.tickData.getDataAndAggregate is more flexible than .eqea.util.tickData.getDataAndAggFromCfg but this comes at the trade-off of needing to write more q-code.
  // Therefore it is only recommended for use cases where .eqea.util.tickData.getDataAndAggFromCfg may not be sufficient.
  // To use .eqea.util.tickData.getDataAndAggregate we need to iterate across each row in our order data.
  // And so, we need to write two custom functions to make use of it. One main function and one sub-function to iterate.
  .custom.myAggAnalytic:{[OrderAnalyticsRes]
               
      // Filter the config for the analytics we want to generate. These analytics should correspond with columns in the OrderAnalytics table.
      cfg:select from .eqea.config.custom.analytics where analyticType =`myAggAnalytic;
               
      // We need to match the analytics with their corresponding aggregations by generating a dictionary from our config.
      aggDict:(exec analytic from cfg)!(exec aggClause from cfg);
               
      // Get our keys so we can re-key our result before returning the data.
      kCols:keys OrderAnalyticsRes;
           
      // Iterate across the table of orders.
      OrderAnalyticsRes:kCols xkey .custom.myAgg.eachAnalytic[cfg;aggDict;] each 0!OrderAnalyticsRes;
      OrderAnalyticsRes
               
      };
  
  // Define the function that iterates across each Order.
  .custom.myAgg.eachAnalytic:{[cfg;aggDict;orderDict]
               
      // When we iterate across the table OrderAnalyticsRes each row of the table becomes a dictionary with columns!values until the function has iterated on the whole table.
      // We represent this in or function as the argument orderDict.
      id:orderDict[`instrumentID];
               
      // Get the time values from our order
      st:orderDict[`strikeTime];
      et:orderDict[`orderCompletedTime];
               
      // Market data table to query.
      tabName:first exec marketDataTabName from cfg;
           
      res:.eqea.util.tickData.getDataAndAggregate[(`tickTabName`id`tw`wc`bc`aggDict`useQueryWindowAdjustmentFactor)!(tabName;id;(st;et);();0b;aggDict;0b)];
      res:flip res;
      // Ensure one item lists are atomic.
      res:key[res]!first each value res;
               
      // join the results to our dictionary:
      orderDict:orderDict,res;
               
      orderDict
      };

Parameter:

Returns:

.eqea.util.timeRackFromCfg

The .eqea.util.timeRackFromCfg utility function creates an expanded dataset (or timeRack) from inputData based on the joinTimeOffset column from the cfg argument.

The time rack table is generated using joinOrderTimeCol + exec distinct joinTimeOffset from cfg where marketDataTabName=tickTabName.

This function takes a dictionary as its input. Accepted dictionary keys are outlined in the below table:

Example usage

This example demonstrates writing a custom function that makes use of .eqea.util.timeRackFromCfg as follows:

• In the example custom configuration file, example.eqeaCustomAnalytics.q, define your config table .eqea.config.custom.analytics, as shown below:

  q

  Copy

  .eqea.config.custom.analytics:flip `analytic`analyticType`funcName`aggClause`marketDataTabName`joinTimeOffset! flip (
      (`tradePriceAtStrikeTimeAlt            ; `myAdvancedAjAnalytic  ; `.custom.advancedAj        ; `price    ; `Trade ; 00:00:00);
      (`tradePriceAtStrikeTimePlus1minAlt    ; `myAdvancedAjAnalytic  ; `.custom.advancedAj        ; `price    ; `Trade ; 00:01:00);
      (`tradePriceAtStrikeTimePlus30minAlt   ; `myAdvancedAjAnalytic  ; `.custom.advancedAj        ; `price    ; `Trade ; 00:30:00)
      );

• Content of the custom function, .custom.advancedAj:

  q

  Copy

  .custom.advancedAj:{[OrderAnalyticsRes]
     
      // Get the relevant config entries
      cfg:select from .eqea.config.custom.analytics where analyticType=`myAdvancedAjAnalytic;
      kCols:keys OrderAnalyticsRes;
     
      // Our analytic is concerned with Trade data
      tabName:`Trade;
  
      // Create a time rack of all order data plus all configured offsets.
      timeRack:.eqea.util.timeRackFromCfg[(`inputData`tickTabName`cfg`joinOrderTimeCol`kCols)!(OrderAnalyticsRes;`Trade;cfg;`strikeTime;kCols)];
  
      // Select the tick data columns we need for the join - need the primary sym and time columns minimum for efficiency.
      tickTimeCol:`eventTimestamp;
      tickIdCol:`instrumentID;;
     
      // Setup a list of columns which includes a list of the primary sym and time columns of the parted data, and the value column we want to find in the join (.e.g. price)
      qCols:distinct tickIdCol,tickTimeCol,exec distinct aggClause from cfg;
     
      // Using the numLookBackDays variable we can retry the aj for `date - N` attempts.
      // This is particularly useful for less liquid instruments.
      // This also helps eradicate an edge case where orders traded on, or near midnight and so do not have corresponding market data in their expected date partition.
      numLookBackDays:5;
     
      // Join tick data to our timeRack
      res:.eqea.util.asof.ajTickDataToInput[(`inputData`tickTabName`tickDataCols`numLookBackDays`wc)!(timeRack;tabName;qCols;numLookBackDays;())];
  
      // Pivot our dataset to ensure we have the required tick data values aligned with each order, under relevant column names
      pivValues:exec distinct aggClause from cfg;
      args:(`data`cfg`timeCol`idCol`kCols`pivValues)!(res;cfg;`strikeTime;tickIdCol;kCols;pivValues);
      res:.eqea.util.pivotByCfg[args];
     
      // Join results to our input data set and return
      OrderAnalyticsRes:(kCols xkey OrderAnalyticsRes) lj res;
      OrderAnalyticsRes
  
      };

Parameter:

Returns:

.eqea.util.asof.ajTickDataToInput

The .eqea.util.asof.ajTickDataToInput utility function is an efficient as-of join, joining an input data set to tick-data.

Refer to the .eqea.util.timeRackFromCfg section for an example on how to use this utility function.

Parameter:

Returns:

.eqea.util.pivotByCfg

The .eqea.util.pivotByCfg utility function pivots a dataset, using the contents of .eqea.analytics.cfg to define output column names.

Refer to the .eqea.util.timeRackFromCfg section for an example on how to use this utility function.

Parameter:

Returns: