Logout / Access Other products Drop Down Arrow
Get live help Monday-Friday from 7AM-7PM ET (12PM-12PM GMT)  •  Contact Us
Check out our new FAQ section!
RSS Feed
TitleUsing Data Buffet: Basket formulas
AuthorPhillip Thorne
Question

How can I create custom formulas in a Data Buffet basket to perform arbitrary computation on native series?

Answer

A Data Buffet basket can contain four types of expressions:

  • Single mnemonic (one series)
  • Single concept using geo list (may expand to multiple series)
  • Wild card (may expand to multiple series)
  • Formula (one synthetic series)

A formula mathematically combines one or more native time series to emit a single synthetic time series.  Some of the operations can be performed identically post-download, but to do so within Data Buffet can simplify your workflow.  Formulas also permit time series manipulation for which external spreadsheets are ill-suited:

  • Convert operands to a single compatible frequency
  • Index (extract) single periods
  • Use period lags and leads

To differentiate a formula from the other three types of expression, it must be enclosed in parentheses:

  • Single mnemonic: ET.PA
  • Formula: (ET.PA + ET.DE)
  • Wrong (lacks parentheses): ET.PA + ET.DE

Formulas can be used in View, Basket, Chart and Map modes.

Caveats

Formulas cannot embed wild cards or geo lists:

  • Wrong: (ET.new_england.lst + ET.mid_atlantic.lst)
  • Wrong: (FET.M^^^ / 12)

Formulas cannot be used as geo lists:

  • Geo list: (ME+NH+VT+MA+CT+RI)
  • Formula using illegal geo list: (RET2371Q.my_agg.lst + RET2373Q.my_agg.lst)

Data Buffet formulas do not behave like the "aggregate functions" of SQL.  You cannot "specify a bunch of series" and then operate on them as a group.  Instead, if you need to "cross-aggregate" "set of concepts" with "set of geographies," you can download the series to Microsoft Excel with Moody's Analytics Power Tools, and then write custom worksheet formulas around them.

Operators

  • Addition, subtraction, multiplication and division (+ - * /). 
  • The exponentiation operator is (**)
  • Date indexing (extracting a single period from a time series) uses square brackets, e.g. ET.US[Jan1990]
  • Control order of execution with parentheses. 
  • Formulas are case-insensitive.
  • Formulas ignore whitespace.

Dates can be expressed in these formats:

  • Daily and weekly = 25dec2010
  • Monthly = Jan1990 or 1990m1
  • Quarterly = 1990q2
  • Annual = 2010

Debugging

Formulas most often fail because:

  • The enclosing parentheses were omitted.
  • The mnemonics are mis-spelled, so the series does not exist.
  • The series has been renamed, so the mnemonic is no longer applicable.
  • You do not have access to the specified series.
  • Values do not exist (i.e., are ND) for part of the date range.
  • You combined series with different frequencies without a CONVERT function.

Do not try to write a complicated formula all at once; instead, "grow" it. Start with a basket that downloads each component series (operand) separately; so you can verify your access and the date range. Then build each sub-expression separately.

Examples

Aggregate (sum) employment for three areas:

(ET.DE + ET.NJ + ET.PA)

Compute state-level productivity by worker (i.e., real output divided by payroll employment):

(RGDP$Q.AL / RETQ.AL)

Index employment to a base period, to wit, January 1990:

(100 * ET.US / ET.US[Jan1990])

CAGRs of a 10-year T-note (monthly series) over 3 years (36 months):

((IRGT10YM.US[T] / IRGT10YM.US[T-36])**(1/3) - 1)