July 2013 -- How do I get started with Moody's Analytics Power Tools Version 8 in Excel?
The Moody’s Analytics Power Tools are a triad of Microsoft Office add-ins that transfer Data Buffet content to Microsoft Excel, PowerPoint and Word, with all the flexibility and familiar workflow that provides. Version 8 provides several new abilities (see introduction), and its basic functionality (time series in Excel) has been streamlined.
If you want to program a custom solution that pulls series data and metadata, the Data Buffet API (2017) is a better solution, since Power Tools (2013) exposes only part of its functionality as an Excel worksheet function.
Install for Excel
1. To start, go to DataBuffet.com » menu:Tools » Power Tools. Please read the instructions and the installation guide. You will need administrative permission on your Windows account.
2. Once installed in Excel, you should see a new ribbon labeled Power Tools. Open the Account dialog and enter your Data Buffet credentials (email and password).
Your first enhanced basket
3. Begin by enhancing a basket.
- On DataBuffet.com, in the basket editor, select Options » Fields » General » Excel Add-In Function (Excel 5.0 Only), which will associate our ECONBUFFET worksheet formula with each time series.
- Disregard the "Excel 5.0 Only" in the above label -- in fact, you can use the Excel 5.0, 2000, or 2007/2010 output file types (Options » File » File » File Type).
- After any changes, press the grey Apply button (and, if in Power Editor mode, the Save menu-button).
- Press Run, wait for the blue box that advises completion, and download.
4. When the basket output opens in Excel, the formula will appear in the first row or column, with the remaining metadata fields and data values unchanged (i.e., the same as in an unenhanced output file). The visible text will resemble:
ET.US|1/1/2000|1/1/2005|0|8948544|0|0|2013-06-18T08:50:52Z
5. To see Power Tools in action, delete the data values and use Force Refresh to resurrect them.
- Press Force Refresh » All.
- If you see “Webservice Error” instead, you probably mistyped the credentials in Step 2; if necessary, use our password reset page.
- Highlight a particular series or value by changing the font, color or background; leave a note to yourself in an adjoining blank cell. Power Tools respects your changes even it rewrites the values.
6. Save your file ... exit ... reopen a week later ... and refresh. If the data changed, you’ll see it, with no need to visit Data Buffet to re-run and re-download a saved basket.
Staying within Excel
7. Select an ECONBUFFET cell and use Edit Selection to adjust the retrieval. The Series Wizard generally mirrors the basket editor’s Options panel: series, date range, frequency and transformation, orientation, and optional metadata fields. (Try to avoid combinations that obscure meaning.)
8. To insert a new ECONBUFFET formula, press Series Wizard.
- You can specify a mnemonic directly, or pick from a catalog tree (much as on Data Buffet).
- Go ahead and add multiple series (we have greatly streamlined this process since Version 7).
- The new series (formula, metadata and values) will immediately populate to the worksheet.
9. For maximum power, tinker "under the hood." For example, Step 4 will appear in the Excel formula bar as:
=EconBuffet("ET.IUSA","1/1/2000","1/1/2005",0,8948544,0,0,"2013-06-18T08:50:52Z")
Left to right, the parameters are:
- 1. Mnemonic or formula
- 2. and 3. Date range: start and end
- 4. Retrieve last N periods; zero for "all"
- 5. to 7. Don't touch these
- 8. Time stamp
Fields 2, 3 and 4 work with 6, which encodes the options. In particular, depending how you set the "Range Type" field, different combinations will be relevant. If "Start and End Date", it's 2 and 3; if "Start Date", only 2; if "Previous Periods", then 4; etc. The only way to work with this is to create a prototype formula in the Series Wizard and configure to your liking; you can then duplicate the formula and alter whichever parameters are relevant.
The formula is self-contained and can be moved anywhere; just be mindful of the range that will be overwritten when you execute, by the data values, date labels and metadata header fields.
You should also know that
- The Series Wizard won’t accept wild cards or geo lists. To fetch a large number of related series, start with a basket (read more) or use the catalog mode.
- The “refresh” commands are optimized to skip series that haven’t been updated recently (relative to the time stamp parameter of ECONBUFFET). If you’re not sure, or you need to repopulate emptied cells, use the “force refresh” commands.
- You can refresh a single series (by selecting the ECONBUFFET cell), a worksheet, a single workbook, or all open workbooks.
- Want to refresh immediately when you open a workbook, or instantly when you edit an ECONBUFFET formula? Specify on the Options dialog.
- Power Tools cannot refresh on a schedule, or when Excel is closed. For such offline functionality, feed a software process with a scheduled basket delivery via email or FTP.
- On the Account dialog, the “Concurrency violation?” hyperlink solves the problem of leftover log-ins elsewhere.
- The message “Series found: access denied” means you don’t subscribe to the series (read more).
- You can specify a formula instead of a single native series, using basket formula syntax, e.g., (ET.PA+ET.DE).
- To obtain Excel-compatible percentage values, use a formula. As specified in the Series Wizard, the transformations return literal percentages, e.g., “17.1 percent” as 17.1. To get 0.171 use an explicit formula and rescale, e.g., (PCHY(ET.US)/100).
See also
Active functionality
Prior functionality