Portfolio variance

[Elisabeth Maes]

Dear all,

I have a panel dataset containing monthly exchange rate returns of 121 currencies over the period 1998-2010. Further, I have another panel dataset containing individual firms use of foreign currencies in international transactions. I would like to combine both datasets in order to obtain an estimate of the firm's exposure to exchange rate changes, dependent upon the relative use of foreign currencies. Therefore I would like to use Modern Portfolio Theory to calculate portfolio variance. Portfolio variance is typically being calculated as a function of correlations of the portfolio’s assets, for all combinations of assets. Here, obviously the assets are the currencies.

So the datasets look as following:
firmid year weight_currency currency
1 2000 0.10 EUR
1 2000 0.20 USD
.... ... .... ....

and

year month USD/EUR ...... XOF/EUR
2000 1 ..... .....
2000 2
...
2000 12
Some returns are missing here.

I would like to work with matrices and apply the following formula W*S*W', with S being the variance-covariance matrix of exchange rate returns and W representing the weights of each currency in a particular firm's portfolio of currencies used. I would like to estimate S for each year in my panel individually. Therefore, W*S*W' is calculated per id-year combination.


Does anyone have an idea on how to proceed best to tackle this issue? I have come across the mvport package (varrets command) to calculate the variance-covariance matrix, but it is not possible to do this on a year basis.

Many thanks in advance.
Elisabeth
PS. fyi, I requested the Stata forum administrator to change my nickname into my full name just now.

[Attaullah Shah]

Well, I could not find enough time to help you throughout, however, I have developed a code for you that calculates portfolio standard deviation with variance-covariance matrix formula in each year.

//First to generate some dummy data
clear
set obs 1000
gen date=_n
format %td date
expand 10
bys date: gen id=_n
gen year=year(date)
gen ri1=uniform()
gen ri2=uniform()
gen ri3=uniform()
gen ri4=uniform()
gen W1=.25
gen W2=.25
gen W3=.25
gen W4=.25

//Now Portfolio SD
qui sum year
loc MAX=r(max)
loc MIN=r(min)
forval i = `MIN' / `MAX' {

    varrets ri1 ri2 ri3 ri4 if year==`i'
    mat S=r(cov)
    mkmat W1 W2 W3 W4 in 1, matrix (W)
    mat VAR = W*S*W'
    svmat double VAR, name(VAR)
    preserve
    keep year VAR1
    drop if VAR1==.
    replace year=`i'
    if year==`MIN' {
        save `i', replace
    }
    append using `MIN'.dta
    save `MIN'.dta, replace
    restore
    cap drop VAR1
}

I hope this will help you.

[Elisabeth Maes]

Many thanks, with some slight modifications wrt shape of my data it works fine!

Regards,
Elisabeth

[Attaullah Shah]

I am glad to know that it worked for you. A mata equivilant of this code might be faster, so if someone on this forum can spare some time and convert the above code to Mata, that would be great.

[Abraham Wolde-Tsadick]

Attaullah,

Here is a Mata code, see if it gives you what you want.

