Deflating Brent Crude Oil Prices ($US) with US CPI

[Shazzle Singh]

I currently have Brent Crude Oil prices in US dollars. However, I'd like to deflate it with US CPI such that I can get the real price of oil.

However, my issue is that I don't know the command for that and secondly, data for US CPI seem to be monthly based on the data sources I have seen.

Given that my data runs from 1980Q1 to 2016Q4,
how do I change monthly US CPI data to be in quarterly format and then deflate the Brent Crude Oil prices with the CPI such that I get real oil prices.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 date double op float lop
"Q1 1980"            35.58  3.571784
"Q2 1980"            35.45  3.568123
"Q3 1980" 32.7066666666667  3.487579
"Q4 1980"            39.09 3.6658666
"Q1 1981" 37.2933333333333 3.6188145
"Q2 1981"            33.54  3.512739
"Q3 1981" 31.7966666666667 3.4593616
"Q4 1981" 33.5233333333333 3.5122416
"Q1 1982" 30.6733333333333  3.423394
"Q2 1982" 32.1066666666667  3.469064
"Q3 1982" 31.8066666666667  3.459676
"Q4 1982"            31.59 3.4528406
"Q1 1983"            29.16  3.372798
"Q2 1983"            29.48  3.383712
"Q3 1983" 30.2966666666667  3.411038
"Q4 1983"            28.94  3.365225
"Q1 1984" 29.2333333333333   3.37531
"Q2 1984" 29.3133333333333 3.3780425
"Q3 1984" 28.1466666666667  3.337429
"Q4 1984"            27.49  3.313822
"Q1 1985" 27.3666666666667  3.309326
"Q2 1985"            26.95  3.293983
"Q3 1985" 27.0933333333333  3.299288
"Q4 1985" 28.0733333333333   3.33482
"Q1 1986"            17.16  2.842581
"Q2 1986" 12.4433333333333  2.521185
"Q3 1986" 12.4433333333333  2.521185
"Q4 1986"            14.64 2.6837575
"Q1 1987" 17.7066666666667  2.873941
"Q2 1987"            18.39  2.911807
"Q3 1987"            18.94  2.941276
"Q4 1987" 17.7566666666667  2.876761
"Q1 1988" 15.7533333333333  2.757052
"Q2 1988"            16.09  2.778198
"Q3 1988" 14.0866666666667 2.6452286
"Q4 1988" 13.1466666666667  2.576168
"Q1 1989" 17.0233333333333  2.834585
"Q2 1989"            18.47  2.916148
"Q3 1989"            17.41  2.857045
"Q4 1989"            18.72 2.9295924
"Q1 1990" 19.4633333333333  2.968532
"Q2 1990"            15.82  2.761275
"Q3 1990" 25.6566666666667 3.2448034
"Q4 1990"               31  3.433987
"Q1 1991"            19.85  2.988204
"Q2 1991"            18.33  2.908539
"Q3 1991" 19.4233333333333  2.966475
"Q4 1991" 19.8666666666667  2.989043
"Q1 1992" 17.5066666666667  2.862582
"Q2 1992" 19.6833333333333  2.979772
"Q3 1992" 19.9966666666667  2.995566
"Q4 1992" 18.9566666666667 2.9421556
"Q1 1993"            17.99  2.889816
"Q2 1993" 17.9933333333333  2.890001
"Q3 1993"             16.2  2.785011
"Q4 1993" 14.9633333333333  2.705603
"Q1 1994"            13.87  2.629728
"Q2 1994"            16.27  2.789323
"Q3 1994"            17.05   2.83615
"Q4 1994" 16.6033333333333 2.8096035
"Q1 1995" 17.2233333333333  2.846265
"Q2 1995" 18.1766666666667 2.9001386
"Q3 1995"            16.44 2.7997174
"Q4 1995" 16.9766666666667   2.83184
"Q1 1996" 18.3133333333333 2.9076295
"Q2 1996" 19.4866666666667 2.9697304
"Q3 1996"            20.63  3.026746
"Q4 1996" 23.0633333333333  3.138244
"Q1 1997" 21.0833333333333  3.048483
"Q2 1997"            18.49   2.91723
"Q3 1997" 18.6566666666667 2.9262035
"Q4 1997"            18.84  2.935982
"Q1 1998" 14.1633333333333 2.6506565
"Q2 1998"            13.28  2.586259
"Q3 1998" 13.0033333333333  2.565206
"Q4 1998"            11.85  2.472328
"Q1 1999" 11.6433333333333  2.454734
"Q2 1999"            16.03  2.774462
"Q3 1999" 20.4433333333333  3.017657
"Q4 1999" 23.8066666666667  3.169966
"Q1 2000" 26.6166666666667 3.2815375
"Q2 2000" 26.7666666666667  3.287157
"Q3 2000" 29.8833333333333  3.397301
"Q4 2000"            29.67 3.3901365
"Q1 2001"            26.07  3.260785
"Q2 2001" 26.7266666666667  3.285662
"Q3 2001" 25.2133333333333  3.227373
"Q4 2001" 19.3133333333333 2.9607956
"Q1 2002" 20.9233333333333  3.040865
"Q2 2002" 25.2033333333333  3.226976
"Q3 2002" 26.9366666666667 3.2934885
"Q4 2002" 26.7366666666667  3.286036
"Q1 2003" 31.3366666666667  3.444789
"Q2 2003" 26.4866666666667 3.2766414
"Q3 2003" 28.3833333333333  3.345802
"Q4 2003"            29.36  3.379633
"Q1 2004"            32.13   3.46979
"Q2 2004" 35.6266666666667 3.5730944
"Q3 2004" 40.5533333333333  3.702618
"Q4 2004"            42.73  3.754901
end

