Execution

Sys.time()
## [1] "2014-10-11 10:36:33 JST"

Package

options("getSymbols.warning4.0"=F)
library(tseries)
library(xts)
library(ggplot2)
library(knitr)
library(PerformanceAnalytics)
library(quantmod)
suppressPackageStartupMessages(library(googleVis))

Import Raw Data as dataframe

year<-c(2013:2014)
location<-2 #1:local 2:web
username<-Sys.info()['user']
path01<-paste("C:/Users/",username,"/Desktop/CFTC/",sep="")
setwd(path01)
url<-"http://www.cftc.gov/files/dea/history/"
tmp<-list()
cnt<-0
for(iii in 1:length(year)){
filename<-paste("deacot",year[iii],".zip",sep="")
if(location==2){
download.file(paste(url,"deacot",year[iii],".zip",sep=""),filename,mode="wb")
}
if(file.exists(filename)==T){
tmp[[iii]]<-read.table(unzip(filename),sep=",",header=T,as.is=T)
if(cnt==0){cftc<-tmp[[iii]];cnt<-1}else if(colnames(cftc)==colnames(tmp[[iii]])){cftc<-rbind(cftc,tmp[[iii]])}
}
}
#colnames(cftc)
kable(head(cftc[,1:3],1))
Market.and.Exchange.Names As.of.Date.in.Form.YYMMDD As.of.Date.in.Form.YYYY.MM.DD
WHEAT-SRW - CHICAGO BOARD OF TRADE 131231 2013-12-31
cftc<-cbind(date=as.Date(cftc[,3]),cftc[,-3]) 
rrr<-order(cftc[,1])
cftc<-cftc[rrr,]
rownames(cftc)<-c(1:nrow(cftc))
kable(head(cftc[,1:3],1))
date Market.and.Exchange.Names As.of.Date.in.Form.YYMMDD
2013-01-08 WHEAT - CHICAGO BOARD OF TRADE 130108
colnames(cftc)[1:10]

[1] “date”
[2] “Market.and.Exchange.Names”
[3] “As.of.Date.in.Form.YYMMDD”
[4] “CFTC.Contract.Market.Code”
[5] “CFTC.Market.Code.in.Initials”
[6] “CFTC.Region.Code”
[7] “CFTC.Commodity.Code”
[8] “Open.Interest..All.”
[9] “Noncommercial.Positions.Long..All.” [10] “Noncommercial.Positions.Short..All.”

cftc$Noncommercial.Positions.Net<-cftc[,9]-cftc[,10]
colnames(cftc)[125:ncol(cftc)]

[1] “Concentration.Net.LT..8.TDR.Short..Other.” [2] “Contract.Units”
[3] “CFTC.Contract.Market.Code..Quotes.”
[4] “CFTC.Market.Code.in.Initials..Quotes.”
[5] “CFTC.Commodity.Code..Quotes.”
[6] “Noncommercial.Positions.Net”

Extract currency data as dataframe

currency<-c("JAPANESE YEN","EURO FX","AUSTRALIAN DOLLAR","BRITISH POUND STERLING","SWISS FRANC","NEW ZEALAND DOLLAR","CANADIAN DOLLAR","RUSSIAN RUBLE","MEXICAN PESO","BRAZILIAN REAL") #,"SOUTH AFRICAN RAND")