mata
st_view(r=.,.,tokens("ri1 ri2 ri3 ri4"))
st_view(year=.,.,"year")
stata("levelsof year, local(y)")
st_local("y")
y = strtoreal( tokens( st_local("y"))' )
VAR = J(rows(y),2,.)
w = 0.25,0.25,0.25,0.25

for (i = j = 1; i<=rows(y); i++ ) {
    s = select(r, ( rowsum(r:!=.) :== cols(r)  ) :* year :== y[i,1]  )
    s = (s :- mean(s))'(s :- mean(s)) :/ ( rows(s) - 1)
    v = w*s*w'
    VAR[j++,.] = y[i,1],v
}
end
clear
getmata (year VAR) = VAR
save VAR, replace

Abraham

[Elisabeth Maes]

Thanks both! I am not familiar with mata, but is there a way to avoid the manually inserting of the weights (w)? I have about 120 assets in the portfolio so this would be practically impossible...

[Abraham Wolde-Tsadick]

Here is an example (with a data similar to yours) where you don't need to insert the weights manually.

clear
input firmid year w_ str3 currency
1    2000    0.25    JPY
1    2000    0.45    EUR
1    2000    0.30    GBP
1    2001    0.5    INR
1    2001    0.5    AUD
1    2002    0.5    CAD
1    2002    0.5    SGD
1    2003    0.1    CHF
2    2001    0.5    MYR
2    2001    0.5    JPY
2    2003    0.5    GBP
2    2003    0.5    CNY
end

reshape wide w_ , i(firmid year) j(currency) string
expand 12
bys firmid year: gen month = _n
save firm.dta, replace

clear
set obs 48
gen year = round((_n+5)/12)+1999
bys year: gen month = _n
set seed 13485678
gen USD_JPY = runiform()
gen USD_EUR = runiform()
gen USD_GBP = runiform()
gen USD_INR = runiform()
gen USD_AUD = runiform()
gen USD_CAD = runiform()
gen USD_SGD = runiform()
gen USD_CHF = runiform()
gen USD_MYR = runiform()
gen USD_CNY = runiform()
save exchange.dta, replace

use firm.dta, clear
sort year month
merge m:1 year month using exchange.dta
drop if _merge == 2
sort firmid year month

foreach e of varlist USD_JPY - USD_CNY {
local c = substr("`e'",5,7)
replace `e' = . if w_`c' == .
}

//end, data creation

egen fy = group(firmid year)
gen VAR = .
mata
mata clear
st_view(wt=.,.,tokens("w_AUD - w_SGD"))
st_view(ex=.,.,tokens("USD_JPY - USD_CNY"))
st_view(year=.,.,"year")
st_view(VAR=.,.,"VAR")
st_view(fy=.,.,"fy")
p = panelsetup(fy, 1)

for (i=1; i<=rows(p); i++) {
    r1 = p[i,1]
    r2 = p[i,2]
    w = select(wt[r1..r2,.],  colsum(wt[r1..r2,.]:!=.) :== rows(wt[r1..r2,.])   )
    w = mean(w)
    s = select(ex[r1..r2,.],  colsum(ex[r1..r2,.]:!=.) :== rows(ex[r1..r2,.])   )
    s = (s :- mean(s))'(s :- mean(s)) :/ ( rows(s) - 1)
    VAR[r1..r2,1] = (w*s*w') * J(r2-r1+1,1,1)
}
end

Abraham

[Elisabeth Maes]

Thanks a lot, Abraham!

[Elisabeth Maes]

One more question, how come the code returns the error r(3200) in some instances, despite the matrices being compatible? Could missing values ( for the exchange rates) be the reason

*: 3200 conformability error
<istmt>: - function returned error

[Abraham Wolde-Tsadick]

Hi Elisabeth,

That may be the reason. The following code might solve the problem.

for (i=1; i<=rows(p); i++) {
    r1 = p[i,1] ;     r2 = p[i,2]
    w = mean( select(wt[r1..r2,.],  colsum(wt[r1..r2,.]:!=.) :== rows(wt[r1..r2,.])  )  )
    s = variance( select(ex[r1..r2,.],  colsum(ex[r1..r2,.]:!=.) :> 0   ) )
    if (cols(w) == cols(s)) {
        VAR[r1..r2,1] = (w*s*w') * J(r2-r1+1,1,1)
    }    
}

Abraham

[Elisabeth Maes]

Thanks a lot!

[Elisabeth Maes]

In follow up to my previous post: What could cause Stata (12SE) to run the previously suggested mata code up until a certain point and then output the following:
variance(): 3301 subscript invalid
<istmt>: - function returned error
?

The VAR variable representing portfolio variance is calculated correctly for a large number of observations in my panel, giving missing values where data is lacking, but then just stops running at a certain point, although there does not seem to be anything different between this observation and the previous ones. Has anyone seen this so far?

Many thanks for any suggestions,
Elisabeth

[Konrad Zdeb]

Therefore I would like to use Modern Portfolio Theory to calculate portfolio variance.

I would like to the liberty and suggest some interesting work related to the subject undertaken in R that I came across recently:

• Building Optimized Portfolio with R
• Package fPortfolio
• Modern Portfolio Optimization Theory: The idea
• Package stockPortfolio

[Elisabeth Maes]

Thanks, Konrad, for your reply, but for the time being I would like to stick to Stata. Any other suggestions?

[Elisabeth Maes]

Finally solved it. Apparently, one single observation had all missing returns, while having non-missing weights.