# Data preparation for estimation of storage models

# Commodity price data are Grilly and Yang (1988) prices updated by
# Pfaffenzeller Newbold, and Rayner (2007) and available on [Stephan
# Pfaffenzeller's personal
# website](http://www.stephan-pfaffenzeller.com/cpi.html).

# US CPI data comes from [Handbook of Labor Statistics,
# U.S. Department of Labor, Bureau of Labor
# Statistics](http://www.minneapolisfed.org/community_education/teacher/calc/hist1800.cfm)

gycpi <- read.csv('../Data/Data_Downloads/gycpi-2011-01.csv',
                  nrows = 112,
                  row.names = "Year")


uscpi <- read.csv('../Data/Data_Downloads/uscpi-1800-2012.csv')

# Load the Historical Pound/Dollar Exchange rate (www.measuringworth.com)
ExR   <- read.csv('../Data/Data_Downloads/EXCHANGEPOUND_1900-2011.csv',sep=",",quote="\"",header=T,skip=2)

# Load the Historical Price Index of Manufacturers (provided by J.Madsen)
hpim  <- read.csv('../Data/Data_Downloads/hpim-1650-2014.csv',sep=";",header=T)

# Select the time span 1900-2011
uscpi  <- subset(uscpi,Year>1899&Year<2012)
hpim  <- subset(hpim,Year>1899&Year<2012)

# Turn GDP sterling pounds into US dollars
hpim[,"HPIM"] <- hpim[,"HPIM"]*ExR[,'Rate']

####################### Normalize so that the 1977-79 average = 100 #######
# Compute the 1977-79 average
M7779            <- subset(hpim,select=c('HPIM'),Year>1976&Year<1980)
M7779            <- mean(M7779$HPIM)

hpim[,c('HPIM')] <- hpim[,c('HPIM')]/M7779*100
###########################################################################

Deflator <- cbind(uscpi[,"USCPI"],gycpi[,"MUV"],hpim[,"HPIM"])
colnames(Deflator) <- c("CPI","MUV","HPIM")
CorrStat  <- data.frame(cor(Deflator))

########### Prepare output before exporting ###############################
ComPrices <- merge(gycpi,uscpi,by.x = "row.names",by.y = "Year")
rownames(ComPrices) <- ComPrices[,1]
ComPrices <- ComPrices[,-1]

ComPricesHPIM <- ComPrices[,1:24]/hpim[,"HPIM"]
ComPricesMUV  <- ComPrices[,1:24]/ComPrices[,"MUV"]
ComPrices     <- ComPrices[,1:24]/ComPrices[,"USCPI"]

# Commodity in alphabetic order
ComPrices     <- ComPrices[,order(colnames(ComPrices))]
ComPricesMUV  <- ComPricesMUV[,order(colnames(ComPricesMUV))]
ComPricesHPIM <- ComPricesHPIM[,order(colnames(ComPricesHPIM))]

# Output commodity price index in a csv file for MATLAB
write.csv(ComPrices,file = "../Data/ComPrices-1900-2011.csv",quote=FALSE)
write.csv(ComPricesMUV,file = "../Data/ComPricesMUV-1900-2011.csv",quote=FALSE)
write.csv(ComPricesHPIM,file = "../Data/ComPricesHPIM-1900-2011.csv",quote=FALSE)