Thank you

[William Lisowski]

It would be helpful to see a sample of the US CPI data you have imported into Stata, covering at least some of the same time period as your sample oil price data. Also, since these prices are typically given daily, you should explain exactly what the quarterly price represents: average over the quarter? last day of the quarter? first day of the quarter?

[Justin Niakamal]

You might want to consider using an energy deflator. In an older post I shared with you something similar. Here's the code and how you can adapt your current setup to produce real oil prices. Also, note the base year from the Bureau of Labor Statistics. You can adjust if you'd like.

* use "Your_Data.dta",  clear

gen quarter = quarterly(substr(date, -4, 4) + "" + substr(date, 1,2), "YQ")
format quarter %tq!Qq-YY

tempfile temp
save `temp'

local series = "CPIAUCSL" // Consumer Price Index for All Urban Consumers: All Items
!curl -L https://fred.stlouisfed.org/series/`series'/downloaddata/`series'.csv> '`series'.csv'
insheet using "`series'.csv", names comma clear
erase "`series'.csv"

gen  quarter = qofd(date(date), "YMD") // CPI is a monthly figure
format quarter %tq!Qq-YY
gen count = 1

collapse (mean) value (sum) count , by (quarter) // Need to collapse monthly to quarterly
drop if count != 3 // Drop incomplete quarters
drop count
rename value cpi