unit<-c("(CONTRACTS OF 12,500,000 JAPANESE YEN)|(CONTRACTS OF JPY 12,500,000)","(CONTRACTS OF 125,000 EUROS)|(CONTRACTS OF EUR 125,000)","(CONTRACTS OF 100,000 AUSTRALIAN DOLLARS)|(CONTRACTS OF AUD 100,000)","(CONTRACTS OF 62,500 POUNDS STERLING)|(CONTRACTS OF GBP 62,500)","(CONTRACTS OF 125,000 SWISS FRANCS)|(CONTRACTS OF CHF 125,000)","(CONTRACTS OF NZ$ 100,000)|(CONTRACTS OF NZD 100,000)","(CONTRACTS OF 100,000 CANADIAN DOLLARS)|(CONTRACTS OF CAD 100,000)","(CONTRACTS OF RUB 2,500,000)","(CONTRACTS OF 500,000 MEXICAN PESOS)|(CONTRACTS OF MXN 500,000)","(CONTRACTS OF BRL 100,000)") #,"(CONTRACTS OF ZAR 500,000)")
#length(currency)
#length(unit)
par(mfrow=c(ceiling(length(currency)/4),4),mar=c(3,4,4,4))
ts.data<-list()
for(ccc in 1:length(currency)){
ts.data[[ccc]]<-cftc[grep(currency[ccc],cftc[,2]),]
cat("\nLatest 6 data",currency[ccc]," CONTRACTS","\n")
ts.data[[ccc]]<-ts.data[[ccc]][grep(unit[ccc],ts.data[[ccc]][,126]),]
ts.data[[ccc]]<-ts.data[[ccc]][,c(1,130,9,10)]
colnames(ts.data[[ccc]])<-c("date",paste("Net",currency[ccc]),paste("Long",currency[ccc]),paste("Short",currency[ccc]))
gTable<-gvisTable(tail(ts.data[[ccc]]))
print(gTable,tag="chart")
#kable(tail(ts.data[[ccc]]))
Date<-ts.data[[ccc]]$date
Net.Positions<-ts.data[[ccc]]$Net
plot(x=Date,y=Net.Positions,type="h",main=currency[ccc],xaxt="n")
axis.Date(side=1,at=seq(ts.data[[ccc]][,1][1],ts.data[[ccc]][,1][nrow(ts.data[[ccc]])],"days"),format="%Y/%m/%d")
}

Latest 6 data JAPANESE YEN CONTRACTS

Latest 6 data EURO FX CONTRACTS

Latest 6 data AUSTRALIAN DOLLAR CONTRACTS

Latest 6 data BRITISH POUND STERLING CONTRACTS

Latest 6 data SWISS FRANC CONTRACTS

Latest 6 data NEW ZEALAND DOLLAR CONTRACTS

Latest 6 data CANADIAN DOLLAR CONTRACTS

Latest 6 data RUSSIAN RUBLE CONTRACTS

Latest 6 data MEXICAN PESO CONTRACTS

Latest 6 data BRAZILIAN REAL CONTRACTS

title(paste("\n","CFTC:Futures Only"),outer=T)

plot of chunk unnamed-chunk-4

Convert Net Position Contracts data to xts

for(ccc in 1:length(currency)){
ts.data[[ccc]]<-as.xts(ts.data[[ccc]][,-1],order.by=as.Date(ts.data[[ccc]]$date),header=T,as.is=T)
#indexClass(ts.data[[ccc]])
}

Import Foreign Exchange Rate from OANDA as xts

cnt<-0
list.s<-list()
merge.fx<-list()
fx.data<-list()
list.s[[1]]<-c("oanda",1,"JPY","EUR","AUD","GBP","CHF","NZD","CAD","RUB","MXN","BRL") #,"ZAR")
for(lll in 1:length(list.s)){
for(iii in 3:length(list.s[[lll]])){
cnt<-cnt+1
item<-paste(list.s[[lll]][iii],"/USD",sep="") 
fx.data[[cnt]]<-getSymbols(item,src=list.s[[lll]][1],auto.assign=FALSE)
fx.data[[cnt]]<-fx.data[[cnt]][,as.double(list.s[[lll]][2])]
}
}

Correlation between IMM position and Exchange Rate.1st Difference

