CPI table

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

CPI table

Post by RustyShackleford »

Until sometime this year, there was a nice table at the Bureau of Labor Statistics website (http://www.bls.gov) that gave the CPI-U for every month of every year. Each month the table was updated. I found it extremely helpful for a spreadsheet I maintain that computes the proper coupon payments for TIPS bonds and other inflation-linked stuff like some SPIAs (I'm suspicious of financial institutions, by nature).

My link to that seems to be broken and I cannot find the table anymore. Perhaps it was just too simple, having all that data available in a nice concise table, to have withstood the test of time. Can anyone tell me if the data might still be available in as neat a form ?

The table looked VERy much like this:

http://www.usinflationcalculator.com/in ... 3-to-2008/

... which I notice is current only through April 2014 (perhaps corresponding to the month that my other link became broken).

Thanks.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

Table Containing History of CPI-U U.S. All Items Indexes and Annual Percent Changes From 1913 to Present is now available in the CPI Detailed Report as Table 24
You have to ignore all the other tables.

Since you are updating your own table, do your really need the data from 1913? Wouldn't the current year's data suffice?
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

You can always get the last few years worth: Go to databases, top picks
http://www.bls.gov/cpi/#data
User avatar
nisiprius
Advisory Board
Posts: 52211
Joined: Thu Jul 26, 2007 9:33 am
Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry

Re: CPI table

Post by nisiprius »

Is this what you want?

http://www.bls.gov/cpi/cpifiles/cpiai.txt

Hmmm... only through January 2014 though.

See also Bureau of Labor Statistics FTP Server Migration Crosswalk
Last edited by nisiprius on Tue Aug 12, 2014 6:49 pm, edited 1 time in total.
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

nisiprius wrote:Is this what you want?

http://www.bls.gov/cpi/cpifiles/cpiai.txt
Probably, except it stopped in January (it is dated 02-20-2014 after the January numbers were released).
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

sscritic wrote:
nisiprius wrote:Is this what you want?

http://www.bls.gov/cpi/cpifiles/cpiai.txt
Probably, except it stopped in January (it is dated 02-20-2014 after the January numbers were released).
Exactly, hence my problem.
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

sscritic wrote:
Table Containing History of CPI-U U.S. All Items Indexes and Annual Percent Changes From 1913 to Present is now available in the CPI Detailed Report as Table 24
You have to ignore all the other tables.

Since you are updating your own table, do your really need the data from 1913? Wouldn't the current year's data suffice?
Yes, Table 24 actually has ALL the stuff the old link had. It's just that Table 24 is actually a bunch of different tables, and I only saw the one that has each year. Sigh. Thanks all.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

I went to tables. I clicked on CPI Detailed Report (tables 1-29) June 2014 (PDF). I scrolled to Table 24. The first page shows every month from 1913 to 1969. The next page shows the annual numbers for the same years. The next page shows every month from 1970 to June 2014. The next page shows annual numbers for 1970 through the first half of 2014.

I don't know what you see.

P.S. I still like databases, top picks.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: CPI table

Post by #Cruncher »

I noticed that the link ( ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt ) was broken some time ago. You can still get it (as other posters have mentioned) but it isn't easy.
Or you could refer to a table I update each month with the same figures [ * ] going back to 1961.

* Actually there is a 0.1 or 0.2 difference between the figures on my table and the ones on the BLS table for Feb 2000 - Aug 2000. This is because the BLS shows corrected figures; but I show the the uncorrected figures that were used in computing inflation adjustments for TIPS and I Bonds. (See this BLS web page for more info.)
User avatar
nisiprius
Advisory Board
Posts: 52211
Joined: Thu Jul 26, 2007 9:33 am
Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry

Re: CPI table

Post by nisiprius »

I haven't had good luck at all exporting tables in PDF files into a spreadsheet. I'd tried selecting, copying, and pasting into a text editor but, well, let's just say it doesn't come out the way I expect. Do either of you have any tips?
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

PDF's are a pain. They are even making them so you can't copy and paste into the compose box.

However, for this particular question, I still like databases, top picks. Here are the first six months of this year, and it pastes perfectly, here and in a spreadsheet.

233.916 234.781 236.293 237.072 237.900 238.343
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

The other nice thing about databases, top picks is that they have the 1967 = 100 series, which is used by one of my pensions in protecting me against loss of purchasing power. Most of the time, you only see 1982-84 = 100.
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

sscritic wrote:I went to tables. I clicked on CPI Detailed Report (tables 1-29) June 2014 (PDF). I scrolled to Table 24. The first page shows every month from 1913 to 1969. The next page shows the annual numbers for the same years. The next page shows every month from 1970 to June 2014. The next page shows annual numbers for 1970 through the first half of 2014.

I don't know what you see.
That's what I see. Like I said, Table 24 is actually 4 separate tables. I think what it actually is is two separate tables, but the width including the annual number would not fit in the page width, so it wraps like that; apparently it was too much trouble to scale so the width fits.

It's still ridiculous, IMHO, that the information is not also available in the old concise format.
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

#Cruncher wrote: Or you could refer to a table I update each month with the same figures [ * ] going back to 1961.
Bingo, we have a winner.
This is because the BLS shows corrected figures; but I show the the uncorrected figures that were used in computing inflation adjustments for TIPS and I Bonds. (See this BLS web page for more info.)
Yep, the uncorrected figures, in your table, are what we want I believe.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

I still don't get why you need an update for 1913 every month. The numbers really shouldn't be changing. Have the 1913 numbers changed once since the 1982-84 resetting?

If you already have the 101 years from 1913 to 2013, why do you need a repeat every month? I keep track of the CPI-W for the social security COLA. Each month, I add one number, I don't go all the way back and reenter all the numbers I already have. Color me confused.
User avatar
1210sda
Posts: 1865
Joined: Wed Feb 28, 2007 7:31 am

Re: CPI table

Post by 1210sda »

I had th same problem, so I found this one.

http://inflationdata.com/Inflation/Cons ... oaded=true

1210
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

sscritic wrote:I still don't get why you need an update for 1913 every month. The numbers really shouldn't be changing. Have the 1913 numbers changed once since the 1982-84 resetting?
You're entirely correct, of course, as I update my spreadsheet several times per year. But I don't update it every month, so it was nice to be able to retrieve one bookmarked link and all the data I DO need was there. Now, I have to go to the link for the CPI tables, then click (and wait for) the huge PDF file, and then search for Table 24; and of course I have to do this twice if the months since I've last updated my spreadsheet span two different years.

And it IS cool to have all historical years' average CPI's in one place, do when dufuses say things like "so and so only cost X back when men were men" you can easily show that it was actually more expensive then than it is now (maybe).
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: CPI table

Post by sscritic »

Have you looked at databases, hot picks? Here is 2013 and the first half of 2014. It's easy to copy and paste.

Code: Select all

2013	230.280	232.166	232.773	232.531	232.945	233.504	233.596	233.877	234.149	233.546	233.069	233.049	232.957	232.366	233.548
2014	233.916	234.781	236.293	237.072	237.900	238.343	 	 	 	 	 	 	 	                                          236.384
The last three columns are Annual, Half1, and Half2, which is why 2014 only has data for the second of the three. [Make your window nice and wide]
Bill M
Posts: 440
Joined: Sun Dec 30, 2012 9:10 pm

Re: CPI table

Post by Bill M »

RustyShackleford wrote:Until sometime this year, there was a nice table at the Bureau of Labor Statistics website (http://www.bls.gov) that gave the CPI-U for every month of every year. Each month the table was updated. I found it extremely helpful for a spreadsheet I maintain that computes the proper coupon payments for TIPS bonds and other inflation-linked stuff like some SPIAs (I'm suspicious of financial institutions, by nature).

My link to that seems to be broken and I cannot find the table anymore. Perhaps it was just too simple, having all that data available in a nice concise table, to have withstood the test of time. Can anyone tell me if the data might still be available in as neat a form ?

The table looked VERy much like this:

http://www.usinflationcalculator.com/in ... 3-to-2008/

... which I notice is current only through April 2014 (perhaps corresponding to the month that my other link became broken).

Thanks.
Start at http://www.bls.gov/data/home.htm, in the row "All Urban Consumers" click on "Top Picks"; click the box "All Items" and click "Retrieve data"; click "More Formatting Options"; under year range, change start to 1913 and change output type to text and click "Retrieve data". Now copy/paste the comma-delimited data to a .csv file and open it in Excel.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: CPI table

Post by #Cruncher »

sscritic in [url=http://www.bogleheads.org/forum/viewtopic.php?p=2153351#p2153351]this post[/url] wrote:The other nice thing about databases, top picks is that they have the 1967 = 100 series, ...
Yes, that's one of about 20 CPI series you can choose from that page. To choose other series you can go to the multi-screen option, Create Customized Tables, and step through the following six screens:
  1. Seasonally adjusted / Not seasonally adjusted
  2. Geographical Area
  3. Alternate (1967=100) / Current (1982-84=100)
  4. Screen to select the specific series, e.g., All Items, Food, Energy
  5. Monthly / Semi-annual
  6. Screen that lists the codes for your selected series which can be copied and pasted in some text document for later use
To later retrieve one or more series for which you've saved the codes in item 6 above, go to Series Report and paste the codes into the box. Here are ones I've saved for All Items, All Items less Food & Energy, Food, and Energy:
Not Seasonally Adjusted:

Code: Select all

CUUR0000SA0
CUUR0000SA0L1E
CUUR0000SAF1
CUUR0000SA0E
Seasonally Adjusted:

Code: Select all

CUSR0000SA0
CUSR0000SA0L1E
CUSR0000SAF1
CUSR0000SA0E
Bill M in previous post wrote:... click "More Formatting Options"; under year range, change start to 1913 and change output type to text and click "Retrieve data". Now copy/paste the comma-delimited data to a .csv file and open it in Excel.
It's not necessary to go to the "More Formatting Options" page. You can change the date range right on the main display page. And to get it into an Excel file you have two easier choices:
  • Click the link that says "Download .xlsx"
  • Copy and paste directly from the HTML table (this is hard the first time, but easy once you get the hang of it):
    • Move the mouse cursor immediately to the left of the "Y" in "Year" (it will change from a pointer to a vertical "I" bar) and click.
    • Hold down the shift key and move the mouse cursor immediately to the right of the last number in the last row and click (the whole table should now be selected).
    • Press Ctrl-C to copy the selection.
    • Open an Excel file and press Ctrl-V to paste. With many browsers and versions of Excel the table elements will automatically be pasted properly into separate cells. But if this doesn't work, choose Excel's Paste Special option and experiment with the various choices (e.g., HTML, Text, Unicode Text).
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

Wow, ok, thanks for explaining all that. Series code CUUR0000SA0 is clearly the one I want: it gives the numbers used for computing TIPS inflation-factors, for inflation adjustments for my AIG SPIA, and for coupon payments for my Sallie-Mae bonds (ticker ISM and OSM).

Still though, looks like I have to go to data.bls.gov/cgi-bin/srgate, then paste in the code and click next, then click retrieve data. I was really hoping for a single link that I could bookmark.

Oh well, looks like the one #Cruncher provided will do the trick, as long as he faithfully updates it.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: CPI table

Post by Oicuryy »

RustyShackleford wrote:I was really hoping for a single link that I could bookmark.
Try this one.
http://research.stlouisfed.org/fred2/data/CPIAUCNS.txt

Ron
Money is fungible | Abbreviations and Acronyms
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

Oicuryy wrote:
RustyShackleford wrote:I was really hoping for a single link that I could bookmark.
Try this one.
http://research.stlouisfed.org/fred2/data/CPIAUCNS.txt

Ron
Thanks.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: CPI table

Post by #Cruncher »

For those interested, this is just one of many data series from the Federal Reserve Economic Data (FRED) web site maintained by the Federal Reserve Bank of St. Louis. You can get to it and see what else is available through the following path: One series I like to see as a graph is the Consumer Price Index for All Urban Consumers: Purchasing Power of the Consumer Dollar under Special Indexes. It is just the inverse of the CPI-U.
Image
User avatar
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: CPI table

Post by siamond »

I had the same issue, a simple link that used to work, and now I learned to go to databases, top picks as several folks explained.

May I ask a question to the OP though? Why do you care about monthly updates? Why not wait for the end of the year update?
User avatar
Topic Author
RustyShackleford
Posts: 1682
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: CPI table

Post by RustyShackleford »

siamond wrote: May I ask a question to the OP though? Why do you care about monthly updates? Why not wait for the end of the year update?
Not sure if you refer to an EOY update of the monthly figures, or just an EOY figure. Either way, it's because various coupon payments are based on monthly figures and because I'd like to compute those payments without having to wait 'til year's end.
Post Reply