Frequency conversion

Overview

The Frequency conversion analysis converts all your time series into one common frequency. This ensures consistent, comparable calculations and presentations of your work.

You can convert the frequency directly in Series list, but this means that the selected frequency will be applied to the entire document prior to any analyses. The main use case for the frequency conversion analysis, then, is when you want to change the frequency of your series after having applied some analysis to the raw data.

Settings

Frequency

Set the common observation frequency you want for your series.

Observations

Select which points in time should be included in the output.

  • Any series outputs only points in time for which at least one series has data
  • All points outputs at each point in time based on the frequency, even if there are skipped periods values

Date range

Specify ranges of dates that you would like to limit your output to. By default, the entire range of the longest series will be the range of your output.

When you want to convert a series to a lower frequency, a full calendar of observations is needed. For instance, to convert a daily series to monthly, a full calendar of daily observations per month is necessary to produce a monthly value. If there are only partial periods available at the start or end of range, you won’t get a value for that month.

To lower frequency conversion method

Select the method which will be used to change series to a lower frequency than the original (one value must be calculated from the many values in the period). The options available are:

  • Automatic, selects the method automatically based on the properties of the series
  • Average, uses the average all values in each period
  • First, uses the first value in each period
  • Flow, aggregates all values in each period
  • Highest, uses the highest value in each period
  • Last, uses the last value in each period
  • Lowest, uses the lowest value in each period
  • Percentage change, aggregates the percentage change of each period
  • Percentage change conditional, uses the percentage change method when it makes sense with the series properties

Partial period to lower frequency method

Select how you want to adjust incomplete periods when converting a series to a lower frequency. The methods available are:

  • Do not extend, only include full periods of lower frequency
  • Automatic (name_of_method), automatically select the method based on series properties
  • Repeat, use the last (or first) value to extend the incomplete periods
  • Flow, use the last (or first) partial average to extend the incomplete periods
  • Rate of change, use the previous (or next) year’s rate of change to extend the incomplete periods
  • Zero, extend the incomplete periods with zeroes

To higher frequency conversion method

Select the method which will be used to convert series to a higher frequency than the original (many values must be calculated from one value in each period). The methods available are:

  • Automatic, selects the method automatically based on the properties of the series
  • Cubic interpolation, uses cubic interpolation over the period
  • Distribute, distributes the value over the period
  • Linear interpolation, uses linear interpolation over the period
  • Percentage change, distributes the percentage change over the period
  • Percentage change conditional, uses the percentage change method when it makes sense with the series properties
  • Pulse, sets only the value for the first observation in the period
  • Quadratic distribution, uses quadratic interpolation to distribute the value over the period
  • Same, uses the original value for all periods

Example

Changing the frequency of a series

In this document, we wanted to calculate the average daily performance per year of the S&P 500. We need the series in daily frequency to calculate the daily performance using the rate of change analysis. Then, we used the frequency conversion analysis to transform the daily performance series into an annual series, using average as the conversion method.

(Setting the frequency to annual in Series List won't allow us to calculate the daily performance, as the data will only contain one observation per year prior to any calculations.)

Questions

Application shows ridiculously high value after using Partial period's Repeat

Let's say you have Monthly series and you use Partial period: Repeat. Empty months are filled in with last available value. That's how it should be. Next you want to change frequency from Monthly to Annual - most series' metadata is set to use 'Last' value, but some are set to 'Flow' method meaning they will aggregate all values within a year. In such cases we recommend to use Partial period: Zero.

You can see and change series settings under Frequency conversion analysis:

How can I mimic frequency conversions using formula language?

To higher frequency

To mimic the methods used to convert a series to higher frequency, you can use the At() formula which returns the value in the series at the specified observation number.

At(series, observation)

Here are a few examples on how to use the formula:

At(series, StartOfYear())

Sets all observations for every calendar year to the first observation of each year.

At(series, EndOfYear())

Sets all observations for every calendar year to the last observation of each year.

At(AggregateSumAnnual(fx:s1), EndOfYear())

Aggregates the observations within a calendar year which means the last value of each year will be the annual aggregate. The At() formula then sets all the observations for every calendar year to the last value of each year.

At(Mean(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the mean value of each year.

At(High(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the highest value of each year.

At(Low(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the lowest value of each year.

To lower frequency

To mimic the methods used to convert a series to lower frequency, you can use the if() and FillNullWithLinear() formulas.

To get Annual value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfYear() | Counter()=EndValid(fx:s1), fx:s1, Null()))

To get Quarterly value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfQuarter() | Counter()=EndValid(fx:s1), fx:s1, Null()))

To get Monthly value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfMonth() | Counter()=EndValid(fx:s1), fx:s1, Null()))

For more information: Built in formulas and functions

Tags: frequencyFrequency conversion