Debra Dalgleish - Contextures Blog

Excel Economic Data Add-in: FRED

How bad is the economy? With FRED, the free Excel add-in from the St. Louis Federal Reserve, you can easily download and and analyze economic data.

Install the Add-In

Just download and install the FRED Excel Add-in, then select and download data. Then, start your analysis:

choose a data manipulation (i.e., growth rate)
specify a date range (i.e., 1960:Q1 to 2010:Q4)
aggregate data to lower frequency (i.e., weekly to monthly)
search for data, and
browse the most popular data series.

Getting Started

If you’re not familiar with the FRED website, take a quick look at the FRED Add-in’s user guide, to help get started.

The key to pulling data is to enter a series name, so if you don’t have those memorized, select one of the Popular Data options, to get its Series ID.

FRED03

Or, use the Data Search button to find the series that you need. For example, I clicked the button, then typed Canada as the search term. This took me to the FRED website, showing a list of economic data series.

I found one that looked interesting, and pasted its ID into cell A1 on the worksheet.

Pull the Data

After you’ve entered a Series ID in cell A1, you can enter a Data Manipulation code in B1 and Frequency Aggregation code in cell C1. Or, leave those cells blank.

FRED04

Click the Get FRED Data button, and the data downloads onto the worksheet – ready to analyze.

FRED01

Make a Chart

The FRED Add-in makes quick charts too – just click the Build Graph button, after you download the data.

FRED02

Have You Tried FRED?

Have you used the FRED Add-in for Excel? Did you like it? Wish it had more features? The FRED website talks about adding more features in future versions, so if you have some ideas, let the FRED people know!

______________

Excel Loan Payment Calculator

Can you afford that new car? Or maybe you loaned money to one of your kids, and you want to calculate a repayment schedule. (Oh yes, they will stick to the plan, without fail.)

To help you figure out the payment amounts, here is a nifty Excel loan payment calculator. (The kids will think you’re cool when you say “nifty”.)

Continue reading “Excel Loan Payment Calculator”

Add Line Break in Excel Formula

It’s easy to add a line break when you’re typing in an Excel worksheet. Just click where you want the line break, and press Alt + Enter. But how can you add a line break in an Excel formula?

Continue reading “Add Line Break in Excel Formula”

Excel Filter Macro: Shark Week

It’s hard to believe that Excel Advanced Filter Week is almost over. I hope that the three filter-feeding shark species, and you, have enjoyed this tribute to Discovery Channel’s Shark Week.

Today, we’ll see how to record a macro while using the Advanced Filter, and edit that macro, so it automatically adjusts if the data changes.

Continue reading “Excel Filter Macro: Shark Week”

Excel Filter for List Items: Shark Week

On Monday, we declared this Excel Advanced Filter Week, in honour of the three filter feeding shark species. Who said Excel wasn’t exciting?

Continue reading “Excel Filter for List Items: Shark Week”

Excel Filter for Blanks: Shark Week

whale shark filter feeder Last year, we celebrated the Discovery Channel’s Shark Week, by using the LARGE and FLOOR functions.

This year, we’ll pay tribute to the three known species of sharks that are filter feeders, by declaring this Excel Advanced Filter Week.

Yes, we’ll have three fun-filled, action-packed days of Excel filtering fabulousness – one day for each filter feeding shark. Please hold your applause until all three articles have been posted.

Advanced Filter Criteria Range

We’ll kick off the week’s celebrations by filtering rows with missing data (blank cells) to a different worksheet.

When you’re using an Advanced Filter, usually you would enter a heading, and one or more criteria, in a criteria range, like the one shown below.

In this example, you would be filtering the customer order list for any orders with Cookies as the product.

Blank Cells in Data

However, if you want to filter orders with a blank cell for Product, you can’t just leave the criteria range blank.

A blank criteria cell is interpreted as “No criteria”, so all the records would pass through the filter. That might be fine for a shark, but not for an Excel report.

Filter for Blanks in Advanced Filter

Instead of leaving the criteria cell blank, you can use a formula, to check for empty cells. In this example, the first product data is in cell C2, so the formula is:

=C2=””

The two double quote marks represent an empty string, so if C2 is not blank, the formula result is FALSE.

Only the records that calculate to TRUE would pass through the filter.

AdvancedFilterBlank03

Remove the Criteria Range Heading

If you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings.

You can either clear the heading cell in the criteria range, or type a different heading.

I usually clear the heading cell, because that’s quick and easy!

AdvancedFilterBlank04

Run the Advanced Filter

After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.

In this example, the filter is started from the Blank Orders sheet, and the list and criteria range are on the Orders sheet.

Download the Advanced Filter Blanks Workbook

To see the sample data, and test the filter, you can download the Advanced Filter for Blanks sample workbook. The file is in Excel 2007 format, and is zipped.

Watch the Advanced Filter for Blanks Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel video tutorial.

___________________

Excel Function Friday: Average Top 10 Scores

If you have a worksheet with 20 scores listed, how can you calculate an average top 10 scores? And if there are only 11 scores, can the formula automatically adjust, to average just the top 4 scores?

Continue reading “Excel Function Friday: Average Top 10 Scores”

How to Find MIN IF or MAX IF in Excel

When you were first learning how to use Excel, you quickly discovered the basic Excel functions, like SUM, COUNT, MIN, MAX, and AVERAGE. Now you’re ready for advanced calculations, like how to find MIN IF or MAX IF in Excel.

Continue reading “How to Find MIN IF or MAX IF in Excel”

No Duplicates in Multiple Selection Excel Drop Down

Thanks to an email question from Leslie, I’ve done another variation on the Data Validation Multiple Selection sample.

Leslie is scheduling training courses, and has a table with dates across the top, and course names down the side. One or more of the instructor names can be selected for each training session.

However, Leslie wants to prevent an instructor’s name from being selected twice on the same day. For example, on Date 01, in the screen shot above, Bob Smith and Mary Jones are each booked for 2 courses.

Check for Existing Names

To prevent the names from being selected twice, I changed the code, to include the COUNTIF function.

After a name is selected, the active column is checked for that name.
If the name is found, a warning message is shown, and the name is not added in the current cell.

Here is the revised section of the code, with the COUNTIF function:

Testing the Code

With the revised code, if I try to select a trainer who is already booked, I see this message, and the name is not added.

Download the Sample File

If you’d like to test the Block Duplicates code, you can download the No Duplicates in Multiple Selection Excel Drop Down sample file.

The file is in Excel 2007 format, and is zipped. When you open the file, enable macros, if you want to test the block duplicates feature.
_____________

Make It Easy to Ask Excel Questions

While working with PowerPivot in Excel 2010, I noticed the Ask Questions command on the Ribbon.

When you click that command, your web browser opens, and takes you to the PowerPivot Help forums on the Microsoft website.

That’s a nice feature, if you’re struggling with PowerPivot.

Ask Questions command on PowerPivot Ribbon

Where Do You Ask Excel Questions?

Why doesn’t Excel have an Ask Questions command? We have questions too! (A moment of silence for the old Excel newsgroups.)

The closest thing that I could find is a Contact Us command, which leads to the Microsoft website.

Microsoft Answers Forum

At the bottom of that page is a link that leads to the general Answers form – not to an Excel related forum. It takes a couple more clicks to get to the Excel section.

ExcelHelpContact02