Time Series Fields

Owned by Becky Weinberg (Unlicensed)
Last updated: Jul 26, 2023 by Pamela Totas (Deactivated)
6 min read

Time Series are custom field types you use to enhance the data which OLAP reports can deliver to the Data Mart. Times Series fields allow data to be calculated and displayed for results over time. In Times Series custom fields, data is calculated and displayed at the security level. For example, a report may be required to show the mean price of a security for the past 3 months from the date of the report effective date. Using Time Series fields, your company can report more meaningful data which previously was difficult to attain. Used with the Data Mart, times series data is calculated and stored in the Data Mart. This data is used by reporting. Reporting displays this data for use by reporting to display this data and calculates statistics based on the time series information. For example, using time series fields, you can report in the standard Deviation of maximum P/E ratios during the last year.

Time Series Fields Underlying Data Types

You can base Times Series fields on Holding and Security data. Security data includes Reference, Price, Ratings, and Analytics. You can also base Times Series fields on detailed calculation, and inference fields if those fields are based on holding and securities.

Times Series Fields, while based on these underlying data types, can be included within other custom fields. These custom fields include:

  • Rollup

  • Detail Calculation

  • Inference

  • Rollup Inference

  • Rollup Calculation

Although a rollup can be based on a Times Series, Time Series cannot be based on a rollup.

For example, given monthly prices for the holdings of Port A below, the average price can be calculated for each holding during the 3 month period.

 

Price- January

Price- February

Price - March

Average Price per Quarter

Port A

 

 

 

 

Security X

95.00

95.25

95.50

95.250

Security Y

73.00

74.50

75.00

74.167

Security Z

85.25

86.00

88.45

85.233

You can also calculate weighted average of the times series at the group (portfolio) level.

 

Price - January

Price - February

Price - March

MV - March

Average Price per Quarter

Weight Average for Quarter

Port A

 

 

 

 

 

82.0719

Security X

95.00

95.25

95.50

1500.00

95.250

95.250

Security Y

73.00

74.50

75.00

2500.00

74.167

74.167

Security Z

85.25

86.00

88.45

3500.00

85.233

85.233

The Eagle solution uses the Market Value field based on the report date to calculate the correct values.

Using Rollup fields, including Rollup Calculation and Rollup Inference, allows for enhanced data to be stored in the Data Mart group models. While Times Series Fields are based on detail level information, Rollup fields of a times series allow group level information to be stored.

Eagle Reporting only calculates the rollup aggregations of the times series results and not the constituent data of the series.

Grouping Rule

You can use Times Series fields with Grouping. Perform careful analysis to ensure that Time Series fields and Grouping are being used in a way that makes business sense.

Use Business Calendar and Date Rules

Since Times Series Fields aggregate data across time, each field must be configured to evaluate a date range. The date range can consist of the same two dates to fetch a single data point or two dates to fetch multiple data points.

When configuring Times Series Fields for use with a Business Calendar, you select either the Date Rule Period or the Business Calendar option:

  • To select the Date Rule Period, click Click here to define your reporting period options... These options are used to determine the range of dates for the engine to fetch the required data for the aggregation.

  • To select a Business Calendar, select the Use Business Calendar checkbox.

Business Calendar and Date Rule Example

For example, you configure a date rule to use a date range of the prior month begin to prior month end using business dates for the range delineation. Month begin should be a business day and Month end should be a business day. In the following table, the date evaluated by the Period option for Business Days is January 2nd and January 30th.

If you have set the Business Calendar option to On in the fields, the data used in the aggregation only includes those dates that are business dates. In this table, the shaded dates are the days included.

January

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

 

 

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

  • 18

19

20

21

22

23

24

25

26

27

28

29

30

31

Set Up Time Series Fields

You create and edit Time Series field on the Time Series Fields dialog box. Complete the following procedure to set up Time Series Fields.

  1. To access the Time Series Field dialog box, open the Custom folder, located on the Field Selector window.

  2. Open the Time Series folder on the Field Selector window.
    You see the Time Series field dialog box.

  3. Complete the following options on the Time Series Field dialog box and click OK.

Option

Description

Name

Enter a name for the Time Series Field

Precision

Select a Precision value from the dropdown list.

Comments

Enter a comment regarding the Time Series Field.

Field Options

Click to select Field options such as report type mapping, publishing, and inventory of fields. Set the parameters used to define the Range of dates used.

Do not allows others to edit this field

Select this checkbox if you do not want other users to be able to edit this field, even if their user permissions are set to allow them to edit other user's field attributes.

Effects

This populates the effects of the field. You must configure Category and Based on Field.

Category

  • This effect determines the Aggregation type of the field. Categories include:

  • Sum. Find data within a date range and sum the results.

  • Fetch. Fetch a value as of a certain date.

  • Fetch (Observation Date). Show the date of the observation fetched.

  • Maximum. Show maximum of all values found within a date range.

  • Maximum (Observation Date). Show the date of the latest observation if multiple like maximum values exist.

  • Minimum. Show minimum of all values found within a date range.

  • Minimum (Observation Date). Show the date of the latest observation if multiple like minimum values exist.

  • Mean. Show average of all values found within a date range.

  • Median. Show median of all values found within a date range.

  • Product. Multiply the values over time.

  • Standard Deviation. Calculate the dispersion around the value in the set.

  • Day Count. Count the number of days based on period options.

  • Observation Count. Count the number of observations found within a date range.

  • Harmonic Mean. Calculates a specialized average appropriate for rates found within a date range.

  • Based on Field. This effect determines which underlying field will be calculated across time.

Period Options

In this section, click Click here to define your reporting period options... to select a time period to be used in calculations. You can select:

  • Values in prior period (days, months, years)

  • Values between two dates.

  • Values based on a date rule.

  • Values using To-Date (fiscal, month, quarter, week, year).

Use Business Calendar

When you select this checkbox, the Business calendar in the drop down is checked when determining values to accumulate.

Exclude Missing Values

When this checkbox is selected, if there are missing values in the accumulate data set, those missing values are not counted in the aggregation.

Do not show if missing observations

When this checkbox is selected, if there are missing values in the data set, then no values are calculated and displayed for the given securities.

Time Series Fields Restrictions Per-Share Data

Times Series

Note that some of your analytical data fields may not be appropriate candidates for Eagle's time series fields. These include the per-share fields for equities, such as price, earnings per share and dividend per share. This is because these fields are impacted by capital changes that companies experience, like splits and stock dividends that change the definition of a share. Eagle's prices are not adjusted for splits, and normally, security analytics are loaded as unadjusted. It is not meaningful to include both pre- and post-split data in a time series calculation. Fortunately, many important equity analytics such as beta, and financial ratios such as P/E do not have a share dimension and are unaffected by splits. The same is true of index levels, which are normally loaded as prices.

Data Mart

You may be able to obtain split-adjusted per-share data through a direct vendor load or exporter approach. If so, you can analyze that data using time series fields. However, if you want to build such fields in Data Mart, your data will reflect different share definitions every time a security has a capital change. To solve this issue, you must re-build all per-share fields, for all past dates, for every required date (perhaps only monthly depending upon reporting requirements) in order not to miss a split in any stock in the Mart. This may be facilitated by adding an extension to the Mart's Security Details table and dedicating it to your per-share fields, building only that extension since inception on a selective-fields basis.