merge 1:1 quarter using `temp', keep(match) nogenerate
gen real_oil = op/(cpi/100)

As an aside, I use a Mac. I'm not sure what the Windows version/analog of !curl -L is.

[Shazzle Singh]

William, my data represents the average over the quarter.

Jason, thanks for the code but for some reason, it does not seem to work. Sorry that I'm not great with the commands but basically this is what I get after inputing the commands.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str132 doctypehtml str64 v2
"en>"                                                                                       ""
"<head>"                                                                                    ""
"utf-8>"                                                                                    ""
"IE=edge>"                                                                                  ""
"width=device-width, initial-scale=1>"                                                      ""
"<title>Error  - St. Louis Fed</title>"                                                     ""
">"                                                                                         ""
">"                                                                                         ""
"//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.1/css/bootstrap.min.css>"           ""
"/css/main.css>"                                                                            ""
"<!--[if IE]>"                                                                              ""
"/css/ie.css />"                                                                            ""
"<![endif]-->"                                                                              ""
"stylesheet>"                                                                               ""
"/assets/components-font-awesome/css/font-awesome.min.css>"                                 ""
"<!--[if IE 8]><!-->"                                                                       ""
"/js/lib/respond.js></script>"                                                              ""
"<!--<![endif]-->"                                                                          ""
"<style>p {margin-bottom:1.5em;}</style>"                                                   ""
"</head>"                                                                                   ""
"<body>"                                                                                    ""
"<script>"                                                                                  ""
"var appConfig = {"                                                                         ""
"uapi_host: 'https://uapi.stlouisfed.org'"                                                  ""
"research_host: 'https://research.stlouisfed.org'"                                          ""
"alfred_host: 'https://alfred.stlouisfed.org'"                                              ""
"fred_host: 'https://fred.stlouisfed.org'"                                                  ""
"gsi_client_id: '115290014367-vpb89b600koe9kn0njeeq38c1unfr3gk.apps.googleusercontent.com'" ""
"};"                                                                                        ""
"</script>"                                                                                 ""
"<!-- Google Tag Manager (noscript) -->"                                                    ""
"https://www.googletagmanager.com/ns.html?id=GTM-5L322W"                                    ""
"display:none;visibility:hidden></iframe></noscript>"                                       ""
"<!-- End Google Tag Manager (noscript) -->"                                                ""
"<!--googleoff: snippet-->"                                                                 ""
"sr-only sr-only-focusable>Skip to main content</a>"                                        ""
"<!--googleon: snippet-->"                                                                  ""
"top></a>"                                                                                  ""
"ie6 container><![endif]-->"                                                                ""
"ie7 container><![endif]-->"                                                                ""
"ie8 container><![endif]-->"                                                                ""
"ie9 container><![endif]-->"                                                                ""
"ie container><![endif]-->"                                                                 ""
"container><!--<![endif]-->"                                                                ""
"col-xs-12>"                                                                                ""
"<div>"                                                                                     ""
"hidden-user class='hide'></div>"                                                           ""
"action-modal></div>"                                                                       ""
"col-xs-10 col-sm-12 col-md-8 header-logo>"                                                 ""
"/>"                                                                                        ""
"hidden-xs>"                                                                                ""
"logo-replacement>"                                                                         ""
"visible-xs>"                                                                               ""
"</span>"                                                                                   ""
"</a>"                                                                                      ""
"https://research.stlouisfed.org>"                                                          ""
"hidden-xs>"                                                                                ""
"</a>"                                                                                      ""
"</div>"                                                                                    ""
"padding-right: 5px; padding-left: 0;text-align: right;>"                                   ""
"visible-xs-block>"                                                                         ""
"margin-top:10px;>"                                                                         ""
"fa fa-bars fa-2x></i></a>"                                                                 ""
"</span>"                                                                                   ""
"signin-wrap>"                                                                              ""
"user-nav></div>"                                                                           ""
"</div>"                                                                                    ""
"hidden-xs col-md-12>"                                                                      ""
"head-search-container>"                                                                    ""
"Search FRED>"                                                                              ""
"</div>"                                                                                    ""
"</form>"                                                                                   ""
"</div>"                                                                                    ""
"</div>"                                                                                    ""
"notifications-container></div>"                                                            ""
"</div>"                                                                                    ""
"<style>"                                                                                   ""
".navbar-nav>li>a {line-height: inherit;}"                                                  ""
"#nav-accordion .panel { border: none; }"                                                   ""
"#nav-accordion .panel-body { padding: inherit; }"                                          ""
"#nav-accordion.panel-group { margin-bottom: inherit; }"                                    ""
"</style>"                                                                                  ""
"navigation>"                                                                               ""
"padding-left: 0; padding-right: 0;>"                                                       ""
"margin-top: 8px;margin-bottom: 4px;>"                                                      ""
"nav-search-container>"                                                                     ""
"Search FRED>"                                                                              ""
"input-group-btn>"                                                                          ""
"fa fa-search></i></button>"                                                                ""
"</span>"                                                                                   ""
"</div>"                                                                                    ""
"</form>"                                                                                   ""
"navbar navbar-nav col-xs-12>"                                                              ""
"<li>"                                                                                      ""
"trademark>&reg;</span> Economic Data</a>"                                                  ""
"</li>"                                                                                     ""
"visible-xs>"                                                                               ""
"https://research.stlouisfed.org/useraccount/>My Account</a>"                               ""
"</li>"                                                                                     ""
"<li>"                                                                                      ""
end

[Justin Niakamal]

I assume you're not using a Mac. I'm not a Windows person so I'm not sure how !curl changes using a different operating system.

However, you can just download the data and work with the code provided. Here's the link
https://fred.stlouisfed.org/series/CPIAUCSL

[Shazzle Singh]

I'm using a Mac but thank you anyway. Appreciate it.

[William Lisowski]

As an alternative to shelling out to the curl command, you can always use Stata's built-in copy command, which like most file-based Stata commands recognizes URLs where filenames are expected. I ran the following on my Mac, Stata 15.1.

. local series = "CPIAUCSL" // Consumer Price Index for All Urban Consumers: All Items

. copy "https://fred.stlouisfed.org/series/`series'/downloaddata/`series'.csv" "`series'.csv", replace

. insheet using "`series'.csv", names comma clear
(2 vars, 864 obs)

. erase "`series'.csv"

. describe

Contains data
  obs:           864                          
 vars:             2                          
 size:        12,096                          
-----------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------------------------
date            str10   %10s                  DATE
value           float   %9.0g                 VALUE
-----------------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

. list in 1/5

     +--------------------+
     |       date   value |
     |--------------------|
  1. | 1947-01-01   21.48 |
  2. | 1947-02-01   21.62 |
  3. | 1947-03-01      22 |
  4. | 1947-04-01      22 |
  5. | 1947-05-01   21.95 |
     +--------------------+

[Shazzle Singh]

Thanks Jason and William. I realised the commands worked but I accidentally included "//" which explains why it did not work initially.