cnt<-length(currency)
for(iii in 1:cnt){
cat(paste(iii,colnames(ts.data[[iii]])[1],"-",colnames(fx.data[[iii]])[1],"\n"))  
}
## 1 Net JAPANESE YEN - JPY.USD 
## 2 Net EURO FX - EUR.USD 
## 3 Net AUSTRALIAN DOLLAR - AUD.USD 
## 4 Net BRITISH POUND STERLING - GBP.USD 
## 5 Net SWISS FRANC - CHF.USD 
## 6 Net NEW ZEALAND DOLLAR - NZD.USD 
## 7 Net CANADIAN DOLLAR - CAD.USD 
## 8 Net RUSSIAN RUBLE - RUB.USD 
## 9 Net MEXICAN PESO - MXN.USD 
## 10 Net BRAZILIAN REAL - BRL.USD
par(mfrow=c(ceiling(cnt/2),2),mar=c(3,4,4,4))
for(iii in 1:cnt){
merge.fx[[iii]]<-merge(ts.data[[iii]],fx.data[[iii]],all=F)
merge.fx[[iii]]<-merge.fx[[iii]][,-c(2,3)]
tmp<-diff(merge.fx[[iii]])[-1]
kable(cor(tmp))
print(summary(lm(tmp[,2]~tmp[,1])))
chart.TimeSeries(ts.data[[iii]][,1],main="",yaxis.right=F,type="h",lwd="1",legend.loc="bottomleft",color="blue")
par(new=T)
chart.TimeSeries(fx.data[[iii]][,1],main="",type="o",lwd="1",xaxis=F,yaxis.right=T,legend.loc="topleft")
}
## 
## 
## |                 | Net.JAPANESE.YEN| JPY.USD|
## |:----------------|----------------:|-------:|
## |Net.JAPANESE.YEN |            1.000|   0.526|
## |JPY.USD          |            0.526|   1.000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -3.48e-04 -3.76e-05 -1.30e-06  4.63e-05  3.62e-04 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -1.01e-05   1.17e-05   -0.86     0.39    
## tmp[, 1]     4.76e-09   9.33e-10    5.10  2.9e-06 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 9.8e-05 on 68 degrees of freedom
## Multiple R-squared:  0.277,  Adjusted R-squared:  0.266 
## F-statistic:   26 on 1 and 68 DF,  p-value: 2.93e-06
## 
## 
## |            | Net.EURO.FX| EUR.USD|
## |:-----------|-----------:|-------:|
## |Net.EURO.FX |      1.0000|  0.6754|
## |EUR.USD     |      0.6754|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.022203 -0.005027 -0.000159  0.005276  0.019491 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -4.31e-05   9.80e-04   -0.04     0.97    
## tmp[, 1]     4.69e-07   6.21e-08    7.55  1.4e-10 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.00817 on 68 degrees of freedom
## Multiple R-squared:  0.456,  Adjusted R-squared:  0.448 
## F-statistic:   57 on 1 and 68 DF,  p-value: 1.43e-10
## 
## 
## |                      | Net.AUSTRALIAN.DOLLAR| AUD.USD|
## |:---------------------|---------------------:|-------:|
## |Net.AUSTRALIAN.DOLLAR |                1.0000|  0.5357|
## |AUD.USD               |                0.5357|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -0.03624 -0.00463  0.00021  0.00724  0.01933 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -1.72e-03   1.28e-03   -1.34     0.18    
## tmp[, 1]     7.73e-07   1.48e-07    5.23  1.8e-06 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.0107 on 68 degrees of freedom
## Multiple R-squared:  0.287,  Adjusted R-squared:  0.277 
## F-statistic: 27.4 on 1 and 68 DF,  p-value: 1.76e-06
## 
## 
## |                           | Net.BRITISH.POUND.STERLING| GBP.USD|
## |:--------------------------|--------------------------:|-------:|
## |Net.BRITISH.POUND.STERLING |                     1.0000|  0.2913|
## |GBP.USD                    |                     0.2913|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -0.05200 -0.00877  0.00227  0.00787  0.02891 
## 
## Coefficients:
##             Estimate Std. Error t value Pr(>|t|)  
## (Intercept) 6.02e-04   1.68e-03    0.36    0.721  
## tmp[, 1]    4.11e-07   1.64e-07    2.51    0.014 *
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.014 on 68 degrees of freedom
## Multiple R-squared:  0.0848, Adjusted R-squared:  0.0714 
## F-statistic:  6.3 on 1 and 68 DF,  p-value: 0.0144
## 
## 
## |                | Net.SWISS.FRANC| CHF.USD|
## |:---------------|---------------:|-------:|
## |Net.SWISS.FRANC |          1.0000|  0.3674|
## |CHF.USD         |          0.3674|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.019427 -0.006458  0.000071  0.007508  0.020814 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)   
## (Intercept) -3.30e-04   1.14e-03   -0.29   0.7735   
## tmp[, 1]     8.09e-07   2.48e-07    3.26   0.0018 **
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.00956 on 68 degrees of freedom
## Multiple R-squared:  0.135,  Adjusted R-squared:  0.122 
## F-statistic: 10.6 on 1 and 68 DF,  p-value: 0.00176
## 
## 
## |                       | Net.NEW.ZEALAND.DOLLAR| NZD.USD|
## |:----------------------|----------------------:|-------:|
## |Net.NEW.ZEALAND.DOLLAR |                  1.000|   0.295|
## |NZD.USD                |                  0.295|   1.000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -0.03204 -0.00697  0.00064  0.00749  0.03546 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)  
## (Intercept) -2.01e-04   1.41e-03   -0.14    0.888  
## tmp[, 1]     1.38e-06   5.44e-07    2.55    0.013 *
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.0118 on 68 degrees of freedom
## Multiple R-squared:  0.087,  Adjusted R-squared:  0.0736 
## F-statistic: 6.48 on 1 and 68 DF,  p-value: 0.0132
## 
## 
## |                    | Net.CANADIAN.DOLLAR| CAD.USD|
## |:-------------------|-------------------:|-------:|
## |Net.CANADIAN.DOLLAR |              1.0000|  0.0693|
## |CAD.USD             |              0.0693|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -0.03168 -0.00439  0.00051  0.00630  0.01584 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)
## (Intercept) -1.11e-03   1.02e-03   -1.09     0.28
## tmp[, 1]     7.08e-08   1.23e-07    0.57     0.57
## 
## Residual standard error: 0.00856 on 68 degrees of freedom
## Multiple R-squared:  0.0048, Adjusted R-squared:  -0.00983 
## F-statistic: 0.328 on 1 and 68 DF,  p-value: 0.569
## 
## 
## |                  | Net.RUSSIAN.RUBLE| RUB.USD|
## |:-----------------|-----------------:|-------:|
## |Net.RUSSIAN.RUBLE |            1.0000|  0.3288|
## |RUB.USD           |            0.3288|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.000976 -0.000301  0.000024  0.000291  0.000772 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)   
## (Intercept) -9.24e-05   5.03e-05   -1.84   0.0711 . 
## tmp[, 1]     1.10e-07   3.97e-08    2.76   0.0075 **
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.000406 on 63 degrees of freedom
## Multiple R-squared:  0.108,  Adjusted R-squared:  0.094 
## F-statistic: 7.64 on 1 and 63 DF,  p-value: 0.00748
## 
## 
## |                 | Net.MEXICAN.PESO| MXN.USD|
## |:----------------|----------------:|-------:|
## |Net.MEXICAN.PESO |           1.0000|  0.3209|
## |MXN.USD          |           0.3209|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.003373 -0.000410  0.000084  0.000346  0.002601 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)   
## (Intercept) -2.39e-05   1.11e-04   -0.21   0.8310   
## tmp[, 1]     2.21e-08   7.91e-09    2.79   0.0068 **
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.000929 on 68 degrees of freedom
## Multiple R-squared:  0.103,  Adjusted R-squared:  0.0898 
## F-statistic: 7.81 on 1 and 68 DF,  p-value: 0.00675
## 
## 
## |                   | Net.BRAZILIAN.REAL| BRL.USD|
## |:------------------|------------------:|-------:|
## |Net.BRAZILIAN.REAL |             1.0000|  0.2557|
## |BRL.USD            |             0.2557|  1.0000|
## 
## Call:
## lm(formula = tmp[, 2] ~ tmp[, 1])
## 
## Residuals:
##       Min        1Q    Median        3Q       Max 
## -0.022653 -0.003066  0.000069  0.004954  0.014293 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)  
## (Intercept) -7.60e-04   1.04e-03   -0.73    0.467  
## tmp[, 1]     6.58e-07   3.35e-07    1.96    0.055 .
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.00775 on 55 degrees of freedom
## Multiple R-squared:  0.0654, Adjusted R-squared:  0.0484 
## F-statistic: 3.85 on 1 and 55 DF,  p-value: 0.0548