Skip to main content
The FRED® Blog

Posts tagged with: "CPIAUCSL"

View this series on FRED

Constructing a bilateral real exchange rate

How to create new series on FRED

FRED lets you create commonly used data series that are not predefined. For example, you can normalize current account balances or government budget balances by GDP and you can deflate nominal data with a price index.

One popular variable that you can create is a bilateral real exchange rate index. While a nominal exchange rate is the relative price of 2 monies (e.g., the relative price of a euro in terms of U.S. dollars), a real exchange rate is the relative price of consumption baskets in two countries. A consumption basket is a set of goods and services that represent the purchases of a typical consumer in country in a given year. Thus the real exchange rate is the price of European goods in terms of U.S. goods. One converts a nominal exchange rate into a real rate by multiplying by the ratio of the national price levels:

U.S. goods per euro area goods basket = (USD per euro) * (euro price level) / (U.S. price level)

FRED has many kinds of broad, real effective exchange rates. Here is a list of FRED’s real U.S. exchange rates. These are effective or trade-weighted real exchange rates. They are weighted averages of bilateral real exchange rates. Currencies of the countries that the U.S. trade with the most receive the highest weights in the formula. Real effective exchange rates provide a look at changes in the overall value of foreign consumption baskets in terms of the U.S. consumption basket. When a real effective exchange rate rises (falls), the average foreign consumption basket becomes more (less) expensive in terms of U.S. consumption.

But what if you want to see the price of foreign consumption in terms of U.S. consumption for a particular country or area? For example, what if you want to see the real exchange rate for the dollar per euro, as we detailed at the start of the post? You can construct such a bilateral real exchange rate yourself in FRED using monthly price and exchange rate data from the U.S. and the euro area. The following instructions give you the graph at the top of this post.

1. Search for “euro” in the FRED search box and select “U.S. / Euro Foreign Exchange Rate.” The default graph will be a daily exchange rate (DEXUSEU).

2. Because consumer price series are monthly (or quarterly), use the orange “Edit Graph” button on the right hand side to change the frequency to monthly and the aggregation method to “average.” This series is series “a” in the graph. Keep the editing box open.

3. Add the U.S. and euro area CPI series using the “customize data” area.

a. To add the U.S. CPI data, type “cpi” directly under the text that says “You can begin by adding a series to combine with your existing series.” Click on the first series in the popup list called “Consumer price index for all urban consumers.” Click “Add” on the right-hand side of the box and the U.S. CPI series became series “b” in the graph. Note that the graph itself has not changed.

b. To add the euro area CPI data, type “euro cpi” directly under the text that says “You can begin by adding a series to combine with your existing series.” Click on the first series in the popup list called “Harmonized Index of Consumer Prices: All Items for the Euro Area.” Click “Add” on the right-hand side of the box and the euro CPI series became series “c” in the graph.

4. Now that we have defined our exchange rate and price series, we use them to construct a real exchange rate by typing the following formula in the “Formula” box near the bottom of the editing box: a*c/b. Then click “Apply” to the right of the box. The picture in the graph will finally change to the bilateral real exchange rate, i.e., baskets of U.S. goods per basket of euro area goods.

5. To change the real exchange rate to an index, select “Index (Scale value to 100 for chosen date)” from the “Units” box at the bottom of the editing box and then type “1999-01-01” in the date box. Close the edit box with the X in the upper right-hand corner.

6. To see a long span of the data series that you have created, select “Max” from the data range choices, i.e., “1Y | 5Y | 10Y | Max”, at the top of the graph.

Suggested by Chris Neely.

View on FRED, series used in this post: CP0000EZ19M086NEST, CPIAUCSL, DEXUSEU

Government spending on police

State and local expenditures data from the BEA

As police presence, tactics, and department funding are being discussed, the FRED Blog offers some data to add to the conversation.

The graph above shows a category of government expenditures, “public order and safety,” as listed in the national income and product accounts from the Bureau of Economic Analysis. One series (in blue) is total government, and one series (in red) is state and local government.

