How can I use the Data Buffet CONVERT function in a basket for precise control when changing the frequency of a time series, or to extract the period-end value?
Data Buffet is a portal to a time series database, which among other functions, permits easy conversion between frequencies (unlike a relational database or spreadsheet). You can convert from the native frequency to a higher frequency (sometimes called "up-converting," which is mathematically disaggregation or interpolation) or to a lower frequency ("down-converting," which is aggregation and has the effect of smoothing); you can compute the average or sum over a period; or extract either the beginning or end value from a period.
You can use the "Frequency" drop-down control in the Basket module to convert using default settings. For additional flexibility, you will need to write a Data Buffet formula using the CONVERT function.
(Similar drop-down controls are provided by the View, Chart and Map modules, and in the Moody's Analytics Power Tools series wizard. Formulas can be used in all four. Because frequency conversion is a purely mathematical process, the output is not guaranteed to satisfy all topical constraints; please sanity-check before use.)
To mark an expression as a formula, enclose it in parentheses. Case and whitespace are ignored. Many of the keywords can be abbreviated. Wild cards and geo lists cannot be used with formulas.
There are two forms of the CONVERT function, with two or four arguments:
(Convert(mnemonic, frequency, technique, observed))
- Mnemonic = the series you're converting.
- Frequency = Annual, A, Quarterly, Q, Monthly, Weekly, etc.
- Technique = Cubic, Constant, Discrete, Linear. For most purposes Cubic is the best choice.
- Observed = Average, Begin, End, Sum. This overrides the attribute on the series.
The "observed" attribute
Every Data Buffet time series has an "observed" metadata attribute which controls how frequency conversions (i.e., aggregation and disaggregation) apply. It is used by the drop-down controls, and is listed in Mnemonic 411. In general, "stock" measures (balance, inventory, volume, etc.) can be converted as END or AVERAGE, and "flow" measures (transactions) as SUM. When possible, we assign per the source's policy (which we can determine if the same variable is reported at multiple frequencies). Read more about the meaning of the "observed" attribute.
Some series should not be converted; they are statistically valid only at their native frequency. Because we cannot present conversion, we assign an "observed" attribute that produces the "least wrong" answer. For such advisories, please check Mnemonic 411 for written background (for example: the MBA National Delinquency Survey) or a specific FAQ (for example: RealtyTrac foreclosure counts).
Smooth the daily S&P 500 stock price index to a monthly average:
(Convert(SP500D.IUSA, MONTHLY, CUBIC, AVERAGE))
Extract the last daily value of each month:
(convert(SP500D.IUSA, m, discrete, end))
Extract the first monthly value of each quarter:
Compute the monthly sum of daily trading volume on the NYSE:
(convert(NYGVOLD.IUSA, M,dis, SUM))
Convert a quarterly forecast series (based on monthly historical data) to monthly. However, this will not and cannot reconstitute the original irregularity in the monthly historical driver (see related article).
Smooth a monthly series to an average over the standard January-December calendar year (using the default "observed" attribute of the series):
Smooth the monthly U.S. federal budget deficit over the federal fiscal year, which is October to September:
Want to change the observed attribute from AVERAGE to END? This does not work, because the input is already a monthly series:
(convert(SP500AM.IUSA, m, discrete, end))