These expenditures, which include both police and fire departments, are a small part of government spending, but they have continually grown. This isn’t surprising, as the data aren’t adjusted for inflation, population growth, or economic growth.

The second graph shows state and local expenditures specifically for police as a share of all state and local government expenses. For 2018, police expenditures were 4.79%, which is at the higher end of the range. The low, in 1980, was 4.25%.

How many people are in the police force? The Current Population Survey helps us here, making the distinction between those who patrol and those in supervision and detective roles. As of 2019, the total was 766,000, with a slight increase in the former and stable if not decreasing numbers in the latter. Note that these numbers include both public and private police forces.

This fourth graph shows how much police are paid. The Current Population Survey doesn’t provide averages, but rather medians: So, half are paid more and half are paid less than the values shown. These values aren’t inflation-adjusted and do not include benefits. Overtime pay is included, though.

The final graph is the same as the previous one, but the wages are adjusted for inflation. There’s quite a bit of fluctuation, likely due to changes in overtime. And there’s a slight upward trend, which can come from higher hourly pay, more overtime, or a combination of the two.

How these graphs were created: First graph: Search FRED for “public order and safety” and click on the series encompassing all government levels. From the “Edit Graph” panel, use the “Add Line” tab to search for and select the state and local government series with the same keywords. Second graph: Start with the graph for state and local police expenses. From the “Edit Graph” panel, add a series through the “Customize data” search bar (different from the Add Line tab): Search for and select state and local government expenses and apply formula a/b*100. Third graph: Search for “employed police” and select the series. Fourth graph: Search for “median police earnings” and select the series. Fifth graph: Start with the fourth graph. Use “Customize data” to add the CPI for both series and apply the formula a/b/*255.651 (the last number being the average value of the CPI in 2019).

Suggested by Christian Zimmermann.

View on FRED, series used in this post: CPIAUCSL, G160081A027NBEA, G160841A027NBEA, G160851A027NBEA, LEU0254491000A, LEU0254491900A, LEU0254544400A, LEU0254545300A, SLEXPND

Medical services spending

A look back at expenditures on treating diseases in the U.S.

The FRED Blog has covered healthcare before. (See the list of related posts below.) In this post, we look at pre-pandemic data on medical services spending in the U.S., specifically by category of illness.

Per the Bureau of Economic Analysis (BEA): “To better measure spending trends and treatment prices, BEA developed a set of supplemental statistics called the Health Care Satellite Account. These statistics give policymakers, researchers and the public another way of understanding the economics of health care. The satellite account measures U.S. health care spending by the diseases being treated (for example, cancer or diabetes) instead of by the types of goods and services purchased (such as doctor’s office visits or drugs).”

These data, available for 2000-2016, allow us to compare per-person expenditures on medical services across different diseases. The FRED graph above shows that expenditures per person on infectious and parasitic diseases (red bars) was, until 2015, smaller than expenditures per person on mental health diseases (green bars). By the way, the values are adjusted for changes in the general cost of living measured through the consumer price index (CPI).

The BEA data also allow us to see how the prices for treating different diseases have changed over time. Those prices are measured through an index number, so we can compare their rates of growth, not their levels in dollars and cents.

The FRED graph above shows that the average cost of medical services related to infectious and parasitic diseases (red line) rose faster than the average cost of all diseases (dashed blue line). The latter includes the cost of medical services related to mental health diseases (green line), which rose more slowly than average.

For a complete list of price indexes for medical services expenditures by disease, go to FRED and click on “Browse data by: Source” underneath the search bar. Scroll down the alphabetical list for “U.S. Bureau of Economic Analysis” and click on the name. Next, click on “Health Care Satellite Account > Health Care Blended Account > Expenditures Price Index, Annual.”

Related posts

How these graphs were created: Follow the instructions above to find the series. To get the bars in the first graph, go to the “Edit Graph panel: From the “Format” tab, select graph type “bar” with no stacking.

Suggested by Diego Mendez-Carbajo.


Subscribe to the FRED newsletter

Follow us

Back to Top